mysql如何分析sql执行效率和进行效率优化
【0】如何分析mysql中sql執(zhí)行較慢的問(wèn)題
- 步驟1、觀察，至少跑一天，看看生產(chǎn)的慢sql情況；
- 步驟2、開(kāi)啟慢查詢?nèi)罩?#xff0c;設(shè)置閾值，比如超過(guò)5秒鐘就是慢sql， 并將它抓取出來(lái)；
- 步驟3、explain+慢sql分析；
- 步驟4、show profile；（推薦）
- 步驟5、運(yùn)維經(jīng)理或dba，進(jìn)行sql數(shù)據(jù)庫(kù)服務(wù)器的參數(shù)調(diào)優(yōu)；（不推薦）
【總結(jié)】?
- 總結(jié)1、慢查詢的開(kāi)啟并捕獲；
- 總結(jié)2、explain + 慢sql分析；
- 總結(jié)3、show profile 查詢sql在mysql 服務(wù)器里面的執(zhí)行細(xì)節(jié)和聲明周期情況；
- 總結(jié)4、sql數(shù)據(jù)庫(kù)服務(wù)器的參數(shù)調(diào)優(yōu)；
==============================================================================================
【1】exists + order by + group by 優(yōu)化?
1.1、查詢優(yōu)化：使用 explain 查看執(zhí)行計(jì)劃，看是否可以基于索引查詢；
1.2、小表驅(qū)動(dòng)大表：當(dāng)兩個(gè)表做連接操作時(shí)， 其實(shí)就是雙層for循環(huán)， 小表驅(qū)動(dòng)大表的意思是， 小表（小數(shù)據(jù)集的表）放在第1層循環(huán)，大表（大數(shù)據(jù)集的表）放在第2層循環(huán)；
【補(bǔ)充】關(guān)于exists 語(yǔ)法 與 in 的區(qū)別：
exists語(yǔ)法：把 where id in 換位 where ?exists 即可；
select ... from tbl where exists (subquery);exists語(yǔ)法可以理解為：將主查詢的數(shù)據(jù)，放到子查詢中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果 true 或 false， 來(lái)決定主查詢的數(shù)據(jù)結(jié)果是否保留；
關(guān)于exists的提示：
- 提示1：exists(subquery) 只返回true或false， 因此子查詢中的select * 也可以是 select 1 或其他， 官方說(shuō)法是實(shí)際執(zhí)行時(shí)會(huì)忽略 select 清單，因此沒(méi)有區(qū)別；
- 提示2：exists 子查詢的實(shí)際執(zhí)行過(guò)程可能經(jīng)過(guò)了優(yōu)化而不是我們理解上的逐條對(duì)比，如果擔(dān)心效率問(wèn)題，可以進(jìn)行實(shí)際檢驗(yàn)；
- 提示3：exists 子查詢往往也可以用條件表達(dá)式，其他子查詢或join來(lái)替代，哪種方法最優(yōu)需要具體問(wèn)題具體分析；
exists用法荔枝（exists與in的區(qū)別）：
-- exists 語(yǔ)法 select * from emp_tbl a where exists (select 1 from dept_tbl b where b.dept_id = a.dept_id) order by a.rcrd_id? limit 10 ; -- in 語(yǔ)法 select * from emp_tbl a where a.dept_id in (select b.dept_id from dept_tbl b) order by a.rcrd_id? limit 10 ; -- 執(zhí)行計(jì)劃18 explain ?select * from emp_tbl a where exists (select 1 from dept_tbl b where b.dept_id = a.dept_id) ; -- 執(zhí)行計(jì)劃19 explain select * from emp_tbl a where a.dept_id in (select b.dept_id from dept_tbl b) ;==============================================================================================
1.2、order by 關(guān)鍵字優(yōu)化?
優(yōu)化1、盡量使用index方式排序，避免使用 filesort方式；
-- 查看order by的執(zhí)行計(jì)劃是否使用了文件排序；?
-- 執(zhí)行計(jì)劃20 ?索引排序 explain select * from birth_tbl where age > 30 order by age ; -- 執(zhí)行計(jì)劃21 ?索引排序 explain select * from birth_tbl where age > 30 order by age, birth ; -- 執(zhí)行計(jì)劃22 ?文件排序 explain select * from birth_tbl where age > 30 order by birth ; -- 執(zhí)行計(jì)劃23 ?文件排序 explain select * from birth_tbl where age > 30 order by birth, age ; -- 執(zhí)行計(jì)劃24 ?文件排序 explain select * from birth_tbl order by birth ; -- 執(zhí)行計(jì)劃25 ?文件排序 explain select * from birth_tbl where birth > '2018-01-01 00:00:00' order by birth ; -- 執(zhí)行計(jì)劃26 ?索引排序 explain select * from birth_tbl where birth > '2018-01-01 00:00:00' order by age ; -- 執(zhí)行計(jì)劃27 ?文件排序 explain select * from birth_tbl order by age asc, birth desc ;mysql支持兩種方式的排序： 文件排序filesort 和 index 索引排序， index排序的效率較高； 它指mysql 掃描索引本身完成排序，文件排序filesort 效率較低；
【補(bǔ)充】 order by 滿足兩個(gè)情況：會(huì)使用索引排序：
- 情況1：order by 語(yǔ)句使用索引最左前列；
- 情況2： 使用where子句與order by 子句條件組合滿足索引最左前列；
優(yōu)化2、盡可能在索引列上完成排序操作，遵照索引建的最佳左前綴；
優(yōu)化3、如果不在索引列上，文件排序filesort有兩種算法： mysql需要啟動(dòng)雙路排序或單路排序；
優(yōu)化策略：
- 策略1：增大 sort_buffer_size 參數(shù)設(shè)置；
- 策略2：增大 max_length_for_sort_data 參數(shù)的設(shè)置；
- 策略3：why ？
【總結(jié)】order by 總結(jié)-為排序使用索引：
- 總結(jié)1、mysql兩種排序方式： 文件排序或掃描有序索引排序；
- 總結(jié)2、mysql能為排序和查詢使用相同的索引；
- 總結(jié)3、具體執(zhí)行計(jì)劃：
key idx_a_b_c(a, b, c);
- 總結(jié)3.1、order by 能使用索引最左前綴的有：
- order by a; order by a, b; order by a, b, c; order by a desc, b desc, c desc;? -- (要么全部升序，要么全部降序)
- ?
- 總結(jié)3.2、如果where 使用索引的最左前綴定義為常量， 則order by 能使用索引；
- where a = const order by b, c; where a=const and b=const order by c; where a = const and b > const order by b, c;
- ?
- 總結(jié)3.3、不能使用索引進(jìn)行排序：
- order by a asc, b desc, c desc; where g = const order by b, c; where a = const order by c; where a=const order by a, d -- d不是索引的一部分； where a in (...) order by b, c;
1.3、group by 關(guān)鍵字優(yōu)化；（均和order by 一樣）
- （1）group by 實(shí)質(zhì)是先排序后再分組，遵照索引建的最佳左前綴；
- （2）當(dāng)無(wú)法使用索引列，增大 max_length_for_sort_data 參數(shù)的設(shè)置+增大 sort_buffer_size 參數(shù)的設(shè)置；
- （3）where高于 having， 能寫(xiě)在 where 限定的條件就不要去 having 限定了 ；
=============================================================================================
【2】慢查詢?nèi)罩?/strong>
0、什么是慢查詢sql：sql運(yùn)行時(shí)間超過(guò) long_query_time 的sql 將會(huì)被記錄到 慢查詢?nèi)罩局?#xff1b;
- 0.1）mysql的慢查詢?nèi)罩臼莔ysql提供的一種日志記錄， 它用來(lái)記錄在mysql中響應(yīng)時(shí)間超過(guò)閾值的語(yǔ)句，具體指運(yùn)行時(shí)間超過(guò) long_query_time 值的sql， 則會(huì)被記錄到慢查詢?nèi)罩局?#xff1b;
- 0.2）long_query_time 的默認(rèn)值為10， 則表示運(yùn)行時(shí)間超過(guò)10秒的sql語(yǔ)句被記錄到慢查詢?nèi)罩局?#xff1b;
- 0.3）通過(guò)收集超過(guò)10秒的sql， 結(jié)合之前 explain 進(jìn)行全面分析；
1）mysql的慢查詢?nèi)罩?/strong>
- 1.1）默認(rèn)情況下， mysql沒(méi)有開(kāi)啟慢查詢?nèi)罩?#xff0c;需要手工開(kāi)啟；
- 1.2）如果不是調(diào)優(yōu)需要的話，一般不建議啟動(dòng)該參數(shù)； 因?yàn)殚_(kāi)啟慢查詢?nèi)罩净蚨嗷蛏贂?huì)帶來(lái)一定的性能影響。慢查詢?nèi)罩局С謱⑷罩居涗泴?xiě)入文件；
2）查看是否開(kāi)啟慢查詢?nèi)罩疽约叭绾伍_(kāi)啟？?
默認(rèn)： show variables like '%slow_query_log%';
開(kāi)啟： set global slow_query_log=1;
【注意】
- 注意1： 使用 set global slow_query_log=1 開(kāi)啟了慢查詢?nèi)罩局粚?duì)當(dāng)前數(shù)據(jù)庫(kù)生效， 如果mysql重啟以后則會(huì)失效；
- 注意2：如果要永久生效，必須修改配置文件 my.cnf ；
如何設(shè)置long_query_time ？
show variables like 'long_query_time%';
查詢 long_query_time的值？即查詢當(dāng)前多少秒算慢？
show variables like 'long_query_time'
設(shè)置慢的閾值時(shí)間？?
set global long_query_time=3;
為什么設(shè)置后期 long_query_time 還是沒(méi)變；
這個(gè)時(shí)候需要重新連接或新開(kāi)一個(gè)會(huì)話； 或者執(zhí)行 show global variables like 'long_query_time' ；
如何制造 執(zhí)行時(shí)間超過(guò)3秒的SQL？
如 ?select sleep(4);
查看當(dāng)前有多少條慢查詢sql？
show global status like '%slow_queries%';
補(bǔ)充1：如何在my.ini文件中配置mysql的慢查詢參數(shù)， 如下：
補(bǔ)充2： 日志分析工具 mysqldumpslow ,常用于在生產(chǎn)中分析sql的性能；
?
=============================================================================================
【3】、批量數(shù)據(jù)腳本；
=============================================================================================
【4】show profile
4、show profile
4.0）intro： show profile 提供了比 explain 更加細(xì)粒度的sql執(zhí)行計(jì)劃分析和sql優(yōu)化；
4.1）是什么： 是mysql提供可以用來(lái)分析當(dāng)前回話中語(yǔ)句執(zhí)行的資源消耗情況。可以用于sql的調(diào)優(yōu)測(cè)量；
4.2）官網(wǎng)： https://dev.mysql.com/doc/refman/8.0/en/show-profile.html?
4.3）默認(rèn)情況下，參數(shù)處于關(guān)閉狀態(tài)，并保持最近15次的運(yùn)行結(jié)果；
4.4）分析步驟：
- 步驟1：是否支持，看看當(dāng)前的mysql版本是否支持 show profile；
- show variables like 'profiling';
- 步驟2：開(kāi)啟功能，默認(rèn)是關(guān)閉，使用前需要開(kāi)啟；
- 步驟3：運(yùn)行 sql； 且要運(yùn)行耗時(shí)長(zhǎng)的sql；
- select * from emp_tbl e inner join dept_tbl d on e.dept_id = d.dept_id ; select * from emp_tbl e left join dept_tbl d on e.dept_id = d.dept_id ; select * from emp_tbl group by rcrd_id%10? ;?
- 步驟4：查看結(jié)果，show profiles;
- 步驟5：診斷sql， show profile cpu, block io for query 上一步前面的問(wèn)題sql數(shù)字號(hào)碼；?查看一條sql的完整生命周期；??show profile cpu, block io for query 3;?
- 補(bǔ)充： 不僅僅可以查看 cpu, block io ， 還可以查看如下類型的信息；
- 步驟6： 日常開(kāi)發(fā)需要注意的結(jié)論；以下結(jié)論都是性能比較差的sql的表現(xiàn)形式，即 show profile cpu, block io for query 3; 中的status中出現(xiàn)以下4種中的一種或幾種，則sql執(zhí)行效率較差，需要優(yōu)化；?
【關(guān)于show profile的結(jié)論】
- 結(jié)論1）converting heap to myisam 查詢結(jié)果太大， 內(nèi)存都不夠用了，往磁盤(pán)上搬；
- 結(jié)論2）creating tmp table 創(chuàng)建臨時(shí)表： 拷貝數(shù)據(jù)到臨時(shí)表，用完再刪除；
- 結(jié)論3）copying to tmp table on disk， 把內(nèi)存中臨時(shí)表復(fù)制到磁盤(pán)，危險(xiǎn)
- 結(jié)論4） locked ：鎖住；??
?
=============================================================================================
【5】、全局查詢?nèi)罩?#xff1b;
5.1、配置啟用： 在mysql的 my.ini 中，配置如下：
5.2、編碼啟用：命令如下：
set global general_log=1; set global log_output='TABLE';
此后， 你所編寫(xiě)的sql語(yǔ)句， 將會(huì)記錄到 mysql庫(kù)里的general_log 表，可以用下面的命令查看：
select * from mysql.general_log;?
5.3、建議不要在生產(chǎn)環(huán)境開(kāi)啟這個(gè)功能， 僅在測(cè)試環(huán)境開(kāi)啟以便調(diào)試；
?
【建議】 建議使用 show profile 功能分析和優(yōu)化sql性能；?
?
?
總結(jié)
以上是生活随笔為你收集整理的mysql如何分析sql执行效率和进行效率优化的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： mysql事务基础+基于innodb的行
- 下一篇： 怎么把cr2文件批量转换成jpg