InnoDB索引允许NULL对性能有影响吗

た 入场券 2023-02-22 13:41 16阅读 0赞

谈谈InnoDB辅助索引的几个特征。

阅读目录

0. 初始化测试表、数据

1. 问题1:索引列允许为NULL,对性能影响有多少

  1. **结论1,**存储大量的NULL值,除了计算更复杂之外,数据扫描的代价也会更高一些

2. 问题2:辅助索引需要MVCC多版本读的时候,为什么需要依赖聚集索引

  1. **结论2,**辅助索引中不存储DB\_TRX\_ID,需要依托聚集索引实现MVCC

3. 问题3:为什么查找数据时,一定要读取叶子节点,只读非叶子节点不行吗

  1. **结论3,**在索引树中查找数据时,最终一定是要读取叶子节点才行

4. 问题4:索引列允许为NULL,会额外存储更多字节吗

结论4,定义列值允许为NULL并不会增加物理存储代价,但对索引效率的影响要另外考虑

5. 几点总结

6. 延伸阅读

本文开始之前,有几篇文章建议先复习一下

  • InnoDB表聚集索引层高什么时候发生变化
  • 浅析InnoDB索引结构
  • Innodb页合并和页分裂
  • innblock | InnoDB page观察利器

接下来,我们一起测试验证关于辅助索引的几个特点。

0. 初始化测试表、数据

测试表结构如下:

  1. [root@yejr.run]> CREATE TABLE `t_sk` (
  2. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  3. `c1` int(10) unsigned NOT NULL,
  4. `c2` int(10) unsigned NOT NULL,
  5. `c3` int(10) unsigned NOT NULL,
  6. `c4` int(10) unsigned NOT NULL,
  7. `c5` datetime NOT NULL,
  8. `c6` char(20) NOT NULL,
  9. `c7` varchar(30) NOT NULL,
  10. `c8` varchar(30) NOT NULL,
  11. `c9` varchar(30) NOT NULL,
  12. PRIMARY KEY (`id`),
  13. KEY `k1` (`c1`)
  14. ) ENGINE=InnoDB;

除了主键索引外,还有个 c1 列上的辅助索引。

mysql_random_data_load 灌入50万测试数据。

  1. [root@yejr.run]# mysql_random_data_load -hXX -uXX -pXX test t_sk 500000

1. 问题1:索引列允许为NULL,对性能影响有多少

把辅助索引列 c1 修改为允许NULL,并且随机更新5万条数据,将 c1 列设置为NULL

  1. [root@yejr.run]> alter table t_sk modify c1 int unsigned;
  2. [root@yejr.run]> update t_sk set c1 = NULL order by rand() limit 50000;
  3. Query OK, 50000 rows affected (2.83 sec)
  4. Rows matched: 50000 Changed: 50000 Warnings: 0
  5. #随机1/10为null
  6. [root@yejr.run]> select count(*) from t_sk where c1 is null;
  7. +----------+
  8. | count(*) |
  9. +----------+
  10. | 50000 |
  11. +----------+

好,现在观察辅助索引的索引数据页结构。

  1. [root@yejr.run]# innblock test/t_sk.ibd scan 16
  2. ...
  3. Datafile Total Size:100663296
  4. ===INDEX_ID:46 --聚集索引(主键索引)
  5. level2 total block is (1) --根节点,层高2(共3层),共1page
  6. block_no: 3,level: 2|*|
  7. level1 total block is (5) --中间节点,层高1,共5page
  8. block_no: 261,level: 1|*|block_no: 262,level: 1|*|block_no: 263,level: 1|*|
  9. block_no: 264,level: 1|*|block_no: 265,level: 1|*|
  10. level0 total block is (5020) --叶子节点,层高0,共5020page
  11. block_no: 5,level: 0|*|block_no: 6,level: 0|*|block_no: 7,level: 0|*|
  12. ...
  13. ===INDEX_ID:47 --辅助索引
  14. level1 total block is (1) --根节点,层高1(共2层),共1page
  15. block_no: 4,level: 1|*|
  16. level0 total block is (509) --叶子节点,层高0,共509page
  17. block_no: 18,level: 0|*|block_no: 19,level: 0|*|block_no: 31,level: 0|*|
  18. ...

