Smarticen Notes

索引失效的问题

6 min read

在很多时候，存在逻辑相同但是性能差异巨大的SQL语句，对这些语句使用不当就会导致数据库的压力变大。

以下面的表为例：

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;

添加字段函数操作

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

不过需要注意，放弃走树搜索，并不代表不会使用这个索引。因为如果逻辑一致的话，mysql会选择大小更小的那个索引，所以遍历也可能会选择正确的索引，但不会利用这个索引快速定位，只是单纯的遍历。

优化器在这个问题上确实有“偷懒”的行为，即使是对于不改变有序性的函数，也不会考虑使用索引。比如，对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加 1 操作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以，需要你在写 SQL 语句的时候，手动改写成 where id = 10000 -1 才可以。

隐式类型转换

查看下面的查询语句：

mysql> select * from tradelog where tradeid=110717;

初步一看没有什么问题，也能正确查询到结果，但是explain的结果却显示这个查询需要走全表扫描。

这是因为tradeid是varchar类型，而我们查询的参数却是整型，所以会做类型转换。

在mysql中，字符串和数字比较会将字符串转为数字。所以上面的查询语句就相当于

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

这就相当于对索引字段做函数操作，优化器会放弃走树搜索功能。

但是下面的这条语句就能够走索引：

select * from tradelog where id="83126";
 
#等价于
select * from tradelog where id=CAST("83126" as signed int);

没有对字段做函数操作。

隐式字符编码转换

现在有另一个trade_detail表，是根据tradeid与tradelog表关联的。

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;

要查询id=2的交易的所有操作步骤信息，SQL语句可以这样写：

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/
  1. 根据id在tradelog表中找到记录
  2. 取出1中记录的tradeid字段的值
  3. 根据tradeid值到trade_detail表中查找条件匹配的行。这个匹配是通过遍历主键索引的方式，一个一个判断是否匹配的。 第三步这里是不符合预期，我们是希望能够使用tradeid索引快速定位到等值的行的，但是这里没有。

这里的原因就是这两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询的使用用不上关联字段的索引。至于为什么，这个与隐式类型转换也有点类似。 如果我们把第三步单独写成一个SQL语句的话，那就是：

select * from trade_detail where tradeid=$L2.tradeid.value;

其中，$L2.tradeid.value的字符集是utf8mb4。字符集utf8mb4是utf8的超集，所以这两个类型的字符串在进行比较的时候，MYSQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。也就是说上面的语句实际等价于

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

这就与我们前面所讲的保持一致了，对索引字段做函数操作，优化器会放弃走树搜索的功能。

业务上遇到这种情况，一是可以更改字符集都为utf8mb4来解决，或者修改SQL语句，

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

主动将l.tradeid转为utf8，避免被驱动表上的字符编码转换。

Graph View