《MySQL高级篇》八、索引优化与查询优化（三）-阿里云开发者社区

5. 排序优化

5.1 排序优化

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？

在 MySQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序。

Index 排序中，索引可以保证数据的有序性，就不需要再进行排序，效率更更高。

FileSort 排序则一般在内存中进行排序，占用 CPU 较多。如果待排序的结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率低。

优化建议:

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。

无法使用 Index 时，需要对 FileSort 方式进行调优。

5.2 测试

执先案例前，调用存储过程删除student和class表上的索引。只留主键：

call proc_drop_index('atguigudb2','student');
call proc_drop_index('atguigudb2','class');

以下是否能使用索引，能否去掉 using filesort

过程一：

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

过程二：

1. 创建索引，但是不加limit限制，索引失效

CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);
#不限制,索引失效
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

我们不是建立了索引嘛，为啥显示没有使用呢？这是优化器通过计算发现，这里需要回表的数据量特别大，使用索引的性能代价反而比不上不用索引的。

2. 假如我们最后只查询二级索引中有的字段，观察结果：

# 会使用索引 （覆盖索引）
EXPLAIN  SELECT SQL_NO_CACHE age,classid,name,id FROM student ORDER BY age,classid;

3. 假如我们限制排序返回的结果数量，观察结果：

#增加limit过滤条件，使用上索引了。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

过程三：order by 时顺序错误，索引失效

#创建索引age,classid,stuno
CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno); 
#以下哪些索引失效?
# 失效
EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;
# 失效
EXPLAIN  SELECT * FROM student ORDER BY classid,NAME LIMIT 10;  
# 可以
EXPLAIN  SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10; 
# 可以
EXPLAIN  SELECT * FROM student ORDER BY age,classid LIMIT 10;
# 可以
EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;

过程四：order by 时规则不一致, 索引失效（顺序错，不索引；方向反，不索引）

# 失效
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;
# 失效
EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;
# 失效
EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; 
# 可以
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

过程五：无过滤，不索引

# 可以
EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;
# 可以
EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid,NAME; 
# 失效
EXPLAIN  SELECT * FROM student WHERE classid=45 ORDER BY age;
# 可以
EXPLAIN  SELECT * FROM student WHERE classid=45 ORDER BY age LIMIT 10;
CREATE INDEX idx_cid ON student(classid);
# 可以
EXPLAIN  SELECT * FROM student WHERE classid=45 ORDER BY age;

小结：

INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀 - ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果 WHERE 使用索引的最左前缀定义为常量，则 order by 能使用索引 
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */ 
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

5.3 案例实战

下面我们通过一个案例来实战filesort和index两种排序。对ORDER BY子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序。

场景:查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序

执行案例前先清除student上的索引，只留主键：

DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid_stuno ON student;
DROP INDEX idx_age_classid_name ON student;
#或者
call proc_drop_index('atguigudb2','student');

测试以下的查询，此时显然使用的是filesort进行排序

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY

结论：type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort，也是最坏的情况。优化是必须的。

方案一：为了去掉 filesort 我们可以创建特定索引

# 创建新索引
CREATE INDEX idx_age_name ON student(age,NAME);
# 进行测试，可以看到已经使用了索引，虽然仅仅使用到了age这个字段
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY NAME ;

方案二：尽量让 where 的过滤条件和排序使用上索引

DROP INDEX idx_age_name ON student;
# 建立三个字段的索引
CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME);
# 进行测试
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

此时又使用了filesort，这是为什么呢？这是因为此时filesort的性能更高。不信你可以对比执行下（0.03sec和0.00sec），看看时间的区别。结果竟然有 filesort 的 sql 运行速度，超过了已经优化掉 filesort的 sql，而且快了很多，几乎一瞬间就出现了结果。看来优化器做的工作真的特别灵活

原因：所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。相对的 stuno < 10100 这个条件，如果没有用到索引的话，要对几万条数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论:

两个索引同时存在，mysql 自动选择最优的方案。（对于这个例子，mysql 选择 idx_age_stuno_name）。但是，随着数据量的变化，选择的索引也会随之变化的。

当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

思考：这里我们使用如下索引，是否可行? (可行)

DROP INDEX idx_age_stuno_name ON student;
CREATE INDEX idx_age_stuno ON student(age,stuno);

5.4 filesort 算法：双路排序和单路排序

排序的字段若不在索引列上，则 filesort 会有两种算法：双路排序和单路排序

1. 双路排序（慢）

MySQL4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行两次扫描，众所周知，IO 是很耗时的，所以在 MySQL4.1 之后，出现了第二种改进的算法，就是单路排序。

2. 单路排序（快）

从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

结论及引申出的问题

由于单路是后出的，总体而言好过双路

但是用单路有问题

