MySQL 索引優化全攻略

所謂索引就是為特定的mysql欄位進行一些特定的演算法排序,比如二叉樹的演算法和雜湊演算法,雜湊演算法是通過建立特徵值,然後根據特徵值來快速查詢。而用的最多,並且是mysql預設的就是二叉樹演算法 BTREE,通過BTREE演算法建立索引的欄位,比如掃描20行就能得到未使用BTREE前掃描了2^20行的結果,具體的實現方式後續本部落格會出一個演算法專題裡面會有具體的分析討論;

Explain優化查詢檢測

EXPLAIN可以幫助開發人員分析SQL問題,explain顯示了mysql如何使用索引來處理select語句以及連線表,可以幫助選擇更好的索引和寫出更優化的查詢語句.

使用方法,在select語句前加上Explain就可以了：

Explain select * from blog where false;

mysql在執行一條查詢之前,會對發出的每條SQL進行分析,決定是否使用索引或全表掃描如果傳送一條select * from blog where falseMysql是不會執行查詢操作的,因為經過SQL分析器的分析後MySQL已經清楚不會有任何語句符合操作;

Example

mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; 
-- 結果： 
id: 1 

select_type: SIMPLE -- 查詢型別（簡單查詢,聯合查詢,子查詢） 

table: user -- 顯示這一行的資料是關於哪張表的 

type: range -- 區間索引（在小於1990/2/2區間的資料),這是重要的列,顯示連線使用了何種型別。從最好到最差的連線型別為system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表掃描了全表才確定結果。一般來說,得保證查詢至少達到range級別,最好能達到ref。 

possible_keys: birthday  -- 指出MySQL能使用哪個索引在該表中找到行。如果是空的,沒有相關的索引。這時要提高效能,可通過檢驗WHERE子句,看是否引用某些欄位,或者檢查欄位不是適合索引。  

key: birthday -- 實際使用到的索引。如果為NULL,則沒有使用索引。如果為primary的話,表示使用了主鍵。 

key_len: 4 -- 最長的索引寬度。如果鍵是NULL,長度就是NULL。在不損失精確性的情況下,長度越短越好 

ref: const -- 顯示哪個欄位或常數與key一起被使用。  

rows: 1 -- 這個數表示mysql要遍歷多少資料才能找到,在innodb上是不準確的。 

Extra: Using where; Using index -- 執行狀態說明,這裡可以看到的壞的例子是Using temporary和Using

select_type

1. simple 簡單select(不使用union或子查詢)
2. primary 最外面的select
3. union union中的第二個或後面的select語句
4. dependent union union中的第二個或後面的select語句,取決於外面的查詢
5. union result union的結果。
6. subquery 子查詢中的第一個select
7. dependent subquery 子查詢中的第一個select,取決於外面的查詢
8. derived 匯出表的select(from子句的子查詢)

Extra與type詳細說明

