MySQL数值扩大一百倍_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 | | NULL | index_subquery | | | 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 | | 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数据库运维与架构设计)