動機

sql的效能議題好像不常被提起 十分niche 那就來整理看看

原則

優先序由上往下

  1. 用index過的所有column(也只有被index過的)去select
  2. 如果需要反覆存取的資料上index與放到temp table
  3. 壓低要做操作的資料量

真的要用select *嗎?

通常不會需要所有的column，選需要的出來就好

避免用distinct

如果可以就select更多column出來就好

用join去join，不要用where去做

如果用where去做就是cross join，會讓資料量變大 效率自然就下去了

能用where去過濾的就用where

having只用在aggregate function的過濾

從sql的執行順序來看，因為where會先把資料量壓小 所以可以的話把條件放在where

sql order

wildcard只用在pattern最後面，如果只比對開頭的字串的話

是合理啦

但如果是要最尾的話，只放wildcard在前面效能也話比較好嗎

用limit(或top,FETCH FIRST，看DBMS)限制query出的數目，尤其是只想看個大概的時候

limit在sql的執行順序不是最高的，但是sql engine會知道不用撈整個table 這樣可以把資料量往下壓

把outer join換成空值+inner join

原本outer join會在不存在的column留下null 可以把null換成某個空值，這樣就可以用inner join

像是下面表的NO CUSTOMER

CUSTOMER_IDCUSTOMER_NAME
0NO CUSTOMER
1John Doe
2Mary Jane
3Peter Pan
4Joe Soap
CUSTOMER_IDSALES_PERSON
0Newbee Smith
2Oldie Jones
1Another Oldie
0Greenhorn

把在join或是where中需要運算的部分放到row中

就是先算好

Before

SELECT *
FROM sales a 
JOIN budget b ON    ((year(a.sale_date)* 100) + month(a.sale_date)) = b.budget_year_month

After


SELECT * FROM PRODUCTSFROM sales a 
JOIN budget b ON    a.sale_year_month = b.budget_year_month

avoid n+1

要select或是update等等 就直接用sql一次做完

不要分成一次一個，DBMS不是記憶體，沒那麼快

subquery不要放在select的column中

這樣的話每一次select出新的row都要subquery一次 如下

SELECT c.Name, 
       c.City,
       (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName 
FROM Customer c

用join取代

SELECT c.Name, 
       c.City, 
       co.CompanyName 
FROM Customer c 
	LEFT JOIN Company co
		ON c.CompanyID = co.CompanyID

用exists來取代aggregate function，確認資料是否存在

Before

IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0
    PRINT 'YES'

After

IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%')
    PRINT 'YES'

如果一行做不完，用temp table存

如下

SELECT * INTO #Temp FROM Customer WHERE RegionID = 5
SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

或是

table變數(根據soruce5，這個效能比較好)

DECLARE @AAAA TABLE 
	(	A1	INT IDENTITY(1,1) PRIMARY KEY,
		A2	VARCHAR(128),
		A3	VARCHAR(128)
	)
-- temp變數是這個
-- CREATE TABLE #AAAA
-- 	(	A1	INT IDENTITY(1,1) PRIMARY KEY,
-- 		A2	VARCHAR(128),
-- 		A3	VARCHAR(128)
-- 	)

Index

Index哪些column

flow chart

Index完之後就不要一直改那些column的值

不然Btree是建心酸的嗎

在where或是order by中用index過的column

CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 LIMIT 1

只有a與date_column在where與order by中，所以DBMS可以直接撈index的結果

index從左到右

CREATE INDEX tbl_idx ON tbl (a, b)

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1

SELECT *
  FROM tbl
 WHERE b = 1

第二個select就吃不到index的好處因為第一個index是a

只用index過的column

CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

因為b所以DBMS沒辦法從index過的結果拉資料，要去看table

如何tuning

  1. Execution Plan
  2. Monitor Resource Usage
  3. SQL DMVs
  4. profiler
  5. SQL SYNTAX EXPLAIN

Ref

source1 source2 source3 source4 soruce5