上篇我们分析了MySQL数据库空间管理问题,今天我们来分析下index问题。

A:(1)MySQL index是基于主键PK来组织存放的,简称cluster index,基于的模型是B+Tree的架构,不用去LSM-Tree,KV,HASH等模型。当然每个模型都有实用的场景,比如RocksDB,LevelDB等好多数据库都基于LSM-tree来实现数据的存储管理维护,好处是insert很快,select较弱,同事存在读写放大的问题。也有适用的场景,这些年基于这个架构做出来了许多改进后的数据库。言归正传,接着说Innodb的事情。简单来说就是index分为主键pk,和其他index,这个其他统称为二级index,那么有什么区别,pk index叶子节点存放的是数据。二级index叶子节点存放的是主键值。那这就涉及到一个问题,一条select如果where条件是pk,那可以直接找到数据,如果是其他二级index列,那么就需要对主键再次查询,简称回表,这个是有代价的,所以有些情况下你看到有些语句有index,但explain执行计划却没有走index,很可能就是执行器认为走index是二级index需要回表代价大于直接full scan pk。

(2)这种索引组织表结构和Oracle里面的堆表结构还是有很大区别的。B+Tree里面存放的数据都是严格有序的,无论是pk还是secondary key,这必然带来一个问题当update,insert,delete这些语句对index操作的时候,要维护其整体的有序就需要进行数据的重组织,结构分裂调整,这必然影响性能。那如何避免pk的乱序呢,MySQL里有个自增主键,如果一个表按照自增主键设置,not null primary key auto_increment.那么insert就是追加插入了保证了顺序,避免page的分裂挪动。但是现实业务场景不见得都用自增做主键,如果我就是想用某个业务字段做主键不行吗?不是不行,问题是涉及到index占用空间问题,index占用空间又会连带一个page存放多少行数据问题,还有B+tree的高度问题,高度问题就会影响性能,主要是IO的。比如你用身份证号这个字符串做主键,那么这个字符串至少20bytes,而如果int做主键占4bytes,bigint占用8个字节。显然主键长度越小,普通index的叶子节点就越小,其占用的空间就越小,后面的效率都会好。

so,主键很重要,尽量使用主键查询。但是回表的问题如何避免呢?

Q:覆盖索引是什么?

A:一般是这样使用,就是在表上根据业务的需要对某个表上经常使用到的业务字段创建联合index,那么这样在使用到相关查询的时候就避免了回表,这会带来另一个问题冗余index,系统里面有很多冗余index,需要权衡这类问题。

联合index有个最左前缀匹配原则,这个最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符。比如,一般有了index(a,b),就不需要单独建立index(a),当然根据业务需要觉得要不要建立index(b)。

Q:index下推是什么?

A:index下推简称ICP,目的是在把对数据的访问直接下推到innodb层面判断,减少在Server层判断后回表的次数,提高性能。

case:重建index的时候,如果一个是主键,一个是二级index,是不是都可以使用

alter table t drop index k;

alter table t add index(k);

这个是可以的。

alter table t drop primary key;

alter table t add primary key(id);

这个是不合理的,不论是删除主键还是创建主键,都会重建整张表。建议使用如下:

alter table t engine=InnoDB来重建主键index。