當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL之查询性能优化（四）

發布時間：2025/3/16 数据库 19 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL之查询性能优化（四）小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

優化特定類型的查詢

COUNT()的作用

COUNT()是一個特殊函數，有兩個非常不同的作用：它可以統計某個列值的數量，也可以統計行數。在統計列值時要求列值是非空的(不統計NULL)。如果在COUNT()的括號中指定了列或者列的表達式，則統計的就是這個表達式有值的結果數。因為很多人對NULL理解有可題, 所以這里很容易產生誤解。

COUNT()的另一個作用是統計結果集的行數。當MySQL確認括號內的表達式值不可能為空時，實際上就是在統計行數. 最簡單的就是當我們使用COUNT(*)的時候，這種情況下通配符*并不會像我們猜想的另那樣擴展成所有的列，實際上它會忽略所有的列而直接統計所有的行數。

mysql> SELECT * FROM test; +-------+ | hello | +-------+ | 1 | | 2 | | NULL | | 4 | | 5 | | 6 | +-------+ 6 rows in set (0.00 sec)mysql> SELECT COUNT(hello) FROM test; +--------------+ | COUNT(hello) | +--------------+ | 5 | +--------------+ 1 row in set (0.00 sec)

我們發現一個最常見的錯誤就是，在括號內指定了一個列卻希望統計結果集的行數。如果希望知道的是結果集的行數，最好使用 COUNT(*)，這樣寫意義清晰，性能也會很好。

關于MyISAM的神話

一個容易產生的誤解就是：MyISAM的COUNT()函數總是非常快，不過這是有前提條件的，即只有沒有任何WHERE條件的COUNT(*)才非常快，因為此時無需實際地去計算表的行數。MySQL可以利用存儲引擎的特性直接獲得這個值。如果MySQL知道某列col不可能為NULL值，那么MySQL內部會將COUNT(col)表達式優化為COUNT(*)。

當統計帶WHERE子句的結果集行數，可以是統計某個列值的數量時，MySQL的COUNT()和其它存儲引擎沒有任何不同，就不再有神話般的速度了。所以在MyISAM引擎表上執行COUNT()有時候比別的引擎快，有時候比別的引擎慢，這受很多因素影響，要視具體情況而定。

簡單的優化

有時候可以使用MyISAM在COUNT(*)全表非常快的這個特性，來加速一些特定條件COUNT()的查詢。在下面的例子中，我們使用標準數據庫world來看看如何快速查找到所有ID大于5的城市。可以像下面這樣來寫這個查詢：

mysql> SELECT COUNT(*) FROM world.city WHERE ID > 5; +----------+ | COUNT(*) | +----------+ | 4074 | +----------+ 1 row in set (0.01 sec)

可以看到該查詢需要掃描4097行數據。如果將條件反轉一下，先查找ID小于等于5的城市數，然后用總城市數一減就能得到同樣的結果，卻可以將掃描的行數減少到5行以內：

mysql> SELECT (SELECT COUNT(*) FROM world.city) - COUNT(*) FROM world.city WHERE ID <= 5; +----------------------------------------------+ | (SELECT COUNT(*) FROM world.city) - COUNT(*) | +----------------------------------------------+ | 4074 | +----------------------------------------------+ 1 row in set (0.00 sec)

這樣做可以大大減少需要掃描的行數，是因為在查詢優化階段會將其中的子查詢直接當成一個常數來處理，讓我們刪除city表的外鍵，并將其表類型更改為MyISAM，我們可以通過EXPLAIN來驗證這點：

通常會看到這樣的問題：如果在同一個查詢中統計同一個列的不同值的數量，以減少查詢的語句量。例如，假設可能需要通過一個查詢返回各種不同顏色的商品數量，此時不能使用OR語句，因為這樣做就無法區分不同顏色的商品數量。下面的查詢可以在一定程度上解決這個問題。

SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0)) AS red FROM items;

也可以使用COUNT()而不是SUM()實現同樣的目的，只需要將滿足條件設置為真，不滿足條件設置為NULL即可：

SELECT COUNT(color = 'blue' OR NULL) AS blue,COUNT(color = 'red' OR NULL) AS red FROM items;

使用近似值

