데이터베이스 쿼리 성능을 최적화하는 방법은 무엇입니까? (상해)

데이터베이스 쿼리 성능을 최적화하는 방법은 무엇입니까? (자세한 설명)

쿼리는 데이터베이스 기술에서 가장 일반적으로 사용되는 작업입니다. 질의 연산 과정은 비교적 간단하다. 먼저 클라이언트로부터 질의 SQL 문을 발급받는다. 클라이언트로부터 전송된 SQL 문을 수신한 후 데이터베이스 서버는 SQL 문을 실행한 후 질의 결과를 클라이언트에 반환한다. 프로세스는 매우 간단하지만 다양한 쿼리 방법과 데이터베이스 설정이 쿼리 성능에 큰 영향을 미칩니다.

따라서 이 기사에서는 MySQL에서 일반적으로 사용되는 쿼리 최적화 기술에 대해 설명합니다. 토론에는 쿼리 버퍼링을 통한 쿼리 속도 향상, 쿼리의 인덱스 기반 정렬, 도달할 수 없는 쿼리 감지 및 성능 향상을 위한 다양한 쿼리 옵션 사용이 포함됩니다.

1. 쿼리 버퍼링을 통한 쿼리 속도 향상
일반적으로 SQL 문을 사용하여 쿼리할 때 데이터베이스 서버는 클라이언트로부터 SQL을 수신할 때마다 이 SQL 문을 실행합니다. 하지만 일정 간격(예: 1분 이내) 내에 완전히 동일한 SQL 문이 수신되면 동일한 방식으로 실행됩니다. 이를 통해 데이터의 실시간 특성이 보장될 수 있지만 대부분의 경우 데이터에 완전한 실시간이 필요하지 않으므로 특정 지연이 발생할 수 있습니다. 이 경우 짧은 시간 내에 정확히 동일한 SQL을 실행하는 것은 이득을 얻을 가치가 없습니다.
다행히 MySQL은 쿼리 버퍼링 기능을 제공합니다(쿼리 버퍼링은 MySQL 4.0.1 이상에서만 사용할 수 있습니다). 쿼리 캐싱을 통해 쿼리 성능을 어느 정도 향상시킬 수 있습니다.

MySQL 설치 디렉터리에 있는 my.ini 파일을 통해 쿼리 버퍼를 설정할 수 있습니다. 설정도 매우 간단합니다. query_cache_type을 1로 설정하면 됩니다. 이 속성을 설정한 후 MySQL은 SELECT 문을 실행하기 전에 동일한 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,
NAME VARCHAR(100) NOT NULL,
PRICE FLOAT NOT NULL,
SALE_COUNT INT NOT NULL,
SALE_DATE DATE NOT NULL,
PRIMARY KEY(ID)，
INDEX (NAME)，
INDEX (SALE_DATE)
)

이 테이블에 수백만 개의 데이터가 저장되어 있고, 2004년과 2005년에 제품 번호가 1000인 제품의 평균 가격을 쿼리하려고 한다고 가정합니다. 다음과 같은 SQL 문을 작성할 수 있습니다.

SELECT AVG(PRICE) FROM SALES
WHERE ID = 1000 AND SALE_DATE BETWEEN '2004-01-01' AND '2005-12-31';

이 제품의 수량이 매우 많으면 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字段上没有单独的索引，因此查询又会慢下来。

四、 不可达查询的检测

在执行SQL语句时，难免会遇到一些必假的条件。所谓必假的条件是无论表中的数据如何变化，这个条件都为假。如WHERE value 200。我们永远无法找到一个既小于100又大于200的数。
如果遇到这样的查询条件，再去执行这样的SQL语句就是多此一举。幸好MySQL可以自动检测这种情况。如我们可以看看如下的SQL语句:

SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”

以上的查询语句要查找NAME既等于name1又等于name2的记录。很明显，这是一个不可达的查询，WHERE条件一定是假。MySQL在执行SQL语句之前，会先分析WHERE条件是否是不可达的查询，如果是，就不再执行这条SQL语句了。为了验证这一点。我们首先对如下的SQL使用EXPLAIN进行测试:

EXPLAIN SELECT * FROM SALES WHERE NAME = “name1”

上面的查询是一个正常的查询，我们可以看到使用EXPLAIN返回的执行信息数据中table项是SALES。这说明MySQL对SALES进行操作了。再看看下面的语句:

EXPLAIN SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”

我们可以看到，table项是空，这说明MySQL并没有对SALES表进行操作。

五、 使用各种查询选择来提高性能

SELECT语句除了正常的使用外，MySQL还为我们提供了很多可以增强查询性能的选项。如上面介绍的用于控制查询缓冲的SQL_NO_CACHE和SQL_CACHE就是其中两个选项。在这一部分，我将介绍几个常用的查询选项。

1. STRAIGHT_JOIN:强制连接顺序

当我们将两个或多个表连接起来进行查询时，我们并不用关心MySQL先连哪个表，后连哪个表。而这一切都是由MySQL内部通过一系列的计算、评估，最后得出的一个连接顺序决定的。如下列的SQL语句中，TABLE1和TABLE2并不一定是谁连接谁:

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 ,TABLE2 WHERE …

如果开发人员需要人为地干预连接的顺序，就得使用STRAIGHT_JOIN关键字，如下列的SQL语句:

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 USE INDEX (FIELD1, FIELD2) …

从以上SQL语句可以看出，无论在TABLE1中已经建立了多少个索引，MySQL在选择索引时，只考虑在FIELD1和FIELD2上建立的索引。

限制不使用索引的范围

如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引，而使用IGNORE INDEX是选择不被考虑的索引。

SELECT * FROM TABLE1 IGNORE INDEX (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语句就可以对这些记录进行查询了)，并且可以长时间地为客户端提供大记录集。

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

和SQL_BUFFER_RESULT.选项类似的还有SQL_BIG_RESULT，这个选项一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1

六、 结论
在程序设计中同样存在一个“二八原则”，即20%的代码用去了80%的时间。数据库应用程序的开发亦然。数据库应用程序的优化，重点在于SQL的执行效率。而数据查询优化的重点，则是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。 

推荐教程：《MySQL教程》

위 내용은 데이터베이스 쿼리 성능을 최적화하는 방법은 무엇입니까? (상해)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!