什么是索引？

數(shù)據(jù)庫(kù)索引是一種數(shù)據(jù)結(jié)構(gòu)，它以額外的寫(xiě)入和存儲(chǔ)空間為代價(jià)來(lái)提高數(shù)據(jù)庫(kù)表上數(shù)據(jù)檢索操作的速度。通俗來(lái)說(shuō)，索引類似于書(shū)的目錄，根據(jù)其中記錄的頁(yè)碼可以快速找到所需的內(nèi)容。——維基百科常見(jiàn)索引有哪些？

普通索引：最基本的索引，沒(méi)有任何限制 唯一索引：與”普通索引“類似，不同的就是：索引列的值必須是唯一，但允許有空值 主鍵索引：它是一種特殊的索引，不允許有空值 全文索引：僅可用于 MyISAM 表，針對(duì)較大的數(shù)據(jù)，生成全文索引很耗時(shí)占空間 組合索引：為了提高多條件查詢效率，可建立組合索引，遵循'最左前綴匹配原則'

這里以相對(duì)復(fù)雜的組合為例，介紹如何優(yōu)化。

最左前綴匹配原則

首先我們要知道什么是最左前綴匹配原則。

最左前綴匹配原則是指在使用 B+Tree 聯(lián)合索引進(jìn)行數(shù)據(jù)檢索時(shí)，MySQL 優(yōu)化器會(huì)讀取謂詞（過(guò)濾條件）并按照聯(lián)合索引字段創(chuàng)建順序一直向右匹配直到遇到范圍查詢或非等值查詢后停止匹配，此字段之后的索引列不會(huì)被使用，這時(shí)計(jì)算 key_len 可以分析出聯(lián)合索引實(shí)際使用了哪些索引列。

如何計(jì)算 key_len

通過(guò) key_len 計(jì)算也幫助我們了解索引的最左前綴匹配原則。

key_len 表示得到結(jié)果集所使用的選擇索引的長(zhǎng)度[字節(jié)數(shù)]，不包括 order by，也就是說(shuō)如果 order by 也使用了索引則 key_len 不計(jì)算在內(nèi)。

在計(jì)算 key_len 之前，先來(lái)溫習(xí)一下基本數(shù)據(jù)類型（以UTF8 編碼為例）：

類型 所占空間 不允許為NULL額外占用 char 一個(gè)字符三個(gè)字節(jié) 一個(gè)字節(jié) varchar 一個(gè)字符三個(gè)字節(jié) 一個(gè)字節(jié) int 四個(gè)字節(jié) 一個(gè)字節(jié) tinyint 一個(gè)字節(jié) 一個(gè)字節(jié)

測(cè)試數(shù)據(jù)表如下：

CREATE TABLE `test_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NOT NULL, `b` int(11) DEFAULT NOT NULL, `c` int(11) DEFAULT NOT NULL, PRIMARY KEY (`id`), KEY `test_table_a_b_c_index` (`a`,`b`,`c`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

命中索引：

mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra |+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 12 | const,const,const | 1 | 100.00 | Using index |+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

可以看到 key_len = 12，這是如何計(jì)算的呢？因?yàn)樽址?UTF8，一個(gè)字段占用四個(gè)字節(jié)，三個(gè)字段就是 4 * 3 = 12 字節(jié)。

是否允許為 NULL，如果允許為 NULL，則需要用額外的字節(jié)來(lái)標(biāo)記該字段，不同的數(shù)據(jù)類型所需的字節(jié)大小不同。

mysql> ALTER TABLE `test_table` CHANGE `a` `a` INT(11) NULL;mysql> ALTER TABLE `test_table` CHANGE `c` `c` INT(11) NULL;mysql> ALTER TABLE `test_table` CHANGE `b` `b` INT(11) NULL;mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra |+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 15 | const,const,const | 1 | 100.00 | Using index |+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

可以看到，當(dāng)字段允許為空時(shí)，這時(shí)的key_len 變成了15 = 4 3 + 1 3（INT 類型為空時(shí)，額外占用一個(gè)字節(jié)）。

索引優(yōu)化

有了這些基礎(chǔ)知識(shí)之后，再來(lái)根據(jù)實(shí)際的SQL 判斷索性性能好壞。

還是以上面那張數(shù)據(jù)表為例，為 a、b、c 三個(gè)字段創(chuàng)建聯(lián)合索引。

SQL 語(yǔ)句 是否索引 explain select * from test_table where a = 1 and b = 2 and c = 3; Extra:Using index key_len: 15 explain select * from test_table where a = 1 and b = 2 and c = 3 order by c; Extra:Using index key_len: 15 explain select * from test_table where b = 2 and c = 3; Extra:Using where; Using index key_len: 15 explain select * from test_table where a = 1 order by c; Extra:Using where; Using index; Using filesort key_len: 5 explain select * from test_table order by a, b, c; Extra:Using index key_len: 15 explain select * from test_table order by a, b, c desc; Extra:Using index; Using filesort key_len:15 explain select * from test_table where a in (1,2) and b in (1,2,3) and c = 1; Extra:Using where; Using index key_len: 15

通常在查看執(zhí)行計(jì)劃時(shí)， Extra 列為 Using index 則表示優(yōu)化器使用了覆蓋索引。

SQL1 可以使用覆蓋索引，性能好 SQL2 可以使用覆蓋索引，同時(shí)避免排序，性能好 SQL3 可以使用覆蓋索引，但是需要根據(jù) where 字句進(jìn)行過(guò)濾 SQL4 可以使用部分索引 a，但無(wú)法避免排序，性能差 SQL5 可以完全使用覆蓋索引，同時(shí)可以避免排序，性能好 SQL6 可以使用覆蓋索引，但無(wú)法避免排序，（這是因?yàn)?MySQL InnoDB 創(chuàng)建索引時(shí)默認(rèn)asc升序，索引無(wú)法自動(dòng)倒序排序） SQL7 可以使用覆蓋索引，但是需要根據(jù) where 子句進(jìn)行過(guò)濾（非定值查詢） 創(chuàng)建索引規(guī)范 考慮到索引維護(hù)的成本，單張表的索引數(shù)量不超過(guò) 5 個(gè)，單個(gè)索引中的字段數(shù)不超過(guò) 5 個(gè) 不在低基數(shù)列上建⽴索引，例如“性別”。 在低基數(shù)列上創(chuàng)建的索引查詢相比全表掃描不一定有性能優(yōu)勢(shì)，特別是當(dāng)存在回表成本時(shí)。 合理創(chuàng)建聯(lián)合索引，(a,b,c) 相當(dāng)于 (a) 、(a,b) 、(a,b,c)。 合理使用覆蓋索引減少I(mǎi)O，避免排序。

以上就是Mysql 索引該如何設(shè)計(jì)與優(yōu)化的詳細(xì)內(nèi)容，更多關(guān)于MySQL 索引設(shè)計(jì)與優(yōu)化的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！