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

一、排序优化（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中没有索引

  • SQL
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

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

  • EXPLAIN
    使用 filesort排序.png

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

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

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

3.4、ORDER BY中时添加 LIMIT

  • SQL
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、不遵守最前左原则，索引失效

  • SQL
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、排序字段与索引字段不同，索引失效

  • SQL
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、排序字段部分匹配，使用索引

  • SQL
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 排序字段升降序不一致

  • SQL
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 排序字段升降序不一致

  • SQL
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 排序字段的升降序与索引相同或相反，索引可用

  • SQL
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、无索引

  • SQL
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 索引

  • SQL
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);
  • SQL
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);
  • SQL
EXPLAIN
SELECT *
FROM student
WHERE age = 45
ORDER BY classid;

  • EXPLAIN
    image.png

  • 小结

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

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

  • SQL
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);
  • SQL
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;
  • SQL
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的学生，按用户名称排序

  • SQL
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);
  • SQL
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);
  • SQL
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会很慢。
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 219,539评论 6赞 508
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 93,594评论 3赞 396
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 165,871评论 0赞 356
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,963评论 1赞 295
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,984评论 6赞 393
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,763评论 1赞 307
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,468评论 3赞 420
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,357评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,850评论 1赞 317
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 38,002评论 3赞 338
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,144评论 1赞 351
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,823评论 5赞 346
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,483评论 3赞 331
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 32,026评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,150评论 1赞 272
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,415评论 3赞 373
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 45,092评论 2赞 355

推荐阅读更多精彩内容