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上建立索引。
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();
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 |
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 |
如果使用了字符串则依然使用了索引。
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 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');
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中获取tradeid字段,再去trade_detail查询匹配的字段,因此把tradelog叫做驱动表,trade_detail 称为被驱动表。tradeid是关联字段。
整个查询过程如下:
从explain的结果中发现,第1步走了trade_log的索引,第3步没有使用索引。
因为trade_log中的编码是utf8mb4, 获取的trade_id编码是utf8mb4,trade_detail的编码是utf8,并且utf8mb4是utf8的超集。在执行第3步的时候,做了一次编码转换,所以没有使用索引。