MySQL的limit和offset最佳化

這兩天在看關於MySQL分頁查詢的最佳化問題，看到網上的不少文章。

 

MySQL的分頁查詢通常都離不開limit幫忙，而其中的關鍵主要圍繞以下兩點：

1. 盡量減小limit的offset，即較小“limit m, n”裡面的“m”。參考文章：http://www.fuchaoqun.com/2009/04/efficient-pagination-using-mysql/

2. 僅先定位offset的id，再查需要的limit資料。參考文章：http://www.sqlclub.cn/Optimi/2009-04/2626.htm

 

參考的這篇文章中，就是通過定位當前頁的起止id，或者目標頁的起止id，來先定位offset。但是也有一定的局限性，比如，當幾次翻頁的過程中，如果增加或者刪除了資料，那麼查詢也許就不是最“即時”的了。不過通過第二點，我也想到一點，就是先尋找需要的id，再根據id“按圖索驥”，查得需要的記錄。畢竟通常id都是索引或者主鍵，查詢起來肯定效率高，而且僅查詢id一個欄位，應該更加快速。尤其是在查詢的時候，需要重新排序的時候（當然是根據索引排序，否則慢死了）。

 

無意中翻看Baron Schwartz寫的《高效能MySQL（第二版）》，其中4.5.5章節講了如何最佳化LIMIT和OFFSET，也證實了這種方法。

 

我自己實驗了幾個例子：

 

例子1：

 

表t_attorney結構如下，具有30000行資料

 

mysql> desc t_applicant;

+---------------+--------------+------+-----+---------+----------------+

| Field         | Type         | Null | Key | Default | Extra          |

+---------------+--------------+------+-----+---------+----------------+

| id            | int(11)      | NO   | PRI | NULL    | auto_increment |

| internalNo    | varchar(45)  | YES  |     | NULL    |                |

| firstName     | varchar(45)  | YES  |     | NULL    |                |

| lastName      | varchar(45)  | YES  |     | NULL    |                |

| email         | varchar(100) | YES  |     | NULL    |                |

| address       | varchar(500) | YES  |     | NULL    |                |

| telNo         | varchar(60)  | YES  |     | NULL    |                |

| certificateNo | varchar(60)  | YES  |     | NULL    |                |

| nationality   | varchar(60)  | YES  |     | NULL    |                |

| idCardNo      | varchar(45)  | YES  |     | NULL    |                |

+---------------+--------------+------+-----+---------+----------------+

10 rows in set (0.00 sec)

 

 

兩種查詢方式：

 

mysql> select a.internalNo, a.firstName, a.lastName, a.email, a.address, a.telNo, a.certificateNo, a.nationality, a.idCardNo from t_
courier new,courier;">applicant

a inner join (select id from t_

applicant

order by id desc limit 18000,15) as a2 using(id)

;

 

15 rows in set (0.03 sec)

 

 

mysql> select a.internalNo, a.firstName, a.lastName, a.email, a.address, a.telNo, a.certificateNo, a.nationality, a.idCardNo from t_applicant a order by id desc limit 18000,15;

 

15 rows in set (0.08 sec)

 

兩種查詢方式，第一種查詢平均耗時0.03秒左右；而第二種平均耗時0.08秒左右。

 

 

 

例子2：

 

表t_patentCase結構如下，具有90000行資料。

 

mysql> desc t_patentCase;

+-------------------+-------------+------+-----+---------+----------------+

| Field             | Type        | Null | Key | Default | Extra          |

+-------------------+-------------+------+-----+---------+----------------+

| id                | int(11)     | NO   | PRI | NULL    | auto_increment |

| internalNo        | varchar(45) | NO   | MUL | NULL    |                |

| name              | varchar(45) | YES  |     | NULL    |                |

| appliedDate       | datetime    | YES  |     | NULL    |                |

| createdDate       | datetime    | YES  |     | NULL    |                |

| patentCaseType    | int(11)     | YES  |     | NULL    |                |

| firstAttorney_id  | int(11)     | YES  | MUL | NULL    |                |

| secondAttorney_id | int(11)     | YES  |     | NULL    |                |

| firstInventor_id  | int(11)     | YES  | MUL | NULL    |                |

