Mysql 中隐式转换

案例一:条件字段函数操作

假设你现在维护了一个交易系统,其中交易记录表 tradelog 包含交易流水号(tradeid)、交易员 id(operator)、交易时间(t_modified)等字段。为了便于描述,我们先忽略其他字段。这个表的建表语句如下:

mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设,现在已经记录了从 2016 年初到 2018 年底的所有数据,运营部门有一个需求是,要统计发生在所有年份中 7 月份的交易记录总数。这个逻辑看上去并不复杂,你的 SQL 语句可能会这么写:

mysql> select count(*) from tradelog where month(t_modified)=7;

由于 t_modified 字段上有索引,于是你就很放心地在生产库中执行了这条语句,但却发现执行了特别久,才返回了结果。如果你问 DBA 同事为什么会出现这样的情况,他大概会告诉你:如果对字段做了函数计算,就用不上索引了,这是 MySQL 的规定。现在你已经学过了 InnoDB 的索引结构了,可以再追问一句为什么?为什么条件是 where t_modified=‘2018-7-1’的时候可以用上索引,而改成 where month(t_modified)=7 的时候就不行了?下面是这个 t_modified 索引的示意图。方框上面的数字就是 month() 函数对应的值。

Mysql 中隐式转换

 对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。

案例二:隐式类型转换


mysql> select * from tradelog where tradeid=110717;

交易编号 tradeid 这个字段上,本来就有索引,但是 explain 的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid 的字段类型是 varchar(32),而输入的参数却是整型,所以需要做类型转换。有数据类型转换,就需要走全索引扫描。

实际相当于:


mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

案例三:隐式字符编码转换

假设系统里还有另外一个表 trade_detail,用于记录交易的操作细节。为了便于量化分析和复现,我往交易日志表 tradelog 和交易详情表 trade_detail 这两个表里插入一些数据。

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1, ‘aaaaaaaa‘, 1000, now());
insert into tradelog values(2, ‘aaaaaaab‘, 1000, now());
insert into tradelog values(3, ‘aaaaaaac‘, 1000, now());

insert into trade_detail values(1, ‘aaaaaaaa‘, 1, ‘add‘);
insert into trade_detail values(2, ‘aaaaaaaa‘, 2, ‘update‘);
insert into trade_detail values(3, ‘aaaaaaaa‘, 3, ‘commit‘);
insert into trade_detail values(4, ‘aaaaaaab‘, 1, ‘add‘);
insert into trade_detail values(5, ‘aaaaaaab‘, 2, ‘update‘);
insert into trade_detail values(6, ‘aaaaaaab‘, 3, ‘update again‘);
insert into trade_detail values(7, ‘aaaaaaab‘, 4, ‘commit‘);
insert into trade_detail values(8, ‘aaaaaaac‘, 1, ‘add‘);
insert into trade_detail values(9, ‘aaaaaaac‘, 2, ‘update‘);
insert into trade_detail values(10, ‘aaaaaaac‘, 3, ‘update again‘);
insert into trade_detail values(11, ‘aaaaaaac‘, 4, ‘commit‘);

这时候,如果要查询 id=2 的交易的所有操作步骤信息,SQL 语句可以这么写:


mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/

实际执行

select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

小结

今天我给你举了三个例子,其实是在说同一件事儿,即:对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。第二个例子是隐式类型转换,第三个例子是隐式字符编码转换,它们都跟第一个例子一样,因为要求在索引字段上做函数操作而导致了全索引扫描。MySQL 的优化器确实有“偷懒”的嫌疑,即使简单地把 where id+1=1000 改写成 where id=1000-1 就能够用上索引快速查找,也不会主动做这个语句重写。

https://time.geekbang.org/column/article/74059?utm_source=pinpaizhuanqu&utm_medium=geektime&utm_campaign=guanwang&utm_term=guanwang&utm_content=0511

相关推荐