mysql 查询结果为空时 ID倒序索引导致性能极低

seraph17 2019-03-25 05:49:18
首先这个表比较奇葩,字段很多,130个
查询的时候做了字段过滤,但是效果基本没什么差别

查询条件做过索引,分别是shop_id和type这两个。另外因为大量删除旧数据 id已经用到4亿多了,但是数据量800w到1200w之间,定时删

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wifi_wa_data | 0 | PRIMARY | 1 | ID | A | 10707454 | NULL | NULL | | BTREE | | |
| wifi_wa_data | 1 | add_time | 1 | add_time | A | 823650 | NULL | NULL | YES | BTREE | | |
| wifi_wa_data | 1 | type | 1 | type | A | 4 | NULL | NULL | YES | BTREE | | |
| wifi_wa_data | 1 | shop_id | 1 | shop_id | A | 25 | NULL | NULL | YES | BTREE | | |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
type的类别很少,shop_id的值也不多

查询基本上就是SELECT XXX FROM `wifi_wa_data` WHERE shop_id=5 and type = 4 ORDER BY `ID` DESC LIMIT 0, 10;
这么个简单的查询。查询字段十来个

本地数据库测试的时候并没有发现问题,数据量200W,查询都是毫秒级
然而线上数据库发现查询结果为空时sending data卡住很长时间。
去掉ORDER则大幅好转,200毫秒左右。不去掉ORDER,只要有命中数据的也都很快顶多几十毫秒

iD倒序,结果为空时则会卡住数十秒甚至几分钟。并且insert会Waiting for table level lock

将查询字段全删掉,只查ID的现象也是一样……

myisam引擎……
...全文
265 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
seraph17 2019-05-28
  • 打赏
  • 举报
回复
引用 5 楼 include_xjy 的回复:
问题关键在 Using filesort, mysql 8.0 以前只支持顺序索引,order by desc 会将索引从头扫到尾,等同于全表扫描。 说到解决方案,不才有个大胆的想法。 使用一个大数,减去数据创建时候的时间戳, 以此字段建立索引, 用于排倒序的数据。 id排正序数据。
不确定是不是这个原因 实际上我的查找目标位于表的最前,倒序查询的时候也不会用那么久,不到一秒。 只有查询结果为空时才会卡住 所以我先正序查了一下条件是否有值,再取数据,就规避了……但是原理还没搞明白 你的这个想法有点意思哇~但是我用的自增索引,用时间戳的话需要避免重复也挺麻烦的
include_xjy 2019-05-27
  • 打赏
  • 举报
回复
问题关键在 Using filesort, mysql 8.0 以前只支持顺序索引,order by desc 会将索引从头扫到尾,等同于全表扫描。 说到解决方案,不才有个大胆的想法。 使用一个大数,减去数据创建时候的时间戳, 以此字段建立索引, 用于排倒序的数据。 id排正序数据。
seraph17 2019-03-28
  • 打赏
  • 举报
回复
补充一点信息

我后来explain了一下发现我本地实验的服务器和出问题的结果不一样
本地
explain select * from wifi_wa_data where shop_id=5 and type =4 order by ID desc limit 10;;
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | wifi_wa_data | ref | type,shop_id | shop_id | 5 | const | 1 | Using where; Using filesort |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-----------------------------+


问题服务器
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | wifi_wa_data | index | type | PRIMARY | 4 | NULL | 10 | Using where |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+


问题服务器使用的索引扫描 没有使用type shop_id索引貌似

两个库结构完全一样,区别只是后者的表存数据量更大

seraph17 2019-03-28
  • 打赏
  • 举报
回复
有人有好的建议吗……
seraph17 2019-03-26
  • 打赏
  • 举报
回复
引用 1 楼 卖水果的net 的回复:
可能是表的大小过大,而实际数据,并没有这么多,可以考虑把表优化一下。

大小过大的意思是表体积么?
大约5G……太大么?
卖水果的net 版主 2019-03-25
  • 打赏
  • 举报
回复
可能是表的大小过大,而实际数据,并没有这么多,可以考虑把表优化一下。

34,571

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