<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                合規國際互聯網加速 OSASE為企業客戶提供高速穩定SD-WAN國際加速解決方案。 廣告
                >[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:任何數據庫層面的優化都抵不上應用系統的優化
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看