典型案例
有兩張表，表結(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)行下面的語(yǔ)句時(shí)：
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í)行計(jì)劃里面第二個(gè)select表B的時(shí)候，沒(méi)有使用索引，而用的全表掃描？？？
解析：
該SQL會(huì)執(zhí)行三個(gè)步驟：
1、先過(guò)濾A.id=1的記錄，使用主鍵索引，只掃描1行LA
2、從LA這一行中找到name的值“zhangsan”，
3、根據(jù)LA.name的值在表B中進(jìn)行查找，找到相同的值z(mì)hangsan，并返回。
其中，第三步可以簡(jiǎn)化為：
select * from student_score where name=$LA.name
這里，因?yàn)長(zhǎng)A是A表info中的內(nèi)容，而info表的字符集是utf8mb4，而B(niǎo)表score表的字符集是utf8。
所以
在執(zhí)行的時(shí)候相當(dāng)于用一個(gè)utf8類(lèi)型的左值和一個(gè)utf8mb4的右值進(jìn)行比較，因?yàn)閡tf8mb4完全包含utf8類(lèi)型(長(zhǎng)字節(jié)包含短字節(jié))，MySQL會(huì)將utf8轉(zhuǎn)換成utf8mb4(不反向轉(zhuǎn)換，主要是為了防止數(shù)據(jù)截?cái)?.
因此，相當(dāng)于執(zhí)行了：
select * from student_score where CONVERT(name USING utf8mb4)=$LA.name
而我們知道，當(dāng)索引字段一旦使用了隱式類(lèi)型轉(zhuǎn)換，那么索引就失效了，MySQL優(yōu)化器將會(huì)使用全表掃描的方式來(lái)執(zhí)行這個(gè)SQL。
要解決這個(gè)問(wèn)題，可以有以下兩種方法：
a、修改字符集。
b、修改SQL語(yǔ)句。
給出修改字符集的方法：
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的方法，大家可以自己嘗試。
附：常見(jiàn)索引失效的情況
一、對(duì)列使用函數(shù)，該列的索引將不起作用。
二、對(duì)列進(jìn)行運(yùn)算(+，-，*，/，! 等)，該列的索引將不起作用。
三、某些情況下的LIKE操作，該列的索引將不起作用。
四、某些情況使用反向操作，該列的索引將不起作用。
五、在WHERE中使用OR時(shí)，有一個(gè)列沒(méi)有索引，那么其它列的索引將不起作用。
六、隱式轉(zhuǎn)換導(dǎo)致索引失效.這一點(diǎn)應(yīng)當(dāng)引起重視.也是開(kāi)發(fā)中經(jīng)常會(huì)犯的錯(cuò)誤。
七、使用not in ,not exist等語(yǔ)句時(shí)。
八、當(dāng)變量采用的是times變量，而表的字段采用的是date變量時(shí).或相反情況。
九、當(dāng)B-tree索引 is null不會(huì)失效,使用is not null時(shí),會(huì)失效,位圖索引 is null,is not null 都會(huì)失效。
十、聯(lián)合索引 is not null 只要在建立的索引列（不分先后）都會(huì)失效。
以上就是MySQL索引失效的典型案例的詳細(xì)內(nèi)容，更多關(guān)于MySQL索引失效的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- mysql回表致索引失效案例講解
- 解決mysql模糊查詢(xún)索引失效問(wèn)題的幾種方法
- mysql索引失效的幾種情況分析
- MySQL索引失效的幾種情況詳析
- MySQL索引失效的幾種情況匯總
- mysql索引失效的五種情況分析
- Mysql索引會(huì)失效的幾種情況分析
- mysql索引失效的十大問(wèn)題小結(jié)
