我的 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 吗?谢谢!
1
hinkal 2016-04-27 15:06:58 +08:00 via Android
Q
|
2
sefemp 2016-04-27 15:08:44 +08:00
记录下最后第二页到哪里
|
3
keakon 2016-04-27 15:24:23 +08:00
你的索引可能不能用来优化这个查询,导致需要全表扫描。你 explain 一下就知道原因了。
|
4
ksupertu 2016-04-27 16:24:16 +08:00 via Android
是不是存了很多二进制内容…这个很容易导致全表扫描,新出的版本针对这个问题有一些优化
|
5
Weixiao0725 OP 上 irc 的 mysql 频道问了下,应该是 id > 1372 之后有很多数据,需要全部扫描才知道后面有多少数据。
|
6
CosWind 2016-04-27 17:46:07 +08:00
http://timyang.net/data/key-list-pagination/ 最好用> from_id 的方式分页
|
7
ryd994 2016-04-27 20:52:56 +08:00
我想知道,为什么 force index(PRIMARY) ?
|
8
Walleve0 2016-04-28 00:43:52 +08:00
不明白你一页的意思
建议用 explain 和 show profile 看下具体执行过程就知道了。 |
9
Weixiao0725 OP @ryd994 这就是我最后说的那个问题,有时候 MySql 自己会走错索引,导致查询变慢, 所以我才加的 force index
|
10
Weixiao0725 OP @CosWind id >10152664498617 ,我是这么做的啊。但是我看 timyang 说的这个超长列表问题,即数据的长尾问题,也没有好的解决方案。
|
11
CosWind 2016-04-28 09:37:02 +08:00
@Weixiao0725 sorry ,没看清除,不过数据这么大,为什么不做下 sharding
|
12
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 ---------------------------------------- 为什么会这样呢?因为子查询是在索引上完成的,而普通的查询时在数据文件上完成的,通常来说,索引文件要比数据文件小得多,所以操作起来也会更有效率。 实际可以利用类似策略模式的方式去处理分页,比如判断如果是一百页以内,就使用最基本的分页方式,大于一百页,则使用子查询的分页方式。 |
13
ihuotui 2016-04-28 11:02:16 +08:00
看你数据量吧,建议看看我提供的资料
|
14
Weixiao0725 OP @ihuotui 好的,谢谢
|
15
pine 2016-05-13 16:40:58 +08:00
和索引的顺序有关,没看你贴出索引顺序啊
|
16
Weixiao0725 OP @pine 应该不是索引顺序吧,就是同样的 sql 语句,以前他会走一个索引,现在他又走另一个索引。所以我只能 force index 解决。
|
17
pine 2016-05-16 09:57:04 +08:00
source_sys order_create_gmt 这两个索引你怎么建的? force index(PRIMARY) 感觉怪怪的。。
|
18
Weixiao0725 OP @pine order_create_gmt 和 source_sys 有各自的索引。后来,我只能先根据查询条件先查出满足条件的一共多少条,然后最后一页的时候 limit 一个准确的数字,这样能快不少。
|