Mysql 在带有偏移量的查询中不使用索引 [英] Mysql doesn't use index in queries with offsets

问题描述

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

创建表`tbl`(`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY，`name` NCHAR(50) 非空……其他领域……) 引擎=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.

解决方案

考虑以下，我将 2.2M 行强制放入一个表中

架构

创建表tbl( id int auto_increment 主键，东西不为空)引擎=MyISAM;插入 tbl(thing) 值 (7),(7),(7),(7),(7),(7),(7),(7),(7),(7),(7),(7)、(7)、(7)、(7)、(7)、(7)；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；insert into tbl(thing) 从 tbl 中选择事物；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；插入到 tbl(thing) 从 tbl 中选择东西；

计数

select count(*) as theCount,max(id) as theMax,min(id) as thMin from tbl;+------------+---------+-------+|伯爵|最大|thMin |+------------+---------+-------+|2228224 |2228224 |1 |+------------+---------+-------+

查询 A(您的，使用派生表)

解释选择 *从`tbl`加入(SELECT id FROM `tbl` ORDER BY id LIMIT 1000000, 100) as b在`b`.`id` = `tbl`.`id`;+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+|身份证 |选择类型 |表|类型 |可能的密钥|关键|密钥长度 |参考 |行 |额外 |+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+|1 |主要 |<派生2>|所有 |空 |空 |空 |空 |1000100 |空 ||1 |主要 |表 |eq_ref |主要 |主要 |4 |b.id |1 |空 ||2 |衍生 |表 |索引 |空 |主要 |4 |空 |2228224 |使用索引 |+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+

查询 B(不是派生表)

解释选择 t1.*从 tbl t1加入 tbl t2在 t2.id = t1.id其中 t2.id>1000000限制 100+----+-------------+-------+--------+--------------+---------+---------+------------+---------+---------------------------+|身份证 |选择类型 |表|类型 |可能的密钥|关键|密钥长度 |参考 |行 |额外 |+----+-------------+-------+--------+--------------+---------+----------+------------+---------+---------------------------+|1 |简单 |t2 |范围|主要 |主要 |4 |空 |1195836 |使用哪里；使用索引 ||1 |简单 |t1 |eq_ref |主要 |主要 |4 |so_gibberish.t2.id |1 |空 |+----+-------------+-------+--------+--------------+---------+----------+------------+---------+---------------------------+

对于那些不熟悉使用 Explain 的人，请参阅我在此处写的一篇文章>

I have a simple database with large amount of rows.

CREATE TABLE `tbl`(
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` NCHAR(50) NOT NULL
    ... other fields ...
  ) Engine=innodb;

I want to make paging imitation. And I know that classic offset is slow operation. Thats why I tried a trick from this website

My version of query is

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

but the trick wasn't effective. The query works extremely slow, because mysql still reads all 1000000 lines.

And even separated inner query works witn primary keys only, but doesn't use a b-tree.

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

So, why mysql engine doesn't use primary index when this query is totally covered with it?

Also I can't use where id > SOME_NUMBER condition because my table is very sparsed, and i don't know boundary ids of random page that was requested by user.

解决方案

Consider the following, where I force 2.2M rows into a table

Schema

create table tbl
(   id int auto_increment primary key,
    thing int not null
)engine=MyISAM;

insert tbl(thing) values (7),(7),(7),(7),(7),(7),(7),(7),(7),(7),(7),(7),(7),(7),(7),(7),(7);

insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;
insert into tbl(thing) select thing from tbl;

The Counts

select count(*) as theCount,max(id) as theMax,min(id) as thMin from tbl;
+----------+---------+-------+
| theCount | theMax  | thMin |
+----------+---------+-------+
|  2228224 | 2228224 |     1 |
+----------+---------+-------+

Query A (yours, uses a derived table)

explain 
SELECT *  
FROM `tbl` 
JOIN 
(SELECT id FROM `tbl` ORDER BY id LIMIT 1000000, 100) as b 
on `b`.`id` = `tbl`.`id`;
+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL | 1000100 | NULL        |
|  1 | PRIMARY     | tbl        | eq_ref | PRIMARY       | PRIMARY | 4       | b.id |       1 | NULL        |
|  2 | DERIVED     | tbl        | index  | NULL          | PRIMARY | 4       | NULL | 2228224 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+

Query B (not a derived table)

explain 
SELECT t1.*  
FROM tbl t1 
JOIN tbl t2 
on t2.id = t1.id 
where t2.id>1000000 
limit 100 
+----+-------------+-------+--------+---------------+---------+---------+--------------------+---------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                | rows    | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+---------+--------------------------+
|  1 | SIMPLE      | t2    | range  | PRIMARY       | PRIMARY | 4       | NULL               | 1195836 | Using where; Using index |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | so_gibberish.t2.id |       1 | NULL                     |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+---------+--------------------------+

For those not familiar with using Explain, see a write-up I did here

这篇关于Mysql 在带有偏移量的查询中不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！