mysql limit的分頁用法與效能最佳化

mysql教程 limit 的效能問題

有個幾千萬條記錄的表 on mysql 5.0.x，現在要讀出其中幾十萬萬條左右的記錄

常用方法，依次迴圈：
select * from mytable where index_col = xxx limit offset, limit;

經驗：如果沒有blob/text欄位，單行記錄比較小，可以把 limit 設大點，會加快速度
問題：頭幾萬條讀取很快，但是速度呈線性下降，同時 mysql server cpu 99%
速度不可接受。

調用 explain select * from mytable where index_col = xxx limit offset, limit;
顯示 type = all

在 mysql optimization 的文檔寫到"all"的解釋
a full table scan is done for each combination of rows from the previous tables. this is normally not good if the table is the first table not marked const, and usually very bad in all other cases. normally, you can avoid all by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

看樣子對於 all, mysql 就使用比較笨的方法，那就改用 range 方式？

因為 id 是遞增的，也很好修改 sql

select * from mytable where id > offset and id < offset + limit and index_col = xxx

explain 顯示 type = range, 結果速度非常理想，返回結果快了幾十倍。

 

在 mySQL 查詢中使用了很多 limit 關鍵字，這就讓我高度興趣了，因為在我印象中， limit 關鍵字似乎更多被使用 mysql 資料庫教程的程式員用來做查詢分頁（當然這也是一種很好的查詢最佳化），那在這裡舉個例子，假設我們需要一個分頁的查詢 ，oracle中一般來說都是用以下 sql 句子實現：

select * from

( select a1.*, rownum rownum_

from testtable a1

where rownum > 20)

where rownum_ <= 1000

       這個語句就能查詢到 testtable 表中的 20 到 1000 記錄，而且還需要巢狀查詢，效率不會太高，看看 mysql 的實現：

       select * from testtable a1 limit 20,980;

       這樣就能返回 testtable 表中的 21 條到（ 20 ＋ 980 ＝） 1000 條的記錄。

       實現文法確實簡單，但如果要說這裡兩個 sql 語句的效率，那就很難做比較了，因為在 mysql 中 limit 選項有多種不同的解釋方式，不同方式下的速度差異是很大的，因此我們不能從這語句的簡潔程度就說誰的效率高。

       不過對程式員來說，夠簡單就好，因為維護成本低，呵呵。

       下面講講這個 limit 的文法吧：

       select ……. --select 語句的其他參數

[limit {[offset,] row_count | row_count offset offset}]

這裡 offset 是位移量（這個位移量的起始地址是 0 ，而不是 1 ，這點很容易搞錯的）顧名思義就是離開起始點的位置，而 row-count 也是很簡單的，就是返回的記錄的數量限制。

eg. select * from testtable a limit 10,20 where ….

這樣就能使結果返回 10 行以後（包括 10 行自身）的符合 where 條件的 20 條記錄。

那麼如果沒有約束條件就返回 10 到 29 行的記錄。

       那這跟避免全表掃描有什麼關係呢？ 下面是 mysql 手冊對 limit 參數最佳化掃描的一些說明：

在一些情況中，當你使用 limit 選項而不是使用 having 時， mysql 將以不同方式處理查詢。

l          如果你用 limit 只選擇其中一部分行，當 mysql 一般會做完整的表掃描時，但在某些情況下會使用索引（跟 ipart 有關）。

l          如果你將 limit n 與 order by 同時使用，在 mysql 找到了第一個合格記錄後，將結束排序而不是排序整個表。

l          當 limit n 和 distinct 同時使用時， mysql 在找到一個記錄後將停止查詢。

l          某些情況下， group by 能通過順序讀取鍵 ( 或在鍵上做排序 ) 來解決，並然後計算摘要直到索引值改變。在這種情況下， limit n 將不計算任何不必要的 group 。

l          當 mysql 完成發送第 n 行到用戶端，它將放棄餘下的查詢。

l          而 limit 0 選項總是快速返回一個空記錄。這對檢查查詢並且得到結果列的列類型是有用的。

l          暫存資料表的大小使用 limit # 計算需要多少空間來解決查詢。

 

百萬資料模糊尋找大改進！！！！！！ （0.03 sec）

 

select id,name from user where name like '%83%' or key like '%83%' limit 0,25;

分頁

mysql中limit的用法詳解[資料分頁常用]

在我們使用查詢語句的時候，經常要返回前幾條或者中間某幾行資料，這個時候怎麼辦呢？不用擔心，mysql已經為我們提供了這樣一個功能。
select * from table   limit [offset,] rows | rows offset offset

limit 子句可以被用於強制 select 語句返回指定的記錄數。limit 接受一個或兩個數字參數。參數必須是一個整數常量。如果給定兩個參數，第一個參數指定第一個返回記錄行的位移量，第二個參數指定返回記錄行的最大數目。初 始記錄行的位移量是 0(而不是 1)： 為了與 postgresql 相容，mysql 也支援句法： limit # offset #。

mysql> select * from table limit 5,10;  // 檢索記錄行 6-15

//為了檢索從某一個位移量到記錄集的結束所有的記錄行，可以指定第二個參數為 -1：
mysql> select * from table limit 95,-1; // 檢索記錄行 96-last.

//如果只給定一個參數，它表示返回最大的記錄行數目：
mysql> select * from table limit 5;     //檢索前 5 個記錄行

//換句話說，limit n 等價於 limit 0,n。
1. select * from tablename <條件陳述式> limit 100,15

從100條記錄後開始取15條 (實際取取的是第101-115條資料)

2. select * from tablename <條件陳述式> limit 100,-1

從第100條後開始-最後一條的記錄

3. select * from tablename <條件陳述式> limit 15

相當於limit 0,15   .查詢結果取前15條資料