Table of Contents

查詢優(yōu)化1.1 最大值和最小值的優(yōu)化1.2 優(yōu)化 limit 分頁1.2.1 使用關(guān)聯(lián)查詢優(yōu)化1.2.2 使用范圍查詢1.2.3 利用唯一自增序列進(jìn)行查詢防止被優(yōu)化參考

查詢優(yōu)化

1.1 最大值和最小值的優(yōu)化

對于 MIN() 和 MAX() 查詢，MySQL 的優(yōu)化做的并不是太好，例如

select MIN(id) FROM film where name = '西游記'；

假設(shè)表 film 數(shù)據(jù)如下：

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

因?yàn)?name 沒有索引，因?yàn)?MySQL 將會進(jìn)行一次全表掃描。因?yàn)?id 為自增，那么我們可以當(dāng)作，第一次找到 name='西游記'時，id 就為我們想要的結(jié)果，此時我們可以改寫 SQL 為：

select id FROM film where name = '西游記' limit 1；

此時當(dāng)查到第一條記錄時，就會停止繼續(xù)查詢，獲得更高的性能。

1.2 優(yōu)化 limit 分頁

在系統(tǒng)進(jìn)行分頁操作的時候，當(dāng)偏移量大時，例如：limit 10000,20 時，MySQL 需要查詢 10020 條記錄然后只返回 20 記錄，前面的記錄全部被舍棄，這樣的代價(jià)非常高

SELECT id, name, price FROM file LIMIT 10000 OFFSET 20

上面的 SQL 我想是分頁常規(guī)的寫法，寫法沒有什么錯誤，正如上面說到，浪費(fèi)了大量的性能。

1.2.1 使用關(guān)聯(lián)查詢優(yōu)化

優(yōu)化此類查詢一個簡單的方法就是盡可能地使用索引覆蓋掃描，而不是查詢所有的列，然后根據(jù)需要做一次關(guān)聯(lián)操作再返回所需的列。對于偏移大的時候，這樣做的效率提升非常大。

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

1.2.2 使用范圍查詢

有時候可以將 LIMIT 轉(zhuǎn)化為已知位置的查詢，讓 MySQL 通過范圍掃描獲得到對應(yīng)的結(jié)果。例如，如果在一個位置列上有索引，并且預(yù)先計(jì)算出了邊界值，則改寫查詢?yōu)椋?/p>

SELECT id, name, price
FROM film
WHERE position BETWEEN 10000 AND 10020
ORDER BY position

1.2.3 利用唯一自增序列進(jìn)行查詢

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

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

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

select * from film 
limit (pageIndex -1) * pageSize OFFSET pageIndex * pageSize

如果改為讓前端傳入最后一次查詢到結(jié)果的 自增序列（sid） 和 每頁數(shù)量（pageSize）

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

select * from film 
where id > sid
limit pageSize

當(dāng)查第一頁的時候，sid 傳入 0 即可，查第二頁的時候，傳入獲取第一頁時最后得到 id 即可

防止被優(yōu)化

在寫 SQL 的時候，除了要考慮優(yōu)化 SQL 降低執(zhí)行時間外，有時還要防止 SQL 被 MySQL 本身給你優(yōu)化掉，造成執(zhí)行結(jié)果和你想象的不一樣。

在 MySQL 使用 group by 語句進(jìn)行查詢時，當(dāng)有多條數(shù)據(jù)都滿足時，會顯示第一條數(shù)據(jù)例如：

假設(shè)表 film 數(shù)據(jù)如下：

則執(zhí)行SQL select * from film group by name, 則結(jié)果為：

但在一些數(shù)據(jù)重復(fù)時，我們往往想要最后一條數(shù)據(jù), 這是我們會想到通過子查詢的形式先排序后group by 如下:

select *
from ( select * from film order by id desc) as film_ordered
group by name;

執(zhí)行后發(fā)現(xiàn)結(jié)果沒變, 這是因?yàn)?MySQL5.7 會對子查詢進(jìn)行優(yōu)化，認(rèn)為子查詢中的 order by 可以進(jìn)行忽略，只要Derived table里不包含如下條件就可以進(jìn)行優(yōu)化：

• UNION clause

• GROUP BY

• DISTINCT

• Aggregation

• LIMIT or OFFSET

根據(jù)上面說明，我們可以使用 limit 阻止子查詢優(yōu)化，改寫后SQL

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

這樣結(jié)果就符合我們想要的了