MySQL——索引

1 索引的常见模型

1.1 哈希表

哈希表是一种以键-值（key-value)存储数据的结构，只要输入待查找的键key, 就可以找到对应的值value。
哈希表适用于做等值查询（即查找的key值在表中有对应），但是哈希索引做区间查询的速度是很慢的

1.2 有序数组

查找数据的时间复杂度是O(log(N))。仅看查询效率，有序数组是一种非常好的数据结构，但是更新数据比较慢，因为要做数据的挪动工作。所以，有序数据只适用于静态存储引擎。

1.3 N叉搜索树

多叉树就是每个结点有多个儿子，儿子之间的大小保证从左到右递增。二叉搜索树效率是最高的，但是实际大多数的数据库存储并不使用二叉树，原因是，索引不止存在内存中，还要写到磁盘上。为了尽量少地读磁盘，一般使用N叉树。N取决于数据块的大小。

2 InnoDB的索引模型

2.1主键索引与非主键索引

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式称为索引组织表。InnoDB使用B+树索引模型，所以数据都是存储在B+树中的。
索引类型分为主键索引和非主键索引。
主键索引的叶子结点是整行数据。在InnoDB中，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子结点是主键的值。在InnoDB中，非主键索引也被称为二级索引。

2.2 索引维护

自增主键是指自增列上定义的主键，在建表语句中一般是：NOT NULL PRIMARY KEY AUTO——INCREMENT。
插入新纪录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。
从性能和存储空间方面考量，自增主键往往是更合理的选择。

3 索引优化

3.1 覆盖索引

执行语句 select ID from T where k between 3 and 5（其中ID为主键，k字段建立索引），这时只需要查ID的值，而ID的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表，提高了查询效率。
如果对某一相关数据有高频请求，就可以在相关字段建立联合索引。因为在高频请求中可以用到覆盖索引，不需要再回表查整行记录，减少语句的执行时间。

3.2 最左前缀原则

最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符，如SQL语句的 "where name like '张%’ "。
所以在建立联合索引的时候要注意如何安排索引内的字段顺序：第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的； 第二原则是考虑建立索引需要的空间，占用空间越少越好。

3.3 索引下推

有一个联合索引（name, age)在执行语句：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

在 MySQL 5.6 之前， 根据前缀索引，语句在搜索树的时候，只能用 ”张“ 找到满足条件的，然后回表判断 age 值是否满足。
在MySQL 5.6之后， 引入的索引下推优化（index condition pushdown），可以在索引遍历过程中，对索引中包含的字段做优化，即联合索引（name, age)会直接滤掉不满足条件的age记录，减少回表次数。

参考文章：

1. MySQL InnoDB 数据页结构分析https://www.cnblogs.com/bdsir/p/8745553.html
2. MySQL实战 https://time.geekbang.org/column/article/69236