一、索引的使用場景

1、全值匹配

通過主鍵索引查詢

mysql> explain select * from t_goods where id = 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

可以看到這里查詢數(shù)據(jù)使用了主鍵索引。

現(xiàn)在我們再創(chuàng)建一個(gè)索引。

ALTER Table t_goods ADD INDEX index_category_name(t_category_id,t_name);

這里為t_category_id與t_name創(chuàng)建了聯(lián)合索引。

mysql> explain select * from t_goods where t_category_id = 1 and t_name = '手機(jī)' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: index_category_name
          key: index_category_name
      key_len: 208
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

這里的查詢條件為t_category_id與t_name，所以查詢時(shí)使用了聯(lián)合索引index_category_name

2、查詢范圍

對索引的值進(jìn)行范圍查找

mysql> explain select * from t_goods where id >= 1 and id <=20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

type: range 說明根據(jù)主鍵索引范圍進(jìn)行查詢。這里 Extra: Using where ，說明MySQL按照主鍵確定范圍后再回表查詢數(shù)據(jù)。

3、匹配最左前綴

解釋：也就是說，在使用索引時(shí)，MySQL優(yōu)化器會根據(jù)查詢條件使用該索引。只有滿足這個(gè)匹配原則才會使用索引。例如過程創(chuàng)建的聯(lián)合索引 index_category_name(t_category_id, t_name) ，如果我跳過 t_category_id 直接使用 t_name 條件查詢，那么這個(gè)查詢將不會使用索引。

mysql> explain select * from t_goods where t_name='手機(jī)' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

可以看到這個(gè)查詢并沒有使用索引。

4、查詢索引列

如果在查詢時(shí)包含索引的列或者查詢的列都在索引中，那么查詢的效率會比SELECT * 或者查詢沒有索引的列的效率要高很多。也就是說，如果查詢的列只包含索引列，那么這個(gè)效率會高很多。例如

mysql> explain select t_name,t_category_id from t_goods where t_name='手機(jī)' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: index
possible_keys: index_category_name
          key: index_category_name
      key_len: 208
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

例如這里查詢的列都是索引列，所以這個(gè)查詢的效率會快很多，并且使用了索引。如果有其他不是索引列需要查詢，那么這個(gè)查詢將不會使用索引。例如

mysql> explain select t_name,t_category_id,t_price from t_goods where t_name='手機(jī)' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

5、匹配字段前綴

如果某個(gè)字段存儲的數(shù)據(jù)特別長的話，那么在這個(gè)字段上建立索引會增加MySQL維護(hù)索引的負(fù)擔(dān)。匹配字段前綴就是用于解決這個(gè)問題。在字段的開頭部分添加索引，按照這個(gè)索引進(jìn)行數(shù)據(jù)查詢。

例如在字段的前10個(gè)字符上添加索引，查詢時(shí)進(jìn)行匹配。

mysql> create index category_part on t_goods(t_category(10));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次進(jìn)行模糊匹配查詢

mysql> explain select * from t_goods where t_category like '電子%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: range
possible_keys: category_part
          key: category_part
      key_len: 43
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

可以看到這里使用了我們剛才創(chuàng)建的索引，這個(gè)索引應(yīng)用于字段的前10個(gè)字符。

6、精準(zhǔn)與范圍匹配查詢

在查詢數(shù)據(jù)時(shí)，可以同時(shí)使用兩個(gè)索引，一個(gè)為精準(zhǔn)匹配索引，一個(gè)為范圍匹配索引。例如

mysql> explain select * from t_goods where t_category_id=1 and id>=1 and id<=10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: PRIMARY,index_category_name
          key: index_category_name
      key_len: 5
          ref: const
         rows: 5
     filtered: 66.67
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

這個(gè)查詢使用了兩個(gè)索引進(jìn)行查找，使用 index_category_name 進(jìn)行精準(zhǔn)匹配并且按照主鍵索引進(jìn)行范圍查詢

7、匹配NULL值

在查詢一個(gè)字段時(shí)，如果這個(gè)字段是索引字段，那么在判斷這個(gè)字段是否為空時(shí)也會使用索引進(jìn)行查詢。例

mysql> explain select * from t_goods where t_category_id is null \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: index_category_name
          key: index_category_name
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

這里我查詢 t_goods 表中 t_category_id 是 NULL 的字段，可以看到這里是使用了索引進(jìn)行查找的。

8、連接查詢匹配索引

在使用JOIN連接語句查詢多個(gè)數(shù)據(jù)表中的數(shù)據(jù)時(shí)，如果連接的字段上添加了索引，那么MySQL會使用索引查詢數(shù)據(jù)

