mysql 有索引还是很慢怎么办？

首先，谢谢大家的帮助。

搜索了下，有很多解决方式，当然没有通用的方法，还是得按实际情况来决定到底用那种。

问题有了新的进展，我这里的 id 是主键，自增。索引类型默认就是 PRIMARY

```[25260 ms]*/ SELECT * FROM `user` ORDER BY `id` DESC LIMIT 12 OFFSET 251676;```

```
EXPLAIN SELECT * FROM `user` WHERE `create_at` BETWEEN 1420065013 AND 1451687419 ORDER BY `id` ASC LIMIT 0,12

id: 1
select_type: SIMPLE
table: user
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 12
Extra: Using where

```

现在就算没有 OFFSET 查询速度依然很慢，这个大概需要 17 秒左右。

当我使用 create_at 作为 ORDER BY 的条件的时候速度很快了， 2 秒。

为什么呢？ id 是主键，自增。索引类型默认就是 PRIMARY

为什么会这样呢？当然这些问题手册里应该有说。偷个懒还请有经验的朋友直接点醒下。谢谢。

Supplement 2 · Oct 27, 2016

为什么这个就很快呢？基本上就 0.2 秒左右
```
EXPLAIN SELECT * FROM `user` WHERE `create_at` BETWEEN 1443653577 AND 1451515975 ORDER BY `create_at` DESC LIMIT 12 OFFSET 12 \G;

id: 1
select_type: SIMPLE
table: user
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 251719
Extra: Using where; Using filesort
```

MySQL

select

offset

desc

17 replies • 2016-10-27 14:06:03 +08:00

mahone3297

Oct 26, 2016

这个问题，往上一大堆一大堆的，专门讲这个问题。
搜 mysql 分页

iyaozhen

Oct 26, 2016 via Android

MySQL 分页是有这个问题。貌似有个解决方案是 id between (xxx, yyy)

luckyduck

Oct 26, 2016

大数据量的分页就不能用 OFFSET 了，如果你只是上一页，下一页这种简单分页方式的话。
可以第一页用
SELECT * FROM `user` ORDER BY `id` DESC LIMIT 12 ；
然后下一页用
SELECT * FROM `user` WHERE `id` > 上一页最后一条记录的 ID ORDER BY `id` DESC LIMIT 12 ；

kn007

Oct 26, 2016

目前用着 3 楼的方法， 2 楼的记得之前也有用，但是数据多，还是会有慢查询

shimanooo

Oct 26, 2016

3 楼+1

另外加了什么索引， explain 看看？

chaegumi

Oct 26, 2016

mysql limit 分页很大时的解决方案

```sql

select AA.*, S1.site_name from analytics AA inner join(
select A.id from analytics A left join sites S on A.site_id=S.id order by A.id desc limit 363460,10
) as my_results USING(id) left join sites S1 on AA.site_id=S1.id

```

yidinghe

PRO

Oct 26, 2016 via Android

好大的 offset ，这意味着即使有索引也必须按着索引扫过去。索引是用来直接定位记录的，扫索引效率照样低。

cjyang1128

Oct 26, 2016

直接搜索 mysql 分页查询优化吧

chenset

Oct 26, 2016

我来粗略解析下楼主 SQL 耗时的原因.

1. SELECT * FROM `user` ORDER BY `id` DESC
MySQL 先全表扫描, ID 索引排序, 但并未返回给客户端.

2. LIMIT 12 OFFSET 251676;
丢弃掉前面的 251676 条, 返回后面的 12 条.

就是因为引擎宏观上分两次执行了 SQL, 第 1 次时扫描了大量行而耗时, 客户端却只接收到 12 行数据.

自己个人理解求大神教做人.

enenaaa

Oct 27, 2016

@chenset id 是索引时应该不会进入全表扫描。我猜测偏移数较大时慢的原因是排序计算造成的。
取前 10 名只是多项式复杂度。
到 251676 可能已经接近于全排。

enenaaa

Oct 27, 2016

这也解释了为什么 3 楼的做法会有效

pubby

Oct 27, 2016 via Android

只给 100 页，其它用搜索解决。

wangdu2012

Oct 27, 2016 via iPhone

offset 这么大。全表了吧。

chenset

Oct 27, 2016

@enenaaa 哦, 不是全表扫, 而是扫了 251676 + 12 行.

dangyuluo

Oct 27, 2016

我现在是用的 Elasticsearch 解决。专业的事情就要交给专业的工具去做。

fuxkcsdn

Oct 27, 2016

3 楼的方法+1

但感觉再慢也不应该 LZ 说的那么慢，应该是表的字段太多，或者有很多 text 字段吧？？
我测试 2,297,101 条记录， 12 个字段
int, int, tinyint,varchar(32),char(32),varchar(512),bigint,bigint,tinyint,varchar(256),varchar(512),tinyint
即使用 LZ 的 SQL 查询也才 0.34 sec ， 3 楼的方法 0.10 sec

同服务器上，另一张表（ 363,175 条记录）， 24 个字段
mediumint,char(32),char(2),char(2),char(5),mediumint,tinyint,timestamp,timestamp,tinyint,mediumint,bigint,bigint,tinyint,tinyint,tinyint,smallint,mediumint,varchar(512),varchar(512),varchar(64),varchar(256),varchar(32),varchar(32)
同样用 LZ 的 SQL 查询得 2.51 sec ， 3 楼的方法 0.09 sec

同服务器上，领一张表（ 363,175 条记录），是上一张表的附属表， 17 个字段
mediumint,varchar(8),varchar(32),varchar(32),varchar(256),varchar(256),varchar(256),varchar(256),text,text,text,text,text,text,text,text,blob
同样用 LZ 的 SQL 查询花了 1 min 21.13 sec ， 3 楼的方法 0.21 sec

Magic347

Oct 27, 2016

3 楼的解法只能从一定程度上缓解题主的痛点，但不是这一类问题的根本解决方案。
这类问题其实是可以归结为同一类问题的，那就是如何高效的从海量关系型数据库中实时查询得到按需排序分页后的数据。以下的个人见解不提供完整的解决方案，仅供抛砖引玉，楼主可以之后慢慢探索优化方案。

众所周知，类似 MySQL 这一类数据库引擎，在面对海量数据（量级千万以上）执行过滤排序时，效率是很难令人满意的。一般的做法往往是，预先将候选数据集筛选出来加载至内存，然后再在内存中使用一定的技巧进行排序和分页，最终输出所需展示的分页数据。

那么，这里就涉及到至少 2 个问题：
1. 如何高效的从数据库引擎中筛选候选数据集。
对于单表的情况，执行简单查询即可。但是对于分表数据，则需要考虑跨表查询问题，这里就涉及到目标数据表的定位问题。
2. 如何在内存中对候选数据集执行高效的内排序和分页操作。
这里也自然不会简单就把候选数据做一个内存排序，可以借鉴桶排的思想，按照排序字段进行区间划分，然后定位到所需页面数据所位于的 k 个桶，最后仅需排序这 k 个桶中的数据即可。这里涉及到的一些计算和排序的细节往往都有很大的优化空间。

以上。