MySQL efficient paging and paging Optimization Analysis

In mysql, paging optimization is a common problem. If we use limit for tens of thousands of data records, we can complete paging without any operation and the performance is good, however, for millions of data, we need to effectively optimize the limit to complete efficient paging. Otherwise, the database will be stuck. At Percona Performance Conference 2009, Surat Singh Bhati (surat@yahoo-inc.com) and Rick James (rjames@yahoo-inc.com) from yahoo shared their experiences with efficient paging of MySQL.I. Overview

• Common paging Methods
• Schema Design and common paging methods (offset)
• Tips for avoiding excessive page offset
• Performance Comparison
• Key Points

Ii. Common paging Methods
Iii. Prerequisites
Efficient Paging for large record tables

1. The WHERE condition is completed using the index.
2. The WHERE condition and sorting can be completed using the same index.

Basic knowledge

• 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

Index a_ B _c (a, B, c) the following query can use the index to solve the ORDER part:

• Order by
• Order by a, B
• Order by a, B, c
• Order by a DESC, B DESC, c DESC

The following query can use indexes to solve the WHERE and ORDER parts ::

• 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 order by B, c

The following query cannot be completed using an index and requires additional sorting:

• 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 the Index */
• WHERE a = const order by c/* No field B is used */
• WHERE a = const order by a, d/* field d is not part of the Index */

Iv. Schema Design

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
Copy code
 
   
 
   
 
    
Create table 'message' ('id' int (11) not null AUTO_INCREMENT, 'title' varchar (255) COLLATE utf8_unicode_ci not null, 'user _ id' int (11) not null, 'content' text COLLATE utf8_unicode_ci not null, 'create _ time' int (11) not null, 'thumbs _ up' int (11) not null default '0 ', /* Number of votes */primary key ('id'), KEY 'thumbs _ up_key '('thumbs _ up', 'id ')) ENGINE = InnoDBmysql> show table status like 'message' GEngine: InnoDBVersion: 10Row_format: CompactRows: 50000040/* 5 million */Avg_row_length: 565Data_length: 28273803264/* 26 GB */Index_length: 789577728/* 753 MB */Data_free: 6291456Create_time: 13:30:45
 
   
 
  
 
 
Two paging examples:

• By time (release time ),
• Page by thumps_up (number of votes ).

5. Typical paging Query
1. Count 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: The id is automatically increased (auto_increment). You can use the id to obtain the latest list. You do not need to create a special record time field.
6. explain

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
Copy code
 
   
 
   
 
    
mysql> explain SELECT * FROM messageORDER BY id DESCLIMIT 10000, 20G***************** 1. row **************id: 1select_type: SIMPLEtable: messagetype: indexpossible_keys: NULLkey: PRIMARYkey_len: 4ref: NULLrows: 10020Extra:1 row in set (0.00 sec)
 
   
 
  
 
 

• It can use indexes and stop scanning as long as the desired results are found.
• LIMIT 10000, 20 needs to read the first 10000 rows, and then get the next 20 rows

Vi. Bottlenecks

• A large OFFSET will increase the result set. MySQL has to bring data in memory that is never returned to caller.
• Performance issue is more visible when your have database that can't fit in main memory.
• A small proportion of inefficient paging is sufficient to cause disk I/O bottlenecks
• To display "21st to 40 (1000000 in total) records, you need to count 1000000 rows

7. Simple Solution

1. The total number of records is not displayed. No user cares about this number.
2. Do not allow users to access records with relatively large pages and redirect them

8. Avoid count (*)

1. The total number is not displayed, so that the user can flip the page through the next page
2. The total number of caches. An approximate value is displayed. No user cares about 324533 or 324633 -_-!!)
3. Display 41 to 80 of Thousands
4. Count the total number separately, increasing or decreasing when inserting or deleting

9. Solve the offset Query

1. Modify the ui. Buttons to jump to a page are not provided.
2. Limit n is efficient, but do not use limit m, N
   • Locate the clue of the paging (limit n) from the WHERE Condition
   • Find the desired records using more restricted WHERE using given clue and order by and limit n without OFFSET)

10. search for clues
The value of last_seen is id. Only the "Previous Page" and "next page" buttons are displayed.
11. solutions based on clues
Next page: http://domain.com/forum? Page = 2 & last_seen = 100 & dir = next

