Scott's world.

MySQL-MySQL-索引函数性能影响笔记

Word count: 952Reading time: 3 min
2020/02/11 Share

MySQL-索引函数性能影响笔记

MySQL中有很多看上去逻辑相同,但性能相差巨大的SQL语句。

对这些语句使用不当的话,就会不经意间导致整个数据库的压力变大。

让我们从案例出发来探讨相似的问题。

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

需要注意的是,优化器并不是放弃使用这个索引。

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

比如我们遇到有时候遇到类似于查询相关日期记录,如下面这两条sql语句

1
2
3
4
5
6
mysql> select count(*) from tradelog where month(t_modified)=7;
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');
#`t_modified`为索引字段

第一条语句是使用了函数month(),而第二句没有使用函数

这两条语句所带来的性能影响是,第一条语句会使用索引但是却放弃了树搜索的功能,导致了全索引扫描,而第二条语句通过基于字段本身的范围的查询用上了t_modified索引的快速定位能力。

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

案例二:隐式类型转换

让我们来看一下这条SQL语句

mysql> select * from tradelog where tradeid=110717;

交易编号tradeid这个字段上,本来就有索引,但是explain的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid的字段类型是varchar(32),而输入的参数却是整型,所以需要做类型转换

数据转换规则可根据简单的方法,比如

select “10” > 9

  • 如果规则是“将字符串转成数字”,那么就是做数字比较,结果应该是1;
  • 如果规则是“将数字转成字符串”,那么就是做字符串比较,结果应该是0。

对于优化器来说,这个语句相当于

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

所以同样地,对索引字段做函数操作,优化器会放弃走树搜索功能。

如果id 的类型是整数,传入的参数类型是字符串的时候,可以用上索引。

案例三:隐式字符码转换

我们知道字符集utf8mb4是utf8的超集。

有些情况下,当两个类型不同的字符串在做比较的时候,比如utf8与utf8mb4,MySQL内部的操作是,先把utf8字符串转成utf8mb4字符集,再做比较。在转换过程中,会使用到CONVERT()函数。

这就再次触发了我们上面说到的原则:对索引字段做函数操作,优化器会放弃走树搜索功能。

若从tradelog表中取tradeid字段,再去trade_detail表里查询匹配字段。因此,我们把tradelog称为驱动表,把trade_detail称为被驱动表,把tradeid称为关联字段。

字符集不同只是条件之一,连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因。

CATALOG
  1. 1. MySQL-索引函数性能影响笔记
    1. 1.1. 案例一:条件字段函数操作
    2. 1.2. 案例二:隐式类型转换
    3. 1.3. 案例三:隐式字符码转换