MySQL 쿼리 최적화에 대한 자세한 설명

1. 최적화의 아이디어와 원칙은 무엇입니까

1. 최적화가 필요한 쿼리를 최적화합니다
2. 최적화 개체의 성능 병목 지점을 찾습니다
3. 최적화 목표를 명확히 합니다
4. 시작합니다. 설명
5. 프로필을 더 많이 사용하세요
6. 큰 결과 세트를 구동하려면 항상 작은 결과 세트를 사용하세요
7. 인덱스 정렬을 최대한 완료하세요
8. 필요한 필드(열)만 빼세요
9. 오직 사용하세요 가장 효과적인 필터링 조건
10. 복잡한 조인을 최대한 피하세요

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

1. 최적화가 필요한 쿼리 최적화

높은 동시성 및 낮은 소비 ( 상대적으로) 쿼리는 전체 시스템에 영향을 미칩니다. 동시성이 낮고 사용량이 많은 쿼리보다 훨씬 큽니다.

2. 최적화 개체의 성능 병목 현상을 찾습니다

최적화가 필요한 쿼리를 받으면 먼저 쿼리의 병목 현상이 IO인지 CPU인지 확인해야 합니다. 더 많은 것을 소비하는 것이 데이터베이스 액세스입니까, 아니면 더 많은 것을 소비하는 데이터 작업(예: 그룹화 및 정렬)입니까?

3. 명확한 최적화 목표

데이터베이스의 현재 전반적인 상태를 이해하면 데이터베이스가 견딜 수 있는 최대 압력, 즉 가장 비관적인 상황을 알 수 있습니다.
관련 데이터베이스 개체 정보를 파악합니다. 쿼리에 대해 우리는 최상의 조건과 최악의 조건에서 얼마나 많은 리소스가 소비되는지 알 수 있습니다.
응용 시스템에서 쿼리의 상태를 알기 위해 쿼리가 점유할 수 있는 시스템 리소스의 비율을 분석할 수 있습니다. 또한 고객 경험에 대한 쿼리의 효율성이 얼마나 큰 영향을 미치는지 알 수 있습니다.

4. 시작하기

Explain은 이 쿼리가 데이터베이스에 어떤 종류의 실행 계획을 구현하는지 알려줍니다. 우선 목표를 가지고 끊임없이 조정하고 노력한 다음, 설명을 사용하여 결과가 우리의 요구 사항을 충족하는지 확인하면 예상한 결과를 얻을 수 있습니다.

5. 큰 결과 세트를 구동하려면 항상 작은 결과 세트를 사용하세요.

많은 사람들은 SQL을 최적화할 때 "작은 테이블이 큰 테이블을 구동합니다"라고 말하고 싶어합니다. where 조건에 의해 필터링된 후 대형 테이블에서 반환된 결과 집합이 반드시 소형 테이블에서 반환된 결과 집합보다 클 필요는 없으므로, 이때 소형 테이블을 구동하기 위해 대형 테이블을 사용하면 반대의 성능 효과가 발생합니다. 얻을 수 있습니다.
이 결과도 MySQL에는 Nested Loop라는 Join 메소드가 하나만 존재합니다. 즉, MySQL의 Join은 중첩 루프를 통해 구현됩니다. 구동된 결과 집합이 클수록 더 많은 루프가 필요하며 구동 테이블에 액세스할 때마다 필요한 논리적 IO가 매우 작더라도 루프 수가 자연스럽게 늘어납니다. 당연히 총량이 아주 작을 수는 없고, 각 사이클마다 CPU를 소모하게 되므로 CPU 계산량도 늘어나게 됩니다. 따라서 구동 테이블을 판단하는 기준으로 테이블의 크기만 사용한다면, 작은 테이블을 필터링한 후 남겨진 결과 집합이 큰 테이블의 결과보다 훨씬 클 경우 결과는 필수 중첩에서 더 많은 루프가 될 것입니다. 반대로, 필요한 사이클 수가 줄어들고 전체 IO 및 CPU 작업량도 줄어듭니다. 더욱이 Oracle의 Hash Join과 같은 비Nested Loop Join 알고리즘의 경우에도 여전히 큰 결과 집합을 구동하기 위한 작은 결과 집합에 대한 최적의 선택입니다.
따라서 조인 쿼리를 최적화할 때 가장 기본적인 원칙은 "작은 결과 세트가 큰 결과 세트를 구동한다"입니다. 이 원칙을 통해 중첩 루프의 루프 수를 줄이고 총 IO 양과 CPU 작업 수를 줄일 수 있습니다. . 가능한 한 많이 인덱스 정렬을 완료하세요

