明明表中有这个索引，但mysql的force index 无效？

女爷i 2021-10-16 01:56 590阅读 0赞

## 一、前言 ##

最近在写`sql`的时候，会格外注意一些`sql`的优化，针对复杂的`sql`，优化器使用的方案并不是最佳方案，所以可能需要我们使用`force index`这种方式来自己选择索引，加快查询速度。这次记录一下明明表中有这个索引，但`mysql`的`force index` 无效的问题。

## 二、分析问题 ##

### 1、问题sql ###

select uuid as small_uuid, user_id as small_user_id from user_server force index (idx_reg_time)  where 1 and reg_time >= 
    CONVERT_TZ('2019-07-30 00:00:00','-07:00','-07:00') ;

这里贴出来的`sql`是简化过的，实际的`sql`主要是和三个表关联查询，一个千万级，两个百万级，虽然表的数据很唬人，但是通过`where`条件的筛选，实际查询的数据量是`60W`左右，预期是`1s`内完成。但实际上却花了`18s`。

### 2、执行计划 ###

表中的索引没有用到，导致进行了全表扫描，性能低下。问题是咱们已经用了`force index`，为啥会用不到索引呢？博主百思不得其解。

### 3、查看表结构 ###

| user_server | CREATE TABLE `user_server` (
      `user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `uin` bigint(20) unsigned NOT NULL,
      `last_login_ip` varchar(32) NOT NULL DEFAULT '',
      `reg_time` int(11) NOT NULL DEFAULT '0',    //这里是int （11）
      PRIMARY KEY (`user_id`),
      KEY `idx_uin_server_id` (`uin`,`server_id`),
      KEY `idx_server_id` (`server_id`),
      KEY `idx_uuid` (`uuid`),
      KEY `idx_uuid_repeat` (`uuid_repeat`),
      KEY `idx_reg_time` (`reg_time`)
    ) ENGINE=InnoDB AUTO_INCREMENT=24805032 DEFAULT CHARSET=utf8 |

在查看表结构的时候，博主注意到`reg_time`这个字段是`int`类型，而我们的`sql`在使用`where`条件的时候字段格式是不对的，是的，犯了索引中的大忌，**索引字段和索引条件的类型必须一致才能用到索引**。

**改sql：**

select uuid as small_uuid, user_id as small_user_id from user_server force index (idx_reg_time)  where 1 and reg_time >= 
    unix_timestamp(CONVERT_TZ('2019-07-30 00:00:00','-07:00','-07:00')) ;			（0.6s）

通过`unix_timestamp()`方法转换日期为时间戳格式，性能突飞猛进，`0.6s`就完事了。

### 4、函数计算影响索引使用 ###

**后续又发现一个sql如下：**

select uuid as small_uuid, user_id as small_user_id from user_server force index (idx_reg_time) where uuid_repeat = 1 and CONVERT_TZ(reg_time,'-07:00','-07:00') >= unix_timestamp('2019-07-30 00:00:00')

同样的味道，同样的配方，索引还是没作用。。这次倒是很快就发现了问题，是字段上面使用函数导致的。把函数计算放到等式的右边即可。

**改为：**

select uuid as small_uuid, user_id as small_user_id from user_server force index (idx_reg_time) where uuid_repeat = 1 and reg_time,'-07:00','-07:00'>= unix_timestamp(CONVERT_TZ('2019-07-30 00:00:00','-07:00','-07:00'))

## 三、sql中没有出现排序操作，但是explain出现了using filesort ##

这部分是因为`group by`的原因，`mysql`在使用 `group by` 的时候，虽然没有使用 `order by`，如果没有索引，是可能同时出现 `using filesort`，`using temporary` 的。因为 `group by` 就是先排序在分组，如果没有排序的需要，可以加上一个 `order by NULL` 来避免排序，这样 `using filesort` 就会去除，能提升一点性能。

**mysql官方解释：** 如果使用`GROUP BY`，则输出行将根据列进行排序，`GROUP BY`就像您具有`ORDER BY`相同列的列一样。为了避免`GROUP BY` 产生排序的开销，添加`ORDER BY NULL`：

SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

依赖于隐式`GROUP BY`排序（即，在没有`ASC`或 `DESC`指示符的情况下排序）或显式排序 `GROUP BY`（即，通过对列使用显式 `ASC`或`DESC`指示符`GROUP BY`），不推荐使用。要生成给定的排序顺序，请提供一个`ORDER BY`子句。

**官方手册链接：** https://dev.mysql.com/doc/refman/5.7/en/select.html

另外，使用`order by null`性能上提升的并不多，至少在我测试，查询数据量在`300W`的时候，使用`order by null`只比不使用快了`0.8-1s`，提升很有限。不过减少了文件排序，某种意义上也减轻了`mysql`的压力，减少了磁盘的部分压力，聊胜于无吧。

**总结：** 记得在刚开始学习索引相关的内容的时候，还经常看到各种不会使用索引的情况，当时还觉得挺好分辨的。只是纸上得来终觉浅啊，自己不碰到就不知道有多坑爹，代价就是几十条sql挨个排查和我那可怜的一个多小时。**要细心！**

**end**