MySQL 前綴索引

當(dāng)要索引的列字符很多時(shí) 索引則會很大且變慢
( 可以只索引列開始的部分字符串 節(jié)約索引空間 從而提高索引效率 )

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

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

發(fā)現(xiàn) area 字段很多都是以 china 開頭的
那么如果以前1-5位字符做前綴索引就會出現(xiàn)大量索引值重復(fù)的情況
索引值重復(fù)性越低 查詢效率也就越高

前綴索引測試

// 創(chuàng)建一個(gè)測試表
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ù)

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

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

3. 重新建立前綴索引 這次以前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';
   查詢時(shí)間:0.703s
   這次以前4位創(chuàng)建索引 大大減少了索引值的重復(fù)性 查詢速度從3秒提升到0.7秒

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

MySQL 5.7 參考手冊 - 前綴索引