58-MySQL索引优化与查询优化-ORDER BY和GROUP

2022-11-03 本文已影响0人紫荆秋雪_文

一、排序优化（ORDER BY）

1、在`WHERE`条件字段上加`索引`，但是为什么在`ORDER BY`字段上还要加`索引`？

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

Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
FileSort排序则一般在内存中进行排序，占用CPU 较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低

2、优化建议

1、SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，需要避免，以提高查询效率。
2、尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引。
3、无法使用Index时，需要对 FileSort 方式进行调优

二、实战

1、删除`student表`和`class表`的索引

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

查看student表和class表的索引

SHOW INDEX FROM student;
SHOW INDEX FROM class;

2、ORDER BY中没有索引

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

EXPLAIN 使用filesort.png

3、`ORDER BY`中时不添加 `LIMIT`，索引失效

3.1、添加索引

CREATE INDEX idx_age_classid_name ON student (age, classid, name);

3.2、`ORDER BY`中时不添加 `LIMIT`

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

EXPLAIN 使用 filesort排序.png

3.3、`ORDER BY`中时不添加 `LIMIT`，但是返回字段为`索引列`时（覆盖索引）

EXPLAIN
SELECT SQL_NO_CACHE age, classid, name, id
FROM student
ORDER BY age, classid;

EXPLAIN 覆盖索引-排序使用到了索引.png

3.4、`ORDER BY`中时添加 `LIMIT`

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

EXPLAIN 使用LIMIT后用到了索引排序.png

3.5、小结

1、通过实战3.2和实战3.3比较得出结论
- 实战3.2查询所有内容（*），即使现在有idx_age_classid_name索引选择可用，但是由于idx_age_classid_name是二级索引，使用idx_age_classid_name索引后还需要回表查询所有列信息，所以优化器选择不适用idx_age_classid_name索引。因为回表成本很高，相较之下，全表扫描效率更高，故不会选择idx_age_classid_name索引
- 实战3.3只查询idx_age_classid_name索引包含和隐藏的字段(id)，不需要额外的回表操作，所以查询优化器最终选择了索引
2、通过实战3.2和实战3.4比较得出结论

由于使用了LIMIT后，查询有效数量，使用idx_age_classid_name索引后及时需要额外的回表操作，但是由于回表数量有限，相比全表扫描成本更低，所以选择使用所以

4、ORDER BY 时顺序错误，索引失效

4.1、索引顺序索引顺序!.png

4.2、不遵守`最前左原则`，索引失效

EXPLAIN
SELECT *
FROM student
ORDER BY classid
LIMIT 10;

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

EXPLAIN 不遵守`最前左原则`，索引失效.png
小结

由于idx_age_classid_name索引字段顺序为age、classid、name，但是上述ORDER BY后的排序字段都没有用到age字段，违反了最前左原则，造成索引失效

4.3、排序字段与索引字段不同，索引失效

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

EXPLAIN image.png
小结

由于idx_age_classid_name索引字段顺序为age、classid、name，但是上述ORDER BY后的排序字段age, classid, stuno由于无法匹配所以没有索引可用

4.4、排序字段部分匹配，使用索引

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

EXPLAIN
SELECT *
FROM student
ORDER BY age
LIMIT 10;

EXPLAIN image.png

5、ORDER BY 时规则不一致, 索引失效（顺序错，不索引；方向反，不索引）

5.1、索引顺序索引顺序!.png

5.2、ORDER BY 排序字段升降序不一致

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

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

EXPLAIN image.png
小结

索引idx_age_classid_name在创建时每个字段都是已升序的方式创建的，而上述实例中排序字段都是有升序又有降序，造成索引失效

5.3、解决ORDER BY 排序字段升降序不一致

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

EXPLAIN 排序字段升降序不一致造成索引失效.png
安装排序顺序创建索引

CREATE INDEX idx_age_classid ON student (age DESC, classid ASC);

索引 image.png
查询分析使用了idx_age_classid索引.png

5.4、ORDER BY 排序字段的升降序与索引相同或相反，索引可用

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

EXPLAIN 排序时反向扫描索引.png

