>[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:任何數據庫層面的優化都抵不上應用系統的優化
- 序言
- 為什么要編碼規范?
- 如何進行編碼規范?
- 編碼規范宏觀微觀細節
- PHP編碼規范
- 基礎規范
- 1 語法規范
- 2 變量命名規范
- 3 常量命名規范
- 4 類命名規范
- 5 函數命名規范
- 6 方法命名規范
- PSR-規范
- 基本代碼規范
- Tp項目規范
- TP命名規范
- Tp目錄規范
- Tp基礎目錄構架
- Tp項目開發思考
- Tp控制器規范
- 控制器構架
- Tp模型規范
- 模型性能優化
- Tp業務規范
- 返回結構規范
- 業務異常規范
- Tp輸出規范
- Restful API
- 模板渲染輸出
- Tp異常規范
- 異常碼狀態碼
- 異常輸出方式
- Tp驗證規范
- Tp路由規范
- Tp加密規范
- Password Hashing
- Tp緩存規范
- 常見數據緩存
- 緩存設計思考
- Tp日志規范
- 日志信息
- 日志分析
- Tp日志接管分析
- Tp性能優化
- vendor包規范
- 項目自動化思考
- 項目檢測告警思考
- 項目注釋規范
- Mysql設計規范
- 序言
- 命名規范
- 表及字段規范
- 索引規范
- 索引原理
- Sql規范
- 事務規范
- 讀寫分離
- 樂觀鎖悲觀鎖
- 數據庫審計
- 性能優化
- 查詢優化神器
- 慢查詢優化步驟
- 分庫分表、分區表
- 根據sql日志篩選數據
- 設計原則
- MongoDB規范
- MongoDB基礎
- MongoDB設計
- MongoDB安全性
- MongoDB備份
- 操作手冊規范
- API文檔說明規范
- 管理端操作手冊
- 用戶使用說明書
- 溯源項目構想