SQL SERVER 的SQL語(yǔ)句優(yōu)化方式小結(jié)
2020-07-25 13:32:00
供稿：網(wǎng)友
1、SQL SERVER 2005的性能工具中有SQL Server Profiler和數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)，極好的東東，必須熟練使用。
2、查詢SQL語(yǔ)句時(shí)打開(kāi)“顯示估計(jì)的執(zhí)行計(jì)劃”，分析每個(gè)步驟的情況
3、初級(jí)做法，在CPU占用率高的時(shí)候，打開(kāi)SQL Server Profiler運(yùn)行，將跑下來(lái)的數(shù)據(jù)存到文件中，然后打開(kāi)數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)調(diào)用那個(gè)文件進(jìn)行分析，由SQL SERVER提供索引優(yōu)化建議。采納它的INDEX索引優(yōu)化部分。
4、但上面的做法經(jīng)常不會(huì)跑出你所需要的，在最近的優(yōu)化過(guò)程中CPU占用率極高，但根本提不出我需要的優(yōu)化建議，特別是有些語(yǔ)句是在存儲(chǔ)過(guò)程中并且多表聯(lián)立。這時(shí)就需要用中級(jí)做法來(lái)定位占用CPU高的語(yǔ)句。
5、還是運(yùn)行SQL Server Profiler，將運(yùn)行結(jié)果保存到某個(gè)庫(kù)的新表中（隨便起個(gè)名字系統(tǒng)會(huì)自己建）。讓它運(yùn)行一段時(shí)間，然后可以用
select top 100 * from test where textdata is not null order by duration desc
這個(gè)可以選出運(yùn)行時(shí)間長(zhǎng)的語(yǔ)句，在ORDER BY 中可以替換成CPU、READS，來(lái)選出CPU占用時(shí)間長(zhǎng)和讀數(shù)據(jù)過(guò)多的語(yǔ)句。
定位出問(wèn)題的語(yǔ)句之后就可以具體分析了。有些語(yǔ)句在執(zhí)行計(jì)劃中很明顯可以看出問(wèn)題所在。
常見(jiàn)的有沒(méi)有建索引或索引建立不合理，會(huì)出現(xiàn)table scan或index scan，凡是看到SCAN，就意味著會(huì)做全表或全索引掃描，這是帶來(lái)的必然是讀次數(shù)過(guò)多。我們期望看到的是seek或鍵查找。
6、怎么看SQL語(yǔ)句執(zhí)行的計(jì)劃很有講究，初學(xué)者會(huì)過(guò)于關(guān)注里面顯示的開(kāi)銷(xiāo)比例，而實(shí)際上這個(gè)有時(shí)會(huì)誤導(dǎo)。我在實(shí)際優(yōu)化過(guò)程中就被發(fā)現(xiàn)，一個(gè)index scan的執(zhí)行項(xiàng)開(kāi)銷(xiāo)只占25%，另一個(gè)鍵查找的開(kāi)銷(xiāo)占50%，而鍵查找部分根本沒(méi)有可優(yōu)化的，SEEK謂詞就是ID=XXX這個(gè)建立在主鍵上的查找。而仔細(xì)分析可以看到，后者CPU開(kāi)銷(xiāo)0.00015，I/O開(kāi)銷(xiāo)0.0013。而前者呢，CPU開(kāi)銷(xiāo)1.4xxxx,I/O開(kāi)銷(xiāo)也遠(yuǎn)大于后者。因此，優(yōu)化重點(diǎn)應(yīng)該放在前者。
7、如何優(yōu)化單個(gè)部分，一個(gè)復(fù)雜的SQL語(yǔ)句，SQL SERVER會(huì)很聰明地重組WHERE后的語(yǔ)句，試圖匹配索引。選中帶優(yōu)化的步驟，選擇旁邊的‘屬性”，再選擇其中的“謂詞”，將其中部分復(fù)制下來(lái)，這部分就是分解后的WHERE 語(yǔ)句，然后在查詢界面中select * from 表 where 剛才復(fù)制下來(lái)的“謂詞”。這個(gè)就是需要優(yōu)化的部分，既然已經(jīng)走到這一步了，大部分人應(yīng)該能手動(dòng)建立索引了，因?yàn)檫@里的WHERE語(yǔ)句比之前的肯定簡(jiǎn)單不少。（在我項(xiàng)目中原始SELECT語(yǔ)句的WHERE部分有10個(gè)條件組合，涉及6個(gè)字段，提取出來(lái)要優(yōu)化的部分就4個(gè)條件，涉及到3個(gè)字段。新的索引建立后，CPU占用率一下子就降低了，而且新建立的索引涉及的字段屬于不常UPDATE的部分，頻繁的讀寫(xiě)操作不會(huì)影響UPDATE的效率）
8、以上就是優(yōu)化的思路，最后提一些優(yōu)化過(guò)程或是系統(tǒng)設(shè)計(jì)時(shí)中需要注意的問(wèn)題。
A、盡量避免用select * from xxx where abc like '%xxx'類(lèi)型的模糊查詢，因?yàn)?在前面的話是無(wú)法利用到索引，必然會(huì)引起全量SCAN操作。應(yīng)該找尋替代方式或用前置條件語(yǔ)句把like查找之前的行數(shù)減到最低。
B、盡量避免對(duì)大表數(shù)據(jù)進(jìn)行select top n * from xxx where xxxx order by newid()的取隨機(jī)記錄的操作。newid()操作會(huì)讀全量數(shù)據(jù)后再排序。也會(huì)占用大量CPU和讀操作。可以考慮用RAND()函數(shù)來(lái)實(shí)現(xiàn)，這方面我還在研究中，對(duì)于整表操作比較好弄，比如id>=(select max(id) from table)*rand()。但如果取局部數(shù)據(jù)的隨機(jī)記錄還需要思量。
C、在SQL Server Profiler記錄中會(huì)看到Audit Logout會(huì)占用大量CPU和讀寫(xiě)等操作。查了一些資料稱(chēng)是某個(gè)鏈接在某次連接過(guò)程中執(zhí)行SQL語(yǔ)句產(chǎn)生的總數(shù)，不用過(guò)于擔(dān)心。看下來(lái)的確似乎這樣，很多Audit Logout的CPU和IO消耗量和之前優(yōu)化的語(yǔ)句基本一致。所以在第5點(diǎn)我提的SQL語(yǔ)句用textdata is not null條件把Audit Logout給隱去。
D、兩個(gè)不同字段OR語(yǔ)句會(huì)導(dǎo)致全表掃描。例如 where m=1 or n=1。如果建立一個(gè)索引是m和n，同樣會(huì)引起scan，解決方法是給m和n分別建立索引。測(cè)試12萬(wàn)條數(shù)據(jù)的表，索引建立錯(cuò)誤的情況下IO開(kāi)銷(xiāo)高達(dá) 10.xxx，分別建立索引后，全部變成0.003，這個(gè)反差是非常巨大的。雖然會(huì)引起INSERT操作的性能問(wèn)題，但畢竟大部分瓶頸在SELECT的讀操作上。
E、索引查找（Index Seek)和索引掃描(Index Scan)，我們需要的是前者，而引起后者的原因通常是某個(gè)索引里的字段多余要查找的，例如索引建立在A和B兩個(gè)字段，而我們只要查找A，則會(huì)導(dǎo)致 INDEX SCAN。建議針對(duì)單獨(dú)的A建立索引，以形成索引查找。
F、對(duì)于小表不建議建立索引，特別是幾百的數(shù)據(jù)量，只有上千上萬(wàn)級(jí)別的數(shù)據(jù)建立索引才有效果。
數(shù)據(jù)庫(kù)優(yōu)化是很深的學(xué)問(wèn)，在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)就應(yīng)該注意，特別是最后提到的A、B兩點(diǎn)，盡可能在設(shè)計(jì)初期避免。