有時候某些業務場景并不要求完全精確的COUNT值，此時可以用近似值來代替。EXPLAIN出來的優化器估算的行數就是一個不錯的近似值，執行EXPLAIN并不需要真正地去執行查詢，所以成本很低。

優化關聯查詢

確保ON或者USING子句中的列上有索引。在創建索引的時候就要考慮到關聯的順序。當表A和表B用到列C關聯的時候，如果優化器關聯順序是B、A，那就不需要在B表的對應列上建立索引。沒有用到的索引只會帶來額外的負擔。一般來說，除非有其他理由，否則只需要在關聯順序中的第二個表的相應列上創建索引。
確保任何的GROUP BY 和ORDER BY中的表達式只涉及到一個表中的列。這樣MySQL才有可能使用索引來優化這個過程。
當升級MySQL的時候需要注意：關聯語法、運算符優先級等其他可能會發生變化的地方。因為以前是普通關聯的地方可能會變成笛卡爾積，不同類型的關聯可能會生成不同的結果。

優化GROUP BY和DISTINCT

在很多場景下，MySQL都使用同樣的辦法優化這兩種查詢，事實上，MySQL優化器會在內部處理的時候相互轉化這兩類查詢。它們都可以使用索引來優化，這也是最有效的優化辦法。

在MySQL中，當無法使用索引的時候，GROUP BY使用兩種策略來完成：使用臨時表或文件排序來做分組。對于任何查詢語句，這兩種策略的性能都有可以提升的地方。可以通過使用提示SQL_BIG_RESULT和SQL_SMALL_RESULT來讓優化器按你希望的方式運行。

如果需要對關聯查詢分組(GROUP BY)，并且是按照查找表中的某個列進行分組，那么通常采用查找表的標識列分組的效率比其他列更高。例如下面的查詢效率不會很好：

SELECT actor.first_name, actor.last_name, COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY actor.first_name, actor.last_name;

如果查詢按照下面的寫法效率則會更高：

SELECT actor.first_name, actor.last_name, COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY film_actor.actor_id;

這個查詢利用了演員的姓名和ID直接相關的特點，因此改寫后的結果不受影響，但顯然不是所有的關聯語句的分組查詢都可以改寫成在SELECT中直接使用非分組的形式的。甚至可能會在服務器上設置SQL_MODE來禁止這樣的寫法。如果是這樣，也可以通過MIN()或者MAX()函數來繞過這種限制，但一定要清楚，SELECT后面出現的非分組列一定是直接依賴分組列，并且在每個組內的值是唯一的，或者是業務上根本不在乎這個值具體是什么。

在分組查詢的SELECT中直接使用非分組列通常不是什么好主意，因為這樣的結果通常是不定的，當索引改變，或者優化器選擇不同的優化策略時都可能導致結果不一樣。我們碰到的大多數這種查詢最后都會導致故障（因為MySQL不會對這類查詢返回錯誤），而且這種寫法大部分是由于偷懶而不是為優化而故意這么設計的。建議始終使用含義明確的語法。事實上，我們建議將MySQL的SQL_MODE設置為包含ONLY_FULL_GROUP_BY，這時MySQL會對這類查詢直接返回一個錯誤，提醒你需要重寫這個查詢。

如果沒有通過ORDER BY子句顯示地指定排序列，當查詢使用GROUP BY子句的時候，結果集會自動按照分組的字段進行排序。如果不關心結果集的順序，而這種默認排序又導致了需要文件排序，則可以使用ORDER BY NULL，讓MySQL不再進行文件排序。也可以在GROUP BY子句中直接使用DESC或ASC關鍵字，使分組的結果集按需要的方向排序。

優化GROUP BY WITH ROLLUP

分組查詢的一個變種就是要求MySQL對返回的分組結果再做一次超級聚合。可以使用WITH ROLLUP子句來實現這種邏輯，但可能會不夠優化。可以通過EXPLAIN來觀察其執行計劃，特別要注意分組是否通過文件排序或者臨時表實現的。然后再去掉WITH ROLLUP子句看執行計劃是否相同。

很多時候，在應用程序中做超級聚合是更好的，雖然這需要返回給客戶端更多的結果。也可以在FROM子句中嵌套使用子查詢，或者是通過一個臨時表存放中間數據，然后和臨時表執行UNION來得到最終結果。

