mysql describe 分页_mysql高效分页

When I run an SQL command like the one below, it takes more than 15 seconds:

SELECT *

FROM news

WHERE cat_id = 4

ORDER BY

id DESC

LIMIT 150000, 10

EXPLAIN shows that its using where and the index on (cat_id, id)

LIMIT 20, 10 on the same query only takes several milliseconds.

This task can be reformulated like this: take the last 150,010 rows in id order and return the first 10 of them

It means that though we only need 10 records we still need to count off the first 150,000.

The table has an index which keeps the records ordered. This allows us not to use a filesort. However, the query is still far from being efficient: 15 seconds for 150,000 records (which are already ordered) is way too much.

To better understand the reason behind the low performance let’s look into this picture:

As we already said before, there is an index created on the table. Logically, an index is a part of a table which is not even visible from the SQL side: all queries are issued against the table, not the index, and the optimizer decides whether to use the index or not.

However, physically, an index is a separate object in the database.

An index is a shadow copy of the table which stores some subset of the table’s data:

Index key, i. e. the columns which the index created on.

Table pointer, that is some value that uniquely identifies a row the record reflects. In InnoDB, it is the value of the PRIMARY KEY; in MyISAM, it is an offset in the .MYD file.

The index records are stored in a B-Tree structure which make the ref and range searching on them super fast.

However, the index itself does not contain all table’s data: only the subset we described above. To find the actual table values while preserving order, one needs to join the index and the table. That is for each index record the engine should find the corresponding table record (using the row pointer) and return all non-indexed values from the table itself.

Here’s how it looks:

The process of fetching the table records corresponding to the index records is called row lookup. It is pictured by the curvy arrows connecting the index and the table.

Since the index records and the table records are located far away from each other in the memory and on the disk, the row lookup leads to much more page misses, cache misses and disk seeks that a sequential access and is therefore quite costly. It takes much time to traverse all the connectors on the picture above.

If we do a plain query which returns all the records we of course need to fetch all the records and each row lookup is necessary.

But do we really need it when we use a LIMIT clause with the offset greater than 0?

If we did something like LIMIT 8, 2 we could just skip the first 8 values using the index and the return the remaining two:

We see that this is a much more efficient algorithm that will save us lots of row lookups.

This is called late row lookup: the engine should look a row up only if there is no way to avoid it. If there is a chance that a row will be filtered out by using the indexed fields only, it should be done before the rows are looked up in the actual MySQL table. There is no point in fetching the records out of the table just to discard them.

However, MySQL always does early row lookup: it searches for a row prior to checking values in the index, even the optimizer decided to use the index.

Let’s create a sample table and try to reproduce this behavior:

CREATE TABLE filler (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT

) ENGINE=Memory;

