1. 详细说明

是优化慢SQL之前常用的语句。 它可以分析具体的查询计划，让我们有目的地进行优化。 这一部分主要是让大家了解查询结果的每一列是做什么用的。 我们先简单看一下各栏的作用：

列表

描述

ID

在大型查询语句中，每个关键字对应一个唯一的id。在连接查询中，记录的id值是相同的； 在具有多个关键字的查询中，查询优化器可能会优化子查询，使得多条记录的id值相同。

查询类型

桌子

表名

匹配分区信息

类型

单表访问方式

可能的指标

钥匙

实际使用的索引

实际使用的索引长度

参考

使用索引列等价查询时，将与索引列匹配的对象信息进行等价。

行

估计要读取的记录数

对于预计需要读取的记录，按搜索条件过滤后剩余记录的百分比。在单表查询中没有意义。 在连接表查询中，您可以计算驱动表完成查询后需要在驱动表上执行多少次查询。

额外的

补充笔记

这些列中的大部分已经在描述信息中解释得足够清楚了。 下面我们主要详细介绍一些必要的栏目：

1.1 .2 类型1.3 参考

当访问方法为 const、ref、、和其中之一时，ref 列显示与索引列等效的内容：

1.4. 基于访问类型优化的优化考虑

在上一篇文章中，我们已经详细介绍了语句中的访问类型（type）。 如果查询的访问类型不是我们所期望的，那么最简单、最直接的解决方案就是在搜索条件列中添加适当的索引。

优化减少扫描线数

有些情况下，单纯添加索引并不能解决问题，比如执行以下SQL：

sql复制代码select name, count(name) from specific_table group by key1;

这条SQL执行后，可能只返回几行数据，但由于COUNT聚合函数的原因，需要扫描的数据可能是几千行，具体取决于表中的数据总量。 对于这种扫描大量数据但只返回几行的情况，通常可以通过添加单独的汇总表来优化。 当然，这需要在应用层添加相应的逻辑来维护汇总表中的数据。

此外，您还可以通过重写复杂查询来进行优化。 下面我们介绍一下重写查询时需要考虑的方向：

一个复杂查询还是多个简单查询？

这是一个值得思考的问题。 将复杂查询拆分为多个简单查询，以尽可能减少数据库的工作，并将一些处理逻辑移至应用层进行处理。 由于 MySQL 处理简单查询的效率非常高，因此这样做通常可以提高效率。

分割

实际工作中，在对数据量较大的数据库表进行结转（或删除）时，通常采用分段的方法将大查询划分为小查询。 每个查询的功能是相同的，但操作的数据量不同。 每个小查询执行完后，大查询的任务就完成了。

一次性结转大量数据可能会锁定大量数据，占用整个事务日志，耗尽系统资源，并阻塞许多小查询。 为了避免这种情况，通常一次数据转发任务只操作10000条。 这样对服务器的影响最小，而且每次传输完成后，可以暂停一段时间，然后再执行下一个任务。 这样可以将压力分散到更长的时间内，大大减少对服务器的影响。 并减少持有锁的时间。

优化连接查询

阿里巴巴开发手册提到，查询联表时，联表数量不能超过3个。 如果联表太多，我们需要将其拆分为多个查询或多个单表查询（单表查询的缓存效率会更高）。 查询分解后，查询之间的锁竞争将会减少。 另外，查询联表时需要注意以下两点：

IN() 条件和 OR 条件

一般来说，我们认为IN()完全等价于多个OR条件，但是在MySQL中两者是有区别的。 MySQL在处理IN()条件时，会先对列表中的数据进行排序，然后使用二分查找的方式判断列表中的值是否满足条件。 这是一个时间复杂度为 O(logn) 的操作。 如果等价转换为OR查询，其时间复杂度为O(n)，因此当IN()条件中有大量值时，MySQL处理速度会更快。

查询时索引是否无效、排序时索引是否无效、索引列是否不为空。 优化

当需要 Min() 和 Max() 操作时，索引列不为 null 可以提高它们的效率。 例如，要查找某一列的最小值，只需查询对应的B-Tree索引的最左边的记录即可。 查询优化器会将此表达式视为常量，并且可以在结果的 Extra 列中找到“away”。

重复和冗余索引

重复索引是指在同一列上以相同的顺序创建相同类型的索引，如以下SQL所示：

sql复制代码create table specific_table (
    id int not null primary key,
    unique key(id)
)engine=InnoDB;

它在 id 列上创建了两个相同的索引，并且需要删除唯一索引。

冗余索引通常发生在向表添加新索引时，例如向现有索引添加索引（，）。 这就是冗余索引出现的地方，因为第二个联合索引可以起到与单列相同的作用。 索引也有同样的效果。

大多数情况下，不需要冗余索引，我们应该尝试扩展现有索引而不是创建新索引。

有索引合并吗？

在大多数情况下，在多个列上独立创建多个单列索引并不能提高 MySQL 查询性能。

MySQL中有一种“索引合并”策略，可以利用表中的多个单列索引来定位指定的数据行，并合并扫描结果。 索引合并策略有时很好，但更多时候则说明表中的索引建得不好：

一般来说，我们需要考虑重建索引或者使用UNION重写查询。 另外，可以通过修改参数来关闭索引合并功能，如下SQL所示：

sql复制代码SELECT @@optimizer_switch;
-- 改成 index_merge=off 
set optimizer_switch = 'index_merge=off, ...';

