##
提升数据库性能的终极指南：10张表JOIN SQL优化案例剖析
1、优化之前的SQL语句
SELECT f.encrypted_id FROM domaincompanymap `a`
INNER JOIN company `b` ON (b.id = a.company_id and b.deactivation_dt is null and b.activation_dt is not null)
INNER JOIN companyprofile `c` ON (c.id = a.companyprofile_id and c.is_active = 1)
INNER JOIN companycompanymap `e` ON (e.parent_company_id = a.company_id and e.domain_id = a.domain_id)
INNER JOIN company `f` ON (f.id = e.company_id and f.deactivation_dt is null and f.activation_dt is not null)
INNER JOIN companyprofile `g` ON (g.company_id = e.company_id and g.is_active = 1)
LEFT JOIN companydomainsubscription `k` ON (k.domain_id = a.domain_id and k.company_id = a.company_id and k.subscription_end_date > now())
INNER JOIN (SELECT a.companyprofile_id, min(a.user_id), b.domain_id FROM companyprofileusermap `a`
INNER JOIN userprofile `b` ON (b.user_id = a.user_id and b.deactivation_dt is null)
INNER JOIN user `c` ON (c.id = a.user_id and c.deactivation_dt is null) GROUP BY b.domain_id, a.companyprofile_id) `l` ON (l.companyprofile_id = a.companyprofile_id and l.domain_id = a.domain_id)
WHERE (a.domain_id = 936 AND a.account_type_lookup_id = 200 AND a.is_active = 1) LIMIT 1000 OFFSET 1000;执行时间
171 rows in set (3.96 sec)2、分析SQL语句
2.1、执行计划分析
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------------------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------------------+--------+----------+------------------------------+
| 1 | PRIMARY | a | NULL | ref | domaincompanymap_domainid_companyid_uix,domaincompanymap_domainid_createddt_nix,domaincompanymap_domainid_companyid_nix,domaincompanymap_domainid_companyid_typeid_nix,idx_domaincompanymap_companyid,domaincompanymap_is_active_domain_id_IDX,domaincompanymap_companyprofile_id_company_is_active_domain_IDX,domaincompanymap_domain_id_comapny_id_is_active_IDX,dcm_domain_id_company_id_account_sub_type_lookup_id_IDX,perf_domain_id_account_type_lookup_id | domaincompanymap_domainid_companyid_nix | 4 | const | 2466 | 10.00 | Using where; Using index |
| 1 | PRIMARY | e | NULL | ref | companycompanymap_company_parent_nix,companycompanymap_parentcompanyid_nix,companycompanymap_domain_id_IDX,companycompanymap_company_id_domain_id_IDX,idx_pcid_cid | companycompanymap_parentcompanyid_nix | 8 | balluun365.a.company_id | 1 | 5.00 | Using where |
| 1 | PRIMARY | c | NULL | eq_ref | PRIMARY | PRIMARY | 8 | balluun365.a.companyprofile_id | 1 | 10.00 | Using where |
| 1 | PRIMARY | g | NULL | ref | company_logo_nix,company_banner_nix,company_showroom_nix,companyprofile_company_id_IDX,companyprofile_profile_name_locale_code_IDX | companyprofile_company_id_IDX | 8 | balluun365.e.company_id | 1 | 10.00 | Using where |
| 1 | PRIMARY | b | NULL | eq_ref | PRIMARY | PRIMARY | 8 | balluun365.a.company_id | 1 | 9.00 | Using where |
| 1 | PRIMARY | f | NULL | eq_ref | PRIMARY | PRIMARY | 8 | balluun365.e.company_id | 1 | 9.00 | Using where |
| 1 | PRIMARY | k | NULL | ref | cds_is_active_domain_id_subscription_end_date_IDX,idx_sed_lstid_did_cid,idx_did_cid_lstid | idx_did_cid_lstid | 13 | const,balluun365.a.company_id | 1 | 100.00 | Using where |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 12 | balluun365.a.companyprofile_id,const | 10 | 100.00 | NULL |
| 2 | DERIVED | a | NULL | index | companyprofileusermap_user_id_IDX,idx_uid_cpid | idx_uid_cpid | 16 | NULL | 410705 | 100.00 | Using index; Using temporary |
| 2 | DERIVED | c | NULL | eq_ref | PRIMARY,user_deactivation_uix | PRIMARY | 8 | balluun365.a.user_id | 1 | 10.00 | Using where |
| 2 | DERIVED | b | NULL | ref | userprofile_userid_locale_nix,userprofile_user_id_IDX,userprofile_user_id_domain_id_image_id_IDX | userprofile_user_id_IDX | 8 | balluun365.a.user_id | 1 | 10.00 | Using where |
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------------------+--------+----------+------------------------------+
11 rows in set, 1 warning (0.21 sec)这里扫描行最多且可能存在问题的是1和9行，且第9行DERIVED有 Using temporary。
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: a
partitions: NULL
type: ref
possible_keys: domaincompanymap_domainid_companyid_uix,domaincompanymap_domainid_createddt_nix,domaincompanymap_domainid_companyid_nix,domaincompanymap_domainid_companyid_typeid_nix,idx_domaincompanymap_companyid,domaincompanymap_is_active_domain_id_IDX,domaincompanymap_companyprofile_id_company_is_active_domain_IDX,domaincompanymap_domain_id_comapny_id_is_active_IDX,dcm_domain_id_company_id_account_sub_type_lookup_id_IDX,perf_domain_id_account_type_lookup_id
key: domaincompanymap_domainid_companyid_nix
key_len: 4
ref: const
rows: 2466
filtered: 10.00
Extra: Using where; Using index
*************************** 9. row ***************************
id: 2
select_type: DERIVED
table: a
partitions: NULL
type: index
possible_keys: companyprofileusermap_user_id_IDX,idx_uid_cpid
key: idx_uid_cpid
key_len: 16
ref: NULL
rows: 410705
filtered: 100.00
Extra: Using index; Using temporary2.2、DERIVED中 Using temporary分析
mysql> explain SELECT a.companyprofile_id, min(a.user_id), b.domain_id FROM companyprofileusermap `a`
-> INNER JOIN userprofile `b` ON (b.user_id = a.user_id and b.deactivation_dt is null)
-> INNER JOIN user `c` ON (c.id = a.user_id and c.deactivation_dt is null) GROUP BY b.domain_id, a.companyprofile_id;
+----+-------------+-------+------------+--------+--------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+--------+----------+------------------------------+
| 1 | SIMPLE | a | NULL | index | companyprofileusermap_user_id_IDX,idx_uid_cpid | idx_uid_cpid | 16 | NULL | 410705 | 100.00 | Using index; Using temporary |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY,user_deactivation_uix | PRIMARY | 8 | balluun365.a.user_id | 1 | 10.00 | Using where |
| 1 | SIMPLE | b | NULL | ref | userprofile_userid_locale_nix,userprofile_user_id_IDX,userprofile_user_id_domain_id_image_id_IDX | userprofile_user_id_IDX | 8 | balluun365.a.user_id | 1 | 10.00 | Using where |
+----+-------------+-------+------------+--------+--------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+--------+----------+------------------------------+
3 rows in set, 1 warning (0.21 sec)验证了DERIVED的SQL语句，可以查看这里的Using temporary是由于GROUP BY b.domain_id, a.companyprofile_id引起的
2.3、mysql workbench分析
这里可以查看我之前的文章，非常好用，并且得到相同的结果。
3、改写SQL语句
3.1、去掉无意义的回表数据
SELECT a.companyprofile_id, min(a.user_id), b.domain_id FROM companyprofileusermap `a`
-> INNER JOIN userprofile `b` ON (b.user_id = a.user_id and b.deactivation_dt is null)
-> INNER JOIN user `c` ON (c.id = a.user_id and c.deactivation_dt is null) GROUP BY b.domain_id, a.companyprofile_id;这里的min(a.user_id)在SQL语句中并无二次使用，所以可以删除。
SELECT a.companyprofile_id, b.domain_id FROM companyprofileusermap `a`
-> INNER JOIN userprofile `b` ON (b.user_id = a.user_id and b.deactivation_dt is null)
-> INNER JOIN user `c` ON (c.id = a.user_id and c.deactivation_dt is null) GROUP BY b.domain_id, a.companyprofile_id;3.2、排序GROUP BY分析
这里group by对结果并无影响，只是分组排序，所以去掉group by，然后查看执行计划
mysql> explain SELECT a.companyprofile_id, min(a.user_id), b.domain_id FROM companyprofileusermap `a` INNER JOIN userprofile `b` ON (b.user_id = a.user_id and b.deactivation_dt is null) INNER JOIN user `c` ON (c.id = a.user_id and c.deactivation_dt is null);
+----+-------------+-------+------------+--------+--------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+--------+----------+--------------------------+
| 1 | SIMPLE | a | NULL | index | companyprofileusermap_user_id_IDX,idx_uid_cpid | idx_uid_cpid | 16 | NULL | 410705 | 100.00 | Using where; Using index |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY,user_deactivation_uix | PRIMARY | 8 | balluun365.a.user_id | 1 | 10.00 | Using where |
| 1 | SIMPLE | b | NULL | ref | userprofile_userid_locale_nix,userprofile_user_id_IDX,userprofile_user_id_domain_id_image_id_IDX | userprofile_user_id_IDX | 8 | balluun365.a.user_id | 1 | 10.00 | Using where |
+----+-------------+-------+------------+--------+--------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+--------+----------+--------------------------+
3 rows in set, 1 warning (0.21 sec)3.3、让group by回归
虽然group by对DERIVED的结果没有影响。但是会影响整体SQL语句，所以这里需要让group by通过 distinct结果集来完成。
change
f.encrypted_id
to
distinct f.encrypted_id3.4、查询结果集验证
UC真好用，仅查询时间不一致，数据一致。
3.5、查询时间验证
171 rows in set (0.27 sec)查询时间优化15倍，完成客户要求。
4、总结
查找SQL缓慢的原因是处理SQL优化的关键，用最简单的方法确定问题的原因，还是免费的，workbench是真不错。
优化方法，SQL的优化方法中最常见的就是索引的处理以及SQL语句的改写，几乎可以解决80%的SQL问题。
原文始发于微信公众号（库海无涯）：提升数据库性能的终极指南：10张表JOIN SQL优化案例剖析
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/281441.html