观察辅助索引的根节点里的数据

  1. [root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 4 page-dump
  2. ...
  3. records:
  4. {:format=>:compact,
  5. :offset=>126, --第一条记录
  6. :header=>
  7. {:next=>428,
  8. :type=>:node_pointer,
  9. :heap_number=>2,
  10. :n_owned=>0,
  11. :min_rec=>true, --min_rec表示最小记录
  12. :deleted=>false,
  13. :nulls=>["c1"],
  14. :lengths=>{},
  15. :externs=>[],
  16. :length=>6},
  17. :next=>428,
  18. :type=>:secondary,
  19. :key=>[{:name=>"c1", :type=>"INT UNSIGNED", :value=>:NULL}], --对应c1列值为NULL
  20. :row=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>9}], --对应id=9
  21. :sys=>[],
  22. :child_page_number=>18, --指向叶子节点 pageno = 18
  23. :length=>8}
  24. ...
  25. {:format=>:compact,
  26. :offset=>6246, --最后一条记录(next=>112,指向supremum)
  27. :header=>
  28. {:next=>112,
  29. :type=>:node_pointer,
  30. :heap_number=>346,
  31. :n_owned=>0,
  32. :min_rec=>false,
  33. :deleted=>false,
  34. :nulls=>[],
  35. :lengths=>{},
  36. :externs=>[],
  37. :length=>6},
  38. :next=>112,
  39. :type=>:secondary,
  40. :key=>[{:name=>"c1", :type=>"INT UNSIGNED", :value=>2142714688}], --对应c1=2142714688
  41. :row=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>73652}], --对应id=73652
  42. :sys=>[],
  43. :child_page_number=>2935, --指向叶子节点2935
  44. :length=>12}

经过统计,根节点中c1列值为NULL的记录共有33条,其余476条是c1列值为非NULL,共509条记录。

叶子节点中,每个page大约可以存储1547条记录,共有5万条记录值为NULL,因此需要至少33个page来保存(ceiling(50000/1547) = 33)。

看下这个SQL的查询计划

  1. [root@yejr.run]> desc select count(*) from t_sk where c1 is null\G
  2. *************************** 1. row ***************************
  3. id: 1
  4. select_type: SIMPLE
  5. table: t_sk
  6. partitions: NULL
  7. type: ref
  8. possible_keys: k1
  9. key: k1
  10. key_len: 5
  11. ref: const
  12. rows: 99112
  13. filtered: 100.00
  14. Extra: Using where; Using index

从上面的输出中,我们能看到,当索引列设置允许为NULL时,是会对其纳入索引统计信息,并且值为NULL的记录,都是存储在索引树的最左边。

接下来,跑几个SQL查询。

SQL1,统计所有NULL值数量

  1. [root@yejr.run]> select count(*) from t_sk where c1 is null;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. | 50000 |
  6. +----------+

查看slow log

  1. InnoDB_pages_distinct: 34
  2. ...
  3. select count(*) from t_sk where c1 is null;

共需要扫描34个page,根节点(1)+叶子节点(33),正好34个page。

备注:需要用Percona版本才能在slow query log中有InnoDB_pages_distinct信息。

SQL2, 查询 c1 is null

  1. [root@yejr.run]> select id,c1 from t_sk where c1 is null limit 1;
  2. +------+------+
  3. | id | c1 |
  4. +------+------+
  5. | 9607 | NULL |
  6. +------+------+

查看slow log

  1. InnoDB_pages_distinct: 12
  2. ...
  3. select id,c1 from t_sk where c1 is null limit 1;

这次的查询需要扫描12个page,除去1个根节点外,还需要扫描12个叶子节点,只是为了返回一条数据而已,这代价有点大。

如果把SQL微调改成下面这样

  1. [root@yejr.run]> select id,c1 from t_sk where c1 is null limit 10000,1;
  2. +-------+------+
  3. | id | c1 |
  4. +-------+------+
  5. | 99671 | NULL |
  6. +-------+------+

可以看到还是需要扫描12个page。

  1. InnoDB_pages_distinct: 12
  2. ...
  3. select id,c1 from t_sk where c1 is null limit 10000,1;

SQL3, 查询 c1 任意非NULL值
如果把 c1列条件改成正常的int值,结果就不太一样了

  1. [root@yejr.run]> select id, c1 from t_sk where c1 = 907299016;
  2. +--------+-----------+
  3. | id | c1 |
  4. +--------+-----------+
  5. | 365115 | 907299016 |
  6. +--------+-----------+
  7. 1 row in set (0.00 sec)

slow log是这样的

  1. InnoDB_pages_distinct: 2
  2. ...
  3. select id, c1 from t_sk where c1 = 907299016;

可以看到,只需要扫描2个page,这个看起来就正常了。

结论1,存储大量的NULL值,除了计算更复杂之外,数据扫描的代价也会更高一些

另外,如果要查询的c1值正好介于两个page的临界位置,那么需要多读取一个page。

