MySQL이 쿼리 속도를 최적화하는 방법 알아보기

이전 장에서는 고성능 MySQL에 필수적인 최적화된 데이터 유형 선택 방법과 인덱스를 효율적으로 사용하는 방법을 소개했습니다. 그러나 이것만으로는 충분하지 않으며 합리적인 쿼리 설계도 필요합니다. 쿼리를 제대로 작성하지 않으면 테이블 구조가 아무리 합리적이고 인덱스가 적절하더라도 높은 성능을 얻을 수 없습니다.

MySQL 성능 최적화에 있어서 쿼리 최적화는 최적화의 원천이며 시스템이 더 빠른지 여부를 가장 잘 반영할 수도 있습니다. 이 장과 다음 장에서는 쿼리 성능 최적화에 중점을 두고 MySQL이 실제로 쿼리를 실행하는 방법, 쿼리가 느린 부분, 속도를 높이는 방법을 더 깊이 이해하고 이해하는 데 도움이 되는 몇 가지 쿼리 최적화 기술을 소개합니다. 효율성이 높고 비효율적인 이유는 쿼리 SQL 문을 더 잘 최적화하는 데 도움이 됩니다.

관련 학습 권장 사항: mysql 비디오 튜토리얼

이 장은 "쿼리 속도가 왜 이렇게 느린가요?"에서 시작하므로 쿼리가 느릴 수 있는 부분을 명확하게 알 수 있으므로 쿼리를 더 잘 최적화하는 데 도움이 됩니다. 이를 인지하고 남들보다 한발 앞서가는 것입니다.

1. 느린 부분은 어디입니까?

쿼리 속도의 실제 척도는 응답 시간입니다. 쿼리를 작업으로 생각하면 각 작업에는 일정한 시간이 걸리는 일련의 하위 작업으로 구성됩니다. 쿼리를 최적화하려면 실제로 해당 하위 작업을 최적화해야 하므로 해당 하위 작업 중 일부를 제거한 다음 하위 작업 실행 횟수를 줄이거나 하위 작업 실행 속도를 높이세요.

MySQL이 쿼리를 실행할 때 어떤 하위 작업이 있으며, 어떤 하위 작업에 가장 많은 시간이 걸리나요? 이를 위해서는 일부 도구나 방법(예: 실행 계획)을 사용하여 쿼리를 분석하여 속도 저하가 발생하는 위치를 찾아야 합니다.

일반적으로 쿼리의 수명주기는 클라이언트에서 서버로, 서버에서 구문 분석되고, 실행 계획이 생성되고, 실행되고, 결과가 클라이언트로 반환되는 순서로 대략적으로 볼 수 있습니다. 그 중 "실행"은 전체 수명주기에서 가장 중요한 단계로 간주될 수 있으며, 여기에는 데이터 검색을 위한 스토리지 엔진에 대한 수많은 호출과 호출 후 정렬, 그룹화 등의 데이터 처리가 포함됩니다.

이러한 작업을 완료할 때 쿼리는 네트워크, CPU 계산, 통계 및 실행 계획 생성, 잠금 대기 및 기타 작업, 특히 기본 스토리지 엔진에서 데이터를 검색하기 위한 호출 작업을 포함하여 다양한 단계의 다양한 위치에서 시간을 소비해야 합니다. , 이러한 호출에는 메모리 작업, CPU 작업이 필요하며 많은 수의 컨텍스트 전환 및 시스템 호출이 생성될 수도 있습니다.

위 작업에는 많은 시간이 소요되며, 일부 불필요한 추가 작업이 여러 번 반복되거나 일부 작업이 매우 느리게 수행될 수 있습니다. 쿼리 최적화의 목적은 이러한 작업에 소요되는 시간을 줄이고 없애는 것입니다 .

위의 분석을 통해 쿼리 프로세스에 대한 전반적인 이해를 갖게 되었으며, 쿼리의 어디에서 문제가 발생할 수 있는지 명확하게 알 수 있으며, 이로 인해 결국 쿼리 전체가 느려지는 원인이 되어 실제 쿼리 최적화를 위한 방향을 제시할 수 있습니다.

즉, 쿼리 최적화는 다음 두 가지 관점에서 접근할 수 있습니다.

서브 쿼리 수 줄이기
• 추가 및 반복 작업 줄이기

쿼리 성능이 저하되는 일반적인 이유는 너무 많은 데이터에 액세스하기 때문입니다. 데이터의 양이 적을 때는 쿼리 속도가 상당히 좋습니다. 데이터의 양이 늘어나면 쿼리 속도가 급격하게 변하여 사람들을 미치게 만들고 매우 열악한 경험을 선사합니다. 쿼리 최적화에 관해서는 다음과 같은 측면에서 확인할 수 있습니다.

불필요한 데이터가 쿼리되는지 여부
• 추가 레코드가 스캔되는지 여부

두 번째, 불필요한 데이터가 쿼리되는지

실습 중에 여러 번 쿼리를 수행하면 실제 필요한 데이터가 쿼리되고 중복된 데이터는 응용 프로그램에서 삭제됩니다. 이는 MySQL에 대한 추가 오버헤드이며 애플리케이션 서버의 CPU 및 메모리 리소스도 소비합니다.

몇 가지 일반적인 경우는 다음과 같습니다.

1. 불필요한 레코드 쿼리

