快捷導(dǎo)航

MySQL索引失效的典型案例

更新時間：2021年06月05日 10:37:37 作者：DBA隨筆

索引在我們使用MySQL數(shù)據(jù)庫時可以極大的提高查詢效率，然而，有時候因為使用上的一些瑕疵就會導(dǎo)致索引的失效，無法達(dá)到我們使用索引的預(yù)期效果，今天介紹一種MySQL中幾種常見的索引失效的原因，可以在以后的工作中盡可能避免因索引失效帶來的坑。

典型案例

有兩張表，表結(jié)構(gòu)如下：

CREATE TABLE `student_info` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `student_score` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

其中一張是info表，一張是score表，其中score表比info表多了一列score字段。

插入數(shù)據(jù)：

mysql> insert into student_info values (1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(4,'zhaoliu');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into student_score values (1,'zhangsan',60),(2,'lisi',70),(3,'wangwu',80),(4,'zhaoliu',90);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student_info;
+----+----------+
| id | name     |
+----+----------+
|  2 | lisi     |
|  3 | wangwu   |
|  1 | zhangsan |
|  4 | zhaoliu  |
+----+----------+
4 rows in set (0.00 sec)

mysql> select * from student_score ;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan |    60 |
|  2 | lisi     |    70 |
|  3 | wangwu   |    80 |
|  4 | zhaoliu  |    90 |
+----+----------+-------+
4 rows in set (0.00 sec)

當(dāng)我們進(jìn)行下面的語句時：

mysql> explain select B.* 
       from 
       student_info A,student_score B 
       where A.name=B.name and A.id=1;
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys    | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | A     | NULL       | const | PRIMARY,idx_name | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | B     | NULL       | ALL   | NULL             | NULL    | NULL    | NULL  |    4 |   100.00 | Using where |
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

為什么B.name上有索引，但是執(zhí)行計劃里面第二個select表B的時候，沒有使用索引，而用的全表掃描？？？

解析：

該SQL會執(zhí)行三個步驟：

1、先過濾A.id=1的記錄，使用主鍵索引，只掃描1行LA

2、從LA這一行中找到name的值“zhangsan”，

3、根據(jù)LA.name的值在表B中進(jìn)行查找，找到相同的值zhangsan，并返回。

其中，第三步可以簡化為：

select * from student_score where name=$LA.name

這里，因為LA是A表info中的內(nèi)容，而info表的字符集是utf8mb4，而B表score表的字符集是utf8。

所以

在執(zhí)行的時候相當(dāng)于用一個utf8類型的左值和一個utf8mb4的右值進(jìn)行比較，因為utf8mb4完全包含utf8類型(長字節(jié)包含短字節(jié))，MySQL會將utf8轉(zhuǎn)換成utf8mb4(不反向轉(zhuǎn)換，主要是為了防止數(shù)據(jù)截斷).

因此，相當(dāng)于執(zhí)行了：

select * from student_score  where CONVERT(name USING utf8mb4)=$LA.name

而我們知道，當(dāng)索引字段一旦使用了隱式類型轉(zhuǎn)換，那么索引就失效了，MySQL優(yōu)化器將會使用全表掃描的方式來執(zhí)行這個SQL。

要解決這個問題，可以有以下兩種方法：

a、修改字符集。

b、修改SQL語句。

給出修改字符集的方法：

mysql> alter table student_score modify name varchar(10)  character set utf8mb4 ;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select B.* from student_info A,student_score B where A.name=B.name and A.id=1;
+----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys    | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | A     | NULL       | const | PRIMARY,idx_name | PRIMARY  | 4       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | B     | NULL       | ref   | idx_name         | idx_name | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

修改SQL的方法，大家可以自己嘗試。