为什么不同的主键查询在innodb上存在巨大的速度差异？

Question

我有一个简单的桌子Test

• id，主键；
• id2，指数；
• 与其他50+的各种类型柱；

我知道，如果我使用select id from Test，它将使用二级索引id2，而不是这个职位中所述的主索引(聚集索引)。

如果我强制使用主索引进行查询，为什么在选择不同的列时，结果的时间会有很大差异？

查询1

select id, url from Test order by id limit 1000000, 1，只使用500ms+，下面是说明：

MySQL [x]> explain select id, url from Test order by id limit 1000000, 1;                                                                                                                                                                                        
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | Test      | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000001 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

查询2

select * from Test order by id limit 1000000, 1只使用2000ms+，下面是说明：

MySQL [x]> explain select * from Test order by ID limit 1000000, 1;                                                                                                                                                                                                     
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | Test      | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000001 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

我看不出这两种解释有什么区别。那么，为什么在结果时间方面有如此巨大的差异，因为它们使用相同的聚集索引？

Answer 1

好吧，我终于找到了原因..。这是因为mysql 限制的实现。(对不起，我刚找到这个中文解释，没有英文版本)

在上面的Query1和Query2中，limit所做的事情如下：

1. Mysql查询聚集索引，得到第一行；
2. Mysql将第一行转换为结果；
3. 然后，在将其发送到客户端之前，Mysql发现有一个限制1000000，所以第一行不是正确的答案.
4. Mysql然后转到第二行，并将其转换为结果；
5. 然后，在将其发送到客户端之前，Mysql发现有一个限制1000000，因此第二行不是正确的答案.；
6. 一次又一次，直到找到1000001行，将其转换为结果后，与limit 1000000, 1 clase匹配；
7. 最后，这是正确的答案，并发送给客户；

但是，它总共转换了1000000行。因此，在上面的问题中，在‘所有字段conversion(**select ***)乘1000000行’和‘一个/两个字段conversion(**select id/url**)乘以1000000行’之间的成本。毫无疑问，前者比后者慢得多。

不知道为什么mysql limit的行为如此笨拙，但它只是.

Answer 2

对于以下查询：

select id, url from t order by id limit 1000000, 1

MySQL似乎读取按id排序的1,000,000行，而不是跳过它们。

我建议将查询改为：

select * from t where id = (select id from t order by id limit 1000000, 1)

当限制放在子查询中时，MySQL似乎在跳过1,000,000行方面做得更好。

Answer 3

1. 检查sql配置文件，确定更多信息

mysql>显示剖面图

2.mysql的解释功能还不是很强。

3.什么样的场景需要限制10000？