案例一:条件字段函数操作
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;
如果你问 DBA 同事为什么会出现这样的情况,他大概会告诉你:如果对字段做了函数计算,就用不上索引了,这是 MySQL 的规定。
- t_modified 的索引没用上
mysql> select count(*) from tradelog where month(t_modified)=7;
图 1 t_modified 索引示意图
如果你的 SQL 语句条件用的是 where t_modified=’2018-7-1’的话,引擎就会按照上面绿色箭头的路线,快速定位到 t_modified=’2018-7-1’需要的结果。
对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
图 2 explain 结果
为了能够用上索引的快速定位能力,我们就要把 SQL 语句改成基于字段本身的范围查询:
mysql> select count(*) from tradelog where
-> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
-> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or
-> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');
案例二:隐式类型转换
mysql> select * from tradelog where tradeid=110717;
交易编号 tradeid 这个字段上,本来就有索引,但是 explain 的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid 的字段类型是 varchar(32),而输入的参数却是整型,所以需要做类型转换。
这里就有两个问题:
- 数据类型转换的规则是什么?
- 为什么有数据类型转换,就需要走全索引扫描?
图 3 MySQL 中字符串和数字转换的效果示意图
对于优化器来说,这个语句相当于:
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
案例三:隐式字符编码转换
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');
mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/
图 5 语句 Q1 的执行过程
- 第 1 步,是根据 id 在 tradelog 表里找到 L2 这一行;
- 第 2 步,是从 L2 中取出 tradeid 字段的值;
- 第 3 步,是根据 tradeid 值到 trade_detail 表中查找条件匹配的行。explain 的结果里面第二行的 key=NULL 表示的就是,这个过程是通过遍历主键索引的方式,一个一个地判断 tradeid 的值是否匹配。
因为这两个表的字符集不同,一个是 utf8,一个是 utf8mb4,所以做表连接查询的时候用不上关联字段的索引。这个回答,也是通常你搜索这个问题时会得到的答案。
实际上这个语句等同于下面这个写法:
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;
图 6 explain 结果
这时候 $R4.tradeid.value 的字符集是 utf8, 按照字符集转换规则,要转成 utf8mb4,所以这个过程就被改写成:
select operator from tradelog where traideid =CONVERT($R4.tradeid.value USING utf8mb4);
这里的 CONVERT 函数是加在输入参数上的,这样就可以用上被驱动表的 traideid 索引。
两种优化方法:
- 比较常见的优化方法是,把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4,这样就没有字符集转换的问题了。
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
- 如果能够修改字段的字符集的话,是最好不过了。但如果数据量比较大, 或者业务上暂时不能做这个 DDL 的话,那就只能采用修改 SQL 语句的方法了。
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
图 7 SQL 语句优化后的 explain 结果