如何展开MySQL中的order by 优化
发布时间：2021-12-20 11:27:36 所属栏目：通讯 来源：互联网
导读：这篇文章将为大家详细讲解有关如何进行MySQL中的order by 优化，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。 一 前言 介绍order by 的基本原理以及优化。如果觉得对order by原理了解不透彻，看完之后
|
这篇文章将为大家详细讲解有关如何进行MySQL中的order by 优化，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。 一 前言 介绍order by 的基本原理以及优化。如果觉得对order by原理了解不透彻，看完之后你可以了解到什么样的select + order by 语句可以使用索引，什么样的不能利用到索引排序。 二 分析 2.1 官方标准介绍 对于select order by语句如何能够利用到索引，官方表述如下: "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.3 能够利用索引的例子分析 官方的文档 中介绍有7个例子可以使用索引进行排序。如果使用explain/desc工具查看执行计划中的extra中出现了Using filesort则说明sql没有用到排序优化。 案例一 文档: SELECT * FROM t1 ORDER BY key_part1,key_part2,...; test [RW] 06:03:52 >desc select * from tx order by shid,gid; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | tx | ALL | NULL | NULL | NULL | NULL | 24 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) 分析: 显然上述sql没有利用到索引排序. type=ALL Extra=Using filesort,因为where字句没有条件，优化器选择全表扫描和内存排序。 test [RW] 06:04:39 >desc select gid from tx order by shid,gid; +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ | 1 | SIMPLE | tx | index | NULL | uniq_shid_gid | 8 | NULL | 24 | Using index | +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ 1 row in set (0.00 sec) test [RW] 06:04:47 >desc select shid,gid from tx order by shid,gid; +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ | 1 | SIMPLE | tx | index | NULL | uniq_shid_gid | 8 | NULL | 24 | Using index | +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ 1 row in set (0.00 sec) test [RW] 06:04:54 >desc select id,shid,gid from tx order by shid,gid; +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ | 1 | SIMPLE | tx | index | NULL | uniq_shid_gid | 8 | NULL | 24 | Using index | +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ 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排序。 test [RW] 06:05:41 >desc select * from tx where shid= 2 order by gid; +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | 1 | SIMPLE | tx | ref | uniq_shid_gid | uniq_shid_gid | 4 | const | 2 | Using where | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) test [RW] 11:30:13 >desc select * from tx where shid= 2 order by gid desc; +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | 1 | SIMPLE | tx | ref | uniq_shid_gid | uniq_shid_gid | 4 | const | 2 | Using where | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ 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层进行排序。 test [RW] 06:06:30 >desc select * from tx order by shid desc,gid desc; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | tx | ALL | NULL | NULL | NULL | NULL | 24 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) 如果select 中选择索引字段，可以利用覆盖索引扫描则可以利用索引进行排序。 test [RW] 06:06:31 >desc select shid,gid from tx order by shid desc,gid desc; +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ | 1 | SIMPLE | tx | index | NULL | uniq_shid_gid | 8 | NULL | 24 | Using index | +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+ 1 row in set (0.00 sec) 案例四 SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC, key_part2 DESC; 本例和案例二类似，只是order by 字句中包含所有的组合索引列。 test [RW] 06:06:55 >desc select * from tx where shid=4 order by shid desc ,gid desc; +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | 1 | SIMPLE | tx | ref | uniq_shid_gid | uniq_shid_gid | 4 | const | 2 | Using where | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ 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; （编辑：云计算网_泰州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330476号