[번역] 속도와 성능을 위해 MySQL 쿼리를 최적화하는 방법

원문 

https://dzone.com/articles/how-to-optimize-mysql-queries-for-speed-and-perfor

How to Optimize MySQL Queries for Speed and Performance on Alibaba Cloud ECS

 

1. Index All Columns Used in 'where', 'order by', and 'group by' Clauses

 

"where", "order by", 그리고 "group by" 절에서 사용되는 모든 열에 인덱스를 추가하면 MySQL 서버는 데이터베이스에서 결과를 더 빠르게 가져올 수 있습니다. 인덱스는 레코드를 정렬하는 데에도 매우 유용합니다.

MySQL 인덱스는 더 많은 공간을 차지하고 삽입, 삭제, 업데이트의 성능을 저하시킬 수 있습니다. 그러나 테이블에 10개 이상의 행이 있는 경우, 인덱스는 선택 쿼리 실행 시간을 상당히 줄일 수 있습니다.

생산 환경에서 어떻게 쿼리가 동작할지 더 명확한 그림을 얻기 위해 "최악의 경우" 샘플 데이터로 MySQL 쿼리를 테스트하는 것이 항상 좋습니다.

예를 들어, 인덱스 없이 500개의 행을 가진 데이터베이스에서 다음 SQL 쿼리를 실행하는 경우를 생각해보세요:

mysql> select customer_id, customer_name from customers where customer_id='140385';

위의 쿼리는 MySQL 서버가 우리가 검색하는 레코드를 검색하기 위해 전체 테이블 스캔(처음부터 끝까지)을 수행하도록 강제합니다.

다행히도, MySQL은 특별한 'EXPLAIN' 문을 가지고 있어서 select, delete, insert, replace, update 문과 함께 사용하여 쿼리를 분석할 수 있습니다.

위의 SQL을 explain 문과 함께 한 번 더 실행하면 MySQL이 쿼리를 실행하기 위해 어떤 작업을 수행할지에 대한 전체 그림을 얻게 됩니다:

mysql> explain select customer_id, customer_name from customers where customer_id='140385';

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

위의 설명 결과에서 최적화기가 우리의 데이터베이스 테이블을 조정하는 데 도움이 되는 매우 중요한 정보를 제공합니다. 첫째로, key 열이 'NULL'이기 때문에 MySQL은 전체 테이블 스캔을 수행할 것임이 분명합니다. 둘째로, MySQL 서버는 500개의 행에 대해 전체 스캔을 수행할 것임을 명확히 나타냅니다.

위의 쿼리를 최적화하기 위해 아래와 같은 구문으로 'customer_id' 필드에 인덱스를 추가할 수 있습니다:

mysql> Create index customer_id ON customers (customer_Id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

explain 문을 한 번 더 실행하면 아래와 같은 결과를 얻을 수 있습니다:

mysql> Explain select customer_id, customer_name from customers where customer_id='140385';

+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | customer_id   | customer_id | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

위의 설명 결과에서 MySQL 서버가 테이블을 검색하기 위해 인덱스(customer_Id)를 사용할 것임이 명확히 나타납니다. 스캔해야 하는 행 수가 1임을 명확히 볼 수 있습니다. 500개의 레코드가 있는 테이블에서 위의 쿼리를 실행했지만, 인덱스는 100만 개의 행이 있는 큰 데이터 집합을 쿼리할 때 매우 유용할 수 있습니다.

 

 

 

2. Optimize Like Statements With Union Clause

때로는 특정 테이블의 다른 필드나 열에 대해 비교 연산자 'or'을 사용하여 쿼리를 실행하고 싶을 수 있습니다. 'or' 키워드가 where 절에서 너무 많이 사용되면 MySQL 옵티마이저가 잘못된 선택으로 전체 테이블 스캔을 수행하여 레코드를 검색하는 경우가 있을 수 있습니다.

유니온(union) 절은 쿼리를 더 빠르게 실행하도록 할 수 있습니다. 특히 쿼리 한쪽의 인덱스를 최적화하고 다른 쪽의 인덱스를 최적화할 수 있는 경우에 유용합니다.

예를 들어, 'first_name'과 'last_name'에 인덱스가 있는 경우 다음과 같은 쿼리를 실행한다고 가정해보겠습니다:

mysql> select * from students where first_name like  'Ade%'  or last_name like 'Ade%' ;

 

위의 쿼리는 인덱스를 최적화할 수 있는 두 개의 별도의 빠른 쿼리의 결과를 병합하는 유니온 연산자를 사용하는 아래의 쿼리에 비해 더 느리게 실행될 수 있습니다.

mysql> select * from students where first_name like  'Ade%'  union all select * from students where last_name like  'Ade%' ;

이렇게 유니온 절을 사용하면 각각의 쿼리가 인덱스를 효과적으로 사용하여 더 빠르게 실행될 수 있습니다. 'or' 키워드를 너무 많이 사용하는 쿼리는 테이블의 모든 레코드를 스캔하게 되므로 성능이 저하될 수 있습니다. 그러나 유니온 절을 사용하면 두 개의 별도 쿼리를 실행하고 결과를 합침으로써 더 효율적으로 실행될 수 있습니다. 이를 통해 쿼리의 성능을 최적화할 수 있습니다.

 

 

 

3. Avoid Like Expressions With Leading Wildcards

Leading wildcards(앞뒤 와일드카드)가 포함된 쿼리에서 MySQL은 인덱스를 활용할 수 없습니다. 위에서 예시로 든 학생 테이블에서 아래와 같은 검색을 실행하는 경우, 'first_name' 필드에 인덱스를 걸었더라도 MySQL은 전체 테이블 스캔을 수행하게 될 것입니다.

mysql> select * from students where first_name like  '%Ade'  ;

 

이를 'explain' 키워드를 사용하여 증명할 수 있습니다.

mysql> explain select * from students where first_name like  '%Ade'  ;

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

위의 설명 결과를 보면, MySQL이 students 테이블의 모든 500개의 행을 스캔하고 쿼리가 굉장히 느려질 것임을 알 수 있습니다.

Leading wildcards(앞뒤 와일드카드)는 인덱스를 활용할 수 없기 때문에 쿼리 성능이 저하될 수 있습니다. 만약 위의 예시와 같이 'first_name' 필드에 인덱스를 추가했다고 해도, 와일드카드가 앞에 붙은 쿼리는 해당 인덱스를 사용하지 못하고 전체 테이블을 스캔하게 됩니다. 따라서 가능하다면 Leading wildcards를 피하는 것이 좋습니다. 대안으로 검색에 사용되는 패턴에 와일드카드를 뒤쪽에만 두어 인덱스를 활용할 수 있도록 해야합니다. 이렇게 하면 쿼리의 성능이 향상될 수 있습니다.

 

 

 

4. Take Advantage of MySQL Full-Text Searches

MySQL Full-Text Search(FTS)를 활용하세요.

와일드카드 문자를 사용하여 데이터를 검색해야 하지만 데이터베이스의 성능이 저하되는 상황에 직면하게 된다면, MySQL Full-Text Search(FTS)를 사용하는 것이 좋습니다. 와일드카드 문자를 사용하는 쿼리보다 훨씬 빠르기 때문입니다.

또한, FTS는 대용량 데이터베이스를 검색할 때 더 나은 결과와 관련된 결과를 가져올 수 있습니다.

학생 샘플 테이블에 Full-Text Search 인덱스를 추가하려면 다음과 같은 MySQL 명령문을 사용할 수 있습니다:

mysql> Alter table students ADD FULLTEXT (first_name, last_name);
mysql> Select * from students where match(first_name, last_name) AGAINST ('Ade');

위의 예시에서는 우리가 검색 키워드로 ('Ade')를 사용하여 일치시키고자 하는 열 (first_name과 last_name)을 지정했습니다.

만약 우리가 위의 쿼리의 실행 계획을 옵티마이저에게 질의한다면, 다음과 같은 결과를 얻을 수 있습니다:

mysql> explain Select * from students where match(first_name, last_name) AGAINST ('Ade');
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table    | partitions | type     | possible_keys | key        | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | students | NULL       | fulltext | first_name    | first_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

위의 설명 결과를 보면, 우리의 학생 데이터베이스가 500개의 행을 가지고 있더라도 단 한 개의 행만이 스캔될 것임을 알 수 있습니다. 이것은 데이터베이스의 성능을 향상시킬 것입니다. Full-Text Search를 사용하면 쿼리의 효율성이 높아질 뿐만 아니라 더 나은 검색 결과를 가져올 수 있습니다.

 

 

 

5. Optimize Your Database Schema

데이터베이스 스키마를 최적화하세요.
쿼리를 최적화하더라도 좋은 데이터베이스 구조를 만들지 못한다면, 데이터가 증가함에 따라 데이터베이스 성능이 여전히 저하될 수 있습니다.

 

테이블 정규화

먼저, 모든 데이터베이스 테이블을 정규화하세요. 이 과정은 어느 정도의 트레이드 오프를 수반할 수 있지만 필요합니다. 예를 들어, 고객 데이터와 주문 데이터를 저장하기 위해 두 개의 테이블을 생성한다면, 주문 테이블에서는 고객의 이름을 반복하는 대신 고객 ID를 사용하여 고객을 참조하는 것이 좋습니다. 후자의 방법은 데이터베이스를 부풀리게 만들 수 있습니다.

아래 이미지는 데이터 중복 없이 성능을 위해 디자인된 데이터베이스 스키마를 나타냅니다. MySQL 데이터베이스 정규화에서는 데이터를 전체 데이터베이스에서 단 한 번만 표현해야 합니다. 각 테이블에서 고객 이름을 반복하지 말고 다른 테이블에서 해당하는 고객_ID를 사용하세요.

또한, 유사한 값을 저장할 때는 항상 동일한 데이터 유형을 사용하세요. 위의 스키마 예시에서는 고객 테이블과 주문 테이블 모두 'INT' 데이터 유형을 사용하여 'customer_id'를 저장하고 있습니다.

 

최적의 데이터 유형 사용

MySQL은 정수, 부동 소수점, 배정밀도 부동 소수점, 날짜, 날짜 및 시간, 문자열(Varchar) 및 텍스트 등 다양한 데이터 유형을 지원합니다. 테이블을 디자인할 때 "짧은 것이 항상 더 좋다"는 원칙을 기억하세요.

예를 들어, 100명 이하의 사용자를 보유하는 시스템 사용자 테이블을 디자인 중이라면, 'user_id' 필드에 'TINYINT' 데이터 유형을 사용하는 것이 좋습니다. 이는 -128부터 128까지의 모든 값을 수용할 수 있기 때문입니다.

또한, 날짜 값을 예상하는 필드(예: sales_order_date)의 경우 date_time 데이터 유형을 사용하는 것이 이상적입니다. 이렇게 하면 SQL을 사용하여 레코드를 검색할 때 필드를 날짜로 변환하는 복잡한 함수를 실행할 필요가 없습니다.

모든 값을 숫자로 예상한다면 정수 값을 사용하세요(예: student_id 또는 payment_id 필드). 계산 관련 작업에서 MySQL은 Varchar와 같은 텍스트 데이터 유형보다 정수 값으로 더 나은 성능을 발휘할 수 있습니다.

 

Null 값을 피하세요

Null은 열에서 어떤 값도 존재하지 않는 상태를 나타냅니다. 가능한 경우 이러한 유형의 값을 피하는 것이 좋습니다. 왜냐하면 Null 값은 데이터베이스 결과에 영향을 미칠 수 있기 때문입니다. 예를 들어, 데이터베이스에서 모든 주문의 합계를 구하려고 할 때 특정 주문 레코드에 Null 금액이 있다면 예상 결과가 원하는 대로 나오지 않을 수 있습니다. 이러한 경우에는 MySQL의 'ifnull' 문을 사용하여 Null 레코드의 경우 대체 값을 반환하도록 해야 합니다.

일부 경우에는 특정 열/필드에 필수 값이 포함되지 않을 수도 있기 때문에 해당 필드에 대한 기본값을 정의해야 할 수도 있습니다.

 

너무 많은 열을 피하세요

넓은(wide) 테이블은 매우 비싸며 더 많은 CPU 시간을 필요로 합니다. 가능하다면, 비즈니스 로직이 명확히 요구하는 경우가 아니라면 100개 이상의 열을 가지지 않도록 노력하세요.

 

한 테이블로 모든 것을 해결하는 대신 논리적 구조로 나누어진 테이블로 분할하는 것이 좋습니다. 예를 들어, 고객 테이블을 생성하는데 있어서 고객이 여러 주소를 가질 수 있다면, 고객 주소를 보관하는 별도의 테이블을 생성하고 이 테이블은 'customer_id' 필드를 사용하여 고객 테이블과 연결하면 좋습니다.

 

조인을 최적화하세요

조인 문에 포함되는 테이블을 최소화하세요. 많은 조인을 포함하는 SQL 문은 잘 설계되지 않은 패턴으로 인해 제대로 작동하지 않을 수 있습니다. 각 쿼리에 최대 12개의 조인을 포함하는 것이 좋습니다.

 

 

 

6. MySQL 쿼리 캐싱

웹 사이트나 애플리케이션이 많은 select 쿼리(예: WordPress)를 수행하는 경우, MySQL 쿼리 캐싱 기능을 활용하는 것이 좋습니다. 이렇게 하면 읽기(read) 작업을 수행할 때 성능이 향상됩니다.

이 기술은 select 쿼리를 결과 데이터셋과 함께 캐싱하여 캐시된 쿼리가 한 번 이상 실행되면 메모리에서 가져오기 때문에 쿼리가 더 빨리 실행됩니다. 그러나 애플리케이션이 테이블을 자주 업데이트한다면 캐시된 쿼리와 결과셋이 무효화될 수 있습니다.

MySQL 서버에서 쿼리 캐시가 활성화되어 있는지 확인하려면 다음 명령을 실행하세요:

mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

 

MySQL 서버 쿼리 캐시 설정하기

MySQL 쿼리 캐시 값을 설정하려면 구성 파일('/etc/mysql/my.cnf' 또는 '/etc/mysql/mysql.conf.d/mysqld.cnf')을 편집하면 됩니다. 이는 MySQL 설치에 따라 다를 수 있습니다. 쿼리 캐시 크기 값을 너무 크게 설정하지 마십시오. 큰 크기의 쿼리 캐시는 캐시 오버헤드와 락 때문에 MySQL 서버 성능을 저하시킬 수 있습니다. 수십 메가바이트 범위의 값이 권장됩니다.

현재 값을 확인하려면 다음 명령을 사용하세요:

mysql> show variables like 'query_cache_%' ;
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

 

그런 다음 다음과 같이 값들을 조정하세요:

query_cache_type=1
query_cache_size=10M
query_cache_limit=256k

위의 값들은 서버의 요구 사항에 맞게 조정할 수 있습니다.

지시문 'query_cache_type=1'은 기본적으로 비활성화된 경우 MySQL 캐싱을 켭니다.

기본 'query_cache_size'는 1MB이며, 위에서 말했듯이 10MB 정도의 값이 권장됩니다. 또한 값은 40KB 이상이어야 합니다. 그렇지 않으면 MySQL 서버에서 "Query cache failed to set size" 경고가 발생합니다.

기본 'query_cache_limit'도 1MB입니다. 이 값은 캐시된 각각의 쿼리 결과를 제어합니다.

 

 

 

결론

이 가이드에서는 Alibaba Cloud에서 호스팅되는 MySQL 서버의 속도와 성능을 최적화하는 방법을 안내해드렸습니다.

이 가이드를 통해 더 나은 쿼리를 작성하고 유지 관리하기 쉬운 잘 구조화된 데이터베이스 구조를 갖추게 되어 소프트웨어 애플리케이션 또는 웹 사이트에 더욱 안정성을 제공할 수 있을 것으로 기대합니다.

감사합니다.