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

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

點擊下載SQL Prompt試用版

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

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

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

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

使用SELECT *進行開發工作

SELECT *是臨時工作的理想選擇，可以在開始優化查詢或調試時準確地查看結果，但它是一種旨在交互式使用的開發設備。如果沒有SELECT *，SQL Server的開發工作會更痛苦，因為查不到關于正在從表中源返回的數據和元數據，這意味著任何可以進入from子句的任何內容。這將包括基本表、視圖、TABLESAMPLE、派生表、連接表和表函數。

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

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

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 *的優點是您可以復制和粘貼任何結果的列名，而不管其來源如何。但是，如今最好將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 *在生產代碼中不好？

一旦您超出了調查階段，SELECT *應將其替換為顯式的列列表，否則可能導致問題：

• 消化不良：數據庫和應用程序中，您請求的每一列數據都需要付費。當您想要一杯水時，您不必打開房子的每一個水龍頭。當在應用程序中指定所有列而不是僅指定所需的列時，您最初將知道，甚至可以接受浪費的程度。如果隨后有人使用更多列擴展了表的寬度，則您會得到很多不必要的列，并且由于增加的內存管理任務，您的應用程序將變慢。

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

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

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

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

消化不良

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

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

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

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

--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;

盡管我們僅將數據量減少了30％，但指定列的版本只花費了另一列的20％的時間。

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

麻木

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

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

假設我們要搜索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

但是，如果查詢為SELECT *，那么合理的索引策略將無濟于事。第二個版本只能包含每個列的索引！

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

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

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

相關內容推薦：

SQL Prompt系列教程>>>

想要購買SQL Prompt正版授權，或了解更多產品信息請點擊