MYSQL Optimizing LIMIT Queries

標籤：

      如果要指定查詢的資料行數，在查詢語句中使用limit子句，而不是擷取所有資料行結果集，然後去掉沒用的資料。

      MYSQL有時會對沒有having子句，帶有limit關鍵字的查詢進行最佳化：

      1：如果用limit子句選擇很少的行資料集，mysql會利用索引來代替全表掃描；

      2：如果組合使用limit 和order by 查詢，mysql會在滿足limit數量限制時停止sort,而不是對所有資料sort.如果利用index來進行排序，過程很快，但如果走的是filesort,所有 匹配該查詢的結果集（except limit）都會被擷取，在滿足limit子句資料行找到之前，大部分（全部）資料都會被sort（即找到所有滿足條件的資料行，然後排序找到滿足limit的前幾條資料，然後再停止）。

      3：一個order by查詢帶有和不帶有limit的返回集可能以不同的順序，下面有介紹：

      4：如果組合使用limit和distinct關鍵字，mysql在找到row_count的唯一資料行時，立刻停止。

      5：一些情況下，group by(order by)可以通過讀取key的順序，此時，limit row_count可以限制不必要計算的group by值。

      6： 當用戶端收到指定行數的時候，會中斷查詢，除非使用了SQL_CALC_FOUND_ROWS。

      7： limit 0直接返回空集，可以用來檢查查詢是否合法。

      8： 當服務使用暫存資料表，會使用limit子句來計算需要多少空間。

 

      當在order by列中具有相同值的很多行時，mysql server會不確定的以任何順序返回這些行資料，換句話說，排序結果的順序對非order by列來說是不確定的。

     

mysql> SELECT * FROM ratings ORDER BY category;+----+----------+--------+| id | category | rating |+----+----------+--------+|  1 |        1 |    4.5 ||  5 |        1 |    3.2 ||  3 |        2 |    3.7 ||  4 |        2 |    3.5 ||  6 |        2 |    3.5 ||  2 |        3 |    5.0 ||  7 |        3 |    2.7 |+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;+----+----------+--------+| id | category | rating |+----+----------+--------+|  1 |        1 |    4.5 ||  5 |        1 |    3.2 ||  4 |        2 |    3.5 ||  3 |        2 |    3.7 ||  6 |        2 |    3.5 |+----+----------+--------+

   如果要確定不適用limit情況下返回集的順序（最好加上一個唯一列），如下：如果id 列為unique，可以這麼使用：

mysql> SELECT * FROM ratings ORDER BY category, id;+----+----------+--------+| id | category | rating |+----+----------+--------+|  1 |        1 |    4.5 ||  5 |        1 |    3.2 ||  3 |        2 |    3.7 ||  4 |        2 |    3.5 ||  6 |        2 |    3.5 ||  2 |        3 |    5.0 ||  7 |        3 |    2.7 |+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;+----+----------+--------+| id | category | rating |+----+----------+--------+|  1 |        1 |    4.5 ||  5 |        1 |    3.2 ||  3 |        2 |    3.7 ||  4 |        2 |    3.5 ||  6 |        2 |    3.5 |+----+----------+--------+

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

 

     如果排序的個數N剛好能在sort buffer裡面，那麼服務就可以避免執行檔案合并和並且把sort buffer當做一個優先順序隊列來處理：

     1：  掃描表，把選中的行都插入隊列中，如果隊列滿了把最後一個剔除掉。

      2：  然後返回前N行，如果有跳過M，那麼先跳過M行，然後返回之後的N行記錄。

    之前使用的處理方法：

      1：  掃描表，重複下面的步驟直到結束

      2：輸入select row直到sort buffer滿。

      3： 寫入前N行到buffer，然後把前N行合并到檔案。

      4：排序合并檔案並返回前N行。

    掃描表的花費和隊列和檔案合并一樣，所以最佳化器在選擇的時候是根據其他花費的：

      1： 隊列的方法會使用很多cpu來插入到隊列。

      2： 合并檔案會使用IO來讀寫檔案，cpu來排序。

   最佳化器在行數和不同值N之間平衡。

 

MYSQL Optimizing LIMIT Queries