關(guān)于MySQL前置索引

一叠荠、前置索引的好處

使用前置索引的好處

Normally, the entire value of a column is used to build the index - this is fine for short data types (integers and the like) but can result in a lot of data in the index for longer data types (CHAR and VARCHAR, for example). Using an index prefix allows you to make a trade off between the space required for the index and the cardinality of the index.

二、前置索引的坑

第一種情況

  • 索引的長度和對應(yīng)字段中的長度一致（name 與 idx_of_name）
| areas | CREATE TABLE `areas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `areaid` varchar(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `cityid` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_of_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3145 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select id, name from areas order by name;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | areas | index | NULL          | idx_of_name | 152     | NULL | 3144 | Using index |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from areas where name = '龍?zhí)秴^(qū)';
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | areas | ref  | idx_of_name   | idx_of_name | 152     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

mysql>  explain select id, name from areas group by name;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | areas | index | idx_of_name   | idx_of_name | 152     | NULL | 3144 | Using index |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.01 sec)

第二種情況：

  • 索引的長度和對應(yīng)字段中的長度不一致（name 與 idx_of_name）
| areas | CREATE TABLE `areas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `areaid` varchar(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `cityid` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_of_name` (`name`(6))
) ENGINE=InnoDB AUTO_INCREMENT=3145 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select id, name from areas order by name;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | areas | ALL  | NULL          | NULL | NULL    | NULL | 3144 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.01 sec)

mysql> explain select * from areas where name = '龍?zhí)秴^(qū)';
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | areas | ref  | idx_of_name   | idx_of_name | 20      | const |    1 | Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.01 sec)

mysql> explain select id, name from areas group by name;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | areas | ALL  | NULL          | NULL | NULL    | NULL | 3144 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.01 sec)

雖然在查詢上用到了索引刘陶，但是在排序時请契，索引不生效咳榜。

使用索引就一定能加快查詢效率么？不一定爽锥。上面的例子就告訴我們涌韩，前置索引
并不是一個萬能藥，它的確可以幫助我們在一個過長的字段中建立索引氯夷。但同時也會導(dǎo)致排序（order by, group by）查詢都無法使用前置索引臣樱。

三、如何計算出前置索引的最佳長度

關(guān)于如何計算出，最恰當(dāng)?shù)脑O(shè)置索引的長度雇毫，總體思想：

# 全列選擇性
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

# 測試某一長度前綴的選擇性
SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

當(dāng)前置的選擇性越接近全列的選擇性的時候玄捕，索引效果越好。

通匙炻＃可以索引開始的幾個字符桩盲，而不是全部值，以節(jié)約空間并取得好的性能席吴。這使索引需要的空間變小赌结，但是也會降低選擇性。索引選擇性不重復(fù)的索引值和表中所有行的比值孝冒。高選擇性的索引有好處柬姚，因為它使MySQL在查找匹配的時候可以過濾更多的行。唯一索引的選擇率為1庄涡，為最佳值量承。

具體操作如下：

CREATE TABLE `areas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `areaid` varchar(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `cityid` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3145 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(distinct(name))/count(id) from areas;
+---------------------------------+
| count(distinct(name))/count(id) |
+---------------------------------+
|                          0.8954 |
+---------------------------------+
1 row in set (0.01 sec)

mysql> select count(distinct left(name, 2))/count(id) from areas;
+-----------------------------------------+
| count(distinct left(name, 2))/count(id) |
+-----------------------------------------+
|                                  0.8648 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select count(distinct left(name, 3))/count(id) from areas;
+-----------------------------------------+
| count(distinct left(name, 3))/count(id) |
+-----------------------------------------+
|                                  0.8909 |
+-----------------------------------------+
1 row in set (0.01 sec)

mysql> select count(distinct left(name, 5))/count(id) from areas;
+-----------------------------------------+
| count(distinct left(name, 5))/count(id) |
+-----------------------------------------+
|                                  0.8941 |
+-----------------------------------------+
1 row in set (0.01 sec)

mysql> select count(distinct left(name, 6))/count(id) from areas;
+-----------------------------------------+
| count(distinct left(name, 6))/count(id) |
+-----------------------------------------+
|                                  0.8954 |
+-----------------------------------------+
1 row in set (0.00 sec)

前置索引為6時，選擇性和列值選擇性相同穴店。那么就設(shè)定該索引的長度為6位撕捍。

mysql> select max(length(name)) from areas;
+-------------------+
| max(length(name)) |
+-------------------+
|                45 |
+-------------------+
1 row in set (0.11 sec)

mysql> select * from areas where length(name) = 45;
+------+--------+-----------------------------------------------+--------+
| id   | areaid | name                                          | cityid |
+------+--------+-----------------------------------------------+--------+
| 2624 | 530925 | 雙江拉祜族佤族布朗族傣族自治縣                | 530900 |
| 2965 | 622927 | 積石山保安族東鄉(xiāng)族撒拉族自治縣                | 622900 |
+------+--------+-----------------------------------------------+--------+
2 rows in set (0.01 sec)

四、索引的最大長度

mysql> select 255 * 3 from dual;
+---------+
| 255 * 3 |
+---------+
|     765 |
+---------+
1 row in set (0.00 sec)

mysql> show create table areas;

CREATE TABLE `areas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `areaid` varchar(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `cityid` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_of_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3145 DEFAULT CHARSET=utf8 |

mysql> alter table areas change `name` `name` varchar(256) not null;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> alter table areas drop index `idx_of_name`;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table areas change `name` `name` varchar(256) not null;
Query OK, 3144 rows affected (0.06 sec)
Records: 3144  Duplicates: 0  Warnings: 0

mysql> show create table areas;

CREATE TABLE `areas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `areaid` varchar(20) NOT NULL,
  `name` varchar(256) NOT NULL,
  `cityid` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3145 DEFAULT CHARSET=utf8

mysql> alter table areas add index `idx_of_name` (name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

添加索引時泣洞，如果不指定索引的長度忧风，MySQL默認(rèn)會選擇該字段的長度作為索引長度。其實這里

alter table areas add index `idx_of_name` (name)
等同于
alter table areas add index `idx_of_name` (name(256))

MySQL 索引最大 bytes 為 767球凰， 255 * 3 < 767 但是 256 * 3 > 767 所以狮腿，varchar(255)時候，創(chuàng)建默認(rèn)索引可以成功呕诉，但是varchar(256)時缘厢，創(chuàng)建默認(rèn)索引就不成功。

并且由上述列子可知甩挫，索引和字段相互影響贴硫，當(dāng)索引設(shè)置為 idx_of_name (name(255))時，要改變字段的長度捶闸，也會提示 Specified key was too long 錯誤夜畴。

總結(jié)

根據(jù)具體的業(yè)務(wù)需求來選擇索引，并不是索引都會加快查詢速度删壮。

參考

錯誤使用MySQL前綴索引導(dǎo)致的慢查詢
MySQL 前綴索引
mysql前綴索引
mysql省市區(qū)郵政編碼和區(qū)號級聯(lián)sql文件

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末贪绘，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子央碟，更是在濱河造成了極大的恐慌税灌，老刑警劉巖均函，帶你破解...
    沈念sama閱讀 207,113評論 6贊 481
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異菱涤，居然都是意外死亡苞也，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,644評論 2贊 381
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門粘秆，熙熙樓的掌柜王于貴愁眉苦臉地迎上來如迟，“玉大人，你說我怎么就攤上這事攻走∫罂保” “怎么了？”我有些...
    開封第一講書人閱讀 153,340評論 0贊 344
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵昔搂，是天一觀的道長玲销。 經(jīng)常有香客問我，道長摘符，這世上最難降的妖魔是什么贤斜？ 我笑而不...
    開封第一講書人閱讀 55,449評論 1贊 279
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮逛裤，結(jié)果婚禮上瘩绒，老公的妹妹穿的比我還像新娘。我一直安慰自己带族，他們只是感情好草讶，可當(dāng)我...
    茶點故事閱讀 64,445評論 5贊 374
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布。 她就那樣靜靜地躺著炉菲，像睡著了一般。 火紅的嫁衣襯著肌膚如雪坤溃。 梳的紋絲不亂的頭發(fā)上拍霜，一...
    開封第一講書人閱讀 49,166評論 1贊 284
  • 城市分裂傳說
    那天，我揣著相機與錄音薪介，去河邊找鬼祠饺。 笑死，一個胖子當(dāng)著我的面吹牛汁政，可吹牛的內(nèi)容都是我干的道偷。 我是一名探鬼主播，決...
    沈念sama閱讀 38,442評論 3贊 401
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼记劈，長吁一口氣：“原來是場噩夢啊……” “哼勺鸦！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起目木，我...
    開封第一講書人閱讀 37,105評論 0贊 261
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤换途，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體军拟，經(jīng)...
    沈念sama閱讀 43,601評論 1贊 300
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡剃执，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,066評論 2贊 325
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了懈息。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片肾档。...
    茶點故事閱讀 38,161評論 1贊 334
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖辫继，靈堂內(nèi)的尸體忽然破棺而出怒见，到底是詐尸還是另有隱情，我是刑警寧澤骇两，帶...
    沈念sama閱讀 33,792評論 4贊 323
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布速种，位于F島的核電站，受9級特大地震影響低千，放射性物質(zhì)發(fā)生泄漏配阵。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點故事閱讀 39,351評論 3贊 307
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一示血、第九天 我趴在偏房一處隱蔽的房頂上張望棋傍。 院中可真熱鬧，春花似錦难审、人聲如沸瘫拣。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,352評論 0贊 19
  • 一樁弒父案告喊，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽麸拄。三九已至，卻和暖如春黔姜，著一層夾襖步出監(jiān)牢的瞬間拢切，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 31,584評論 1贊 261
  • 情欲美人皮
    我被黑心中介騙來泰國打工秆吵， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留淮椰，地道東北人。 一個月前我還...
    沈念sama閱讀 45,618評論 2贊 355
  • 代替公主和親
    正文 我出身青樓纳寂，卻偏偏與公主長得像主穗，于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子毙芜，可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 42,916評論 2贊 344

推薦閱讀更多精彩內(nèi)容

  • 本文轉(zhuǎn)載自http://blog.jobbole.com/24006/ 摘要本文以MySQL數(shù)據(jù)庫為研究對象忽媒，討論...
    海納百川_spark閱讀 4,455評論 3贊 135
  • SQL SELECT 語句 一、查詢SQL SELECT 語法 （1）SELECT 列名稱 FROM 表名稱 （2...
    有錢且幸福閱讀 5,426評論 0贊 33
  • 什么是數(shù)據(jù)庫爷肝？ 數(shù)據(jù)庫是存儲數(shù)據(jù)的集合的單獨的應(yīng)用程序猾浦。每個數(shù)據(jù)庫具有一個或多個不同的API陆错，用于創(chuàng)建，訪問金赦，管理...
    chen_000閱讀 4,030評論 0贊 19
  • 推開故鄉(xiāng)老宅那吱吱呀呀的木門音瓷，迎面跑來的是記憶里的林氏四兄妹。沉著嚴(yán)肅的是大哥夹抗，眼睛賊亮賊亮的绳慎；一臉詭笑的是二哥，...
    零凝閱讀 516評論 3贊 3
  • 1."ISIS: Inside the Army of Terror"漠烧，在美國關(guān)于ISIS深度報道最早的幾本書之一...
    話嘮吉米閱讀 491評論 0贊 5