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索引失效的资料请关注猪先飞其它相关文章!
相关文章
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进...2015-11-24
- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
- MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
- 一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
- 这篇文章主要介绍了node.js如何操作MySQL数据库,帮助大家更好的进行web开发,感兴趣的朋友可以了解下...2020-10-29
- Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
- 这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
- 这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
- 一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
Mysql中 show table status 获取表信息的方法
这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12- mysql 唯一索引UNIQUE一般用于不重复数据字段了我们经常会在数据表中的id设置为唯一索引UNIQUE,下面我来介绍如何在mysql中使用唯一索引UNIQUE吧。 创建唯一索引的目的不是为了提高访问速度,而只是为了避免数据出现重复...2015-11-24
- 这篇文章主要为大家分享了20分钟MySQL基础入门教程,快速掌握MySQL基础知识,真正了解MySQL,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-12-02