#4. 첫 번째 수업 - How to Think Like the Engine 2

<How to Think Like the Engine>

(* 직접 읽고 듣고 해석한 것이기 때문에 오류 많을 수 있음 주의)

 

■ 네 번째 쿼리

 

SELECT *
FROM dbo.Users
WHERE LastAccessDate > '2014/07/01'
ORDER BY LastAccessDate;

 

이번에는 id뿐만 아니라 모든 컬럼을 다 가지고 와보자. 이 쿼리는 세 번째 쿼리(id만 SELECT하는 쿼리)보다 굉장히 성능이 별로인 쿼리가 될 것이다. 왜 별로일까?

 

Do we work harder to read the data?

YES. SELECT id 쿼리랑 같은 row를 읽게 되긴 할건데, 이번에는 About me 컬럼 처럼 varchar(max)나 nvarchar(max) 크기를 가지고 있는 컬럼들도 같이 딸려올 것이다. 이번에는 그런 컬럼들을 갖고 있는 페이지도 같이 읽어야 한다. 그래서 이런 큰 데이터 타입 컬럼들을 갖고 있는 경우라면 읽는 데 더 비용이 많이 드는 건 맞다.

 

Do we work harder to write the data?

YES. SELECT id 쿼리에서 살펴봤듯이 ORDER BY를 하기 위해서 포스트잇에다가 갖고 온 id랑 LastAccessDate를 적었다. 그런데 이번에는 SELECT * 이니까 id뿐만 아니라 해당 row의 컬럼 전체를 포스트잇에다 적고 정렬을 해야할 것이다.

 

Do we work harder to sort the data?

YES. SELECT id 쿼리와 같은 수의 row를 정렬하기는 하지만, 이번 쿼리의 경우 우리가 정렬하려는 페이지 스택의 사이즈가 훨씬 크다. 그리고 우리는 그 데이터들을 CPU 캐시에다 놓고 섞을 건데 이것도 추가적인 비용이 드는 것이다.

 

Do we work harder to output the data?

YES. 필요 없는 컬럼은 최대한 안 가져오면 좋긴 한데 사실상 이 컬럼들을 output 하는 비용은 생각보다 그렇게 크지 않다고 한다. 사실 이 쿼리 성능을 안 좋게 만드는 건 sorting이 대부분을 차지한다.

 

 

sorting에 95%의 비용을 쓰고 있는 것을 확인할 수 있다. SELECT id 쿼리와 SELECT * 쿼리의 Estimated Subtree Cost 비교했을 때도 차이가 많이 나는 것을 확인할 수 있다.

 

SELECT id 쿼리의 SELECT 비용

 

SELECT * 쿼리의 SELECT 비용

 

▶ Lesson

Sorting data is expensive, and more fields make it worse.

 

이 쿼리가 여러 번 돌수록 더 별로일 것이다. 이 쿼리를 100번 돌린다고 해보자.

 

SELECT Id
FROM dbo.Users
WHERE LastAccessDate > '2014/07/01'
ORDER BY LastAccessDate;
GO 100

 

팀장님께서 나한테 사원들 인사기록지를 다 가져오라고 (LastAccessDate > '2014/07/01'인 것만) 하시는데 이걸 100번 해야 한다. 그리고 사전에 팀장님께서 넌 이 짓을 100번 할 것이라고 미리 귀띔해주셨다고 가정하자. (우리가 쿼리에 100번 할거라고 썼듯이) 

만약 내가 이 쿼리 내용을 수행한다고 하면, 앞으로 같은 걸 100번 할 걸 아니까 필터링하고 정렬해놓은 데이터를 근처 가까운데다가 쟁여놓고 다음 번에 재사용하지 않을까?

➔ 오라클은 이게 가능하다. (core 당 47,000달러 짜린데 가능해야겠지)

 

이 분 영상을 보다보면 오라클이 세계에서 제일 비싼 DBMS라는 말이 종종 나온다. 오라클이 그 돈 받아서 뭐해먹고 사는지도 아래 화면과 함께 언급이 되곤 한다.

 

 

오라클 돈 진짜 잘 벌긴 하나보다. 

 

SQL Server는 query output이 아닌 데이터 페이지를 저장한다. 그렇기 때문에 SQL Server는 저 쿼리가 100번 돌면 100번의 sorting을 한다. 