比方下面有一張學生科目分數表，共有四個字段，第一個是主鍵，第二個是學生ID，第三個是科目，第四個是科目分數

mysql> SELECT * FROM stu_subject; +----+--------+---------+-------+ | id | stu_id | subject | score | +----+--------+---------+-------+ | 1 | 1 | Chinese | 80 | | 2 | 1 | Math | 90 | | 3 | 2 | Chinese | 85 | | 4 | 2 | Math | 80 | | 5 | 3 | Chinese | 75 | | 6 | 3 | Math | 85 | +----+--------+---------+-------+ 6 rows in set (0.00 sec)

我們很容易根據不同的學生ID進行平均分的統計

mysql> SELECT stu_id, AVG(score) FROM stu_subject GROUP BY stu_id; +--------+------------+ | stu_id | AVG(score) | +--------+------------+ | 1 | 85.0000 | | 2 | 82.5000 | | 3 | 80.0000 | +--------+------------+ 3 rows in set (0.00 sec)

但是，如果在統計各個學生的平均分時，還希望統計全部學生的平均分，傳統的GROUP BY就無法實現了，這時我們必須借助GROUP BY WITH ROLLUP：

mysql> SELECT stu_id, AVG(score) FROM stu_subject GROUP BY stu_id WITH ROLLUP; +--------+------------+ | stu_id | AVG(score) | +--------+------------+ | 1 | 85.0000 | | 2 | 82.5000 | | 3 | 80.0000 | | NULL | 82.5000 | +--------+------------+ 4 rows in set (0.00 sec)mysql> SELECT AVG(score) FROM stu_subject; +------------+ | AVG(score) | +------------+ | 82.5000 | +------------+ 1 row in set (0.00 sec)

優化LIMIT分頁

在系統需要分頁操作的時候，我們通常會使用LIMIT加上偏移量的辦法實現，同時加上合適的ORDER BY子句。如果有對應的索引，通常效率會不錯，否則，MySQL需要做大量的文件排序操作。

一個常見又令人頭疼的問題是，在偏移量非常大的時候，例如LIMIT 1000,20這樣的查詢，這時MySQL需要查詢10020條記錄然后只返回最后20條，前面10000條記錄都將被拋棄，這樣的代價非常高。如果所有的頁面被返回的頻率都相同，那么這樣的查詢平均需要訪問半個表的數據。要優化這種查詢，要么是在頁面中限制分頁的數量，要么是優化大偏移量的性能。

優化此類分頁查詢的一個最簡單的方法就是盡可能的使用索引覆蓋掃描，而不是查詢所有的列。然后根據需要做一次關聯操作再返回所需的列。對于偏移量很大的時候，這樣做的效率會提升非常大。考慮下面的查詢：

SELECT film_id, description FROM film ORDER BY title LIMIT 50, 5;

如果這個表非常大，最好改寫成下面的樣子：

SELECT film_id, description FROM film INNER JOIN(SELECT film_id FROM film ORDER BY title LIMIT 50, 5) AS film USING(film_id);

這里的延遲關聯將大大提升查詢效率，它讓MySQL掃描盡可能少的頁面，獲取需要訪問的記錄后再根據關聯列回原表查詢需要的所有列。這個技術也可以用于優化關聯查詢中的LIMIT子句。

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

SELECT film_id, description FROM film WHERE position BETWEEN 50 AND 54 ORDER BY position;

對數據排名的問題也與此類似，但往往還會同時和GROUP BY混合使用。在這種情況下通常都需要先計算并存儲排名信息。

LIMIT和OFFSET搭配使用，其實是OFFSET的問題，它會導致MySQL掃描大量不需要的行然后再拋棄掉。如果可以使用書簽記錄上次取數據的位置，那么下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用OFFSET。例如，若需要按照租借記錄記錄做翻頁，那么可以根據最新一條租借記錄向后追溯，這種做法可行是因為租借記錄的主鍵是單調增長的。首先使用下面的查詢獲得第一組結果：

SELECT * FROM rental ORDER BY rental_id DESC LIMIT 20;

假設上面的查詢返回的是主鍵為16049到16030的租借記錄，那么下一頁查詢就可以從16030這個點開始：

