這篇文章將為大家詳細(xì)講解有關(guān)MySQL數(shù)據(jù)庫性能優(yōu)化之SQL優(yōu)化的示例分析，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

在松江等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)、成都外貿(mào)網(wǎng)站建設(shè) 網(wǎng)站設(shè)計(jì)制作按需制作,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),成都品牌網(wǎng)站建設(shè),網(wǎng)絡(luò)營銷推廣,成都外貿(mào)網(wǎng)站制作,松江網(wǎng)站建設(shè)費(fèi)用合理。

注：這篇文章是以 MySQL 為背景，很多內(nèi)容同時(shí)適用于其他關(guān)系型數(shù)據(jù)庫，需要有一些索引知識(shí)為基礎(chǔ)。
優(yōu)化目標(biāo)
1.減少 IO 次數(shù)
IO永遠(yuǎn)是數(shù)據(jù)庫最容易瓶頸的地方，這是由數(shù)據(jù)庫的職責(zé)所決定的，大部分?jǐn)?shù)據(jù)庫操作中超過90%的時(shí)間都是 IO 操作所占用的，減少 IO 次數(shù)是 SQL 優(yōu)化中需要第一優(yōu)先考慮，當(dāng)然，也是收效最明顯的優(yōu)化手段。
2.降低 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)的方法。對于 SQL 語句來說，達(dá)到上述2個(gè)目標(biāo)的方法其實(shí)只有一個(gè)，那就是改變 SQL 的執(zhí)行計(jì)劃，讓他盡量“少走彎路”，盡量通過各種“捷徑”來找到我們需要的數(shù)據(jù)，以達(dá)到 “減少 IO 次數(shù)” 和 “降低 CPU 計(jì)算” 的目標(biāo)
常見誤區(qū)
1.count(1)和count(primary_key) 優(yōu)于 count(*)
　很多人為了統(tǒng)計(jì)記錄條數(shù)，就使用 count(1) 和 count(primary_key) 而不是 count(*) ，他們認(rèn)為這樣性能更好，其實(shí)這是一個(gè)誤區(qū)。對于有些場景，這樣做可能性能會(huì)更差，應(yīng)為數(shù)據(jù)庫對 count(*) 計(jì)數(shù)操作做了一些特別的優(yōu)化。
2.count(column) 和 count(*) 是一樣的
這個(gè)誤區(qū)甚至在很多的資深工程師或者是 DBA 中都普遍存在，很多人都會(huì)認(rèn)為這是理所當(dāng)然的。實(shí)際上，count(column) 和 count(*) 是一個(gè)完全不一樣的操作，所代表的意義也完全不一樣。
count(column) 是表示結(jié)果集中有多少個(gè)column字段不為空的記錄
count(*) 是表示整個(gè)結(jié)果集有多少條記錄
3.select a,b from … 比 select a,b,c from … 可以讓數(shù)據(jù)庫訪問更少的數(shù)據(jù)量
這個(gè)誤區(qū)主要存在于大量的開發(fā)人員中，主要原因是對數(shù)據(jù)庫的存儲(chǔ)原理不是太了解。
　實(shí)際上，大多數(shù)關(guān)系型數(shù)據(jù)庫都是按照行(row)的方式存儲(chǔ)，而數(shù)據(jù)存取操作都是以一個(gè)固定大小的IO單元(被稱作 block 或者 page)為單位，一般為4KB，8KB… 大多數(shù)時(shí)候，每個(gè)IO單元中存儲(chǔ)了多行，每行都是存儲(chǔ)了該行的所有字段(lob等特殊類型字段除外)。
所以，我們是取一個(gè)字段還是多個(gè)字段，實(shí)際上數(shù)據(jù)庫在表中需要訪問的數(shù)據(jù)量其實(shí)是一樣的。
當(dāng)然，也有例外情況，那就是我們的這個(gè)查詢在索引中就可以完成，也就是說當(dāng)只取 a,b兩個(gè)字段的時(shí)候，不需要回表，而c這個(gè)字段不在使用的索引中，需要回表取得其數(shù)據(jù)。在這樣的情況下，二者的IO量會(huì)有較大差異。
4.order by 一定需要排序操作
我們知道索引數(shù)據(jù)實(shí)際上是有序的，如果我們的需要的數(shù)據(jù)和某個(gè)索引的順序一致，而且我們的查詢又通過這個(gè)索引來執(zhí)行，那么數(shù)據(jù)庫一般會(huì)省略排序操作，而直接將數(shù)據(jù)返回，因?yàn)閿?shù)據(jù)庫知道數(shù)據(jù)已經(jīng)滿足我們的排序需求了。
實(shí)際上，利用索引來優(yōu)化有排序需求的 SQL，是一個(gè)非常重要的優(yōu)化手段
延伸閱讀：MySQL ORDER BY 的實(shí)現(xiàn)分析，MySQL 中 GROUP BY 基本實(shí)現(xiàn)原理以及 MySQL DISTINCT 的基本實(shí)現(xiàn)原理這3篇文章中有更為深入的分析，尤其是第一篇
5.執(zhí)行計(jì)劃中有 filesort 就會(huì)進(jìn)行磁盤文件排序
有這個(gè)誤區(qū)其實(shí)并不能怪我們，而是因?yàn)?MySQL 開發(fā)者在用詞方面的問題。filesort 是我們在使用 explain 命令查看一條 SQL 的執(zhí)行計(jì)劃的時(shí)候可能會(huì)看到在 “Extra” 一列顯示的信息。
實(shí)際上，只要一條 SQL 語句需要進(jìn)行排序操作，都會(huì)顯示“Using filesort”，這并不表示就會(huì)有文件排序操作。
延伸閱讀：理解 MySQL Explain 命令輸出中的filesort，我在這里有更為詳細(xì)的介紹。

