索引为什么失效了

索引为什么失效了

1. where条件中使用函数

1.1 建立示例表

代码语言：javascript复制

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;

在tradeid，t_modified上建立索引。

1.2 插入数据

代码语言：javascript复制

delete from tradelog;
delimiter ;;
create procedure tradedata2()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into tradelog values (i 1,i 10000, 1, date_add(NOW(), interval i MONTH));
    set i=i 1;
    end while;
end;;
delimiter ;
call tradedata2();

1.3 索引失效的情况

代码语言：javascript复制

explain select * from tradelog where month(t_modified)=11;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tradelog		ALL					91018	100	Using where

我们发现查询语句索引失效了，原因在于我们在t_modified字段上使用了month函数。

代码语言：javascript复制

select * from tradelog where t_modified >= '2020-11-1' and t_modified<='2020-11-30';

而使用上述的查询语句，则使用了索引。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tradelog		range	t_modified	t_modified	6		1	100	Using index condition

2. 隐藏的类型转换

代码语言：javascript复制

explain select * from tradelog  where tradeid =10041;

tradeid定义为字符串，使用上述的查询语句的时候，进行了类型转换，索引就会失效。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tradelog		ALL	tradeid				91018	10	Using where

代码语言：javascript复制

explain select * from tradelog  where tradeid ='10041';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tradelog		ref	tradeid	tradeid	131	const	1	100

如果使用了字符串则依然使用了索引。

3. 隐藏字符编码转换

3.1 建立另外一张表使用utf-8编码

代码语言：javascript复制

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;

3.2 插入测试数据

代码语言：javascript复制

insert into trade_detail values(1, '10011', 1, 'add');
insert into trade_detail values(2, '10012', 2, 'update');
insert into trade_detail values(3, '10013', 3, 'commit');
insert into trade_detail values(4, '10014', 1, 'add');
insert into trade_detail values(5, '10015', 2, 'update');

3.3 查询测试

代码语言：javascript复制

explain select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	l		const	PRIMARY,tradeid	PRIMARY	4	const	1	100
1	SIMPLE	d		ALL					5	100	Using where

• 第一行显示tradelog 查到id=2的数据，使用了索引（rows=1）。
• 第二行key=null，表示没有使用trade_detail的索引。

这个查询语句的查询计划里面，先从tradelog中获取tradeid字段，再去trade_detail查询匹配的字段，因此把tradelog叫做驱动表，trade_detail 称为被驱动表。tradeid是关联字段。

整个查询过程如下：

1. 根据id在trade_log查找id=2的记录
2. 从查出的记录中获取trade_id字段
3. 根据trade_id去trade_detail中查找对应的记录。

从explain的结果中发现，第1步走了trade_log的索引，第3步没有使用索引。

因为trade_log中的编码是utf8mb4， 获取的trade_id编码是utf8mb4，trade_detail的编码是utf8，并且utf8mb4是utf8的超集。在执行第3步的时候，做了一次编码转换，所以没有使用索引。

utf8 编程算法

0 人点赞