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

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

原則： 降低重復的索引值

例如現(xiàn)在有一個地區(qū)表

發(fā)現(xiàn) area 字段很多都是以 china 開頭的

那么如果以前1-5位字符做前綴索引就會出現(xiàn)大量索引值重復的情況

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

前綴索引測試

// 創(chuàng)建一個測試表
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萬條測試數(shù)據(jù)
INSERT INTO x_test(x_name,x_time) SELECT CONCAT(rand()*3300102,x_name),x_time FROM x_test WHERE id < 30000;

200萬 測試數(shù)據(jù)

• 在無任何索引的情況下隨便查詢一條
  SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查詢時間:2.253s

• 添加前綴索引 ( 以第一位字符創(chuàng)建前綴索引 )
  alter table x_test add index(x_name(1))再次查詢相同sql語句
  SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查詢時間:3.291s
  當使用第一位字符創(chuàng)建前綴索引后 貌似查詢的時間更長了
  因為只第一位字符而言索引值的重讀性太大了
  200萬條數(shù)據(jù)全以數(shù)字開頭那么平均20萬條的數(shù)據(jù)都是相同的索引值

• 重新建立前綴索引 這次以前4位字符來創(chuàng)建
  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位創(chuàng)建索引 大大減少了索引值的重復性 查詢速度從3秒提升到0.7秒

• 200萬條數(shù)據(jù)都以數(shù)字開頭 而0-9排列組合7位則可達到千萬種組合
  也就是以前7位來做索引則不會出現(xiàn)重復索引值的情況了
  alter table x_test add index(x_name(7));再次查詢相同sql語句
  SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查詢時間:0.014s ( 首次執(zhí)行無緩存狀態(tài)下 )

補充：使用前綴索引的執(zhí)行過程

1. 從index2的索引樹上，找到滿足索引值是“zhangs”的記錄，找到第一個是ID1；
2. 到主鍵索引樹上查到ID1這一行，判斷email的值滿不滿足where后的條件，不滿足這一行丟棄。
3. 繼續(xù)回到index2這個索引樹上查下一條記錄，發(fā)現(xiàn)如果還是"zhangs"，取出ID2，再回到ID2索引樹上進行判斷，如果值正確，將結果返回結果集中。
4. 重復執(zhí)行以上流程，直到從index2索引樹上取出的數(shù)據(jù)不是“zhangs”,循環(huán)結束。

通過以上執(zhí)行流程的分析你就可以知道，前綴索引會導致掃描的行數(shù)變多，這和你所指定前綴的長度有關?；蛟Semail(7)中的區(qū)分度就比email(6)高，就不會掃描那么多行。

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

那怎樣定義前綴索引長度比較好呢？

實際上，建立索引時關注的是區(qū)分度，區(qū)分度越高，越能體現(xiàn)索引的價值和他的優(yōu)勢。因此我們可以通過統(tǒng)計索引上有不同的值來判斷要使用多長的前綴。

select count(distinct email) as L from User;

MySQL 5.7 參考手冊 - 前綴索引

總結

到此這篇關于MySQL前綴索引的文章就介紹到這了,更多相關MySQL前綴索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論