一个mysql比较典型的子查询改join优化

一个mysql比较典型的子查询改join优化

早些年当作sql server /orcal sql的dba,后来进入互联网用mysql比较高，早年各项目重度依赖sql

近年几搞大数据，mysql也是用，不涉及优化，整体mysql在行业内是被弱化了的，因为针对各场景的其他sql/nosql组件可以选择

量不大，不用优化，会读写就可以了。量大，换别的方案，而对sql的优化很少有像早期有专值的DBA 针对性的维护和操作

最近业务方的数据慢查询提到了我这里，建议能不能抽空作点优化

这又算是老本行了，就协助简单优化了一部分

其实很多慢查询优化，说白了是mysql的问题，mysql的查询优化相比orcal和sql server 作的太差 不得不用一些很难看的技巧来适配mysql低下的处理能力

表结构就不放了
只放sql和查询计划

重点是把子查询去掉，替换为join结构

原始查询计划

explain SELECT
ed_set_interval, s.seed_set_status, s.creator_id, s.created_at, s.updated_at, s.expired_at, s.is_fulled, u.username, u.user_email d_set_platform, s.seed_set_price, s.se
    -> FROM
    -> sms_seed_set s
    -> LEFT JOIN sms_user u
    -> ON s.creator_id = u.id
    -> LEFT JOIN
    -> (SELECT sms_seed_set_id, count(*) AS count FROM sms_seed WHERE is_deleted = 'NO' GROUP BY sms_seed_set_id) AS ssc
    -> ON s.id = ssc.sms_seed_set_id
    -> WHERE ( s.is_deleted = 'NO' ) order by id desc LIMIT 20;
+------+-------------+------------+--------+---------------+-------------------------+---------+-----------------------+---------+--------------------------+
| id   | select_type | table      | type   | possible_keys | key                     | key_len | ref                   | rows    | Extra                    |
+------+-------------+------------+--------+---------------+-------------------------+---------+-----------------------+---------+--------------------------+
|    1 | PRIMARY     | s          | index  | NULL          | PRIMARY                 | 4       | NULL                  |       1 | Using where              |
|    1 | PRIMARY     | u          | eq_ref | PRIMARY       | PRIMARY                 | 4       | sms_test.s.creator_id |       1 |                          |
|    1 | PRIMARY     | <derived2> | ref    | key0          | key0                    | 5       | sms_test.s.id         |   14223 | Using where              |
|    2 | DERIVED     | sms_seed   | index  | NULL          | idx_seed_set_is_deleted | 6       | NULL                  | 1422360 | Using where; Using index |
+------+-------------+------------+--------+---------------+-------------------------+---------+-----------------------+---------+--------------------------+

优化后

explain SELECT ifnull(count(*),0) as seedCount,s.id, s.seed_set_source, s.seed_set_name, s.seed_set_description, s.seed_set_type, s.seed_set_platform, s.seed_set_price, s.seed_set_interval, s.seed_set_status, s.creator_id, s.created_at, s.updated_at, s.expired_at, s.is_fulled, u.username, u.user_email  FROM sms_seed ssc  LEFT JOIN sms_seed_set s ON ssc.sms_seed_set_id =s.id
LEFT JOIN sms_user u ON s.creator_id = u.id 
WHERE ( s.is_deleted = 'NO' and ssc.is_deleted = 'NO') 
GROUP BY sms_seed_set_id order by s.id desc LIMIT 20;

+------+-------------+-------+--------+-------------------------+-------------------------+---------+-----------------------+------+----------------------------------------------+
| id   | select_type | table | type   | possible_keys           | key                     | key_len | ref                   | rows | Extra                                        |
+------+-------------+-------+--------+-------------------------+-------------------------+---------+-----------------------+------+----------------------------------------------+
|    1 | SIMPLE      | s     | ALL    | PRIMARY                 | NULL                    | NULL    | NULL                  |   21 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | u     | eq_ref | PRIMARY                 | PRIMARY                 | 4       | sms_test.s.creator_id |    1 |                                              |
|    1 | SIMPLE      | ssc   | ref    | idx_seed_set_is_deleted | idx_seed_set_is_deleted | 6       | sms_test.s.id,const   | 4063 | Using where; Using index                     |
+------+-------------+-------+--------+-------------------------+-------------------------+---------+-----------------------+------+----------------------------------------------+

隐约记得mysql有个索引应用优先级的的问题，通过调整sql把大表的索引优先级调高，我习惯把大表提前

从技术角度以上优化已经完成任务，但又要结合业务，业务对每个sql是有主副表的，要求再调下join几张表的先后顺序，把大表调后

验证下调整table顺序后的查询计划是否变更，结果一致

explain SELECT ifnull(count(*),0) as seedCount,s.id, s.seed_set_source, s.seed_set_name, s.seed_set_description, s.seed_set_type, s.seed_set_platform, s.seed_set_price, s.seed_set_interval, s.seed_set_status, s.creator_id, s.created_at, s.updated_at, s.expired_at, s.is_fulled, u.username, u.user_email  
FROM sms_seed_set s  LEFT JOIN sms_seed ssc ON ssc.sms_seed_set_id =s.id
LEFT JOIN sms_user u ON s.creator_id = u.id 
WHERE ( s.is_deleted = 'NO' and ssc.is_deleted = 'NO') GROUP BY sms_seed_set_id order by s.id desc LIMIT 20;
+------+-------------+-------+--------+-------------------------+-------------------------+---------+-----------------------+-------+----------------------------------------------+
| id   | select_type | table | type   | possible_keys           | key                     | key_len | ref                   | rows  | Extra                                        |
+------+-------------+-------+--------+-------------------------+-------------------------+---------+-----------------------+-------+----------------------------------------------+
|    1 | SIMPLE      | s     | ALL    | PRIMARY                 | NULL                    | NULL    | NULL                  |    23 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | u     | eq_ref | PRIMARY                 | PRIMARY                 | 4       | sms_test.s.creator_id |     1 |                                              |
|    1 | SIMPLE      | ssc   | ref    | idx_seed_set_is_deleted | idx_seed_set_is_deleted | 6       | sms_test.s.id,const   | 78177 | Using where; Using index                     |
+------+-------------+-------+--------+-------------------------+-------------------------+---------+-----------------------+-------+----------------------------------------------+