作者:阿茂
我们在第四篇讲了索引原理,这一篇我们列举一些索引失效的场景,谈谈平时大家都不太注意的一些细节问题:
索引条件使用函数操作或运算
看到这个标题有的哥们就说了:这个我知道,给索引条件加函数Mysql不会走索引。那么是为什么呢?我们来分析一下,将第五章的建表语句稍微改动下:
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
`update_time` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `update_index` (`update_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;添加update_time字段 ,并给此字段添加索引
现在我们要统计12月的记录一共有多少条记录SQL语句如下:
select count(*) from t where month(update_time)=12;
看运行结果竟然用到了update_index这个索引,惊奇不惊奇,意外不意外?然后我们再细分析一下,实际上这个函数已经破坏了索引的原本的结构,在这种情况下SQL优化器可以选择唯一索引,也可以选择索引update_index,然鹅优化器在经过一系列运算后发现还是用update_index划算,那么他就还是走了update_index。但是我们看一下执行计划的rows是10004条(本表一共有10000条记录,满足条件的有9000多条),也就是说虽然走了update_index索引,却走了全表扫描(这就论证了,业内所传的函数条件导致不走索引的结论不是很严谨,哈哈哈...)。再看看我们使用这样一条SQL的执行计划:
EXPLAIN select * from t where month(update_time)=12;
当需要查询非索引字段时Mysql果断放弃了update_index而使用了全表扫描。
说完函数我们说说索引条件的运算,这个就比较好理解的了,举个例子:
select * from t where id + 1 = 10
这个条件并不会破坏索引原有结构的有序性,但是Mysql还是不能通过索引一次性找到这条记录,需要扫描全表
隐式转换
我们这里抛出一个sql大家猜猜看看是什么结果就知道了:
select “10” > 9
我这里就不截图了,它查询结果是1,也就是说MySQL中,字符串和数字做比较的话,是将字符串转换成数字。那么它的执行SQL是这样的:
select CAST("10" AS signed int) > 9
假如我们将这种使用方式在where条件中,就会破坏到当前条件索引,平时在写sql时注意:一般都是宽类型向窄类型转换,尽量将需要转换的类型放在表达式后面。
隐式字符编码转换
这里大家需要注意的是:多表关联查询时,被驱动表的字符集与其他表的不一致时,被驱动的表的关联索引字段会加上字符转换函数CONVERT(),又会导致全表扫描。所以在数据库表设计和创建之初需要有统一规范的建模流程,避免这样的惨剧发生。