共計(jì) 4871 個(gè)字符，預(yù)計(jì)需要花費(fèi) 13 分鐘才能閱讀完成。

如何解析 MySQL 性能優(yōu)化中的 SQL 優(yōu)化，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

注：以 MySQL 為背景，很多內(nèi)容同時(shí)適用于其他關(guān)系型數(shù)據(jù)庫(kù)，需要有一些索引知識(shí)為基礎(chǔ)

優(yōu)化目標(biāo)

減少 IO 次數(shù)
IO 永遠(yuǎn)是數(shù)據(jù)庫(kù)最容易瓶頸的地方，這是由數(shù)據(jù)庫(kù)的職責(zé)所決定的，大部分?jǐn)?shù)據(jù)庫(kù)操作中超過(guò) 90% 的時(shí)間都是 IO 操作所占用的，減少 IO 次數(shù)是 SQL 優(yōu)化中需要第一優(yōu)先考慮，當(dāng)然，也是收效最明顯的優(yōu)化手段。

降低 CPU 計(jì)算
除了 IO 瓶頸之外，SQL 優(yōu)化中需要考慮的就是 CPU 運(yùn)算量的優(yōu)化了。order by, group by,distinct … 都是消耗 CPU 的大戶（這些操作基本上都是 CPU 處理內(nèi)存中的數(shù)據(jù)比較運(yùn)算）。當(dāng)我們的 IO 優(yōu)化做到一定階段之后，降低 CPU 計(jì)算也就成為了我們 SQL 優(yōu)化的重要目標(biāo)

優(yōu)化方法

改變 SQL 執(zhí)行計(jì)劃
明確了優(yōu)化目標(biāo)之后，我們需要確定達(dá)到我們目標(biāo)的方法。對(duì)于 SQL 語(yǔ)句來(lái)說(shuō)，達(dá)到上述 2 個(gè)目標(biāo)的方法其實(shí)只有一個(gè)，那就是改變 SQL 的執(zhí)行計(jì)劃，讓他盡量“少走彎路”，盡量通過(guò)各種“捷徑”來(lái)找到我們需要的數(shù)據(jù)，以達(dá)到“減少 IO 次數(shù)”和“降低 CPU 計(jì)算”的目標(biāo)

常見(jiàn)誤區(qū)

count(1)和 count(primary_key) 優(yōu)于 count(*)
很多人為了統(tǒng)計(jì)記錄條數(shù)，就使用 count(1) 和 count(primary_key) 而不是 count(*)，他們認(rèn)為這樣性能更好，其實(shí)這是一個(gè)誤區(qū)。對(duì)于有些場(chǎng)景，這樣做可能性能會(huì)更差，應(yīng)為數(shù)據(jù)庫(kù)對(duì) count(*) 計(jì)數(shù)操作做了一些特別的優(yōu)化。

count(column) 和 count(*) 是一樣的
這個(gè)誤區(qū)甚至在很多的資深工程師或者是 DBA 中都普遍存在，很多人都會(huì)認(rèn)為這是理所當(dāng)然的。實(shí)際上，count(column) 和 count(*) 是一個(gè)完全不一樣的操作，所代表的意義也完全不一樣。
count(column) 是表示結(jié)果集中有多少個(gè) column 字段不為空的記錄
count(*) 是表示整個(gè)結(jié)果集有多少條記錄

select a,b from … 比 select a,b,c from … 可以讓數(shù)據(jù)庫(kù)訪問(wèn)更少的數(shù)據(jù)量
這個(gè)誤區(qū)主要存在于大量的開(kāi)發(fā)人員中，主要原因是對(duì)數(shù)據(jù)庫(kù)的存儲(chǔ)原理不是太了解。
實(shí)際上，大多數(shù)關(guān)系型數(shù)據(jù)庫(kù)都是按照行（row）的方式存儲(chǔ)，而數(shù)據(jù)存取操作都是以一個(gè)固定大小的 IO 單元（被稱作 block 或者 page）為單位，一般為 4KB，8KB… 大多數(shù)時(shí)候，每個(gè) IO 單元中存儲(chǔ)了多行，每行都是存儲(chǔ)了該行的所有字段（lob 等特殊類型字段除外）。
所以，我們是取一個(gè)字段還是多個(gè)字段，實(shí)際上數(shù)據(jù)庫(kù)在表中需要訪問(wèn)的數(shù)據(jù)量其實(shí)是一樣的。
當(dāng)然，也有例外情況，那就是我們的這個(gè)查詢?cè)谒饕芯涂梢酝瓿桑簿褪钦f(shuō)當(dāng)只取 a,b 兩個(gè)字段的時(shí)候，不需要回表，而 c 這個(gè)字段不在使用的索引中，需要回表取得其數(shù)據(jù)。在這樣的情況下，二者的 IO 量會(huì)有較大差異。

