概述

隨著電商的發展，使用數據庫的業務越來越復雜，除了掌握哪些場景可以使用索引，哪些場景適合使用索引，還需要掌握索引在運行過程中的一些使用規則，特別是組合索引的使用。比如索引的優先級，索引失效場景等等，掌握這些規則，可以幫助分析并解決或優化在數據庫運行過程中遇到的索引問題。

索引優先級

索引都有效的情況下，如果查詢語句中同時有主鍵索引和輔助索引或多個索引，MySQL會如何執行查找？此種情況下兩種索引會如何使用，順序如何？索引在使用的優先級上，遵循以下規則

• 索引字段使用等值或全值匹配(=)的索引優先于使用范圍匹配(like <>between)的索引
• 比較類型相同時，主鍵索引優先于輔助索引
• 優先級別相同時，出現在語句中的最左邊順序優先

所以，查詢語句中有多個索引的情況下，如果等值比較的主鍵索引優先；如果主鍵是范圍比較，輔助索引是等值比較，則輔助索引優先，否則主鍵索引優先；多個輔助索引，比較類型相同時，按照出現在語句最左邊的順序優先。

優先的索引將被用于檢索數據，剩下的索引，如果是有效索引，將作為索引下推的索引過濾條件(index filter)對數據進行過濾，否則將作為一般的WHERE條件和其它WHERE條件一起在Server層對數據進行過濾

組合索引匹配原則

組合索引是由多個字段組成的索引，MySQL會按照創建的順序使用或匹配索引。在一棵索引樹上，多個字段形成索引，效率高、省空間、容易形成覆蓋索引，即查詢結果均在索引上，直接作為結果返回，不需要再回表查詢一次。使用組合索引，遵循最左前綴原則：

前綴索引

• 通配符在最右邊時 ，組合索引有效

比如格式[索引字段 like 常量%]，前面的索引字段和like本身的索引字段有效，但like后面的索引字段將失效。

• 通配符在最左邊時，索引失效

比如格式[索引字段 like %常量]，將不使用對應的組合索引。在結果字段、排序或分組字段形成覆蓋索引的情況下，使用[索引字段 like %常量]，執行計劃會顯示使用index索引，但其實和like無關，顯示index是因為覆蓋索引的原因，注意區分。

最左前綴

即按照創建順序使用，從左向右匹配直到遇到范圍查詢(like、>、<、<>或!=、between) 組合索引失效，即該字段后面的字段即使是組合索引中的字段也同樣使用不到索引。

索引失效分析

在InnoDB引擎中，如果使用輔助索引使用范圍查詢且結果大于總數據記錄三分之一時，如果沒有其它的有效索引，MySQL將丟棄輔助索引而使用全表掃描。在查看執行計劃信息時，要注意。

如果mysql認為使用全表掃描要比使用索引快時，則不會使用到索引，索引也會失效。

除了以上兩種失效情況，下面通過使用EXPLAIN命令分析SQL語句執行計劃，總結了大概10條使用規則應用及其失效的情況。下面先創建用于示例說明數據表、索引、數據等。

# 用戶表
create table tuser(
id int primary key,
loginname varchar(100),
name varchar(100),
age int,
sex char(1),
dep int,
address varchar(100)
);
# 部門表
create table tdep(
id int primary key,
name varchar(100)
);

# 創建普通索引
mysql> alter table tuser add index idx_dep(dep);
# 創建唯一索引
mysql> alter table tuser add unique index idx_loginname(loginname);
# 創建組合索引
mysql> alter table tuser add index idx_name_age_sex(name,age,sex);
# 非唯一單列索引
create index index_name on tdep (name);

# 分別新增1條部門和用戶數據
mysql> insert into tdep values (1,'技術部');
mysql> insert into tuser (id,loginname,name,age,sex,dep,address) values(1,'fdcoffee','蕃豆咖啡',36,'M',1,'廣東廣州市環市中路371號');

• 全值匹配我最愛

即索引列使用=進行比較。等值匹配，優先級高于其它操作符。主鍵索引使用范圍匹配，輔助索引使用等值匹配，則優先使用輔助索引；如果都是等值匹配時，則主鍵索引優先輔助索引。

# 主鍵索引列全值匹配
mysql> explain select * from tuser where id = 1 G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
        
# 唯一輔助索引列全值匹配
mysql> explain select * from tuser where loginname = 'fdcoffee' G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: const
possible_keys: idx_loginname
          key: idx_loginname
      key_len: 303
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
        
# 輔助索引列全值匹配，遵循最左前綴原則
mysql> explain select * from tuser where name = 'fdcoffee' and age = 36 and sex = 'M' G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ref
possible_keys: idx_name_age_sex
          key: idx_name_age_sex
      key_len: 312
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL

• 最佳左前綴法則

組合索引中，需要按照最左前綴原則使用索引字段才會有效：帶頭索引不能丟，中間索引不能斷

# 正確示例
mysql> explain select * from tuser where name = 'fdcoffee' and age = 36 and sex = 'M' G;
mysql> explain select * from tuser where name = 'fdcoffee' and age = 36 G;
mysql> explain select * from tuser where name = 'fdcoffee' G;

# 錯誤示例
# 缺少帶頭索引name，剩下的age和sex字段都無法使用索引
mysql> explain select * from tuser where age = 36 and sex = 'M' G;
# 同上，沒有前面的name和age字段一起，sex字段無法使用到索引
mysql> explain select * from tuser where sex = 'M' G;
# 缺少中間索引age,只能使用到部分索引：name字段有效，但sex字段無法用到索引
mysql> explain select * from tuser where name = 'fdcoffee' and sex = 'M' G;

