At the Percona Performance Conference 2009 conference from Yahoo Surat Singh Bhati (surat@yahoo-inc.com) and Rick James ( rjames@yahoo-inc.com) to share the MySQL efficient paging experience.
Second, common paging way
Large record table to be efficient paging
-
Where conditions are completed using the index
-
Where conditions and sorting can be done using the same index
-
-
Http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
-
Http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html
-
Http://dev.mysql.com/doc/refman/5.1/en/limit-optimization.html
The following query can use an index to resolve the order section:
The following query can use an index to resolve the where and order sections:
-
WHERE A = Const ORDER by B, C
-
WHERE a = const and b = Const ORDER BY C
-
WHERE A = Const ORDER by B, C
-
WHERE a = const and B > const by B, c
The following query cannot be completed with an index and requires an extra sort:
-
Order by a ASC, B DESC, C DESC/* Mixed ASC and DESC * *
-
WHERE g = Const ORDER BY B, c/* field G is not part of index * *
-
WHERE A = const ORDER BY c/* Does not use field b * *
-
WHERE A = Const ORDER by a, D/* field D is not part of the index * *
| The code is as follows |
Copy Code |
CREATE TABLE ' message ' (
' id ' int (one) not NULL auto_increment,
' title ' varchar (255) COLLATE Utf8_unicode_ci not NU LL,
' user_id ' int (one) not null,
' content ' text COLLATE utf8_unicode_ci not null,
' create_time ' int (one) not NU LL,
' thumbs_up ' int (one) not NULL DEFAULT ' 0 ',/* voting number
/PRIMARY key (' id '),
key ' Thumbs_up_key ' (' thumbs_up ', ' Id ')
) Engine=innodb
mysql> Show table status like ' message ' G
engine:innodb
version:10
Row_format:compact
rows:50000040/* 50 million/
avg_row_length:565
data_length:28273803264 * GB */
index_length:789577728/* 753 MB * *
data_free:6291456
create_time:2009-04-20 13:30:45
|
Two pagination examples:
-
Page by Time (published), New posted in front
-
According to THUMPS_UP (number of votes) paging, the ticket high in the front
V. Typical paging query
1. Number of statistical records
| The code is as follows |
Copy Code |
SELECT Count (*) from message |
2. Query the current page
| The code is as follows |
Copy Code |
SELECT * FROM message ORDER by ID DESC LIMIT 0, 20
-
Http://domain.com/message?page=1
Order by ID DESC LIMIT 0, 20
-
http://domain.com/message?page=2
Order BY ID DESC LIMIT 20, 20
-
Http://domain.com/message?page=3
Order by ID DESC LIMIT 40, 20
|
Tip: IDs are automatically grown (auto_increment), you can get the latest list by ID, and you don't need to create a field that records time specifically.
VI. Explain
| The code is as follows |
Copy Code |
Mysql> explain SELECT * from message
ORDER by ID DESC
LIMIT 10000, 20G
***************** 1. Row ********
id:1
select_type:simple
table:message
type:index
possible_keys:null
key: PRIMARY
key_len:4
ref:null
rows:10020
Extra:
1 row in Set (0.00 sec) |
-
It can use the index and stop the scan as soon as the desired results are found.
-
LIMIT 10000, 20 need to read the first 10000 lines, then get the 20 rows behind
-
The larger offset (offset) Increases the result set, MySQL has to bring the data in memory this is never returned to caller.
-
Performance issue is more visible when your have database, can ' t fit in main memory.
-
A small percentage of inefficient paging is sufficient to generate disk I/O bottlenecks
-
To display "21st to 40 (total 1000000), you need to count 1000000 rows
Seven, simple way to solve
-
Do not show the total number of records, no users care about this number
-
Do not allow users to access pages that are larger than the record, redirect them
-
Don't show totals, let the user page through "next"
-
Total cache, display a ballpark value, no user care is 324,533 or 324633 (translate: Test cares about-_-!! ）
-
Display thousands of
-
Total number of individual statistics, increment/decrement on insert and delete
Ix. resolution of the offset query
-
Change the UI and do not provide a button to jump to a page
-
LIMIT N is efficient, but do not use LIMIT m,n
Last_seen is the ID. The pagination here is only the previous page, next page button
Xi. according to the clue solution
Next page:
Http://domain.com/forum?page=2&last_seen=100&dir=next
WHERE id</* Last_seen/ORDER by
ID DESC LIMIT $page _size/* No offsets * *
-
Http://domain.com/forum?page=1&last_seen=98&dir=prev
WHERE ID > \ * last_seen/ORDER by
ID ASC LIMIT $page _size/* No offset/
To filter by the ID of the first or last record on each page, and then get the result set on/next with descending and ascending order
12, according to the clues to solve the solution
| code is as follows |
copy code |
mysql> explain SELECT * from message WHERE ID < ' 49999961 ' ORDER by ID DESC LIMIT G 1. Row *************************** id:1 select_type:simple table:message type:range possible_keys:primary key:primary k
Ey_len:4 ref:null rows:25000020/* Ignore here * * extra:using where 1 row in Set (0.00 sec) |
13, What if the field you are sorting is not unique?
| The code is as follows |
Copy Code |
|
99
99
98 first Page
98
98
98
98
97 second Page
97
10
|
| code is as follows |
copy code |
WHERE thumbs_up< ORDER BY thumbs_up DESC/* Results will return duplicate records */ |
We can query this way:
| The code is as follows |
Copy Code |
WHERE thumbs_up <=
and < additional conditions > ORDER by
thumbs_up DESC |
14. Additional Conditions
-
Considering that THUMBS_UP is a "primary field", if we add a "secondary field", we can use the primary and secondary fields as the criteria for the query
-
Second, we can consider using the ID (primary key) as our secondary field
XV. Solutions
First page:
| code is as follows |
copy code |
SELECT thumbs_up, ID from Message order by THUMBS_UP DESC, id DESC LIMIT $page _size +-----------+-- --+
| thumbs_up |
ID | +-----------+----+
| 99 | 14 | | 99 | 2 | | 98 | 18 | | 98 | 15 | | 98 |
13 | +-----------+----+ |
next page:
| code is as follows |
copy code |
SELECT thumbs_up, ID from message WHERE thumbs_up <= and (ID < OR thumbs_up<) ORD ER by thumbs_up DESC, id DESC LIMIT $page _size +-----------+----+ | thumbs_up |
ID | +-----------+----+
| 98 | 10 | | 98 | 6 | | 97 | | |
16, Optimizing Query:
| code is as follows |
copy code |
SELECT * from message WHERE thumbs_up <= and (ID < OR thumbs_up <) Order by thumb S_UP DESC, id DESC LIMIT |
We can write this:
| code is as follows |
copy code |
SELECT m2.* from message M1, message m2 WHERE m1.id = m2.id and M1.thumbs_up <= and (M1.id &L t;13 OR m1.thumbs_up<) Order by M1.thumbs_up DESC, m1.id DESC; |
17, explain
| code is as follows |
copy code |
*************************** 1. Row *************************** id:1 select_type:simple table: M1 Type:range Possible_keys:primary,thumbs_up_key Key:thumbs_up_key * * (thumbs_up,id)/Key_len:4 ref:null rows:250 00020/* Ignore here * * extra:using where; Using Index/* Cover: Cover means that the data needed to be obtained from the index can be satisfied with the */*************************** 2. Row *************************** id:1 select_type:simple table:m2 type:eq_ref possible_keys:primary key:primary key_l En:4 ref:forum.m1.id rows:1 Extra: |
18, performance promotion
19. Throughput improvement
30 records per page, look at the first page, use LIMIT OFFSET, N mode, can reach 600 queries/sec, if using LIMIT N (no offset) way, to 3.7k query/sec
20, Bonus Point
Product issue with LIMIT M, N
User is reading a page, in the mean time some records may being added to
Previous page.
Due to Insert/delete pages records are going to move Forward/backward
As rolling window:
–user is reading messages on 4th page
–while He is reading, one new message posted (it would is there on page
One), all pages are going to move one message to next page.
–user clicks on Page 5
–one message from Page got pushed forward in page 5, user has to read it
Again
No such issue with news approach
21, insufficient
SEO experts will say: let bot reach all your pages with fewer number of deep dive
Two solutions:
Two Solutions:
read extra rows
–read extra rows in advance and constr UCT links for few previous & next pages
use small offset
–do not read extra rows in advance, just add l Inks for few past & next pages
with required offset & last_seen_id on current page
–do query using NE W approach with small offset to display desired page
