MySQL索引实践

一、MySQL架构

1.1 发展历程

MySQL最早来源于MySQL AB公司前身的ISAM与mSQL项目，于1996年前后发布第一个版本1.0，当时只支持SQL特性，没有事务支持。在2000 年的时候，MySQL 公布了自己的源代码，并采用GPL许可协议，正式进入开源世界。2000年4月，MySQL对旧的存储引擎进行了整理，命名为MyISAM。

2002年前后，InnoDB引擎出现，并加入到MySQL4.0中，在2005年的时候发布了5.0版本。三年之后也就是2008年MySQL被Sun收购，次年Sun被Oracle收购，MySQL从此转入Oracle门下，得到了更加快速的发展，后面基本上是每隔两三年就发布一个新版本，并且在2018年发布了最新的8.0版本

1.2 架构

MySQL整体架构包括三部分：客户端层、服务器层以及存储引擎层。如下图所示，client进行请求的输入，server 负责 sql的parse与执行; storage engine 去真正的做数据或索引的读取和写入。

每个客户端连接都会在mysql服务端产生一个线程（内部通过线程池管理），比如一个select语句进入，mysql首先会在查询缓存中查找是否缓存了这个select的结果集，如果没有则继续执行解析、优化、执行的过程；否则会从缓存中获取结果集。具体mysql的执行过程如下图所示。

1.3 存储引擎

MySQL主要包括两大存储引擎：

1、MyISAM，其特点主要如下

• 表锁
• 不支持事务
• 不支持外键
• 索引文件和数据文件分开的，索引文件仅保存（叶节点的data域存放的是）数据记录的地址 

2、InnoDB

InnoDB作为目前最常用的存储引擎，具体下面特性

• 行锁，间隙锁等
• 支持事务
• 支持外键
• InnoDB的表数据⽂件本身就是（主）索引⽂件，这个索引的key是数据表的主键，树叶子节点data域保存了完整的数据记录

除此之外，还有Memory ,Csv,Archive,Blackhole等内建引擎，以及PBXT，TokuDB等第三方引擎。平时用的最广泛的是InnoDB,因此本文就是基于此讨论。

二、索引分类

索引（Index）是帮助MySQL高效获取数据的数据结构，加速查询。InnoDB存储引擎目前支持索引类型主要包括三种：B+树索引、哈希索引以及全文索引。

由于B+树高度较低且能很好的利用局部性原理及磁盘预读特性，能很好的减少磁盘IO（每个节点可存储多个记录，将节点大小设置为页长，比如4k），因此B+树索引在实际生产中用的最多。下面我们主要介绍B+树种的两种形式：主键索引和辅助索引。

2.1 主键索引

主键索引又称为聚集索引（Clustered Index），决定了数据在磁盘上的物理排序，它是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。由于InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，若未显式指定且不存在任何一列可以唯一标识数据记录，则Mysql会自动生成一个6字节的long型隐含字段作为内置主键。

如下所示，MySQL会以主键的⽅式构造⼀棵树，叶⼦节点存放该主键对应的整⾏数据。

2.2 辅助索引

辅助索引即非聚集索引（Non-Clustered Index），其data域存储相应记录主键的值而不是地址。辅助索引的树，叶⼦节点存放两个东⻄，⼀个是索引⾃⾝的值，另外⼀个是索引对应主键的值。其组织结构如下所示（addtime上建立了索引）

很明显该辅助索引树有两个特点，一是其叶子节点为addtime,并且是排好序的，二是这个addtime对应的另一个值是主键value。假设SQL命中了辅助索引，一般情况下，则会进行两次查询（除非发生了索引覆盖），首先去辅助索引树拿到主键的value，然后基于此再去主键索引树中获取叶子节点中的完整数据记录行。

三、索引最佳实践

索引使用是否得当直接影响数据的存取性能，下面主要介绍5种索引最佳实践。

3.1最左前缀匹配

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。假设表stu中一个联合索引

key IX_addid_classid_uptime（add_id, class_id, update_time）

如下给出了8个场景

场景描述	索引使用	举例
1全列匹配	Y
2最左前缀匹配	Y
3查询条件用到了索引中列的精确匹配，但是中间某个条件未提供	N（只能用到索引第一列addid）	select *  from stu where add_id=1 and update_time="2018-01-01";
4查询条件没有指定索引第一列	N	select * from stu where class_id=2 and update_time="2018-01-01";
5匹配某列的前缀字符串	Y
6范围查询后面的排序字段	N（只能用到索引第一列addid）	select * from stu where add_id BETWEEN 10 and 12 order by class_id;
7多值精确匹配	Y
8查询条件中含有函数或表达式	N	select * from stu where add_id *2 = 12;

