MySQL 查询优化

MySQL版本：

建表语句：

为用户表建立索引：

为用户表建立索引：

为代理商建立索引：

用户表索引列表：

代理商索引列表：

1. ORDER BY 优化

MySLQ官方文档给出了能够使用索引加速排序以及无法通过使用索引加速排序的场景案例，下面来一一列举。

1.1 使用索引加速排序

1.1.1 案例1

使用复合索引（多列索引）中的一个或多个列进行排序。要遵循最左前缀匹配原则。如果是使用复合索引中的多个列进行排序，这些列排序的顺序要么都是升序，要么都是降序，否则无法使用索引加速排序操作。

从执行计划的结果来看，出现了，这就是通常说的排序。

排序并不代表是通过磁盘文件进行的排序，而只是说数据库服务器需要对数据进行一次额外的排序。凡是不是通过索引直接返回排序结果的排序，都叫排序。

排序算法首先是尝试将取出的数据一次性加载到内存中进行排序，排序最大能使用的内存空间大小是由系统变量决定的。如果内存空间无法容下所有取出的数据，那么排序就会分解成多个更小的排序，然后每次只排序其中一小部分的数据，并将每次排序的结果存储到磁盘临时文件中，最后再将临时文件中的数据进行一次排序和合并结果输出。

每将各个有序的小数据块合并成一个有序的结果集就会增加的数值。因此，如果数据库服务器的的数值过大，可以考虑适当增加的数值以加速排序的操作。但是需要注意，系统变量配置的内存空间是每个线程独占的，不宜设置过大，应该综合考虑数据库连接数量和服务器内存的总大小。

# 查看系统 sort_merge_passes 状态的值

# 查看系统 sort_buffer_size 变量的值

# 设置系统 sort_buffer_size 变量的值, 1M

使用覆盖索引的方式改写上面的查询语句：

从执行计划的结果来看，出现了，已经没有了。

1.1.2 案例2

使用复合索引（多列索引）中的一部分列做等值查询，一部列做排序操作。要遵循最左前缀匹配原则。如果是使用复合索引中的多个列进行排序，这些列排序的顺序要么都是升序，要么都是降序，否则无法使用索引加速排序操作。

1.1.3 案例3

使用复合索引（多列索引）中的一部分列做比较值查询，一部列做排序操作。要遵循最左前缀匹配原则。如果是使用复合索引中的多个列进行排序，这些列排序的顺序要么都是升序，要么都是降序，否则无法使用索引加速排序操作。

比较值的场景下，优化器不一定会选择采用索引的方式，主要还得看表数据量的大小以及MySQL估算查询要扫描的行数来决定是否选择走索引。

1.2 无法使用索引加速排序

1.2.1 案例1

使用多个不同的单列索引进行排序。

为了确保查询走索引，这里采用了覆盖索引的方式。

1.2.2 案例2

使用复合索引（多列索引）的多个列进行排序时，不遵循最左前缀匹配原则。

本文没有建立三个列的复合索引，这里采用覆盖索引的方式来实现，达到的效果是一样的。

1.2.3 案例3

使用复合索引（多列索引）的多个列进行排序时，同时存在升序和降序的混合排序。

为了确保查询走索引，这里采用了覆盖索引的方式。

2. LIMIT 优化

2.1 案例

查询代理商ID为2下面的所有用户信息，每页展示20条数据，起始值从200万行开始。为保证优化前和优化后返回相同的数据列表，这里增加了根据主键排序的条件：

在一张数据量为2000万行的表中，真实的查询耗时为。

2.2 优化

思路：采用覆盖索引的方式先取出已经通过索引加速排序好的分页数据的行记录ID，然后再通过行记录ID关联回表查询所需的所有数据。这样可以减少全表扫描的行数，提升查询效率。

在一张数据量为2000万行的表中，真实的查询耗时为。优化后的查询时间是毫秒级别的，速度提升了约180倍。

3. IN 优化

3.1 案例

查询二级代理商下的所有用户信息，因测试数据量过大，此处只查询前20条数据。为保证优化前和优化后返回相同的数据列表，这里增加了根据主键排序的条件：

代理商表6行数据，用户表2000万行数据，真实的查询耗时为，极慢，无法忍受。

通过trace分析优化器是如何选择执行计划的：

可以看到，MySQL优化器认为使用的方式能够更高效率的查找到数据行，因此内部将查询语句改写成了（半连接）。也由此可见，在MySQL中，半连接优化的效率也未必高。

3.2 优化

思路：查询的性能很糟糕，建议使用来等效的改写查询以获得更好的查询性能。

代理商表6行数据，用户表2000万行数据，真实的查询耗时为，提升效率是显而易见的。

3.3 案例

上面是涉及关联子查询的情况，如果后面是常量而非子查询的情况，效率是很客观的：

在一张数据量为2000万行的表中，真实的查询耗时基本为（没有查询缓存）。