基本原則
1.盡量少 join
　MySQL 的優(yōu)勢在于簡單，但這在某些方面其實(shí)也是其劣勢。MySQL 優(yōu)化器效率高，但是由于其統(tǒng)計(jì)信息的量有限，優(yōu)化器工作過程出現(xiàn)偏差的可能性也就更多。對于復(fù)雜的多表 Join，一方面由于其優(yōu)化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現(xiàn)離 Oracle 等關(guān)系型數(shù)據(jù)庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會(huì)極小甚至在有些場景下要優(yōu)于這些數(shù)據(jù)庫前輩。
2.盡量少排序
排序操作會(huì)消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場景下會(huì)較大影響 SQL 的響應(yīng)時(shí)間。
對于MySQL來說，減少排序有多種辦法，比如：
上面誤區(qū)中提到的通過利用索引來排序的方式進(jìn)行優(yōu)化
減少參與排序的記錄條數(shù)
非必要不對數(shù)據(jù)進(jìn)行排序
…
3.盡量避免 select *
很多人看到這一點(diǎn)后覺得比較難理解，上面不是在誤區(qū)中剛剛說 select 子句中字段的多少并不會(huì)影響到讀取的數(shù)據(jù)嗎?
是的，大多數(shù)時(shí)候并不會(huì)影響到 IO 量，但是當(dāng)我們還存在 order by 操作的時(shí)候，select 子句中的字段多少會(huì)在很大程度上影響到我們的排序效率，這一點(diǎn)可以通過我之前一篇介紹 MySQL ORDER BY 的實(shí)現(xiàn)分析的文章中有較為詳細(xì)的介紹。
此外，上面誤區(qū)中不是也說了，只是大多數(shù)時(shí)候是不會(huì)影響到 IO 量，當(dāng)我們的查詢結(jié)果僅僅只需要在索引中就能找到的時(shí)候，還是會(huì)極大減少 IO 量的。
4.盡量用 join 代替子查詢
　雖然 Join 性能并不佳，但是和 MySQL 的子查詢比起來還是有非常大的性能優(yōu)勢。MySQL 的子查詢執(zhí)行計(jì)劃一直存在較大的問題，雖然這個(gè)問題已經(jīng)存在多年，但是到目前已經(jīng)發(fā)布的所有穩(wěn)定版本中都普遍存在，一直沒有太大改善。雖然官方也在很早就 承認(rèn)這一問題，并且承諾盡快解決，但是至少到目前為止我們還沒有看到哪一個(gè)版本較好的解決了這一問題。
5.盡量少 or
　當(dāng) where 子句中存在多個(gè)條件以“或”并存的時(shí)候，MySQL 的優(yōu)化器并沒有很好的解決其執(zhí)行計(jì)劃優(yōu)化問題，再加上 MySQL 特有的 SQL 與 Storage 分層架構(gòu)方式，造成了其性能比較低下，很多時(shí)候使用 union all 或者是union(必要的時(shí)候)的方式來代替“or”會(huì)得到更好的效果。
6.盡量用 union all 代替 union
　union 和 union all 的差異主要是前者需要將兩個(gè)(或者多個(gè))結(jié)果集合并后再進(jìn)行唯一性過濾操作，這就會(huì)涉及到排序，增加大量的 CPU 運(yùn)算，加大資源消耗及延遲。所以當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時(shí)候，盡量使用 union all 而不是 union。
7.盡量早過濾
這一優(yōu)化策略其實(shí)最常見于索引的優(yōu)化設(shè)計(jì)中(將過濾性更好的字段放得更靠前)。
　在 SQL 編寫中同樣可以使用這一原則來優(yōu)化一些 Join 的 SQL。比如我們在多個(gè)表進(jìn)行分頁數(shù)據(jù)查詢的時(shí)候，我們最好是能夠在一個(gè)表上先過濾好數(shù)據(jù)分好頁，然后再用分好頁的結(jié)果集與另外的表 Join，這樣可以盡可能多的減少不必要的 IO 操作，大大節(jié)省 IO 操作所消耗的時(shí)間。
8.避免類型轉(zhuǎn)換
這里所說的“類型轉(zhuǎn)換”是指 where 子句中出現(xiàn) column 字段的類型和傳入的參數(shù)類型不一致的時(shí)候發(fā)生的類型轉(zhuǎn)換：
人為在column_name 上通過轉(zhuǎn)換函數(shù)進(jìn)行轉(zhuǎn)換
直接導(dǎo)致 MySQL(實(shí)際上其他數(shù)據(jù)庫也會(huì)有同樣的問題)無法使用索引，如果非要轉(zhuǎn)換，應(yīng)該在傳入的參數(shù)上進(jìn)行轉(zhuǎn)換
由數(shù)據(jù)庫自己進(jìn)行轉(zhuǎn)換
如果我們傳入的數(shù)據(jù)類型和字段類型不一致，同時(shí)我們又沒有做任何類型轉(zhuǎn)換處理，MySQL 可能會(huì)自己對我們的數(shù)據(jù)進(jìn)行類型轉(zhuǎn)換操作，也可能不進(jìn)行處理而交由存儲(chǔ)引擎去處理，這樣一來，就會(huì)出現(xiàn)索引無法使用的情況而造成執(zhí)行計(jì)劃問題。
9.優(yōu)先優(yōu)化高并發(fā)的 SQL，而不是執(zhí)行頻率低某些“大”SQL
　對于破壞性來說，高并發(fā)的 SQL 總是會(huì)比低頻率的來得大，因?yàn)楦卟l(fā)的 SQL 一旦出現(xiàn)問題，甚至不會(huì)給我們?nèi)魏未⒌臋C(jī)會(huì)就會(huì)將系統(tǒng)壓跨。而對于一些雖然需要消耗大量 IO 而且響應(yīng)很慢的 SQL，由于頻率低，即使遇到，最多就是讓整個(gè)系統(tǒng)響應(yīng)慢一點(diǎn)，但至少可能撐一會(huì)兒，讓我們有緩沖的機(jī)會(huì)。
10.從全局出發(fā)優(yōu)化，而不是片面調(diào)整
SQL 優(yōu)化不能是單獨(dú)針對某一個(gè)進(jìn)行，而應(yīng)充分考慮系統(tǒng)中所有的 SQL，尤其是在通過調(diào)整索引優(yōu)化 SQL 的執(zhí)行計(jì)劃的時(shí)候，千萬不能顧此失彼，因小失大。
11.盡可能對每一條運(yùn)行在數(shù)據(jù)庫中的SQL進(jìn)行 explain
　優(yōu)化 SQL，需要做到心中有數(shù)，知道 SQL 的執(zhí)行計(jì)劃才能判斷是否有優(yōu)化余地，才能判斷是否存在執(zhí)行計(jì)劃問題。在對數(shù)據(jù)庫中運(yùn)行的 SQL 進(jìn)行了一段時(shí)間的優(yōu)化之后，很明顯的問題 SQL 可能已經(jīng)很少了，大多都需要去發(fā)掘，這時(shí)候就需要進(jìn)行大量的 explain 操作收集執(zhí)行計(jì)劃，并判斷是否需要進(jìn)行優(yōu)化。

關(guān)于“MySQL數(shù)據(jù)庫性能優(yōu)化之SQL優(yōu)化的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請把它分享出去讓更多的人看到。

網(wǎng)站欄目：MySQL數(shù)據(jù)庫性能優(yōu)化之SQL優(yōu)化的示例分析
文章出自：http://www.chinadenli.net/article42/igpihc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站建設(shè)、做網(wǎng)站、小程序開發(fā)、用戶體驗(yàn)、自適應(yīng)網(wǎng)站、移動(dòng)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)