MySQL調優之索引在什麼情況下會失效？

MySQL中提高性能的一個最有效的方式是對資料表設計合理的索引。索引提供了高效通路資料的方法，并且加快查詢的速度，是以索引對查詢的速度有着至關重要的影響。

使用索引可以快速地定位表中的某條記錄，進而提高資料庫查詢的速度，提高資料庫的性能。
如果查詢時沒有使用索引，查詢語句就會掃描表中的所有記錄。在資料量大的情況下，這樣查詢的速度回很慢。

大多數情況下都（預設）采用B+樹來建構索引。隻是空間列類型的索引使用R-樹，并且MEMORY表還支援hash索引。

其實，用不用索引，最終都是優化器說了算。優化器是基于什麼的考慮？基于cost開銷（CostBaseOptimizer），它不是基于規則（Rule-BasedOptimizer），也不是基于語義，隻是依據數值大小。另外，SQL語句是否使用索引，跟資料庫版本、資料量、資料選擇度都有關系。

本文我們嘗試總結索引失效的一些場景。我們會準備class和student兩個表，class插入一萬條資料，student插入50萬條資料。

【1】全值比對我最愛

系統中經常出現的SQL語句如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

建立索引前執行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時間: 0.308s

建立索引(age)：

CREATE INDEX idx_age ON student(age);

建立索引後執行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時間: 0.113s

繼續建立索引(age,classId)：

CREATE INDEX idx_age_classid ON student(age,classId);

建立索引後執行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時間: 0.007s

繼續建立索引(age,classId,NAME)：

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

建立索引後執行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時間: 0.000s  # 其實必然不是0，隻是更小了

從執行計劃可以看到，MySQL會幫我們選擇

最多包含查詢列

的聯合索引。

【2】最佳左字首法則

在MySQL建立聯合索引時會遵守最佳左字首比對原則，即最左優先，在檢索資料時從聯合索引的最左邊開始比對。

舉例：age、name可以用到索引。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd' ;

雖然可以正常使用，但是隻有部分被使用到了。而且MySQL優化器考慮的索引是idx_age，而非idx_age_classid_name。

舉例2：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';

可以看到，沒有age開頭，完全沒有用到索引。

舉例3：索引idx_age_classid_name還能否正常使用？

# MySQL會進行優化，形成age,classid,name以符合聯合索引idx_age_classid_name
EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE classid=4 AND student.age=30 AND student.name = 'abcd';

如果索引了多列，要遵守最左字首法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。

我們删掉索引idx_age 、idx_age_classid 再次執行查詢age and name，沒有中間的classid。

DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid ON student;

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abcd';

這裡key_len=5，說明隻用到了聯合索引的一部分–age用到了索引。因為其中間環節 classid不存在，故而不能完全使用聯合索引。

結論： MySQL可以為多個字段建立索引，一個索引可以包括16個字段。對于多列索引，過濾條件要使用索引必須按照索引建立時的順序，依次滿足，一旦跳過某個字段，索引後面的字段都無法被使用。如果查詢條件中沒有使用這些字段中第一個字段時，多列（或聯合）索引不會被使用。

對于

=值

查詢，如果where中條件查詢沒有按照聯合索引字段順序編寫，MySQL優化器會進行調優以使其滿足聯合索引字段順序。

【3】主鍵插入順序

對于一個使用InnoDB存儲引擎的表來說，在我們沒有顯示的建立索引時，表中的資料實際上都是存儲在聚簇索引的葉子節點的。而記錄又是存儲在資料頁中的，資料頁和記錄又是按照記錄主鍵值從小到大的順序進行排序。是以如果我們插入的記錄的主鍵值是依次增大的話，那我們每插滿一個資料頁就換到下一個資料頁繼續插。

而如果我們插入的主鍵值忽大忽小的話，就比較麻煩了。假設某個資料頁存儲的記錄已經滿了，它存儲的主鍵值在1~100之間：

如果此時再插入一條主鍵值為9的記錄，那它插入的位置就如下圖：

可這個資料頁已經滿了，再插進來咋辦呢？我們需要把目前頁面分裂成兩個頁面，把本頁中的一些記錄移動到新建立的這個頁中。

頁面分裂和記錄移位意味着什麼？意味着性能損耗！

是以如果我們想進來避免這樣無謂的性能損耗，最好讓插入的記錄的主鍵值依次遞增，這樣就不會發生這樣的性能損耗了。

是以我們建議：讓主鍵具有AUTO_INCREMENT，讓存儲引擎自己為表生成主鍵，而不是我們手動插入，比如person_info表：

create table person_info(
  id int unsigned not null auto_increment,
  name varchar(100) not null,
  birthday date not null,
  phone_numnber char(11) not null,
  country varchar(100) not null,
  primary key (id),
  key idx_name_bd_ph_num(name(10),birthday,phone_number)
)

我們自定義的主鍵列id擁有

AUTO_INCREMENT

屬性，在插入記錄時存儲引擎會自動為我們填入自增的主鍵值。這樣的主鍵占用空間小，順序寫入，減少頁分裂。

【4】計算、函數、類型轉換(自動或手動)導緻索引失效

如下兩條SQL，哪個更好呢？其實是第一條，能夠使用到索引，第二條有了函數計算。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

我們建立索引(NAME)：

