使用 limit 優化查詢和防止SQL被優化

Table of Contents

查詢優化

1.1 最大值和最小值的優化

對於 MIN() 和 MAX() 查詢， MySQL 而且優化做得並不是太好，例如

select MIN(id) FROM film where name = '西遊記'；
複製代碼

假設表 film 數據如下：

其中 id 為主鍵並自增， name 為 varchar 且沒有索引

因為 name 沒有索引，因為 MySQL 將會進行一次全表掃描。因為 id 為自增，那麼我們可以當作，第一次找到 name=’西遊記’ 時， id 就為我們想要的結果，此時我們可以改寫 SQL 問：

select id FROM film where name = '西遊記' limit 1；
複製代碼

此時當查到第一條記錄時，就會停止繼續查詢，獲得更高的性能。

1.2 優化 limit 分頁

在系統進行分頁操作的時候，當偏移量大時，例如： limit 10000,20 時， MySQL 需要查詢 10020 條記錄然後只返回 20 記錄，前面的記錄全部被捨棄，這樣的代價非常高

SELECT id, name, price FROM file LIMIT 10000 OFFSET 20
複製代碼

上面的 SQL 我想是分頁常規的寫法，寫法沒有什麼錯誤，正如上面說到，浪費了大量的性能。

1.2.1 使用關聯查詢優化

優化此類查詢一個簡單的方法就是儘可能地使用索引覆蓋掃描，而不是查詢所有的列，然後根據需要做一次關聯操作再返回所需的列。對於偏移大的時候，這樣做的效率提升非常大。

SELECT
    id, name, price
FROM film
INNER JOIN (
    SELECT id
    FROM film
    LIMIT 10000 OFFSET 20
    ) AS LIM USING(id)
複製代碼

1.2.2 使用範圍查詢

有時候可以將 LIMIT 轉化為已知位置的查詢，讓 MySQL 通過範圍掃描獲得到對應的結果。例如，如果在一個位置列上有索引，並且預先計算出了邊界值，則改寫查詢為：

SELECT id, name, price
FROM film
WHERE position BETWEEN 10000 AND 10020
ORDER BY position
複製代碼

1.2.3 利用唯一自增序列進行查詢

這裡的唯一自增序列可以是自增 id 主鍵，也可以其他的具有唯一和升序的數字即可

在前面的思路中，我們考慮的都是傳入頁數和每頁數量，在一些操作中可以改為傳入上一次查詢到的自增序列，然後往後查詢對應的每頁數量即可。

例如原來要求前端傳入頁數（pageIndex）和 每頁數量（pageSize）, 此時的 SQL 為

select * from film 
limit (pageIndex -1) * pageSize OFFSET pageIndex * pageSize
複製代碼

如果改為讓前端傳入最後一次查詢到結果的 自增序列（sid） 和 每頁數量（pageSize）

比如這時的自增序列（sid） 就是 film 的 id, 則 SQL 可以改寫成

select * from film 
where id > sid
limit pageSize
複製代碼

當查第一頁的時候，sid 傳入 0 即可，查第二頁的時候，傳入獲取第一頁時最後得到 id 即可

防止被優化

在寫 SQL 的時候，除了要考慮優化 SQL 降低執行時間外，有時還要防止 SQL 被 MySQL 本身給你優化掉，造成執行結果和你想像的不一樣。

在 MySQL 使用 group by 語句進行查詢時，當有多條數據都滿足時，會顯示第一條數據例如：

假設表 film 數據如下：

則執行SQL select * from film group by name , 則結果為：

但在一些數據重複時，我們往往想要最後一條數據, 這是我們會想到通過子查詢的形式先排序後group by 如下:

select *
from ( select * from film order by id desc) as film_ordered
group by name;
複製代碼

執行後發現結果沒變, 這是因為 MySQL5.7 會對子查詢進行優化，認為子查詢中的 order by 可以進行忽略，只要Derived table里不包含如下條件就可以進行優化：

• UNION clause
• GROUP BY
• DISTINCT
• Aggregation
• LIMIT or OFFSET

根據上面說明，我們可以使用 limit 阻止子查詢優化，改寫後SQL

select *
from ( select * from film order by id desc limit 10000000) as film_ordered
group by name;
複製代碼

這樣結果就符合我們想要的了