MySQL高效分頁與分頁最佳化分析

在Percona Performance Conference 2009大會上來自yahoo的Surat Singh Bhati (surat@yahoo-inc.com) 和 Rick James (rjames@yahoo-inc.com)給大家分享了MySQL高效分頁的經驗。

一、概述

• 常見分頁方式
• schema設計和常見的分頁方式(位移)
• 避免分頁位移過大的技巧
• 效能對比
• 重點

二、常見分頁方式

三.前提

大記錄表要高效分頁

1. WHERE條件使用索引完成
2. WHERE條件和排序能夠使用同個索引完成
3.  

基礎知識

• 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

 

 

索引 a_b_c (a, b, c)

 

下面的查詢可以使用索引來解決ORDER部分：

• ORDER BY a
• ORDER BY a,b
• ORDER BY a, b, c
• ORDER BY a DESC, b DESC, c DESC

下面的查詢可以使用索引來解決WHERE和ORDER部分：:

• 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

下面的查詢無法使用索引完成，需額外排序：

• ORDER BY a ASC, b DESC, c DESC /* 混合ASC和DESC */
• WHERE g = const ORDER BY b, c /* 欄位g不是索引一部分 */
• WHERE a = const ORDER BY c /* 沒有使用欄位b */
• WHERE a = const ORDER BY a, d /* 欄位d不是索引的一部分 */

 

四、Schema 設計

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
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', /* 投票數 */PRIMARY KEY (`id`),KEY `thumbs_up_key` (`thumbs_up`,`id`)) ENGINE=InnoDBmysql> show table status like 'message' GEngine: InnoDBVersion: 10Row_format: CompactRows: 50000040 /* 5千萬 */Avg_row_length: 565Data_length: 28273803264 /* 26 GB */Index_length: 789577728 /* 753 MB */Data_free: 6291456Create_time: 2009-04-20 13:30:45
 
   
  
 
兩個分頁例子：

• 按照time(發布時間)分頁，新發布的在前面
• 按照thumps_up(投票數)分頁，票高的在前面

五、典型的分頁查詢
1.統計記錄數量

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
SELECT count(*) FROM message
 
   
  
 
2. 查詢當前頁

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
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
      
     
 
   
  
 
提示：id 是自動成長的(auto_increment)，通過id就可以取得最新的列表，不需要建立專門記錄時間的欄位。
六、explain

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
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)
 
   
  
 

• 它可以使用索引，並且只要找到需要的結果後就停止掃描.
• LIMIT 10000, 20 需要讀取前10000行，然後擷取後面的20行

六、瓶頸

• 較大的位移(OFFSET)會增加結果集， 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.
• 小比例的低效分頁足夠產生磁碟I/O瓶頸
• 為了顯示“第 21條 至 40條 (共 1000000)，需要統計1000000行

七、簡單的解決方案

1. 不顯示記錄總數，沒使用者在乎這個數字
2. 不讓使用者訪問頁數比較大的記錄，重新導向他們

八、避免count(*)

1. 不顯示總數，讓使用者通過“下一頁”來翻頁
2. 緩衝總數，顯示一個大概值，沒有使用者在乎是324533條還是324633 （譯：測試在乎-_-!!）
3. Display 41 to 80 of Thousands
4. 單獨統計總數，在插入和刪除時遞增/遞減

九、解決位移查詢

1. 更改ui，不提供跳到某頁的按鈕
2. LIMIT N 是高效的, 但不要使用 LIMIT M,N
   • 從WHERE條件裡找到分頁(LIMIT N)的線索
   • Find the desired records using more restricted WHERE using given clue and ORDER BY and LIMIT N without OFFSET)
    

十、尋找線索

譯：last_seen是id。這裡的分頁只有“上一頁”、“下一頁” 按鈕
十一、根據線索解決方案
下一頁:

http://domain.com/forum?page=2&last_seen=100&dir=next

WHERE id< 100 /* last_seen */ORDER BY id DESC LIMIT $page_size /* 沒有位移 */

上一頁:

1. http://domain.com/forum?page=1&last_seen=98&dir=prev

WHERE id > 98 /* last_seen */ORDER BY id ASC LIMIT $page_size /* 沒有位移 */

譯：通過每頁第一條或最後一條記錄的id來做條件式篩選，再配合降序和升序獲得上/下一頁的結果集
十二、根據線索解決方案

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
mysql> explainSELECT * FROM messageWHERE id < '49999961'ORDER BY id DESC LIMIT 20 G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: messagetype: rangepossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: NULLRows: 25000020 /* 忽略這裡 */Extra: Using where1 row in set (0.00 sec)
 
   
  
 
十三、當你排序的欄位不是唯一的，怎麼辦?

代碼如下	複製代碼
99 99 98 第一頁 98 98 98 98 97 第二頁 97 10

我們不能這樣查詢:

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
WHERE thumbs_up< 98ORDER BY thumbs_up DESC /* 結果將返回重複的記錄 */
 
   
  
 
我們可以這樣查詢:

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
WHERE thumbs_up <= 98AND <額外的條件>ORDER BY thumbs_up DESC
 
   
  
 
十四、額外的條件

• 考慮到 thumbs_up 是“主要欄位”，如果我們添加一個“次要欄位”，我們可以使用“主要欄位”和“次要欄位”作為查詢條件
• 其次，我們可以考慮使用id(primary key)作為我們的次要欄位

十五、解決方案
第一頁：

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
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 |+-----------+----+
 
   
  
 
下一頁：

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
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 |
 
   
  
 
十六、最佳化
查詢:

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
SELECT * FROM messageWHERE thumbs_up <= 98AND (id < 13 OR thumbs_up < 98)ORDER BY thumbs_up DESC, id DESCLIMIT 20
 
   
  
 
我們可以這樣寫:

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
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;
 
   
  
 
十七、explain

 
  
  

   

    
 代碼如下
    
複製代碼
 
   
 
   

    
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: m1type: rangepossible_keys: PRIMARY,thumbs_up_keykey: thumbs_up_key /* (thumbs_up,id) */key_len: 4ref: NULLRows: 25000020 /* 忽略這裡 */Extra: Using where; Using index /* Cover 譯：Cover就是說所需要的資料之從索引裡擷取就可以滿足了 */*************************** 2. row ***************************id: 1select_type: SIMPLEtable: m2type: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: forum.m1.idrows: 1Extra:
 
   
  
 
十八、效能提升

十九、輸送量提升
每頁30條記錄，查看第一頁的話，使用 LIMIT OFFSET, N方式，可以達到 600 次查詢/秒，如果使用 LIMIT N (無位移)方式，提升到 3.7k 次查詢/秒
二十、Bonus Point
Product issue with LIMIT M, N

User is reading a page, in the mean time some records may be 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 was reading, one new message posted (it would 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
again

No such issue with news approach
二十一、不足
SEO專家會說:Let bot reach all you pages with fewer number of deep dive

兩個解決方案：
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