| firstApplicant_id | int(11)     | YES  | MUL | NULL    |                |

| firstContact_id   | int(11)     | YES  | MUL | NULL    |                |

| applicationNo     | varchar(45) | YES  |     | NULL    |                |

+-------------------+-------------+------+-----+---------+----------------+

12 rows in set (0.03 sec)

 

 

視圖v_patentCase_summary是t_patentCase左串連另外4張表的結果檢視，結構如下：

 

mysql> desc v_patentCase_summary;

+--------------------------+-------------+------+-----+---------+-------+

| Field                    | Type        | Null | Key | Default | Extra |

+--------------------------+-------------+------+-----+---------+-------+

| id                       | int(11)     | NO   |     | 0       |       |

| internalNo               | varchar(45) | NO   |     | NULL    |       |

| name                     | varchar(45) | YES  |     | NULL    |       |

| patentCaseType           | int(11)     | YES  |     | NULL    |       |

| createdDate              | datetime    | YES  |     | NULL    |       |

| appliedDate              | datetime    | YES  |     | NULL    |       |

| applicationNo            | varchar(45) | YES  |     | NULL    |       |

| firstAttorney_id         | int(11)     | YES  |     | NULL    |       |

| firstAttorney_firstName  | varchar(45) | YES  |     | NULL    |       |

| firstAttorney_lastName   | varchar(45) | YES  |     | NULL    |       |

| secondAttorney_id        | int(11)     | YES  |     | NULL    |       |

| secondAttorney_firstName | varchar(45) | YES  |     | NULL    |       |

| secondAttorney_lastName  | varchar(45) | YES  |     | NULL    |       |

| firstApplicant_id        | int(11)     | YES  |     | NULL    |       |

| firstApplicant_firstName | varchar(45) | YES  |     | NULL    |       |

| firstApplicant_lastName  | varchar(45) | YES  |     | NULL    |       |

| firstInventor_id         | int(11)     | YES  |     | NULL    |       |

| firstInventor_firstName  | varchar(45) | YES  |     | NULL    |       |

| firstInventor_lastName   | varchar(45) | YES  |     | NULL    |       |

| firstContact_id          | int(11)     | YES  |     | NULL    |       |

| firstContact_firstName   | varchar(45) | YES  |     | NULL    |       |

| firstContact_lastName    | varchar(45) | YES  |     | NULL    |       |

+--------------------------+-------------+------+-----+---------+-------+

22 rows in set (0.00 sec)

 

 

兩種查詢方式：

 

mysql> select v.id, v.internalNo, v.name, v.patentCaseType, v.createdDate, v.appliedDate, v.applicationNo, v.firstAttorney_id, v.firstAttorney_firstName, v.firstAttorney_lastName, v.secondAttorney_id, v.secondAttorney_firstName, v.secondAttorney_lastName, v.firstApplicant_id, v.firstApplicant_firstName, v.firstApplicant_lastName, v.firstInventor_id, v.firstInventor_firstName, v.firstInventor_lastName, v.firstContact_id, v.firstContact_firstName, v.firstContact_lastName from v_patentCase_summary v right join (select id from t_patentCase order by id desc limit 60000,15) as p on v.id=p.id

;

 

15 rows in set (1.45 sec)

 

 

mysql> select v.id, v.internalNo, v.name, v.patentCaseType, v.createdDate, v.appliedDate, v.applicationNo, v.firstAttorney_id, v.firstAttorney_firstName, v.firstAttorney_lastName, v.secondAttorney_id, v.secondAttorney_firstName, v.secondAttorney_lastName, v.firstApplicant_id, v.firstApplicant_firstName, v.firstApplicant_lastName, v.firstInventor_id, v.firstInventor_firstName, v.firstInventor_lastName, v.firstContact_id, v.firstContact_firstName, v.firstContact_lastName from v_patentCase_summary v order by id desc limit 60000,15;

 

? rows in set (??? sec)

 

 

兩種查詢方式，第一種查詢平均耗時1.50秒左右；而第二種過了數分鐘尚無反應。

 

 

小結：

通過以上兩個例子可以看出這種方法的效率改善，當然最佳化無止境，根據以後的具體應用要求，還可以繼續最佳化offset的定位問題。