Mysql优化----一条SQL百倍提升之旅

在实现业务逻辑的时候，有些复杂一点逻辑会用数据库子查询去实现，但是sql用子查询会带来性能问题，下面就一个例子来说明，怎么优化子查询，来提升查询速度

mysql> desc update t_student_info a
    ->  set a.exstudentid='test01'
    ->  where a.studentID in
    ->  (select studentID from (select studentID from t_student_info where stdTYPE='8' and state=2 limit 10000,100) b);
+----+--------------------+----------------+------------+----------------+---------------+-------------+---------+------+--------+----------+-------------+
| id | select_type        | table          | partitions | type           | possible_keys | key         | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+----------------+------------+----------------+---------------+-------------+---------+------+--------+----------+-------------+
|  1 | UPDATE             | a              | NULL       | index          | NULL          | PRIMARY     | 24      | NULL | 221058 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | <derived3>     | NULL       | index_subquery | <auto_key0>   | <auto_key0> | 24      | func |    221 |   100.00 | Using index |
|  3 | DERIVED            | t_student_info | NULL       | ALL            | NULL          | NULL        | NULL    | NULL | 221058 |     1.00 | Using where |
+----+--------------------+----------------+------------+----------------+---------------+-------------+---------+------+--------+----------+-------------+
3 rows in set (0.00 sec)

复制

可以看到这个Update语句的执行计划，用的是DEPENDENT SUBQUERY，这样就需要循环的去执行这个只查询，效率会慢，能不能把这个只查询改一下，改成join查询呢，下面就是优化之后的sql写法

update t_student_info a set a.exstudentid='test01' where a.studentID in (select studentID from (select studentID from t_student_info where stdTYPE='8' and state=2 limit 10000,100) b)

mysql> desc update t_student_info a
    ->        inner join
    ->        (select studentID from t_student_info where stdTYPE='8' and state=2 limit 10000,100) b
    ->        on a.studentID=b.studentID
    ->        set a.exstudentid='test01';
+----+-------------+----------------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+
| id | select_type | table          | partitions | type   | possible_keys | key     | key_len | ref         | rows   | filtered | Extra       |
+----+-------------+----------------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2>     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL        |   2210 |   100.00 | NULL        |
|  1 | UPDATE      | a              | NULL       | eq_ref | PRIMARY       | PRIMARY | 24      | b.studentID |      1 |   100.00 | NULL        |
|  2 | DERIVED     | t_student_info | NULL       | ALL    | NULL          | NULL    | NULL    | NULL        | 221058 |     1.00 | Using where |
+----+-------------+----------------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+
3 rows in set (0.00 sec)

复制

可以从执行计划中看到执行计划已经从DEPENDENT SUBQUERY变成了DERIVED，以驱动表去关联查询了，下面来看看实际执行效果

mysql> update t_student_info a set a.exstudentid='test01' where a.studentID in (select studentID from (select studentID from t_student_info where stdTYPE='8' and state=2 limit 10000,100) b);
Query OK, 0 rows affected (0.37 sec)
Rows matched: 100  Changed: 0  Warnings: 0

mysql> update t_student_info a set a.exstudentid='test01' where a.studentID in (select studentID from (select studentID from t_student_info where stdTYPE='8' and state=2 limit 10000,100) b);
Query OK, 0 rows affected (0.39 sec)
Rows matched: 100  Changed: 0  Warnings: 0

mysql> update t_student_info a inner join (select studentID from t_student_info where stdTYPE='8' and state=2 limit 10000,100) b on a.studentID=b.studentID set a.exstudentid='test01';
Query OK, 0 rows affected (0.07 sec)
Rows matched: 100  Changed: 0  Warnings: 0

mysql> update t_student_info a inner join (select studentID from t_student_info where stdTYPE='8' and state=2 limit 10000,100) b on a.studentID=b.studentID set a.exstudentid='test01';
Query OK, 0 rows affected (0.07 sec)
Rows matched: 100  Changed: 0  Warnings: 0

复制

为了排除因为物理读导致的干扰，没条sql都连续执行2遍，从执行结果可以看到，使用子查询的sql平均执行时间在370毫秒，而用inner join的sql平均执行时间在70毫秒，效率提升了5倍多，优化效果还是很明显的，小伙伴可能会觉得，才有5倍提升，其实优化之后的语句耗费时间的在limit 10000,100这里，如果改外limit 1,100大家再来看看对比效果

mysql> update t_student_info a inner join (select studentID from t_student_info where stdTYPE='8' and state=2 limit 1,100) b on a.studentID=b.studentID set a.exstudentid='test01';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 100  Changed: 0  Warnings: 0

mysql> update t_student_info a inner join (select studentID from t_student_info where stdTYPE='8' and state=2 limit 1,100) b on a.studentID=b.studentID set a.exstudentid='test01';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 100  Changed: 0  Warnings: 0

mysql> update t_student_info a set a.exstudentid='test01' where a.studentID in (select studentID from (select studentID from t_student_info where stdTYPE='8' and state=2 limit 1,100) b);
Query OK, 0 rows affected (0.31 sec)
Rows matched: 100  Changed: 0  Warnings: 0

mysql> update t_student_info a set a.exstudentid='test01' where a.studentID in (select studentID from (select studentID from t_student_info where stdTYPE='8' and state=2 limit 1,100) b);
Query OK, 0 rows affected (0.31 sec)
Rows matched: 100  Changed: 0  Warnings: 0

复制

inner join的执行时间已经是几毫秒了，而子查询还是在310毫秒，这效果就分明显，提升了100多倍，这种方法优化，不仅适合in,还是适合exists的优化

喜欢的同学可以关注我的公众号（db_arch）（Mysql数据库运维与架构设计）