mysql 中的最后一页达不到 limit 的要求,查询变得奇慢无比,求问为什么啊?

2016-04-27 15:03:17 +08:00
 Weixiao0725

我的 sql 语句: select * from table_name force index(PRIMARY) where source_sys = 'gfm' and order_create_gmt >= '2016-03-08 00:00:00' and order_create_gmt<= '2016-03-08 23:59:59' and id >10152664498617 order by id asc limit 1000; 正常的走主键索引查询是很快的,但是最后一页不够 1000 的时候,查询变得很慢。

另外大家对 mysql 执行引擎偶尔改变索引导致查询变慢都是怎么解决的? force index 吗?谢谢!

6763 次点击
所在节点    MySQL
18 条回复
hinkal
2016-04-27 15:06:58 +08:00
Q
sefemp
2016-04-27 15:08:44 +08:00
记录下最后第二页到哪里
keakon
2016-04-27 15:24:23 +08:00
你的索引可能不能用来优化这个查询,导致需要全表扫描。你 explain 一下就知道原因了。
ksupertu
2016-04-27 16:24:16 +08:00
是不是存了很多二进制内容…这个很容易导致全表扫描,新出的版本针对这个问题有一些优化
Weixiao0725
2016-04-27 16:28:49 +08:00
上 irc 的 mysql 频道问了下,应该是 id > 1372 之后有很多数据,需要全部扫描才知道后面有多少数据。
CosWind
2016-04-27 17:46:07 +08:00
http://timyang.net/data/key-list-pagination/ 最好用> from_id 的方式分页
ryd994
2016-04-27 20:52:56 +08:00
我想知道,为什么 force index(PRIMARY) ?
Walleve0
2016-04-28 00:43:52 +08:00
不明白你一页的意思
建议用 explain 和 show profile 看下具体执行过程就知道了。
Weixiao0725
2016-04-28 09:27:55 +08:00
@ryd994 这就是我最后说的那个问题,有时候 MySql 自己会走错索引,导致查询变慢, 所以我才加的 force index
Weixiao0725
2016-04-28 09:29:24 +08:00
@CosWind id >10152664498617 ,我是这么做的啊。但是我看 timyang 说的这个超长列表问题,即数据的长尾问题,也没有好的解决方案。
CosWind
2016-04-28 09:37:02 +08:00
@Weixiao0725 sorry ,没看清除,不过数据这么大,为什么不做下 sharding
ihuotui
2016-04-28 11:00:21 +08:00
Mysql 的分页查询语句的性能分析


  MySql 分页 sql 语句,如果和 MSSQL 的 TOP 语法相比,那么 MySQL 的 LIMIT 语法要显得优雅了许多。使用它来分页是再自然不过的事情了。

2.1 最基本的分页方式:

Sql 代码 收藏代码

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

在中小数据量的情况下,这样的 SQL 足够用了,唯一需要注意的问题就是确保使用了索引:
举例来说,如果实际 SQL 类似下面语句,那么在 category_id, id 两列上建立复合索引比较好:

Sql 代码 收藏代码
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10


2.2 子查询的分页方式:

随着数据量的增加,页数会越来越多,查看后几页的 SQL 就可能类似:
Sql 代码 收藏代码
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10

一言以蔽之,就是越往后分页, LIMIT 语句的偏移量就会越大,速度也会明显变慢。
此时,我们可以通过子查询的方式来提高分页效率,大致如下:
Sql 代码 收藏代码
SELECT * FROM articles WHERE id >=
(SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10


2.3JOIN 分页方式

Sql 代码 收藏代码
SELECT * FROM `content` AS t1
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

经过我的测试, join 分页和子查询分页的效率基本在一个等级上,消耗的时间也基本一致。
explain SQL 语句:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

----------------------------------------

为什么会这样呢?因为子查询是在索引上完成的,而普通的查询时在数据文件上完成的,通常来说,索引文件要比数据文件小得多,所以操作起来也会更有效率。

实际可以利用类似策略模式的方式去处理分页,比如判断如果是一百页以内,就使用最基本的分页方式,大于一百页,则使用子查询的分页方式。
ihuotui
2016-04-28 11:02:16 +08:00
看你数据量吧,建议看看我提供的资料
Weixiao0725
2016-04-28 15:35:21 +08:00
@ihuotui 好的,谢谢
pine
2016-05-13 16:40:58 +08:00
和索引的顺序有关,没看你贴出索引顺序啊
Weixiao0725
2016-05-13 17:26:48 +08:00
@pine 应该不是索引顺序吧,就是同样的 sql 语句,以前他会走一个索引,现在他又走另一个索引。所以我只能 force index 解决。
pine
2016-05-16 09:57:04 +08:00
source_sys order_create_gmt 这两个索引你怎么建的? force index(PRIMARY) 感觉怪怪的。。
Weixiao0725
2016-05-16 10:55:17 +08:00
@pine order_create_gmt 和 source_sys 有各自的索引。后来,我只能先根据查询条件先查出满足条件的一共多少条,然后最后一页的时候 limit 一个准确的数字,这样能快不少。

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/274802

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX