MySQL的索引最佳化

changwan發表於2024-05-19
MySql索引

一、索引的使用場景

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)

可以看到這裡查詢資料使用了主鍵索引。

現在我們再建立一個索引。

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

這裡為t_category_id與t_name建立了聯合索引。

mysql> explain select * from t_goods where t_category_id = 1 and t_name = '手機' \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，所以查詢時使用了聯合索引index_category_name

2、查詢範圍

對索引的值進行範圍查詢

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說明根據主鍵索引範圍進行查詢。這裡 Extra: Using where，說明MySQL按照主鍵確定範圍後再回表查詢資料。

3、匹配最左字首

解釋：也就是說，在使用索引時，MySQL最佳化器會根據查詢條件使用該索引。只有滿足這個匹配原則才會使用索引。例如過程建立的聯合索引index_category_name(t_category_id, t_name)，如果我跳過t_category_id直接使用t_name條件查詢，那麼這個查詢將不會使用索引。

mysql> explain select * from t_goods where t_name='手機' \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)

可以看到這個查詢並沒有使用索引。

4、查詢索引列

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

mysql> explain select t_name,t_category_id from t_goods where t_name='手機' \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)

例如這裡查詢的列都是索引列，所以這個查詢的效率會快很多，並且使用了索引。如果有其他不是索引列需要查詢，那麼這個查詢將不會使用索引。例如

mysql> explain select t_name,t_category_id,t_price from t_goods where t_name='手機' \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、匹配欄位字首

如果某個欄位儲存的資料特別長的話，那麼在這個欄位上建立索引會增加MySQL維護索引的負擔。匹配欄位字首就是用於解決這個問題。在欄位的開頭部分新增索引，按照這個索引進行資料查詢。

例如在欄位的前10個字元上新增索引，查詢時進行匹配。

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

再次進行模糊匹配查詢

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)

可以看到這裡使用了我們剛才建立的索引，這個索引應用於欄位的前10個字元。

6、精準與範圍匹配查詢

在查詢資料時，可以同時使用兩個索引，一個為精準匹配索引，一個為範圍匹配索引。例如

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)

這個查詢使用了兩個索引進行查詢，使用index_category_name進行精準匹配並且按照主鍵索引進行範圍查詢

7、匹配NULL值

在查詢一個欄位時，如果這個欄位是索引欄位，那麼在判斷這個欄位是否為空時也會使用索引進行查詢。例

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的欄位，可以看到這裡是使用了索引進行查詢的。

8、連線查詢匹配索引

在使用JOIN連線語句查詢多個資料表中的資料時，如果連線的欄位上新增了索引，那麼MySQL會使用索引查詢資料

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聯合多表查詢時，如果聯合的欄位是索引欄位，那麼這個查詢也會使用索引列。

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

1、以萬用字元開始的LIKE語句

在使用LIKE語句時，如果使用萬用字元%開頭，那麼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、資料型別轉換

當查詢的欄位資料進行了資料轉換時，也就是說，某個索引欄位的型別為字元，但是在匹配條件時，不是字元型別，那麼這個查詢將不會使用索引查詢。例如

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，說明進行了全表掃描查詢。

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)

這裡因為t_stock不是索引欄位，所以哪怕t_category_id索引欄位匹配成功，這條語句也不會使用索引查詢

4、計算索引列

如果在使用索引條件時，這個索引欄位進行了計算或者使用了函式，那麼此時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使用了函式，判斷這個欄位的前兩個字元是否為“電子”。可以看到有15條記錄，但是並沒有使用索引，哪怕t_category是索引列。

5、使用<>或!=運算子匹配查詢條件

這兩個符號都用於表示不等於。當查詢條件使用這個時不會使用索引查詢。

mysql> explain select * from t_goods where t_category<>'電子產品' \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來判斷時不會使用索引查詢。


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查詢最佳化器使用特定的索引，不需要評估是否使用其他索引。

mysql> explain select * from t_goods use index(index_category_name,category_part) where (t_category_id = 1 and t_name='手機' ) or t_category = '電子產品'\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()指定查詢時使用特定的索引。但是MySQL仍然可以根據自身的最佳化器決定是否使用該索引。

2、忽略索引

可以在查詢時，指定不使用某個索引。

mysql> explain select * from t_goods ignore index(category_part) where t_category = '電子產品'\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（），指定在查詢時，忽略指定的索引，使用這條查詢沒有使用索引，而是進行全表掃描

3、強制使用索引

在查詢資料時，強制使用某個索引來檢索資料。

與use index()的區別為，FORCE INDEX會強制使用指定的索引，而不會管MySQL的最佳化器如何選擇。

mysql> explain select * from t_goods force index(category_part) where t_category = '電子產品'\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)

相關文章