当前位置： 首页>后端>正文

索引不能导出bmp 索引不生效的原因

后端2024-06-18 17:45:53

一、前言

      最近在写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、执行计划

mysql> explain 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');
+----+-------------+-------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | user_server | ALL  | idx_reg_time  | NULL | NULL    | NULL | 14117522 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

      表中的索引没有用到，导致进行了全表扫描，性能低下。问题是咱们已经用了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子句。

 

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

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

 


https://www.xamrdz.com/backend/3kv1964234.html

相关文章：