Part 5-3-9. SQL 튜닝 : Hash 조인

 

 

🌞 SQL 튜닝

 

 지난 시간에는 조인의 3가지 원리 중 하나인 Merge 조인에 대해서 알아보았다. 머지 조인은 양쪽 집합을 정렬하고 병합하여 Sort Merge 조인이라고도 부르기도 한다. 또한 다대다(Many-To-Many)보다는, 일대다(One-To-Many) 조인에 보다 효과적인 것 까지 알아보았다. 이번 시간에는 마지막으로 Hash 조인에 대해서 알아보는 시간을 가져보도록 하자.

 

 이번 시간에는  BaseballData 데이터베이스를 사용하는 것이 아닌, 다시  Northwind  데이터베이스를 사용한다.

 

---

 

🧛‍♀️ Hash 조인

 

 해시 조인은 인덱스가 없을 때 큰 위력을 발휘하는데, 오늘도 실습을 통해 알아보도록 하자.

 

-- TestOrders 테이블 생성
SELECT *
INTO TestOrders
FROM Orders;

-- TestCustomers 테이블 생성
SELECT *
INTO TestCustomers
FROM Customers;

 

 SELECT ~ INTO 문을 통해 늘 하던 것처럼 먼저 테이블을 생성해주고, 각 테이블에 데이터가 잘 들어갔는지 확인하도록 하자.

 

-- 데이터 조회
SELECT * FROM TestOrders; -- 830개 데이터
SELECT * FROM TestCustomers; -- 91개 데이터

 

 조회 결과를 살펴보면 둘 다 CustomerID를 보유하고 있어 CustomerID를 통해 짝을 맞춰 살펴보면 데이터를 보다 쉽게 추출할 수 있다. 그리고 해시 테이블은 데이터의 수량도 영향을 받기 떄문에 이를 기억하고 있자. 🫡

 

-- HASH를 통한 데이터 조회
SELECT *
FROM TestOrders AS o
	INNER JOIN TestCustomers AS c
	ON o.CustomerID = c.CustomerID;

 실행 계획을 살펴보면 Hash 조인을 사용한 것을 볼 수 있다. 그렇다면 NL과 Merge를 통해 동작시키면 어떻게 될까? 각각에 대해서 복습 겸 분석해보도록 하자.

 

-- NL로 동작시키면 어떻게 될까?
SELECT *
FROM TestOrders AS o
	INNER JOIN TestCustomers AS c
	ON o.CustomerID = c.CustomerID
	OPTION (FORCE ORDER, LOOP JOIN);

 

 기존에 학습했던 것 처럼 TestOrders가 C#으로 따지면 외부 포문 (Outer), TestCustomers가 C#으로 따지면 내부 포문 (Inner)가 되어 하나하나씩 데이터를 살펴본다. 이 경우 Inner Table에 INDEX가 없기 때문에 오래 걸린다. 그렇다면 Merge로 조회 할 경우 어떻게 나타날까?

 

-- MERGE로 동작시키면 어떻게 될까?
SELECT *
FROM TestOrders AS o
	INNER JOIN TestCustomers AS c
	ON o.CustomerID = c.CustomerID
	OPTION (FORCE ORDER, MERGE JOIN);

 

 실행 계획을 보면 이전에 학습한 것과 같이 양쪽 테이블을 각각 모두 소팅하고, 다대다(Many-To-Many)까지 True로 처리된 것을 볼 수 있다. 따라서 Merge 조인도 인덱스가 없는 상황이기 떄문에 효율적으로 데이터를 조회할 수 없다. 이제 해시 조인에 대해서 알아보기 위해 오늘도 비주얼 스튜디오 C# 코드를 통해 알아보자.

 