그러면 이제 어떻게 이 쿼리를 빨라지게 할 수 있을까?

➔ Nonclustered index를 이용하면 된다.

 

Nonclusterd indexes: copies.

- Stored in order we want, include the fields we want

 

이번에는 검은색 데이터 페이지가 필요하다.

 

 

CREATE INDEX IX_LastAccessDate_Id 
ON dbo.Users (LastAccessDate, Id)

 

위의 검은색 페이지는 IX_LastAccessDate_Id 인덱스 데이터의 모습이다. 이제 우리는 Users 테이블의 두 개의 물리적 카피를 가지고 있는 셈이다.

• PK_Users_Id : id로 정렬된 clustered index. 모든 컬럼의 데이터를 가지고 있음.
• IX_LastAccessDate_Id : LastAccessDate, Id로 정렬된 nonclustered index. 인덱스 컬럼(LastAccessDate, Id)의 데이터만 가지고 있음.

 

 

그러면 세 번째 쿼리부터 다시 해 실행해보자.

SELECT Id
FROM dbo.Users
WHERE LastAccessDate > '2014/07/01'
ORDER BY LastAccessDate;

 

 

LastAccessDate로 정렬해놓은 데이터 카피(IX_LastAccessDate_Id 인덱스)가 있으므로, LastAccessDate > '2014/07/01' 조건에 맞는 시작지점을 알고 바로 진입할 수 있다. 그리고 그 카피에 함께 있는 Id를 순서대로 읽어올 것이다. 데이터 자체는 이미 LastAccessDate로 정렬되어 있기 때문에 별도의 sorting 과정은 필요 없다.

 

 

Estimated Subtree Cost가 엄청 줄어든 걸 확인할 수 있다. ➔ It's cheap.

 

 

Why cheaper?

For starters, it does less logical reads...

 

인덱스가 있고 없을 때의 CPU time을 비교해보자.

 

 

time이라는 건 사실 그렇게 믿을 만한 요인이 못 된다. (다른 쿼리 때문에 늦어질 수도 있고, 여러 가지 환경적 요인 등으로 인해 time은 늘 달라질 수 있다.) 지금은 index scan과 index seek의 단순 비교를 위해 사용해본 것이다.

 

Covering index

세 번째 쿼리를 보면 Users 테이블에서 LastAccessDate 컬럼과 Id 컬럼만을 사용하고 있다. 공교롭게도 모두 IX_LastAccessDate_Id 인덱스에 있는 컬럼들이다.

➔ 쿼리에 필요한 컬럼이 모두 한 인덱스 안에 있을 때, 이 인덱스를 커버링 인덱스라고 한다. 

The index covers the fields needed by the query, so we call it a covering index.

*But covering isn't really a special kind of index - it's only covering when we're talking about a query.

 

So nonclustered index seeks are great, right?

이렇게는 섣불리 결론짓지는 않았으면 좋겠다. 

이렇게들 오해하는 이유가 "seek"라는 단어가 주는 느낌 때문인 것 같다. "seek"라고 하면 뭔가 작을 것 같은 느낌을 준다. 그치만 결과 데이터를 봤듯이(14만 건) 데이터량은 인덱스를 쓰든 안 쓰든 동일하게 많다.

 

You probably think "seek" means, "I'm going to jump to a row and read that one row."

You probably think "scan" means, "I'm going to read the whole thing."

➔ 이거슨 ㄴㄴ

"seek"라는 말의 의미는 "나는 내가 어디서부터 시작해야하는지를(starting point) 알고 있다"라는 의미이다.

 

Seek means,

"I'm going to jump to a row and start reading."

Scan means,

"I'm going to start at either end of the object(might be either the start, or the end) and start reading."

Neither terms defines how many rows will be read.

 

적은 양의 데이터를 읽어올 때의 scan이 seek 보다 성능이 좋을 수 있다.

 

 

Seeks vs Scans

A seek can start at the first row, and read the entire table.

A scan can start at one end of the table, and only read a few pages.

 

We can't just aty, "All index seeks! We're done."

 

■ 다섯 번째 쿼리

SELECT Id 쿼리에 몇 가지 컬럼을 추가해보자.

 

