當(dāng)要索引的列字符很多時(shí) 索引則會(huì)很大且變慢

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

原則： 降低重復(fù)的索引值

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

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

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

索引值重復(fù)性越低 查詢(xún)效率也就越高

前綴索引測(cè)試

// 創(chuàng)建一個(gè)測(cè)試表
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萬(wàn)條測(cè)試數(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萬(wàn) 測(cè)試數(shù)據(jù)

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

• 添加前綴索引 ( 以第一位字符創(chuàng)建前綴索引 )
  alter table x_test add index(x_name(1))再次查詢(xún)相同sql語(yǔ)句
  SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查詢(xún)時(shí)間:3.291s
  當(dāng)使用第一位字符創(chuàng)建前綴索引后 貌似查詢(xún)的時(shí)間更長(zhǎng)了
  因?yàn)橹坏谝晃蛔址运饕档闹刈x性太大了
  200萬(wàn)條數(shù)據(jù)全以數(shù)字開(kāi)頭那么平均20萬(wàn)條的數(shù)據(jù)都是相同的索引值

• 重新建立前綴索引 這次以前4位字符來(lái)創(chuàng)建
  alter table x_test add index(x_name(4));再次查詢(xún)相同sql語(yǔ)句
  SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';查詢(xún)時(shí)間:0.703s
  這次以前4位創(chuàng)建索引 大大減少了索引值的重復(fù)性 查詢(xún)速度從3秒提升到0.7秒

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

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

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

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

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

那怎樣定義前綴索引長(zhǎng)度比較好呢？

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

select count(distinct email) as L from User;

MySQL 5.7 參考手冊(cè) - 前綴索引

總結(jié)

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

最新評(píng)論