SELECT * FROM rental WHERE rental_id < 16030 ORDER BY rental_id DESC LIMIT 20;

該技術的好處是無論翻頁到多么后面，其性能都會很好。其他優化辦法還包括使用預先計算的匯總表，或者關聯到一個冗余表，冗余表只包含主鍵列和需要做排序的數據列。還可以使用Sphinx優化一些搜索操作。

優化SQL_CALC_FOUND_ROWS

分頁的時候，另一個常用的技巧是在LIMIT語句中加上SQL_CALC_FOUND_ROWS提示(hint)，這樣就可以獲得去掉LIMIT以后滿足條件的行數，因此可以作為分頁的總數。看起來，MySQL做了一些非常高深的優化，像是通過某種方法預測了總行數。但實際上，MySQL只有在掃描了所有滿足條件的行以后，才知道行數，所以加上這個提示以后，不管是否需要，MySQL就會掃描所有滿足條件的行，然后再拋棄掉不需要的行，而不是在滿足LIMIT的行數后就終止掃描，所以該提示的代價可能非常高。

一個更好的設計是將具體的頁數換成“下一頁”按鈕，假設每頁顯示20條記錄，那么我們每次查詢時都是用LIMIT返回21條記錄并只顯示20條，如果第21條存在，那么我們就顯示“下一頁”按鈕，否則就說明沒有更多的數據，也就無需顯示“下一頁”按鈕了。

另一種做法是先獲取并緩存較多的數據，例如緩存1000條，然后每次分頁都從這個緩存中獲取。這樣做可以讓應用程序根據結果集的大小采取不同的策略，如果結果集少于1000，就可以在頁面上顯示所有的分頁鏈接，因為數據都在緩存中，所以這樣做性能不會有問題。如果結果集大于1000，就可以在頁面上設計一個額外的“找到的結果多于1000條”之類的按鈕，這兩種策略都比每次生成全部結果集再拋棄掉不需要的數據的效率要高很多。

有時候也可以考慮使用EXPLAIN的結果中的rows列的值來作為結果集總數的近似值。當需要精確結果集的時候，再單獨使用COUNT(*)來滿足需求，這時如果能使用索引覆蓋掃描則通常也會比SQL_CALC_FOUND_ROWS快的多。

優化UNION查詢

MySQL總是通過創建并填充臨時表的方式來執行UNION查詢。因此很多優化策略在UNION查詢中都沒法很好地使用。經常需要手工地將WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各個子查詢中，以便優化器可以充分利用這些條件進行優化。

除非確實需要服務器消除重復的行，否則就一定要使用UNION ALL，這一點很重要。如果沒有ALL關鍵字，MySQL會給臨時表加上DISTINCT選項，這會導致對整個臨時表的數據做唯一性檢查，這樣做的代價非常高。即使有ALL關鍵字，MySQL仍然會使用臨時表存儲結果。事實上，MySQL總是將結果放入臨時表，然后再讀出，再返回給客戶端。雖然很多時候這樣做是沒有必要的。

靜態查詢分析

Percona Toolkit中的pt-query-advisor能夠解析查詢日志、分析查詢模式，然后給出所有可能存在潛在問題的查詢，并給出足夠詳細的建議。這像是給MySQL所有的查詢做一次全面的健康檢查，它能檢測出許多常見的問題。

使用用戶自定義變量

用戶自定義變量是一個容易被遺忘的MySQL特性，在某些場景可以利用它寫出非常高效的查詢語句。在查詢中混合使用過程化和關系化邏輯的時候，自定義變量可能會非常有用。單純的關系查詢將所有的東西都當成無序的數據集合，并且一次性操作它們，MySQL則采用了更加程序化的處理方式。MySQL的這種方式有它的弱點，但如果能熟練的掌握，則會發現其強大之處，用戶自定義變量也可以給這種方式帶來很大的幫助。

用戶自定義變量是一個用來存儲內容的臨時容器，在連接MySQL的整個過程中都存在，可以使用下面的SET和SELECT語句來定義它們：

