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