Mysql在带有偏移量的查询中不使用索引。

21 浏览
0 Comments

Mysql在带有偏移量的查询中不使用索引。

我有一个包含大量行的简单数据库。

CREATE TABLE `tbl`(
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` NCHAR(50) NOT NULL
    ... 其他字段 ...
  ) Engine=innodb;

我想要进行分页模拟。我知道传统的偏移操作很慢。这就是为什么我尝试了来自这个网站的技巧。

我的查询版本是

SELECT * FROM `tbl` JOIN (SELECT id FROM `tbl` ORDER BY id LIMIT 1000000, 100) as b on `b`.`id` = `tbl`.`id`;

但这个技巧并不有效。查询非常慢，因为MySQL仍然读取了全部1000000行。

即使是单独的内部查询也只使用了主键，而没有使用B树。

SELECT id FROM `tbl` ORDER BY id LIMIT 1000000, 100

那么，为什么MySQL引擎在这个完全由主键覆盖的查询中没有使用主索引呢？

另外，我无法使用where id > SOME_NUMBER的条件，因为我的表非常稀疏，我不知道用户请求的随机页面的边界ID是什么。

0

1 答案

0 Comments

Mysql doesn't use index in queries with offsets是一个在使用偏移量的查询中Mysql不使用索引的问题。这个问题的出现是因为在偏移量查询中，Mysql需要扫描整个结果集，并且不使用索引来优化查询性能。解决这个问题的方法是通过优化查询语句，使用其他方式来实现偏移量查询。

在给定的示例中，首先创建了一个包含2.2M行的表tbl，并插入了大量的数据。然后通过执行查询语句来验证表的行数、最大值和最小值。接下来，通过两个不同的查询语句来展示这个问题的出现和解决方法。

第一个查询(Query A)使用了派生表来实现偏移量查询。通过执行explain命令来查看查询的执行计划，可以看到派生表的查询结果需要扫描整个结果集，没有使用索引来优化查询。这就是为什么Mysql不使用索引的原因。

第二个查询(Query B)使用了另一种方法来实现偏移量查询，而不是使用派生表。同样通过执行explain命令来查看查询的执行计划，可以看到查询结果使用了索引来优化查询性能。这是因为查询中使用了where条件，并且使用了索引来过滤结果集。

Mysql在偏移量查询中不使用索引是因为需要扫描整个结果集，无法使用索引来优化查询性能。解决这个问题的方法是尽量避免使用偏移量查询，或者通过优化查询语句来实现偏移量查询。例如，可以使用where条件来限制结果集的范围，或者使用其他技术来实现分页功能。

Mysql在偏移量查询中不使用索引的原因是需要扫描整个结果集，解决这个问题的方法是优化查询语句，使用其他方式来实现偏移量查询。

0