mysql> SET @one := 1; Query OK, 0 rows affected (0.00 sec)mysql> SET @min_actor:= (SELECT MIN(actor_id) FROM actor); Query OK, 0 rows affected (0.00 sec)mysql> SET @last_week:= CURRENT_DATE-INTERVAL 1 WEEK; Query OK, 0 rows affected (0.00 sec)

然后可以在任何可以使用表達式的地方使用這些自定義變量：

SELECT ... WHERE col <= @last_week;

在了解自定義變量的強大之前，我們再看看它們自身的一些屬性和限制，看看在哪些場景下我們不能使用用戶自定義變量：

使用自定義變量的查詢，無法使用查詢緩存。
不能在使用常量或者標識符的地方使用自定義變量，例如表名、列名和LIMIT子句中。
用戶自定義變量的生命周期是在一個連接中有效，所以不能用它們來做連接間的通信。
如果使用連接池或者持久化連接，自定義變量可能讓看起來毫無關系的代碼發生交互。
在5.0版本之前，是大小寫敏感的，所以要注意MySQL版本間的兼容性問題。
不能顯式的聲明自定義變量的類型，確定未定義變量的具體類型的時機在不同MySQL版本中也可能不一樣。如果你希望變量是整型，最好在初始化時賦值為0，如果希望是浮點型則賦值為0.0，如果希望是字符串則賦值''，用戶自定義變量的類型在賦值的時候會改變。MySQL的用戶自定義變量是一個動態類型。
MySQL優化器在某些場景下可能會將這些變量優化掉，這可能導致代碼不按預想的方式運行。
賦值的順序和賦值的時間點并不總是固定的，這依賴于優化器的決定，后面還會介紹這一點。
賦值符號:=的優先級非常低，所以賦值表達式應使用明確的括號。
使用未定義變量不會產生任何語法錯誤，如果沒有意識到這一點，非常容易犯錯。

優化排名語句

使用用戶自定義變量的一個重要特性是你可以在給一個變量賦值的同時使用這個變量。換句話說，用戶自定義變量的賦值具有“左值”特性。下面的例子展示了如何使用變量來實現一個類似“行號”的功能：

mysql> SET @rownum := 0; Query OK, 0 rows affected (0.00 sec)mysql> SELECT actor_id, @rownum := @rownum + 1 AS rownum FROM actor LIMIT 3; +----------+--------+ | actor_id | rownum | +----------+--------+ | 58 | 1 | | 92 | 2 | | 182 | 3 | +----------+--------+ 3 rows in set, 1 warning (0.00 sec)

這個例子的實際意義并不大，它只是實現了一個和該表主鍵一樣的列。不過，我們也可以把它當做一個排名。現在，我們來看一個更復雜的用法。我們先編寫一個查詢獲取演過最多電影的前10位演員，然后根據他們的出演電影次數做一個排名，如果出演的電影數量一樣，則排名相同。我們先編寫一個查詢，返回每個演員參演電影的數量：

mysql> SELECT actor_id, COUNT(*) as cnt FROM film_actor GROUP BY actor_id ORDER BY cnt DESC LIMIT 10; +----------+-----+ | actor_id | cnt | +----------+-----+ | 107 | 42 | | 102 | 41 | | 198 | 40 | | 181 | 39 | | 23 | 37 | | 81 | 36 | | 158 | 35 | | 106 | 35 | | 13 | 35 | | 37 | 35 | +----------+-----+ 10 rows in set (0.01 sec)

現在我們再把排名加上去，這里看到有四名演員都參演了35部電影，所以他們的排名應該是相同的。我們使用用三個變量來實現：一個用來記錄當前的排名，一個用來記錄前一個演員的排名，還有一個用來記錄當前演員參演的電影數量。只有當前演員參演的電影的數量和前一個演員不同時，排名才變化。我們先試試下面的寫法：

mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0; Query OK, 0 rows affected (0.00 sec)mysql> SELECT actor_id,-> @curr_cnt := COUNT(*) AS cnt,-> @rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS actor_rank,-> @prev_cnt := @curr_cnt AS dummy-> FROM film_actor-> GROUP BY actor_id-> ORDER BY cnt DESC-> LIMIT 10; +----------+-----+------------+-------+ | actor_id | cnt | actor_rank | dummy | +----------+-----+------------+-------+ | 107 | 42 | 0 | 0 | | 102 | 41 | 0 | 0 | | 198 | 40 | 0 | 0 | | 181 | 39 | 0 | 0 | | 23 | 37 | 0 | 0 | | 81 | 36 | 0 | 0 | | 106 | 35 | 0 | 0 | | 37 | 35 | 0 | 0 | | 144 | 35 | 0 | 0 | | 60 | 35 | 0 | 0 | +----------+-----+------------+-------+ 10 rows in set, 3 warnings (0.01 sec)

