Mysql的索引

从数据结构说起

1. 哈希存储

最基本的k-v存储，优点是根据k取值效率高，插入快；缺点也很明显，如果做区间查询，需要便利整个存储数据链。键冲突问题可以使用链表或者树来解决。

1. 数组存储

有序数组存储是在等值查询和区间查询均具有很高的性能。但是在插入的时候，可能涉及到数组重排序问题。

1. 二叉搜索树

二叉搜索树的特性是所有的左子节点均比根节点小，右子节点均比根节点大。它的查询效率是O（log(N)），插入效率，为了维持树的平衡，也是O（log(N)）级别的。二叉搜索树应该是搜索效率最快的数据结构。但是数据库中一般不用它，而是使用N叉树。其原因在于如果100W条数据，二叉树高20，数据存在磁盘中，一次查询需要访问20个磁盘块，一次寻址访问10ms，查询的效率也是低效的。

1. N叉树

二叉树中的一个节点只存2个儿子，N叉树，一个节点有N个儿子，数据之间按照顺序从左到右排列。InnoDB引擎的一个整数字段索引，一个节点大概存1200条数据。如果树高为4，那么可存储的数据为1200的3次方，也有17亿之多。如果极限要求每次查询需要控制在50ms之内，那么保证寻址磁盘时间在50ms以内，只要树高为3，单表数据可达到144W，这可能也是某些文档建议单表数据不超过百万级的原因。

1. 主键索引和一般索引

InnoDB引擎索引分为主键索引和一般索引。主键索引的叶子节点存储的行数据，所以也叫聚簇索引。一般索引保存的是主键值。通过一般索引查询到的是主键值，再根据主键去主键索引查询数据的过程叫回表。

上图中，黑色箭头表示的是根据key name 来查询数据，先根据name查询普通索引，获取Pk的值，在回表根据pk查询数据。红色箭头则表示直接根据pk值查询数据，无需回表。而红色箭头则表示，通过改变查询条件（或者设置联合索引）使所需要的数据都在一般索引中而避免回表查询，这是后面要说的覆盖索引。

1. 索引的维护

上面说过，有序数组的插入可能会导致数组重排序，降低插入的效率。InnoDB的索引树保存的数据时有序的，如果这个时候，索引本身是无序的比如UUID，此时插入数据就需要后面的数据挪动位置以供新数据插入，大大降低了插入效率。更糟糕的是，如果此时该数据页满了，还需要进行数据页分裂处理，将数据页一分为2，也降低了数据空间的使用效率。

1. 设置自增索引的原因

自增主键是指自增列上定义的主键，

在建表语句中一般是这么定义的：

 NOTNULL PRIMARY KEY AUTO_INCREMENT。

如果表设置了主键自增，那么我们在插入数据的时候，根据主键有序性，数据直接插在树的后面，都是追加操作，避免了数据挪动、页分裂等影响效率的问题；另外，由于一般索引保存的是主键的值，所以主键使用自增整数，只要4个字节（bigint  8字节），那么一般索引的占用空间就会比主键使用UUID或者其他长业务主键大大降低。这也是很多资料建议单表必须设置自增主键的原因。无论从性能还是从存储空间考虑，自增主键都是首选。

什么场景适合业务主键当做表主键呢？同时符合2个条件：

• 表只有一个索引
• 该索引是唯一索引。

这其实就是k-v存储。因为表里没有其他索引，所以不用考虑主键大小带来的一般索引的占用空间影响，这个时候我们考虑优先使用主键查询的原则，可以直接把业务主键当做表主键。

1. 关于重建索引

重建索引的sql：

alter table T drop index k;

alter table T add index(k);

这个是一般使用的sql语句，没有问题。为什么要重建索引？在上面分析中提到数据的插入可能导致数据页分裂导致的空间利用率变低等问题，重建索引可以使空间利用更高效。

那么如果我们重建主键呢？sql:

Alter table T drop primary key id;

Alter table T add primary key(id);

无需如此。因为不论是删除主键还是新增主键，最后都会重新建表，2条sql一起执行，第一条等于白做。我们可以使用alter table T engine = InnoDB;来重建主键。

1. 覆盖索引

在第5点中，我们提到了关于回表问题，通过一般索引先拿到主键索引，再回主键索引拿到需要的数据。同时我们介绍了如何不去回表，那就是使用覆盖索引，所谓覆盖索引，就是索引中已经包含了我们需要查找的所有信息，自然就不需要再回表查。减少回表次数是在高并发下非常有用的优化手段。

如果我们有一张身份证id + 姓名的表，是否有必要把身份证Id+姓名做联合索引，取决于sql查询条件和查询并发。高频请求下做联合索引的意义就会很大。

1. 最左前缀原则

我们知道类似： name like ‘%三’ 是无法走到索引的，索引需要符合最左匹配原则。

实际上在联合索引中，最左匹配原则仍然有效。如果有联合索引

index(schoolId,subjectCode),则条件where schoolId = ‘’ 和 where schoolId=’’ and subjectCode = ‘’ 均可以走到索引，甚至where schoolId like ‘xxx%’也可以走到索引。但是只有subjectCode是无法走到索引的。

建议联合索引的标准和原则是怎么样的呢？

原则一：如果通过调整顺序能够减少一个索引的维护，则以此优先

          说明：如果我们已经有联合索引(a,b)，那么我们就可以不再需要单独的索引a了。

原则二：最小空间原则

说明：如果表T有字段name,age，我们既要根据name,age单独查，也要根据name,age联合查。因为只使用age查是不能走联合索引（name,age）的，那么我们是建立(name,age) + age还是(age,name) + name 呢？因为name长度大于age，索引如果我们维护（name,age）和age 两个索引査勇的空间会小于(age,name) 和name 两个索引的。

1. 索引下推

表T中有联合索引(name,age).现在我们查询所有

“姓张的，年纪等于10岁的男子”

Select * from T where name like ‘张%’ and age = 10 and ismale=1 ;

在mysql5.6之前，数据库会先根据联合索引找到姓张的人，然后再回表比较age=10和ismale=1的人取出数据。

Mysql5.6之后，数据库会进行索引下推，先根据联合索引查找张姓的数据，直接获取联合索引的age进行比较，检少回表的次数。