如何編寫(xiě)更好的SQL查詢(xún)終極攻略-第一部分

2024-09-07 22:13:01

字體：大中小

供稿：網(wǎng)友

　　結(jié)構(gòu)化查詢(xún)語(yǔ)言（SQL）是數(shù)據(jù)挖掘分析行業(yè)不可或缺的一項(xiàng)技能，總的來(lái)說(shuō)，學(xué)習(xí)這個(gè)技能是比較容易的。對(duì)于SQL來(lái)說(shuō)，編寫(xiě)查詢(xún)語(yǔ)句只是第一步，確保查詢(xún)語(yǔ)句高效并且適合于你的數(shù)據(jù)庫(kù)操作工作，才是最重要的。這個(gè)教程將會(huì)提供給你一些步驟，來(lái)評(píng)估你的查詢(xún)語(yǔ)句。

　　為什么要學(xué)SQL？
　　尋找數(shù)據(jù)挖掘分析行業(yè)的工作，SQL是最需要的技能之一，不論是申請(qǐng)數(shù)據(jù)分析工作、數(shù)據(jù)引擎工作、數(shù)據(jù)挖掘分析或者其它工作。在O'Reilly發(fā)布的《2016數(shù)據(jù)科學(xué)從業(yè)者薪酬報(bào)告》中，有70％的受訪(fǎng)者證實(shí)了這一點(diǎn)，表示他們需要在專(zhuān)業(yè)環(huán)境中使用SQL。此外，本次調(diào)查中，SQL遠(yuǎn)勝于R（57％）和Python（54％）等編程語(yǔ)言。所以在數(shù)據(jù)挖掘分析領(lǐng)域，SQL是必備技能。

　　我們分析一下SQL從1970s早期開(kāi)發(fā)出，到現(xiàn)在還經(jīng)久不衰的原因：

　　一、公司基本都將數(shù)據(jù)存儲(chǔ)在關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)（RDBMS）或關(guān)系數(shù)據(jù)流管理系統(tǒng)（RDSMS）中，所以需要使用SQL來(lái)實(shí)現(xiàn)訪(fǎng)問(wèn)。SQL是通用的數(shù)據(jù)語(yǔ)言，可以使用SQL和幾乎其它任何數(shù)據(jù)庫(kù)進(jìn)行交互，甚至可以在本地建立自己的數(shù)據(jù)庫(kù)！

　　二、只有少量的SQL實(shí)現(xiàn)沒(méi)有遵循標(biāo)準(zhǔn)，在供應(yīng)商之間不兼容。因此，了解SQL標(biāo)準(zhǔn)是在數(shù)據(jù)挖掘分析行業(yè)立足的必要要求。

　　三、最重要的是SQL也被更新的技術(shù)所接受，例如Hive或者Spark SQL。Hive是一個(gè)用于查詢(xún)和管理大型數(shù)據(jù)集的類(lèi)似于SQL的查詢(xún)語(yǔ)言界面；Spark SQL可用于執(zhí)行SQL查詢(xún)。

　　為了提高SQL查詢(xún)的性能，首先需要知道，運(yùn)行查詢(xún)時(shí)，內(nèi)部會(huì)發(fā)生什么。

　　以下時(shí)查詢(xún)執(zhí)行的過(guò)程：

　　首先，將查詢(xún)解析成“解析樹(shù)”; 分析查詢(xún)是否滿(mǎn)足語(yǔ)法和語(yǔ)義要求。解析器將會(huì)創(chuàng)建一個(gè)輸入查詢(xún)的內(nèi)部表示，然后將此輸出傳遞給重寫(xiě)引擎。
　　然后，優(yōu)化器的任務(wù)是為給定的查詢(xún)，尋找最佳執(zhí)行或查詢(xún)計(jì)劃。執(zhí)行計(jì)劃準(zhǔn)確地定義了每個(gè)操作所使用的算法，以及如何協(xié)調(diào)操作的執(zhí)行。
　　最后，為了找到最佳的執(zhí)行計(jì)劃，優(yōu)化器會(huì)列舉所有可能的執(zhí)行計(jì)劃，并確定每個(gè)計(jì)劃的質(zhì)量或成本，以便獲取有關(guān)當(dāng)前數(shù)據(jù)庫(kù)狀態(tài)的信息，最后選擇最佳的執(zhí)行計(jì)劃。由于查詢(xún)優(yōu)化器可能不完善，因此數(shù)據(jù)庫(kù)用戶(hù)和管理員有時(shí)需要手動(dòng)檢查并調(diào)整優(yōu)化器生成的計(jì)劃，以便獲得更好的性能。
　　現(xiàn)在你已經(jīng)清楚了什么才是好的執(zhí)行計(jì)劃。

　　正如前面了解到的，計(jì)劃的成本質(zhì)量起著重要的作用。更具體地說(shuō)，評(píng)估計(jì)劃所需的磁盤(pán)I / O數(shù)量，計(jì)劃的CPU花銷(xiāo)以及數(shù)據(jù)庫(kù)客戶(hù)端的整體響應(yīng)時(shí)間和總執(zhí)行時(shí)間等因素至關(guān)重要。這就是時(shí)間復(fù)雜性的概念。后面還將繼續(xù)了解。

　　接下來(lái)，執(zhí)行所選擇的查詢(xún)計(jì)劃，由系統(tǒng)的執(zhí)行引擎進(jìn)行評(píng)估，并返回查詢(xún)結(jié)果。

　　編寫(xiě)SQL查詢(xún)
　　需要進(jìn)一步說(shuō)明的是，垃圾回收原則（GIGO）原本就是表達(dá)在查詢(xún)處理和執(zhí)行之中：制定查詢(xún)的人，同時(shí)也決定著SQL查詢(xún)的性能。

