案例中的两张表

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;

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');

条件字段函数操作

使用上索引但还是执行很慢

1 2	-- 命中索引，但走全索引扫描 select count(*) from tradelog where month(t_modified) = 7;

优化器权衡后依然选择了走索引（t_modified），但是这个索引没有办法通过树搜索功能快速定位，只能全索引扫描(遍历索引树)。无法利用树搜索【原因：在树的根节点就不知道要前往哪个子节点，所以只能全量遍历叶子节点】
正确写法

1
2

-- 命中索引
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');

使用索引且利用上树搜索

隐式类型转换

-- tradeid为varchar类型，入参为int类型
select * from tradelog where tradeid = 110717;
-- 等价于以下sql，放弃使用索引，遍历全表
select * from tradelog where CAST(tradid AS signed int) = 110717;

因为tradeid为varchar类型，而我们的入参是int类型，此时会做一个字符串类型转换的操作
那么varchar类型和int类型匹配时，他们的转换规则是什么呢？【结论：字符串和数字做比较的话，是将字符串转换成数字】

1	select "10" > 9;

那么，当类型转换发生在参数上的时候，又会命中索引

1
2
3

select * from tradelog where id = "1";
-- 等价于
select * from tradelog where id = CAST("1" AS signed int);

补充一个情况，假如我们的字符串中含有不能转换为int的内容，此时会如何转换

SELECT CAST("10a" AS UNSIGNED INT); 		-- 结果是10
SELECT CAST('10a1' AS UNSIGNED INT); 		-- 结果是10
SELECT CAST('a10a1' AS UNSIGNED INT);		-- 结果是0
SELECT CAST('01a10' AS UNSIGNED INT);		-- 结果是1

字符串转数字是从最左侧开始识别数字,直到碰到不是数字或者到最后截止,将识别出来是数字的字符串转为数字
再补充一个sql注入的例子

1
2
3

select * from tradelog where tradeid = 0;
-- 遇到不能转换被丢弃的数据时, 等价于 0 = 0 => true
select * from tradelog where 0 = 0;

隐式字符编码转换

1
2
3

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
-- 等价于 原因：utf8mb4 是 utf8 的超集，此处会进行编码转换【被驱动表进行编码转换】
select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

explain select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;
-- 此时被驱动表为tradelog，tradelog是在入参做编码转换，可以使用上索引
-- 等价于
select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

写在最后，1024节日快乐！