MySQL索引失效的典型案例

索引在我们使用MySQL数据库时可以极大的提高查询效率,然而,有时候因为使用上的一些瑕疵就会导致索引的失效,无法达到我们使用索引的预期效果,今天介绍一种MySQL中几种常见的索引失效的原因,可以在以后的工作中尽可能避

典型案例

有两张表,表结构如下:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATETABLE`student_info`(

`id`int(11)NOTNULL,

`name`varchar(10)DEFAULTNULL,

PRIMARYKEY(`id`),

KEY`idx_name`(`name`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4

CREATETABLE`student_score`(

`id`int(11)NOTNULL,

`name`varchar(10)DEFAULTNULL,

`score`int(11)DEFAULTNULL,

PRIMARYKEY(`id`),

KEY`idx_name`(`name`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8

其中一张是info表,一张是score表,其中score表比info表多了一列score字段。

插入数据:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

mysql>insertintostudent_infovalues(1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(4,'zhaoliu');

QueryOK,4rowsaffected(0.01sec)

Records:4Duplicates:0Warnings:0

mysql>insertintostudent_scorevalues(1,'zhangsan',60),(2,'lisi',70),(3,'wangwu',80),(4,'zhaoliu',90);

QueryOK,4rowsaffected(0.01sec)

Records:4Duplicates:0Warnings:0

mysql>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)

当我们进行下面的语句时:

?

1

2

3

4

5

6

7

8

9

10

11

mysql>explainselectB.*

from

student_infoA,student_scoreB

whereA.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)

为什么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(不反向转换,主要是为了防止数据截断).

因此,相当于执行了:

?

1

select*fromstudent_scorewhereCONVERT(nameUSINGutf8mb4)=$LA.name

而我们知道,当索引字段一旦使用了隐式类型转换,那么索引就失效了,MySQL优化器将会使用全表扫描的方式来执行这个SQL。

要解决这个问题,可以有以下两种方法:

a、修改字符集。

b、修改SQL语句

给出修改字符集的方法:

?

1

2

3

4

5

6

7

8

9

10

11

12

mysql>altertablestudent_scoremodifynamevarchar(10)charactersetutf8mb4;

QueryOK,4rowsaffected(0.03sec)

Records:4Duplicates:0Warnings:0

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|ref|idx_name|idx_name|43|const|1|100.00|NULL|

+—-+————-+——-+————+——-+——————+———-+———+——-+——+———-+——-+

2rowsinset,1warning(0.01sec)

修改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索引失效的资料请关注钦钦技术栈其它相关文章!

原文链接:https://mp.weixin.qq.com/s/VtsS0vPmqjPNaJhy8IPYmQ

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/7059/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-07-31 3:32:09
下一篇 2022-07-31 3:32:22

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!