一、背景
对于后端开发者而言，在开发过程中与数据库打交道是不可避免的，由于mysql的简单高效，几乎是绝大多数开发者选择的持久化工具。但是即便工具再优秀，如果只知其然而不知其所以然，是很难发挥出它该有的性能。本次分享主要针对mysql索引的基础知识，让大家在开发的时候也能同时考虑数据库性能，写出高效简洁优雅的sql。同时也是抛砖引玉，希望有兴趣的小伙伴一起深入研究关于mysql的种种。那么接下来就让我们一起进入主题吧。
二、基础知识
1、索引概念
索引是帮助MySQL高效获取数据的数据结构。
我们知道，查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最简单的查询算法当然是顺序查找，但是这种算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找、二叉树查找等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构，所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
2、索引类型
| 类型 | 说明 |
|---|---|
| 普通索引 | 就是最常见的索引，一般不具备唯一性 |
| 唯一索引 | 与普通索引相似，该索引具备唯一性，但可以包含空值 |
| 主键索引 | 组件索引是一种特殊的唯一索引，与唯一索引的区别是，不可包含空值 |
| 组合索引 | 与普通索引类似，可以使用多个列组成，遵循“最左前缀”匹配原则 |
3、调试神器-explain
3.1 explain字段含义
| 字段 | 含义 |
|---|---|
| id | 执行编号，值越大越先执行 |
| select_type | 显示查询的类型，比如简单查询还是子查询等 |
| table | 访问引用哪个表（引用某个查询，如“derived1”） |
| type | 数据访问/读取操作类型（ALL、index、range、ref、eq_ref、const/system、NULL） |
| possible_keys | 列出哪一些索引可能有利于高效的查找 |
| key | 显示mysql在本次查询中决定使用的索引 |
| key_len | 显示mysql在索引里使用的字节数 |
| ref | 显示了之前的表在key列记录的索引中查找值所用的列或常量 |
| rows | 为了找到所需的行而需要读取的行数，估算值，不精确。 |
| Extra | 额外信息，如using index、filesort等 |
3.2 部分字段详解
3.2.1 select_type
| 字段 | 含义 |
|---|---|
| simple | 简单子查询，不包含子查询和union |
| primary | 包含union或者子查询，最外层的部分标记为primary |
subquery 一般子查询中的子查询被标记为subquery，也就是位于select列表中的查询
derived| 派生表——该临时表是从子查询派生出来的，位于form中的子查询
union| 位于union中第二个及其以后的子查询被标记为union
union result| 用来从匿名临时表里检索结果的select被标记为union result
dependent union | UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询
dependent subquery | 子查询中第二个以及后面的SELECT语句，同时该语句依赖外部的查询
3.2.2 type
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref。
| 类型 | 说明 |
|---|---|
| All | 全表扫描 |
| index | 和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多 |
| range | 范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range |
| ref | 一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。ref可以用于使用=或<=>操作符的带索引的列。 |
| eq_ref | 最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效） |
| const | 当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效） |
| system | 这是const连接类型的一种特例，表仅有一行满足条件。 |
| Null | 意味说mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引 |
3.2.3 key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好 。
3.2.3 extra
Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。
| 类型 | 说明 |
|---|---|
| Using filesort | MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。 |
| Using temporary | 用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。 |
| Not exists | MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。 |
| Using index | 说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。 |
| Using index condition | 这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。 |
| Using where | 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。 |
| Using join buffer | 使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接 |
| impossible where | where子句的值总是false，不能用来获取任何元组 |
| select tables optimized away | 在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 |
| distinct | 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作 |
三、索引原理
前面说到，索引是一种数据结构，那么mysql在维护数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，达成快速定位查询数据的目的。
看一个例子：
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。
B-Tree和B+Tree
目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，而mysql的索引在默认情况下是使用B+Tree结构（InnoDB）；下面简单描述一下这两种数据结构：
B-Tree是满足下列条件的数据结构：
- 1.d为大于1的一个正整数，称为B-Tree的度。
- 2.h为一个正整数，称为B-Tree的高度。
- 3.每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
- 4.每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
- 5.所有叶节点具有相同的深度，等于树高h。
- 6.key和指针互相间隔，节点两端是指针。
- 7.一个节点中的key从左到右非递减排列。
- 8.所有节点组成树结构。
- 9.每个指针要么为null，要么指向另外一个节点。
- 10.如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)，其中v(key1)为node的第一个key的值。
- 11.如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)，其中v(keym)为node的最后一个key的值。
- 12.如果某个指针在节点node的左右相邻key分别是keyi和keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)且大于v(keyi)。
由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。
B+Tree
B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。
与B-Tree相比，B+Tree有以下不同点：
- 每个节点的指针上限为2d而不是2d+1。
- 内节点不存储data，只存储key；叶子节点不存储指针。
一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。
InnoDB索引实现
InnoDB使用B+Tree作为索引结构，在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
图4是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
而InnoDB的所有辅助索引都引用主键作为data域。例如，图5为定义在Col3上的一个辅助索引：
聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
四、常见优化
1、选择适合创建索引的字段
通过创建索引，可以有效的提高查询的效率，但索引并不是银弹，它给我们带来便利的同时，也有对我们性能上的限制，过多的索引不仅会占用存储空间，耗费更多的维护时间，最重要的是对表的数据进行更新（增删改）时索引也要动态维护，如果索引的量很大，就会大大降低数据的维护速度。因此选择合适的字段建索引，是数据库开发的一个重要技能。
一般来说，应该在这些列上创建索引，例如：
- 1)在经常需要搜索的列上，可以加快搜索的速度；
- 2)在作为主键的列上（主键索引）；
- 3)在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
- 4)在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
- 5)在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
- 6)在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
一般来说，不应该创建索引的的这些列具有下列特点：
- 对于那些在查询中很少使用或者参考的列不应该创建索引。
- 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如性别列，由于这些列的区分度不高，并不能明显减少查询数据行，加快检索速度。
- 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
- 当修改性能远远大于检索性能时，不应该创建索引。
那么，如何定义数据的区分度高低，是不是适合建索引呢？
这里有一条公式计算索引的区分度：数据表中不重复的索引值与表记录数的比值。
即：select count(distinct(col1,col2)) / count(1) from table
可以看出，计算结果落在(0,1]区间内，并且值越大，所以区分度越高，越适合建索引。
2、导致索引失效的查询
- 查询的数量是大表的大部分，大约30％以上。
- 索引本身失效
- 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，，/，! 等) 错误的例子：select from test where id-1=9; 正确的例子：select * from test where id=10;
- 对小表查询
- 隐式转换导致索引失效. 错误的例子：select from test where tu_mdn=13333333333; 正确的例子：select from test where tu_mdn='13333333333';
- like "%_" 百分号在前.
- 向右匹配直到遇到范围查询(>、<、between、like)，后面的索引就会停止匹配
- 单独引用复合索引里非第一位置的索引列.
- not in ,not exist.
- B-tree索引 is null不会走,is not null会走
- 联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时，必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时，其他建立索引的列可以是is null（但必须在所有列，都满足is null的时候），或者=一个值； 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走。