6、无过滤，不索引

6.1、删除索引

CALL proc_drop_index('atguigudb2', 'student');

6.2、无索引

EXPLAIN
SELECT *
FROM student
WHERE age = 45
ORDER BY classid;

EXPLAIN image.png

6.2、为 ORDER BY 字段创建索引

CREATE INDEX idx_cid ON student (classid);

idx_cid 索引.png

6.3、在 WHERE 没有索引的情况下，不会使用 ORDER BY 索引

EXPLAIN
SELECT *
FROM student
WHERE age = 45
ORDER BY classid;

EXPLAIN ORDER BY 索引失效.png

6.4、为WHERE字段创建索引

给age字段创建索引

CREATE INDEX idx_age ON student (age);

EXPLAIN
SELECT *
FROM student
WHERE age = 45
ORDER BY classid;

EXPLAIN image.png
小结

虽然使用了索引idx_age，但是依然使用FILESORT

6.5、为`WHERE字段`和`ORDER BY`创建联合索引

CREATE INDEX idx_age_classid_name ON student (age, classid, name);

EXPLAIN
SELECT *
FROM student
WHERE age = 45
ORDER BY classid;

EXPLAIN image.png
小结

只有为WHERE字段和ORDER BY创建联合索引才能解决FILESORT

6.6、 ORDER BY 字段顺序与索引顺序不匹配，造成`FILESORT`

EXPLAIN
SELECT *
FROM student
WHERE age = 45
ORDER BY name, classid;

EXPLAIN image.png

6.7、 ORDER BY 字段索引和WHERE字段索引分别独立

为classid创建索引

CREATE INDEX idx_cid ON student (classid);

EXPLAIN
SELECT *
FROM student
WHERE classid = 45
ORDER BY age;

使用 LIMIT 情况

EXPLAIN
SELECT *
FROM student
WHERE classid = 45
ORDER BY age
LIMIT 10;

EXPLAIN image.png
小结
- 1、WHERE条件使用到了索引
- 2、ORDER BY没有使用到索引，依然使用filesort

6.8、 ORDER BY 字段有索引和WHERE字段没有索引

删除classid字段上索引

DROP INDEX idx_cid ON student;

EXPLAIN
SELECT *
FROM student
WHERE classid = 45
ORDER BY age;

EXPLAIN image.png
添加 LIMIT

EXPLAIN
SELECT *
FROM student
WHERE classid = 45
ORDER BY age
LIMIT 10;

EXPLAIN image.png

6.9、小结

INDEX a_b_c(a,b,c)

1、order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
2、如果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
3、不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /丢失a索引/
- WHERE a = const ORDER BY c /丢失b索引/

7、测试filesort和index排序

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

7.1、删除索引

CALL proc_drop_index('atguigudb2', 'student');

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

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

查询时间 130ms
EXPLAIN image.png
小结

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

7.3、为了去掉filesort可以创建索引

创建age，name联合索引

CREATE INDEX idx_age_name ON student (age, name);

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

EXPLAIN image.png

7.4、尽量让where的过滤条件和排序使用上索引

创建age，stuno，name联合索引

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;

查询时间：34ms
EXPLAIN image.png
小结
- 发现 Using filesort 依然存在，所以name并没有用到索引，而且type还是range光看字面其实并不美好，原因是stuno 是一个范围过滤，所以索引后面的字段不会再使用索引了。
- 结果竟然是有 filesort 的 SQL 运行速度，超过了已经优化掉 filesort 的 sql ，而且快了很多。
- 所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。相对的 stuno < 101000 这个条件，如果没有用到索引的话，要对几万条数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

7.5、小结

1、两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysql选择idx_age_stuno_name）。但是， 随着数据量的变化，选择的索引也会随之变化的
2、当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然

8、 filesort算法：双路排序和单路排序

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

8.1、双路排序（慢）

MySQL 4.1之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
从磁盘取排序字段，在buffer进行排序，再从 磁盘取其他字段
取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序

8.2、单路排序（快）

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

8.3、结论及引申出的问题

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

8.3.2、单路排序的问题

