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

Gan_1314

已于 2023-12-26 11:41:18 修改

阅读量1.7k

点赞数 22

文章标签： mysql 数据库

于 2023-12-26 11:41:06 首次发布

本文链接：https://blog.csdn.net/Gan_1314/article/details/135215721

版权

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 层还是要做一轮判断的。

原文