扫描第31号page,确认该数据页中的最小和最大物理记录

  1. [root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 31 page-dump
  2. ...
  3. records:
  4. {:format=>:compact,
  5. :offset=>126,
  6. :header=>
  7. {:next=>9996,
  8. :type=>:conventional,
  9. :heap_number=>2,
  10. :n_owned=>0,
  11. :min_rec=>false,
  12. :deleted=>false,
  13. :nulls=>[],
  14. :lengths=>{},
  15. :externs=>[],
  16. :length=>6},
  17. :next=>9996,
  18. :type=>:secondary,
  19. :key=>[{:name=>"c1", :type=>"INT UNSIGNED", :value=>1531865685}],
  20. :row=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>1507}],
  21. :sys=>[],
  22. :length=>8}
  23. ...
  24. {:format=>:compact,
  25. :offset=>5810,
  26. :header=>
  27. {:next=>112,
  28. :type=>:conventional,
  29. :heap_number=>408,
  30. :n_owned=>0,
  31. :min_rec=>false,
  32. :deleted=>false,
  33. :nulls=>[],
  34. :lengths=>{},
  35. :externs=>[],
  36. :length=>6},
  37. :next=>112,
  38. :type=>:secondary,
  39. :key=>[{:name=>"c1", :type=>"INT UNSIGNED", :value=>1536700825}],
  40. :row=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>361382}],
  41. :sys=>[],
  42. :length=>8}

指定c1的值为 1531865685、1536700825 执行查询,查看slow log,确认都需要扫描3个page,而如果换成介于这两个值之间的数据,则只需要扫描2个page。

  1. InnoDB_pages_distinct: 3
  2. ...
  3. select id, c1 from t_sk where c1 = 1531865685;
  4. InnoDB_pages_distinct: 3
  5. ...
  6. select id, c1 from t_sk where c1 = 1536700825;
  7. InnoDB_pages_distinct: 2
  8. ...
  9. select id, c1 from t_sk where c1 = 1536630003;
  10. InnoDB_pages_distinct: 2
  11. ...
  12. select id, c1 from t_sk where c1 = 1536575377;

这是因为辅助索引是非唯一的,即便是在等值查询时,也需要再读取下一条记录,以确认已获取所有符合条件的数据。

还有,当利用辅助索引读取数据时,如果要读取整行数据,则需要回表。

也就是说,除了扫描辅助索引数据页之外,还需要扫描聚集索引数据页。

来个例子看看就知道了。

  1. #无需回表时
  2. InnoDB_pages_distinct: 2
  3. ...
  4. select id, c1 from tnull where c1 = 1536630003;
  5. #需要回表时
  6. InnoDB_pages_distinct: 5
  7. ...
  8. select * from t_sk where c1 = 1536630003;

需要回表时,除了扫描辅助索引页2个page外,还需要回表扫描聚集索引页,而聚集索引是个3层树,因此总共需要扫描5个page。

2. 问题2:辅助索引需要MVCC多版本读的时候,为什么需要依赖聚集索引

InnoDB的MVCC是通过在聚集索引页中同时存储了DB_TRX_ID和DB_ROLL_PTR来实现的。

但是我们从上面page dump出来的结果也很明显能看到,附注索引页是不存储DB_TRX_ID信息的。

所以说,辅助索引上如果想要实现MVCC,需要通过回表读聚集索引来实现。

结论2,辅助索引中不存储DB_TRX_ID,需要依托聚集索引实现MVCC

3. 问题3:为什么查找数据时,一定要读取叶子节点,只读非叶子节点不行吗

在辅助索引的根节点这个页面中(pageno=4),我们注意到它记录的最小记录(min_rec)对应的是(c1=NULL, id=9)这条记录。

在它指向的叶子节点页面中(pageno=18)也确认了这个情况。

现在把id=9的记录删掉,看看辅助索引数据页会发生什么变化。

  1. [root@yejr.run]> delete from t_sk where id = 9 and c1 is null;
  2. Query OK, 1 row affected (0.01 sec)

先检查第4号数据页。

  1. [root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 4 page-dump
  2. ...
  3. records:
  4. {:format=>:compact,
  5. :offset=>126,
  6. :header=>
  7. {:next=>428,
  8. :type=>:node_pointer,
  9. :heap_number=>2,
  10. :n_owned=>0,
  11. :min_rec=>true,
  12. :deleted=>false,
  13. :nulls=>["c1"],
  14. :lengths=>{},
  15. :externs=>[],
  16. :length=>6},
  17. :next=>428,
  18. :type=>:secondary,
  19. :key=>[{:name=>"c1", :type=>"INT UNSIGNED", :value=>:NULL}],
  20. :row=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>9}],
  21. :sys=>[],
  22. :child_page_number=>18,
  23. :length=>8}
  24. ...

看到第四号数据页中,最小记录还是 id=9,没有更新。

