MySQL 성능 튜닝 쿼리 최적화

이 기사는 mysql에 대한 관련 지식을 제공합니다. 쿼리 최적화를 비롯한 성능 최적화와 관련된 내용을 주로 소개합니다. 모두에게 도움이 되기를 바랍니다.

추천 학습: mysql 비디오 튜토리얼

빠른 쿼리를 작성하기 전에 실제로 중요한 것은 응답 시간이라는 점을 분명히 해야 하며, 쿼리를 실행하는 동안 각 단계에 걸리는 시간을 알아야 합니다. 전체 SQL 문 실행 효율성을 저하시키는 주요 단계를 파악하는 데 얼마나 걸리나요? 이를 위해서는 쿼리의 수명 주기를 알고 최적화해야 합니다. 일반화하지 마세요. 상황을 자세히 분석하세요.

1. 쿼리 속도가 느린 이유

2. CPU

4. 시스템 호출

6.

2. 데이터 액세스 최적화

1. 쿼리 성능이 저하되는 주된 이유는 너무 많은 데이터에 액세스하기 때문입니다. 일부 쿼리에서는 필연적으로 많은 양의 데이터를 필터링해야 합니다

( 1 ) 애플리케이션이 필요 이상으로 많은 양의 데이터를 검색하고 있는지 확인

(2) mysql 서버 계층이 필요 이상으로 많은 양의 데이터 행을 분석하고 있는지 확인

2. 불필요한 데이터를 데이터베이스에서 요청하는지 확인

(1) 쿼리가 필수 레코드가 아닙니다. (우리는 종종 mysql이 필요한 데이터만 반환한다고 잘못 생각합니다. 실제로 mysql은 모든 결과를 먼저 반환한 다음 계산을 수행합니다. 일상적인 개발 습관에서는 select 문을 사용하여 쿼리하는 경우가 많습니다. 많은 수의 결과를 얻은 다음 처음 N 행 이후에 결과 집합을 닫습니다. 최적화 방법은 쿼리 후 제한을 추가하는 것입니다.

(2) 여러 테이블이 연결되면 모든 열을 반환합니다(액터 내부 조인에서 * 선택). (actor_id)를 사용하는 film_actor (film_id)를 사용하는 내부 조인 필름 where film.title='Academy Dinosaur';select actor.* from actor...;)

(3) 항상 모든 열을 제거하십시오(회사의 기업 요구 사항에 따라 select * 사용은 금지됩니다. 이 방법은 개발을 단순화할 수 있지만 쿼리 성능에 영향을 미치므로 사용하지 마십시오.)

(4) 동일한 데이터를 반복적으로 쿼리합니다(동일한 쿼리를 반복적으로 실행해야 하는 경우). 매번 정확히 동일한 데이터를 쿼리하고 반환하므로 이러한 응용 시나리오를 기반으로 데이터의 이 부분을 캐시할 수 있어 쿼리 효율성이 향상될 수 있습니다.)

3. 실행 프로세스 최적화

1.

쿼리 문을 구문 분석하기 전에 쿼리 캐시가 켜져 있으면 mysql은 쿼리가 쿼리 캐시의 데이터에 도달했는지 먼저 확인합니다. 쿼리가 쿼리 캐시에 도달하면 먼저 사용자 권한을 확인합니다. 결과를 반환합니다. 권한에 문제가 없으면 mysql은 모든 단계를 건너뛰고 캐시에서 직접 결과를 가져와 클라이언트에 반환합니다

2. 쿼리 최적화 처리

mysql이 캐시를 쿼리한 후, SQL 구문 분석, 전처리 및 SQL 실행 계획 최적화 단계를 거치게 됩니다. 오류가 발생하면 쿼리가 종료될 수 있습니다.

(1) 문법 파서 및 전처리

mysql은 키워드를 통해 SQL 문을 구문 분석하고 구문 분석 트리를 생성합니다. mysql 파서는 mysql 문법 규칙을 사용하여 키워드의 사용 여부를 확인하는 등 쿼리를 확인하고 구문 분석합니다. 또는 순서가 올바른지 등을 확인하면 전처리기는 테이블 이름과 열 이름이 존재하는지, 모호성이 있는지 등 구문 분석 트리가 적합한지 여부를 추가로 확인하고 권한도 확인합니다.

(2) 쿼리 옵티마이저 구문 트리에 문제가 없으면 옵티마이저는 이를 실행 계획으로 변환합니다. 쿼리 문은 다양한 실행 방법을 사용할 수 있으며 결국 해당 결과를 얻을 수 있지만 효율성은 다릅니다. 실행 방법이 다릅니다. 최적화 프로세서의 주요 목적은 가장 효과적인 실행 계획을 선택하는 것입니다.

MySQL은 최적화 중에 특정 쿼리 계획을 사용하여 쿼리 비용을 예측하고 비용이 가장 작은 것을 선택하려고 시도합니다.

a.film_actor에서 count(*)를 선택하세요.

(a) 각 테이블 또는 인덱스의 페이지 수

(b) 인덱스의 카디널리티

