1. MySQL 索引的最左前綴原則

左前綴原則是聯(lián)合索引在使用時(shí)要遵循的原則，查詢(xún)索引可以使用聯(lián)合索引的一部分，但是必須從最左側(cè)開(kāi)始。在創(chuàng)建聯(lián)合索引時(shí)，要根據(jù)業(yè)務(wù)需求，where子句中將使用最頻繁的一列放在最左邊，mysql會(huì)一直向右匹配直到遇到范圍查詢(xún)(>、<、between、like)時(shí)停止匹配。即范圍列可以用到索引，范圍列后面的列無(wú)法用到索引。

比如查詢(xún) a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a，b，d，c) 順序的索引，d是用不到索引的，如果建立 (a，b，d，c) 的索引則都可以用到，a，b，d的順序可以任意調(diào)整?？梢哉{(diào)整 a，b，c 順序的原因是 MySQL 具有查詢(xún)優(yōu)化器

MySQL 查詢(xún)優(yōu)化器

當(dāng)按照索引中所有列進(jìn)行精確匹配（“=” 或 “IN”）時(shí)，索引可以被用到，并且 type 為 const。理論上索引對(duì)順序是敏感的，但是 MySQL 的查詢(xún)優(yōu)化器會(huì)自動(dòng)調(diào)整 where 子句的條件順序以使用適合的索引，所以 MySQL 在進(jìn)行精確匹配時(shí)不存在因 where 子句的順序問(wèn)題而造成索引失效。

2. 前綴索引

定義： 對(duì)于BLOB、TEXT，或者很長(zhǎng)的VARCHAR類(lèi)型的列，為它們的前幾個(gè)字符(具體幾個(gè)字符是在建立索引時(shí)指定的)建立索引，這樣的索引就叫前綴索引。

優(yōu)點(diǎn)： 這樣建立起來(lái)的索引更小，所以查詢(xún)更快。

缺點(diǎn)： 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把前綴索引用作覆蓋索引。

創(chuàng)建方法：

alter table table_name add key( column_name( prefix_length));

注：這里最關(guān)鍵的參數(shù)就是 prefix_length，這個(gè)值需要根據(jù)實(shí)際表的內(nèi)容，來(lái)得到合適的索引選擇性。

prefix_length 計(jì)算方法：

先計(jì)算完整列的選擇性 :

select count(distinct col_1)/count(1) from table_1

再計(jì)算不同前綴長(zhǎng)度的選擇性 :

select count(distinct left(col_1,4))/count(1) from table_1

到最優(yōu)長(zhǎng)度之后，創(chuàng)建前綴索引 :

create index idx_front on table_1 (col_1(4))

3. 索引下推（ICP——Index Condition Pushdown）

定義： 索引下推 Index Condition Pushdown(ICP) 是MySQL使用索引從表中檢索行數(shù)據(jù)的一種優(yōu)化方式，從 MySQL5.6 開(kāi)始支持。5.6 之前，存儲(chǔ)引擎會(huì)通過(guò)遍歷索引定位基表中的行，然后返回給 Server層,再去為這些數(shù)據(jù)行進(jìn)行 WHERE 后的條件的過(guò)濾。MySQL5.6之后支持 ICP，如果WHERE條件可以使用索引，MySQL 會(huì)把這部分過(guò)濾操作放到存儲(chǔ)引擎層，存儲(chǔ)引擎通過(guò)索引過(guò)濾，把滿(mǎn)足的行從表中讀取出。ICP 能減少引擎層訪問(wèn)基表的次數(shù)（回表次數(shù)）和 Server層訪問(wèn)存儲(chǔ)引擎的次數(shù)。

MySQL通過(guò) optimizer_switch 參數(shù)中的 index_condition_pushdown 選項(xiàng)來(lái)控制,默認(rèn)是開(kāi)啟的。

操作：

查看是否開(kāi)啟

show variables like'% optimizer_switch%';

設(shè)置 ICP

SET optimizer_switch = ‘index_condition_pushdown=off';

例子：

在 people_table中有一個(gè)二級(jí)索引(zipcode，lastname，firstname)，查詢(xún)是

SELECT * FROM people WHERE zipcode='95054′ AND lastname LIKE ‘%etrunia%' AND address LIKE ‘%Main Street%';

• 如果沒(méi)有使用索引下推技術(shù)，則MySQL會(huì)通過(guò)zipcode=’95054’從存儲(chǔ)引擎中查詢(xún)對(duì)應(yīng)的數(shù)據(jù)，返回到MySQL服務(wù)端，然后MySQL服務(wù)端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’來(lái)判斷數(shù)據(jù)是否符合條件
• 如果使用了索引下推技術(shù)，則MYSQL首先會(huì)返回符合zipcode=’95054’的索引，然后根據(jù)lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’來(lái)判斷索引是否符合條件。如果符合條件，則根據(jù)該索引來(lái)定位對(duì)應(yīng)的數(shù)據(jù)，如果不符合，則直接reject掉。

4. 查看 MySQL 語(yǔ)句是否用到索引

1. 方法一：通過(guò) show index 查看表中含那些索引

show index from table_name

2. 方法二：通過(guò) explain 命令查看 SQL 語(yǔ)句的執(zhí)行計(jì)劃：

例子：

explain select * from t where name = 'name1';

• 我們可以先從查詢(xún)類(lèi)型type列開(kāi)始查看，如果出現(xiàn)all關(guān)鍵字，后面的內(nèi)容就都可以不用看了，代表全表掃描。—— const(主鍵索引或者唯一二級(jí)索引進(jìn)行等值匹配的情況下)，ref(普通的?級(jí)索引列與常量進(jìn)?等值匹配)，index(掃描全表索引的覆蓋索引)
• 再看key列，看是否使用了索引， null代表沒(méi)有使用索引。
• 然后看rows列，該列用來(lái)表示在SQL執(zhí)行過(guò)程中被掃描的行數(shù)，該數(shù)值越大，意味著需要掃描的行數(shù)越多，相應(yīng)的耗時(shí)就更長(zhǎng)。
• 最后再看extra列,在這列中要觀察是否有Using filesort或者Using temporary這樣的關(guān)鍵字出現(xiàn),這些是很影響數(shù)據(jù)庫(kù)性能的。
• MySQL5.7的執(zhí)行計(jì)劃中會(huì)默認(rèn)添加filtered列 (MySQL5.6 使用 explain extended 也會(huì)增加此列)，它指返回結(jié)果的行占需要讀到的行(rows 列的值)的百分比。需要注意的是， explain中輸出的rows只是一個(gè)估算值。本例中該表進(jìn)行了全表掃描。

5. 為什么官方建議用自增長(zhǎng)主鍵作為索引

減少分裂和移動(dòng)的頻率： 結(jié)合B+Tree的特點(diǎn)，自增主鍵是連續(xù)的，在插入過(guò)程中能盡量減少頁(yè)分裂，即使要進(jìn)行頁(yè)分裂，也只會(huì)分裂很少一部分。并且能減少數(shù)據(jù)的移動(dòng)，每次插入都是插入到最后。

6. 如何創(chuàng)建索引

1. 在執(zhí)行 CREATE TABLE 時(shí)創(chuàng)建索引

CREATE TABLE user_index2 (
 id INT auto_increment PRIMARY KEY,
 first_name VARCHAR (16),
 last_name VARCHAR (16),
 id_card VARCHAR (18),
 information text,
 KEY name (first_name, last_name),
 FULLTEXT KEY (information),
 UNIQUE KEY (id_card)
);

2. 使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE 可用來(lái)創(chuàng)建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中 table_name 是要增加索引的表名，column_list 指出對(duì)哪些列進(jìn)行索引，多列時(shí)各列之間用逗號(hào)分隔。

索引名 index_name 可自己命名，缺省時(shí)，MySQL將根據(jù)第一個(gè)索引列賦一個(gè)名稱(chēng)。另外，ALTER TABLE允許在單個(gè)語(yǔ)句中更改多個(gè)表，因此可以在同時(shí)創(chuàng)建多個(gè)索引。

3. 使用CREATE INDEX命令創(chuàng)建。

CREATE INDEX index_name ON table_name (column_list);

7. 創(chuàng)建索引注意事項(xiàng)

• 選擇性低的字段不要?jiǎng)?chuàng)建索引(例如，性別sex、狀態(tài)status)。
• 很少查詢(xún)的列不要?jiǎng)?chuàng)建索引(項(xiàng)目初期就要確定好)。
• 大數(shù)據(jù)類(lèi)型字段不要?jiǎng)?chuàng)建索引。
• 盡量避免不要使用NULL,應(yīng)該指定列為NOTNULL(在MySQL中,含有空值的列很難進(jìn)行查詢(xún)優(yōu)化，它們會(huì)使得索引、索引的統(tǒng)計(jì)信息及比較運(yùn)算更加復(fù)雜?？梢允褂每兆址婵罩?。

8. 使用索引一定可以提高查詢(xún)性能嘛

通常通過(guò)索引查詢(xún)數(shù)據(jù)比全表掃描要快。但是我們也必須注意到它的代價(jià)。索引需要空間來(lái)存儲(chǔ)，也需要定期維護(hù)， 每當(dāng)有記錄在表中增減或索引列被修改時(shí)，索引本身也會(huì)被修改。 這意味著每條記錄的I* NSERT，DELETE，UPDATE將為此多付出4，5 次的磁盤(pán)I/O。 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理，那些不必要的索引反而會(huì)使查詢(xún)反應(yīng)時(shí)間變慢。使用索引查詢(xún)不一定能提高查詢(xún)性能。

9. 索引失效

• 通過(guò)索引掃描的行記錄數(shù)超過(guò)全表的30%，優(yōu)化器就不會(huì)走索引，而變成全表掃描。
• 聯(lián)合索引中，第一個(gè)查詢(xún)條件不是最左索引列。 —— 優(yōu)化器
• 聯(lián)合索引中，第一個(gè)查詢(xún)條件不是最左前綴列。—— 優(yōu)化器
• 聯(lián)合索引中，第一個(gè)索引列使用范圍查詢(xún)，只能使用到部分索引，有ICP出現(xiàn) (范圍查詢(xún)是指<、=、<=、BETWEEN and)。
• 模糊查詢(xún)條件列最左以通配符%開(kāi)始(可以考慮放到子查詢(xún)里面)。
• 兩個(gè)單列索引，一個(gè)用于檢索，一個(gè)用于排序。這種情況下只能使用到一個(gè)索引。因?yàn)椴樵?xún)語(yǔ)句中最多只能使用一個(gè)索引，考慮建立聯(lián)合索引。
• 查詢(xún)字段上面有索引，但是使用了函數(shù)運(yùn)算。

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