可以看到，排名和統計列一直無法更新，這是什么原因呢？

這里，通過EXPLAIN我們看到會使用臨時表和文件排序，所以可能是由于變量賦值的時間和我們所預料的不同。

在使用用戶自定義變量的時候，經常會遇到一些詭異的問題，但研究這些問題是很有意義的。使用SQL語句生成排名值通常需要做兩次計算，例如，需要額外計算一次出演過相同數量電影的演員有哪些。使用變量則可一次完成，這對性能是一個很大的提升。

針對這個案例，另一個簡單的方案是在FROM子句中使用子查詢生成一個中間的臨時表：

mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0; Query OK, 0 rows affected (0.00 sec)mysql> SELECT actor_id,-> @curr_cnt := cnt AS cnt,-> @rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS actor_rank,-> @prev_cnt := @curr_cnt AS dummy-> FROM(-> SELECT actor_id, COUNT(*) AS cnt-> FROM film_actor-> GROUP BY actor_id-> ORDER BY cnt DESC-> LIMIT 10-> ) as der; +----------+-----+------------+-------+ | actor_id | cnt | actor_rank | dummy | +----------+-----+------------+-------+ | 107 | 42 | 1 | 42 | | 102 | 41 | 2 | 41 | | 198 | 40 | 3 | 40 | | 181 | 39 | 4 | 39 | | 23 | 37 | 5 | 37 | | 81 | 36 | 6 | 36 | | 60 | 35 | 7 | 35 | | 158 | 35 | 7 | 35 | | 106 | 35 | 7 | 35 | | 13 | 35 | 7 | 35 | +----------+-----+------------+-------+ 10 rows in set, 3 warnings (0.01 sec)

避免重復查詢剛更新的數據

如果在更新行的同時又需要獲取該行的信息，要怎么做才能避免重復的查詢呢？不幸的是，MySQL并沒有提供像PostgreSQL那樣的UPDATE RETURNING語法，這個語法可以幫助我們在更新的時候同時返回該行的信息。不過我們還是可以使用變量來解決這個問題。例如，我們的一個客戶希望能夠高效的更新一條記錄的時間戳，同時希望查詢當前記錄中存放的時間戳是什么，可以使用變量來實現：

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

上面看起來有兩個查詢，需要兩次網絡來回，但第二個查詢無需訪問任何數據表，所以會快很多。

統計更新和插入的數量

當使用了INSERT ON DUPLICATE UPDATE的時候，如果想知道到底插入了多少行數據，到底有多少數據是因為沖突而改寫成更新操作的，可以用如下方法實現：

INSERT INTO t1(c1, c2) VALUES(4, 4),(2, 1),(3, 1) ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + (0 * ( @x := @x + 1));

當每次由于沖突導致更新時對變量@x自增一次，然后通過對這個表達式乘0來讓其不要影響更新的內容。另外，MySQL的協議會返回被更改的總行數，所以不需要單獨統計這個值。

確定取值的順序

使用用戶自定義變量的一個最常見的問題就是沒有注意到在賦值和讀取變量的時候可能是在查詢的不通過階段。例如，在SELECT子句中進行賦值然后再WHERE子句中讀取變量，則可能變量取值并不如你所想。下面的查詢看起來只返回一個結果，但事實上并非如此：

mysql> SET @rownum := 0; Query OK, 0 rows affected (0.00 sec)mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt FROM actor WHERE @rownum <= 1; +----------+------+ | actor_id | cnt | +----------+------+ | 58 | 1 | | 92 | 2 | +----------+------+ 2 rows in set, 1 warning (0.00 sec)

因為WHERE和SELECT是在查詢執行的不同階段被執行的。如果在查詢中再加入ORDER BY的話，結果可能會更不同：

