mysql使用不上索引的几种情况

索引出现的主要目的是提高查询语句的执行效率。为了能够提高查询语句的性能，我们基本的做法都是在查询条件字段上合理地增加索引来实现的。

不过，你是否碰到过类似的情况，查询的条件字段明明有创建索引，可是MySQL 服务器为什么没有使用索引，而是走全表扫描呢？是否曾怀疑查询优化器生成的执行计划是不是有问题呢？

查询优化器是根据数据表的统计信息来生成执行计划的，统计的信息会有一定的误差，所以优化器生成的执行计划是可能有问题的。

但是在怀疑优化器生成的执行计划有问题之前，可以检查一下SQL 语句是否存在一下的情况。因为如果有一下的情况的话，查询确实是不会使用索引的。

条件字段函数操作

这个情况怎么理解呢？其实很好理解，就是在查询条件字段上使用了函数计算，类似于select f1,f2 form t where month(f1)=7。这种写法，即使在字段f1上有建立索引，MySQL 做查询时也是使用不上的，因为函数操作可能会破坏了索引值的有序性！

为什么会是这样子呢？

字段的索引是有序的，它是根据字段的值进行排序的。假如 t1 是表示时间的信息，那么用来进行索引排序的值就类似于这样的

2018-02-23 11:23:18
2018-05-16 01:09:25
2019-01-12 08:17:27
2019-06-08 09:37:57

如果对字段进行了函数操作，如month(f1)=7,那么要到索引树中查找的值就是 7 了。很明显，MySQL数据库是无法从索引树查到对应的记录的，所以只能放弃使用索引了。

口说无凭，来看一个例子吧。

我创建了一个测试表，表结构如下：

CREATE TABLE `new_t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `f1` datetime NOT NULL,
  `f2` varchar(12) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_f1` (`f1`),
  KEY `idx_f2` (`f2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

然后随机插入了 30 万左右的数据。

接下来测试了以下两个查询语句的执行计划。

查询 1

mysql> explain select f1,f2 from new_t where year(f1) = 2019\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: new_t
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 320048
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

查询 2

mysql> explain select f1,f2 from new_t where f1 = '2019'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: new_t
   partitions: NULL
         type: ref
possible_keys: idx_f1
          key: idx_f1
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 5 warnings (0.00 sec)

从以上的信息可以看出，查询 1 的查询条件使用了函数操作 year()，它破坏了索引值的有序性，所以查询使用不了索引，只能走全表扫描，遍历完 32 万的记录才能得到结果。

查询 2 的查询条件没有使用函数操作，能正确的使用索引 idx_f1 进行查询，只需读取一行记录就能返回结果了。

隐式类型转换

还是使用上面创建的测试表，我们再来看下面的例子：

查询 3

mysql> explain select * from new_t where f2=746983\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: new_t
   partitions: NULL
         type: ALL
possible_keys: idx_f2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 319725
     filtered: 10.00
        Extra: Using where
1 row in set, 3 warnings (0.00 sec)

在字段 f2 我明明是创建了索引的，为什么查询没有使用索引，反而进行全表扫描呢？

不知道你看到 explain 的结果时是不是会有类似的疑问。不过从标题你也可以猜到，这是因为 MySQL 进行了隐式类型转换的原因。

现在这里就有两个问题需要理解的：

1. 数据类型转换的规则是什么？
2. 为什么有数据类型转换，就需要走全索引扫描？

先来看第一个问题，你可能会说，数据库里面类型这么多，这种数据类型转换规则更多，我记不住，应该怎么办呢？

这里有一个简单的方法，看 select “10” > 9 的结果：

如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是1；

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

mysql> select "10" > 9\G;
*************************** 1. row ***************************
"10" > 9: 1
1 row in set (0.00 sec)

从图中可知，select “10” > 9 返回的是1，所以你就能确认MySQL里的转换规则了：在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。

再回来看我们刚才的查询 3 语句，MySQL 内部也进行了类似的类型转换，它相当于：

explain select * from new_t where CAST(f2 AS signed int)=746983\G;

也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

隐式字符编码转换

会发生隐式字符编码转换的原因是两个相互关联查询的表的字符集不同。例如，一个是utf8，一个是utf8mb4，在做表连接查询的时候就可能用不上关联字段的索引了。

其实在这个连接过程中，MySQL 是会使用函数将某一方的字符集进行转换才执行比较操作的。

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

最后

总的来说，以上三种情况都是在说同一件事儿，即：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

看了这篇文章，以后做表结构设计，写查询语句时可要长点心了，别犯那样的错误了