一文簡單瞭解MySQL字首索引

2022-04-02 13:02:31

當要索引的列字元很多時索引則會很大且變慢

( 可以只索引列開始的部分字串節約索引空間從而提高索引效率 )

原則： 降低重複的索引值

例如現在有一個地區表

area	gdp	code
chinaShanghai	100	aaa
chinaDalian	200	bbb
usaNewYork	300	ccc
chinaFuxin	400	ddd
chinaBeijing	500	eee

發現 area 欄位很多都是以 china 開頭的

那麼如果以前1-5位字元做字首索引就會出現大量索引值重複的情況

索引值重複性越低查詢效率也就越高

字首索引測試

// 建立一個測試表
CREATE TABLE `x_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `x_name` varchar(255) NOT NULL,
  `x_time` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4145025 DEFAULT CHARSET=utf8mb4

// 新增200萬條測試資料
INSERT INTO x_test(x_name,x_time) SELECT CONCAT(rand()*3300102,x_name),x_time FROM x_test WHERE id < 30000;

200萬測試資料

在無任何索引的情況下隨便查詢一條
SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查詢時間:2.253s
新增字首索引 ( 以第一位字元建立字首索引 )
alter table x_test add index(x_name(1))再次查詢相同sql語句
SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查詢時間:3.291s
當使用第一位字元建立字首索引後貌似查詢的時間更長了
因為只第一位字元而言索引值的重讀性太大了
200萬條資料全以數位開頭那麼平均20萬條的資料都是相同的索引值
重新建立字首索引這次以前4位元字元來建立
alter table x_test add index(x_name(4));再次查詢相同sql語句
SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查詢時間:0.703s
這次以前4位元建立索引大大減少了索引值的重複性查詢速度從3秒提升到0.7秒
200萬條資料都以數位開頭而0-9排列組合7位則可達到千萬種組合
也就是以前7位來做索引則不會出現重複索引值的情況了
alter table x_test add index(x_name(7));再次查詢相同sql語句
SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查詢時間:0.014s ( 首次執行無快取狀態下 )

補充：使用字首索引的執行過程

從index2的索引樹上，找到滿足索引值是“zhangs”的記錄，找到第一個是ID1；
到主鍵索引樹上查到ID1這一行，判斷email的值滿不滿足where後的條件，不滿足這一行丟棄。
繼續回到index2這個索引樹上查下一條記錄，發現如果還是"zhangs"，取出ID2，再回到ID2索引樹上進行判斷，如果值正確，將結果返回結果集中。
重複執行以上流程，直到從index2索引樹上取出的資料不是“zhangs”,迴圈結束。

通過以上執行流程的分析你就可以知道，字首索引會導致掃描的行數變多，這和你所指定字首的長度有關。或許email(7)中的區分度就比email(6)高，就不會掃描那麼多行。

也即是說使用字首索引，定義好長度，就可以節省空間又不用額外增加太多的查詢成本

那怎樣定義字首索引長度比較好呢？

實際上，建立索引時關注的是區分度，區分度越高，越能體現索引的價值和他的優勢。因此我們可以通過統計索引上有不同的值來判斷要使用多長的字首。

select count(distinct email) as L from User;

MySQL 5.7 參考手冊 - 字首索引

總結

到此這篇關於MySQL字首索引的文章就介紹到這了,更多相關MySQL字首索引內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

一文簡單瞭解MySQL字首索引

熱門文章