mysql> explain select goods.t_name,category.t_category from t_goods goods join t_goods_category category on goods.t_category_id = category.id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: category
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: ref
possible_keys: index_category_name
          key: index_category_name
      key_len: 5
          ref: demo.category.id
         rows: 5
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

在使用 JOIN 聯(lián)合多表查詢時(shí)，如果聯(lián)合的字段是索引字段，那么這個(gè)查詢也會使用索引列。

二、不適合使用索引的場景

1、以通配符開始的LIKE語句

在使用LIKE語句時(shí)，如果使用通配符%開頭，那么MySQL將不會使用索引。例如

mysql> explain select * from t_goods where t_category like '%電' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

這里的 t_category 字段雖然說是索引字段，但是這里的條件是以通配符 % 開頭，所以不會使用索引查詢

2、數(shù)據(jù)類型轉(zhuǎn)換

當(dāng)查詢的字段數(shù)據(jù)進(jìn)行了數(shù)據(jù)轉(zhuǎn)換時(shí)，也就是說，某個(gè)索引字段的類型為字符，但是在匹配條件時(shí)，不是字符類型，那么這個(gè)查詢將不會使用索引查詢。例如

mysql> explain select * from t_goods where t_category = 0 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: category_part
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where
1 row in set, 3 warnings (0.00 sec)

例如這里的查詢就沒有使用索引，并且 type 的類型為 ALL ，說明進(jìn)行了全表掃描查詢。

3、OR語句

在OR語句中如果條件中有不是索引的字段，那么這查詢就不會使用索引查詢。例如

mysql> explain select * from t_goods where t_category_id = 1 or t_stock = 2 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: index_category_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 40.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

這里因?yàn)? t_stock 不是索引字段，所以哪怕 t_category_id 索引字段匹配成功，這條語句也不會使用索引查詢

4、計(jì)算索引列

如果在使用索引條件時(shí)，這個(gè)索引字段進(jìn)行了計(jì)算或者使用了函數(shù)，那么此時(shí)MySQL是不會使用索引的。

mysql> explain select * from t_goods where left(t_category,2)='電子'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

這里對索引字段 t_category 使用了函數(shù)，判斷這個(gè)字段的前兩個(gè)字符是否為“電子”。可以看到有15條記錄，但是并沒有使用索引，哪怕 t_category 是索引列。

5、使用<>或!=操作符匹配查詢條件

這兩個(gè)符號都用于表示不等于。當(dāng)查詢條件使用這個(gè)時(shí)不會使用索引查詢。

mysql> explain select * from t_goods where t_category<>'電子產(chǎn)品' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: category_part
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

6、匹配NOT NULL值

在MySQL中，使用IS NULL來判斷索引字段會使用索引查詢，但是使用NOT NULL來判斷時(shí)不會使用索引查詢。

mysql> explain select * from t_goods where t_category_id is not null \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: index_category_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

三、索引提示

1、使用索引

提示MySQL查詢優(yōu)化器使用特定的索引，不需要評估是否使用其他索引。

mysql> explain select * from t_goods use index(index_category_name,category_part) where (t_category_id = 1 and t_name='手機(jī)' ) or t_category = '電子產(chǎn)品'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: index_merge
possible_keys: index_category_name,category_part
          key: index_category_name,category_part
      key_len: 208,43
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using sort_union(index_category_name,category_part); Using where
1 row in set, 1 warning (0.00 sec)

這里可以使用 use index() 指定查詢時(shí)使用特定的索引。但是MySQL仍然可以根據(jù)自身的優(yōu)化器決定是否使用該索引。

2、忽略索引

可以在查詢時(shí)，指定不使用某個(gè)索引。

mysql> explain select * from t_goods ignore index(category_part) where t_category = '電子產(chǎn)品'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

這里使用 ignore index（） ，指定在查詢時(shí)，忽略指定的索引，使用這條查詢沒有使用索引，而是進(jìn)行全表掃描

3、強(qiáng)制使用索引

在查詢數(shù)據(jù)時(shí)，強(qiáng)制使用某個(gè)索引來檢索數(shù)據(jù)。

與 use index() 的區(qū)別為， FORCE INDEX 會強(qiáng)制使用指定的索引，而不會管MySQL的優(yōu)化器如何選擇。

mysql> explain select * from t_goods force index(category_part) where t_category = '電子產(chǎn)品'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: category_part
          key: category_part
      key_len: 43
          ref: const
         rows: 5
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)