索引为什么失效了
索引为什么失效了
1. where条件中使用函数
1.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;
在tradeid，t_modified上建立索引。
1.2 插入数据
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 索引失效的情况
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函数。
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. 隐藏的类型转换
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 |
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编码
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 插入测试数据
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 查询测试
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是关联字段。
整个查询过程如下：
- 根据id在trade_log查找id=2的记录
- 从查出的记录中获取trade_id字段
- 根据trade_id去trade_detail中查找对应的记录。
从explain的结果中发现，第1步走了trade_log的索引，第3步没有使用索引。
因为trade_log中的编码是utf8mb4， 获取的trade_id编码是utf8mb4，trade_detail的编码是utf8，并且utf8mb4是utf8的超集。在执行第3步的时候，做了一次编码转换，所以没有使用索引。