MySQL:为什么明明创建了索引还是走了全表扫描

2023-12-26 13:56:32

为了解决线上的慢查询已经创建了索引,但是却还是走了全表扫描,甚至在测试环境能够正常运行,但是到了线上却出现了不一样的表征

1 条件字段函数操作

如下表为交易系统的表:

CREATE TABLE `tradelog` (
	`id` INT ( 11 ) NOT NULL,
	`tradeid` VARCHAR ( 32 ) DEFAULT NULL,
	`operator` INT ( 11 ) DEFAULT NULL,
	`t_modified` datetime DEFAULT NULL,
	PRIMARY KEY ( `id` ),
	KEY `tradeid` ( `tradeid` ),
KEY `t_modified` ( `t_modified` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

如果想要获取7月份的交易数据,使用month()函数并不会走索引,因为对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器决定放弃走树搜索功能。

上文只是说索引放弃走树搜索功能,并不是说优化器不再使用索引,但是这个索引没有办法通过树搜索功能实现快速定位,只能全索引扫描,即:遍历索引树。

优化器通过对比主键索引和t_modified索引,t_modified索引更小,因此,就会遍历该索引。
在这里插入图片描述
从分析来看,key=t_modified表示使用了索引,rows=100335表示进行了全表扫描,extra=using index表示使用了覆盖索引。

如果要真正利用索引的快速定位能力,可以使用如下语句:

SELECT count(*) FROM tradelog WHERE
	(t_modified >= '2016-7-1' AND t_modified < '2016-8-1') 
	OR (t_modified >= '2017-7-1' AND t_modified < '2017-8-1') 
	OR (t_modified >= '2018-7-1' AND t_modified < '2018-8-1');

同样的,只要在查询的条件上增加函数,就可能导致不走索引,如:

select * from tradelog where id + 1 = 10000

2 隐式类型转换

如果在where条件中没有输入正确的类型,则依旧可能导致全表扫描,比如如下语句:

select * from tradelog where tradeid=110717;

traceid在数据库中是varchar类型,所以当字符串类型和int类型直接比较时,会有隐式转换(数据库中字符串和数字比较,会将字符串转化为数字)。

cast(traceid as signed int)

也就是隐式的使用了函数转换,导致优化器放弃走树搜索功能,从而全表扫描。

但是如下的语句,却会走索引:

select * from tracelog where id="83126";

这是因为字符串和数字进行比较的时候,将字符串转化为数字,隐式转换不会应用到字段上,因此,这时候会走主键索引。

同时,在推荐字符串转换为数字是从最左侧开始识别数字,直到碰到不是数字或者是最后截止,将识别出来是数字的字符串转换为数字。无法最后没有可转换为数字的字符,结果为0.

SELECT CAST("10a" AS UNSIGNED INT);//结果是10
SELECT CAST('10a1' AS UNSIGNED INT);//结果也是10
SELECT CAST('a10a1' AS UNSIGNED INT);//结果是0
SELECT CAST('01a10' AS UNSIGNED INT);//结果是1
SELECT 'a' = 0;//结果是1

3 隐式字符编码转换

CREATE TABLE `trade_detail` (
	`id` INT ( 11 ) NOT NULL,
	`tradeid` VARCHAR ( 32 ) DEFAULT NULL,
	`trade_step` INT ( 11 ) DEFAULT NULL,
	`step_info` VARCHAR ( 32 ) DEFAULT NULL,
	PRIMARY KEY ( `id` ),
KEY `tradeid` ( `tradeid` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

在上面的表中,如果要获取id=2的交易对应的交易详情,SQL语句为:

SELECT d.* FROM tradelog l,trade_detail d WHERE d.tradeid = l.tradeid	AND l.id = 2;

在这里插入图片描述
在上面的执行计划中,在驱动表tradelog中查询id=2使用了主键索引,但是在被驱动表trade_detail使用关联字段traceid查询是全表遍历。
在这里插入图片描述
首先我们排除隐式转换的可能性,因为tradeid都是字符串,再看表的字符集,发现异常,两张表的字符集不一致,一个是utf8,一个是utf8mb4,由于utf8mb4是utf8的超集,所以一定是先把utf8字符串转成utf8mb4字符串再做比较。

因此,上面的语句实际执行时为如下的语句:

select * from trade_detail where tradeid=$L2.tradeid.value; 

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

因此,实际原因就是连接过程中要求在被驱动表的索引字段加函数操作,直接导致对被驱动表做全表扫描。

但是在执行如下语句时,却使用到了索引:

EXPLAIN SELECT l.operator FROM	tradelog l,trade_detail d WHERE d.tradeid = l.tradeid	AND d.id = 4;

在这里插入图片描述
我们还是看真实执行的语句:

select operator from tradelog  where traideid =$R4.tradeid.value; 

select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4); 

这时候的convert函数加在了输入参数上,这样可以用上被驱动表的traceid索引。

因此,上面的查询语句的优化方法有:
1)把trace_detail的traceid的字符串也成为utf8mb4,这样就不再存在字符集转换问题;


alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

2)修改SQL语句:

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

在这里插入图片描述

4 字符串的匹配与比较

4.1 字符串比较规则

SELECT "10" > "9";

在这里插入图片描述
原因在于:字符串比较大小是逐位从高位到低位逐个比较(按ascii码) 那么“10”的“1”的ascii比“9”小,所以结果为0

4.2 字符串匹配问题

CREATE TABLE `table_a` (
`id` INT ( 11 ) NOT NULL,
`b` VARCHAR ( 10 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `b` ( `b` ) ) ENGINE = INNODB;

假设现在表里面,有100万行数据,其中有10万行数据的b的值是’1234567890’, 假设现在执行语句是这么写的:

select * from table_a where b='1234567890abcd';

这时候,MySQL 会怎么执行呢?

最理想的情况是,MySQL 看到字段 b 定义的是 varchar(10),那肯定返回空呀。可惜,MySQL 并没有这么做。

那要不,就是把’1234567890abcd’拿到索引里面去做匹配,肯定也没能够快速判断出索引树 b 上并没有这个值,也很快就能返回空结果。

但实际上,MySQL 也不是这么做的。

这条 SQL 语句的执行很慢,流程是这样的:

  • 在传给引擎执行的时候,做了字符截断。因为引擎里面这个行只定义了长度是 10,所以只截了前 10 个字节,就是’1234567890’进去做匹配;
  • 这样满足条件的数据有 10 万行;
  • 因为是 select *, 所以要做 10 万次回表;
  • 但是每次回表以后查出整行,到 server 层一判断,b 的值都不是’1234567890abcd’;
  • 返回结果是空。

这个例子,是我们文章内容的一个很好的补充。虽然执行过程中可能经过函数操作,但是最终在拿到结果后,server 层还是要做一轮判断的。

原文

文章来源:https://blog.csdn.net/Gan_1314/article/details/135215721
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。