在優(yōu)化MySQL時，通常需要對數(shù)據(jù)庫進行分析，常見的分析手段有慢查詢?nèi)罩荆珽XPLAIN 分析查詢，profiling分析以及show命令查詢系統(tǒng)狀態(tài)及系統(tǒng)變量，通過定位分析性能的瓶頸，才能更好的優(yōu)化數(shù)據(jù)庫系統(tǒng)的性能。
1 慢查詢?nèi)罩?br />
MySQL 的慢查詢?nèi)罩居脕碛涗浽?MySQL 中響應時間超過閾值的語句，具體指運行時間超過long_query_time值的 SQL，則會被記錄到慢查詢?nèi)罩局?br />
● long_query_time的默認值為10，意思是運行10秒以上的語句。
● 默認情況下，MySQL數(shù)據(jù)庫沒有開啟慢查詢?nèi)罩荆枰謩釉O置參數(shù)開啟
------------------------------------------------
修改配置文件my.cnf或my.ini，在[mysqld]一行下面加入兩個配置參數(shù)
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3
在生產(chǎn)環(huán)境中，如果手工分析日志，查找、分析SQL，還是比較費勁的，所以MySQL提供了日志分析工具mysqldumpslow。
2 Explain(執(zhí)行計劃)
使用 Explain 關鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道 MySQL 是如何處理你的 SQL 語句的。分析你的查詢語句或是表結構的性能瓶頸
3 Show Profile 分析查詢
Show Profile 是 MySQL 提供可以用來分析當前會話中語句執(zhí)行的資源消耗情況。可以用于SQL的調優(yōu)的測量。默認情況下，參數(shù)處于關閉狀態(tài)，并保存最近15次的運行結果 .
1、先運行看看是否真的很慢，注意設置SQL_NO_CACHE
2、where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數(shù)最小的表開始查起，單表每個字段分別查詢，看哪個字段的區(qū)分度最高
3、explain查看執(zhí)行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢）
4、order by limit 形式的sql語句讓排序的表優(yōu)先查
5、了解業(yè)務方使用場景
6、加索引時參照建索引的幾大原則
7、觀察結果，不符合預期繼續(xù)從0分析
① 盡可能的使用復合索引而不是索引的組合；
②創(chuàng)建索引盡量讓輔助索引進行索引覆蓋 而不是回表；
③在可以使用主鍵id的表中，盡量使用自增主鍵id，這樣可以避免頁分裂；
④查詢的時候盡量不要使用select * ，這樣可以避免大量的回表；
⑤盡量少使用子查詢，能使用外連接就使用外連接，這樣可以避免產(chǎn)生笛卡爾集；
⑥能使用短索引就是用短索引，這樣可以在非葉子節(jié)點存儲更多的索引列降低樹的層高，并且減少空間的開銷；
（1）Where子句中：where表之間的連接必須寫在其他Where條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在Where子句的末尾.HAVING最后。
（2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。
（3）避免在索引列上使用計算
（4）避免在索引列上使用IS NULL和IS NOT NULL
（5）對查詢進行優(yōu)化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
（6）應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描
（7）應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描
首先對于索引的維護來說是需要成本的，我們對數(shù)據(jù)的增/刪/修改,刪除,都會產(chǎn)生額外的對索引文件的操作,這些操作需要消耗額外的IO,會降低增/改/刪的執(zhí)行效率。所以，在我們刪除數(shù)據(jù)庫百萬級別數(shù)據(jù)的時候，刪除數(shù)據(jù)的速度和創(chuàng)建的索引數(shù)量是成正比的,如果直接刪除很可能會產(chǎn)生中斷情況，所以我們想要刪除百萬數(shù)據(jù)的時候可以做如下操作：
1. 先刪除索引（此時大概耗時三分多鐘）
2. 然后刪除其中無用數(shù)據(jù)（此過程需要不到兩分鐘）
3. 刪除完成后重新創(chuàng)建索引(此時數(shù)據(jù)較少了創(chuàng)建索引也非常快，約十分鐘左右)
官方微信
官方抖音
京公網(wǎng)安備 11030102010736號