MySQL索引优化

一、EXPLAIN

首先我们要先明白怎么去判断一条查询语句的索引使用情况，这样才能够判断查询语句是否有效利用索引。为了达到这一目的，我们使用Mysql内置的EXPLAIN命令来查看查询语句的状态。

根据该文章可以对EXPLAIN做一点简单的了解：https://www.cnblogs.com/tufujie/p/9413852.html

看完上述文章后，此处我们再强调一下EXPLANIN中的Extra字段（额外信息字段）：

在后续中我们使用以下数据库表作为示例：

DROP TABLE IF EXISTS `data`;
CREATE TABLE `data` (
  `ID` bigint(10) NOT NULL AUTO_INCREMENT,
  `LICENSEPLATENO` varchar(20) NOT NULL
  `GPS_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `LONGITUDE` double(15,7) NOT NULL,
  `LATITUDE` double(15,7) NOT NULL,
  PRIMARY KEY (`ID`),  /*索引1*/
  KEY `GPS_TIME` (`GPS_TIME`) USING BTREE, /*索引2*/
  KEY `GPS_TIME_2` (`GPS_TIME`,`LONGITUDE`,`LATITUDE`) USING BTREE, /*索引3*/
  KEY `LONGITUDE` (`LONGITUDE`,`LATITUDE`) USING BTREE /*索引4*/
) ENGINE=MyISAM CHARSET=utf8;

该列包含MySQL解决查询的详细信息,有以下几种情况：

• Using where：意味着使用where进行精准匹配，具体有没有使用索引需要看同行中key列的值。

EXPLAIN SELECT * FROM data WHERE LICENSEPLATENO = '111';

• Using index condition：在5.6版本后加入的新特性（Index Condition Pushdown）会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

EXPLAIN SELECT * from data WHERE LONGITUDE BETWEEN 11 AND 13;

• Using index：覆盖索引，从表中仅使用索引树中的信息就能获取查询语句的列的信息, 而不必进行其他额外查找（seek）去读取实际的行记录。当查询的列是单个索引的部分的列时, 可以使用此策略。

EXPLAIN SELECT LONGITUDE， LATITUDE from data ORDER BY LONGITUDE;

• NULL：不一定是没有使用索引，出现NULL的情况时我们可以判断同行中的 key是否存在值，若存在，则走普通索引。
• Using union：在合并多个索引为一个多列索引时出现

EXPLAIN SELECT * FROM data WHERE GPS_TIME = '2017-02-01 15:21:37' OR LONGITUDE = 1;

• Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by。一般在多个表都涉及到排序字段才会引起。
• Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”。filesort使用临时表进行排序。具体使用内存临时表还是使用磁盘临时表取决于tmp_table_size的大小是否大于建立临时表所需的大小。对大数据集进行filesort往往效果不佳，因为在频繁使用的排序中我们尽量使用覆盖索引加快查询速率。

explain select * from data order by LICENSEPLATENO;

• Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
• Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
• Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
• No tables used：Query语句中使用from dual 或不含任何from子句

-- explain select now() from dual;

二、索引类型

2.1 B-Tree

要先优化索引我们还需要了解索引一共有哪些类型。在Mysql中，索引一般通过B-Tree实现（InnoDB使用B+Tree）。

B-Tree索引适用于全键值、键值范围或键前缀查找（最左前缀）。以上索引针对以下类型的查询有效：

• 全值匹配：和索引中的所有列进行匹配。如索引4中的LONGITUDE,LATITUDE

EXPLAIN SELECT * from data WHERE LONGITUDE = 13 AND LATITUDE = 13 

• 匹配最左前缀：和索引中的前面部分的列进行匹配。如索引4中的LONGITUDE

EXPLAIN SELECT * from data WHERE LONGITUDE = 13;

• 匹配列前缀：只匹配某一列的值的开头部分。使用Like ‘data%’，注意不能匹配’%data’

// 这里我们先把LICENSEPLATENO设置为B-Tree索引
EXPLAIN SELECT * from data WHERE LICENSEPLATENO LIKE '111%'; // 会走索引
EXPLAIN SELECT * from data WHERE LICENSEPLATENO LIKE '%111'; // 不会走索引

• 匹配范围值：使用索引列查找一定范围内的数据。

EXPLAIN SELECT * from data WHERE LONGITUDE BETWEEN 11 AND 13;

• 精准匹配某几列并范围匹配另外一列：可以前面几列索引使用精准匹配，最后一列索引使用范围匹配，反过来则不行（Mysql8.0测试后发现可以调转顺序）。

EXPLAIN SELECT * from data WHERE LATITUDE = 11 AND LONGITUDE BETWEEN 111 AND 113;
EXPLAIN SELECT * from data WHERE LONGITUDE BETWEEN 111 AND 113 AND LATITUDE = 11;

• 只范围索引的查询：查询只需要访问索引，而无须访问数据行，即覆盖索引。

EXPLAIN SELECT LONGITUDE， LATITUDE from data ORDER BY LONGITUDE;

2.2 Hash

除开B-Tree索引外，我们还可以采用Hash索引，但是在Mysql中，只用Memory引擎显式支持Hash索引，根据不同的行的键值对计算出来的哈希码，将该哈希码存储在索引中，并且在哈希表中保存指向每个数据行的指针。采用拉链法进行解决冲突。

由于自身只需存储对应的哈希值，所以索引的结构非常紧凑，并且哈希查找的特点也使得哈希索引查找非常快。

不过缺点也是很明显：

• 只存储了哈希值和行指针，没有存储字段值，因此始终无法避免读取行。
• 没有按照顺序而是使用哈希码进行存储，因此无法用于排序
• 不支持部分索引列匹配查找（哈希码根据整段字符串计算得出），只能够进行精确的等值比较查询。
• 当哈希冲突非常大的时候（选择性(不重复的索引值和数据表的记录总数的比值)小），会显著降低Hash索引的效率。

因为以上这些缺点，哈希索引应用的范围较小，但是查找速率还是很可观的。

2.3 InnoDB中的Hash

尽管我们无法显式在InnoDB中创建Hash索引，但是还是有几个办法能让我们在InnoDB中使用到Hash索引。

**自适应哈希索引：**当InnoDB注意到某些索引值被使用得非常频繁时，会在内存中基于B-Tree索引智商在创建一个哈希索引。

**自定义哈希索引：**在B-Tree的基础上创建一个伪哈希索引。使用哈希值而不是键本身来进行索引查找，我们需要做的就是在查询的WHERE子句中手动指定使用哈希函数（可以选择CRC32()或FNV64()）。对于一些长度很长的字符串来说，使用哈希索引能够有效降低需要的存储长度，并且在哈希后增大选择性。不过我们往往需要在数据插入和更新的时候维护哈希列，可以使用触发器来实现。

三、索引的优化

首先我们来了解索引有哪些优点：

• 大大减少了服务器端需要扫描的数据量
• 帮助服务器避免排序和临时表
• 将随机IO变为顺序IO

2.1 保证独立列

在满足匹配最左前缀的情况下，我们还需要注意Mysql在查询语句中使用独立的索引列才会走索引。独立的列指索引列不能是表达式的一部分，也不能是函数的参数。比如以下查询都是无法使用索引的

EXPLAIN SELECT * FROM data WHERE LONGITUDE + 11 = 19;

EXPLAIN SELECT * FROM data WHERE ABS(LONGITUDE) = 19;

2.2 使用前缀索引

针对于长字符串的索引（对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀匹配），在大部分情况下都没有进行整段比较的必要。此时我们可以使用前缀索引来减少索引空间的占用（每行的字符串节省下来总量还是很客观的）。但不好的地方则是会降低索引的选择性、无法针对前缀索引做ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

在设计前缀索引的长度中，我们需要注意的是要选择足够长的前缀以保证较高的选择性，同时又不能太长。

我们可以参考以下SQL语句进行判断：

SELECT COUNT(DISTINCT LEFT(LICENSEPLATENO, 2))/COUNT(*) AS s2,
COUNT(DISTINCT LEFT(LICENSEPLATENO, 3))/COUNT(*) AS s3,
COUNT(DISTINCT LEFT(LICENSEPLATENO, 4))/COUNT(*) AS s4,
COUNT(DISTINCT LEFT(LICENSEPLATENO, 5))/COUNT(*) AS s5,
COUNT(DISTINCT LEFT(LICENSEPLATENO, 6))/COUNT(*) AS s6,
COUNT(DISTINCT LEFT(LICENSEPLATENO, 7))/COUNT(*) AS s7
FROM data;

从得出的选择性中，当再增加前缀长度，选择性提升的幅度已经很小时，这时候我们就可以大概确定这个前缀长度是适合的。

但是我们仍然需要考虑最坏下的选择性。可能在上述得出的合适长度中，数据分布会有不均匀的状态。比如前缀A有2222个，前缀B只有33个。

我们可以参考以下SQL语句进行判断：

SELECT COUNT(*) AS count, LEFT(LICENSEPLATENO, 3) AS pre FROM data GROUP BY pre ORDER BY count DESC;

如果出现分布不均匀的情况，我们则需要适当增加先前得出的前缀长度，以使得数据的分布差距减少。

最后我们就可以创建前缀索引了：

ALTER TABLE data ADD KEY (LICENSEPLATENO(7))

2.3 多列索引

为了减少索引的空间占用，Mysql支持**“索引合并”。可以使用表上的多个单列索引来定位指定的行，简单来说就是对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)**。比如：

EXPLAIN SELECT * FROM data WHERE GPS_TIME = '2017-02-01 15:21:37' OR LONGITUDE = 1; //索引合并
EXPLAIN SELECT * FROM data WHERE GPS_TIME = '2017-02-01 15:21:37' AND LONGITUDE = 1; //没有索引合并

GPS_TIME与LONGITUDE处于不同的单列索引，但通过索引合并，在一个查询中可以同时使用该两个单列索引。

同一个表的多个索引的范围扫描可以对结果进行合并，合并方式分为三种：union(AND), intersection(OR), 以及它们的组合(先内部intersect然后在外面union)。

官方文档给出了四个例子：

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key=30;
SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

注意不能单独使用并集AND。

索引合并策略是一种优化策略，但实际上很多时候都说明了表上的索引建得很糟糕。

• 当服务器对多个索引做相交操作时，通常意味这需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 由于合并索引的实质是对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。那么当服务器需要对多个索引进行联合操作时（OR），通常需要耗费大量的资源在reduce上。
• 优化器不会将reduce成本计算到“查询成本中”。这导致后续优化中容易忽略该问题。

可以通过参数optimizer_switch关闭索引功能，也可以使用IGNORE INDEX让优化器忽略掉某些索引。

2.4 选择合适的索引列顺序

在B-Tree索引中，要求查询的条件满足完全匹配或者最左前缀匹配。那么此时索引列顺序就非常重要了。

在不需要考虑排序和分组的情况下，我们可以将运行频率最高的和选择性最高的列放到索引最前列。

在程序运行过程中我们可以使用诸如pt-query-digest这样的工具来获取查询的报告，从而针对其中最差的查询来进行优化。如果没有类似的具体查询来运行，建议还是将运行频率最高的和选择性最高的列放到索引最前列。具体是选择性优先还是运行频率优先，在相对于其他列没有较为突出的运行频率时，还是以选择性优先为主。

上述做法在多数程序上是有用的，但是可能存在特殊情况会影响整个应用的性能。

比如当一个列在程序中尽管选择性较高，但是若该列中存在一个特殊值，占据着整体数据中大部分的行数，这个时候当查询该特殊值时，索引的意义便不大了。最好的办法是在设计数据库阶段，避免出现某一个重要的列中大量出现某一个特殊值，这种情况会严重拖慢程序的运行速率，特别是在该列运行频率颇高的情况下。

2.5 （非）聚簇索引

Mysql中的InnoDB引擎使用的索引即为聚簇索引。聚簇索引使用B-Tree的数据结构进行数据存储，特殊的是在B-Tree的叶子节点中包含了行的全部数据（另外还有主键值、事务ID、用于事务和MVCC的回滚指针）。

InnoDB默认会构建聚簇索引来存储数据，索引列的选择规则如下：如果没有定义主键，InnoDB会选择一个非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引的一级索引存储了行的所有数据，次级索引存储了其主键值。这样在更新行信息时只需要移动一级索引的叶子节点位置。

聚集的数据有以下一些优点：

• 可以将相关数据保存在一起。读取相关数据时在磁盘IO中采用顺序读取。这样就不会导致多次的随机读取。
• 访问速度更快。将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比从非聚簇索引中获取数据要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

但聚簇索引也有缺点：

• 聚簇索引通过将相关数据保存在一起，从而使大量的随机IO转化为数次顺序IO，但如果将全部数据放在内存中，随机IO转化为顺序IO所获得的收益就很小了。
• 插入速度严重依赖于插入顺序。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置（在聚簇索引中叶子节点存储的是整个行数据）。
• 页分裂会导致表占用更多的磁盘空间。
• 可能会导致全表扫描便慢，尤其是行比较稀疏，或者由于也分裂导致数据存储不连续的时候。（全表扫描更着重于整体的顺序性，聚簇索引更着重于相关信息的顺序性）
• 针对二级索引需要进行两次B-Tree查找。

MyISAM中的非聚簇索引同样是使用B-Tree进行数据存储，与聚簇索引不一样的是，MyISAM将数据按照数据插入的顺序顺序存储在磁盘中，并且在行的旁边显示行号，从0开始递增，MyISAM在聚簇索引的叶子节点中存储该行值。当MyISAM使用静态表时，因为行是定长的，所以M有ISAM可以从表的开头跳过所需的字节找到需要的行。当MyISAM使用动态表时，则需进行顺序查找。关于MyISAM的三种存储格式可以参考：https://blog.youkuaiyun.com/qq_34556414/article/details/78425388

另外一个不同点是，非聚簇索引无论构建几个索引，其叶子节点存储的都是相关数据的行数。

如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关。最好避免随机的聚簇索引，这会使得数据没有任何聚集特征。并且随机的主键会导致在插入过程中需要经常为新的行寻找合适的位置（一般在已有数据的中间位置），这会导致InnoDB不得不频繁做页分裂操作，同时由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。在运行一段时间后，也许需要做一个OPTIMIZE TABLE来重建表并优化列的填充。

2.6 覆盖索引

从表中仅使用索引树中的信息就能获取查询语句的列的信息, 而不必进行其他额外查找（seek）去读取实际的行记录。当查询的列是单个索引的部分的列时, 可以使用此策略。

如果查询只需扫描索引而不需回表，可以带来以下好处：

• 索引条目一般远小于数据行的大小，若只读取索引，则可以极大地减少数据访问量。
• 由于B-Tree的特殊结构，索引是按照列值顺序存储的，因此在针对该列的顺序查找与范围查找中，顺序读取会比随机读取快很多，尤其是在I/O密集型的应用中。
• 一些存储引擎在内存中至缓存索引，而数据依赖操作系统进行缓存。因此访问数据需要一次系统调用，这可能会导致严重的性能问题。
• 在聚簇索引的查询中，如果主键值可以通过覆盖索引进行查询，那么就可以避免对主键索引的二次查询。

需要注意的是：

• 使用覆盖索引时，查找的列需要被索引全部覆盖。
• 在索引中只能做最左前缀匹配的LIKE比较。

**延迟关联：**利用子查询的覆盖索引得到主键值，再利用主键值得到需要查找的数据。

延迟关联适用于子查询后只返回很少的数据集，但返回的数据集亦不能太小。这个需要根据数据库的情况进行测试再决定是否使用延迟关联。

2.7 索引扫描

前面我们在选择合适的索引列顺序处提到过：在不需要考虑排序和分组的情况下，我们可以将运行频率最高的和选择性最高的列放到索引最前列。

那么使用索引扫描便可以将排序和分组的情况考虑进去。

如果EXPLAIN出来的type列的值为index，则说明MySQL使用了索引扫描来排序。

如果排序的列采用的正好是索引列，那么查询时只需从一条索引记录移动到紧接着的下一条索引记录，但如果索引不能够覆盖查询所需的所有列，那就不得不没扫描一条索引记录就会表查询一次，这样反而可能比顺序地全表扫描慢，因此我们一般将覆盖索引与索引扫描结合起来进行排序。

2.8 压缩索引

再MyISAM中支持使用前缀压缩来减少索引的大小，从而让更多的索引可以被放入到内存中。默认只压缩字符串。

压缩块能获得更小的空间，代价是类如二分查找、倒序查找的操作会变慢。

2.9 小技巧

在设计索引的过程中，我们有跳过前缀索引列使用后缀索引列的需求。那么我们可以使用IN()来实现该功能：

EXPLAIN SELECT * FROM data WHERE LONGITUDE IN (11, 12) AND LATITUDE = 12;

这样做的缺点是若LONGITUDE需要所有的数据项，则需要把所有可能的值列出来，因此这只能应用在可能值很少的情况下，比如说性别。

但这种技巧不能够滥用，因为每额外增加一个IN()条件，优化器所需要做的组合都将以指数形式增加，最终会极大地降低查询性能。因此该方法实际上是以时间换空间。

对于范围条件查询，MySQL无法再使用范围列后面的索引列（笔者在Mysql8.0测试是可以使用的），因此若有多个范围条件查询，我们也可能使用IN()条件，将范围条件查询转化为多个等值条件查询。

在排序后查询的过程中，由于随着偏移量量的增加，MySQL都要花费大量时间扫描需要丢弃的数据。因此我们可以使用延迟关联：先通过覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。具体可以参考：https://blog.youkuaiyun.com/fdipzone/article/details/72793837

四、索引、表的维护

维护表有三个主要的目的：

• 找到并修复损坏的表
• 维护准确的索引统计信息
• 减少碎片

4.1 找到并修复损坏的表

检查表损坏（可能是查询返回错误或者奇怪的主键冲突等等）：

CHECK TABLE data;

修复表（有些存储引擎不支持）：

REPAIR TABLE data;

4.2 更新索引统计信息

重新生成统计信息：

ANALYZE TABLE data;

查看索引信息：

SHOW INDEX FROM data;

4.3 减少索引和数据的碎片

数据存储有三种类型的数据碎片：

• 行碎片：指数据行被存储为多个地方的多个片段。InnoDB会移动短小的行并重写到一个片段中，因此不会出现短小的行碎片。
• 行间碎片：指逻辑上顺序的页、或者行在磁盘上不是顺序存储的。
• 剩余空间碎片：指数据页中有大量的空余空间。

数据碎片会导致范围查询、索引覆盖扫描操作需要访问很多碎片下的片段，这样会影响查询的速度。

我们可以通过以下方式进行数据的整理：

• 执行OPTIMIZWE TABLE data
• 导出再重新导入数据
• 通过先删除、再重新创建索引的方式消除索引的碎片化
• 通过不做任何操作的修改表操作来重建表：ALTER TABLE <table> ENGINE=<engine>

五、总结

在索引优化的过程中，我们会经常使用EXPLAIN来判断SQL语句是否能够如我们预料般使用索引来进行数据检索。根据列中的key判断SQL语句会采用的是哪条索引，根据列中的Extra判断查询的方式，根据列中的type判断是否执行索引扫描。

采用哪种索引建立在我们的需求之上。一般我们会因为安全性、事务性的需要而采用InnoDB作为数据库引擎，这也相当于选择了聚簇索引，而聚簇索引的数据存储结构为B+ Tree。若对安全性、事务性并无严格的要求并且无频繁的写操作，那么MyISAM因其更快的查询速度可能更能得到你的青睐。而这样也相当于选择了非聚簇索引，非聚簇索引的数据存储结构为B-Tree。除此之外还有Hash索引，能够显式使用该索引的只有Memory数据库引擎。另外InnoDB为了提高对热点数据的访问速度，通过使用自适应哈希索引在内存中构建Hash索引。在数据量大且选择性小的数据中，可以通过伪哈希索引来分散数据的分布，从而避免拖慢索引性能的情况。

在索引与SQL语句的设计中，我们应该尽量保证SQL语句的简单。保证查询时索引列的独立，避免MySQL的优化器无法进行优化。使用前缀索引减少建立索引所需耗费的空间大小，但是要注意选择性与数据分布是否均匀。多列索引可以合并多个索引，但其性能没有直接建立需要的索引高，一般用于以时间换空间的场景。聚簇索引与非聚簇索引之间的底层实现注定了适用的场景并不完全相同，但相同的是，都不适用于具有频繁的更新操作的系统，因数据更新操作所导致的数据移动所产生的磁盘碎片，会拖慢磁盘IO的检索性能。

在针对查询结果为索引列的查询中，索引覆盖能够更快地得到结果。该特性还能够应用于索引排序上，因为尽管利用索引进行排序能够减少排序所需要的时间，但是查询行数据的回表操作仍然需要进行随机IO，因此若索引排序后需要查询的数据仍为索引列的话，提高性能会很客观。

使用压缩索引能够节省内存空间，但在排序、倒序查找等操作上会消耗更多的时间。

合理使用IN()与延迟关联也可以减少索引的数量与增加查询的性能。

在进行多次更新操作后，数据库表会产生许多碎片，这些也要定时维护。可以使用定时器在夜晚定时重新组织表以得到更佳的索引性能。