6. 필요한 필드(열)만 꺼내세요

모든 쿼리에 대해 반환된 데이터는 네트워크 데이터 패킷을 통해 클라이언트에 전송되어야 합니다. 꺼내면 전송해야 하는 데이터의 양이 자연스럽게 늘어나는데, 이는 네트워크 대역폭이나 네트워크 전송 버퍼 측면에서 볼 때 낭비입니다.

7. 가장 효과적인 필터링 조건만 사용하세요.

예를 들어 사용자 테이블에는 id 및 nick_name과 같은 필드가 있습니다. 다음은 두 개의 쿼리 문입니다.

#1
select * from user where id = 1 and nick_name = 'zs';
#2
selet * from user where id = 1

결과는 다음과 같습니다. 동일하지만 첫 번째 문에서 사용하는 인덱스가 두 번째 문보다 훨씬 더 많은 공간을 차지합니다. 더 많은 공간을 차지한다는 것은 더 많은 데이터를 읽어야 한다는 의미이기도 합니다. 즉, 2의 질의문이 최적의 질의이다.

8. 복잡한 조인 쿼리 방지

쿼리에 포함되는 테이블이 많을수록 잠가야 하는 리소스도 많아집니다. 즉, Join 문이 복잡할수록 잠그는 데 필요한 리소스가 늘어나고 차단하는 다른 스레드도 많아집니다. 반대로, 더 복잡한 쿼리 문을 여러 개의 간단한 쿼리 문으로 분할하고 단계별로 실행하면 매번 잠기는 리소스가 줄어들고 차단되는 다른 스레드도 줄어듭니다.
많은 사람들이 질문을 할 수 있습니다. 복잡한 Join 문을 여러 개의 간단한 쿼리 문으로 분할하면 더 많은 네트워크 상호 작용이 발생하지 않을까요? 전체 쿼리를 완료하는 데 시간이 더 오래 걸리지 않습니까? 예, 가능하지만 확실하지는 않습니다. 복잡한 쿼리문이 실행될 때 더 많은 리소스를 잠궈야 하고, 간단한 쿼리라면 잠궈야 할 리소스가 적기 때문에 다른 사람에 의해 차단될 확률이 더 높습니다. 차단될 확률도 훨씬 낮아집니다. 따라서 더 복잡한 연결 쿼리는 실행 전에 차단되어 더 많은 시간을 낭비할 수 있습니다. 더욱이, 우리 데이터베이스는 이 쿼리 요청뿐만 아니라 수많은 다른 요청도 처리합니다. 동시성이 높은 시스템에서는 전체 처리 능력을 향상시키기 위해 단일 쿼리의 짧은 응답 시간을 희생하는 것이 매우 가치가 있습니다. 최적화 자체는 균형과 절충의 기술입니다. 절충점을 알고 전체의 균형을 유지해야만 시스템이 더 좋아질 수 있습니다.

2. 설명 및 프로파일링 사용

1. 설명은

다양한 정보 표시

ID을 사용합니다. Select_type DEPENDENT SUBQUERY: 외부 쿼리 결과 집합에 따라 달라지는 하위 쿼리 내부 레이어의 첫 번째 SELECT 테이블 TYPE ALL: 전체 테이블 스캔 Possible_keysKeyKey_len행Extra

2、Profiling使用

该工具可以获取一条Query在整个执行过程中多种资源消耗情况，如CPU,IO,IPC,SWAP等，以及发生PAGE FAULTS, CONTEXT SWITCHE等等，同时还能得到该Query执行过程中MySQL所调用的各个函数在源文件中的位置。

1、开启profiling参数 1-开启，0-关闭

#开启profiling参数 1-开启，0-关闭set profiling=1;SHOW VARIABLES LIKE '%profiling%';

2、然后执行一条Query

3、获取系统保存的profiling信息

show PROFILES;

4、通过QUERY_ID获取profile的详细信息（下面以获取CPU和IO为例）

show profile cpu, block io for QUERY 7;

三、合理利用索引

1、什么是索引

 简单来说，在关系型数据库中，索引是一种单独的，物理的对数据库表中一列或者多列的值进行排序的一种存储结构。就像书的目录，可以根据目录中的页码快速找到需要的内容。
 在MySQL中主要有四种类型索引，分别是：B-Tree索引，Hash索引，FullText索引，R-Tree索引，下面主要说一下我们常用的B-Tree索引，其他索引可以自行查找资料。

2、索引的数据结构

 一般来说，MySQL中的B-Tree索引的物理文件大多数都是以平衡树的结构来存储的，也就是所有实际需要存储的数据都存储于树的叶子节点，二到任何一个叶子节点的最短路径的长度都是完全相同的。MySQL中的存储引擎也会稍作改造，比如Innodb存储引擎的B-Tree索引实际上使用的存储结构是B+Tree,在每个叶子节点存储了索引键相关信息之外，还存储了指向相邻的叶子节点的指针信息，这是为了加快检索多个相邻的叶子节点的效率。
 在Innodb中，存在两种形式的索引，一种是聚簇形式的主键索引，另外一种形式是和其他存储引擎（如MyISAM）存放形式基本相同的普通B-Tree索引，这种索引在Innodb存储引擎中被称作二级索引。

 图示中左边为 Clustered 形式存放的 Primary Key，右侧则为普通的 B-Tree 索引。两种索引在根节点和 分支节点方面都还是完全一样的。而 叶子节点就出现差异了。在主键索引中，叶子结点存放的是表的实际数据，不仅仅包括主键字段的数据，还包括其他字段的数据，整个数据以主键值有序的排列。而二级索引则和其他普通的 B-Tree 索引没有太大的差异，只是在叶子结点除了存放索引键的相关信息外，还存放了 Innodb 的主键值。
 所以，在 Innodb 中如果通过主键来访问数据效率是非常高的，而如果是通过二级索引来访问数据的话，Innodb 首先通过二级索引的相关信息，通过相应的索引键检索到叶子节点之后，需要再通过叶子节点中存放的主键值再通过主键索引来获取相应的数据行。
 MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空的键而已。而且 MyISAM 存储引擎的索引和 Innodb 的二级索引的存储结构也基本相同，主要的区别只是 MyISAM 存储引擎在叶子节点上面除了存放索引键信息之外，再存放能直接定位MyISAM 数据文件中相应的数据行的信息（如 Row Number），但并不会存放主键的键值信息。

3、索引的利弊

优点： 提高数据的检索速度，降低数据库的IO成本；
缺点：查询需要更新索引信息带来额外的资源消耗，索引还会占用额外的存储空间

4、如何判断是否需要建立索引

 上面说了索引的利弊，我们知道索引并不是越多越好，索引也会带来副作用。那么我们该怎么判断是否需要建立索引呢？
1、 较频繁的作为查询条件的字段应该创建索引；
2、更新频繁的字段不适合建立索引；
3、唯一性太差的不适合创建索引，如状态字段；
4、不出现在where中的字段不适合创建索引；

5、单索引还是组合索引？

일반적인 응용 시나리오에서 필터 필드 중 하나가 대부분의 시나리오에서 데이터의 90% 이상을 필터링할 수 있고 다른 필터 필드가 자주 업데이트되는 한, 나는 일반적으로 특히 동시성에서 결합된 인덱스를 생성하는 것을 선호합니다. 특히 볼륨이 높은 시나리오에서는 더욱 그렇습니다. 왜냐하면 동시성을 높이면 각 쿼리에 대한 IO 소모량을 조금만 절약하더라도 실행량이 매우 크기 때문에 절약되는 총 리소스 양은 여전히 ​​매우 크기 때문입니다.
하지만 결합 인덱스를 생성한다고 해서 쿼리 조건의 모든 필드가 하나의 인덱스에 배치되어야 한다는 의미는 아닙니다. 하나의 인덱스를 여러 쿼리에서 사용할 수 있도록 하고 인덱스 수를 최대한 줄여야 합니다. 비용 및 업데이트 저장.
MySQL은 인덱스 자체의 최적화를 줄이는 기능, 즉 "Prefix index"를 제공합니다. 즉, 필드의 이전 부분만 인덱스 키로 사용하여 필드를 인덱스할 수 있으므로 인덱스가 차지하는 공간이 줄어들고 인덱스의 액세스 효율성이 향상됩니다. 물론 접두사 인덱스는 접두사가 상대적으로 무작위이고 반복이 거의 없는 필드에만 적합합니다.

6. 인덱스 선택

