Mysql中条件字段有索引,但使用不了索引的几种场景详解

目录

• mysql条件字段有索引,但使用不了索引的场景
• 一、前期准备
• 二、函数操作
• 三、隐式转换
• 四、模糊查询
• 五、范围查询
• 六、计算操作
• 总结

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,php
  `b` int(20) DEFAULT NULL,
  `c` djavascriptatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,  
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`) USING BTREE,
  KEY `idx_b` (`b`) USIwww.devze.comNG 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的值进行wh编程ile循环 */
    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查询不能以%开头，范围查询时，包含的数据比例不能太大，不建议对条件字段做运算及函数操作；

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。