索引
無論是面試,還是實際工作中,對於一個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的結果:
結果很明顯,沒有用到索引,這表明對索引列使用函式將導致索引失效。
一個技巧是,依然使用=,但是索引列不使用函式而對常數項使用函式,這樣索引就有效了,當然這條語句是無法這麼優化的。