1. 단일 키 인덱스의 경우 현재 쿼리에 가장 적합한 인덱스를 필터링해 보세요.
2. 결합된 인덱스를 선택할 때 현재 쿼리의 가장 좋은 필터링 필드가 인덱스에 있습니다. field 순서가 높을수록 좋습니다.
3. 결합 인덱스를 선택할 때 현재 쿼리의 where 절에 더 많은 필드를 포함할 수 있는 인덱스를 선택하세요.
4. 통계 정보를 분석하고 조정해 보세요. 쿼리를 작성하는 방식에 따라 수동 힌트를 통해 인덱스 제어의 선택을 줄이기 위해 적절한 인덱스를 선택하려면 나중에 유지 관리 비용이 매우 높을 것으로 예상됩니다.

7. MySQL 인덱스의 제한 사항

1. MyISAM 스토리지 엔진의 인덱스 키 길이의 합은 1000바이트를 초과할 수 없습니다.
2. BLOB 및 TEXT 유형 필드에 대해서는 접두사 인덱스만 생성할 수 있습니다. MySQL은 기능 인덱스를 지원하지 않습니다.
4. != 또는 를 사용할 경우 MySQL 인덱스를 사용할 수 없습니다.
5. 함수 작업에 필터 필드를 사용한 후에는 MySQL 인덱스를 사용할 수 없습니다. jion 문의 근거리 필드 유형이 일치하지 않는 경우 MySQL 인덱스를 사용할 수 없습니다.
7. 이전에 일치하도록 like를 사용하는 경우(예: '%aaa') MySQL 인덱스를 사용할 수 없습니다.
8. 사용 시; 동일하지 않은 쿼리, MySQL은 HASH 인덱스를 사용할 수 없습니다.
9. 문자 유형이 숫자인 경우 사용하려면 = '1'을 직접 사용할 수 없습니다.

8. 조인 원리 및 최적화

조인 원칙: MySQL에는 단 하나의 조인 알고리즘이 있는데, 이는 유명한 중첩 루프입니다. 실제로 구동 테이블의 결과 집합을 루프의 기본 데이터로 사용합니다. 그리고 결과 집합의 데이터를 필터 조건으로 사용하여 다음 테이블의 데이터를 하나씩 쿼리한 후 결과를 병합합니다. 아직 최근 참가자가 있는 경우 이전 최근 결과 세트가 주기의 기본 데이터로 사용되며 주기가 다시 순회됩니다.

Optimization: 1. Join 문의 총 루프 수를 최대한 줄입니다(앞에서 언급한 큰 결과 집합을 구동하는 작은 결과 집합을 기억하세요).
2. 내부 루프의 최적화 우선 순위를 지정합니다. Join 문이 구동 테이블의 Join 조건 필드가 인덱싱되었는지 확인하세요. 4. 구동 테이블의 Join 조건 필드가 인덱싱되었다는 보장이 없고 메모리 리소스가 충분할 경우 설정에 인색하지 마세요. 조인 버퍼(조인 버퍼는 All, index, range에서만 설정 가능)

9. ORDER BY 최적화

MySQL에는 두 가지 유형의 ORDER BY 구현만 있습니다.

1. 정렬된 인덱스를 통해 직접 데이터를 정렬하므로 클라이언트가 요구하는 정렬된 데이터를 얻기 위해 정렬 작업을 수행할 필요가 없습니다. 2. MySQL 정렬 알고리즘을 통해 스토리지 엔진에 반환된 데이터를 정렬한 다음 정렬된 데이터를 클라이언트.

인덱스 정렬을 사용하는 것이 가장 좋은 방법이지만 인덱스 린용이 없는 경우 MySQL은 주로 두 가지 알고리즘을 구현합니다.

1. 정렬에 사용되는 필터링 조건을 충족하는 필드와 행 데이터를 직접 찾을 수 있는 필드를 제거합니다. 행 포인터 정보, Sort Buffer에서 실제 정렬 작업을 수행한 후 정렬된 데이터를 사용하여 행 포인터 정보에 따라 테이블로 반환하여 클라이언트가 요청한 다른 필드의 데이터를 얻은 후 클라이언트에 반환합니다.

2. 필터 조건에 따라 정렬 필드와 클라이언트가 요청한 다른 모든 필드의 데이터를 한 번에 꺼내고 정렬이 필요하지 않은 필드를 메모리 영역에 저장한 다음 정렬을 수행합니다. Sort Buffer에 있는 필드와 행 포인터 정보를 정렬하고 최종적으로 정렬을 사용합니다. 결과 행 포인터는 다른 필드와 함께 메모리 영역에 저장된 행 포인터 정보와 일치하여 결과 집합을 병합한 후 순서대로 클라이언트에 반환됩니다.