order by 一定需要排序操作
我們知道索引數(shù)據(jù)實(shí)際上是有序的，如果我們的需要的數(shù)據(jù)和某個(gè)索引的順序一致，而且我們的查詢又通過(guò)這個(gè)索引來(lái)執(zhí)行，那么數(shù)據(jù)庫(kù)一般會(huì)省略排序操作，而直接將數(shù)據(jù)返回，因?yàn)閿?shù)據(jù)庫(kù)知道數(shù)據(jù)已經(jīng)滿足我們的排序需求了。
實(shí)際上，利用索引來(lái)優(yōu)化有排序需求的 SQL，是一個(gè)非常重要的優(yōu)化手段
延伸閱讀：MySQL ORDER BY 的實(shí)現(xiàn)分析  ，MySQL 中 GROUP BY 基本實(shí)現(xiàn)原理   以及  MySQL DISTINCT 的基本實(shí)現(xiàn)原理   這 3 篇文章中有更為深入的分析，尤其是第一篇

執(zhí)行計(jì)劃中有 filesort 就會(huì)進(jìn)行磁盤文件排序
有這個(gè)誤區(qū)其實(shí)并不能怪我們，而是因?yàn)?MySQL 開(kāi)發(fā)者在用詞方面的問(wèn)題。filesort 是我們?cè)谑褂?explain 命令查看一條 SQL 的執(zhí)行計(jì)劃的時(shí)候可能會(huì)看到在“Extra”一列顯示的信息。
實(shí)際上，只要一條 SQL 語(yǔ)句需要進(jìn)行排序操作，都會(huì)顯示“Using filesort”，這并不表示就會(huì)有文件排序操作。
延伸閱讀：理解  MySQL Explain 命令輸出中的 filesort，我在這里有更為詳細(xì)的介紹

基本原則

人為在 column_name 上通過(guò)轉(zhuǎn)換函數(shù)進(jìn)行轉(zhuǎn)換
直接導(dǎo)致 MySQL（實(shí)際上其他數(shù)據(jù)庫(kù)也會(huì)有同樣的問(wèn)題）無(wú)法使用索引，如果非要轉(zhuǎn)換，應(yīng)該在傳入的參數(shù)上進(jìn)行轉(zhuǎn)換

由數(shù)據(jù)庫(kù)自己進(jìn)行轉(zhuǎn)換
如果我們傳入的數(shù)據(jù)類型和字段類型不一致，同時(shí)我們又沒(méi)有做任何類型轉(zhuǎn)換處理，MySQL 可能會(huì)自己對(duì)我們的數(shù)據(jù)進(jìn)行類型轉(zhuǎn)換操作，也可能不進(jìn)行處理而交由存儲(chǔ)引擎去處理，這樣一來(lái)，就會(huì)出現(xiàn)索引無(wú)法使用的情況而造成執(zhí)行計(jì)劃問(wèn)題。

上面誤區(qū)中提到的通過(guò)利用索引來(lái)排序的方式進(jìn)行優(yōu)化

減少參與排序的記錄條數(shù)

非必要不對(duì)數(shù)據(jù)進(jìn)行排序

…

盡量少 join
MySQL 的優(yōu)勢(shì)在于簡(jiǎn)單，但這在某些方面其實(shí)也是其劣勢(shì)。MySQL 優(yōu)化器效率高，但是由于其統(tǒng)計(jì)信息的量有限，優(yōu)化器工作過(guò)程出現(xiàn)偏差的可能性也就更多。對(duì)于復(fù)雜的多表 Join，一方面由于其優(yōu)化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現(xiàn)離 Oracle 等關(guān)系型數(shù)據(jù)庫(kù)前輩還是有一定距離。但如果是簡(jiǎn)單的單表查詢，這一差距就會(huì)極小甚至在有些場(chǎng)景下要優(yōu)于這些數(shù)據(jù)庫(kù)前輩。

盡量少排序
排序操作會(huì)消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場(chǎng)景下會(huì)較大影響 SQL 的響應(yīng)時(shí)間。
對(duì)于 MySQL 來(lái)說(shuō)，減少排序有多種辦法，比如：

