AI 助理
备案控制台
开发者社区 数据库 文章 正文

【MySQL】再说order by 优化

2017-08-04 1463
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 一 前言     为什么是再说呢？因为前面已经写过一篇blog，介绍order by 的基本原理以及优化。如果觉得对order by原理了解不透彻可以参考其他同行的文章《MySQL排序内部原理探秘》.本文是基于官网文档的二刷(基本翻译+测试验证),看完本文可以了解到什么样的select + order by 语句可以使用索引，什么样的不能利用到索引排序。
一 前言 
   为什么是再说呢？因为前面已经写过 一篇blog，介绍order by 的基本原理以及优化。如果觉得对order by原理了解不透彻可以参考其他同行的文章《 MySQL排序内部原理探秘》.本文是基于官网文档的二刷(基本翻译+测试验证),看完本文可以了解到什么样的select + order by 语句可以使用索引，什么样的不能利用到索引排序。
二 分析  
2.1 官方标准介绍
对于select  order by语句如何能够利用到索引，官方表述如下:
  1. "The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause."
翻译一下就是
即使ORDER BY语句不能精确匹配(组合)索引列也能使用索引,只要WHERE条件中的所有未使用的索引部分和所有额外的ORDER BY列为常数就行。
如何理解这句话呢？我们通过具体用例来解释。
2.2 准备工作
  1. CREATE TABLE `tx` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  3.   `shid` int(11) NOT NULL COMMENT '商店ID',
  4.   `gid` int(11) NOT NULL COMMENT '物品ID',
  5.   `type` tinyint(1) NOT NULL COMMENT '支付方式',
  6.   `price` int(10) NOT NULL COMMENT '物品价格',
  7.   `comment` varchar(200) NOT NULL COMMENT '备注',
  8.   PRIMARY KEY (`id`),
  9.   UNIQUE KEY `uniq_shid_gid` (`shid`,`gid`),
  10.   KEY `idx_price` (`price`),
  11.   KEY `idx_type` (`type`)
  12. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;
  13. INSERT INTO `tx` (`shid`, `gid`, `type`, `price`, `comment`) VALUES (6, 2, 0, '399', '2'),(6, 5, 0, '288', '2'),(6, 11, 0, '10', '2');
  14. (1, 1, 0, '10', 'sd'),
  15. (2, 55, 0, '210', 'sa'),
  16. (2, 33, 1, '999', 'a'),
  17. (3, 17, 0, '198', 'b'),
  18. (3, 22, 1, '800', 'e'),
  19. (4, 12, 0, '120', 'f'),
  20. (4, 73, 0, '250', 'd'),
  21. (5, 61, 0, '10', 'c'),
  22. (6, 1, 0, '210', '2'),
  23. (7, 9, 1, '999', '44'),
  24. (7, 2, 0, '198', '45'),
  25. (8, 3, 1, '800', 'rt'),
  26. (9, 4, 0, '120', 'pr'),
  27. (9, 6, 0, '250', 'x'),
  28. (10, 8, 0, '10', 'w'),
  29. (12, 9, 0, '210', 'w'),
  30. (12, 10, 1, '999', 'q'),
  31. (13, 11, 0, '198', ''),
  32. (13, 12, 1, '800', ''),
  33. (14, 13, 0, '120', ''),
  34. (14, 19, 0, '250', '');
  35. CREATE TABLE `goods_type` (
  36.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  37.   `type` int NOT NULL COMMENT '类型',
  38.   `name` varchar(20) NOT NULL COMMENT '名称',
  39.   PRIMARY KEY (`id`)
  40. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  41. INSERT INTO `goods_type` (`id`, `type`, `name`) VALUES
  42. (1, 1, 'hw手机'),
  43. (2, 0, 'xiaomi'),
  44. (3, 1, 'apple')

2.3 能够利用索引的例子分析
官方的文档 中介绍有7个例子可以使用索引进行排序。如果使用explain/desc工具查看执行计划中的extra中出现了 Using filesort则说明sql没有用到排序优化。
案例一
文档: SELECT * FROM t1 ORDER BY key_part1,key_part2,...; 
  1. test [RW] 06:03:52 >desc select * from tx order by shid,gid;
  2. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  4. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  5. | 1  | SIMPLE      | tx    | ALL  | NULL          | NULL | NULL    | NULL | 24   | Using filesort |
  6. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  7. 1 row in set (0.00 sec)
分析:
  显然上述sql没有利用到索引排序. type=ALL Extra=Using filesort,因为where字句没有条件，优化器选择全表扫描和内存排序。
  1. test [RW] 06:04:39 >desc select gid from tx order by shid,gid;
  2. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  3. | id | select_type | table | type  | possible_keys | key         | key_len   | ref  | rows | Extra       |
  4. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  5. | 1  | SIMPLE      | tx    | index | NULL          | uniq_shid_gid | 8       | NULL | 24   | Using index |
  6. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  7. 1 row in set (0.00 sec)
  8. test [RW] 06:04:47 >desc select shid,gid from tx order by shid,gid;
  9. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  10. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra |
  11. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  12. | 1  | SIMPLE      | tx    | index | NULL          | uniq_shid_gid | 8       | NULL | 24   | Using index |
  13. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  14. 1 row in set (0.00 sec)
  15. test [RW] 06:04:54 >desc select id,shid,gid from tx order by shid,gid;
  16. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  17. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra |
  18. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  19. | 1  | SIMPLE      | tx    | index | NULL          | uniq_shid_gid | 8       | NULL | 24   | Using index |
  20. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  21. 1 row in set (0.00 sec)
分析
    从type=index，extra=Using index 可以看出当select 的字段包含在索引中时，能利用到索引排序功能，进行覆盖索引扫描。
    使用select * 则不能利用覆盖索引扫描且由于where语句没有具体条件MySQL选择了全表扫描且进行了排序操作。
案例二
  SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
使用组合索引中的一部分做等值查询 ，另一部分作为排序字段。更严谨的说法是where条件使用组合索引的左前缀等值查询，使用剩余字段进行order by排序。
  1. test [RW] 06:05:41 >desc select * from tx where shid= 2 order by gid;
  2. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref     | rows  | Extra|
  4. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  5. | 1  | SIMPLE      | tx    | ref  | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using where |
  6. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  7. 1 row in set (0.00 sec)
  8. test [RW] 11:30:13 >desc select * from tx where shid= 2 order by gid desc;
  9. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  10. | id | select_type | table | type | possible_keys | key | key_len | ref     | rows  | Extra|
  11. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  12. | 1  | SIMPLE      | tx    | ref  | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using where |
  13. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  14. 1 row in set (0.00 sec)
分析:
  where 条件字句可以基于 shid 进行索引查找 并且利用(shid,gid)中gid的有序性避免额外的排序工作. 我们基于本例解释"即使ORDER BY语句不能精确匹配(组合)索引列也能使用索引,只要WHERE条件中的所有未使用的索引部分和所有额外的ORDER BY列为常数就行。"
该语句的order by  gid 并未精确匹配到组合索引(shid,gid)，where条件 shid利用了组合索引的最左前缀且为等值常量查询，对order by 而言shid就是额外的字段，没有出现在order by子句中却是组合索引的一部分。这样的条件既可以使用索引来排序。

案例三
SELECT * FROM t1  ORDER BY key_part1 DESC, key_part2 DESC;
其实和案例一 类似，只是选择了倒序。该sql不能利用索引的有序性，需要server层进行排序。
  1. test [RW] 06:06:30 >desc select * from tx order by shid desc,gid desc;
  2. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  4. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  5. | 1  | SIMPLE      | tx    | ALL  | NULL          | NULL | NULL    | NULL | 24   | Using filesort |
  6. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  7. 1 row in set (0.00 sec)
  8. 如果select 中选择索引字段，可以利用覆盖索引扫描则可以利用索引进行排序。
  9. test [RW] 06:06:31 >desc select shid,gid from tx order by shid desc,gid desc;
  10. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  11. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
  12. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  13. | 1  | SIMPLE      | tx    | index | NULL          | uniq_shid_gid | 8       | NULL | 24   | Using index |
  14. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  15. 1 row in set (0.00 sec)
案例四
SELECT * FROM t1 WHERE key_part1 = 1  ORDER BY key_part1 DESC, key_part2 DESC;
本例和案例二类似，只是order by 字句中包含所有的组合索引列。
  1. test [RW] 06:06:55 >desc select * from tx where shid=4 order by shid desc ,gid desc;
  2. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  3. | id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
  4. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  5. | 1  | SIMPLE      | tx    | ref  | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using where |
  6. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
  7. 1 row in set (0.00 sec)
分析:
   where shid=4 可以利用shid的索引定位数据记录，select *  有不在索引里面的字段，所以回表访问组合索引列之外的数据，利用了gid索引的有序性避免了排序工作。
案例五
SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC;
  1. test [RW] 11:40:48 >desc select * from tx where shid>5 order by shid desc ;
  2. +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
  4. +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
  5. | 1  | SIMPLE      | tx    | ALL  | uniq_shid_gid | NULL | NULL    | NULL | 24   | Using where; Using filesort |
  6. +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
  7. 1 row in set (0.00 sec)

  9. test [RW] 11:47:25 >desc select * from tx where shid>13 order by shid desc ;
  10. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  11. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                 |
  12. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  13. | 1  | SIMPLE      | tx    | range | uniq_shid_gid | uniq_shid_gid | 4       | NULL | 2    | Using index condition |
  14. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  15. 1 row in set (0.00 sec)
分析
  表总共24行,其中大于5的有16行，大于13的2行，导致MySQL优化器选择了不同的执行计划。这个测试说明和shid的区分度有关。
案例六
SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;
利用组合索引前缀索引进行ref等值查询，其他字段进行范围查询，order by 非等值的字段
  1. test [RW] 06:10:41 >desc select * from tx where shid=6 and gid>1 order by gid;
  2. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  3. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                 |
  4. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  5. | 1  | SIMPLE      | tx    | range | uniq_shid_gid | uniq_shid_gid | 8       | NULL | 3    | Using index condition |
  6. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------+
  7. 1 row in set (0.02 sec)
分析:
    利用shid=6的进行索引查询记录到了MySQL的ICP特性，无排序操作。为啥使用ICP 这个待确认。

2.4 不能利用索引排序的分析
案例一
order by语句使用了多个不同的索引
SELECT * FROM t1 ORDER BY key1, key2;
  1. test [RW] 09:44:03 >desc select * from tx order by price, type;
  2. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  4. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  5. | 1  | SIMPLE      | tx    | ALL  | NULL          | NULL | NULL    | NULL | 24   | Using filesort |
  6. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
  7. 1 row in set (0.00 sec)
因为sql使用了不同的索引列，在存储上顺序存在不一致的可能性，MySQL会选择排序操作。
特例 因为所有的辅助索引里面都包含主键id，当where 字段加上order by字段沟通完整的索引时 ，可以避免filesort的
  1. test [RW] 11:20:10 >desc select * from tx where type=1 order by id;
  2. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
  3. | id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |
  4. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
  5. | 1  | SIMPLE      | tx    | ref  | idx_type      | idx_type | 1       | const | 6    | Using where |
  6. +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
  7. 1 row in set (0.00 sec)
案例二
当查询条件使用了与order by不同的其他的索引，且值为常量，但排序字段是另一个联合索引的非连续部分时
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part1, key_part3;

  1. test [RW] 11:19:17 >desc select * from tx where type=1 order by gid;
  2. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  3. | id | select_type | table | type | possible_keys | key      | key_len | ref | rows   | Extra                       |
  4. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  5. | 1  | SIMPLE      | tx    | ref  | idx_type      | idx_type | 1       | const | 6    | Using where; Using filesort |
  6. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  7. 1 row in set (0.00 sec)
  8. test [RW] 11:21:08 >desc select * from tx where type=1 order by shid;
  9. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  10. | id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
  11. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  12. | 1  | SIMPLE      | tx    | ref  | idx_type      | idx_type | 1       | const | 6    | Using where; Using filesort |
  13. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  14. 1 row in set (0.00 sec)
分析 
  与案例一一致，key2 的顺序语句key1(key_part1)存储排序不一样的情况下，MySQL 都会选择filesort 。
案例三
order by 语句使用了和组合索引默认不同的排序规则
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
官方文档中提示使用混合索引排序规则会导致额外排序，其实我们创建索引的时候可以做 (key_part1 DESC, key_part2 ASC)
案例四
当where 条件中利用的索引与order by 索引不同时，与案例二有相似性。
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
  1. test [RW] 11:19:44 >desc select * from tx where type=1 order by shid;
  2. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  3. | id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
  4. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  5. | 1  | SIMPLE      | tx    | ref  | idx_type      | idx_type | 1       | const | 6    | Using where; Using filesort |
  6. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  7. 1 row in set (0.00 sec)
  8. test [RW] 11:20:07 >desc select * from tx where type=1 order by shid,gid;
  9. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  10. | id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
  11. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  12. | 1  | SIMPLE      | tx    | ref  | idx_type      | idx_type | 1       | const | 6    | Using where; Using filesort |
  13. +----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
  14. 1 row in set (0.00 sec)
案例五
order by 字段使用了表达式
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

  1. test [RW] 11:53:39 >desc select * from tx where shid=3 order by -shid;
  2. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------------+
  3. | id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra                       |
  4. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------------+
  5. | 1  | SIMPLE      | tx    | ref  | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using where; Using filesort |
  6. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------------+
  7. 1 row in set (0.00 sec)
  1. test [RW] 11:56:26 >desc select * from tx where shid=3 order by shid;
  2. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
  3. | id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra |
  4. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
  5. | 1  | SIMPLE      | tx    | ref  | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | NULL |
  6. +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
分析
    order by 的字段使用函数，和在where条件中使用函数索引一样 ，MySQL都无法利用到索引。
案例六
The query joins many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)
当查询语句是多表连接，并且ORDER BY中的列并不是全部来自第1个用于搜索行的非常量表.(这是EXPLAIN输出中的没有使用const联接类型的第1个表)

  1. test [RW] 12:32:43 >explain select shid,gid from tx a left join goods_type b on a.shid=b.id where a.shid=2 order by a.gid,b.id;
  2. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+-------------------------------+
  3. | id | select_type | table | type  | possible_keys | key           | key_len | ref   | rows | Extra                         |
  4. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+-------------------------------+
  5. | 1  | SIMPLE      | a     | ref   | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using index; Using temporary;                                                                                                Using filesort                |
  6. | 1  | SIMPLE      | b     | const | PRIMARY       | PRIMARY       | 4       | const | 1    | Using index                   |
  7. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+----------------------------------------------+
  8. 2 rows in set (0.00 sec)
  9. test [RW] 12:32:44 >explain select shid,gid from tx a left join goods_type b on a.shid=b.id where a.shid=2 order by a.gid;
  10. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+--------------------------+
  11. | id | select_type | table | type  | possible_keys | key           | key_len | ref   | rows | Extra |
  12. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+--------------------------+
  13. | 1  | SIMPLE      | a     | ref   | uniq_shid_gid | uniq_shid_gid | 4       | const | 2    | Using where; Using index |
  14. | 1  | SIMPLE      | b     | const | PRIMARY       | PRIMARY       | 4       | const | 1    | Using index |
  15. +----+-------------+-------+-------+---------------+---------------+---------+-------+------+--------------------------+
  16. 2 rows in set (0.00 sec)
