MySQL優(yōu)化SQL語(yǔ)句的技巧

在面對(duì)不夠優(yōu)化、或者性能極差的SQL語(yǔ)句時(shí)，我們通常的想法是將重構(gòu)這個(gè)SQL語(yǔ)句，讓其查詢的結(jié)果集和原來(lái)保持一樣，并且希望SQL性能得以提升。而在重構(gòu)SQL時(shí)，一般都有一定方法技巧可供參考，本文將介紹如何通過(guò)這些技巧方法來(lái)重構(gòu)SQL。

一、分解SQL

有時(shí)候?qū)τ谝粋€(gè)復(fù)雜SQL，我們首先想到的是是否需要將一個(gè)復(fù)雜SQL分解成多個(gè)簡(jiǎn)單SQL，來(lái)完成相同業(yè)務(wù)處理結(jié)果。

在以前，大家總是強(qiáng)調(diào)需要數(shù)據(jù)庫(kù)層來(lái)完成盡可能的工作，這也就不難理解在一些老的產(chǎn)品、項(xiàng)目中時(shí)常會(huì)看見(jiàn)很多超級(jí)復(fù)雜、超級(jí)長(zhǎng)的SQL語(yǔ)句，這樣做的邏輯在以前認(rèn)為多次交互，在網(wǎng)絡(luò)帶寬、程序與數(shù)據(jù)庫(kù)間網(wǎng)絡(luò)通信等方面是一件代價(jià)很高的事情。然后在現(xiàn)在，無(wú)論是帶寬還是延遲，網(wǎng)絡(luò)速度比以前要快的很多，多次交互也沒(méi)有太大的問(wèn)題。即使在一個(gè)通用服務(wù)器上，也能夠運(yùn)行每秒超過(guò)10萬(wàn)的查詢，所以運(yùn)行多個(gè)小查詢現(xiàn)在已經(jīng)不是大問(wèn)題了。

復(fù)雜SQL的分解，在面對(duì)超級(jí)復(fù)雜SQL語(yǔ)句時(shí)，性能提升尤為明顯。所以，在面對(duì)超級(jí)復(fù)雜SQL語(yǔ)句，并且存在性能問(wèn)題時(shí)，推薦分解為小查詢來(lái)進(jìn)行優(yōu)化。

不過(guò)，在應(yīng)用設(shè)計(jì)的時(shí)候，如果一個(gè)查詢能夠勝任并且不會(huì)產(chǎn)生性能問(wèn)題，這時(shí)完全可以用一個(gè)稍微復(fù)雜的SQL來(lái)完成的，倘若再死板的強(qiáng)制拆分成多個(gè)小查詢是不明智的。

在當(dāng)今很多高性能的應(yīng)用系統(tǒng)中，都是極力推薦使用單表操作，然后將單表查詢結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)，以滿足復(fù)雜業(yè)務(wù)的查詢需求。一個(gè)SQL可以搞定事情，為何要分開(kāi)來(lái)寫(xiě)，而且還得在應(yīng)用程序中多次執(zhí)行SQL查詢，再進(jìn)行結(jié)果集的關(guān)聯(lián)，這到底為什么要這么做呢？

乍一看，這樣做復(fù)雜不說(shuō)而且沒(méi)有什么好處，原本一條查詢，這樣卻變成了多條查詢。事實(shí)上，這樣分解有如下的優(yōu)勢(shì)：

• 讓緩存更高效。在應(yīng)用程序中，可以很方便地緩存單表查詢結(jié)果對(duì)應(yīng)的結(jié)果對(duì)象，便于后續(xù)任何時(shí)候可以直接從結(jié)果對(duì)象中獲取數(shù)據(jù)。
• 分解查詢后，執(zhí)行單個(gè)查詢可以減少表鎖的競(jìng)爭(zhēng)。
• 在程序應(yīng)用層做關(guān)聯(lián)，可以更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分，更容易做到高性能和可擴(kuò)展。
• 單表查詢效率高于多表復(fù)雜查詢。
• 減少冗余記錄的查詢。在程序應(yīng)用層關(guān)聯(lián)，意味著對(duì)于某條記錄應(yīng)用只需要查詢一次，而在數(shù)據(jù)庫(kù)中做關(guān)聯(lián)查詢，則可能需要重復(fù)地訪問(wèn)一部分?jǐn)?shù)據(jù)記錄。從這點(diǎn)來(lái)看，這樣的重構(gòu)還可能減少網(wǎng)絡(luò)和內(nèi)存的消耗。
  

二、查詢切分

有時(shí)候?qū)τ谝粋€(gè)大查詢，即：結(jié)果集很大的查詢，我們需要采用“分而治之”的思想，將大查詢切分為小查詢，每個(gè)查詢功能完全一樣，只是完成一小部分，每次只返回一小部分查詢結(jié)果。通俗來(lái)講，就是對(duì)where條件的過(guò)濾范圍進(jìn)行切分，每次只查詢其中一部分?jǐn)?shù)據(jù)，即：類(lèi)似于分頁(yè)查詢。

這樣做，不管對(duì)于SQL查詢本身，還是對(duì)于上層業(yè)務(wù)來(lái)說(shuō)，都是很小的開(kāi)銷(xiāo)。最典型的的案例就是分頁(yè)查詢，目前各類(lèi)框架都有了很好的支持，如：MyBatis等，只需在實(shí)際使用時(shí)稍加留意就可避免。