盡量避免 select *
很多人看到這一點(diǎn)后覺(jué)得比較難理解，上面不是在誤區(qū)中剛剛說(shuō) select 子句中字段的多少并不會(huì)影響到讀取的數(shù)據(jù)嗎？
是的，大多數(shù)時(shí)候并不會(huì)影響到 IO 量，但是當(dāng)我們還存在 order by 操作的時(shí)候，select 子句中的字段多少會(huì)在很大程度上影響到我們的排序效率，這一點(diǎn)可以通過(guò)我之前一篇介紹  MySQL ORDER BY 的實(shí)現(xiàn)分析   的文章中有較為詳細(xì)的介紹。
此外，上面誤區(qū)中不是也說(shuō)了，只是大多數(shù)時(shí)候是不會(huì)影響到 IO 量，當(dāng)我們的查詢結(jié)果僅僅只需要在索引中就能找到的時(shí)候，還是會(huì)極大減少 IO 量的。

盡量用 join 代替子查詢
雖然 Join 性能并不佳，但是和 MySQL 的子查詢比起來(lái)還是有非常大的性能優(yōu)勢(shì)。MySQL 的子查詢執(zhí)行計(jì)劃一直存在較大的問(wèn)題，雖然這個(gè)問(wèn)題已經(jīng)存在多年，但是到目前已經(jīng)發(fā)布的所有穩(wěn)定版本中都普遍存在，一直沒(méi)有太大改善。雖然官方也在很早就承認(rèn)這一問(wèn)題，并且承諾盡快解決，但是至少到目前為止我們還沒(méi)有看到哪一個(gè)版本較好的解決了這一問(wèn)題。

盡量少 or
當(dāng) where 子句中存在多個(gè)條件以“或”并存的時(shí)候，MySQL 的優(yōu)化器并沒(méi)有很好的解決其執(zhí)行計(jì)劃優(yōu)化問(wèn)題，再加上 MySQL 特有的 SQL 與 Storage 分層架構(gòu)方式，造成了其性能比較低下，很多時(shí)候使用 union all 或者是 union（必要的時(shí)候）的方式來(lái)代替“or”會(huì)得到更好的效果。

盡量用 union all 代替 union
union 和 union all 的差異主要是前者需要將兩個(gè)（或者多個(gè)）結(jié)果集合并后再進(jìn)行唯一性過(guò)濾操作，這就會(huì)涉及到排序，增加大量的 CPU 運(yùn)算，加大資源消耗及延遲。所以當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時(shí)候，盡量使用 union all 而不是 union。

盡量早過(guò)濾
這一優(yōu)化策略其實(shí)最常見(jiàn)于索引的優(yōu)化設(shè)計(jì)中（將過(guò)濾性更好的字段放得更靠前）。
在 SQL 編寫(xiě)中同樣可以使用這一原則來(lái)優(yōu)化一些 Join 的 SQL。比如我們?cè)诙鄠€(gè)表進(jìn)行分頁(yè)數(shù)據(jù)查詢的時(shí)候，我們最好是能夠在一個(gè)表上先過(guò)濾好數(shù)據(jù)分好頁(yè)，然后再用分好頁(yè)的結(jié)果集與另外的表 Join，這樣可以盡可能多的減少不必要的 IO 操作，大大節(jié)省 IO 操作所消耗的時(shí)間。

避免類型轉(zhuǎn)換
這里所說(shuō)的“類型轉(zhuǎn)換”是指 where 子句中出現(xiàn) column 字段的類型和傳入的參數(shù)類型不一致的時(shí)候發(fā)生的類型轉(zhuǎn)換：

優(yōu)先優(yōu)化高并發(fā)的 SQL，而不是執(zhí)行頻率低某些“大”SQL
對(duì)于破壞性來(lái)說(shuō)，高并發(fā)的 SQL 總是會(huì)比低頻率的來(lái)得大，因?yàn)楦卟l(fā)的 SQL 一旦出現(xiàn)問(wèn)題，甚至不會(huì)給我們?nèi)魏未⒌臋C(jī)會(huì)就會(huì)將系統(tǒng)壓跨。而對(duì)于一些雖然需要消耗大量 IO 而且響應(yīng)很慢的 SQL，由于頻率低，即使遇到，最多就是讓整個(gè)系統(tǒng)響應(yīng)慢一點(diǎn)，但至少可能撐一會(huì)兒，讓我們有緩沖的機(jī)會(huì)。