您还可以使用 INDEX 语法告诉优化器忽略某些索引，从而阻止优化器使用包含该索引的索引合并执行计划：

sql复制代码select * from specific_table ignore index(index_name)
where column_name = #{value};

除了发生索引合并时考虑忽略索引外，当执行查询时无法形成合适的扫描间隔且无法达到减少扫描记录数的目的时，还需要考虑忽略索引并使用全表扫描取得成就。

下面我们介绍三种类型的索引合并，以便大家对索引合并有一个更完整的认识：它们是索引合并、Union索引合并和Sort-Union索引合并。

索引合并

让我们看一下下面的查询：

sql复制代码select * from specific_table where key1 = 'a' and key2 = 'b';

我们都知道，当索引列值相同时，二级索引记录按照主键值的大小进行排序。 那么key1过滤出来的主键值和key2过滤出来的主键值就可以进行交集了。 ，然后根据结果执行退表操作。 这比返回分别为 key1 和 key2 过滤掉的主键值要便宜。 在这种情况下，使用索引合并策略。

联合索引合并

让我们看一下下面的查询：

sql复制代码select * from specific_table where key1 = 'a' or key2 = 'b';

将key1过滤出的主键值和key2过滤出的主键值取并集，然后根据结果做返表操作。 这种方法称为联合索引合并，可以与直接进行全表扫描进行比较。 开销应该很低。 需要注意的是，Union索引合并要求二级索引过滤出来的主键值是有序的。 如果主键值乱序，需要考虑Sort-Union索引合并。

Sort-Union索引合并

有如下查询：

sql复制代码select * from specific_table where key1 < 'a' or key2 > 'b';

我们将上面的查询条件改为范围查询条件。 现在各个索引过滤出来的主键值都是乱序的，所以无法使用Union索引合并。 Sort-Union索引合并是在Union索引合并的基础上增加的。 排序操作：将key1过滤出的主键值和key2过滤出的主键值进行排序，这样就可以继续使用Union索引进行合并。

优化 COUNT()

当我们需要对带值的结果进行统计时，我们需要在COUNT()条件中指定列名或COUNT(0)； 当我们需要统计所有行时，我们需要指定COUNT(*)，它会忽略所有列，直接统计所有行。 了解了这两点之后，我们在做数据统计的时候就可以更加清晰的传达我们的意图。

一般来说，COUNT()查询需要扫描大量数据行才能获得准确的结果，因此很难优化。 如果业务场景不需要完全准确，我们可以使用预估行数来代替； 或者我们可以去掉查询条件中的一些约束并删除，以避免排序操作。 这些做法可以提高统计查询性能。

优化 UNION 查询

当我们使用UNION查询时，如果不需要消除重复行，就必须使用UNION ALL，因为如果没有ALL关键字，MySQL会将其添加到临时表中，这样就会对数据进行去重，成本比较高。 另外，我们可以对每个查询应用WHERE、LIMIT和ORDER BY语句，这使得MySQL能够更好地优化它们。

优化

在分页查询中，会导致MySQL扫描大量不必要的行，然后丢弃它们。 例如，表达式 LIMIT 1000, 20 将查询 1020 条数据，然后丢弃前 1000 条数据。 这是非常昂贵的。

我们可以使用书签来记录上次读取数据的“位置”，以便下次查询可以直接从该位置开始扫描，从而避免使用它。 比如每页显示20条数据，我们记录当前页的数据ID值为200，那么当我们查看下一页的数据时，查询SQL如下：

sql复制代码select * from specific_table
where id <= 180
limit 20;

然而，这种情况也有缺点。 不能指定查询的页码。 比如我想查看第5页的数据，我们无法计算出对应页面的具体ID值范围。 除非我们能保证ID值是单调递增的，并且没有数据被删除，这样的话，ID值是连续的，我们很容易算出第5页数据的ID值是从120开始的。这样做的好处是无论页面向后翻多远，它都表现得很好。

使用WITH优化GROUP BY

我们通常使用GROUP BY来进行分组聚合查询。 如果我们想对分组后的结果再次求和，可以使用WITH操作，但更好的方法是将WITH处理带到应用层。

桌子

如果我们删除大量数据，或者插入数据时，没有按照主键升序插入，很可能会产生大量的内存碎片，影响数据查询的效率。 这是因为当数据被删除时，MySQL并不会立即清除它们并整理空间，而是将它们标记为删除。 可以通过TABLE来组织空间，减少内存碎片。

引擎不支持TABLE操作，会提示如下信息：

sql复制代码OPTIMIZE TABLE specific_table;
-- Table does not support optimize, doing recreate + analyze instead

我们可以通过 ALTER 命令重构表达式来达到上述目的，而不需要做任何操作：

sql复制代码alter table specific_table engine=InnoDB;

执行完成后，我们通过以下SQL查看执行状态。 如果该列为0，则说明我们的空间碎片整理成功。

sql复制代码show table status from specific_db like specific_table;

然而，在大多数情况下，这不是必需的。

查找并修复损坏的表

索引可能因硬件问题、MySQL本身或操作系统问题而损坏。 当然，这种问题是非常罕见的。 我们可以通过下面的SQL来检查大部分表和索引的错误：

sql复制代码check table specific_table;

如果发现异常，可以通过以下SQL修复：

sql复制代码repair table specific_table;
-- 如果存储引擎不支持上述操作的话，也可通过表重建来完成
alter table specific_table engine=InnoDB;