三、執(zhí)行計(jì)劃

使用執(zhí)行計(jì)劃EXPLAIN關(guān)鍵字，可以使我們知道MySQL是如何執(zhí)行SQL語(yǔ)句的，這樣可以幫助我們分析我們的查詢語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。EXPLAIN的查詢結(jié)果還會(huì)告訴我們索引主鍵是如何被利用的，數(shù)據(jù)表是如何被搜索或排序的…等等。

語(yǔ)法格式是：

EXPLAIN SELECT語(yǔ)句;

通過(guò)執(zhí)行計(jì)劃結(jié)果，將會(huì)指導(dǎo)我們進(jìn)一步來(lái)重構(gòu)SQL語(yǔ)句，如：增加索引、調(diào)整索引順序、避免使用某些函數(shù)等等。

關(guān)于執(zhí)行計(jì)劃，后續(xù)章節(jié)將會(huì)單獨(dú)詳細(xì)講解。

四、遵守原則

在平時(shí)寫(xiě)SQL時(shí)，養(yǎng)成好的習(xí)慣，多加留意，很大程度上就會(huì)避免一些SQL性能問(wèn)題。匯總?cè)缦拢?/p>

• 永遠(yuǎn)為每張表設(shè)置一個(gè)ID主鍵。
• 避免使用SELECT *。
• 為搜索字段建立索引。
• 在Join表的時(shí)候使用對(duì)應(yīng)類(lèi)型的列，并將其索引。
• 盡可能的使用NOT NULL。
• 越小的列會(huì)越快。
• 當(dāng)只要一行數(shù)據(jù)時(shí)使用LIMIT 1。
• 操作符的優(yōu)化，盡量不采用不利于索引的操作符，目的就是為了避免全表掃描。
  

             1）in 和not in慎用，盡量用between代替in，用 not exists 代替 not in
             2）is null和is not null慎用
             3）!=或>操作符能不用就不用，否則將使引擎放棄使用索引而進(jìn)行全表掃描。

五、使用查詢緩存

當(dāng)有很多相同的查詢被執(zhí)行了多次的時(shí)候，這些查詢結(jié)果會(huì)被放入一個(gè)緩存中，這樣后續(xù)的相同查詢就不用操作而直接訪問(wèn)緩存結(jié)果了。

MySQL查詢緩存保存查詢返回的完整結(jié)果。當(dāng)查詢命中該緩存，MySQL會(huì)like返回結(jié)果，跳過(guò)了解析、優(yōu)化和執(zhí)行截?cái)唷?/p>

這是提高查詢性能最有效的方法之一，而且這是被MySQL引擎處理的，通常MySQL默認(rèn)是不開(kāi)啟查詢緩存的，需要手動(dòng)開(kāi)啟。

查詢緩存對(duì)應(yīng)用程序是完全透明的。應(yīng)用程序無(wú)需關(guān)心MySQL是通過(guò)查詢返回的還是實(shí)際執(zhí)行返回的結(jié)果。事實(shí)上，這兩種方式執(zhí)行的結(jié)果是完全相同的。換句話說(shuō)，查詢緩存無(wú)需使用任何語(yǔ)法。

隨著現(xiàn)在的通用服務(wù)器越來(lái)越強(qiáng)大，查詢緩存被發(fā)現(xiàn)是一個(gè)影響服務(wù)器擴(kuò)展性的因素。它可能成為整個(gè)服務(wù)器的資源競(jìng)爭(zhēng)單點(diǎn)，在多核服務(wù)器上還可能導(dǎo)致服務(wù)器僵死。所以大部分時(shí)候應(yīng)該默認(rèn)關(guān)閉查詢緩存，如果查詢緩存作用很大的話，可以配置個(gè)幾十兆的小緩存空間。（在選擇時(shí)，需要進(jìn)行權(quán)衡）

關(guān)于查詢緩存有如下參數(shù)可供配置：

• query_cache_type

是否打開(kāi)查詢緩存?？梢栽O(shè)置OFF、ON、DEMAND，DEMAND表示只有在查詢語(yǔ)句中明確寫(xiě)入sql_cache的語(yǔ)句才放入查詢緩存。

• query_cache_size
  

查詢緩存使用的總內(nèi)存空間，單位是字節(jié)。這個(gè)值必須是1024的整倍數(shù)，否則實(shí)際分配的數(shù)據(jù)會(huì)和指定的大小有區(qū)別。

• query_cache_min_res_unit
  

在查詢緩存中分配內(nèi)存塊時(shí)的最小單位。

• query_cache_limit

緩存的最大查詢結(jié)果。如果查詢結(jié)果大于這個(gè)值，則不會(huì)被緩存。因?yàn)椴樵兙彺嬖跀?shù)據(jù)生成的時(shí)候就開(kāi)始嘗試緩存數(shù)據(jù)，所以只有當(dāng)結(jié)果全部返回后，MySQL才知道查詢結(jié)果是否超出限制。

關(guān)于查詢緩存，后續(xù)章節(jié)將會(huì)單獨(dú)詳細(xì)講解。

以上就是MySQL優(yōu)化SQL語(yǔ)句的技巧的詳細(xì)內(nèi)容，更多關(guān)于MySQL優(yōu)化sql語(yǔ)句的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！