從全局出發(fā)優(yōu)化，而不是片面調(diào)整
SQL 優(yōu)化不能是單獨(dú)針對(duì)某一個(gè)進(jìn)行，而應(yīng)充分考慮系統(tǒng)中所有的 SQL，尤其是在通過(guò)調(diào)整索引優(yōu)化 SQL 的執(zhí)行計(jì)劃的時(shí)候，千萬(wàn)不能顧此失彼，因小失大。

盡可能對(duì)每一條運(yùn)行在數(shù)據(jù)庫(kù)中的 SQL 進(jìn)行 explain
優(yōu)化 SQL，需要做到心中有數(shù)，知道 SQL 的執(zhí)行計(jì)劃才能判斷是否有優(yōu)化余地，才能判斷是否存在執(zhí)行計(jì)劃問(wèn)題。在對(duì)數(shù)據(jù)庫(kù)中運(yùn)行的 SQL 進(jìn)行了一段時(shí)間的優(yōu)化之后，很明顯的問(wèn)題 SQL 可能已經(jīng)很少了，大多都需要去發(fā)掘，這時(shí)候就需要進(jìn)行大量的 explain 操作收集執(zhí)行計(jì)劃，并判斷是否需要進(jìn)行優(yōu)化。

作者：Sky.Jian | 可以任意轉(zhuǎn)載, 但轉(zhuǎn)載時(shí)務(wù)必以超鏈接形式標(biāo)明文章原始出處 和 作者信息 及   版權(quán)聲明  
鏈接：http://isky000.com/database/mysql-performance-tuning-sql

博主附：
第 8 點(diǎn)曾經(jīng)在 eygle 的 dba-notes 中提到過(guò)。（在 Oracle DB 中仍然有效）

主要問(wèn)題是通過(guò)連接訪問(wèn)新聞頁(yè)面及其緩慢，通常需要數(shù)十秒才能返回。

查詢 v$session 視圖，獲取進(jìn)程信息：
SQL select sid,serial#,username from v$session where username is not null;

對(duì)相應(yīng)會(huì)話啟用 sql_trace 跟蹤：
SQL exec dbms_system.set_sql_trace_in_session(7,284,true)
SQL exec dbms_system.set_sql_trace_in_session(11,214,true)
SQL exec dbms_system.set_sql_trace_in_session(16,1042,true)

執(zhí)行一段時(shí)間后，關(guān)閉：
SQL exec dbms_system.set_sql_trace_in_session(7,284,false)
SQL exec dbms_system.set_sql_trace_in_session(11,214,false)
SQL exec dbms_system.set_sql_trace_in_session(16,1042,false)

檢查 trace 文件，可以找到跟蹤過(guò)程中前臺(tái)執(zhí)行的 sql 調(diào)用，檢查發(fā)現(xiàn)以下語(yǔ)句是可疑的性能瓶頸點(diǎn)：
select auditstatus,categoryid,auditlevel from
categoryarticleassign a,category b where b.id=a.categoryid
and articleId=20030700400141 and auditstatus 0

這里的查詢是根據(jù) articleId 進(jìn)行新聞讀取的，但是注意到邏輯讀有 3892，這是一個(gè)較高的數(shù)字，這個(gè)內(nèi)容引起了我的注意。
接下來(lái)的類似查詢跟蹤得到的執(zhí)行計(jì)劃顯示，全表訪問(wèn)被執(zhí)行：

然后檢查表結(jié)構(gòu)，查看是否存在有效索引，以下輸出的 idx_articleid 給予 articleid 創(chuàng)建，但在以下查詢中都沒(méi)有被用到  
SQL select index_name,table_name,column_name from user_ind_columns
2 where table_name=upper(categoryarticleassign

檢查發(fā)現(xiàn)表結(jié)構(gòu)如下：

此時(shí)發(fā)現(xiàn) articleid 為 varchar2 類型，而查詢中給出的 articleid=20030700400141 是一個(gè) number 型，oracle 發(fā)生了潛在的數(shù)據(jù)類型轉(zhuǎn)換，從而導(dǎo)致了索引失效。

解決方法很簡(jiǎn)單，只要將 articleid 加上一個(gè)單引號(hào)即可。

看完上述內(nèi)容，你們掌握如何解析 MySQL 性能優(yōu)化中的 SQL 優(yōu)化的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝各位的閱讀！