---

 

 SQL에서 테이블 생성하는 것과 마찬가지로 늘 똑같이 player와 salary 클래스를 만들어 주도록 하자.

 

    class Player
    {
        public int playerId;
    }

    class Salary
    {
        public int playerId;
    }

 

 이 후, List를 만들고 Random 함수를 통해 각각의 리스트에 데이터를 넣어주도록 하자.

 

        static void Main(string[] args)
        {
            Random rand = new Random();

            List<Player> players = new List<Player>();
            for (int i = 0; i < 10000; i++)
            {
                if (rand.Next(0, 2) == 0)
                    continue;

                players.Add(new Player() { playerId = i });
            }

            List<Salary> salaries = new List<Salary>();
            for (int i = 0; i < 10000; i++)
            {
                if (rand.Next(0, 2) == 0)
                    continue;

                salaries.Add(new Salary() { playerId = i });
            }
        }

 

 이 후 해시를 통해 result 리스트에 데이터를 처리하는 작업을 할 것인데, 우리가 은연중에 사용하던 Dictionary가 바로 해시를 통해 자료를 처리하고 있었기에 Dictionary 자료형을 통해 처리하도록 하자.

 

            // 해시는 해시테이블을 이용해서 사용하는 방법이다.
            // 딕셔너리가 바로 해시테이블이다
            Dictionary<int, Salary> hash = new Dictionary<int, Salary>();
            foreach (Salary s in salaries)
            {
                hash.Add(s.playerId, s);
            }

            List<int> result = new List<int>();
            foreach (Player p in players)
            {
                if (hash.ContainsKey(p.playerId))
                {
                    result.Add(p.playerId);
                }
            }

 

 유심히 보면 NL과 다를게 없어보이지만 NL이랑 다른 점은 NL 조인은 아우터와 이너의 개념이 존재해서 양쪽 모두를 순회하면서 찾지만 해시 조인은 그렇지 않다는 점과 NL 조인은 애초에 Dictionary를 통해 사용되지 않는 다는 점이 다르다. 

 

 Dictionary 자료형을 통해 처리한 것이 마음에 들지 않으므로 우리가 임의로 HashTable 클래스를 만들어보도록 하자.

 

    // HashTable을 직접 만들어보자
    // 통이 10개 있다고 가정한다
    // [] [] [] [] [] [] [] [] [] []
    // 특정 숫자 N을 10으로 나눈 나머지의 값을 인덱스로 사용한다
    // [0] [1] [2] [3] [4] [5] [6] [7] [8] [9]
    // ex) 41 % 10 = 1 (Key) → [1]에는 41이 들어간다.
    // ex) 51 % 10 = 1 (Key) → 마찬가지로 [1]에 51이 들어간다.
    // 따라서 일련의 숫자들이 주르륵 저장되어 있을 것이다.

    // 나중에 누군가 '41' 있나요? 할 경우, 키 값을 구해야 한다. 키는 1 (Key)
    // 따라서 1번 상자를 뒤져서 41이 있을 경우 있는것이고, 없으면 없는 것이다.
    // 해시 = 공간을 내주고, 속도를 얻는다

    // 따라서 동일한 값 → 동일한 Bucket (YES)
    // 동일한 Bucket → 동일한 값 (NO)이 된다.
    // 해시는 일방향 소통이다.

    class HashTable
    {
        int _bucketCount;
        List<int>[] _buckets;

        public HashTable(int bucketCount = 100)
        {
            _bucketCount = bucketCount;
            _buckets = new List<int>[bucketCount];

            for (int i = 0; i < bucketCount; i++)
            {
                _buckets[i] = new List<int>();
            }
        }

        public void Add(int value)
        {
            int key = value % _bucketCount;
            _buckets[key].Add(value);
        }

        public bool Find(int value)
        {
            int key = value % _bucketCount;
            foreach (int v in _buckets[key])
            {
                if (v == value)
                    return true;
            }

            return false;
        }

 

 해시 테이블을 직접 구현하기 위해 동작 방식을 주석으로 작성해 어떻게 처리해야 할 지 생각하고, 이를 의사코드로 구현할 경우 위와 같이 구성되어진다. 마지막으로 우리가 직접 만든 해시 테이블을 통해 구현해보면 아래와 같이 사용할 수 있다. 

 

            // 직접 만든 해시 테이블을 이용해보자.
            HashTable hashtable = new HashTable();
            foreach (Salary s in salaries)
            {
                hashtable.Add(s.playerId);
            }

            List<int> result2 = new List<int>();
            foreach (Player p in players)
            {
                if (hashtable.Find(p.playerId))
                {
                    result2.Add(p.playerId);
                }
            }

 

---

 

 다시 SQL로 돌아와 해시 테이블을 통한 데이터 조회 구문을 다시 살펴보도록 하자.

 

-- HASH를 통한 데이터 조회
SELECT *
FROM TestOrders AS o
	INNER JOIN TestCustomers AS c
	ON o.CustomerID = c.CustomerID;

 

 이 때 실행계획을 보면 TestCustomers를 통해 해시 테이블을 만들어 주는 것을 볼 수 있는데, 이 결과로 데이터가 작은 쪽에서 해시 테이블을 만들어 주는게 유리한지, 데이터가 큰 쪽에서 해시 테이블을 만들어 주는 것이 유리할지? 궁금할 수 있다.

 

 결과론적으로 실행계획에서 TestCustomers를 이용한 걸 보니 데이터가 작은 쪽에서 해시 테이블을 만들어 준 것을 볼 수 있다. 왜냐하면 데이터가 많은 곳에서 해시 테이블을 만드는 것 자체도 부하기 떄문에, 데이터가 작은 쪽에서 해시 테이블을 만들어 준 걸 볼 수 있다.

 

---

 

🧛‍♀️ 결론

 

해시 조인의 특징
1) 정렬이 필요하지 않다. → 데이터가 너무 많아서 머지가 부담스러울 때 오늘 배운 Hash가 대안이 될 수 있다.
2) 인덱스 유뮤에 영향을 받지 않는다. (★★★★★)
 * NL/Merge에 비해 확실한 장점!
 * HashTable 자체를 만드는 비용을 무시하면 안된다. (수행 빈도가 많은 쿼리를 만들 경우, 결국 인덱스를 추가해줘서 관리를 하는 것이 더 좋다)
3) 랜덤 엑세스 위주로 수행되지 않는다.
4) 데이터가 적은 쪽을 해시 테이블로 만드는 것이 유리하다.