CREATE TABLE t_limit (

id INT NOT NULL,

value VARCHAR(20) NOT NULL,

stuffing VARCHAR(200) NOT NULL,

CONSTRAINT pk_limit_id PRIMARY KEY (id)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELIMITER $$

CREATE PROCEDURE prc_filler(cnt INT)

BEGIN

DECLARE _cnt INT;

SET _cnt = 1;

WHILE _cnt <= cnt DO

INSERT

INTO filler

SELECT _cnt;

SET _cnt = _cnt + 1;

END WHILE;

END

$$

DELIMITER ;

START TRANSACTION;

CALL prc_filler(200000);

COMMIT;

INSERT

INTO t_limit

SELECT id, CONCAT('Value ', id), RPAD('', 200, '*')

FROM filler;

This MyISAM table contains 200,000 records and has a PRIMARY KEY index on id. Each record is filled with 200 bytes of stuffing data.

Here’s the query to select values from 150,001 to 150,010:

SELECT id, value, LENGTH(stuffing) AS len

FROM t_limit

ORDER BY

id

LIMIT 150000, 10

idvaluelen

150001

Value 150001

200

150002

Value 150002

200

150003

Value 150003

200

150004

Value 150004

200

150005

Value 150005

200

150006

Value 150006

200

150007

Value 150007

200

150008

Value 150008

200

150009

Value 150009

200

150010

Value 150010

200

10 rows fetched in 0.0004s (5.9686s)

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra

1

SIMPLE

t_limit

ALL

200000

100.00

Using filesort

select `20091023_late`.`t_limit`.`id` AS `id`,`20091023_late`.`t_limit`.`value` AS `value`,length(`20091023_late`.`t_limit`.`stuffing`) AS `len` from `20091023_late`.`t_limit` order by `20091023_late`.`t_limit`.`id` limit 150000,10

This query works for almost 6 seconds which is way too long.

It, however, uses a filesort which the optimizer considered more efficient than using the index. This would make sense if not for the stuffing field which is too long to be sorted efficiently. In this very case traversing the index would be faster.

Let’s try to force the index:

SELECT id, value, LENGTH(stuffing) AS len

FROM t_limit FORCE INDEX (PRIMARY)

ORDER BY

id

LIMIT 150000, 10

idvaluelen

150001

Value 150001

200

150002

Value 150002

200

150003

Value 150003

200

150004

Value 150004

200

150005

Value 150005

200

150006

Value 150006

200

150007

Value 150007

200

150008

Value 150008

200

150009

Value 150009

200

150010

Value 150010

200

10 rows fetched in 0.0003s (1.2343s)

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra

1

SIMPLE

t_limit

index

PRIMARY

4

150010

133.32

select `20091023_late`.`t_limit`.`id` AS `id`,`20091023_late`.`t_limit`.`value` AS `value`,length(`20091023_late`.`t_limit`.`stuffing`) AS `len` from `20091023_late`.`t_limit` FORCE INDEX (PRIMARY) order by `20091023_late`.`t_limit`.`id` limit 150000,10

Now it is only 1.23 seconds but still too long due to the early row lookups.

We, however, can trick MySQL to use the late row lookups.

We will only select the id in the subquery with an ORDER BY and LIMIT and then join the original table back on id.

This will make each individual row lookup less efficient, since each join will require looking up the index value again. However, this is not much of a deal, and the total number of lookups will be reduced greatly, so overall performance increase is expected:

SELECT l.id, value, LENGTH(stuffing) AS len

FROM (

SELECT id

FROM t_limit

ORDER BY

id

LIMIT 150000, 10

) o

JOIN t_limit l

ON l.id = o.id

ORDER BY

l.id

idvaluelen

150001

Value 150001

200

150002

Value 150002

200

150003

Value 150003

200

150004

Value 150004

200

150005

Value 150005

200

150006

Value 150006

200

150007

Value 150007

200

150008

Value 150008

200

150009

Value 150009

200

150010

Value 150010

200

10 rows fetched in 0.0003s (0.0755s)

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra

1

PRIMARY

ALL

10

100.00

Using temporary; Using filesort

1

PRIMARY

l

eq_ref

PRIMARY

PRIMARY

4

o.id

1

100.00

2

DERIVED

t_limit

index

PRIMARY

4

150010

133.32

Using index

select `20091023_late`.`l`.`id` AS `id`,`20091023_late`.`l`.`value` AS `value`,length(`20091023_late`.`l`.`stuffing`) AS `len` from (select `20091023_late`.`t_limit`.`id` AS `id` from `20091023_late`.`t_limit` order by `20091023_late`.`t_limit`.`id` limit 150000,10) `o` join `20091023_late`.`t_limit` `l` where (`20091023_late`.`l`.`id` = `o`.`id`) order by `20091023_late`.`l`.`id`

This is only 75 ms, or 16 times as fast as the previous query.

Note that we put an additional ORDER BY to the end of the query, since the order is not guaranteed to be preserved after the join. This resulted in an extra filesort in the plan. However, the actual plan used outputs the values already sorted and this filesort, therefore, will require only a single pass over 10 values which is instant.