再查看第18号数据页。

  1. [root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 18 page-dump
  2. ...
  3. records:
  4. {:format=>:compact,
  5. :offset=>136,
  6. :header=>
  7. {:next=>146,
  8. :type=>:conventional,
  9. :heap_number=>3,
  10. :n_owned=>0,
  11. :min_rec=>false,
  12. :deleted=>false,
  13. :nulls=>["c1"],
  14. :lengths=>{},
  15. :externs=>[],
  16. :length=>6},
  17. :next=>146,
  18. :type=>:secondary,
  19. :key=>[{:name=>"c1", :type=>"INT UNSIGNED", :value=>:NULL}],
  20. :row=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>30}],
  21. :sys=>[],
  22. :length=>4}
  23. ...

在这个数据页(叶子节点)中,最小记录已经被更新成 id=30 这条数据了。

可见,索引树中的非叶子节点数据不是实时更新的,只有叶子节点的数据才是最准确的。

结论3,在索引树中查找数据时,最终一定是要读取叶子节点才行

4. 问题4:索引列允许为NULL,会额外存储更多字节吗

之前流传有一种说法,不允许设置列值允许NULL,是因为会额外多存储一个字节,事实是这样吗?

我们先把c1列改成NOT NULL DEFAULT 0,当然了,改之前要先把所有NULL值更新成0。

  1. [root@yejr.run]> update t_sk set c1=0 where c1 is null;
  2. [root@yejr.run]> alter table t_sk modify c1 int unsigned not null default 0;

在修改之前,每条索引记录长度都是10字节,更新之后却变成了13个字节。
直接对比索引页中的数据,发现不同之处

  1. #允许为NULL,且默认值为NULL时
  2. {:format=>:compact,
  3. :offset=>136,
  4. :header=>
  5. {:next=>146,
  6. :type=>:conventional,
  7. :heap_number=>3,
  8. :n_owned=>0,
  9. :min_rec=>false,
  10. :deleted=>false,
  11. :nulls=>["c1"],
  12. :lengths=>{},
  13. :externs=>[],
  14. :length=>6},
  15. :next=>146,
  16. :type=>:secondary,
  17. :key=>[{:name=>"c1", :type=>"INT UNSIGNED", :value=>:NULL}],
  18. :row=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>48}],
  19. :sys=>[],
  20. :length=>4}
  21. #不允许为NULL,默认值为0时
  22. {:format=>:compact,
  23. :offset=>138,
  24. :header=>
  25. {:next=>151,
  26. :type=>:conventional,
  27. :heap_number=>3,
  28. :n_owned=>0,
  29. :min_rec=>false,
  30. :deleted=>false,
  31. :nulls=>[],
  32. :lengths=>{},
  33. :externs=>[],
  34. :length=>5},
  35. :next=>151,
  36. :type=>:secondary,
  37. :key=>[{:name=>"c1", :type=>"INT UNSIGNED", :value=>0}],
  38. :row=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>48}],
  39. :sys=>[],
  40. :length=>8}

可以看到,原先允许为NULL时,record header需要多一个字节(共6字节),但实际物理存储中无需存储NULL值。

而当设置为NOT NULL DEFAULT 0时,record header只需要5字节,但实际物理存储却多了4字节,总共多了3字节,所以索引记录以前是10字节,更新后变成了13字节,实际上代价反倒变大了。

列值允许为NULL更多的是计算代价变大了,以及索引对索引效率的影响,反倒可以说是节省了物理存储开销。

结论4,定义列值允许为NULL并不会增加物理存储代价,但对索引效率的影响要另外考虑

最后,本文使用的MySQL版本Percona-Server-5.7.22,下载源码后自编译的。

  1. Server version: 5.7.22-22-log Source distribution

5. 几点总结

最后针对InnoDB辅助索引,总结几条建议吧。
a) 索引列最好不要设置允许NULL。
b) 如果是非索引列,设置允许为NULL基本上无所谓。
c) 辅助索引需要依托聚集索引实现MVCC。
d) 叶子节点总是存储最新数据,而非叶子节点则不一定。
e) 尽可能不SELECT *,尽量利用覆盖索引完成查询,能不回表就不回表。

6. 延伸阅读

  • InnoDB表聚集索引层高什么时候发生变化
  • 浅析InnoDB索引结构
  • Innodb页合并和页分裂
  • innblock | InnoDB page观察利器
  • jcole.us:The physical structure of InnoDB index pages
  • jcole.us:B+Tree index structures in InnoDB

Enjoy MySQL :)

全文完。


    • 由叶老师主讲的「MySQL优化」课已升级到MySQL 8.0版本,扫码开启MySQL 8.0的修行之旅吧
  1. * ![format_png][]

发表评论

表情:
评论列表 (有 0 条评论,16人围观)

还没有评论,来说两句吧...

相关阅读