MySQL——子查询、排序、分组、分页优化_15. mysql 的分组与查询优化

---

MySQL——子查询、排序、分组、分页优化

1、子查询优化

子查询

当一个查询是另一个查询的子部分是，，称之为子查询(查询语句中嵌套含有查询语句)。子查询也是使用频率比较高的一种查询类型。因此，优化子查询，对于整个系统的性能也有直接的影响。

子查询的位置

在 SELECT 语句中，子查询可以被嵌套在 SELECT 语句的列、表和查询条件中，即 SELECT 子句，FROM 子句、WHERE 子句、GROUP BY 子句和 HAVING 子句。

子查询效率不高的原因

1、执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录（临时表无法建立索引）。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

2、子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。

3、对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

优化方向

在MySQL中，可以把子查询改为连接查询，连接查询不用建立临时表，其速度比子查询要快。

例如子查询为：

select s.*
from student s
where s.stuno not in (select monitor from class c where monitor is not null)
1
2
3

修改后的连接查询：

select a.*
from student s left join class c
on s.stuno = c.monitor
where c.monitor is null;
1
2
3
4

2、ORDER BY排序优化

MySQL支持两种排序方式，分别是 FileSort 和 Index 排序

• Index 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高
• FileSort 排序一般在内存中进行，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

优化建议

1、SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句使用索引避免使用FileSort排序。当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

2、尽量使用Index索引完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引。

3、无法使用Index索引时，需要对 FileSort 方式进行调优

实例

创建索引：

 create index idx_stuno_name_age on student(stuno,name,age);
1

只使用 ORDER BY：索引失效，原因：进行回表操作（二级索引中查完需要到聚簇索引中再查一遍）时要排序的数据量太大，效率低，查询优化器会选择在内存中排序的方式

mysql>  explain select * from student order by stuno,name;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8

使用 ORDER BY 时加上LIMIT：索引没有失效，原因：对前10条数据使用索引排序再进行回表操作

mysql>  explain select * from student order by stuno,name limit 10;
+----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | index | NULL          | idx_stuno_name_age | 72      | NULL |   10 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8

当索引列排序相反时，索引失效

mysql> explain select * from student order by stuno desc ,name asc limit 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8

按照索引长度为4可知只使用了stuno列上的索引，因为根据stuno条件过滤筛选出来的数据量不大，可以直接进行回表操作，而不需要使用其他索引

mysql> explain select * from student where stuno = 10 and age < 20 order by name;
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_stuno_name_age | idx_stuno_name_age | 4       | const |    1 |    33.33 | Using index condition |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8

当【范围条件】和【group by或者order by】的字段出现创建索引二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上，如：

select * from student where stuno = 10 and age < 20 order by name;
1

反之，当过滤的数据不足，而需要排序的数据很多时，优先把索引放在排序字段上。

3、filesort算法：双路排序和单路排序

双路排序（又叫回表排序模式，慢)

先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次从磁盘取回其它需要的字段；

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序(快)

是一次性从磁盘读取查询满足条件行的所有字段，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

对比：

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort_buffer中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

单路存在的问题

在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并)，排完再取sort_buffer容量大小，再排…从而多次I/O。

单路本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

单路优化方式一：提高sort_buffer_size

InnoDB存储引擎默认值是1MB：

mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

单路优化方式二：提高max_length_for_sort_data

max_length_for_sort_data默认值1024字节

mysql> show variables like '%max_length_for_sort_data%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

如果需要返回的列的总长度大于max_length_for_sort_data，使用双路排序算法，否则使用单路排序算法。

order by时select *是一个大忌。最好只Query需要的字段。原因:

当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法――多路排序。

两种算法的数据都有可能超出sort_buffer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

4、GROUP BY 分组优化

group by使用索引的原则几乎跟order by一致，group by即使where过滤条件没有用到索引，也可以直接使用索引，但是实际上是否使用索引还是基于查询优化器的选择

group by先排序再分组，遵照索引建的最佳左前缀法则

当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置

where效率高于having，能写在where限定的条件就不要写在having中了

减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

包含了order by、group by、distinct这些查询的语句,where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

5、分页查询优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。

一个常见又非常头疼的问题就是limit 2000000,10，此时需要MysQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大

select * from student limit 2000000,10;
1

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

select * from student t ,(select id from studnet order by id limit 2000000,10) a 
where t.id = a.id;
1
2

优化思路二：

该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询。

select * from student where id > 2000000 limit 10;
1