mysql子查询分析_Mysql子查询分析一

首先介绍几个常用的优化分析命令工具，刚开始写博客不习惯写详细说明

EXPLAIN或DESC(describe缩写)SQL执行分析计划工具

EXPLAIN的EXTENDED选项能够在原有的EXPLAIN基础上提供“查询优化SQL”，执行EXPLAIN EXTENDED SELECT...之后使用SHOW WARNINGS命令查看Mysql对原SQL的优化

配置profiling来分析查询SQL的执行过程

写道

show variables like "%profil%";

set profiling = 1;

select @@profiling;

show profiles;    //查看操作记录

show profile for query 1;   //查看具体的操作分析过程

接下来进入正题，之前写的一个报表sql中包含了子查询，刚开始还没发现什么问题，最近发现数据量也就3、4千的样子查询速度竟然达到2S左右。

简化SQL:

SELECT

count(1)

FROM

tb_mpsqd AS sqd1

LEFT JOIN tb_customer AS cust1 ON sqd1.customer_id = cust1.customer_id

LEFT JOIN t_s_depart AS depart1 ON sqd1.sys_org_code = depart1.org_code

LEFT JOIN tb_mpzqsqd AS zqsqd1 ON sqd1.mpsqd_id = zqsqd1.mpsqd_id AND sqd1.delayed_times = zqsqd1.delayed_times

LEFT JOIN tb_mppgd AS pgd1 ON sqd1.mpsqd_id = pgd1.mpsqd_id

LEFT JOIN (

select group_concat(distinct(a.classify_name)) as classify_name, group_concat(b.pawn_name) as pawn_name, group_concat(b.stand_by separator ' ') as wp_stand_by, b.mppgd_id from tb_classify as a inner join tb_mppgd_base as b on a.classify_id = b.classify_id GROUP BY b.mppgd_id

) AS pgdbase1 ON pgdbase1.mppgd_id = pgd1.mppgd_id

执行计划如下：

+----+-------------+------------+--------+-----------------+--------------+---------+-------------------------+------+----------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+--------+-----------------+--------------+---------+-------------------------+------+----------------+

| 1 | PRIMARY | sqd1 | ALL | NULL | NULL | NULL | NULL | 1329 | |

| 1 | PRIMARY | cust1 | eq_ref | PRIMARY | PRIMARY | 4 | tdperp.sqd1.customer_id | 1 | Using index |

| 1 | PRIMARY | depart1 | ALL | NULL | NULL | NULL | NULL | 104 | |

| 1 | PRIMARY | zqsqd1 | ref | mpsqd_id | mpsqd_id | 4 | tdperp.sqd1.mpsqd_id | 1 | |

| 1 | PRIMARY | pgd1 | ref | mppgd_id_idx | mppgd_id_idx | 4 | tdperp.sqd1.mpsqd_id | 1 | Using index |

| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 1116 | |

| 2 | DERIVED | b | ALL | idx_classify_id | NULL | NULL | NULL | 1446 | Using filesort |

| 2 | DERIVED | a | eq_ref | PRIMARY | PRIMARY | 4 | tdperp.b.classify_id | 1 | Using where |

+----+-------------+------------+--------+-----------------+--------------+---------+-------------------------+------+----------------+

分析：

首先此报表业务关联了7张表，涉及到一些子表的属性group_concat和group by操作

sql中存在子查询，通过执行计划看到有DERIVED的查询类型，存储在临时表中。还存在一个Using filesort文件排序问题(group by引起)

通过拆分查询，并没有发现任何影响速度的问题。只有当子查询与主查询整合时速度就变慢

通过简单的测试后定位问题主要在于子查询这里：

当使用子查询时，mysql需要创建临时表来存储并处理子查询的数据(filesort)，且在本例中子查询的结果集是大字符串内容

子查询优化——解决filesort的问题

SELECT group_concat(DISTINCT(select classify_name from tb_classify a where b.classify_id = a.classify_id)) AS classify_name,

group_concat(b.pawn_name) AS pawn_name,

group_concat(b.stand_by SEPARATOR ' ') AS wp_stand_by,

b.mppgd_id

FROM tb_mppgd_base AS b GROUP BY b.mppgd_id;

+----+--------------------+-------+--------+---------------+--------------+---------+----------------------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------------+-------+--------+---------------+--------------+---------+----------------------+------+-------------+

| 1 | PRIMARY | b | index | NULL | mppgd_id_idx | 4 | NULL | 1446 | |

| 2 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4 | tdperp.b.classify_id | 1 | Using where |

+----+--------------------+-------+--------+---------------+--------------+---------+----------------------+------+-------------+

子查询优化——解决速度问题

SELECT

count(1)

FROM

tb_mpsqd AS sqd1

LEFT JOIN tb_customer AS cust1 ON sqd1.customer_id = cust1.customer_id

LEFT JOIN t_s_depart AS depart1 ON sqd1.sys_org_code = depart1.org_code

LEFT JOIN tb_mpzqsqd AS zqsqd1 ON sqd1.mpsqd_id = zqsqd1.mpsqd_id AND sqd1.delayed_times = zqsqd1.delayed_times

LEFT JOIN tb_mppgd AS pgd1 ON sqd1.mpsqd_id = pgd1.mpsqd_id

LEFT JOIN tb_mppgd_base AS b ON b.mppgd_id = pgd1.mppgd_id

LEFT JOIN tb_classify AS c ON b.classify_id = c.classify_id

GROUP BY sqd1.mpsqd_id;

+----+-------------+---------+--------+-----------------+-----------------+---------+--------------------------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------+--------+-----------------+-----------------+---------+--------------------------+------+-------------+

| 1 | SIMPLE | sqd1 | index | NULL | PRIMARY | 4 | NULL | 1329 | |

| 1 | SIMPLE | cust1 | eq_ref | PRIMARY | PRIMARY | 4 | tdperp.sqd1.customer_id | 1 | Using index |

| 1 | SIMPLE | depart1 | ref | unique_org_code | unique_org_code | 195 | tdperp.sqd1.sys_org_code | 1 | Using index |

| 1 | SIMPLE | zqsqd1 | ref | mpsqd_id | mpsqd_id | 4 | tdperp.sqd1.mpsqd_id | 1 | |

| 1 | SIMPLE | pgd1 | ref | mppgd_id_idx | mppgd_id_idx | 4 | tdperp.sqd1.mpsqd_id | 1 | Using index |

| 1 | SIMPLE | b | ref | mppgd_id_idx | mppgd_id_idx | 4 | tdperp.pgd1.mppgd_id | 1 | |

| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | tdperp.b.classify_id | 1 | Using index |

+----+-------------+---------+--------+-----------------+-----------------+---------+--------------------------+------+-------------+

优化后速度从2000ms提升到300ms以下。

总结：

子查询的使用需要慎用，在Mysql5.6之后的版本已经做了优化，可以不考虑。Mysql5.6之前的版本建议尽量把子查询替换为Join关联查询。

尽量在join关联的属性上建立索引

使用ORDER BY null来避免filesort