MySQL性能優化之SQL語句優化
前言
這個話題其實難度蠻大的，因為不但需要理解一些Mysql的專業知識，同時還需要長時間的觀察統計，以及大量的使用經驗
之前在某項目中，服務端發生過兩起關於Mysql的頻繁大量拋錯的事件，一個是拋出Mysql異常" too many connections", 另一個是拋出Mysql異常 " open too many files",這促使我開始重視起Mysql的性能優化。
優化目標
1.減少 IO 次數
IO永遠是資料庫最容易瓶頸的地方，這是由資料庫的職責所決定的，大部分資料庫操作中超過90%的時間都是 IO 操作所佔用的，減少 IO 次數是 SQL 優化中需要第一優先考慮，當然，也是收效最明顯的優化手段。
2.降低 CPU 計算
除了 IO 瓶頸之外，SQL優化中需要考慮的就是 CPU 運算量的優化了。order by, group by,distinct … 都是消耗 CPU 的大戶（這些操作基本上都是 CPU 處理內存中的數據比較運算）。當我們的 IO 優化做到一定階段之後，降低 CPU 計算也就成為了我們 SQL 優化的重要目標
優化方向
1.SQL語句及索引的優化
2.資料庫表結構的優化
3.系統配置的優化
4.硬體的優化
SQL語句及索引的優化
1.如何發現有問題的SQL語句？
（1）使用mysql慢查詢日誌監控有效率問題的SQL語句
檢查滿查詢日誌是否開啟
mysql> show variables like "%slow%";
（2）開啟慢查詢日誌
開啟
mysql> set global slow_query_log="ON";
設置記錄慢查詢的時間閥值
mysql> set global long_query_time=0.1;
即超過100ms的查詢就認為是慢查詢
（註：slow_launch_time 表示如果建立線程花費了比這個值更長的時間,slow_launch_threads 計數器將增加）
記錄沒有使用索引的查詢
mysql> set global log_queries_not_using_indexes=on;
開啟慢查詢日誌的操作也可以在MySQL的配置文件my.cnf中添加以下兩行：
log-slow-queries=/var/run/mysqld/mysqld-slow.log
long_query_time=0.1
（3）使用慢查詢日誌分析工具：mysqldumpslow或mysqlsla
mysqldumpslow是官方提供的，在安裝Mysql時就已經自帶了，使用方法請自行輸入命令 mysqldumpslow --help查看。
mysqlsal是hackmysql.com推出的，功能非常強大，請自行google其安裝步驟和使用方法。
2.使用EXPLAIN分析SQL查詢語句
例如：mysql> explain select * from file;
mysql> explain select * from file where fid > 0;
比較以上兩條語句的執行結果。
EXPLAIN語句返回結果中列的解釋：
table：這行數據的所屬表的表名字
type：顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、index和ALL
const：表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行，這個值實際就是常數，因為MYSQL先讀這個值然後把它當做常數來對待
eq_ref：MYSQL在查詢時，從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用
ref：只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊前綴）時發生。對於之前的表的每一個行聯合，全部記錄都將從表中讀出。這個類型嚴重依賴於根據索引匹配的記錄多少—越少越好
range：使用索引返回一個範圍中的行，比如使用>或
index：對前面的表中的每一個記錄聯合進行完全掃描（比ALL更好，因為索引一般小於表數據）
ALL：對於前面的每一個記錄聯合進行完全掃描，這一般比較糟糕，應該盡量避免
possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句
key： 實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MYSQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MYSQL忽略索引
key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好
ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數
rows：MYSQL認為必須檢查的用來返回請求數據的行數
Extra：關於MYSQL如何解析查詢的額外信息。Using temporary和Using filesort，意思MYSQL根本不能使用索引，結果是檢索會很慢，會創建臨時表或文件來存儲結果，造成大量的IO操作，影響效率
3.count() 和 max()的優化
（1）max()優化
max(A) 給max函數里的欄位A添加索引
mysql> create index idx_A on table B(A)
此時若用 EXPLAIN語句再次分析，會在Extra列中看到如下返回：
表示不需要查詢，只需要通過索引就可以找出查詢結果，這條索引叫覆蓋索引，就是select的數據列只用從索引中就能夠取得，不必讀取數據行，換句話說查詢列要被所建的索引覆蓋。
（2）count()優化
在不加where限制條件的情況下， count(*)與 count(COL)基本可以認為是等價的；
但是在有where限制條件的情況下， count(*)會比 count(COL)快非常多；
count(*)通常是對主鍵進行索引掃描，而 count(COL)就不一定了，另外前者是統計表中的所有符合的紀錄總數，而後者是計算表中所有符合的列的紀錄數。另外，count(*)會包含NULL值
4.子查詢優化
通常情況下需要把子查詢優化為join查詢，但在優化時要注意關聯鍵是否有一對多的關係，注意重複數據：
select tid from t1 where tid in (select id from t);
改成
selectdistinctt1.tid from t1 join t2 on t1.tid = t.id;
5.limit優化
limit常用於分頁處理，會伴隨order by 語句，因為會使用FileSorts 這樣會造成大量的IO問題
優化1：使用有索引的列或主鍵進行order by操作
不使用索引列的語句：
使用索引列後的語句：
對比以上兩條語句可以看到type從ALL變成了index。
但隨著翻頁越來越往後，掃描的行數會越來越多， 實際上響應速度會越來越慢
查詢第一頁：
查詢第N頁
對比以上兩條，可以看到掃描的行數明顯增加了。因此需要進一步優化。
優化步驟2：
記錄上次返回的主鍵，在下次查詢的時候使用主鍵過濾 （避免數據量大時掃描過多的記錄）
可以看到掃描行數明顯減少。
6.選擇合適的列建立合適的索引
（1）在where從句，group by從句， order by從句， on從句中出現的列
（2）索引欄位越小越好（資料庫存儲是以頁為單位的，一頁里能存越多數據，一次IO操作獲取的數據量越大，效率更高）
（3）離散度大的列放到聯合索引前面
如何判斷哪個列離散度高？
select count(A), count(B) from X;
如果count(A)的值大於count(B)的值，那麼說明A欄位的離散度大，選擇範圍會更大，需要以A為主要排序欄位，索引應該為index(A,B) 而不是inde（B，A）
7.索引的維護及優化
建立索引會有利於查詢但是會影響insert和update、delete等更新操作
並且過多的索引也會影響查詢，不利於資料庫分析和選擇索引
重複索引：設為主鍵的欄位不需要再加索引了
冗餘索引：在聯合索引中包含了主鍵的索引，對於innodb來說，每一個索引後面都會附加主鍵信息，這個時候就是冗餘的。
TAG:瘋狂牛人 |