阅读目录
0. 初始化测试表、数据1. 问题1：索引列允许为NULL，对性能影响有多少 结论1，存储大量的NULL值，除了计算更复杂之外，数据扫描的代价也会更高一些2. 问题2：辅助索引需要MVCC多版本读的时候，为什么需要依赖聚集索引 结论2，辅助索引中不存储DB_TRX_ID，需要依托聚集索引实现MVCC3. 问题3：为什么查找数据时，一定要读取叶子节点，只读非叶子节点不行吗 结论3，在索引树中查找数据时，最终一定是要读取叶子节点才行4. 问题4：索引列允许为NULL，会额外存储更多字节吗 结论4，定义列值允许为NULL并不会增加物理存储代价，但对索引效率的影响要另外考虑5. 几点总结6. 延伸阅读
本文开始之前，有几篇文章建议先复习一下
接下来，我们一起测试验证关于辅助索引的几个特点。
0. 初始化测试表、数据
测试表结构如下：
[root@yejr.run]> CREATE TABLE `t_sk` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `c1` int(10) unsigned NOT NULL, `c2` int(10) unsigned NOT NULL, `c3` int(10) unsigned NOT NULL, `c4` int(10) unsigned NOT NULL, `c5` datetime NOT NULL, `c6` char(20) NOT NULL, `c7` varchar(30) NOT NULL, `c8` varchar(30) NOT NULL, `c9` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `k1` (`c1`) ) ENGINE=InnoDB;
除了主键索引外，还有个 c1 列上的辅助索引。
用 mysql_random_data_load 灌入50万测试数据。
1. 问题1：索引列允许为NULL，对性能影响有多少
把辅助索引列 c1 修改为允许NULL，并且随机更新5万条数据，将 c1 列设置为NULL
[root@yejr.run]> alter table t_sk modify c1 int unsigned; [root@yejr.run]> update t_sk set c1 = NULL order by rand() limit 50000; Query OK, 50000 rows affected (2.83 sec) Rows matched: 50000 Changed: 50000 Warnings: 0 #随机1/10为null [root@yejr.run]> select count(*) from t_sk where c1 is null; +----------+ | count(*) | +----------+ | 50000 | +----------+
好，现在观察辅助索引的索引数据页结构。
[root@yejr.run]# innblock test/t_sk.ibd scan 16 ... Datafile Total Size:100663296 ===INDEX_ID:46 --聚集索引(主键索引) level2 total block is (1) --根节点,层高2(共3层),共1个page block_no: 3,level: 2|*| level1 total block is (5) --中间节点,层高1,共5个page block_no: 261,level: 1|*|block_no: 262,level: 1|*|block_no: 263,level: 1|*| block_no: 264,level: 1|*|block_no: 265,level: 1|*| level0 total block is (5020) --叶子节点,层高0,共5020个page block_no: 5,level: 0|*|block_no: 6,level: 0|*|block_no: 7,level: 0|*| ... ===INDEX_ID:47 --辅助索引 level1 total block is (1) --根节点,层高1(共2层),共1个page block_no: 4,level: 1|*| level0 total block is (509) --叶子节点,层高0,共509个page block_no: 18,level: 0|*|block_no: 19,level: 0|*|block_no: 31,level: 0|*| ...
观察辅助索引的根节点里的数据
[root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 4 page-dump ... records: {:format=>:compact, :offset=>126, --第一条记录 :header=> {:next=>428, :type=>:node_pointer, :heap_number=>2, :n_owned=>0, :min_rec=>true, --min_rec表示最小记录 :deleted=>false, :nulls=>["c1"], :lengths=>{}, :externs=>[], :length=>6}, :next=>428, :type=>:secondary, :key=>[{:name=>"c1", :type=>"INT UNSIGNED", :value=>:NULL}], --对应c1列值为NULL :row=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>9}], --对应id=9 :sys=>[], :child_page_number=>18, --指向叶子节点 pageno = 18 :length=>8} ... {:format=>:compact, :offset=>6246, --最后一条记录(next=>112,指向supremum) :header=> {:next=>112, :type=>:node_pointer, :heap_number=>346, :n_owned=>0, :min_rec=>false, :deleted=>false, :nulls=>[], :lengths=>{}, :externs=>[], :length=>6}, :next=>112, :type=>:secondary, :key=>[{:name=>"c1", :type=>"INT UNSIGNED", :value=>2142714688}], --对应c1=2142714688 :row=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>73652}], --对应id=73652 :sys=>[], :child_page_number=>2935, --指向叶子节点2935 :length=>12}
经过统计，根节点中c1列值为NULL的记录共有33条，其余476条是c1列值为非NULL，共509条记录。
叶子节点中，每个page大约可以存储1547条记录，共有5万条记录值为NULL，因此需要至少33个page来保存（ceiling(50000/1547) = 33)。
看下这个SQL的查询计划
[root@yejr.run]> desc select count(*) from t_sk where c1 is null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_sk partitions: NULL type: ref possible_keys: k1 key: k1 key_len: 5 ref: const rows: 99112 filtered: 100.00 Extra: Using where; Using index
从上面的输出中，我们能看到，当索引列设置允许为NULL时，是会对其纳入索引统计信息，并且值为NULL的记录，都是存储在索引树的最左边。
接下来，跑几个SQL查询。
SQL1，统计所有NULL值数量
[root@yejr.run]> select count(*) from t_sk where c1 is null; +----------+ | count(*) | +----------+ | 50000 | +----------+
查看slow log
InnoDB_pages_distinct: 34 ... 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
[root@yejr.run]> select id,c1 from t_sk where c1 is null limit 1; +------+------+ | id | c1 | +------+------+ | 9607 | NULL | +------+------+
查看slow log
InnoDB_pages_distinct: 12 ... select id,c1 from t_sk where c1 is null limit 1;