分析
  出现join的情况下不能利用索引其实有很多种，只要对a的访问不满足上面说的可以利用索引排序的情况都会导致额外的排序动作。但是当where + order 复合要求，order by 有包含了其他表的列就会导致额外的排序动作。
案例七
sql中包含的order by 列与group by 列不一致 
  1. test [RW] 11:26:54 >desc select * from tx group by shid order by gid;
  2. +----+-------------+-------+-------+---------------+---------------+---------+------+------+---------------------------------+
  3. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                          |
  4. +----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------------+
  5. | 1  | SIMPLE      | tx    | index | uniq_shid_gid | uniq_shid_gid | 8       | NULL | 24   | Using temporary; Using filesor |
  6. +----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------------+
  7. 1 row in set (0.00 sec)
group by 本身会进行排序的操作,我们可以显示的注让group by不进行额外的排序动作。
  1. test [RW] 12:09:52 >desc select * from tx group by shid order by null;
  2. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
  3. | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra |
  4. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
  5. | 1  | SIMPLE      | tx    | index | uniq_shid_gid | uniq_shid_gid | 8       | NULL | 24   | NULL |
  6. +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
  7. 1 row in set (0.00 sec)
案例八
索引本身不支持排序存储 比如，hash索引。
  1. CREATE TABLE `hash_test` (
  2.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT ,
  3.   `name` varchar(20) NOT NULL COMMENT '名称',
  4.   PRIMARY KEY (`id`),
  5.   KEY `name` (`name`)
  6. ) ENGINE=MEMORY ;
  7. INSERT INTO `hash_test` (`id`, `name`) VALUES
  8. (1, '张三'),
  9. (2, '李四');
  10. test [RW] 12:07:27 >explain select * from hash_test force index(name) order by name;
  11. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  12. | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  13. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  14. | 1  | SIMPLE      | hash_test | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using filesort |
  15. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  16. 1 row in set (0.00 sec)
  17. test [RW] 12:07:48 >explain select * from hash_test order by name;
  18. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  19. | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  20. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  21. | 1  | SIMPLE      | hash_test | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using filesort |
  22. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  23. 1 row in set (0.00 sec)
  24. test [RW] 12:07:53 >alter table hash_test ENGINE=innodb;
  25. Query OK, 2 rows affected (0.45 sec)
  26. Records: 2 Duplicates: 0 Warnings: 0
  27. test [RW] 12:08:33 >explain select * from hash_test order by name;
  28. +----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
  29. | id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra |
  30. +----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
  31. | 1  | SIMPLE      | hash_test | index | NULL          | name | 82      | NULL | 1    | Using index |
  32. +----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
  33. 1 row in set (0.00 sec)
