MySQL索引失效的典型案例
典型案例
有兩張表，表結構如下：
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字段。
插入數據：
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)
當我們進行下面的語句時：
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上有索引，但是執行計劃裡面第二個select表B的時候，沒有使用索引，而用的全表掃描？？？
解析：
該SQL會執行三個步驟：
1、先過濾A.id=1的記錄，使用主鍵索引，隻掃描1行LA
2、從LA這一行中找到name的值“zhangsan”，
3、根據LA.name的值在表B中進行查找，找到相同的值zhangsan，並返回。
其中，第三步可以簡化為：
select * from student_score where name=$LA.name
這裡，因為LA是A表info中的內容，而info表的字符集是utf8mb4，而B表score表的字符集是utf8。
所以
在執行的時候相當於用一個utf8類型的左值和一個utf8mb4的右值進行比較，因為utf8mb4完全包含utf8類型(長字節包含短字節)，MySQL會將utf8轉換成utf8mb4(不反向轉換，主要是為瞭防止數據截斷).
因此，相當於執行瞭：
select * from student_score where CONVERT(name USING utf8mb4)=$LA.name
而我們知道，當索引字段一旦使用瞭隱式類型轉換，那麼索引就失效瞭，MySQL優化器將會使用全表掃描的方式來執行這個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的方法，大傢可以自己嘗試。
附：常見索引失效的情況
一、對列使用函數，該列的索引將不起作用。
二、對列進行運算(+，-，*，/，! 等)，該列的索引將不起作用。
三、某些情況下的LIKE操作，該列的索引將不起作用。
四、某些情況使用反向操作，該列的索引將不起作用。
五、在WHERE中使用OR時，有一個列沒有索引，那麼其它列的索引將不起作用。
六、隱式轉換導致索引失效.這一點應當引起重視.也是開發中經常會犯的錯誤。
七、使用not in ,not exist等語句時。
八、當變量采用的是times變量，而表的字段采用的是date變量時.或相反情況。
九、當B-tree索引 is null不會失效,使用is not null時,會失效,位圖索引 is null,is not null 都會失效。
十、聯合索引 is not null 隻要在建立的索引列（不分先後）都會失效。
以上就是MySQL索引失效的典型案例的詳細內容，更多關於MySQL索引失效的資料請關註WalkonNet其它相關文章！