上篇講到了 MySQL 的冷門知識——自定義變量灶体，估計很少人有用到，但是如果用好了也是可以輔助進行性能優(yōu)化的。需要注意的是變量是基于連接會話的偷拔，而且可能存在一些意外的情況岗喉，需要小心使用。本篇介紹如何利用自定義變量進行查詢優(yōu)化瓶盛，提高效率。
優(yōu)化排序查詢
自定義變量的一個重要特性是你可以同時將該變量的數(shù)學計算后的結果再賦值給該變量，類似于我們的 i = i + 1這種方式惩猫。下面是一個用于計算數(shù)據(jù)表行號的例子：
SET @rownum := 0;
SELECT actor_id, @rownum := @rownum + 1 AS rownum
FROM sakila.actor LIMIT 3;
| actor_id | rownum |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
得到的結果也許看起來沒什么意義芝硬，這是因為主鍵是從1自增的，因此行號和主鍵值是一樣的轧房。但是拌阴，這種方式可以用于做排序。例如需要查詢飾演電影數(shù)量最多的前10名演員奶镶，通常的做法是像下面這樣寫：
SELECT actor_id, COUNT(*) as cnt
FROM sakila.film_actor
GROUP BY actor_id
ORDER BY cnt DESC
LIMIT 10;
如果我們要獲得相應的排名值的話迟赃，則可以引入變量來完成：
SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
SELECT actor_id,
@curr_cnt := cnt AS cnt,
@rank := IF(@prev_cnt <> @curr_cnt, @rank+1, @rank) as rank,
@prev_cnt := @curr_cnt AS dummy
FROM (
SELECT actor_id, COUNT(*) AS cnt
FROM sakila.film_actor
GROUP BY actor_id
ORDER BY cnt DESC
LIMIT 10
) as der;
這里是將飾演電影的數(shù)量賦值給了 curr_cnt 變量，使用了prev_cnt 存儲前一個演員的參演數(shù)量厂镇。排名從第一名開始的纤壁，如果后面的演員的數(shù)量和前一個演員的數(shù)量不同，則排名要往下（+1）捺信，如果相同則和前一個演員的排名相同酌媒。通過這種方式可以直接從查詢結果中得到演員的排名，而不需要再從數(shù)據(jù)庫查詢做二次處理（當然也可以通過程序代碼實現(xiàn)）迄靠。
避免重復獲取剛剛修改的數(shù)據(jù)行
如果想在更新數(shù)據(jù)行的時候再重新獲取數(shù)據(jù)行的信息馍佑，往往需要再讀取一次數(shù)據(jù)庫。這是因為 MySQL 不像 PostgreSQL 的 UPDATE RETURNING 功能可以同時返回更新后的數(shù)據(jù)行梨水，而只是返回更新影響的行數(shù)拭荤。但是，我們可以通過自定義變量完成這樣的操作疫诽。例如舅世，獲取剛剛被修改過更新時間的行，不使用自定義變量的話需要做一次額外的查詢：
UPDATE tb1 SET lastUpdated = NOW() WHERE id = 1;
SELECT lastUpdated FROM tb1 WHERE id = 1;
而使用自定義變量的時候可以避免這種情況：
UPDATE tb1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
SELECT @now;
雖然還是有一個查詢操作奇徒，但是后面的查詢操作不再需要訪問數(shù)據(jù)庫了雏亚。
懶加載的聯(lián)合查詢
假設我們需要寫一個聯(lián)合查詢完成如下任務：在聯(lián)合的分支上查找匹配的數(shù)據(jù)行，如果找到了就跳過其他分支摩钙。y這種情況發(fā)生在需要從熱區(qū)數(shù)據(jù)或低頻訪問數(shù)據(jù)中查找（比如近期訂單和歷史訂單）罢低。這是下面針對用戶查詢的一個普通的 SQL：
SELECT id FROM users WHERE id = 123
UNION ALL
SELECT id FROM users_archived WHERE id = 123;
這個查詢會先從當前正在使用的用戶表查詢 id 為123的用戶，然后 在從已歸檔的用戶表找同樣 id 的用戶胖笛。但是网持，這種寫法比較低效，即便是在 users 表找到了想要找的用戶长踊，還是需要從users_archived 這個表再找一次功舀，而實際用戶 id 為123的只會存在其中的一張表中或兩張表的數(shù)據(jù)是一樣的。通過懶加載的聯(lián)合查詢身弊，可以避免這種情況——只有在第一個分支沒有找到數(shù)據(jù)時才進行第二個分支的查詢辟汰。因此可以使用 MySQL 的 GREATEST 方法來作為查詢結果的容器以避免多返回數(shù)據(jù)列列敲。
SELECT GREATEST(@found := -1, id) AS id, users.name, 'users' as which_tb1
FROM users WHERE id = 123
UNION ALL
SELECT id, users_archived.name, 'users_archived'
FROM users_archived WHERE id = 123 AND @found IS NULL
UNION ALL
SELECT 1, '', 'reset' FROM DUAL WHERE ( @found := NULL) IS NOT NULL;
上述的查詢?nèi)绻谝恍杏薪Y果，則@found 不會被賦值帖汞，因而是 NULL戴而，從而執(zhí)行第二次查詢。而第三次的 UNION 實際沒什么效果翩蘸，只是為了將@found恢復到 NULL 值所意，以便這段 SQL 可以重復執(zhí)行。另一個驗證的方法是對同一張表進行這樣的操作鹿鳖，可以發(fā)現(xiàn)實際只會返回一行數(shù)據(jù)或不返回數(shù)據(jù)（查詢不到數(shù)據(jù)時）扁眯。
SELECT GREATEST(@found := -1, `id`) AS `id`, `infocenter_city`.`name`, 'city' as which_tb1
FROM `infocenter_city` WHERE `id` = 460100
UNION ALL
SELECT `id`, `infocenter_city`.`name`, 'infocenter_city'
FROM `infocenter_city` WHERE id = 460100 AND @found IS NULL
UNION ALL
SELECT 1, '', 'reset' FROM DUAL WHERE ( @found := NULL) IS NOT NULL
