目錄優(yōu)化排序查詢避免重復(fù)獲取剛剛修改的數(shù)據(jù)行懶加載的聯(lián)合查詢優(yōu)化排序查詢

自定義變量的一個(gè)重要特性是你可以同時(shí)將該變量的數(shù)學(xué)計(jì)算后的結(jié)果再賦值給該變量，類似于我們的 i = i + 1這種方式。下面是一個(gè)用于計(jì)算數(shù)據(jù)表行號(hào)的例子：

SET @rownum := 0;SELECT actor_id, @rownum := @rownum + 1 AS rownumFROM sakila.actor LIMIT 3;

actor_id rownum 1 1 2 2 3 3

得到的結(jié)果也許看起來(lái)沒(méi)什么意義，這是因?yàn)橹麈I是從1自增的，因此行號(hào)和主鍵值是一樣的。但是，這種方式可以用于做排序。例如需要查詢飾演電影數(shù)量最多的前10名演員，通常的做法是像下面這樣寫：

SELECT actor_id, COUNT(*) as cntFROM sakila.film_actorGROUP BY actor_idORDER BY cnt DESCLIMIT 10;

得到的結(jié)果也許看起來(lái)沒(méi)什么意義，這是因?yàn)橹麈I是從1自增的，因此行號(hào)和主鍵值是一樣的。但是，這種方式可以用于做排序。例如需要查詢飾演電影數(shù)量最多的前10名演員，通常的做法是像下面這樣寫：

SELECT actor_id, COUNT(*) as cntFROM sakila.film_actorGROUP BY actor_idORDER BY cnt DESCLIMIT 10;

如果我們要獲得相應(yīng)的排名值的話，則可以引入變量來(lái)完成：

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 dummyFROM ( SELECT actor_id, COUNT(*) AS cnt FROM sakila.film_actorGROUP BY actor_idORDER BY cnt DESCLIMIT 10) as der;

這里是將飾演電影的數(shù)量賦值給了 curr_cnt 變量，使用了prev_cnt 存儲(chǔ)前一個(gè)演員的參演數(shù)量。排名從第一名開(kāi)始的，如果后面的演員的數(shù)量和前一個(gè)演員的數(shù)量不同，則排名要往下（+1），如果相同則和前一個(gè)演員的排名相同。通過(guò)這種方式可以直接從查詢結(jié)果中得到演員的排名，而不需要再?gòu)臄?shù)據(jù)庫(kù)查詢做二次處理（當(dāng)然也可以通過(guò)程序代碼實(shí)現(xiàn)）。

避免重復(fù)獲取剛剛修改的數(shù)據(jù)行

如果想在更新數(shù)據(jù)行的時(shí)候再重新獲取數(shù)據(jù)行的信息，往往需要再讀取一次數(shù)據(jù)庫(kù)。這是因?yàn)?MySQL 不像 PostgreSQL 的 UPDATE RETURNING 功能可以同時(shí)返回更新后的數(shù)據(jù)行，而只是返回更新影響的行數(shù)。但是，我們可以通過(guò)自定義變量完成這樣的操作。例如，獲取剛剛被修改過(guò)更新時(shí)間的行，不使用自定義變量的話需要做一次額外的查詢：

UPDATE tb1 SET lastUpdated = NOW() WHERE id = 1;SELECT lastUpdated FROM tb1 WHERE id = 1;

而使用自定義變量的時(shí)候可以避免這種情況：

UPDATE tb1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();SELECT @now;

雖然還是有一個(gè)查詢操作，但是后面的查詢操作不再需要訪問(wèn)數(shù)據(jù)庫(kù)了。

懶加載的聯(lián)合查詢

假設(shè)我們需要寫一個(gè)聯(lián)合查詢完成如下任務(wù)：在聯(lián)合的分支上查找匹配的數(shù)據(jù)行，如果找到了就跳過(guò)其他分支。y這種情況發(fā)生在需要從熱區(qū)數(shù)據(jù)或低頻訪問(wèn)數(shù)據(jù)中查找（比如近期訂單和歷史訂單）。這是下面針對(duì)用戶查詢的一個(gè)普通的 SQL：

SELECT id FROM users WHERE id = 123UNION ALLSELECT id FROM users_archived WHERE id = 123;

這個(gè)查詢會(huì)先從當(dāng)前正在使用的用戶表查詢 id 為123的用戶，然后 在從已歸檔的用戶表找同樣 id 的用戶。但是，這種寫法比較低效，即便是在 users 表找到了想要找的用戶，還是需要從users_archived 這個(gè)表再找一次，而實(shí)際用戶 id 為123的只會(huì)存在其中的一張表中或兩張表的數(shù)據(jù)是一樣的。通過(guò)懶加載的聯(lián)合查詢，可以避免這種情況——只有在第一個(gè)分支沒(méi)有找到數(shù)據(jù)時(shí)才進(jìn)行第二個(gè)分支的查詢。因此可以使用 MySQL 的 GREATEST 方法來(lái)作為查詢結(jié)果的容器以避免多返回?cái)?shù)據(jù)列。

SELECT GREATEST(@found := -1, id) AS id, users.name, ’users’ as which_tb1FROM users WHERE id = 123UNION ALLSELECT id, users_archived.name, ’users_archived’ FROM users_archived WHERE id = 123 AND @found IS NULLUNION ALLSELECT 1, ’’, ’reset’ FROM DUAL WHERE ( @found := NULL) IS NOT NULL;

上述的查詢?nèi)绻谝恍杏薪Y(jié)果，則@found 不會(huì)被賦值，因而是 NULL，從而執(zhí)行第二次查詢。而第三次的 UNION 實(shí)際沒(méi)什么效果，只是為了將@found恢復(fù)到 NULL 值，以便這段 SQL 可以重復(fù)執(zhí)行。另一個(gè)驗(yàn)證的方法是對(duì)同一張表進(jìn)行這樣的操作，可以發(fā)現(xiàn)實(shí)際只會(huì)返回一行數(shù)據(jù)或不返回?cái)?shù)據(jù)（查詢不到數(shù)據(jù)時(shí)）。

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

以上就是MySQL 使用自定義變量進(jìn)行查詢優(yōu)化的詳細(xì)內(nèi)容，更多關(guān)于MySQL 用自定義變量進(jìn)行查詢優(yōu)化的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！