更新时间: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 |+------------+---------+-------+
解释选择 *从`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 |使用索引 |+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+
解释选择 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
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;
select count(*) as theCount,max(id) as theMax,min(id) as thMin from tbl;
+----------+---------+-------+
| theCount | theMax | thMin |
+----------+---------+-------+
| 2228224 | 2228224 | 1 |
+----------+---------+-------+
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 |
+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+
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