mysql 位运算 索引_MySql那些事儿(七):索引失效案例分析

作者:阿茂

我们在第四篇讲了索引原理,这一篇我们列举一些索引失效的场景,谈谈平时大家都不太注意的一些细节问题:

索引条件使用函数操作或运算

看到这个标题有的哥们就说了:这个我知道,给索引条件加函数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(),又会导致全表扫描。所以在数据库表设计和创建之初需要有统一规范的建模流程,避免这样的惨剧发生。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值