SQL語法提示工具SQL Prompt教程：為什么SELECT *（BP005）在生產(chǎn)代碼中不好？（上）

SQL Prompt根據(jù)數(shù)據(jù)庫的對象名稱、語法和代碼片段自動進(jìn)行檢索，為用戶提供合適的代碼選擇。自動腳本設(shè)置使代碼簡單易讀--當(dāng)開發(fā)者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。

點(diǎn)擊下載SQL Prompt試用版

如果“提示”警告您在SELECT語句中使用星號或“star”（*），請考慮將其替換為顯式列列表。它將防止不必要的網(wǎng)絡(luò)負(fù)載和查詢性能問題，并避免在插入表時如果列順序更改而造成問題。本文是該教程的上半部分內(nèi)容~

關(guān)于SELECT *，我應(yīng)該說第一件事是我們所有人都在使用它。我每天都將其用于開發(fā)工作，但除非有充分的理由，否則很少會投入生產(chǎn)。在開發(fā)之外，SELECT *在一個批處理中只有幾個有效的用途?？梢允褂肧ELECT * FROM內(nèi)部IF EXISTS語句SELECT COUNT(*)，SELECT * FROM…FOR XML或者也可以SELECT * FROM…FOR JSON。

在其他地方，使用星號作為列說明符的查詢可以在一段時間內(nèi)正常工作，但會帶來漏洞和性能問題。如果假設(shè)列順序是“固定的”，通過使用INSERT INTO… SELECT * FROM語句傳輸數(shù)據(jù)，那么當(dāng)該順序更改時，您最大的希望是它會導(dǎo)致錯誤，但在最壞的情況下，您會看到分配錯誤數(shù)據(jù)的可怕后果。同樣，選擇表中所有列的查詢（即使應(yīng)用程序不需要全部）也會導(dǎo)致不必要的網(wǎng)絡(luò)負(fù)載和查詢性能問題。這也使查詢很難索引，從而導(dǎo)致執(zhí)行計劃效率低下。

最后，SELECT *這還將使您的代碼可讀性降低，并且難以維護(hù)。所有這些都解釋了為什么SQL Prompt實(shí)現(xiàn)“最佳實(shí)踐”代碼分析規(guī)則（BP005），該規(guī)則將提示您*在SELECT語句中用明確的列列表替換asterisk（）。

使用SELECT *進(jìn)行開發(fā)工作

SELECT *是臨時工作的理想選擇，可以在開始優(yōu)化查詢或調(diào)試時準(zhǔn)確地查看結(jié)果，但它是一種旨在交互式使用的開發(fā)設(shè)備。如果沒有SELECT *，SQL Server的開發(fā)工作會更痛苦，因?yàn)椴椴坏疥P(guān)于正在從表中源返回的數(shù)據(jù)和元數(shù)據(jù)，這意味著任何可以進(jìn)入from子句的任何內(nèi)容。這將包括基本表、視圖、TABLESAMPLE、派生表、連接表和表函數(shù)。

簡而言之，SELECT *是觀察數(shù)據(jù)必不可少的。當(dāng)然，sp_help它將為您提供有關(guān)表和其他對象的信息，并且您擁有SSMS瀏覽器以及希望的SQL Prompt，但是在調(diào)查查詢、表值函數(shù)或過程的結(jié)果時，看到該SSMS數(shù)據(jù)網(wǎng)格無所不能。

從表、視圖或TVF獲取列名列表仍然是一種流行的方法。但是，如果您只需要一個表或視圖的列列表（而不是TVF），則可以將表的Columns子文件夾從SSMS對象資源管理器拖到查詢窗格中?；蛘?，在這種情況下，在目標(biāo)數(shù)據(jù)庫的上下文中Adventureworks2016，您可以使用以下方法調(diào)查表：

SELECT col.name
  FROM sys.objects obj -- from all the objects (system and database)
      INNER JOIN sys.columns col --to get all the columns
          ON col.object_id = obj.object_id
  WHERE obj.object_id = OBJECT_ID('[Person].[Address]')
  ORDER BY col.column_id;

SELECT *的優(yōu)點(diǎn)是您可以復(fù)制和粘貼任何結(jié)果的列名，而不管其來源如何。但是，如今最好將sys.dm_exec_describe_first_result_set用于語句，將sp_describe_first_result_set用于批處理。例如，如果我們想查看Person.Address表中的哪些列AdventureWorks，可以使用：