CREATE INDEX idx_name ON student(NAME);

檢視第一條SQL的執行計劃：

檢視第二條SQL的執行計劃：

對比執行計劃可以看到，第一條SQL使用到了索引，第二條SQL的type=all表示全表掃描。說明

函數計算或導緻索引失效

。

我們再看一下數學計算：

CREATE INDEX idx_sno ON student(stuno);

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

如上圖所示，SQL中有數學計算，執行計劃中 type=all表示沒有使用索引進行了全表掃描。我們再看下面這個SQL，很顯然其會使用到索引。這就說明

數學計算會導緻索引失效

。

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

最後我們再看一下類型轉換

字元串類型一定不要忘記單引号，否則索引失效。

# 會進行隐式類型轉換 ，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';

對比二者的執行計劃可知，

類型轉換會導緻索引失效

。

【5】範圍條件右邊的列索引失效

首先删除表student的索引：

alter table student drop index idx_name;
alter table student drop index idx_age;
alter table student drop index idx_age_classid;

檢視目前索引：

show index from student;

對于如下SQL，索引idx_age_classid_name還能夠正常使用嗎？

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId > 20 AND student.name = 'abc' ;

執行計劃如下所示，key_len=10，說明隻有age和classid用到了索引。

這時候即使交換次序，也是沒有意義的，如下所示：

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

那麼如何使其能夠使用到索引呢？如下所示建立索引(age,NAME,classId)。

CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);

這時再執行上面SQL，可以看到充分用到了聯合索引。

對于下面這個SQL，執行計劃是一樣的。查詢優化器對于and條件會進行順序的調整，以滿足聯合索引的順序。

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId > 20 AND student.name = 'abc' ;

總結

範圍 右邊的列 不能使用索引。比如 < 、<=、 >、 >=、 between。
這個 右邊 指的是聯合索引字段的右邊，至于SQL where中的and條件，查詢優化器是可以進行調整的。
建立的聯合索引中，務必把範圍涉及到的字段寫在最後。

【6】不等于(!=或者 <>) 索引失效

為name字段建立索引：

CREATE INDEX idx_name ON student(NAME);

進行等值判斷，正常使用索引：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = 'abc' ;

對于不等判斷，檢視索引是否失效：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;

可以看到，兩條SQL均為使用到索引。

【7】is null可以使用索引，is not null無法使用索引

is null可以觸發索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME IS NULL;

is not null無法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME IS NOT NULL;

結論：最好在設計資料表的時候就将字段設定為

not null

限制，比如你可以将int類型的字段，預設值設定為

.将字元類型的預設值設定為空字元

('')

。同理，在查詢中使用

not like

也無法使用索引，導緻全表掃描。

【8】like以通配符%開頭索引失效

在使用like關鍵字進行查詢的查詢語句中，如果比對字元串的第一個字元為

“%”

，索引就不會起作用。隻有

"%"

不在第一個位置，索引才會起作用。

使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';

沒有用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';

【9】OR前後存在非索引的列，索引失效

在where子句中，如果在or前的條件列進行了索引，而在or後的條件列沒有進行索引，那麼索引會失效。也就是說，OR 前後的兩個條件中的列都是索引列時，查詢中才會使用到索引。

因為OR的含義就是兩個隻要滿足一個即可，是以隻有一個條件列進行了索引是沒有意義的。隻要有條件列沒有進行索引，就會進行全表掃描，是以索引的條件列也會失效。

SHOW INDEX FROM student;

# 删除索引
alter table student drop index idx_age_classid_name;
alter table student drop index idx_age_name_cid;
alter table student drop index idx_sno;
alter table student drop index idx_name;

#建立索引
CREATE INDEX idx_age ON student(age);

這時我們查詢語句使用OR關鍵字的情況（age有索引，classid沒有索引）

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

可以看到，是沒有使用到索引的。如果我們為classid建立索引呢？

CREATE INDEX idx_cid ON student(classid);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

可以看到，其使用到了索引，

type=index_merge

。簡單來說，index_merge就是對age和classid分别進行了掃描，然後将這兩個結果集進行了合并。這樣做的好處就是避免了全表掃描。

【10】資料庫和表的字元集統一使用utf8mb4

統一使用utf8mb4(5.5.3版本以上支援)相容性更好，統一字元集可以避免由于字元集轉換産生的亂碼。

對于單列索引，盡量選擇針對目前query過濾性更好的索引；
在選擇組合索引的時候，目前query中過濾性最好的字段在索引字段順序中，位置越靠前越好；
在選擇組合索引的時候，盡量選擇能夠包含目前query中的where子句中更多字段的索引；
在選擇組合索引的時候，如果某個字段可能出現範圍查詢時，盡量把這個字段放在索引次序的最後面。

MySQL調優之索引在什麼情況下會失效？

【1】全值比對我最愛

【2】最佳左字首法則

【3】主鍵插入順序

【4】計算、函數、類型轉換(自動或手動)導緻索引失效

【5】範圍條件右邊的列索引失效

【6】不等于(!=或者 <>) 索引失效

【7】is null可以使用索引，is not null無法使用索引

【8】like以通配符%開頭索引失效

【9】OR前後存在非索引的列，索引失效

【10】資料庫和表的字元集統一使用utf8mb4

繼續閱讀

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

詳解STM32單片機的堆棧

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離