(c) 인덱스 및 데이터의 길이 행

(d) 인덱스 분포

b 많은 경우 mysql은 다음과 같은 이유로 잘못된 실행 계획을 선택합니다.

(a) 부정확한 통계 정보(InnoDB는 행 수에 대한 정확한 통계 정보를 유지할 수 없습니다. mvcc 아키텍처로 인해 데이터 테이블의 행)

(b) 실행 계획의 비용 추정은 실제 실행 비용과 동일하지 않습니다. (때로는 실행 계획에서 더 많은 페이지를 읽어야 하지만 이러한 페이지를 순차적으로 읽는 경우 비용이 더 적습니다. 메모리에 있으면 액세스 비용이 매우 작습니다. mysql 수준은 어떤 페이지가 메모리에 있고 어떤 페이지가 디스크에 있는지 알지 못하므로 쿼리 실행 중에 필요한 IO 시간을 알 수 없습니다.)

(c ) mysql의 최적값은 여러분이 생각하는 것과 다를 수 있습니다(mysql의 최적화는 비용 모델 최적화를 기반으로 하지만 가장 빠른 최적화는 아닐 수 있습니다)

(d) mysql은 동시에 실행되는 다른 쿼리를 고려하지 않습니다

( e) MySQL은 통제할 수 없는 운영 비용(저장 프로시저 또는 사용자 정의 함수 실행 비용)을 고려하지 않습니다.

c. 옵티마이저의 최적화 전략

(a) 정적 최적화(파싱 트리 직접 분석) ) , 그리고 최적화 완료)

(b) 동적 최적화 (동적 최적화는 쿼리의 컨텍스트와 관련이 있으며 인덱스에 해당하는 값 및 행 수와 관련될 수도 있음)

(c) MySQL 쿼리는 정적으로 한 번만 최적화하면 되지만 동적 최적화는 실행될 때마다 재평가가 필요하며, 옵티마이저의 최적화 유형

(a) 관련 테이블의 순서를 재정의합니다(데이터 테이블의 연관 관계가 아님) 항상 쿼리에 지정된 순서대로 수행됩니다. 연결 순서를 결정할 때 옵티마이저의 매우 중요한 기능입니다.)

(b) 외부 조인을 내부 조인으로 변환하는 것이 외부 조인보다 효율적입니다

(c) 사용 등가 변환 규칙, mysql은 일부 등가물을 사용할 수 있습니다. 식을 단순화하고 계획하기 위한 변경 사항

(d) count(), min(), max() 최적화(인덱스 및 열이 null일 수 있는지 여부는 mysql이 이러한 유형의 식을 최적화하는 데 도움이 되는 경우가 많습니다) : 예를 들어 특정 열 값의 최소값을 찾으려면 인덱스의 가장 왼쪽 레코드만 쿼리하면 되며 전체 텍스트 검색 및 비교가 필요하지 않습니다.)

(e) 추정하여 상수 표현식으로 변환합니다. mysql이 표현식이 상수로 변환될 수 있음을 감지하면 표현식은 항상 상수로 처리됩니다. (film.film_id = 1인(film_id)를 사용하여 film 내부 조인 film_actor에서 film.film_id,film_actor.actor_id를 선택한다고 설명하세요.)

(f) 인덱스의 열에 사용해야 하는 모든 열이 포함된 경우 인덱스 적용 범위 스캔 쿼리, 커버링 인덱스를 사용할 수 있습니다

(g) 하위 쿼리 최적화(mysql은 경우에 따라 하위 쿼리를 보다 효율적인 형식으로 변환할 수 있으므로 자주 쿼리되는 등 데이터에 여러 번 액세스하는 다중 쿼리를 줄일 수 있습니다. 데이터가 캐시에 저장됩니다. )

