mysql-index

 转自
 04 | 深入浅出索引(上) MySQL实战45讲
 05 | 深入浅出索引(下) MySQL实战45讲

MySQL 不走索引的情况

转自 18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?

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

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

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

1
2
3
4
5
6
7
8
9
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 语句可能会这么写:

案例二:隐式类型转换

数据类型转换的规则是什么?
为什么有数据类型转换,就需要走全索引扫描?

这里有一个简单的方法,看 select “10” > 9 的结果:
如果规则是“将字符串转成数字”,那么就是做数字比较,结果应该是 1;
如果规则是“将数字转成字符串”,那么就是做字符串比较,结果应该是 0。

select * from tradelog where tradeid=110717;
就知道对于优化器来说,这个语句相当于:
select * from tradelog where CAST(tradid AS signed int) = 110717;

现在,我留给你一个小问题,id 的类型是 int,如果执行下面这个语句,是否会导致全表扫描呢?
select * from tradelog where id=”83126”;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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');

在这个执行计划里,是从 tradelog 表中取 tradeid 字段,再去 trade_detail 表里查询匹配字段。因此,我们把 tradelog 称为驱动表,把 trade_detail 称为被驱动表,把 tradeid 称为关联字段。

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

参照前面的两个例子,你肯定就想到了,字符集 utf8mb4 是 utf8 的超集,所以当这两个类型的字符串在做比较的时候,MySQL 内部的操作是,先把 utf8 字符串转成 utf8mb4 字符集,再做比较。
CONVERT() 函数,在这里的意思是把输入的字符串转成 utf8mb4 字符集。

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

References

[1] 04 | 深入浅出索引(上) MySQL实战45讲
[2] 05 | 深入浅出索引(下) MySQL实战45讲