mysql> SET @rownum := 0; Query OK, 0 rows affected (0.00 sec)mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt FROM actor WHERE @rownum <= 1 ORDER BY first_name; +----------+------+ | actor_id | cnt | +----------+------+ | 71 | 1 | | 132 | 2 | | 165 | 3 | | 173 | 4 | | 125 | 5 | | 146 | 6 | ………………………………………………… | 108 | 193 | | 119 | 194 | | 140 | 195 | | 168 | 196 | | 175 | 197 | | 28 | 198 | | 82 | 199 | | 11 | 200 | +----------+------+ 200 rows in set, 1 warning (0.01 sec)

這是因為ORDER BY 引入了文件排序，而WHERE條件是在文件排序操作之前取值的，所以這條查詢會返回表中的全部記錄。解決這個問題的辦法是讓變量的賦值和取值發生在執行查詢的同一階段：

mysql> SET @rownum := 0; Query OK, 0 rows affected (0.00 sec)mysql> SELECT actor_id, @rownum AS rownum FROM actor WHERE (@rownum := @rownum + 1) <= 1; +----------+--------+ | actor_id | rownum | +----------+--------+ | 58 | 1 | +----------+--------+ 1 row in set, 1 warning (0.00 sec)

編寫偷懶的UNION

假設需要編寫一個UNION查詢，其第一個子查詢作為分支條件先執行，如果找到了匹配的行，則跳過第二個分支。在某些業務場景中確實會有這樣的需求，比如先在一個頻繁訪問的表中查找熱數據，找不到再去另外一個較少的表中查找冷數據。區分熱數據和冷數據是一個很好的提高緩存命中率的方法。

下面的查詢會在兩個地方查找一個用戶——一個主用戶表、一個長時間不活躍的用戶表，不活躍用戶表的目的是為了實現更高效的歸檔：

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

上面這個查詢是可以正常工作的，但是即使在users表中已經找到了記錄，上面的查詢還是會去歸檔表users_archived中再查找一次。我們可以用一個偷懶的UNION查詢來抑制這樣的數據返回，而且只有當第一個表中沒有數據時，我們才在第二個表中查詢。一旦在第一個表中找到記錄，就定義一個變量@found。我們通過在結果列中做一次賦值來實現，然后將賦值放在函數GREATEST中來避免返回額外的數據。為了明確我們的結果到底來自哪個表，我們新增了一個包含表名的列。最后需要在查詢的末尾將變量重置為NULL，保證遍歷時不干擾后面的結果。完成的查詢如下：

SELECT GREATEST(@found := -1, id) AS id, 'users' AS which_tbl FROM users WHERE id = 1 UNION ALLSELECt id, 'users_archived'FROM users_archived WHERE id = 1 AND @found IS NULL UNION ALLSELECT 1, 'reset' FROM DUAL WHERE (@found := NULL) IS NOT NULL;

用戶自定義變量的其他好處
不僅是在SELECT語句中，在其他任何類型的SQL語句中都可以對變量進行賦值。事實上，這也是用戶自定義變量最大的用途。例如，可以像前面使用子查詢的方式改進排名語句一樣來改進UPDATE語句。

不過，我們需要使用一些技巧來獲得我們希望的結果。有時，優化器會把變量當作一個編譯時常量來對待，而不是對其進行賦值。將函數放在類似于LEAST()這樣的函數中通常可以避免這樣的問題。另一個方法是在查詢被執行前檢查變量是否賦值。不同場景下有不同的辦法。

通過一些實踐，可以了解所有用戶自定義變量能夠做的有趣的事情，例如下面這些用法：

查詢運行時計算總數和平均值。
模擬GROUP語句中的函數FIRST()和LAST()。
對大量數據做一些數據計算。
計算一個大表的MD5散列值。
編寫一個樣本處理函數，當樣本中的數值超過某個邊界的時候將其變成0。
模擬讀/寫游標。
在SHOW語句的WHERE子句中加入變量值。

轉載于:https://www.cnblogs.com/beiluowuzheng/p/10150284.html

總結

以上是生活随笔為你收集整理的MySQL之查询性能优化（四）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

MySQL
性能

上一篇： rename 批量修改文件名简单用法
下一篇：查询ElasticSearch：用SQL