正确利用条件字段索引
MySQL中,如果需要查找某一行的值,可以先通过索引找到对应的值,之后根据索引匹配的记录找到需要返回的数据行。MySQL索引会有专门的一组笔记进行整理,这里只是对在条件字段中正确使用索引进行总结。
条件字段存在索引,但是不走索引导致慢查询的情况有如下几种。
函数操作
有时会借助MySQL函数实现查询。
1)验证对条件字段使用函数是否能走索引
创建测试表,
use test;
drop table if exists t1;
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
a varchar(20) DEFAULT NULL,
b int(20) DEFAULT NULL,
c datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_a (a) USING BTREE,
KEY idx_b (b) USING BTREE,
KEY idx_c (c) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1,则删除 */
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1(); /* 运行存储过程insert_t1 */
update t1 set c = '2019-05-22 00:00:00'; /* 更新表t1的c字段,值都为'2019-05-22 00:00:00' */
update t1 set c = '2019-05-21 00:00:00' where id=10000; /* 将id为10000的行的c字段改为与其它行都不一样的数据 */
对于上表中的几率,查询单独某一天的所有数据,
# DATE() 函数返回日期或日期/时间表达式的日期部分
explain select * from t1 where date(c) = '2019-5-21';
返回分析结果,
执行计划中type
字段为ALL,参考explain返回结果,可知此查询未走索引。
2)对条件字段使用函数操作不走索引的原因
本例中字段c普通索引的B+树索引如下,
索引树中存储的是索引c的实际值和其对应的主键值。如果使用DATE
函数返回的2019-05-21
进行匹配,在索引树中不会有匹配结果,所以放弃索引,使用全表扫描。
3)函数操作的SQL优化
本例中,可以将SQL优化为字段c实际值相匹配的形式。如使用范围查询对某天的记录进行搜索,
explain select * from t1 where c>='2019-05-21 00:00:00' and c<='2019-05-21 23:59:59';
返回结果如下,
类似某一天或者某个月数据的需求,建议写为范围查询的形式,可以让查询走索引。在实际开发中,尽量避免对条件字段使用函数。
隐式转换
1)隐式转换定义及规则
隐式转换: 当条件查询值的类型与字段类型不一致时(或者说等号两端的数据类型不一致),就会发生类型转换,使操作能够兼容。
隐式转换规则
- 如果一个或两个参数都是NULL,比较的结果是NULL,除了NULL安全的<=>相等比较运算符。对于NULL <=> NULL,结果为true。不需要转换
- 如果两个参数都是字符串,则进行字符串比较
- 如果两个参数都是整数,则进行整数比较
- 十六进制值如果不与数字比较,则被视为二进制字符串
- 如果其中一个参数是十进制值,则比较取决于另一个参数。如果另一个参数是十进制或整型值,则将其作为十进制值进行比较;如果另一个参数是浮点值,则将其作为浮点值进行比较。
- 如果其中一个参数是时间戳或日期时间,而另一个参数是常数,则常数在执行比较之前会转换成时间戳。
为了安全起见,在进行比较时,总是使用完整的datetime、date或time字符串。例如,为了在使用BETWEEN with date或time值时获得最佳结果,可以使用CAST()来显式地将这些值转换为所需的数据类型。 - 在所有其他情况下,参数都作为浮点数(实数)进行比较。例如,字符串和数字操作数的比较将作为浮点数的比较。
隐式转换参见MySQL文档。
2)验证隐式转换能否走索引
explain select * from t1 where a = 1000;
返回结果如下,
type
字段显示的是ALL,是最差的情况即全表扫描。key
项显示没有走字段a的索引。
3)不走索引的原因
表中字段a的类型是varchar
,而上面查询语句中条件字段的值没有加引号,依据隐式转换的规则,
在所有其他情况下,参数都作为浮点数(实数)进行比较。例如,字符串和数字操作数的比较将作为浮点数的比较。
相当于在执行语句时,MySQL内部会先把字段a的值转换成int型再去做判断,即内部执行,
select * from t1 where cast(a as signed int) = 1000;
对索引字段使用函数操作时,是不会走索引的。本质上触发的还是第一个原因。
4)隐式转换SQL优化
写SQL时,先明确字段类型,根据字段类型写SQL,保证等号两端的类型是相同的。
模糊查询
1)分析模糊查询
有时候根据某个字段的关键字进行模糊查询,
explain select * from t1 where a like '%1111%';
返回结果如下,
各项结果显示,即使a字段存在索引,但是上面的模糊匹配没有走索引。通配符不走索引会在索引专题的笔记中说明。
2)模糊查询优化
让模糊查询必须包含条件字段前面的值,即能省略开头的%
通配符就尽量省略(即like查询不能以%开头,否则不走索引)。当模糊查询的开头字符明确时,MySQL是能够走索引的,
explain select * from t1 where a like '1111%';
返回结果,
如果只知道中间值,需要模糊查询时,建议使用ElasticSearch或者其他搜索引擎。
范围查询
范围查询在实际开发中很常见,但是有时会出现范围查询的条件字段存在索引但不走索引的情况。
1)构造不能使用索引的范围查询
假设取出b字段范围在1到2000的数据,SQL如下,
explain select * from t1 where b >= 1 and b <= 2000;
返回结果如下,
b字段没有走索引查询的原因是优化器根据检索比例、表大小、IO块大小等进行评估是否使用索引。如果单次查询数据量过大,优化器不会走索引。
2)范围查询SQL优化
降低单次查询范围,分为多次查询,
explain select * from t1 where b >= 1 and b <= 1000;
explain select * from t1 where b >= 1001 and b <= 2000;
返回结果如下,
范围查询无法走索引的情况经常出现,在执行这类SQL前应先做explain分析,确定能走索引再进行操作。
计算操作
对条件字段进行运算时需要格外注意运算的方式。
1)计算操作的执行效率
explain select * from t1 where b-1 = 1000;
返回结果如下,
2)计算操作SQL优化
务必将计算操作放在等号右侧,不要作用在条件字段上。
explain select * from t1 where b = 1000+1;
返回结果如下,
一般对条件字段进行计算时,建议使用程序代码实现,而不是通过MySQL实现。如果避免不了在MySQL中进行运算,务必把运算放在等号右侧。