첫 번째 알고리즘에 비해 두 번째 알고리즘은 주로 데이터에 대한 2차 접근을 줄입니다. 정렬 후에는 데이터를 검색하기 위해 테이블로 돌아갈 필요가 없으므로 IO 작업이 절약됩니다. 물론 두 번째 알고리즘은 더 많은 메모리를 소비하게 되는데, 이는 시간과 공간을 교환하는 일반적인 최적화 방법입니다.

 다중 테이블 Join 정렬의 경우 이전 Join 결과 세트를 먼저 임시 테이블을 통해 임시 테이블에 저장한 후 임시 테이블의 데이터를 Sort Buffer로 가져와서 작업합니다.

비인덱스 정렬의 경우 정렬을 위한 두 번째 알고리즘을 선택해 보세요.

1. max_length_for_sort_data 매개변수 설정을 늘립니다.
MySQL은 필드를 반환할 때 매개변수 max_length_for_sort_data에 의해 결정되는 알고리즘을 결정합니다. 최대 길이가 이 매개변수보다 작으면 MySQL은 두 번째 알고리즘을 선택하고 그 반대의 경우도 마찬가지입니다. 따라서 메모리가 충분하면 이 매개변수 값을 늘리면 MySQL이 두 번째 알고리즘을 선택할 수 있습니다.

2. 불필요한 반환 필드를 줄입니다.
위와 동일합니다. 필드 수가 적으면 max_length_for_sort_data 매개변수보다 작아집니다.

3. sort_buffer_size 매개변수 설정을 늘립니다.

sort_buffer_size를 늘리는 것은 MySQL이 향상된 정렬 알고리즘을 선택하도록 허용하는 것이 아니라 MySQL이 정렬 프로세스 중에 정렬해야 하는 데이터의 분할을 최소화하도록 허용하는 것입니다. , MySQL은 교환 정렬을 수행하기 위해 임시 테이블을 사용해야 합니다.

4.마침내

튜닝은 사실 어려운 일이고, 튜닝은 위의 쿼리 튜닝에만 국한되지 않습니다. 테이블 디자인 최적화, 데이터베이스 매개변수 튜닝, 애플리케이션 튜닝(주기적인 데이터베이스 작업 감소, 일괄 추가, 데이터베이스 연결 풀, 캐시) 등이 있습니다. 물론 실제 실습에서만 진가를 알 수 있는 튜닝 기술이 많이 있습니다. 이론과 사실을 바탕으로 끊임없이 자신을 향상시키려는 노력만이 진정한 튜닝 마스터가 될 수 있습니다.

관련 무료 학습 권장 사항: mysql 데이터베이스(동영상)

설명
실행 계획서에서 문의한 일련번호
쿼리 유형:	DEPENDENT UNION: 두 번째부터 시작하는 하위 쿼리의 UNION에 있는 모든 후속 SELECT SELECT SELECT는 외부 쿼리 결과 집합에 따라 달라집니다. PRIMARY: 기본 키 쿼리가 아닌 하위 쿼리의 가장 바깥쪽 쿼리입니다. SUBQUERY: 하위 쿼리의 내부 쿼리 중 첫 번째 SELECT이며 결과는 외부 쿼리에 종속되지 않습니다. UNCACHEABLE SUBQUERY: 결과 세트를 캐시할 수 없는 하위 쿼리 UNION: UNION 문의 두 번째 SELECT에서 시작하는 모든 SELECT, 첫 번째 SELECT는 PRIMARY입니다. UNION RESULT: UNION의 병합된 결과
액세스됨 데이터베이스의 테이블 이름
액세스 방법:	const: 상수, 최대 하나의 레코드만 일치합니다. 상수이므로 실제로는 읽기만 하면 됩니다. 한 번 eq_ref: 최대 1개만 일치합니다. 결과는 일반적으로 기본 키 또는 고유 인덱스로 액세스됩니다. 인덱스: 전체 인덱스 스캔 범위: 인덱스 범위 스캔 ref: jion 문 시스템에서 구동 테이블 인덱스의 참조 쿼리 : 시스템 테이블, 테이블에 데이터 행이 하나만 있습니다
사용 가능한 인덱스
사용된 인덱스
인덱스 길이
The 예상 결과 세트 레코드 수
추가 정보

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