欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL索引失效的典型案例

程序员文章站 2022-03-22 17:18:05
典型案例有两张表,表结构如下:createtable`student_info`(`id`int(11)notnull,`name`varchar(10)defaultnull,primarykey(...

典型案例

有两张表,表结构如下:

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 索引