1、在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建 tmp文件，多路合并），排完再取sort_buffer容量大小，再排. . .从而多次I/O
2、单路本来想省一次I/O操作，反而导致了大量的 I/O操作，反而得不偿失

8.4、优化策略

8.4.1、尝试提高 sort_buffer_size

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

查看命令

SHOW VARIABLES LIKE '%sort_buffer_size%';

image.png

8.4.2、尝试提高 max_length_for_sort_data

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

SHOW VARIABLES LIKE '%max_length_for_sort_data%';

image.png

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

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

当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序
两种算法的数据都有可能超出sort_buffer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size

三、GROUP BY

1、group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
2、group by 先排序再分组，遵照索引建的最佳左前缀法则
3、当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
4、where效率高于having，能写在where限定的条件就不要写在having中了
5、减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
6、包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

58-MySQL索引优化与查询优化-ORDER BY和GROUP

一、排序优化（ORDER BY）

1、在WHERE条件字段上加索引，但是为什么在ORDER BY字段上还要加索引？

2、优化建议

二、实战

1、删除student表和class表的索引

2、ORDER BY中没有索引

3、ORDER BY中时不添加 LIMIT，索引失效

3.1、添加索引

3.2、ORDER BY中时不添加 LIMIT

3.3、ORDER BY中时不添加 LIMIT，但是返回字段为索引列时（覆盖索引）

3.4、ORDER BY中时添加 LIMIT

3.5、小结

4、ORDER BY 时顺序错误，索引失效

4.1、索引顺序 索引顺序!.png

4.2、不遵守最前左原则，索引失效

4.3、排序字段与索引字段不同，索引失效

4.4、排序字段部分匹配，使用索引

5、ORDER BY 时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）

5.1、索引顺序 索引顺序!.png

5.2、ORDER BY 排序字段升降序不一致

5.3、解决ORDER BY 排序字段升降序不一致

5.4、ORDER BY 排序字段的升降序与索引相同或相反，索引可用

6、无过滤，不索引

6.1、删除索引

6.2、无索引

6.2、为 ORDER BY 字段创建索引

6.3、在 WHERE 没有索引的情况下，不会使用 ORDER BY 索引

6.4、为WHERE字段创建索引

6.5、为WHERE字段和ORDER BY创建联合索引

6.6、 ORDER BY 字段顺序与索引顺序不匹配，造成FILESORT

6.7、 ORDER BY 字段索引和WHERE字段索引分别独立

6.8、 ORDER BY 字段有索引和WHERE字段没有索引

6.9、小结

7、测试filesort和index排序

7.1、删除索引

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

7.3、为了去掉filesort可以创建索引

7.4、 尽量让where的过滤条件和排序使用上索引

7.5、小结

8、 filesort算法：双路排序和单路排序

8.1、双路排序 （慢）

8.2、单路排序 （快）

8.3、结论及引申出的问题

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

8.3.2、单路排序的问题

8.4、优化策略

8.4.1、尝试提高 sort_buffer_size

8.4.2、尝试提高 max_length_for_sort_data

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

三、GROUP BY

猜你喜欢

热点阅读

1、在`WHERE`条件字段上加`索引`，但是为什么在`ORDER BY`字段上还要加`索引`？

1、删除`student表`和`class表`的索引

3、`ORDER BY`中时不添加 `LIMIT`，索引失效

3.2、`ORDER BY`中时不添加 `LIMIT`

3.3、`ORDER BY`中时不添加 `LIMIT`，但是返回字段为`索引列`时（覆盖索引）

3.4、`ORDER BY`中时添加 `LIMIT`

4.1、索引顺序索引顺序!.png

4.2、不遵守`最前左原则`，索引失效

5、ORDER BY 时规则不一致, 索引失效（顺序错，不索引；方向反，不索引）

5.1、索引顺序索引顺序!.png

6.5、为`WHERE字段`和`ORDER BY`创建联合索引

6.6、 ORDER BY 字段顺序与索引顺序不匹配，造成`FILESORT`

7.4、尽量让where的过滤条件和排序使用上索引

8.1、双路排序（慢）

8.2、单路排序（快）