이것은 흔히 MySQL이 필요한 데이터만 반환한다고 잘못 생각하는 경우가 있습니다. 실제로 MySQL은 전체 결과 집합을 반환한 다음 반환합니다. 계산합니다.

개발자는 습관적으로 SELECT 문을 사용하여 많은 수의 결과를 쿼리한 다음 애플리케이션 쿼리 또는 프런트 엔드 디스플레이 레이어를 사용하여 이전 N 행의 데이터를 가져옵니다. 예를 들어 뉴스 웹 사이트에서 100개의 레코드를 쿼리하지만 페이지 10개 항목의 처음 N행을 표시합니다.

가장 효과적인 솔루션은 필요한 만큼 많은 레코드를 쿼리하는 것입니다. 일반적으로 쿼리 뒤에 LIMIT가 추가됩니다. 즉, 페이징 쿼리입니다.

2. 여러 테이블 연결 시 모든 열 반환

영화 아카데미 공룡에 출연한 모든 배우를 쿼리하려는 경우 다음과 같은 방식으로 쿼리하지 마세요.

select * fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

이렇게 하면 세 개의 테이블이 반환됩니다. 모든 데이터 열 , 실제 요구 사항은 배우 정보를 쿼리하는 것입니다. 이를 작성하는 올바른 방법은 다음과 같습니다.

select a.* fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

3. 总是查询出全部列

每次看到select *的时候一定要用异样的目光来审视它，是不是真的需要返回全部数据列？

在大部分情况下，是不需要的。 select *会导致进行全表扫描，会让优化器无法完成索引扫描这类优化，过多的列还会为服务器带来额外的I/O、内存和CPU的消耗。 即使真的需要查询出全部列，应该逐个罗列出全部列而不是*。

4. 重复查询相同的数据

如果你不太留意，很容易出现这样的错误： 不断地重复执行相同的查询，然后每次都返回完全相同的数据。

例如，在用户评论的地方需要查询用户头像的URL，那么用户多次评论的时候，可能就会反复来查询这个数据。 比较好处理方法是，在初次查询的时候将这个数据缓存起来，后续使用时直接从缓存中取出。

三、是否扫描了额外的记录

确 定查询只查询了需要的数据以后，接下来应该看看查询过程中是否扫描了过多的数据。 对于MySQL，最简单衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

没有哪个指标能够完全来衡量查询的开销，但它们能够大致反映MySQL内部执行查询时需要访问多少数据，并可以大概推算出查询运行的实际。 这三个指标都会记录到MySQL的慢日志中，所以 检查慢日志记录是找出扫描行数过多查询的办法 。

慢查询： 用于记录在MySQL中响应时间超过阈值(long_query_time,默认10s)的语句，并会将慢查询记录到慢日志中。 可通过变量slow_query_long来开启慢查询，默认是关闭状态，可以将慢日志记录到表slow_log或文件中，以供检查分析。

1. 响应时间

响应时间是两个部分之和： 服务时间和排队时间。 服务时间是指数据库处理这个查询真正花费了多长时间。 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间，可能是等待I/O操作，也可能是等待 行 锁等等。

在不同类型的应用压力下，响应时间并没有什么一致的规律或者公式。 诸如存储引擎的锁（表锁，行锁），高并发资源竞争，硬件响应等诸多因素都会影响响应时间，所以，响应时间既可能是一个问题的结果也可能是一个问题的原因，不同案例情况不同。

当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个合理的值。

2. 扫描的行数和返回的行数

在分析查询时，查看该查询扫描的行数是非常有帮助的，在此之上也能够分析是否扫描了额外的记录。

对于找出那些糟糕查询，这个指标可能还不够完美，因为并不是所有行的访问代价都是相同的。 较短的行的访问速度相当快，内存中的行也比磁盘中的行的访问速度要快的多。

理想的情况下，扫描的行数和返回的行数应该是相同的。 但实际上这种美事并不多，例如在做一个关联查询的时候，扫描的行数和对返回的行数的比率通常都很小，一般在1:1和10：1之间，不过有时候这个值也可能非常大。

3. 扫描的行数和访问类型

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。 MySQL有好几种访问方式可以查找并返回一行结果。 这些访问方式可能需要访问很多行才能返回一条结果，也有些访问方式可能无需扫描就能返回结果。

在执行计划EXPLAIN语句中的type列反映了访问类型。 访问类型有很多种，从全表扫描到索引扫描，范围扫描，唯一索引，常数索引等。 这里列的这些，速度是从慢到快，扫描的行数也是从多到少。

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引，这也是我们之前讨论索引的问题。 现在应该明白为什么索引对于查询优化如此重要了。 索引让MySQL以最高效，扫描行数最少的方式找到需要的记录 。

如果发现查询扫描了大量的数据但只返回少数的行，通常可以尝试下面的技巧去优化它：

• 인덱스 커버리지 스캔을 사용하여 필요한 모든 열을 인덱스에 넣으면 스토리지 엔진이 해당 행을 얻기 위해 테이블로 돌아가지 않고도 결과를 반환할 수 있습니다.
  
• 테이블 구조를 최적화하세요. 예를 들어 별도의 요약 테이블을 사용하여 쿼리를 완료합니다.
• MySQL 최적화 프로그램이 보다 최적화된 방식으로 쿼리를 실행할 수 있도록 복잡한 쿼리를 다시 작성하세요.

관련 추천: 프로그래밍 비디오 강좌

위 내용은 MySQL이 쿼리 속도를 최적화하는 방법 알아보기의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!