第十八章 为什么这些SQL语句逻辑相同，性能却差异巨大？

第十八章 为什么这些SQL语句逻辑相同，性能却差异巨大？

什么情况下可能导致索引失效 ?

• 索引字段手动的函数操作
• 隐式类型转换
• 隐式字符编码转换等

条件字段函数操作

索引字段手动函数操作下，什么情况会发生索引失效，怎么解决，举一个例子说明一下 ?

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;

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

• t_modified 索性字段的有序性是建立在其完整的基础上的
• 而 函数 month( ) 相当于对引擎获取的索引树的节点首先进行函数运算，此时有序性被打破，优化器分析认为，需要进行全表扫描
• 由于该普通索引树比主键索引树要小，索引选择遍历普通索引树叶节点链表

优化器 explain 结果：

• key = “t_modified” 表示的是：使用了 t_modified 这个索引；
• 我在测试表数据中插入了 10 万行数据，rows=100335，说明这条语句扫描了整个索引的所有值；
• Extra 字段的 Using index，表示的是使用了覆盖索引
  • Using index：索引覆盖，此时不需要进行回表了

解决方法

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

全表扫描一定没有使用索引吗 ?

• 不是的
• 全表扫描一般指扫描主键索引树
• 但若不需要回表查询数据，则如果普通索引树比主键索引树要小，就扫描普通索引树，此时执行 explain 命令，可以看到使用的是哪个索引

select * from tradelog where id + 1 = 10000 没有破坏搜索树的有序性，能正常走索引吗 ?

• 不能，优化器偷懒了，只要是对索引字段进行了函数操作，就不考虑该索引的正常执行
• 可以改写成：where id = 10000 -1，就能够用上索引快速查找

隐式类型转换

如果一个查询语句，索引字段类型是字符串，此时输入参数为数字，那能走索引吗？为什么 ?

• 不一定，需要根据数据库的类型转换规则进行判断

SELECT '10' > 9;

• 如上进行数字与字符的大小比较：
  • 如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1
  • 如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0
    • 字符串比较大小是根据高位的 ASCII 码比较的，‘9’比‘1’大，所以 ‘10’ 比 ‘9’ 小
• 如果该数据库是 “字符串转成数字”，则相当于 CAST(XX AS signed int)，为索引的函数操作，此时索引失效

再看一个例子：

id 的类型是 int，如果执行下面这个语句，是否会导致全表扫描呢 ?

select * from tradelog where id="83126";

• 不会
• 字符串和数字做比较，会将字符串转换为数字
• 现在字符串在比较符右边，只会把右边的转换为数字，所以不发生函数操作，会走索引快速查找
• 也就是说：因为转换的对象是 右边的常量，而不是 索引字段 ，不满足 “对索引字段做函数操作”，所以会走索引

隐式字符编码转换

什么是驱动表和被驱动表 ? 选择的依据是什么 ?

• 多表关联查询，优先执行查询逻辑的表是驱动表，对其而言，第二张被执行查询的表是被驱动表
• 一般优化器会根据筛选条件、索引决定 —— 哪张表小选哪张为驱动表

两个表关联查询，若关联的索引字段编码不一致，进行筛选查询时，是否一定索引失效 ?

• 不一定
• 若关联查询为左（右）连接，看用户选择哪张表作为驱动表（对应左连接为左表，右连接为右表）
• 若关联查询是内连接，则看优化器选择哪张表为驱动表
• 关联的两个字段上，编码的函数转换在驱动表上，则被驱动表依然能使用索引，否则索引失效
• 换个说法：一看驱动表的索引是否生效，二看被驱动表的索引是否生效
  • 一般情况下，这种场景多出现在utf8编码与utf8mb4编码上
  • 编写的sql被转化成了CONVERT(traideid USING utf8mb4) = ‘xx’