MySQL索引失效的典型案例
典型案例
有两张表,表结构如下:
CREATETABLE`student_info`(`id`int(11)NOTNULL,`name`varchar(10)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_name`(`name`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4CREATETABLE`student_score`(`id`int(11)NOTNULL,`name`varchar(10)DEFAULTNULL,`score`int(11)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_name`(`name`))ENGINE=InnoDBDEFAULTCHARSET=utf8
插入数据:
mysql>insertintostudent_infovalues(1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(4,'zhaoliu');QueryOK,4rowsaffected(0.01sec)Records:4Duplicates:0Warnings:0mysql>insertintostudent_scorevalues(1,'zhangsan',60),(2,'lisi',70),(3,'wangwu',80),(4,'zhaoliu',90);QueryOK,4rowsaffected(0.01sec)Records:4Duplicates:0Warnings:0mysql>select*fromstudent_info;+----+----------+|id|name|+----+----------+|2|lisi||3|wangwu||1|zhangsan||4|zhaoliu|+----+----------+4rowsinset(0.00sec)mysql>select*fromstudent_score;+----+----------+-------+|id|name|score|+----+----------+-------+|1|zhangsan|60||2|lisi|70||3|wangwu|80||4|zhaoliu|90|+----+----------+-------+4rowsinset(0.00sec)
mysql>explainselectB.*fromstudent_infoA,student_scoreBwhereA.name=B.nameandA.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|Usingwhere|+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+2rowsinset,1warning(0.00sec)
解析:
该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*fromstudent_scorewhereCONVERT(nameUSINGutf8mb4)=$LA.name
要解决这个问题,可以有以下两种方法:
a、修改字符集。
b、修改SQL语句。
给出修改字符集的方法:
mysql>altertablestudent_scoremodifynamevarchar(10)charactersetutf8mb4;QueryOK,4rowsaffected(0.03sec)Records:4Duplicates:0Warnings:0mysql>explainselectB.*fromstudent_infoA,student_scoreBwhereA.name=B.nameandA.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|+----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+2rowsinset,1warning(0.01sec)
附:常见索引失效的情况
一、对列使用函数,该列的索引将不起作用。
二、对列进行运算(+,-,*,/,! 等),该列的索引将不起作用。
三、某些情况下的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 什么时候使用INNER JOIN 或 LEFT JOIN
- [android源码下载索引贴】微信+二维码那都不是事......
- android触控,先了解MotionEvent
- 系出名门 Android(安卓)系列文章索引
- 系出名门 Android(安卓)系列文章索引
- 系出名门 Android(安卓)系列文章索引
- 系出名门 Android(安卓)系列文章索引
- Android(安卓)自定义Gallery
- android 知识点汇总