慢sql优化
2018-04-24 本文已影响0人
vale_nbg
背景：通过阿里云RDS后台，查看慢SQL，发现如下慢sql。
SELECT
T0.`id`,
T0.`instance_i_d`,
T0.`create_user_id`,
T0.`update_user_id`,
T0.`create_date`,
T0.`update_date`,
T0.`sales_man_tpl_id`,
T0.`order_id`,
T0.`channel_tpl_id`,
T0.`is_emerge`,
T1.`id`,
T1.`instance_i_d`,
T1.`create_user_id`,
T1.`update_user_id`,
T1.`create_date`,
T1.`update_date`,
T1.`name`,
T1.`contact`,
T1.`tel`,
T1.`wechat`,
T1.`delete_at`,
T2.`id`,
T2.`instance_i_d`,
T2.`create_user_id`,
T2.`update_user_id`,
T2.`create_date`,
T2.`update_date`,
T2.`name`,
T2.`identity`,
T2.`birthday`,
T2.`customer_sex`,
T2.`mobile`,
T2.`ali_pay`,
T2.`address`,
T2.`job_number`,
T2.`department`,
T2.`supervisor_tpl_id`,
T2.`staff_status`,
T2.`entry_work_date`,
T2.`regular_work_date`,
T2.`leave_work_date`,
T2.`remark`,
FROM `xxx_order` T0 LEFT OUTER JOIN `xxx_channel` T1 ON T1.`id` = T0.`channel_tpl_id`
LEFT OUTER JOIN `xxx_staff` T2 ON T2.`id` = T0.`sales_man_tpl_id`
GROUP BY T0.`id`
ORDER BY T0.`id` DESC
LIMIT 20
分析原因：
使用mysql explain
mysql> explain SELECT
T0.`id`,
T0.`instance_i_d`,
T0.`create_user_id`,
T0.`update_user_id`,
T0.`create_date`,
T0.`update_date`,
T0.`sales_man_tpl_id`,
T0.`order_id`,
T0.`channel_tpl_id`,
T0.`is_emerge`,
T1.`id`,
T1.`instance_i_d`,
T1.`create_user_id`,
T1.`update_user_id`,
T1.`create_date`,
T1.`update_date`,
T1.`name`,
T1.`contact`,
T1.`tel`,
T1.`wechat`,
T1.`delete_at`,
T2.`id`,
T2.`instance_i_d`,
T2.`create_user_id`,
T2.`update_user_id`,
T2.`create_date`,
T2.`update_date`,
T2.`name`,
T2.`identity`,
T2.`birthday`,
T2.`customer_sex`,
T2.`mobile`,
T2.`ali_pay`,
T2.`address`,
T2.`job_number`,
T2.`department`,
T2.`supervisor_tpl_id`,
T2.`staff_status`,
T2.`entry_work_date`,
T2.`regular_work_date`,
T2.`leave_work_date`,
T2.`remark`,
FROM `xxx_order` T0 LEFT OUTER JOIN `xxx_channel` T1 ON T1.`id` = T0.`channel_tpl_id`
LEFT OUTER JOIN `xxx_staff` T2 ON T2.`id` = T0.`sales_man_tpl_id`
GROUP BY T0.`id`
ORDER BY T0.`id` DESC
LIMIT 20;
+----+-------------+-------+--------+------------------------------------+---------+---------+--------------------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------+---------+---------+--------------------------------+------+----------------------------------------------------+
| 1 | SIMPLE | T0 | ALL | PRIMARY,fg_visa_order_instance_i_d | NULL | NULL | NULL | 3464 | Using temporary; Using filesort |
| 1 | SIMPLE | T1 | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | T2 | eq_ref | PRIMARY | PRIMARY | 8 | xxx.T0.sales_man_tpl_id | 1 | NULL |
+----+-------------+-------+--------+------------------------------------+---------+---------+--------------------------------+------+----------------------------------------------------+
7 rows in set (0.01 sec)
这里可知left join T1表的时候，没有用索引，所以整个sql的执行时间被拖慢了。但是很奇怪的是T1和T0的关系，T2和T0的关系，完全一致，为什么T2和T0使用外键查询，而T1却不使用？
查看T1 表，然后insert一条数据，数据量从6 -> 7
mysql> select * from xxx_channel;
+----+--------------+----------------+----------------+---------------------+---------------------+--------------+---------+-------------+--------------+-----------+
| id | instance_i_d | create_user_id | update_user_id | create_date | update_date | name | contact | tel | wechat | delete_at |
+----+--------------+----------------+----------------+---------------------+---------------------+--------------+---------+-------------+--------------+-----------+
| 1 | 1484 | 1484 | 1484 | 2018-03-07 17:18:05 | 2018-03-07 17:18:05 | 自有渠道 | | | | 0 |
| 2 | 1485 | 1485 | 1485 | 2018-03-07 20:55:18 | 2018-03-07 20:55:18 | 线下渠道 | 小李 | 15688779966 | 微微儿额 | 0 |
| 3 | 1429 | 1429 | 1429 | 2018-04-09 11:13:28 | 2018-04-09 11:13:28 | 杜微信 | | | | 0 |
| 4 | 1429 | 1429 | 1429 | 2018-04-15 17:05:11 | 2018-04-15 17:05:11 | 同行小周 | 小周 | | | 0 |
| 5 | 1429 | 1429 | 1429 | 2018-04-15 17:05:26 | 2018-04-15 17:05:26 | 同行小邵 | 小邵 | | | 0 |
| 6 | 1429 | 1429 | 1429 | 2018-04-15 17:05:48 | 2018-04-15 17:05:48 | 不详 | 不详 | | | 0 |
+----+--------------+----------------+----------------+---------------------+---------------------+--------------+---------+-------------+--------------+-----------+
6 rows in set (0.00 sec)
mysql> insert into fg_channel (instance_i_d, create_date, update_date, name, delete_at) values(0, now(), now(), "for_slow_sql", 1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from xxx_channel;
+----+--------------+----------------+----------------+---------------------+---------------------+--------------+---------+-------------+--------------+-----------+
| id | instance_i_d | create_user_id | update_user_id | create_date | update_date | name | contact | tel | wechat | delete_at |
+----+--------------+----------------+----------------+---------------------+---------------------+--------------+---------+-------------+--------------+-----------+
| 1 | 1484 | 1484 | 1484 | 2018-03-07 17:18:05 | 2018-03-07 17:18:05 | 自有渠道 | | | | 0 |
| 2 | 1485 | 1485 | 1485 | 2018-03-07 20:55:18 | 2018-03-07 20:55:18 | 线下渠道 | 小李 | 15688779966 | 微微儿额 | 0 |
| 3 | 1429 | 1429 | 1429 | 2018-04-09 11:13:28 | 2018-04-09 11:13:28 | 杜微信 | | | | 0 |
| 4 | 1429 | 1429 | 1429 | 2018-04-15 17:05:11 | 2018-04-15 17:05:11 | 同行小周 | 小周 | | | 0 |
| 5 | 1429 | 1429 | 1429 | 2018-04-15 17:05:26 | 2018-04-15 17:05:26 | 同行小邵 | 小邵 | | | 0 |
| 6 | 1429 | 1429 | 1429 | 2018-04-15 17:05:48 | 2018-04-15 17:05:48 | 不详 | 不详 | | | 0 |
| 7 | 0 | NULL | NULL | 2018-04-24 13:41:02 | 2018-04-24 13:41:02 | for_slow_sql | | | | 1 |
+----+--------------+----------------+----------------+---------------------+---------------------+--------------+---------+-------------+--------------+-----------+
再次执行explain
mysql> explain SELECT
T0.`id`,
T0.`instance_i_d`,
T0.`create_user_id`,
T0.`update_user_id`,
T0.`create_date`,
T0.`update_date`,
T0.`sales_man_tpl_id`,
T0.`order_id`,
T0.`channel_tpl_id`,
T0.`is_emerge`,
T1.`id`,
T1.`instance_i_d`,
T1.`create_user_id`,
T1.`update_user_id`,
T1.`create_date`,
T1.`update_date`,
T1.`name`,
T1.`contact`,
T1.`tel`,
T1.`wechat`,
T1.`delete_at`,
T2.`id`,
T2.`instance_i_d`,
T2.`create_user_id`,
T2.`update_user_id`,
T2.`create_date`,
T2.`update_date`,
T2.`name`,
T2.`identity`,
T2.`birthday`,
T2.`customer_sex`,
T2.`mobile`,
T2.`ali_pay`,
T2.`address`,
T2.`job_number`,
T2.`department`,
T2.`supervisor_tpl_id`,
T2.`staff_status`,
T2.`entry_work_date`,
T2.`regular_work_date`,
T2.`leave_work_date`,
T2.`remark`,
FROM `xxx_order` T0 LEFT OUTER JOIN `xxx_channel` T1 ON T1.`id` = T0.`channel_tpl_id`
LEFT OUTER JOIN `xxx_staff` T2 ON T2.`id` = T0.`sales_man_tpl_id`
GROUP BY T0.`id`
ORDER BY T0.`id` DESC
LIMIT 20;;
+----+-------------+-------+--------+------------------------------------+---------+---------+-------------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------+---------+---------+-------------------------+------+-------+
| 1 | SIMPLE | T0 | index | PRIMARY,fg_visa_order_instance_i_d | PRIMARY | 8 | NULL | 20 | NULL |
| 1 | SIMPLE | T1 | eq_ref | PRIMARY | PRIMARY | 8 | xxx.T0.channel_tpl_id | 1 | NULL |
| 1 | SIMPLE | T2 | eq_ref | PRIMARY | PRIMARY | 8 | xxx.T0.sales_man_tpl_id | 1 | NULL |
+----+-------------+-------+--------+------------------------------------+---------+---------+-------------------------+------+-------+
7 rows in set (0.00 sec)
神奇的是这次使用外键关联了，猜测mysql根据数据库的数量自己决定是否使用外键查询。
