做业务开发的同学都知道，有些SQL看起来没啥问题，索引也用到了，但就是很慢.......

今天我们来通过几个常见的例子来深入探究下SQL慢的原因

案例一

假设你现在维护了一个帖子表，包含帖子id（tweet_id）、用户id（user_id）、时间（gmt_create）等字段。为了便于描述，我们先忽略其他字段。这个表的建表语句如下：

MySQL> CREATE TABLE `t_tweet` (
  `id` int(11) NOT NULL,
  `tweet_id` varchar(32) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `gmt_create` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tweet_id` (`tweet_id`),
  KEY `gmt_create` (`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
复制代码

假设，现在已经记录了从2016年初到2018年底的所有数据，有一个需求是，要统计发 生在所有年份中7月份的帖子记录总数。这个逻辑看上去并不复杂，你的SQL语句可能会这么 写：

select count(*) from t_tweet where month(gmt_create)=7;
复制代码

由于gmt_create字段上有索引，于是你就很放心地在生产库中执行了这条语句，但却发现执行了 特别久，才返回了结果。

如果你问DBA同事为什么会出现这样的情况，他大概会告诉你：如果对字段做了函数计算，就 用不上索引了，这是MySQL的规定。

如果你已经学过了InnoDB的索引结构了，可以再追问一句，为什么？

为什么条件是where gmt_create='2018-7-1’的时候可以用上索引，而改成where month(gmt_create)=7的时候就不行了？

下面是这个gmt_create索引的示意图。方框上面的数字就是month()函数对应的值。

如果你的SQL语句条件用的是where gmt_create='2018-7-1’的话，引擎就会按照上面绿色箭头的 路线，快速定位到 gmt_create='2018-7-1’需要的结果。

实际上，B+树提供的这个快速定位能力，来源于同一层兄弟节点的有序性。

但是，如果计算month()函数的话，你会看到传入7的时候，在树的第一层就不知道该怎么办了。 也就是说，对索引字段做函数操作， 可能会破坏索引值的有序性， 因此优化器就决定放弃 走树搜索功能。

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

在这个例子里，放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引 gmt_create，优化器对比索引大小后发现，索引gmt_create更小，遍历这个索引比遍历主键索引 来得更快。因此最终还是会选择索引gmt_create。 可以使用explain命令，查看一下这条SQL语句的执行结果

key="gmt_create"表示的是，使用了gmt_create这个索引；我在测试表数据中插入了10万行数 据，rows=100335，说明这条语句扫描了整个索引的所有值；Extra字段的Using index，表示的是使用了覆盖索引。

也就是说，由于在gmt_create字段加了month()函数操作，导致了全索引扫描。

到这里我给你说明了，由于加了month()函数操作，MySQL无法再使用索引快速定位功能，而只能使用全索引扫描。

优化器在某些问题上确实有“偷懒”行为，即使是对于不改变有序性的函数，也不会考虑使用索 引。

比如，对于select * from t_tweet where id + 1 = 10000这个SQL语句，这个加1操作并不会改变有序性，但是MySQL优化器还是不能用id索引快速定位到9999这一行。所以，需要你在写 SQL语句的时候，手动改写成 where id = 10000 -1才可以。

案例二

我们一起看一下这条SQL语句：

select * from t_tweet where tweet_id=110717;
复制代码

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

那么，现在这里就有两个问题：

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

先来看第一个问题，你可能会说，数据库里面类型这么多，这种数据类型转换规则更多，我记不 住，应该怎么办呢？ 这里有一个简单的方法，看 select “10” > 9的结果：

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

这时，你再看这个全表扫描的语句：

select * from t_tweet where tweet_id=110717;
复制代码

就知道对于优化器来说，这个语句相当于：

select * from t_tweet where CAST(tweet_id AS tweetId int) = 110717;
复制代码

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

案例三

假设系统里还有另外一个表tweet_detail，用于记录帖子的详情数据。

mysql> CREATE TABLE `t_tweet_detail` (
  `id` int(11) NOT NULL,
  `tweet_id` varchar(32) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL, /* 帖子作者id*/
  `tweet_info` varchar(32) DEFAULT NULL, /* 帖子内容 */
  PRIMARY KEY (`id`),
  KEY `tweet_id` (`tweet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码

这时候，如果要查询id=2的帖子的所有信息，SQL语句可以这么写：

select d.* from t_tweet l, t_tweet_detail d where t1.tweet_id=t2.tweet_id and t2.id=2; 
复制代码

explain的结果里面第二行的key=NULL表示的就是，这个过程是通过遍历主键索引的方式，一个一个地判断tweet_id的值是否匹配。

表示没有用上帖子详情表t_tweet_detail上的tweet_id索引，进行了全表扫描。

进行到这里，你会发现第3步不符合我们的预期。因为表t_tweet_detail里tweet_id字段上是有索引 的，我们本来是希望通过使用tweet_id索引能够快速定位到等值的行。但，这里并没有。

如果你去问DBA同学，他们可能会告诉你，因为这两个表的字符集不同，一个是utf8，一个是 utf8mb4，所以做表连接查询的时候用不上关联字段的索引。

这个回答，也是通常你搜索这个问 题时会得到的答案。 但是你应该再追问一下，为什么字符集不同就用不上索引呢？

结论是：字符集utf8mb4是utf8的超集，所以当这两个类型的字 符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。

这个设定很好理解，utf8mb4是utf8的超集。类似地，在程序设计语言里面，做自动类型转换 的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方 向”进行转换的。

因此， 在执行上面这个语句的时候，需要将被驱动数据表里的字段一个个地转换成utf8mb4

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

优化：把t_tweet_detail表上的tweet_id字段的字符集也改成utf8mb4，这样就 没有字符集转换的问题了。

原文链接：https://juejin.im/post/6860513755000733709