我要评论mysql条件字段有索引,但使用不了索引的场景
对于 mysql 而言，如果需要查找某一行的值，可以先通过索引找到对应的值，然后根据索引匹配的记录找到需要查询的数据行。然而，有时会发现，即使查询条件有索引，也会查询很慢；
下面会讲解几种有索引但是查询不走索引导致查询慢的场景。
一、前期准备
drop table if exists t1; /* 如果表t1存在则删除表t1 */
create table `t1` ( /* 创建表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() /* 创建存储过程insert_t1 */
begin
declare i int; /* 声明变量i */
set i=1; /* 设置i的初始值为1 */
while(i<=10000)do /* 对满足i<=10000的值进行while循环 */
insert into t1(a,b) values(i,i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
set i=i+1; /* 将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字段改为与其它行都不一样的数据，以便后面实验使用 */二、函数操作
在使用 mysql 查询数据时，可能很多时候会借助一些函数实现查询。有时可能我们关注的重心在是否能查出结果，往往忽略了查询的效率；
对于上面创建的测试表，比如要查询测试表 t1 单独某一天的所有数据，sql如下：
结果如下所示：
type 为 all，key 字段结果为 null，因此知道该 sql 是没走索引的全表扫描；
结论一：对条件字段做函数操作走不了索引；
如果需要优化的话，改成 c 字段实际值相匹配的形式。因为 sql 的目的是查询 2019-05-21 当天所有的记录，因此可以改成范围查询，结果如下所示：
类似求某一天或者某一个月数据的需求，建议写成类似上例的范围查询，可让查询能走索引。避免对条件索引字段做函数处理；
三、隐式转换
隐式转换：当操作符与不同类型的操作对象一起使用时，就会发生类型转换以使操作兼容。
某些转换是隐式的；更多信息可以参考官网：mysql :: mysql 5.7 reference manual :: 12.3 type conversion in expression evaluation
隐式转换估计是很多 mysql 使用者踩过的坑，比如联系方式字段。由于有时电话号码带加、减等特殊字符，有时需要以 0 开头，因此一般设计表时会使用 varchar 类型存储，并且会经常做为条件来查询数据，所以会添加索引；
比如我们要查询 a 字段等于 1000 的值， 仔细对比下面两个查询：
a 字段类型是 varchar(20)，而语句中 a 字段条件值没加单引号，导致 mysql 内部会先把a转换成int型，再去做判断，再次印证了结论一：对索引字段做函数操作时，优化器会放弃使用索引；
所以建议在写sql时，先看字段类型，然后根据字段类型写sql；
四、模糊查询
很多时候我们想根据某个字段的某几个关键字查询数据，比如会有如下 sql：结果如下图所示：
模糊查询优化建议：修改业务，让模糊查询必须包含条件字段前面的值；如果条件只知道中间的值，需要模糊查询去查，那就建议使用elasticsearch或其它搜索服务器。
优化后结果如下：
五、范围查询
拿测试表举例，比如要取出b字段1到3000范围数据，sql 如下 ：
结论二：单次查询的数据量过大，优化器将不走索引，优化范围查询：降低单次查询范围，分多次查询：
实际这种范围查询而导致使用不了索引的场景经常出现，比如按照时间段抽取全量数据，每条sql抽取一个月的；或者某张业务表历史数据的删除。遇到此类操作时，应该在执行之前对sql做explain分析，确定能走索引，再进行操作；
六、计算操作
有时我们与有对条件字段做计算操作的需求，在使用 sql 查询时，就应该小心了；
优化后结果：
结论三：一般需要对条件字段做计算时，建议通过程序代码实现，而不是通过mysql实现。如果在mysql中计算的情况避免不了，那必须把计算放在等号后面
总结
应该避免隐式转换、like查询不能以%开头，范围查询时，包含的数据比例不能太大，不建议对条件字段做运算及函数操作；
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论