在 sort_buffer 中，单路比多路要多占用很多空间，因为单路是把所有字段都取出，所以可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 temp 文件，多路合并），排完再取 sort_buffer 容量大小，再排…从而多次I/O。

单路本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

优化策略

尝试提高 sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M - 8M 之间调整。MySQL5.7，InnoDB 存储引擎默认值都是 1048576 字节，1MB。

尝试提高 max_length_for_sort_data

提高这个参数，会增加改进算法的概率。

SHOW VARIABLES LIKE'%max_length_for_sort_data%';

但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。如果需要返回的列的总长度大于 max_length_for_sort_data，使用双路算法，否则使用单路算法。1024-8192字节之间调整。

Order by 时 select 是一个大忌。最好只Query需要的字段。*

当 Query 的字段大小综合小于 max_length_for_sort_data，而且排序字段不是 TEXT|BLOG 类型时，会改进后的算法——单路排序，否则用老算法——多路排序。

两种算法的数据都有可能超出 sort_buffer_size 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size

6. GROUP BY优化

group by 使用索引的原则几乎跟 order by 一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。

group by 先排序再分组，遵照索引建的最佳左前缀法则

当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置

where 效率高于 having，能写在 where 限定的条件就不要写在 having 中了

减少使用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct 这些语句较为耗费 CPU，数据库的 CPU 资源是极其宝贵的。

包含了 order by、group by、distinct 这些查询的语句，where 条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢。

7. 优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见有非常头疼的问题就是 limit 2000000,10，此时需要 MySQL 排序前 2000010 记录，仅仅返回 2000000-2000010 的记录，其他记录丢弃，查询排序的代价非常大。

EXPLAIN SELECT * FROM student LIMIT 2000000,10;

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;

优化思路二

该方案适用于主键自增的表，可以把 Limit 查询转换成某个位置的查询。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

8. 优先考虑覆盖索引

8.1 什么是覆盖索引？

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二：非聚簇复合索引的一种形式，它包括在查询里的 SELECT、JOIN 和 WHERE 子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是，索引列+主键包含 SELECT 到 FROM 之间查询的列。

举例一：

#删除之前的索引
DROP INDEX idx_age_stuno ON student;
CREATE INDEX idx_age_name ON student (age,NAME);
EXPLAIN SELECT * FROM student WHERE age <> 20;

EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;

注意：前面我们提到如果使用上<>就不会使用上索引了并不是绝对的。比如上面这条SQL就用上了！！！Attention！我们讲解的关于索引失效以及索引优化都是根据效率来决定的。对于二级索引来说：查询时间 = 二级索引计算时间 + 回表查询时间，由于我们使用的是覆盖索引，回表查询时间 = 0，索引优化器考虑到这一点就使用上二级索引了~

举例二：

EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc';

同上，由于也使用了覆盖索引，最终SQL执行也正常使用上了索引~

8.2 覆盖索引的利弊

好处:

1. 避免Innodb表进行索引的二次查询（回表）

Innodb 是以聚集索引的顺序来存储的，对于 Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。

在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了 IO 操作，提升了查询效率。

2. 可以把随机 IO 变成顺序 IO 加快查询效率

由于覆盖索引是按键值的顺序存储的，对于 I/O 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 I/O 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 I/O 转变成索引查找的顺序 I/O。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

弊端:

索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务 DBA，或者称为业务数据架构师的工作。

9. 如何给字符串添加索引

有一张教师表，表定义如下：

create table teacher(
    ID bigint unsigned primary key,
    email varchar(64),
    ...
)engine=innodb;

讲师要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句:

mysql> select col1, col2 from teacher where email='xxx';

如果 email 这个字段上没有索引，那么这个语句就只能做 全表扫描 。

9.1 前缀索引

MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

mysql> alter table teacher add index index1(email); 
#或
mysql> alter table teacher add index index2(email(6));

这两种不同的定义在数据结构和存储上有什么区别呢?下图就是这两个索引的示意图。

以及

如果使用的是 index1 （即 email 整个字符串的索引结构），执行顺序是这样的:

从 index1 索引树找到满足索引值是’ zhangssxyz@xxx.com ’的这条记录，取得 ID2 的值;

到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集;

取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足email=’ zhangssxyz@xxx.com ’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：

从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1;

到主键上查到主键值是 ID1 的行，判断出 email 的值不是’ zhangssxyz@xxx.com ’，这行记录丢弃;

取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集;

重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

《MySQL高级篇》八、索引优化与查询优化（三）

5. 排序优化

5.1 排序优化

5.2 测试

5.3 案例实战

5.4 filesort 算法：双路排序和单路排序

6. GROUP BY优化

7. 优化分页查询

8. 优先考虑覆盖索引

8.1 什么是覆盖索引？

8.2 覆盖索引的利弊

9. 如何给字符串添加索引

9.1 前缀索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像