您的位置：首頁 > 軟件教程 > 教程 > MySQL的索引優(yōu)化

MySQL的索引優(yōu)化

來源：好特整理　|　時間：2024-05-19 15:57:31 |　閱讀：156　|　 標簽： S 　 |　分享到：

哪些場景下MySQL會使用索引查詢數(shù)據(jù)，哪些場景下MySQL不會使用索引查詢數(shù)據(jù)，以及如何使用索引提示來告知查詢優(yōu)化器使用索引、忽略索引和強制索引索引。

一、索引的使用場景

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)建一個索引。 

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 = '手機' \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，所以查詢時使用了聯(lián)合索引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 說明根據(jù)主鍵索引范圍進行查詢。這里 Extra: Using where ，說明MySQL按照主鍵確定范圍后再回表查詢數(shù)據(jù)。

3、匹配最左前綴

解釋：也就是說，在使用索引時，MySQL優(yōu)化器會根據(jù)查詢條件使用該索引。只有滿足這個匹配原則才會使用索引。例如過程創(chuàng)建的聯(lián)合索引 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、匹配字段前綴

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

例如在字段的前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)

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

6、精準與范圍匹配查詢

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

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連接語句查詢多個數(shù)據(jù)表中的數(shù)據(jù)時，如果連接的字段上添加了索引，那么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)合多表查詢時，如果聯(lián)合的字段是索引字段，那么這個查詢也會使用索引列。

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

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、數(shù)據(jù)類型轉換

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

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、計算索引列

如果在使用索引條件時，這個索引字段進行了計算或者使用了函數(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ù)，判斷這個字段的前兩個字符是否為“電子”。可以看到有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查詢優(yōu)化器使用特定的索引，不需要評估是否使用其他索引。 

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仍然可以根據(jù)自身的優(yōu)化器決定是否使用該索引。

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、強制使用索引

在查詢數(shù)據(jù)時，強制使用某個索引來檢索數(shù)據(jù)。

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

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)
小編推薦閱讀

好特網(wǎng)發(fā)布此文僅為傳遞信息，不代表好特網(wǎng)認同期限觀點或證實其描述。

相關視頻攻略

更多

掃二維碼進入好特網(wǎng)手機版本！

掃二維碼進入好特網(wǎng)微信公眾號！

本站所有軟件，都由網(wǎng)友上傳，如有侵犯你的版權，請發(fā)郵件[email protected]

湘ICP備2022002427號-10 湘公網(wǎng)安備：43070202000427號© 2013~2024 haote.com 好特網(wǎng)