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

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

比如查詢 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 具有查詢優(yōu)化器

MySQL 查詢優(yōu)化器

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

2. 前綴索引

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

優(yōu)點(diǎ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)容，來得到合適的索引選擇性。

prefix_length 計(jì)算方法：

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

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

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

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

到最優(yōu)長度之后，創(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 開始支持。5.6 之前，存儲(chǔ)引擎會(huì)通過遍歷索引定位基表中的行，然后返回給 Server層,再去為這些數(shù)據(jù)行進(jìn)行 WHERE 后的條件的過濾。MySQL5.6之后支持 ICP，如果WHERE條件可以使用索引，MySQL 會(huì)把這部分過濾操作放到存儲(chǔ)引擎層，存儲(chǔ)引擎通過索引過濾，把滿足的行從表中讀取出。ICP 能減少引擎層訪問基表的次數(shù)（回表次數(shù)）和 Server層訪問存儲(chǔ)引擎的次數(shù)。

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

操作：

查看是否開啟

show variables like'% optimizer_switch%';

設(shè)置 ICP

SET optimizer_switch = ‘index_condition_pushdown=off';

例子：

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

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

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

4. 查看 MySQL 語句是否用到索引

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

show index from table_name

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

例子：

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

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

5. 為什么官方建議用自增長主鍵作為索引

減少分裂和移動(dòng)的頻率： 結(jié)合B+Tree的特點(diǎn)，自增主鍵是連續(xù)的，在插入過程中能盡量減少頁分裂，即使要進(jìn)行頁分裂，也只會(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 可用來創(chuàng)建普通索引、UNIQUE索引或PRIMARY KEY索引。

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

索引名 index_name 可自己命名，缺省時(shí)，MySQL將根據(jù)第一個(gè)索引列賦一個(gè)名稱。另外，ALTER TABLE允許在單個(gè)語句中更改多個(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)。
• 很少查詢的列不要?jiǎng)?chuàng)建索引(項(xiàng)目初期就要確定好)。
• 大數(shù)據(jù)類型字段不要?jiǎng)?chuàng)建索引。
• 盡量避免不要使用NULL,應(yīng)該指定列為NOTNULL(在MySQL中,含有空值的列很難進(jìn)行查詢優(yōu)化，它們會(huì)使得索引、索引的統(tǒng)計(jì)信息及比較運(yùn)算更加復(fù)雜?？梢允褂每兆址婵罩?。

8. 使用索引一定可以提高查詢性能嘛

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

9. 索引失效

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

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

最新評(píng)論