WHERE id <100/* last_seen */order by id desc limit $ page_size/* No offset */

Previous Page:

1. Http://domain.com/forum? Page = 1 & last_seen = 98 & dir = prev

WHERE id> 98/* last_seen */order by id asc limit $ page_size/* No offset */

Use the id of the first or last record on each page for conditional filtering, and then use the descending and ascending order to obtain the result set of the previous or next page.
12. solutions based on clues

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
Copy code
 
   
 
   
 
    
Mysql> explainSELECT * FROM messageWHERE id <'000000' order by id desc limit 20G ********************** * ***** 1. row ************************* id: 1select_type: SIMPLEtable: messagetype: rangepossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: NULLRows: 25000020/* ignore here */Extra: Using where1 row in set (0.00 sec)
 
   
 
  
 
 
13. What should I do if the field you sort is not unique?

The Code is as follows:	Copy code
99 99 Page 1 98 9898 98 97 page 2 97 10

We cannot query it like this:

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
Copy code
 
   
 
   
 
    
WHERE thumbs_up <98 order by thumbs_up DESC/* duplicate records will be returned */
 
   
 
  
 
 
We can query it like this:

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
Copy code
 
   
 
   
 
    
WHERE thumbs_up <= 98AND <additional condition> order by thumbs_up DESC
 
   
 
  
 
 
14. Additional Conditions

• Considering thumbs_up is a "main field", if we add a "secondary field", we can use "primary field" and "secondary field" as the query conditions.
• Second, we can consider using id (primary key) as our secondary field

15. Solution
Page 1:

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
Copy code
 
   
 
   
 
    
SELECT thumbs_up, idFROM messageORDER BY thumbs_up DESC, id DESCLIMIT $page_size+-----------+----+| thumbs_up | id |+-----------+----+| 99 | 14 || 99 | 2 || 98 | 18 || 98 | 15 || 98 | 13 |+-----------+----+
 
   
 
  
 
 
Next page:

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
Copy code
 
   
 
   
 
    
SELECT thumbs_up, idFROM messageWHERE thumbs_up <= 98 AND (id < 13 OR thumbs_up< 98)ORDER BY thumbs_up DESC, id DESCLIMIT $page_size+-----------+----+| thumbs_up | id |+-----------+----+| 98 | 10 || 98 | 6 || 97 | 17 |
 
   
 
  
 
 
16. Optimization
Query:

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
Copy code
 
   
 
   
 
    
SELECT * FROM messageWHERE thumbs_up <= 98AND (id < 13 OR thumbs_up < 98)ORDER BY thumbs_up DESC, id DESCLIMIT 20
 
   
 
  
 
 
We can write as follows:

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
Copy code
 
   
 
   
 
    
SELECT m2.* FROM message m1, message m2WHERE m1.id = m2.idAND m1.thumbs_up <= 98AND (m1.id <13 OR m1.thumbs_up< 98)ORDER BY m1.thumbs_up DESC, m1.id DESCLIMIT 20;
 
   
 
  
 
 
17. explain

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
Copy code
 
   
 
   
 
    
* *************************** 1. row ************************* id: 1select_type: SIMPLEtable: m1type: rangepossible_keys: PRIMARY, thumbs_up_keykey: thumbs_up_key/* (thumbs_up, id) */key_len: 4ref: NULLRows: 25000020/* ignore here */Extra: Using where; Using index/* Cover: cover means that the required data can be obtained from the index */*********************** * *** 2. row ************************* id: 1select_type: SIMPLEtable: m2type: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: forum. m1.idrows: 1 Extra:
 
   
 
  
 
 
18. Performance Improvement

19. throughput Improvement
30 records per page. If you view the first page, you can use the limit offset, N method to query 600 times/second. If you use the limit n (no OFFSET) method, up to 3.7 k Queries/second
20. Bonus Point
Product issue with limit m, NUser is reading a page, in the mean time some records may be added
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 was reading, one new message posted (it wocould be 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 on page 5, user has to read it
AgainNo such issue with news approach
21. Deficiency
SEO experts will say: Let bot reach all you pages with fewer number of deep dive Two solutions:
Two Solutions:
• Read extra rows
-Read extra rows in advance and construct links for few previous & next pages
• Use small offset
-Do not read extra rows in advance, just add links for few past & next pages
With required offset & last_seen_id on current page
-Do query using new approach with small offset to display desired page