19.order by关键字优化

order by 子句,尽量使用index方式排序,避免使用filesort方式排序

准备数据

CREATE TABLE tblA(
  #`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  age INT,
  birth TIMESTAMP NOT NULL 
);

INSERT INTO tblA(age, birth) VALUES (22, NOW());
INSERT INTO tblA(age, birth) VALUES (23, NOW());
INSERT INTO tblA(age, birth) VALUES (23, NOW());

CREATE INDEX idx_A_ageBirth ON tblA(age, birth);

SELECT * FROM tblA;

以下只关注有没有使用 filesort

order by字段,带头大哥在

mysql> EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)


order by字段,带头大哥在

mysql> EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age, birth;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

order by字段,带头大哥不在

mysql> EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

order by字段顺序与索引字段不一致

mysql> EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth, age;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

带头大哥不在

mysql> EXPLAIN SELECT * FROM tblA ORDER BY birth;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

带头大哥不在

mysql> EXPLAIN SELECT * FROM tblA where birth > '2016-01-28 00:00:00'  ORDER BY birth;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |    33.33 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

带头大哥 在

mysql> EXPLAIN SELECT * FROM tblA where birth > '2016-01-28 00:00:00'  ORDER BY age;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)


两个字段排序方向不一致

mysql> EXPLAIN SELECT * FROM tblA  ORDER BY age ASC, birth DESC;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tblA  | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

MySQL支持二种方式的排序,FileSort和Index, Index效率高。它指MySQL扫描索引本身完成排序。FileSort方式效率较低

ORDER BY满足两情况,会使用INDEX方式排序:

    1. ORDER BY 语句使用索引最左前列
    1. 使用Where子句与ORDER BY 子句条件列组合满足索引最左前列

filesort有两种算法:双路排序算法和单路排序算法

  • 双路排序:MySQL4.1之前使用双路排序,字面意思是两次扫描磁盘,最终得到数据。
  • 单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中了。

在sort_buffer中,方法B比方法A要多占用很多空间,因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据进行排序(创建tmp文件,多路合并),排完再取sort_buffer容量大小,再排......从而多次IO.

本来想省一次IO操作,反而导致了大量的IO 操作,反而得不偿失。

优化策略

  • 增大sort_buffer_size参数的设置
  • 增大max_length_for_sort_dataf参数设置

提高Order by的速度

  • 1.Order by 时select * 是一个大忌,只查询需要的字段,这点非常重要。在这里的影响是:
    • 1.1 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用改进后的算法--单路排序,否则用老算法--多路排序。
    • 1.2 两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建 tmp文件进行合并排序,导致多次IO。此时单路排序算法的性能还不如双路,所以要提高sort_buffer_size.
    1. 尝试提高sort_buffer_size , 不管用哪种算法,会增加用改进算法的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘IO活动和低的处理器使用率。

为排序使用索引

MySQL两种排序方式:文件排序或扫描有序索引排序

MySQL能为排序玮查询使用相同的索引

KEY a_b_c(a,b,c)

order by能使用索引最左前缀

  • ORDER BY a
  • ORDER BY a,b
  • ORDER BY a,b,c
  • ORDER BY a DESC, b DESC, c DESC

如果 WHERE使用索引的最左前缀为常量,则order by能使用索引排序,不产生filessort

  • WHERE a=const ORDER BY b, c
  • WHERE a=const AND b=const ORDER BY c
  • WHER a=const ORDER BY b,c
  • WHERE a=const AND b>const ORDER BY b,c

不能使用索引进行排序

  • ORDER BY a ASC, b DESC, c DESC 排序不一致
  • WHERE g=const ORDER BY b,c 丢失a索引
  • WHERE a=const ORDER BY c 丢失b索引
  • WHERE a=const ORDER BY a,d d不是索引的一部分
  • WHERE a in(...) ORDER BY b,c 对于排序来说,多个相等条件也是范围查询

GROUP BY 关键字优化

  • group by 实质是先排序后再分组,尊照索引建的最佳左前缀
  • 无法使用索引列, 增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
  • where 高于having,能写在where限定的条件就不要去having限定了。