　　這意味著在編寫(xiě)查詢(xún)，有些事情可以同步去做。就像文章開(kāi)始時(shí)介紹的，編寫(xiě)查詢(xún)需要遵循兩個(gè)標(biāo)準(zhǔn)：首先，編寫(xiě)的查詢(xún)需要滿(mǎn)足一定的標(biāo)準(zhǔn)，其次還應(yīng)該應(yīng)對(duì)查詢(xún)中可以出現(xiàn)的性能問(wèn)題。

　　總的來(lái)說(shuō)，有四個(gè)分句和關(guān)鍵字，方便新手考慮性能問(wèn)題：

　　WHERE 分句
　　INNER JOIN 和 LEFT JOIN 關(guān)鍵字
　　HAVING 分句
　　雖然這種做法簡(jiǎn)單而天真，但對(duì)于一個(gè)初學(xué)者來(lái)說(shuō)，這些方法卻是一個(gè)很好的指引。這些地方也是你剛開(kāi)始編寫(xiě)時(shí)，容易發(fā)生錯(cuò)誤的地方，這些錯(cuò)誤也很難發(fā)現(xiàn)。

　　同時(shí)，要想提升性能，使其變得有意義，就不能脫離上下文：在考慮SQL性能時(shí)，不能武斷的認(rèn)為上面的分句和關(guān)鍵字不好。使用WHERE 或 HAVING的分句也可能是很好的查詢(xún)語(yǔ)句。

　　通過(guò)下面的章節(jié)來(lái)來(lái)進(jìn)一步了解編寫(xiě)查詢(xún)時(shí)反向模型和代替方法，并將這些提示和技巧作為指導(dǎo)。如何重寫(xiě)查詢(xún)和是否需要重寫(xiě)查詢(xún)?nèi)Q于數(shù)據(jù)量，以及數(shù)據(jù)庫(kù)和執(zhí)行查詢(xún)所需的次數(shù)等。這完全取決于你的查詢(xún)目標(biāo)，事先掌握一些有關(guān)數(shù)據(jù)的知識(shí)是非常重要的！

　　1. 僅檢索你需要的數(shù)據(jù)
　　在編寫(xiě)SQL查詢(xún)時(shí)，并不是數(shù)據(jù)越多越好。因此在使用SELECT 語(yǔ)句、DISTINCT分句和LIKE操作符時(shí)，需要謹(jǐn)慎。

　　SELECT聲明

　　在編寫(xiě)完查詢(xún)語(yǔ)句之后，首先需要做的事情就是檢查select語(yǔ)句是否簡(jiǎn)潔。你的目標(biāo)應(yīng)該是刪除不必要的select列。以便只取到符合你查詢(xún)目的的數(shù)據(jù)。

　　如果還有相關(guān)使用exists的子查詢(xún)，那么就應(yīng)該在select語(yǔ)句中使用常量，而不是選擇實(shí)際列的值。當(dāng)檢查實(shí)體時(shí)，這是特別方便的。

　　請(qǐng)記住，相關(guān)子查詢(xún)是使用外部查詢(xún)中的值的子查詢(xún)，并且在這種情況下，NULL是可以作為“常量”的，這點(diǎn)確實(shí)令人困惑！

　　2. 縮小查詢(xún)結(jié)果
　　如果無(wú)法避免使用 SELECT語(yǔ)句時(shí)，可以考慮通過(guò)其它方式縮小查詢(xún)結(jié)果。例如，使用LIMIT 分句和數(shù)據(jù)類(lèi)型轉(zhuǎn)換的方法。

　　TOP，LIMIT和ROWNUM分句

　　可以在查詢(xún)中添加LIMIT或TOP分句，來(lái)設(shè)置查詢(xún)結(jié)果的最大行數(shù)。下面是一個(gè)示例：

　　SELECT TOP 3 *FROM Drivers;
　　請(qǐng)注意，你可以進(jìn)一步指定PERCENT。

　　例如，如果你想更改查詢(xún)的第一行 SELECT TOP 50 PERCENT *。

　　SELECT driverslicensenr, nameFROM Drivers
　　LIMIT 2;
　　此外，你還可以添加ROWNUM 分句，相應(yīng)于在查詢(xún)中使用的LIMIT：

　　SELECT *FROM DriversWHERE driverslicensenr = 123456 AND ROWNUM <= 3;
　　數(shù)據(jù)類(lèi)型轉(zhuǎn)換
　　應(yīng)該使用最小的數(shù)據(jù)類(lèi)型，因?yàn)樾〉臄?shù)據(jù)類(lèi)型更加有效。

　　當(dāng)查詢(xún)中需要進(jìn)行數(shù)據(jù)類(lèi)型轉(zhuǎn)化，會(huì)增加執(zhí)行時(shí)間，所以盡可能的避免數(shù)據(jù)類(lèi)型轉(zhuǎn)換的發(fā)生；

　　如果不能避免的話(huà)，需要謹(jǐn)慎的定義數(shù)據(jù)類(lèi)型的轉(zhuǎn)換。

　　本文是本系列教程的第一篇，后續(xù)還有更多《如何編寫(xiě)更好的SQL查詢(xún)》的文章分享給大家，敬請(qǐng)期待。

（編輯：武林網(wǎng)）

上一篇：Linux系統(tǒng)下mariadb數(shù)據(jù)庫(kù)的基本設(shè)置

下一篇：php優(yōu)化數(shù)據(jù)庫(kù)的方法有哪些