SELECT f.name
  FROM sys.dm_exec_describe_first_result_set
   (N'SELECT * FROM adventureworks2016.Person.Address;', NULL, 0) AS f; 
         -- (@tsql, @Params, @include_browse_information)

為什么SELECT *在生產(chǎn)代碼中不好？

一旦您超出了調(diào)查階段，SELECT *應(yīng)將其替換為顯式的列列表，否則可能導(dǎo)致問題：

• 消化不良：數(shù)據(jù)庫和應(yīng)用程序中，您請求的每一列數(shù)據(jù)都需要付費(fèi)。當(dāng)您想要一杯水時，您不必打開房子的每一個水龍頭。當(dāng)在應(yīng)用程序中指定所有列而不是僅指定所需的列時，您最初將知道，甚至可以接受浪費(fèi)的程度。如果隨后有人使用更多列擴(kuò)展了表的寬度，則您會得到很多不必要的列，并且由于增加的內(nèi)存管理任務(wù)，您的應(yīng)用程序?qū)⒆兟?/p>

• 麻木。檢索信息的查詢可能會使用索引，特別是如果您的查詢使用過濾器。SQL Server的查詢優(yōu)化器將盡可能從覆蓋索引中獲取數(shù)據(jù)，而不必在聚集索引中四處逛逛。如果使用SELECT *，則很有可能沒有索引被覆蓋。即使您足夠笨拙地使用非聚集索引覆蓋整個表，但是如果表的大小進(jìn)一步增加，則將完全變得毫無用處。

• 誤解：如果您按順序而不是名稱來引用列，使用INSERT INTO… SELECT *，那么您必須希望列的順序永遠(yuǎn)不會改變。如果是這樣，并且有可能將值強(qiáng)制轉(zhuǎn)換為您期望的數(shù)據(jù)類型，那么數(shù)據(jù)可能會以錯誤的列結(jié)尾，而不會觸發(fā)錯誤。

• 約束問題。當(dāng)您使用星號顯示的查詢SELECT…INTO時，很容易遇到列名重復(fù)的問題。如果指定列，則將在重復(fù)項之前知道它們并可以對其進(jìn)行別名。相反，如果將這樣的查詢傳遞給應(yīng)用程序，則無法輕松地知道哪一列是正確的值。

• 可維護(hù)性：任何閱讀您的代碼并看到的SELECT *人都必須搜索元數(shù)據(jù)，以優(yōu)化查詢所引用的列的名稱。如果您列出它們，并且它們是有意義的名稱，那么將會更清楚發(fā)生了什么，并且查詢的目的將更加明顯。

消化不良

在應(yīng)用程序中，我見過的最壞習(xí)慣可能是指定不需要的數(shù)據(jù)。在一些開發(fā)人員中間，人們誤解了數(shù)據(jù)庫速度很慢，但是每個查詢花費(fèi)的時間大約相同。通過這種錯誤的邏輯，在一個查詢中獲取所有內(nèi)容就很有意義，并且SELECT *意味著：“把所擁有的全部給我”。就像一個銷售購物者，他不得不排隊，并且從貨架上搶走了他們可能無法承受或負(fù)擔(dān)得起的更多廉價商品。

具有諷刺意味的是，查詢中許多明顯的緩慢結(jié)果并不是“數(shù)據(jù)庫正在緩慢”，而是在不必要的數(shù)據(jù)通過網(wǎng)絡(luò)拉動而不得不將其壓縮在大型對象中的情況下，本地內(nèi)存管理陷入困境。如果要從數(shù)據(jù)庫將數(shù)據(jù)加載到對象中，通常最好使用延遲加載，尤其是在對象較大的情況下。就花費(fèi)的時間而言，跨網(wǎng)絡(luò)傳遞數(shù)據(jù)的開銷令人驚訝地昂貴，因此最好只請求查詢中立即需要的內(nèi)容。幾個快速查詢比一個龐大的超級查詢要快。

即使在SQL Server中，SELECT *也會咬你。它突破了一般性原則，出于性能原因，您在過濾和投影數(shù)據(jù)時會盡快降低結(jié)果。這意味著您在進(jìn)行諸如聚合之類的高級操作之前，首先將列和行都縮減為所需的內(nèi)容。現(xiàn)在，在查詢中的派生表中使用SELECT *對性能的危害要小于以前，這是因?yàn)閮?yōu)化器現(xiàn)在通常可以將列限制在必要的范圍內(nèi)，但這仍然是錯誤的，尤其是在外部查詢中。 結(jié)果應(yīng)始終僅包含所需的列。

