这是一个创建于 1890 天前的主题，其中的信息可能已经有所发展或是发生改变。
我们都知道 mysql 二级索引的存的是主键值，所以我们一般通过二级索引查询数据时，需要通过主键值再去查一次。
但是现在有一个问题是：例如 sql 是这样的
SELECT id,name,grade from student where grade = 1;
这个 SQL，grade 是加了索引的
但是根据 grade 索引查询的主键，再去通过聚集索引查的时候是如何查的？
1、查询出所有的主键值，再一次性查出 2、一个主键就去查一次，如果是这种那就需要查询很多次
第 1 条附言 · 2019-09-19 20:04:53 +08:00
我实际的 sql 是这样的
select id from xxx.xxx where type = 12 and status in (11,12,13) limit 300000,500;
select id,user_idfrom xxx.xxx where type = 12 and status in (11,12,13) limit 300000,500;
这个表数据量 7000w+，第一条 sql 查询可能 100ms+，第二条 sql 得将近 1min
我想这是为什么
第 2 条附言 · 2019-09-19 20:59:23 +08:00
使用二级索引查询执行过程
select * from T where k between 3 and 5
-
在 k 索引树上找到 k=3 的记录，取得 ID = 300；
-
再到 ID 索引树查到 ID=300 对应的 R3；
-
在 k 索引树取下一个值 k=5，取得 ID=500；
-
再回到 ID 索引树查到 ID=500 对应的 R4；
-
在 k 索引树取下一个值 k=6，不满足条件，循环结束。
这是我看到的解释，如果二级索引执行过程是这样的，那么慢就可以理解了，但是Mysql这么蠢吗，要一条一条记录去回表？
第 3 条附言 · 2019-09-20 11:06:59 +08:00
想明白了，蛋疼的地方在
Limit 300000,500
limit 这个蛋疼的东西是查询出最终的结果再去除前offset的数据，所以这条sql相当于需要找300500条数据。
- 第一条SQL走索引，因为只查主键，所以一次查询就出来了
- 第二条SQL因为需要回表，回表需要通过主键去匹配300500条数据，这就造成查询需要1min的情况。
这limit这种大列表数据最好的方法是分两条SQL查询。例如：
SELECT id FROM xxx.xxx WHERE type = 12 AND statue in (11,12,13) limit 300000,500;
然后再通过查询出来的主键去in，这样就只需要匹配500条数据，而不是300500条数据了。
SELECT * FROM xxx.xxx WHERE id in (`第一条SQL查询的结果`);
 |
1
yumenawei 2019-09-19 18:00:06 +08:00 via Android
帮顶
|
 |
2
sujin190 2019-09-19 18:07:39 +08:00
感觉理解是不是有偏差，这里应该不是查出所有主键通过主键再去查询数据，正确的意思应该是索引不包含实际数据，只有主键信息，通过主键才能在真实的数据文件中读取出数据，这么设计出于提高性能目的，比如改表结构的时候，数据文件修改了，但是主键不变，此时无需重建索引，其他的比如可变类型修改超出原是长度需要挪动数据位置的时候也无需重建索引
|
 |
3
arrow8899 2019-09-19 18:14:20 +08:00
查出所有的 id，再去回表查询 name 的值，由于 id 唯一索引查询时间是 const，所以批量查和单独查时间差不多；
如果你不需要 name 的值得话，就不需要回表了，grade 索引已经包含了 grade 和 id 的值，直接就可以返回。 |
 |
4
masironen 2019-09-19 18:16:08 +08:00 via Android
我觉得应该是查询出所有的主键值，然后再一次性根据主键去查需要的值，因为如果查到一个主键就去查相应的数据的话，io 次数是要多很多的，在查询速度上快不了，反而可能要比全表查询慢。
以上是我个人的理解，不一定对。 |
 |
5
dackh OP |
 |
