MySQL 5.7 ORDER BY排序的优化

た入场券 2024-04-18 02:01 5阅读 0赞

**在一些情况下，MySQL可以使用索引而无需额外的排序来执行ORDER BY排序。**  
**即使ORDER BY没有匹配上指定的索引，也可以使用索引，只要所有未使用索引部分和所有ORDER BY字段是WHERE语句的常数。**

SELECT * FROM t1
      ORDER BY key_part1,key_part2,... ;
    
    SELECT * FROM t1
      WHERE key_part1 = constant ORDER BY key_part2;
    
    SELECT * FROM t1
      ORDER BY key_part1 DESC, key_part2 DESC;
    
    SELECT * FROM t1
      WHERE key_part1 = 1
      ORDER BY key_part1 DESC, key_part2 DESC;
    
    SELECT * FROM t1
      WHERE key_part1 > constant ORDER BY key_part1 ASC;
    
    SELECT * FROM t1
      WHERE key_part1 < constant ORDER BY key_part1 DESC;
    
    SELECT * FROM t1
      WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;

**在某些情况下，MySQL不会使用索引进行排序**  
  
 **查询使用多个不同的索引进行排序**

SELECT * FROM t1 ORDER BY key1, key2;

查询在索引的非连续部分上使用排序

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

**排序中既有ASC又有DESC**

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

**用于获取数据的索引不同于ORDER BY排序的索引**

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

**查询使用表达式进行排序，而不是使用索引的字段名进行排序**

SELECT * FROM t1 ORDER BY ABS(key);
    SELECT * FROM t1 ORDER BY -key;

查询关联了许多表，并且ORDER BY使用的字段不是返回行的第一个非常量表中的字段（在EXPLAIN执行计划输出中第一个表的连接类型不是const）  
  
查询使用不同的ORDER BY和GROUP BY表达式  
  
在排序中只使用了一个字段的前缀索引  
  
索引没有按照顺序存储行，例如MEMORY表的HASH索引  
  
如果一个查询包含GROUP BY，但是你不想将查询结果排序，可以通过ORDER BY NULL语句来取消排序

INSERT INTO foo
    SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

**与提升ORDER BY速度相关的系统参数：**  
  
增加sort\_buffer\_size参数的值。理想状况下，这个参数的值应该足够大，可以将整个结果集放入到sort buffer中，避免到磁盘中进行排序。  
  
存储在缓冲区的列值的大小是由max\_sort\_length参数决定的。  
  
想要监控排序在磁盘上的合并数量，可以通过观察Sort\_merge\_passes这个状态参数。  
mysql> show global status like 'Sort\_merge\_passes';  
\+-------------------+-------+  
| Variable\_name     | Value |  
\+-------------------+-------+  
| Sort\_merge\_passes | 0     |  
\+-------------------+-------+  
1 row in set (0.11 sec)  
  
增加read\_rnd\_buffer\_size参数的值。  
  
定义列的时候，仅分配它们需要的值，这样每行在排序时可以使用更少的内存。例如，如果每列的值不超过16个字符，CHAR(16)比CHAR(200)更好。  
  
改变tmpdir系统变量，将其指向一个专用的大容量的文件系统目录。  
  
如果ORDER BY排序没有使用索引，但是语句中带有LIMIT语句，优化器可能会避免使用合并文件而是在内存中进行排序。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2122017/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2122017/