MySQL——子查询、排序优化

万里顾一程
995 阅读4分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第22天，点击查看活动详情

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)

修改后的连接查询：

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

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);

只使用 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)

使用 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)

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

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)

按照索引长度为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)

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

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

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

avatar
文章
阅读
粉丝