阅读目录
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;
</div>
