MySQL索引及Explain及常見最佳化

標籤：

MySQL索引設計的原則   

1. 搜尋的索引列，不一定是所要選擇的列。換句話說，最適合索引的列是出現在WHERE 子句中的列，或串連子句中指定的列，而不是出現在SELECT 關鍵字後的挑選清單中的列。

2. 使用惟一索引。考慮某列中值的分布。對於惟一值的列，索引的效果最好，而具有多個重複值的列，其索引效果最差。例如，存放年齡的列具有不同值，很容易區分各行。而用來記錄性別的列，只含有“ M”和“F”，則對此列進行索引沒有多大用處（不管搜尋哪個值，都會得出大約一半的行）

3. 使用短索引。如果對串列進行索引，應該指定一個前置長度，只要有可能就應該這樣做。例如，如果有一個CHAR(200) 列，如果在前10 個或20 個字元內，多數值是惟一的，那麼就不要對整個列進行索引。對前10 個或20 個字元進行索引能夠節省大量索引空

間，也可能會使查詢更快。較小的索引涉及的磁碟I/O 較少，較短的值比較起來更快。更為重要的是，對於較短的鍵值，索引快取中的塊能容納更多的鍵值，因此，MySQL也可以在記憶體中容納更多的值。這增加了找到行而不用讀取索引中較多塊的可能性。

（當然，應該利用一些常識。如僅用列值的第一個字元進行索引是不可能有多大好處的，因為這個索引中不會有許多不同的值。）

4. 利用最左首碼。在建立一個n 列的索引時，實際是建立了MySQL 可利用的n 個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左首碼。（這與索引一個列的首碼不同，索引一個列的首碼是利用該的前n 個字

符作為索引值。）5. 不要過度索引。不要以為索引“越多越好”，什麼東西都用索引是錯的。每個額外的索引都要佔用額外的磁碟空間，並降低寫操作的效能，這一點我們前面已經介紹過。在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。如果有一個索引很少利用或從不使用，那麼會不必要地減緩表的修改速度。此外，MySQL 在產生一個執行計畫時，要考慮各個索引，這也要費時間。建立多餘的索引給查詢最佳化帶來了更多的工作。索引太多，也可能會使MySQL 選擇不到所要使用的最好索引。只保持所需的索引有利於查詢最佳化。如果想給已索引的表增加索引，應該考慮所要增加的索引是否是現有多列索引的最左索引。如果是，則就不要費力去增加這個索引了，因為已經有了。

6. 考慮在列上進行的比較類型。索引可用於“ <”、“ < = ”、“ = ”、“ > =”、“ >”和BETWEEN 運算。在模式具有一個直接量首碼時，索引也用於LIKE 運算。如果只將某個列用於其他類型的運算時（如STRCMP( )），對其進行索引沒有價值。

最佳化select

select * from pc_spu where cate_id=2;

 

select * from pc_spu where cate_id=2 and brand_id=2; 

 

 

最佳化ORDER BY

SELECT * FROM pc_spu WHERE CATE_ID=2 ORDER BY BRAND_ID ASC,CATE_ID DESC;

EXPLAIN SELECT * FROM pc_spu FORCE INDEX(idx_example) WHERE cate_id=2 ORDER BY BRAND_ID ASC,CATE_ID DESC;

這個查詢必須要用到該索引

CREATE INDEX idx_example  
ON table1 (col1 ASC, col2 DESC, col3 ASC)  在這種情況下，以下查詢可以得到最佳化：   
SELECT col1, col2, col3 from table1  ORDER BY col1 ASC, col2 DESC, col3 ASC  和   
SELECT col1, col2, col3 from example  
ORDER BY col1 DESC, col2 ASC, col3 DESC  
索引不用於最佳化在 ORDER BY 子句中具有 ASC 和 DESC 的其它任何模式的查詢。例如：   
SELECT col1, col2, col3 from table1  ORDER BY col1 ASC, col2 ASC, col3 ASC  不會得到最佳化

 

最佳化分頁查詢

SELECT * FROM bbs_title ORDER BY GMT_MODIFIED LIMIT 1000,10;

 

SELECT a.* FROM bbs_title a INNER JOIN (SELECT ID FROM bbs_title b ORDER BY b.GMT_MODIFIED LIMIT 1000,10) b ON a.ID=b.ID;

再來給GMT_MODIFIED上加上索引

COUNT查詢

SELECT COUNT(*) FROM bbs_title;

SELECT COUNT(ID) FROM bbs_title;

SELECT COUNT(1) FROM bbs_title;

效能上差不多

不要用count(*)，查詢的時候鎖定的列數多

EXPLAIN列的解釋：

table：顯示這一行的資料是關於哪張表的

type：這是重要的列，顯示串連使用了何種類型。從最好到最差的連線類型為const、eq_reg、ref、range、indexhe和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根本不能使用索引，結果是檢索會很慢

 

extra列返回的描述的意義

Distinct:一旦MYSQL找到了與行相聯合匹配的行，就不再搜尋了

Not exists: MYSQL最佳化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜尋了

Range checked for each Record（index map:#）:沒有找到理想的索引，因此對於從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，並用它來從表中返回行。這是使用索引的最慢的串連之一

Using filesort: 看到這個的時候，查詢就需要最佳化了。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連線類型以及儲存排序鍵值和匹配條件的全部行的行指標來排序全部行

Using index: 列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表返回的，這發生在對錶的全部的請求列都是同一個索引的部分的時候

Using temporary 看到這個的時候，查詢需要最佳化了。這裡，MYSQL需要建立一個暫存資料表來儲存結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上

Where used 使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給使用者。如果不想返回表中的全部行，並且連線類型ALL或index，這就會發生，或者是查詢有問題不同連線類型的解釋（按照效率高低的順序排序）

system 表只有一行：system表。這是const連線類型的特殊情況

const:表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行，這個值實際就是常數，因為MYSQL先讀這個值然後把它當做常數來對待

eq_ref:在串連中，MYSQL在查詢時，從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用

ref:這個連線類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊首碼）時發生。對於之前的表的每一個行聯合，全部記錄都將從表中讀出。這個類型嚴重依賴於根據索引匹配的記錄多少—越少越好

range:這個連線類型使用索引返回一個範圍中的行，比如使用>或<尋找東西時發生的情況

index: 這個連線類型對前面的表中的每一個記錄聯合進行完全掃描（比ALL更好，因為索引一般小於表資料）

ALL:這個連線類型對於前面的每一個記錄聯合進行完全掃描，這一般比較糟糕，應該盡量避免

MySQL索引及Explain及常見最佳化