另外有5种索引失效的情况，平时在实践索引的过程中需要额外注意：

场景描述

1. 对索引列进行运算，运算包括(+，-，*，/，! 等)

2. 隐式转换导致索引失效

3. like "%_" 百分号在前.

4. not in ,not exist

5. 查询的数量是大表的大部分，30％以上

尤其是隐式转换导致索引失效，我们很容易忽视，比如 存在`customer_id` varchar(20) NOT NULL

错误的姿势： select * from rental where customer_id=130;

正确的姿势：select * from rental where customer_id=‘130’;

3.2索引覆盖

覆盖索引（Covering Index）指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，而不需要进行回表查询聚集索引中的记录，也可以称之为实现了索引覆盖，因此可以大大减少随机IO操作。例如对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3,id from tb where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，可以减少了数据的访问量，同时减少了很多的随机io，可以有效提升sql性能，是性能优化的一个重要手段。

验证SQL是否实现了索引覆盖，可通过explain查看extra属性，若extra显示为using index则说明该sql实现了索引覆盖。需要注意的是只有当查询语句所访问的列是索引的一部分时，索引才会覆盖，当然包括主键，因为主键也在辅助索引中。

3.3 Order By

mysql有两种方式生产有序的结果集：

1. 按照索引顺序扫描得出的结果自然是有序的
2. 使用FileSort,对结果集进行排序的操作

好的sql要尽量避免FileSort的发生，利用索引进行排序操作是非常快的。如果索引本身不能覆盖所有需要查询的列，就不得不每扫描一条索引记录就回表查询一次对应的行，为保证索引排序，需注意下面几点：

1. 索引能覆盖所有需要查询的列

2. 索引的列顺序和Order By子句的顺序完全一致

3. Order By子句和查询的限制是一样的，都要满足最左前缀的要求.

3.4 Limit Offset

当需要分页操作时，通常会使用Limit加上偏移量的办法实现，同时加上合适的Order By子句。一个常见的问题是当偏移量非常大的时候，比如：LIMIT 10000,20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，无效查询特别大，资源浪费的同时也会带来性能开销。为此，提供两种优化方案：

• 延迟关联

使用覆盖索引扫描（先不查询所需要的列），然后做一次关联查询再返回所有的列，如下

 

• 两次查询

每次分页查询后返回上一次结果的主键id（记录好上次查询的位置），并基于此id配合limit pageSize进行范围查询，可以有效避免大offset导致的无效查询，案例如下

3.5选择度

索引的选择度（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（TotalSize）的比值：Index Selectivity = Cardinality / TotalSize，选择度的取值范围为(0, 1]，选择度越高的索引价值越大（选择性为1的索引即唯一索引）。如下，单纯用first_name字段索引的选择性很低（0.0042），将first_name和last_nam做一个联合索引，很明显选择性就很好（0.9313）。

 

但是first_name和last_name加起来若长度很长，那有没有兼顾长度和选择性的办法？答案是肯定的，可以利用前缀索引的特性，指定索引参与字段的长度，如

Alter table employees add key (`first_name`,`last_name`(4)); 并且其选择性也比较好，为0.9007

四、总结

上面着重介绍的5种最佳操作只是索引实践中的一部分，下面小结并引申一下使用索引的相关最佳实践：

（1）确保使用索引,尽可能使用联合索引

①Where条件中的高筛字段要有索引

②条件字段禁止函数

③条件字段禁止隐式类型转换

④避免子查询以及关联子查询

（2）索引列选取原则

①使⽤区分度⾼、选择性高的列作为索引

②尽量使⽤字段⻓度⼩的列作为索引,使⽤数据类型简单的列（int 型，固定⻓度）

③尽量的扩展索引，选⽤NOT NULL的列

④索引列不能参与计算，否则索引失效

（3）排序是MySQL的痛

①避免fileSort

②大偏移量limit仍要扫描大量数据，可采取延迟关联或者二次查询方案进行优化

③尽量避免在数据库中做分页,如无法避免,通过条件过滤结果集一次预取多页或者进行分页优化，降低查询次数

④自己程序里去排序特别是大结果集

（4）索引不宜建立场景

①表记录较少，如千⾏以下

②索引的选择性区分度较低