SELECT Id, DisplayName, Age
FROM dbo.Users
WHERE LastAccessDate > '2014/07/01'
ORDER BY LastAccessDate;

 

이 쿼리에는 IX_LastAccessDate_Id 인덱스에 없는 컬럼들이 포함되어 있다. 

➔ 이 쿼리를 실행하기 위해서는 IX_LastAccessDate_Id 인덱스만으로는 충분하지 않다는 의미

이 경우, 아래와 같은 순서로 실행을 할 것이다.

 

1. IX_LastAccessDate_Id 인덱스에서 LastAccessDate가 2014/07/01인 곳(시작점)을 찾는다.

2. 조건에 맞는 데이터들의 Id와 LastAccessDate를 따로 적어둔다.

3. PK_Users_Id로 가서 따로 적어둔 Id에 해당하는 row를 찾아서 DisplayName과 Age를 가져온다.

➔ key lookup이라고 한다.

 

 

SQL Server always includes your clustering keys wheter you ask for'em or not because it has to join indexes together.

nonclustered index를 만들 때 Id를 안 포함시켜도 key라면 자동으로 포함된다.

 

Classic index tuning sign

key lookup은 인덱스에 쿼리에 필요한 모든 컬럼이 포함되어 있지 않을 때 발생한다. key lookup 실행계획에 마우스를 올려서 Output List를 확인한다.

컬럼이 작거나 사용 빈도가 높으면 인덱스에 포함 ㄱㄱ

 

 

SQL Server의 특징 중 하나는 실행계획을 만들 때 원본 데이터가 들어있는 테이블을 보고 만들지 않는다는 것이다. 쿼리가 실행되기 전에(쿼리가 실행될 때가 아니라) 미리 계획을 짜놓아야 한다.

이렇게 하기 위해서는 테이블 안에 뭐가 들어있는지에 대한 rough idea가 필요하다. 

➔ 통계치(statistics)가 필요한 이유

 

우리가 인덱스를 생성할 때마다 SQL Server는 인덱스와 동일한 이름의 statistics를 함께 만든다.

 

 

statistics는 인덱스 내용에 관한 1 페이지(8KB)짜리 메타데이터이다. SQL Server가 인덱스 내용을 보고 이 인덱스가 뭐하는 앤지 간단하게 남겨둔 노트 정도로 생각하면 될 것 같다.

 

Statistics help SQL Server:

• Decide which index to use
• What order to process tables/indexs in 
• Whether to do seeks or scans
• Guess how many rows will match you query
• How much memory to allocate for the query

 

statistics는 어떻게 생겼을까?

 

 

첫 번째 result : 테이블 또는 인덱스의 기본 정보 (at the time that the stat was updated)

두 번째 result : 인덱스 컬럼 정보

세 번째 result : 히스토그램 정보

 

LastAccessDate > '2014/07/01' 의 예상 행수를 계산할 때, SQL Server는 히스토그램에서 DISTINCT_RANGE_ROWS를 본다. 거기서 조건에 해당하는 데이터 건수의 합을 다 합친다.

 

 

기억해야할 것은 statistics는 1 페이지(8KB)짜리이고, 이것은 최대 201개의 bucket(또는 step)까지만 가질 수 있다는 것이다. 

 

 

이 stats에서 사용한 bucket(step)은 134개임을 알 수 있다. 그래서 데이터가 다양해지거나 히스토리가 많이 쌓일수록 버킷 200개로는 데이터 전체를 정확하게 표현하기에 부족할 수 있다.

 

 

 

Two ways you can help

1. Keep your stats updated at least weekly. Automatic stats updates aren't enough. Consider Ola Hallengren's free scripts: Ola. Hallengren.com

• SQL Server는 테이블 내 데이터의 20%가 변경되면 stats을 업데이트 한다. (SQL Server 버전 별로 이 수치는 다를 수 있음) 그렇지만 성능 문제를 빨리 해결해야하는데 SQL Server가 자동으로 통계를 업데이트해줄 때까지(데이터의 20%가 변경될 때까지) 기다리는 건 좀 아니다. 그보다 자주 사용자가 업데이트할 수 있다. 

2. Learn which T-SQL elements will cause cardinality estimation problems, ignoring statistics

• 비슷한 동작을 하는 쿼리라도 실행계획이 다를 수 있다.

