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

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

一、前言

      最近在写sql的时候,会格外注意一些sql的优化,针对复杂的sql,优化器使用的方案并不是最佳方案,所以可能需要我们使用force index这种方式来自己选择索引,加快查询速度。这次记录一下明明表中有这个索引,但mysqlforce 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 filesortusing 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排序(即,在没有ASCDESC指示符的情况下排序)或显式排序 GROUP BY(即,通过对列使用显式 ASCDESC指示符GROUP BY),不推荐使用。要生成给定的排序顺序,请提供一个ORDER BY子句。

 

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

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

 


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

相关文章: