在最近的运维中，遇到一个慢SQL优化的问题，用户一个简单的SQL跑了30多秒，而我在尝试性的使用已有的其他索引进行查询后，SQL优化到了毫秒级别。这是意外的优化效果，而我却不知道原因，索引决定学习测试一下。
客户的SQL很简单，就只是取出范围时间内，规定id范围内的所有id值，其中id是主键，time是普通索引，类似如下：
select id from t1 where time > '2019-06-18 014:28:48' and id < 123456 order by id desc limit 200优化之前，MySQL执行优化器选择的是走id主键索引，扫描行数在500万。执行时间为30 s 我尝试让SQL走time字段的索引，发现扫描行数在460万左右，执行时间却是在300 ms。
效果差别这么大，究竟是为什么，所以我决定测试一下
我使用的阿里云RDS生成的随机数据，因为RDS是5.7，而客户是在5.6上面。为了确保版本无影响，就在自己的虚拟机上测试安装5.6并将测试数据从RDS上导下来。由此引发一个题外话： 在使用.sql 文件恢复数据的时候，发现会报错
ERROR 2006 (HY000) at line 32: MySQL server has gone away查找相关资料发现，是因为我下载的数据包太大导致的，mysql会拒绝超过限定额的数据包的导入 可以通过相关参数查询
mysql> show variables like 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 4194304 | +--------------------+---------+单位是字节，一般的饿mysql版本默认为1M或4M，而我的软件包确是70M+，显然是不可以的。所以需要将此值增大
SET GLOBAL max_allowed_packet=1073741824; mysql> show variables like 'max_allowed_packet'; +--------------------+-----------+ | Variable_name | Value | +--------------------+-----------+ | max_allowed_packet | 104857600 | +--------------------+-----------+增大之后，数据恢复正常
题外话结束
主表中有如下索引，一个主键一个create_time 索引，为了避免扫描行数对SQL的影响，已尽量保证走不同索引的扫描行数大致相同。
mysql> show index from sys_test\G; *************************** 1. row *************************** Table: sys_test Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 166115 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: sys_test Non_unique: 1 Key_name: idx_create_time Seq_in_index: 1 Column_name: create_time Collation: A Cardinality: 166115 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment:两次测试如下
mysql> explain SELECT `id` FROM `sys_test` WHERE `id` <= '300000' AND `create_time` >= '2015-06-17 07:41:48' ORDER BY `id` desc LIMIT 200; +----+-------------+---------+-------+-------------------------+---------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+-------------------------+---------+---------+------+-------+-------------+ | 1 | SIMPLE | sys_msg | range | PRIMARY,idx_create_time | PRIMARY | 8 | NULL | 83057 | Using where | +----+-------------+---------+-------+-------------------------+---------+---------+------+-------+-------------+ 1 row in set (0.00 sec) mysql> explain SELECT `id` FROM `sys_test` force index(idx_create_time) WHERE `id` <= '300000' AND `create_time` >= '2015-06-17 07:41:48' ORDER BY `id` desc LIMIIT 200; +----+-------------+---------+-------+-----------------+-----------------+---------+------+-------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+-----------------+-----------------+---------+------+-------+------------------------------------------+ | 1 | SIMPLE | sys_msg | range | idx_create_time | idx_create_time | 6 | NULL | 76916 | Using where; Using index; Using filesort | +----+-------------+---------+-------+-----------------+-----------------+---------+------+-------+------------------------------------------+ 1 row in set (0.00 sec)mysql的优化器不出意外的还是选择id主键列进行查询。从真实的测试时间来看。走create_time 索引的执行时间更短，要是数据量非常大，效果会更加明显（虚拟机性能有限，执行低数据量测试了）
从执行计划来看，原SQL时走主键，因为SQL中还有对id列的排序，一般情况下走id列会更好一点。但是为什么走time索引会更快呢，从第二个执行计划中可以看出端倪，在Extra中，使用了Using index。可以看出，使用了覆盖索引。
innodb存储引擎支持覆盖索引（covering index ，或称为索引覆盖），即从辅助索引中就可以得到查询的记录而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远远小于聚集索引，因此可以减少大量IO操作
可以再来了解一下mysql索引的基本知识： 聚集索引（主键索引） innodb存储引擎是索引组织表，即表中的数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据 聚集索引的叶子节点称为数据页，数据页，数据页！重要的事说三遍。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。 辅助索引（二级索引） 非主键索引 叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值
由于检索数据时，总是先获取到书签值(主键值)，再返回查询，因此辅助索引也被称之为二级索引
覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据有以下一些优点: 1、索引项通常比记录要小，所以MySQL访问更少的数据 2、索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O 3、大多数据引擎能更好的缓存索引，比如MyISAM只缓存索引 4、覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了
限制： 1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值 2、Hash 和full-text索引不存储值，因此MySQL只能使用B-TREE 3、 不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引 5、如果要使用覆盖索引，一定要注意SELECT 列表值取出需要的列，不可以是SELECT *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降，不能为了利用覆盖索引而这么做
辅助索引的性能是很高的，在日常的业务开发或者是运维中，能够高效的使用它是一种运维技巧。通过一次真实场景的运维让我更进一步了解此功能的特性。