分析 
   hash 索引本身不支持排序存储，故不能利用到排序特性，将表转化为innodb再次查询，避免了filesort
案例九
order by的索引使用部分字符串 比如 key idx_name(name(2))

  1. test [RW] 12:08:37 >alter table hash_test drop key name ,add key idx_name(name(2));
  2. Query OK, 0 rows affected (0.03 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0
  4. test [RW] 12:09:50 >explain select * from hash_test order by name;
  5. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  6. | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra          |
  7. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  8. | 1  | SIMPLE      | hash_test | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using filesort |
  9. +----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
  10. 1 row in set (0.00 sec)
三 老生常谈的优化策略
 为了提高order by 查询的速度，尽可能的利用索引的有序性进行排序，如果不能利用索引排序的功能，那么我们只能退而求其次优化order by相关的缓存参数
1 增加 sort_buffer_size 大小，建议sort_buffer_size要足够大能够避免磁盘排序和合并排序次数。
2 增加 read_rnd_buffer_size 大小。
3 使用合适的列大小存储具体的内容，比如对于city字段 varchar(20)比varchar(200)能获取更好的性能。
4 将tmpdir 目录指定到os上面有足够空间的具有比较高iops能力的存储上。

四 推荐文章
[1] MySQL order by 优化的那些事儿
[2] 官方文档 
[3] order by 结果不准确的问题及解决  
[4]  MySQL排序原理与案例分析 
[5]  order by 原理以及优化 
看完本文 如果您觉得有所收获 ，可以请 北在南方 一瓶饮料 ^_^




文章标签：
云数据库 RDS MySQL 版
索引
关系型数据库
MySQL
存储
SQL
测试技术
缓存
关键词：
云数据库 RDS MySQL 版优化
云数据库 RDS MySQL 版order
云数据库 RDS MySQL 版order优化
云数据库 RDS MySQL 版by
云数据库 RDS MySQL 版order by优化
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
yangyi_dba
目录
相关文章
东阳马生架构
|
5天前
|
缓存 算法 关系型数据库
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念，包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序（Using filesort）的区别及优化方法、以及单表和多表查询的索引优化策略。
东阳马生架构
55 22
MySQL底层概述—8.JOIN排序索引优化
东阳马生架构
|
5天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了：Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
东阳马生架构
75 15
MySQL底层概述—7.优化原则及慢查询
东阳马生架构
|
6天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化，旨在提升数据库性能。主要内容包括： 1. 内存相关参数优化：缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化：日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化： 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
东阳马生架构
45 12
MySQL底层概述—5.InnoDB参数优化
东阳马生架构
|
8天前
|
SQL 关系型数据库 MySQL
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例，涵盖不同场景下的问题分析与解决方案： 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
东阳马生架构
32 8
小虚竹
|
8天前
|
关系型数据库 MySQL 数据库
从MySQL优化到脑力健康：技术人与效率的双重提升
聊到效率这个事，大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整，怎么能更快、更省力地完成事情，都是我们每天要琢磨的事。
小虚竹
56 23
东阳马生架构
|
10天前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题： 1. **MySQL日志的顺序写和数据文件的随机读指标**：解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**：解析了Linux存储系统的分层架构，包括VFS、Page Cache、IO调度等，并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**：介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**：分析了MySQL连接数限制问题的原因及解决方法。
东阳马生架构
62 23
拉丁解牛说技术
|
13天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中，系统性能优化，从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化，除了DBA在集群性能、服务器调优需要投入精力，我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后，SQL执行效率可能就会出现瓶颈，影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能，在工作中解决SQL性能问题显得非常关键。
拉丁解牛说技术
82 13
蓝易云
|
3天前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍，您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限，以及识别和解决长时间运行的事务和死锁问题，可以有效地保障应用的稳定运行。
蓝易云
42 25
蓝易云
|
12天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤，希望对您有所帮助。
蓝易云
90 42
游客r2tgrg3ez7yzc
|
25天前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示：“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后，连接时提示不允许连接问题解决
游客r2tgrg3ez7yzc
162 69

热门文章

最新文章

  • 1
    美团面试：MySQL为什么 不用 Docker部署？
  • 2
    【深入了解MySQL】优化查询性能与数据库设计的深度总结
  • 3
    docker pull mysql:8.0.26提示Error response from daemon: Get “https://registry-1.docker.io/v2/“: EOF错误
  • 4
    MySQL底层概述—2.InnoDB磁盘结构
  • 5
    数据库连接工具连接mysql提示：“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
  • 6
    MySQL底层概述—1.InnoDB内存结构
  • 7
    MySQL原理简介—1.SQL的执行流程
  • 8
    嵌入式C++、STM32、MySQL、GPS、InfluxDB和MQTT协议数据可视化
  • 9
    MySQL 分库分表方案
  • 10
    MySQL 中如何实现分库分表？常见的分库分表策略有哪些？
  • 1
    如何排查和解决PHP连接数据库MYSQL失败写锁的问题
    42
  • 2
    京东面试：MySQL MVCC是如何实现的？如何通过MVCC实现读已提交、可重复读隔离级别的？
    38
  • 3
    MySQL底层概述—10.InnoDB锁机制
    50
  • 4
    MySQL底层概述—9.ACID与事务
    39
  • 5
    MySQL底层概述—8.JOIN排序索引优化
    55
  • 6
    如何实现 MySQL 的读写分离？
    101
  • 7
    MySQL 主从复制
    72
  • 8
    MySQL底层概述—7.优化原则及慢查询
    75
  • 9
    MySQL底层概述—6.索引原理
    50
  • 10
    MySQL底层概述—5.InnoDB参数优化
    45

    • 相关课程

    更多
  • Analytic DB MySql产品介绍
  • 实时数据接入：5分钟上手 Flink MySQL 连接器
  • RDS MySQL 高效设计及性能调优
  • 云数据库RDS MySQL从入门到高阶
  • Java Spring Boot 2.6.0开发实战-1024程序员节创造营公益课
  • MySQL实操课程

    • 相关电子书

    更多
  • PolarDB-X：更具性价比的 MySQL替换
  • 2023云栖大会：AnalyticDB MySQL新特性介绍
  • 云数据库RDS MySQL从入门到高阶

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • RDS MySQL大促场景下的智能化弹性
  • PolarDB MySQL自动SQL限流
  • 如何开通MySQL的专属集群

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    阿里云oss简介和如何对接使用