且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

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

更新时间:2023-02-26 12:43:42

考虑以下,我将 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