慢查詢優化步驟 · 項目編碼規范

>[success]### 慢查詢優化基本步驟 0.先運行看看是否真的很慢，注意設置SQL_NO_CACHE 1.where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個字段分別查詢，看哪個字段的區分度最高 2.explain查看執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢） 3.order by limit 形式的sql語句讓排序的表優先查 4.了解業務方使用場景 5.加索引時參照建索引的幾大原則 6.觀察結果，不符合預期繼續從0分析 >[success]### 幾個慢查詢案例下面幾個例子詳細解釋了如何分析和優化慢查詢。 #### 復雜語句寫法很多情況下，我們寫SQL只是為了實現功能，這只是第一步，不同的語句書寫方式對于效率往往有本質的差別，這要求我們對mysql的執行計劃和索引原則有非常清楚的認識，請看下面的語句： ~~~sql select distinct cert.emp_id from cm_log cl inner join ( select emp.id as emp_id, emp_cert.id as cert_id from employee emp left join emp_certificate emp_cert on emp.id = emp_cert.emp_id where emp.is_deleted=0 ) cert on ( cl.ref_table='Employee' and cl.ref_oid= cert.emp_id ) or ( cl.ref_table='EmpCertificate' and cl.ref_oid= cert.cert_id ) where cl.last_upd_date >='2013-11-07 15:03:00' and cl.last_upd_date<='2013-11-08 16:00:00'; ~~~ 0.先運行一下，53條記錄 1.87秒，又沒有用聚合語句，比較慢 ~~~sql 53 rows in set (1.87 sec) ~~~ 1.explain ~~~sql +----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+ | 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where; Using temporary | | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 63727 | Using where; Using join buffer | | 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 13317 | Using where | | 2 | DERIVED | emp_cert | ref | emp_certificate_empid | emp_certificate_empid | 4 | meituanorg.emp.id | 1 | Using index | +----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+ ~~~ 簡述一下執行計劃，首先mysql根據idx\_last\_upd\_date索引掃描cm\_log表獲得379條記錄；然后查表掃描了63727條記錄，分為兩部分，derived表示構造表，也就是不存在的表，可以簡單理解成是一個語句形成的結果集，后面的數字表示語句的ID。derived2表示的是ID = 2的查詢構造了虛擬表，并且返回了63727條記錄。我們再來看看ID = 2的語句究竟做了寫什么返回了這么大量的數據，首先全表掃描employee表13317條記錄，然后根據索引emp\_certificate\_empid關聯emp\_certificate表，rows = 1表示，每個關聯都只鎖定了一條記錄，效率比較高。獲得后，再和cm\_log的379條記錄根據規則關聯。從執行過程上可以看出返回了太多的數據，返回的數據絕大部分cm\_log都用不到，因為cm\_log只鎖定了379條記錄。如何優化呢？可以看到我們在運行完后還是要和cm\_log做join,那么我們能不能之前和cm\_log做join呢？仔細分析語句不難發現，其基本思想是如果cm\_log的ref\_table是EmpCertificate就關聯emp\_certificate表，如果ref\_table是Employee就關聯employee表，我們完全可以拆成兩部分，并用union連接起來，注意這里用union，而不用union all是因為原語句有“distinct”來得到唯一的記錄，而union恰好具備了這種功能。如果原語句中沒有distinct不需要去重，我們就可以直接使用union all了，因為使用union需要去重的動作，會影響SQL性能。優化過的語句如下： ~~~sql select emp.id from cm_log cl inner join employee emp on cl.ref_table = 'Employee' and cl.ref_oid = emp.id where cl.last_upd_date >='2013-11-07 15:03:00' and cl.last_upd_date<='2013-11-08 16:00:00' and emp.is_deleted = 0 union select emp.id from cm_log cl inner join emp_certificate ec on cl.ref_table = 'EmpCertificate' and cl.ref_oid = ec.id inner join employee emp on emp.id = ec.emp_id where cl.last_upd_date >='2013-11-07 15:03:00' and cl.last_upd_date<='2013-11-08 16:00:00' and emp.is_deleted = 0 ~~~ 4.不需要了解業務場景，只需要改造的語句和改造之前的語句保持結果一致 5.現有索引可以滿足，不需要建索引 6.用改造后的語句實驗一下，只需要10ms 降低了近200倍！ ~~~sql +----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+ | 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where | | 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where | | 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where | | 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | | | 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+ 53 rows in set (0.01 sec) ~~~ #### 明確應用場景舉這個例子的目的在于顛覆我們對列的區分度的認知，一般上我們認為區分度越高的列，越容易鎖定更少的記錄，但在一些特殊的情況下，這種理論是有局限性的。 ~~~sql select * from stage_poi sp where sp.accurate_result=1 and ( sp.sync_status=0 or sp.sync_status=2 or sp.sync_status=4 ); ~~~ 0.先看看運行多長時間,951條數據6.22秒，真的很慢。 ~~~sql 951 rows in set (6.22 sec) ~~~ 1.先explain，rows達到了361萬，type = ALL表明是全表掃描。 ~~~sql +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | sp | ALL | NULL | NULL | NULL | NULL | 3613155 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ ~~~ 2.所有字段都應用查詢返回記錄數，因為是單表查詢 0已經做過了951條。 3.讓explain的rows 盡量逼近951。看一下accurate\_result = 1的記錄數： ~~~sql select count(*),accurate_result from stage_poi group by accurate_result; +----------+-----------------+ | count(*) | accurate_result | +----------+-----------------+ | 1023 | -1 | | 2114655 | 0 | | 972815 | 1 | +----------+-----------------+ ~~~ 我們看到accurate\_result這個字段的區分度非常低，整個表只有-1,0,1三個值，加上索引也無法鎖定特別少量的數據。再看一下sync\_status字段的情況： ~~~sql select count(*),sync_status from stage_poi group by sync_status; +----------+-------------+ | count(*) | sync_status | +----------+-------------+ | 3080 | 0 | | 3085413 | 3 | +----------+-------------+ ~~~ 同樣的區分度也很低，根據理論，也不適合建立索引。問題分析到這，好像得出了這個表無法優化的結論，兩個列的區分度都很低，即便加上索引也只能適應這種情況，很難做普遍性的優化，比如當sync\_status 0、3分布的很平均，那么鎖定記錄也是百萬級別的。 4.找業務方去溝通，看看使用場景。業務方是這么來使用這個SQL語句的，每隔五分鐘會掃描符合條件的數據，處理完成后把sync\_status這個字段變成1,五分鐘符合條件的記錄數并不會太多，1000個左右。了解了業務方的使用場景后，優化這個SQL就變得簡單了，因為業務方保證了數據的不平衡，如果加上索引可以過濾掉絕大部分不需要的數據。 5.根據建立索引規則，使用如下語句建立索引 ~~~sql alter table stage_poi add index idx_acc_status(accurate_result,sync_status); ~~~ 6.觀察預期結果,發現只需要200ms，快了30多倍。 ~~~sql 952 rows in set (0.20 sec) ~~~ 我們再來回顧一下分析問題的過程，單表查詢相對來說比較好優化，大部分時候只需要把where條件里面的字段依照規則加上索引就好，如果只是這種“無腦”優化的話，顯然一些區分度非常低的列，不應該加索引的列也會被加上索引，這樣會對插入、更新性能造成嚴重的影響，同時也有可能影響其它的查詢語句。所以我們第4步調差SQL的使用場景非常關鍵，我們只有知道這個業務場景，才能更好地輔助我們更好的分析和優化查詢語句。 #### 無法優化的語句 ~~~sql select c.id, c.name, c.position, c.sex, c.phone, c.office_phone, c.feature_info, c.birthday, c.creator_id, c.is_keyperson, c.giveup_reason, c.status, c.data_source, from_unixtime(c.created_time) as created_time, from_unixtime(c.last_modified) as last_modified, c.last_modified_user_id from contact c inner join contact_branch cb on c.id = cb.contact_id inner join branch_user bu on cb.branch_id = bu.branch_id and bu.status in ( 1, 2) inner join org_emp_info oei on oei.data_id = bu.user_id and oei.node_left >= 2875 and oei.node_right <= 10802 and oei.org_category = - 1 order by c.created_time desc limit 0 , 10; ~~~ 還是幾個步驟。 0.先看語句運行多長時間，10條記錄用了13秒，已經不可忍受。 ~~~sql 10 rows in set (13.06 sec) ~~~ 1.explain ~~~sql +----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+ | 1 | SIMPLE | oei | ref | idx_category_left_right,idx_data_id | idx_category_left_right | 5 | const | 8849 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | bu | ref | PRIMARY,idx_userid_status | idx_userid_status | 4 | meituancrm.oei.data_id | 76 | Using where; Using index | | 1 | SIMPLE | cb | ref | idx_branch_id,idx_contact_branch_id | idx_branch_id | 4 | meituancrm.bu.branch_id | 1 | | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 108 | meituancrm.cb.contact_id | 1 | | +----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+ ~~~ 從執行計劃上看，mysql先查org\_emp\_info表掃描8849記錄，再用索引idx\_userid\_status關聯branch\_user表，再用索引idx\_branch\_id關聯contact\_branch表，最后主鍵關聯contact表。 rows返回的都非常少，看不到有什么異常情況。我們在看一下語句，發現后面有order by + limit組合，會不會是排序量太大搞的？于是我們簡化SQL，去掉后面的order by 和 limit，看看到底用了多少記錄來排序。 ~~~sql select count(*) from contact c inner join contact_branch cb on c.id = cb.contact_id inner join branch_user bu on cb.branch_id = bu.branch_id and bu.status in ( 1, 2) inner join org_emp_info oei on oei.data_id = bu.user_id and oei.node_left >= 2875 and oei.node_right <= 10802 and oei.org_category = - 1 +----------+ | count(*) | +----------+ | 778878 | +----------+ 1 row in set (5.19 sec) ~~~ 發現排序之前居然鎖定了778878條記錄，如果針對70萬的結果集排序，將是災難性的，怪不得這么慢，那我們能不能換個思路，先根據contact的created\_time排序，再來join會不會比較快呢？于是改造成下面的語句，也可以用straight\_join來優化： ~~~ select c.id, c.name, c.position, c.sex, c.phone, c.office_phone, c.feature_info, c.birthday, c.creator_id, c.is_keyperson, c.giveup_reason, c.status, c.data_source, from_unixtime(c.created_time) as created_time, from_unixtime(c.last_modified) as last_modified, c.last_modified_user_id from contact c where exists ( select 1 from contact_branch cb inner join branch_user bu on cb.branch_id = bu.branch_id and bu.status in ( 1, 2) inner join org_emp_info oei on oei.data_id = bu.user_id and oei.node_left >= 2875 and oei.node_right <= 10802 and oei.org_category = - 1 where c.id = cb.contact_id ) order by c.created_time desc limit 0 , 10; ~~~ 驗證一下效果預計在1ms內，提升了13000多倍！ ~~~sql 10 rows in set (0.00 sec) ~~~ 本以為至此大工告成，但我們在前面的分析中漏了一個細節，先排序再join和先join再排序理論上開銷是一樣的，為何提升這么多是因為有一個limit！大致執行過程是：mysql先按索引排序得到前10條記錄，然后再去join過濾，當發現不夠10條的時候，再次去10條，再次join，這顯然在內層join過濾的數據非常多的時候，將是災難的，極端情況，內層一條數據都找不到，mysql還傻乎乎的每次取10條，幾乎遍歷了這個數據表！用不同參數的SQL試驗下： ~~~sql select sql_no_cache c.id, c.name, c.position, c.sex, c.phone, c.office_phone, c.feature_info, c.birthday, c.creator_id, c.is_keyperson, c.giveup_reason, c.status, c.data_source, from_unixtime(c.created_time) as created_time, from_unixtime(c.last_modified) as last_modified, c.last_modified_user_id from contact c where exists ( select 1 from contact_branch cb inner join branch_user bu on cb.branch_id = bu.branch_id and bu.status in ( 1, 2) inner join org_emp_info oei on oei.data_id = bu.user_id and oei.node_left >= 2875 and oei.node_right <= 2875 and oei.org_category = - 1 where c.id = cb.contact_id ) order by c.created_time desc limit 0 , 10; Empty set (2 min 18.99 sec) ~~~ 2 min 18.99 sec！比之前的情況還糟糕很多。由于mysql的nested loop機制，遇到這種情況，基本是無法優化的。這條語句最終也只能交給應用系統去優化自己的邏輯了。通過這個例子我們可以看到，并不是所有語句都能優化，而往往我們優化時，由于SQL用例回歸時落掉一些極端情況，會造成比原來還嚴重的后果。所以，第一：不要指望所有語句都能通過SQL優化，第二：不要過于自信，只針對具體case來優化，而忽略了更復雜的情況。慢查詢的案例就分析到這兒，以上只是一些比較典型的案例。我們在優化過程中遇到過超過1000行，涉及到16個表join的“垃圾SQL”，也遇到過線上線下數據庫差異導致應用直接被慢查詢拖死，也遇到過varchar等值比較沒有寫單引號，還遇到過笛卡爾積查詢直接把從庫搞死。再多的案例其實也只是一些經驗的積累，如果我們熟悉查詢優化器、索引的內部原理，那么分析這些案例就變得特別簡單了。 >[danger]Tips:任何數據庫層面的優化都抵不上應用系統的優化