為了演示其影響，在這里我通過使用時序表中的SELECT…INTO在時序工具中創(chuàng)建兩個臨時表，并用Directory表中的200萬行數(shù)據(jù)填充它們。在其中一個例子中，我只指定了我需要的列，在另一個中，我聳了聳肩，然后鍵入*。

--set up
  DECLARE @log TABLE (TheOrder INT IDENTITY(1,1), 
    WhatHappened varchar(200), 
    WhenItDid  Datetime2 DEFAULT GETDATE())
  ----start of timing
  INSERT INTO @log(WhatHappened) SELECT 'Starting to time select into'--place at the start
  --end of setup
  SELECT * INTO #myTempDirectory FROM Directory;
  INSERT INTO @log (WhatHappened) 
    SELECT 'SELECT INTO with wildcard took';
     --copy and paste in anywhere
  SELECT Name, Address1, Address2, Town, City, County, Postcode, Region
    INTO #AnotherTempDirectory
    FROM Directory;
  INSERT INTO @log (WhatHappened) 
    SELECT 'SELECT INTO with fields specified took ';
     --where the routine you want to time ends
  SELECT ending.WhatHappened,
    DateDiff(ms, starting.WhenItDid, ending.WhenItDid) AS ms
    FROM @log AS starting
      INNER JOIN @log AS ending
        ON ending.TheOrder = starting.TheOrder + 1
  UNION ALL
  SELECT 'Total', DateDiff(ms, Min(WhenItDid), Max(WhenItDid)) FROM @log;
  --list out all the timings
  --tear down
  DROP TABLE #myTempDirectory;
  DROP TABLE #AnotherTempDirectory;

盡管我們僅將數(shù)據(jù)量減少了30％，但指定列的版本只花費(fèi)了另一列的20％的時間。

換句話說，對于本測試，使用通配符SELECT…INTO的時間是僅返回所需列的SELECT…INTO的五倍。

麻木

幾乎不可能為SELECT *查詢提供覆蓋索引，即使您要嘗試，也要比僅簡單地明確拼出所需的列要困難得多。

如果您只是在獲取信息，SQL Server通常將使用索引。對于頻繁、重要且昂貴的查詢，您可能希望它具有一個可用的索引，它可以從中檢索查詢請求的所有列。如果沒有，則可以從索引中獲取可用列，然后在聚簇索引中查找其他列的值，或者可以簡單地掃描聚簇索引。如果表很大并且查詢返回很多行，您將開始注意到性能下降。

假設(shè)我們要搜索Directory表格以查找給定城市中所有被稱為“某物”的酒吧。我們可以做到

SELECT Name, Address1, Address2, Town, City, County, Postcode, Region FROM Directory WHERE name LIKE '% arms%' and city ='Cambridge'

或者我們可以這樣做...

SELECT * FROM Directory WHERE name LIKE '% arms%' and city ='Cambridge'

第一個查詢可能具有如下覆蓋索引：

CREATE NONCLUSTERED INDEX IX_Directory_City
  ON [dbo].[Directory] ([City])
  INCLUDE ([Address1], [Address2], [Town], [County], [Postcode], [Region])
  GO

但是，如果查詢?yōu)镾ELECT *，那么合理的索引策略將無濟(jì)于事。第二個版本只能包含每個列的索引！

使用與以前相同的測試工具，即使大多數(shù)工作都是在劍橋所有企業(yè)的名稱中搜索“武器”一詞，您也可以更快地指定列。在第一種情況下，索引是此查詢的覆蓋索引，而對于SELECT *版本，則不是。執(zhí)行計劃仍使用它，因?yàn)樵谶@種情況下查詢僅返回19行，但是SQL Server必須為每行執(zhí)行其他鍵查找，以返回此索引中未包含的列值。

在此數(shù)據(jù)庫上進(jìn)行更一般的查詢后，指定列的查詢平均使用了相同索引但需要進(jìn)行額外的鍵查找的時間的三分之二。

本教程尚未完結(jié)，后面會接著更新本教程后半部分的內(nèi)容，大家敬請期待哦~您可以關(guān)注我們了解更多該產(chǎn)品信息，或者下載SQL Prompt免費(fèi)版進(jìn)行測試~

相關(guān)內(nèi)容推薦：

SQL Prompt系列教程>>>

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點(diǎn)擊“咨詢在線客服”