<One idea, written differently>

 

위의 쿼리를 보면 두 쿼리 모두 비슷한 동작을 수행하는 것으로 보이고 결과 건수도 동일하다. 하지만 실행계획은 다르다. 첫 번째 쿼리는 예상 건수를 2262로 잡았고, 데이터가 적지 않다고 판단해 index scan을 한 것 같다. 두 번째 쿼리는 예상 건수를 1건으로 잡았고, 데이터가 1건 밖에 없으니 index seek + 룩업을 하자고 판단했던 것 같다.

Both produce the same 2,443 rows, but they use 2 different ways to retrieve thosde rows due to their different estimates.

 

SQL Server has to decide between:

• Scanning the entire table, which is great for big data, or
• An index seek + key lookup, which is better for small data

➔ Cardinality estimation

We can avoid this problem by widening our nonclustered index.

 

CREATE INDEX IX_LastAccessDate_Id_DisplayName_Age
ON dbo.Users (LastAccessDate, Id, DisplayName, Age)

Or:

CREATE INDEX IX_LastAccessDate_Id_Includes
ON dbo.Users (LastAccessDate, Id)
INCLUDE (DisplayName, Age)

 

첫 번째 쿼리는 이 인덱스를 LastAccessDate, Id, DisplayName, Age 컬럼 순으로 정렬하여 생성하겠다는 쿼리이다. 두 번째 쿼리는 이 인덱스를 LastAccessDate, Id 컬럼 순으로만 정렬할건데 그 옆에다가 DisplayName과 Age를 포함시키겠다는 쿼리이다.

 

 

INCLUDE로 컬럼을 추가하면 데이터의 정렬 순서와는 무관하므로, 데이터가 변경될때마다 데이터를 재정렬하지 않아도 되므로 효과적이라고 착각할 수 있다. 그렇지는 않다.

위 회색 데이터는 IX_LastAccessDate_Id_DisplayName_Age 인덱스의 데이터이다. 여기서 Id가 643인 Sarcastic의 Age를 30에서 31로 변경해야 한다고 가정해보자. (우측 두 번째 줄)

 

 

정렬순서에 Age 컬럼이 포함되어 있는 첫 번째 인덱스를 사용하면 데이터가 변경될 때마다 데이터가 재정렬될 것이고, INCLUDE로 Age를 포함시킨 두 번째 인덱스를 사용하면 재정렬하지 않을 것이라 생각할 수 있다. 하지만 위의 Sarcastic의 Age가 30에서 31로 변경된다고 했을 때 데이터의 이동은 일어나지 않는다.

➔ 왜냐하면 인덱스의 첫 번째 컬럼(LastAccessDate)이 selective하기 때문에(id가 643인 사람과 LastAccessDate가 같은 사람이 없음), 이 사람의 DisplayName, Id, Age를 변경한다고 해도 데이터는 이동하지 않는다.

➔ 이래서 인덱스의 첫 번째 컬럼을 selective한 컬럼(unique한 컬럼)으로 잡으라고 하는 것이다. (만약 성별 컬럼이 인덱스의 첫 번째 컬럼이라면 문제가 많을 것이다.)

 

 

이제 총 3개의 인덱스(=3개의 데이터 카피본)를 가지게 되었다. 회색 인덱스(IX_LastAccessDate_Id_DisplayName_Age)가 있으므로 이제 검은색 인덱스(IX_LastAccessDate_Id)는 drop 하는 것이 좋다.

 

Recap

• SET STATISTICS IO ON: shows # of 8KB pages read
• SET STATISTICS TIME ON: shows CPU work done
• WHERE without a supporting index: table scan
• ORDER BY  without a supporting index: CPU work
• Indexes reduce page read and sorts
• Seek =! awesome, and scan!= terribad
• Index seek + key lookup = we may need wider indexes
• Statistics help SQL Server pick indexes, methods
• Cardinality estimation isn't perfect (especially with real-world T-SQL and joins to multiple tables)
• You can help by understanding SQL's limitations and crafting your T-SQL to avoid them

 

 

그냥 듣기만 하는 것보다 한 세 배는 더 걸린 것 같은 느낌이다. 이제 첫 번째 수업을 끝냈다는 것이 믿기지 않는다. 앞으로 갈 길이 멀다. 열심히 하자.