1. Distinct:一旦MYSQL找到了與行相聯合匹配的行,就不再搜尋了
2. Not exists: MYSQL優化了LEFT JOIN,一旦它找到了匹配LEFT JOIN標準的行,就不再搜尋了
3. Range checked for each Record(index map:#):沒有找到理想的索引,因此對於從前面表中來的每一個行組合,MYSQL檢查使用哪個索引,並用它來從表中返回行。這是使用索引的最慢的連線之一
4. Using filesort: 看到這個的時候,查詢就需要優化了 。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連線型別以及儲存排序鍵值和匹配條件的全部行的行指標來排序全部行
5. Using index: 列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表返回的,這發生在對錶的全部的請求列都是同一個索引的部分的時候
6. Using temporary 看到這個的時候,查詢需要優化了 。這裡,MYSQL需要建立一個臨時表來儲存結果,這通常發生在對不同的列集進行ORDER BY上,而不是GROUP BY上
7. Where used 使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給使用者。如果不想返回表中的全部行,並且連線型別ALL或index,這就會發生,或者是查詢有問題不同連線型別的解釋(按照效率高低的順序排序
8. system 表只有一行：system表。這是const連線型別的特殊情況
9. const:表中的一個記錄的最大值能夠匹配這個查詢(索引可以是主鍵或惟一索引)。因為只有一行,這個值實際就是常數,因為MYSQL先讀這個值然後把它當做常數來對待
10. eq_ref:在連線中,MYSQL在查詢時,從前面的表中,對每一個記錄的聯合都從表中讀取一個記錄,它在查詢使用了索引為主鍵或惟一鍵的全部時使用
11. ref:這個連線型別只有在查詢使用了不是惟一或主鍵的鍵或者是這些型別的部分(比如,利用最左邊字首)時發生。對於之前的表的每一個行聯合,全部記錄都將從表中讀出。這個型別嚴重依賴於根據索引匹配的記錄多少—越少越好+
12. range:這個連線型別使用索引返回一個範圍中的行,比如使用>或<查詢東西時發生的情況+
13. index: 這個連線型別對前面的表中的每一個記錄聯合進行完全掃描(比ALL更好,因為索引一般小於表資料)+
14. ALL:這個連線型別對於前面的每一個記錄聯合進行完全掃描,這一般比較糟糕,應該儘量避免

其中type:

1. 如果是Only index,這意味著資訊只用索引樹中的資訊檢索出的,這比掃描整個表要快。
2. 如果是where used,就是使用上了where限制。
3. 如果是impossible where 表示用不著where,一般就是沒查出來啥。
4. 如果此資訊顯示Using filesort或者Using temporary的話會很吃力,WHERE和ORDER BY的索引經常無法兼顧,如果按照WHERE來確定索引,那麼在ORDER BY時,就必然會引起Using filesort,這就要看是先過濾再排序划算,還是先排序再過濾划算。

索引

索引的型別

UNIQUE唯一索引

不可以出現相同的值,可以有NULL值

INDEX普通索引

允許出現相同的索引內容

PRIMARY KEY主鍵索引

不允許出現相同的值,且不能為NULL值,一個表只能有一個primary_key索引

fulltext index 全文索引

上述三種索引都是針對列的值發揮作用,但全文索引,可以針對值中的某個單詞,比如一篇文章中的某個詞, 然而並沒有什麼卵用,因為只有myisam以及英文支援,並且效率讓人不敢恭維,但是可以用coreseek和xunsearch等第三方應用來完成這個需求

索引的CURD

索引的建立

ALTER TABLE

適用於表建立完畢之後再新增

ALTER TABLE 表名 ADD 索引型別 (unique,primary key,fulltext,index)[索引名](欄位名)

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名,可要可不要;如果不要,當前的索引名就是該欄位名; 
ALTER TABLE `table_name` ADD UNIQUE (`column_list`) 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`) 
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)

CREATE INDEX

CREATE INDEX可對錶增加普通索引或UNIQUE索引

--例,只能新增這兩種索引; 
CREATE INDEX index_name ON table_name (column_list) 
CREATE UNIQUE INDEX index_name ON table_name (column_list)

另外,還可以在建表時新增

CREATE TABLE `test1` ( 
  `id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意,下面建立了主鍵索引,這裡就不用建立了 
  `username` varchar(64) NOT NULL COMMENT '使用者名稱', 
  `nickname` varchar(50) NOT NULL COMMENT '暱稱/姓名', 
  `intro` text, 
  PRIMARY KEY (`id`),  
  UNIQUE KEY `unique1` (`username`), -- 索引名稱,可要可不要,不要就是和列名一樣 
  KEY `index1` (`nickname`), 
  FULLTEXT KEY `intro` (`intro`) 
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='後臺使用者表';

索引的刪除

DROP INDEX `index_name` ON `talbe_name`  
ALTER TABLE `table_name` DROP INDEX `index_name` 
-- 這兩句都是等價的,都是刪除掉table_name中的索引index_name; 

ALTER TABLE `table_name` DROP PRIMARY KEY -- 刪除主鍵索引,注意主鍵索引只能用這種方式刪除

索引的檢視

show index from tablename \G;

索引的更改

更改個毛線,刪掉重建一個既可

建立索引的技巧

1.維度高的列建立索引

資料列中 不重複值 出現的個數,這個數量越高,維度就越高

如資料表中存在8行資料a ,b ,c,d,a,b,c,d這個表的維度為4

要為維度高的列建立索引,如性別和年齡,那年齡的維度就高於性別

性別這樣的列不適合建立索引,因為維度過低

2.對 where,on,group by,order by 中出現的列使用索引

3.對較小的資料列使用索引,這樣會使索引檔案更小,同時記憶體中也可以裝載更多的索引鍵

4.為較長的字串使用字首索引

5.不要過多建立索引,除了增加額外的磁碟空間外,對於DML操作的速度影響很大,因為其每增刪改一次就得從新建立索引

6.使用組合索引,可以減少檔案索引大小,在使用時速度要優於多個單列索引

組合索引與字首索引

注意,這兩種稱呼是對建立索引技巧的一種稱呼,並非索引的型別;

組合索引

MySQL單列索引和組合索引究竟有何區別呢?

為了形象地對比兩者,先建一個表：

CREATE TABLE `myIndex` ( 
  `i_testID` INT NOT NULL AUTO_INCREMENT,  
  `vc_Name` VARCHAR(50) NOT NULL,  
  `vc_City` VARCHAR(50) NOT NULL,  
  `i_Age` INT NOT NULL,  
  `i_SchoolID` INT NOT NULL,  
  PRIMARY KEY (`i_testID`)  
);

假設表內已有1000條資料,在這 10000 條記錄裡面 7 上 8 下地分佈了 5 條 vc_Name=”erquan” 的記錄,只不過 city,age,school 的組合各不相同。來看這條 T-SQL：

SELECT `i_testID` FROM `myIndex` WHERE `vc_Name`='erquan' AND `vc_City`='鄭州' AND `i_Age`=25; -- 關聯搜尋;

首先考慮建MySQL單列索引：

在 vc_Name 列上建立了索引。執行 T-SQL 時,MYSQL 很快將目標鎖定在了 vc_Name=erquan 的 5 條記錄上,取出來放到一中間結果集。在這個結果集裡,先排除掉 vc_City 不等於”鄭州”的記錄,再排除 i_Age 不等於 25 的記錄,最後篩選出唯一的符合條件的記錄。雖然在 vc_Name 上建立了索引,查詢時MYSQL不用掃描整張表,效率有所提高,但離我們的要求還有一定的距離。同樣的,在 vc_City 和 i_Age 分別建立的MySQL單列索引的效率相似。

為了進一步榨取 MySQL 的效率,就要考慮建立組合索引。就是將 vc_Name,vc_City,i_Age 建到一個索引裡：

ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);

建表時,vc_Name 長度為 50,這裡為什麼用 10 呢?這就是下文要說到的字首索引,因為一般情況下名字的長度不會超過 10,這樣會加速索引查詢速度,還會減少索引檔案的大小,提高 INSERT 的更新速度。

執行 T-SQL 時,MySQL 無須掃描任何記錄就到找到唯一的記錄!!

如果分別在 vc_Name,vc_City,i_Age 上建立單列索引,讓該表有 3 個單列索引,查詢時和上述的組合索引效率一樣嗎?答案是大不一樣,遠遠低於我們的組合索引。雖然此時有了三個索引, 但 MySQL 只能用到其中的那個它認為似乎是最有效率的單列索引,另外兩個是用不到的,也就是說還是一個全表掃描的過程 。

建立這樣的組合索引,其實是相當於分別建立了

• vc_Name,vc_City,i_Age
• vc_Name,vc_City
• vc_Name

這樣的三個組合索引!為什麼沒有 vc_City,i_Age 等這樣的組合索引呢?這是因為 mysql 組合索引 “最左字首” 的結果。簡單的理解就是隻從最左面的開始組合。並不是只要包含這三列的查詢都會用到該組合索引,下面的幾個 T-SQL 會用到：

SELECT * FROM myIndex WHREE vc_Name=”erquan” AND vc_City=”鄭州” SELECT * FROM myIndex WHREE vc_Name=”erquan”

而下面幾個則不會用到：

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City=”鄭州” SELECT * FROM myIndex WHREE vc_City=”鄭州”

也就是,name_city_age(vc_Name(10),vc_City,i_Age) 從左到右進行索引,如果沒有左前索引Mysql不執行索引查詢

字首索引

如果索引列長度過長,這種列索引時將會產生很大的索引檔案,不便於操作,可以使用字首索引方式進行索引字首索引應該控制在一個合適的點,控制在0.31黃金值即可(大於這個值就可以建立)

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; — 這個值大於0.31就可以建立字首索引,Distinct去重複 ALTER TABLE `user` ADD INDEX `uname`(title(10)); — 增加字首索引SQL,將人名的索引建立在10,這樣可以減少索引檔案大小,加快索引查詢速度

什麼樣的sql不走索引

要儘量避免這些不走索引的sql

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不會使用索引,因為所有索引列參與了計算 

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會使用索引,因為使用了函式運算,原理與上面相同 

SELECT * FROM `houdunwang` WHERE `uname` LIKE'後盾%' -- 走索引 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%後盾%" -- 不走索引 

-- 正規表示式不使用索引,這應該很好理解,所以為什麼在SQL中很難看到regexp關鍵字的原因 

-- 字串與數字比較不使用索引; 
CREATE TABLE `a` (`a` char(10)); 
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引 

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果條件中有or,即使其中有條件帶索引也不會使用。換言之,就是要求使用的所有欄位,都必須建立索引, 我們建議大家儘量避免使用or 關鍵字 

-- 如果mysql估計使用全表掃描要比使用索引快,則不使用索引

多表關聯時的索引效率

• SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; — 不會使用索引,因為使用了函式運算,原理與上面相同
• SELECT * FROM `houdunwang` WHERE `uname` LIKE’後盾%’ — 走索引
• SELECT * FROM `houdunwang` WHERE `uname` LIKE “%後盾%” — 不走索引

從上圖可以看出,所有表的type為all,表示全表索引;也就是6 6 6,共遍歷查詢了216次;

除第一張表示全表索引(必須的,要以此關聯其他表),其餘的為range(索引區間獲得),也就是6+1+1+1,共遍歷查詢9次即可;

所以我們建議在多表join的時候儘量少join幾張表,因為一不小心就是一個笛卡爾乘積的恐怖掃描,另外,我們還建議儘量使用left join,以少關聯多.因為使用join 的話,第一張表是必須的全掃描的,以少關聯多就可以減少這個掃描次數.

索引的弊端

不要盲目的建立索引,只為查詢操作頻繁的列建立索引,建立索引會使查詢操作變得更加快速,但是會降低增加、刪除、更新操作的速度,因為執行這些操作的同時會對索引檔案進行重新排序或更新;

但是,在網際網路應用中,查詢的語句遠遠大於DML的語句,甚至可以佔到80%~90%,所以也不要太在意,只是在大資料匯入時,可以先刪除索引,再批量插入資料,最後再新增索引。