7
NizumaEiji 2019-09-19 20:19:44 +08:00
覆盖索引？
|
|
8
dackh OP @NizumaEiji 第一条因为 id 是 primary key 不需要回表，所以很快，第二条 user_id，需要多回表查一次，但是回表一次为什么这么慢
|
|
9
dackh OP @mccreefei type 跟 status 都有单列索引，问题的关键在雨多查一个 user_id 为什么回表查一次慢这么多
|
 |
10
mccreefei 2019-09-19 20:53:24 +08:00
@dackh #9 type 和 status 两张二级索引表根据索引值查询很快，是因为要查询的索引值是连续的，查询到的数据也是分布在连续的页上(连续的页之间有指针可快速访问)。但是得到的主键值却不连续，基于你的数据量有 7000w 之多，那么回表查询的数据页跨度也会很大，因此两者查询速度相差大。
|
|
11
NizumaEiji 2019-09-19 20:56:04 +08:00
@dackh #8
你分别 explain 一下看看吧 |
 |
12
aliipay 2019-09-19 20:57:59 +08:00 via Android
没看太明白，我猜问题原因是 offset 太大
|
|
14
dackh OP @NizumaEiji explain 出来的结果一模一样的
|
 |
16
DonaldY 2019-09-19 21:24:17 +08:00
`select * from T where k between 3 and 5`
在 k 索引表下，3 5 是连续的，可能在同一个分页。 但对应到主键索引表，可能就不在同一个分页，要多次查找分页 |
 |
17
woyixinyiyi 2019-09-19 22:43:57 +08:00
@dackh 楼主 贴出你的 explain 里面的信息
第一条 sql 查询可能 100ms+，这个是因为二级索引会保护聚簇索引的值，这个时候是不需要回表的。 第二条 sql 得将近 1min，这个是因为查询的字段的值 user_id，不仅仅只有聚簇索引，包括其他的字段，同时这个字段也不是覆盖索引的字段，所以这个过程肯定是需要根据聚簇索引回表来查询具体的行记录的。 mysql 毕竟只是一个程序，没那么智能，只能尽可能覆盖各种场景遇到的一些问题。 比如你遇到的问题 根据二级索引查询可以获取一个聚簇索引集合（主键集合一般为 id ）， 楼主可以用关键字 搜索下 read_rnd_buffer 和 MRR。 如果开启 MRR，大概思路是，根据你二级索引获取到的聚簇索引进行排序，针对排完的聚簇索引去回表查询， 因为聚簇索引是在数据页中是有序的，这样让读取数据是顺序读，理论上是可以减少磁盘数据页的访问。 |
 |
18
akira 2019-09-20 00:02:27 +08:00
那这样写呢 ，这样就能保证他是一次过去查了吧
select id , user_id from xxxx where id in (select id from xxx.xxx where type = 12 and status in (11,12,13) limit 300000,500) |
 |
19
CRVV 2019-09-20 00:21:19 +08:00
select * from T where k between 3 and 5
用 k 的索引找到 id 再用 id 来查整条记录 我觉得这么查没有问题，请问不蠢的查询方式是什么？ |
|
20
aliipay 2019-09-20 00:31:24 +08:00
|
 |
21
chibupang 2019-09-20 01:35:47 +08:00 via Android
索引都没有命中吧？最左匹配原则了解一下
|
 |
22
Soar360 2019-09-20 01:53:33 +08:00 via iPhone
覆盖索引？
|
 |
23
GTim 2019-09-20 09:07:31 +08:00
因为，使不使用索引是看天吃饭的.....
``` If you select only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan ``` |
 |
24
mineqiqi 2019-09-20 09:30:55 +08:00
7000w 单表数据还不分表。。。再怎么优化也不会很快
|
 |
25
noahsophie 2019-09-20 11:46:33 +08:00
学习了~
|
 |
26
optional 2019-09-20 12:23:56 +08:00
把分页从 offset 分页改成主键分页，可以从 100ms->10ms
|
 |
28
jojojo 2019-09-22 17:28:50 +08:00 via Android
延迟关联
|
Select Language