Mysql：索引實戰

標籤：相對   null   sql函數   java程式員   auto   效能提升   char   xpl   不能   

 

MySQL主要提供2種方式的索引：B-Tree索引，Hash索引

B樹索引具有範圍尋找和首碼尋找的能力，對於有N節點的B樹，檢索一條記錄的複雜度為O(LogN)。相當於二分尋找。

雜湊索引只能做等於尋找，但是無論多大的Hash表，尋找複雜度都是O(1)。

顯然，如果值的差異性大，並且以等值尋找（=、 <、>、in）為主，Hash索引是更高效的選擇，它有O(1)的尋找複雜度。

如果值的差異性相對較差，並且以範圍尋找（between and）為主，B樹是更好的選擇，它支援範圍尋找。

索引

無論是面試，還是實際工作中，對於一個Java程式員來說，資料庫最佳化是避不開的一個技術點，關於資料庫的最佳化，在效能達不到要求的情況下，我大致給出以下幾個方向：

（1）最佳化表結構，對常用欄位和非常用的欄位分開儲存

（2）最佳化SQL，合理使用索引

（3）做資料庫讀寫分離，減少IO壓力，由於資料庫對記錄做了持久化並儲存在磁碟上，對磁碟的I/O又是非常消耗效能的操作，因此讀、寫都在一個庫中會大大增加I/O的壓力

（4）嘗試使用緩衝，不要讓資料都走資料庫

（5）對業務做垂直分割

（6）對錶做水平分割，這一步比較麻煩，要注意主鍵建置規則以及請求路由規則

以上6個點是有優先順序的，本文關注的是第二點的索引部分。正確合理地使用索引對於資料庫效能提升是至關重要的，本文暫時不分析索引原理，只是從實戰的角度，總結一下索引的提示，理論結合實踐，印象會更深一些。

當然，事前我已經建立了一張很簡單的student表並向表中插入了10萬條資料，SQL為：

DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (  `s_id` int(11) NOT NULL AUTO_INCREMENT,  `s_name` varchar(100) DEFAULT NULL,  `s_age` int(11) DEFAULT NULL,  `s_phone` varchar(30) DEFAULT NULL,  PRIMARY KEY (`s_id`),  KEY `s_name` (`s_name`)) ENGINE=InnoDB, CHARSET=utf8;

 

使用普通索引與不使用普通索引的區別

先看一下不使用普通索引，進行查詢，執行SQL語句：

select * from student where s_name = "99999ssss";

看一下查詢時間：

花費了0.179秒，使用explain查看一下該條SQL語句的執行情況：

分析幾個關鍵資訊：

• select_type：SIMPLE，這個不是很關鍵，只是表示這是一次簡單的查詢，沒有join，沒有union，沒有中間表
• type：ALL，表示該次SQL進行了全表查詢
• key：MySQL使用的索引名，這裡null表示此次SQL查詢MySQL並沒有使用索引
• rows：這個是最關鍵的，表示這次SQL查詢了100665條記錄

OK，接下來給s_name這一列加上普通索引：

alter table student add index s_name(s_name);

看一下運行結果：

看到在s_name上加上索引之後，查詢速度馬上快了3倍以上。

從分析結果上來看，由於此次SQL對列s_name使用了索引，因此rows只查了1條記錄，大大提升了查詢效率。

 

把索引建立在有大量重複資料的欄位上

把索引建立在有大量重複資料的欄位上，並不能有效地提升SQL效率，比如我的s_phone的取值為"00000000"~"99999999"，此時對s_phone做查詢，未加索引的時候：

看到這條select語句的查詢時間是0.05秒，而給s_phone欄位加了索引之後：

反而變為了0.064秒，並沒有顯著地提升查詢效率，反而更加緩慢。通過explain語句，發現此次SQL通過索引查詢了18000條rows，再去定位這18000多條資料，自然會慢一點。

這說明了，即使查詢的時候用到了索引，也未必能提升查詢的效率，索引建立在重複資料量很少的欄位上效果才明顯，但是這也將導致索引的增大，不過大多數時候這並不是太大的問題。

 

索引與like

不建議對索引列使用like語句，比如說執行以下兩句SQL：

select * from student where s_name like "%99999ssss%";select * from student where s_name like "%99999ssss";

看一下explain出來的結果，都是一樣的：

發現沒有用到索引，這是對索引列使用like的限制，要對索引列使用like，萬用字元只能在結尾，開頭不可以有任何的萬用字元，比如：

select * from student where s_name like "99999ssss%";

此時再explain看一下：

看到這麼實用like則使用到了索引，這不得不說是一個限制。

 

索引與函數

在索引列上使用MySQL函數也會導致索引失效，看一個例子：

select * from student where "99999ssss" = left(s_name, 9);

這條SQL語句非常好理解，查詢s_name列中從左邊開始截取9個字元後的字串為"99999ssss"的記錄，查看一下explain的結果：

結果很明顯，沒有用到索引，這表明對索引列使用函數將導致索引失效。

一個技巧是，依然使用=，但是索引列不使用函數而對常數項使用函數，這樣索引就有效了，當然這條語句是無法這麼最佳化的。

Mysql：索引實戰