案例一:条件字段函数操作
假设你现在维护了一个交易系统,其中交易记录表tradelog包含交易流水号(tradeid)、交易员id(operator)、交易时间(t_modified)等字段。为了便于描述,我们先忽略其他字段。这个表的建表语句如下:
mysql> CREATE TABLE `tradelog` (
ìdìnt(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL,
òperatorìnt(11) DEFAULT NULL,
`t_modified` datetime DEFAULT NULL,
PRIMARY KEY (ìd`),
KEY `tradeid` (`tradeid`),
KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
要统计发生在所有年份中7月份的交易记录总数。这个逻辑看上去并不复杂,你的SQL语句可能会这么写:
mysql> select count(*) from tradelog where month(t_modified)=7;
由于t_modified字段上有索引,于是你就很放心地在生产库中执行了这条语句,但却发现执行了特别久,才返回了结果。
如果对字段做了函数计算,就用不上索引了
为什么会导致这样的现象呢?
B+树可以快速定位,其能力来源于同一层兄弟节点的有序性。但是,如果计算month()函数的话,在树的第一层就不知道该怎么办了。
对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
案例二:隐式类型转换
mysql> select * from tradelog where tradeid=110717;
交易编号tradeid这个字段上,本来就有索引,但是explain的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid的字段类型是varchar(32),而输入的参数却是整型,所以需要做类型转换。
对于这个案例来说
mysql> select * from tradelog where tradeid=110717;
就相当于
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
也就是说其对索引字段添加了函数,从案例一可知,这种情况下会走全表扫描。