• 索引列上不計算

不要在索引列上進行計算操作：計算、函數、自動/手動類型轉換，不然會導致索引失效而轉向全表掃描

# 使用left函數對loginname長度截取，索引失效
mysql> explain select * from tuser where left(loginname,4) = 'fdco' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: Using where
        
# 上面的示例可以這樣優化：使用 like% 或在程序上先對loginname做處理在傳入MySQL數據庫查詢
mysql> explain select * from tuser where loginname like 'fdco%' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: range
possible_keys: idx_loginname
          key: idx_loginname
      key_len: 303
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

• 范圍右邊列失效

不能繼續使用索引中范圍條件(between、<、>、in等)右邊的列

# 由于age使用范圍操作符，后面的sex字段索引失效
mysql> explain select * from tuser where name = 'fdcoffee' and age > 20 and sex = 'M' G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: range
possible_keys: idx_name_age_sex
          key: idx_name_age_sex
      key_len: 308
          ref: NULL
         rows: 1
     filtered: 14.29
        Extra: Using index condition

• 盡量用覆蓋索引

盡量使用覆蓋索引(select 索引列)，也就是查詢列和索引列一致，減少使用select *

# 使用select *全表掃描
mysql> explain select * from tuser G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: NULL

# 使用select 索引列時，掃描索引，不需要回表
mysql> explain select name,age,sex from tuser G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_name_age_sex
      key_len: 312
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: Using index

• 不等判斷不要用

索引字段上使用不等(!=或者<>)判斷時，會導致索引失效而轉向全表掃描

mysql> explain select * from tuser where name != 'fdcoffee' G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ALL           # 全表掃描
possible_keys: idx_name_age_sex
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: Using where

• 索引不做空判斷

索引字段上使用is null/is not null判斷會走全表掃描。允許為null的索引字段呢？看下面分析。

# 主鍵id
mysql> explain select * from tuser where id is not null G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 85.71
        Extra: Using where
# 由于主鍵不允許為空，使用is null，執行計劃信息Extra列將提示Impossible WHERE信息
mysql> explain select * from tuser where id is null G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE

# 允許為空的索引
# 使用is not null將走全表掃描
mysql> explain select * from tuser where loginname is not null G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ALL
possible_keys: idx_loginname
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: Using where
# 而is null則用到了索引
mysql> explain select * from tuser where loginname is null G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ref
possible_keys: idx_loginname
          key: idx_loginname
      key_len: 303
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition    

• 通配符號寫最右

索引字段使用like以通配符開頭(%字符串)時，會導致索引失效而轉向全表掃描 。盡量以(字符串%)方式寫語句。如果一定要使用%開頭，業務滿足的話，可以配合覆蓋索引進行一定優化

# 以%開頭，索引失效，變成全表掃描
mysql> explain select * from tuser where name like '%fdco' G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 14.29
        Extra: Using where

# 以%結尾，可以使用到索引
mysql> explain select * from tuser where name like 'fdco%' G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: range
possible_keys: idx_name_age_sex
          key: idx_name_age_sex
      key_len: 303
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition

# 如果業務上只需要返回索引中的字段，則可以將*改為索引字段，形成覆蓋索引，可以提升性能
mysql> explain select name,age,sex from tuser where name like '%fdcofd' G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_name_age_sex
      key_len: 312
          ref: NULL
         rows: 7
     filtered: 14.29
        Extra: Using where; Using index     

• 字符要加單引號

索引字段是字符串，但查詢時不加單引號，形成隱式類型轉換，相當于使用了函數進行計算，會導致索引失效而轉向全表掃描

# 字符串類型的字段，傳入一個數字類型，導致索引失效全表掃描
mysql> explain select * from tuser where name = 7 G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ALL
possible_keys: idx_name_age_sex
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 14.29
        Extra: Using where

# 數字類型的age可以傳入字符串的數字，索引有效，但不建議這樣去使用
mysql> explain select * from tuser where name = 'ccc' and age = '7' and sex = 'M' G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ref
possible_keys: idx_name_age_sex
          key: idx_name_age_sex
      key_len: 312
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL

• 索引字段不用or

索引字段使用or時，會導致索引失效（這個似乎MySQL有做優化，5.7版本測試可以使用索引，后面再詳細測試）

# 5.7版本測試下，同一個索引字段用or時可以使用到索引的
mysql> explain select * from tuser where loginname = 'fd' or loginname = 'coffee' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: range
possible_keys: idx_loginname
          key: idx_loginname
      key_len: 303
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition
        
#5.7版本測試下，不同索引字段用or則變成全表掃描        
mysql> explain select * from tuser where name='fdcoffee' or age = 7 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ALL
possible_keys: idx_name_age_sex
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 26.53
        Extra: Using where  
# 同上        
mysql> explain select * from tuser where name='fdcoffee' and age = 7 or sex = 'M' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tuser
   partitions: NULL
         type: ALL
possible_keys: idx_name_age_sex
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 16.03
        Extra: Using where 

索引優化

優化索引，其實就是要遵守索引的使用規則，然后避免出現導致索引失效的情況。通過EXPLAIN可以發現索引失效的情況，然后按照使用規則糾正即可。下面是流傳的優化口訣，簡單實用易記。

全值匹配我最愛，最左前綴要遵守；

帶頭大哥不能死，中間兄弟不能斷；

索引列上少計算，范圍之后全失效；

LIKE百分寫最右，覆蓋索引不寫星；

不等空值還有or，索引失效要少用。