(h) 등가 전파(두 열의 값이 동등하게 관련되어 있는 경우 mysql은 한 열의 where 조건을 다른 열에 전달할 수 있습니다.

explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;

여기에서 사용 film_id 필드는 동등하게 관련되어 있습니다. film_id 컬럼은 film 테이블뿐만 아니라 film_actor 테이블

explain select film.film_id from film inner join film_actor using(film_id
 ) where film.film_id > 500 and film_actor.film_id > 500;）

e에도 적용 가능합니다. 관련 쿼리

mysql의 관련 쿼리는 매우 중요하지만 실제로 관련 쿼리를 실행하는 전략은 비교적 간단합니다. mysql은 모든 연관에서 매우 중요한 것은 중첩된 루프 연관 작업을 수행하는 것입니다. 즉, MySQL은 먼저 한 테이블의 단일 데이터 조각을 루프한 다음 이를 다음 테이블에 중첩하여 일치하는 행을 찾는 식으로 모든 항목에서 일치하는 동작이 나타날 때까지 계속됩니다. 테이블에서 일치하는 행은 쿼리에 필요한 열을 반환합니다. MySQL은 마지막 관련 테이블에서 일치하는 모든 행을 찾으려고 시도합니다. 마지막 관련 테이블에서 더 이상 행을 찾을 수 없으면 MySQL은 다음으로 돌아갑니다. 이전 레벨 관련 테이블에서 일치하는 레코드를 더 찾을 수 있는지 확인하는 등 전반적인 아이디어는 동일하지만 실제 실행 프로세스에는 여러 변형이 있습니다.

f, 정렬 최적화

상관없습니다. 뭐, 정렬은 비용이 많이 들기 때문에 성능 측면에서 최대한 피하거나 대용량 데이터를 정렬해야 합니다.

정렬에는 인덱스를 사용하는 것이 좋습니다. 인덱스를 사용할 수 없으면 MySQL이 직접 정렬해야 하는데, 데이터의 양이 적으면 메모리에서 처리하고, 데이터의 양이 많으면 디스크를 사용한다. 정렬할 데이터의 양이 정렬 버퍼('%sort_buffer_size%';와 같은 변수 표시)보다 작습니다. 메모리를 사용하여 정렬 작업에 메모리가 충분하지 않으면 MySQL이 먼저 데이터를 나눕니다. 트리를 블록으로 나누고, 퀵 정렬을 사용하여 각 독립 블록을 정렬하고, 각 블록의 정렬 결과를 디스크에 저장한 후, 잘 정렬된 블록을 병합하여 최종적으로 정렬 결과를 반환합니다. 정렬 알고리즘:

(a) 두 가지 전송 정렬

첫 번째 데이터 읽기는 정렬이 필요한 필드를 읽은 다음 정렬하는 것입니다. 두 번째는 필요에 따라 정렬된 결과에 따라 데이터 행을 읽는 것입니다.

이 방법은 두 번째로 데이터를 읽을 때 정렬되어 있기 때문에 모든 레코드를 읽어야 하기 때문에 이 때 더 많은 임의 IO가 사용되며 데이터를 읽는 비용이 발생하기 때문입니다. be high

두 번 전송의 장점은 정렬 중에 가능한 한 적은 데이터를 저장하여 정렬 버퍼가 정렬 작업을 위해 가능한 한 많은 행을 수용할 수 있다는 것입니다

(b) 단일 전송 정렬

先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

（c）如何选择

当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式

四、优化特定类型的查询

1、优化count()查询

count()是特殊的函数，有两种不同的作用，一种是某个列值的数量，也可以统计行数。

（1）总有人认为myisam的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的。

（2）使用近似值

在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如可以使用explain来获取近似的值
其实在很多OLAP的应用中，需要计算某一个列值的基数，有一个计算近似值的算法叫hyperloglog。

（3）更复杂的优化

一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统。

2、优化关联查询

（1）确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序

当表A和表B使用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要再B表的对应列上建上索引，没有用到的索引只会带来额外的负担，一般情况下来说，只需要在关联顺序中的第二个表的相应列上创建索引。

（2）确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程

3、优化子查询

子查询的优化最重要的优化建议是尽可能使用关联查询代替

4、优化limit分页

在很多应用场景中我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的orderby 的子句，如果这种方式有索引的帮助，效率通常不错，否则的话需要进行大量的文件排序操作，还有一种情况，当偏移量非常大的时候，前面的大部分数据都会被抛弃，这样的代价太高。
要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能。

优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列。

select film_id,description from film order by title limit 50,5;
explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

5、优化union查询

mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化

除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高。

6、推荐使用用户自定义变量

用户自定义变量是一个容易被遗忘的mysql特性，但是如果能够用好，在某些场景下可以写出非常高效的查询语句，在查询中混合使用过程化和关系话逻辑的时候，自定义变量会非常有用。
用户自定义变量是一个用来存储内容的临时容器，在连接mysql的整个过程中都存在。

（1）自定义变量的使用

set @one :=1
set @min_actor :=(select min(actor_id) from actor)
set @last_week :=current_date-interval 1 week;

（2）自定义变量的限制

a、无法使用查询缓存

b、不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名或者limit子句

c、用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信

d、不能显式地声明自定义变量地类型

e、mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行

f、赋值符号：=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号。

g、使用未定义变量不会产生任何语法错误。

（3）自定义变量的使用案例

a、优化排名语句

在给一个变量赋值的同时使用这个变量

select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;

查询获取演过最多电影的前10名演员，然后根据出演电影次数做一个排名

select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;

b、避免重新查询刚刚更新的数据

当需要高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么

update t1 set  lastUpdated=now() where id =1;
select lastUpdated from t1 where id =1;
update t1 set lastupdated = now() where id = 1 and @now:=now();
select @now;

c、确定取值的顺序

在赋值和读取变量的时候可能是在查询的不同阶段

（a）set @rownum:=0;

select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;

因为where和select在查询的不同阶段执行，所以看到查询到两条记录，这不符合预期

（b）set @rownum:=0;

select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name

当引入了orde;r by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而where条件是在文件排序操作之前取值的  。

（c）解决这个问题的关键在于让变量的赋值和取值发生在执行查询的同一阶段：

set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;

推荐学习：mysql视频教程

위 내용은 MySQL 성능 튜닝 쿼리 최적화의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!