쿼리는 데이터베이스 기술에서 가장 일반적으로 사용되는 작업입니다. 질의 연산 과정은 비교적 간단하다. 먼저 클라이언트로부터 질의 SQL 문을 발급받는다. 데이터베이스 서버는 클라이언트가 보낸 SQL 문을 수신한 후 질의 결과를 클라이언트에 반환한다. 프로세스는 매우 간단하지만 다양한 쿼리 방법과 데이터베이스 설정이 쿼리 성능에 큰 영향을 미칩니다.
따라서 이 기사에서는 MySQL에서 일반적으로 사용되는 쿼리 최적화 기술에 대해 설명합니다. 쿼리 버퍼링을 통한 쿼리 속도 향상, 쿼리의 인덱스 기반 정렬, 도달할 수 없는 쿼리 감지 및 성능 향상을 위한 다양한 쿼리 옵션 사용에 대한 논의가 포함됩니다.
1. 쿼리 버퍼링을 통해 쿼리 속도 향상
일반적으로 SQL 문을 사용하여 쿼리할 때 데이터베이스 서버는 클라이언트로부터 SQL을 수신할 때마다 이 SQL 문을 실행합니다. 하지만 일정 간격(예: 1분 이내) 내에 완전히 동일한 SQL 문이 수신되면 동일한 방식으로 실행됩니다. 이를 통해 데이터의 실시간 특성이 보장될 수 있지만 대부분의 경우 데이터에 완전한 실시간이 필요하지 않으므로 특정 지연이 발생할 수 있습니다. 이 경우 짧은 시간 내에 정확히 동일한 SQL을 실행하는 것은 이득을 얻을 가치가 없습니다.
다행스럽게도 MySQL은 쿼리 버퍼링 기능을 제공합니다(쿼리 버퍼링은 MySQL 4.0.1 이상에서만 사용할 수 있습니다). 쿼리 캐싱을 통해 쿼리 성능을 어느 정도 향상시킬 수 있습니다.
MySQL 설치 디렉터리의 my.ini 파일을 통해 쿼리 버퍼를 설정할 수 있습니다. 설정도 매우 간단합니다. query_cache_type을 1로 설정하면 됩니다. 이 속성을 설정한 후 SELECT 문을 실행하기 전에 MySQL은 동일한 SELECT 문이 실행되었는지 여부를 버퍼에서 확인하고 실행 결과가 만료되지 않은 경우 쿼리 결과가 클라이언트에 직접 반환됩니다. 그러나 SQL 문을 작성할 때 MySQL의 쿼리 버퍼는 대소문자를 구분한다는 점에 유의하세요. 다음 두 개의 SELECT 문은 다음과 같습니다. SELECT * from TABLE1
SELECT * FROM TABLE1
위의 두 SQL 문은 쿼리 버퍼링에 대한 완전히 다른 SELECT입니다. 또한 쿼리 버퍼는 공백을 자동으로 처리하지 않으므로 SQL 문을 작성할 때 공백, 특히 SQL의 시작과 끝 부분의 공백 사용을 줄여야 합니다. 시작과 끝).
쿼리 버퍼를 설정하지 않으면 성능 저하가 발생할 수 있지만, 실시간으로 데이터를 쿼리해야 하거나 자주 사용되지 않는(아마 하루에 한두 번 실행되는) SQL 문도 있습니다. 이를 위해서는 버퍼링을 꺼야 합니다. 물론 query_cache_type 값을 설정하여 쿼리 캐시를 끌 수 있지만 이렇게 하면 쿼리 캐시가 영구적으로 꺼집니다. MySQL 5.0은 쿼리 캐시를 일시적으로 끄는 방법을 제공합니다: SELECT SQL_NO_CACHE field1, field2 FROM TABLE1
위 SQL 문은 SQL_NO_CACHE를 사용하므로, 이 SQL 문이 이전에 실행되었는지 여부에 관계없이 서버는 버퍼에서 검색하지 않고 매번 실행합니다.
my.ini의 query_cache_type을 2로 설정하면 SQL_CACHE를 사용한 후에만 쿼리 캐시가 사용됩니다. SELECT SQL_CALHE * FROM TABLE1
2. MySQL의 쿼리 자동 최적화
인덱스는 데이터베이스에 매우 중요합니다. 쿼리 중 성능을 향상시키기 위해 인덱스를 사용할 수 있습니다. 그러나 때로는 인덱스를 사용하면 성능이 저하될 수 있습니다. 다음 SALES 테이블을 볼 수 있습니다. CREATE TABLE SALES
(
ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
이름 VARCHAR(100) NOT NULL,
가격 변동은 NULL이 아닙니다.
SALE_COUNT INT는 NULL이 아닙니다.
SALE_DATE 날짜는 NULL이 아닙니다.
기본 키(ID),
인덱스(이름),
인덱스(SALE_DATE)
)
이 테이블에 수백만 개의 데이터가 저장되어 있고 2004년과 2005년에 제품 번호 1000의 평균 가격을 쿼리하려고 한다고 가정합니다. 다음 SQL 문을 작성할 수 있습니다. SELECT AVG(PRICE) FROM SALES
ID = 1000이고 '2004-01-01'과 '2005-12-31' 사이의 SALE_DATE입니다.
이 제품의 수량이 매우 많으면 SALES 테이블 기록의 거의 50% 이상을 차지합니다. 그런 다음 SALE_DATE 필드의 인덱스를 사용하여 평균을 계산하는 것은 약간 느립니다. 인덱스를 사용하면 인덱스를 정렬해야 하기 때문입니다. 조건에 맞는 레코드가 너무 많은 경우(예: 전체 테이블의 레코드 중 50% 이상 차지) 속도가 느려지므로 테이블 전체를 스캔하는 것이 좋습니다. 따라서 MySQL은 전체 테이블에서 조건을 만족하는 데이터의 비율을 기준으로 쿼리에 인덱스를 사용할지 여부를 자동으로 결정합니다.
MySQL의 경우 위의 쿼리 결과가 전체 테이블의 레코드에 차지하는 비율이 약 30%일 경우에는 인덱스를 사용하지 않습니다. 이 비율은 MySQL 개발자의 경험을 토대로 도출한 것입니다. 그러나 실제 배율 값은 사용되는 데이터베이스 엔진에 따라 달라집니다.
3. 인덱스 기반 정렬
MySQL의 약점 중 하나는 정렬입니다. MySQL은 1초에 약 15,000개의 레코드를 쿼리할 수 있지만 MySQL은 쿼리할 때 최대 하나의 인덱스만 사용할 수 있습니다. 따라서 WHERE 조건이 이미 인덱스를 점유하고 있는 경우 해당 인덱스는 정렬에 사용되지 않으므로 쿼리 속도가 크게 저하됩니다. 다음 SQL 문을 볼 수 있습니다. SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
NAME 필드의 인덱스는 위 SQL의 WHERE 절에서 사용되었으므로 SALE_DATE를 정렬할 때 더 이상 인덱스가 사용되지 않습니다. 이 문제를 해결하기 위해 SALES 테이블에 복합 인덱스를 생성할 수 있습니다. ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)
이렇게 하면 위의 SELECT 문을 사용하여 쿼리할 때 속도가 크게 향상됩니다. 하지만 이 방법을 사용할 때는 WHERE 절에 정렬 필드가 없는지 확인하세요. 위의 예에서는 SALE_DATE를 사용하여 쿼리할 수 없습니다. 그렇지 않으면 정렬이 더 빠르지만 SALE_DATE 필드에 별도의 인덱스가 없습니다. 이므로 쿼리 속도가 느려집니다.
4. 도달할 수 없는 쿼리 감지
SQL 문을 실행할 때 필연적으로 false여야 하는 몇 가지 조건이 발생합니다. 소위 must-false 조건은 테이블의 데이터가 어떻게 변경되든 이 조건이 false라는 것입니다. 예: WHERE 값 < 100 AND 값 > 200. 100보다 작으면서도 200보다 큰 숫자는 찾을 수 없습니다.
이러한 쿼리 조건이 발생하면 해당 SQL 문을 실행할 필요가 없습니다. 다행스럽게도 MySQL은 이러한 상황을 자동으로 감지할 수 있습니다. 예를 들어, 다음 SQL 문을 볼 수 있습니다. SELECT * FROM SALES WHERE NAME = "name1" AND NAME = "name2"
위 쿼리 문은 NAME이 name1 및 name2와 모두 동일한 레코드를 검색합니다. 분명히 이것은 도달할 수 없는 쿼리이며 WHERE 조건은 false여야 합니다. MySQL은 SQL 문을 실행하기 전에 먼저 WHERE 조건이 도달할 수 없는 쿼리인지 분석합니다. 그렇다면 SQL 문은 더 이상 실행되지 않습니다. 이것을 확인하기 위해. 먼저 EXPLAIN을 사용하여 다음 SQL을 테스트합니다. EXPLAIN SELECT * FROM SALES WHERE NAME = "name1"
위 쿼리는 EXPLAIN이 반환한 실행 정보 데이터의 테이블 항목이 SALES임을 알 수 있다. 이는 MySQL이 SALES를 운영한다는 것을 보여줍니다. 다음 명령문을 다시 살펴보십시오. EXPLAIN SELECT * FROM SALES WHERE NAME = "name1" AND NAME = "name2"
테이블 항목이 비어 있음을 알 수 있습니다. 이는 MySQL이 SALES 테이블에서 작동하지 않았음을 의미합니다.
5. 다양한 쿼리 선택을 사용하여 성능 향상
SELECT 문의 일반적인 사용 외에도 MySQL은 쿼리 성능을 향상시킬 수 있는 다양한 옵션도 제공합니다. 위에서 언급했듯이 쿼리 버퍼링을 제어하는 데 사용되는 SQL_NO_CACHE와 SQL_CACHE는 두 가지 옵션입니다. 이 섹션에서는 일반적으로 사용되는 몇 가지 쿼리 옵션을 소개하겠습니다.
1. STRAIGHT_JOIN: 강제 연결 순서
쿼리를 위해 두 개 이상의 테이블을 연결할 때 MySQL이 어떤 테이블을 먼저 연결하고 어떤 테이블을 마지막에 연결하는지 신경 쓸 필요가 없습니다. 이 모든 것은 MySQL이 일련의 내부 계산과 평가를 통해 결정한 연결 순서에 따라 결정됩니다. 다음 SQL 문에서 TABLE1과 TABLE2는 반드시 서로 연결될 필요는 없습니다. SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1, TABLE2 WHERE …
개발자가 연결 순서에 수동으로 개입해야 하는 경우 다음 SQL 문과 같은 STRAIGHT_JOIN 키워드를 사용해야 합니다. SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
위의 SQL 문에서 볼 수 있듯이 STRAIGHT_JOIN은 MySQL이 TABLE1과 TABLE2의 순서로 테이블을 조인하도록 강제하는 데 사용됩니다. MySQL에서 권장하는 순서보다 자신만의 순서로 조인하는 것이 더 효율적이라고 생각되면 STRAIGHT_JOIN을 사용하여 연결 순서를 결정할 수 있습니다.
2. 인덱스 사용에 개입하고 성능을 향상시킵니다.
위에서 인덱스의 사용에 대해 언급했습니다. 일반적인 상황에서 MySQL은 인덱스를 사용할지 여부와 쿼리할 때 사용할 인덱스를 결정합니다. 그러나 특별한 경우에는 MySQL이 하나 또는 몇 개의 인덱스만 사용하기를 원하거나 특정 인덱스를 사용하지 않기를 원합니다. 이를 위해서는 인덱스를 제어하기 위해 MySQL의 일부 쿼리 옵션을 사용해야 합니다.
인덱스 사용 범위 제한
때때로 우리는 데이터 테이블에 많은 인덱스를 생성합니다. MySQL이 인덱스를 선택할 때 이러한 인덱스가 모두 고려됩니다. 그러나 때때로 우리는 MySQL이 모든 인덱스 대신 몇 개의 인덱스만 고려하기를 원합니다. 이를 위해서는 쿼리 문을 설정하기 위해 USE INDEX를 사용해야 합니다. SELECT * FROM TABLE1 인덱스 사용(FIELD1, FIELD2) …
위의 SQL 문에서 알 수 있듯이, TABLE1에 몇 개의 인덱스가 설정되어 있더라도 MySQL은 인덱스를 선택할 때 FIELD1과 FIELD2에 설정된 인덱스만 고려한다.
사용되지 않는 인덱스 범위 제한
고려해야 할 인덱스가 많고 사용되지 않는 인덱스가 거의 없는 경우 IGNORE INDEX를 사용하여 역방향 선택을 수행할 수 있습니다. 위의 예에서는 고려되는 인덱스를 선택하고, IGNORE INDEX를 사용하는 것은 고려하지 않는 인덱스를 선택하는 것이다. SELECT * FROM TABLE1 인덱스 무시(FIELD1, FIELD2) …
위의 SQL 문에서는 TABLE1 테이블의 FIELD1과 FIELD2에 대한 인덱스만 사용되지 않습니다.
인덱스를 강제로 사용
위의 두 가지 예는 모두 MySQL에게 선택권을 제공하는데, 이는 MySQL이 이러한 인덱스를 사용할 필요가 없음을 의미합니다. 때때로 우리는 MySQL이 특정 인덱스를 사용해야 하기를 바랍니다(MySQL은 쿼리할 때 하나의 인덱스만 사용할 수 있으므로 MySQL은 하나의 인덱스만 사용하도록 강제할 수 있습니다). 이 기능을 완료하려면 FORCE INDEX를 사용해야 합니다. SELECT * FROM TABLE1 FORCE INDEX(FIELD1) …
위의 SQL 문은 다른 필드의 인덱스가 아닌 FIELD1에 작성된 인덱스만 사용합니다.
3. 임시 테이블을 사용하여 쿼리 성능 향상
쿼리 결과 집합에 많은 데이터가 있는 경우 SQL_BUFFER_RESULT 옵션을 통해 결과 집합을 임시 테이블에 강제로 적용하여 MySQL 테이블 잠금을 빠르게 해제할 수 있습니다(다른 SQL 문에서 이를 쿼리할 수 있음). records) ) 그리고 오랜 기간 동안 클라이언트에게 대규모 레코드 세트를 제공할 수 있습니다. SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
SQL_BUFFER_RESULT 옵션과 유사하게 이 옵션은 일반적으로 그룹화 또는 DISTINCT 키워드에 사용됩니다. 이 옵션은 필요한 경우 쿼리 결과가 임시 테이블에 배치되거나 정렬될 것임을 MySQL에 알립니다. SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1
6. 결론
프로그래밍에는 "20/80 원칙"도 있습니다. 즉, 코드의 20%가 시간의 80%를 차지합니다. 데이터베이스 애플리케이션 개발도 마찬가지입니다. 데이터베이스 애플리케이션의 최적화는 SQL의 실행 효율성에 중점을 둡니다. 데이터 쿼리 최적화의 초점은 데이터베이스 서버가 디스크에서 더 적은 데이터를 읽고 페이지를 비순차적 대신 순차적으로 읽도록 하는 것입니다.