MySQL 최적화 이슈 15개 선정 및 요약-MySQL 튜토리얼-php.cn

이 글에서는 개발 과정에서 SQL 문 문제를 해결하는 방법, 프로덕션 환경에서 SQL 문제를 해결하는 방법 등 SQL 최적화와 관련된 문제를 주로 소개하는 mysql에 대한 관련 지식을 제공합니다. 함께하면 모두에게 도움이 되기를 바랍니다.

MySQL 최적화 이슈 15개 선정 및 요약

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

개발 과정에서 SQL 문제를 해결하는 방법은 무엇입니까?

문제 해결 아이디어

대부분의 프로그래머에게 개발 과정에서 SQL 문제 해결은 기본적으로 공백입니다. 그러나 업계의 혁신으로 인해 개발 프로세스에 대한 관심과 전문성이 점점 더 높아지고 있습니다. 그 중 하나는 생산 과정에서 SQL 문제가 노출되지 않도록 개발 과정에서 SQL 문제를 최대한 해결하는 것입니다. 그렇다면 개발 과정에서 프로그램의 SQL 문제 해결을 편리하게 수행하려면 어떻게 해야 할까요?

이 아이디어는 여전히 Mysql의 느린 로그를 사용하여 다음을 달성하는 것입니다.

우선, 개발 프로세스 중에 데이터베이스 Mysql의 느린 쿼리도 활성화해야 합니다
```
SET GLOBAL slow_query_log='on';
```
로그인 후 복사
로그인 후 복사
두 번째로, 최소 시간을 설정합니다. 느린 SQL

참고: 여기서 시간 단위는 s초이지만 소수점 6자리이므로 미묘한 시간 강도를 표현할 수 있습니다. 일반적으로 단일 테이블의 SQL 실행 시간은 20ms 이내입니다. 개발 과정에서 실행하는 SQL 문이 20ms를 초과한다면 이에 주의해야 한다는 것입니다.
```
SET GLOBAL long_query_time=0.02;
```
로그인 후 복사
로그인 후 복사
작업의 편의를 위해 Slow SQL을 파일이 아닌 테이블에 기록할 수 있습니다
```
SET GLOBAL log_output='TABLE';
```
로그인 후 복사
마지막으로 기록된 Slow SQL을 mysql.slow_log 테이블을 통해 쿼리할 수 있습니다

도구 사용

용 형제가 귀하를 위해 개발한 소프트웨어에서는 한 번의 클릭으로 위의 기능을 빠르게 구현할 수 있도록 그래픽 인터페이스도 제공합니다.

프로덕션 환경에서 SQL 문제를 해결하는 방법은 무엇입니까?

문제 해결 아이디어

생성된 SQL 문제를 해결하는 것은 좀 더 복잡하지만 전반적인 아이디어는 느린 SQL을 통해 문제를 해결하는 것입니다. 구체적인 아이디어는 다음과 같습니다.

먼저 Mysql 데이터베이스의 느린 쿼리를 활성화합니다
```
SET GLOBAL slow_query_log='on';
```
로그인 후 복사
로그인 후 복사
두 번째 설정 느린 SQL의 최소 시간
```
SET GLOBAL long_query_time=0.02;
```
로그인 후 복사
로그인 후 복사
일반적으로 느린 SQL을 생성할 때 파일에 넣습니다
```
SET GLOBAL log_output='FILE';
```
로그인 후 복사
느린 SQL 로그 파일을 로컬에 다운로드
마지막으로 느린 쿼리를 닫습니다. 데이터베이스 Mysql

주의 사항: 프로덕션 중에 느린 SQL을 열고 사용 후에는 로그가 비즈니스 성능에 영향을 미치지 않도록 닫는 것이 가장 좋습니다
```
SET GLOBAL slow_query_log='off';
```
로그인 후 복사

SQL을 조정하는 방법은 무엇입니까?

SQL 튜닝은 지식의 여러 측면을 통합합니다. 일반적으로 테이블 구조와 테이블 인덱스라는 두 가지 측면에서 최적화하는 것이 일반적입니다.

테이블 구조 최적화

1. 필드 클래스 및 길이의 합리적인 사용

이해해야 할 예: 성별 필드의 경우tinyint(1) 저장소는 1바이트를 차지하고 int(1) 저장소는 4바이트를 차지합니다. 레코드가 100만 개라면 int에 저장된 테이블의 파일 크기는tinyint에 저장된 테이블의 파일 크기보다 약 2.8M 더 큽니다. 따라서 int 유형에 저장된 테이블을 읽을 때 파일이 더 커지고 읽기 속도가 느려집니다. Tinyint를 읽는 것보다. 이것이 실제로 를 합리적으로 사용해야 하는 이유의 핵심입니다. 이는 읽기 성능 을 제공하기 위해 저장된 파일의 크기를 줄이는 것입니다.

물론 일부 친구들은 2.8M이 전반적인 상황에 영향을 미치지 않으므로 무시해도 된다고 말할 수도 있습니다. 용 형제는 이 아이디어에 뭔가를 추가하고 싶습니다. 테이블에 10개의 필드가 있고 시스템에 총 30개의 테이블이 있다고 가정해 보겠습니다. 그런 다음 추가 파일 크기를 살펴보겠습니다. (2.8Mx10x30=840M, Thunder Super를 사용하여 840M을 다운로드하는 데 몇 초가 걸립니다. 이 시간은 컴퓨터에서 매우 느립니다...)

2. 중복 디자인의 합리적인 사용

2.1.

Mysql 내부에는 MySQL에 의해 자동으로 생성되고 삭제되는 특별하고 가벼운 임시 테이블이 있습니다. 임시 테이블은 주로 SQL 실행 중에 특정 작업의 중간 결과를 저장하는 데 사용됩니다. 이 프로세스는 MySQL에 의해 자동으로 완료되며 사용자가 수동으로 개입할 수 없으며 이 내부 테이블은 사용자에게 보이지 않습니다.

내부 임시 테이블은 SQL 문의 최적화 프로세스에서 매우 중요합니다. MySQL의 많은 작업은 최적화 작업을 위해 내부 임시 테이블을 사용합니다. 하지만 내부 임시 테이블을 사용하려면 테이블을 생성하고 중간 데이터에 접근하는 비용이 필요하므로 SQL 문을 작성할 때는 임시 테이블 사용을 피해야 합니다. 그렇다면 MySQL은 내부적으로 임시 테이블을 사용하게 될까요?

다중 테이블 관련 쿼리(JOIN)에서 order by 또는 group by에서 사용된 컬럼이 첫 번째 테이블의 컬럼이 아닙니다.
group by의 컬럼이 테이블의 컬럼이 아닌 경우 인덱스 열
Distinct와 group by를 함께 사용합니다
order by 문은 Unique 키워드를 사용합니다.
group by 열은 인덱스 열이지만 데이터의 양이 너무 많은 경우

2.2. 내부 임시 테이블 사용 여부를 확인하세요.

Explain 키워드나 도구의 함수 버튼을 사용하면 SQL의 실행 과정을 볼 수 있습니다. 결과의 Extra 열에 Using temporary 키워드가 나타나면 SQL 문이 실행될 때 임시 테이블을 사용한다는 의미입니다.

아래 그림과 같이 역할 테이블과 역할 그룹 Role_Group은 다대일 관계를 갖습니다. 관련 쿼리를 수행할 때 임시 테이블은 role_group의 ID를 사용하여 정렬하는 데 사용됩니다(아래 그림 1 참조). 역할의 ID가 정렬에 사용되는 경우 임시 테이블은 사용되지 않습니다(그림 2 참조).

2.3. 내부 임시 테이블을 사용하지 않는 문제를 해결하는 방법은 무엇입니까?

이 문제에는 두 가지 해결 방법이 있습니다. 하나는 임시 테이블을 사용하지 않도록 SQL 문을 조정하는 것이고, 다른 하나는 테이블에 중복 저장하는 것입니다. 예를 들어 2.2의 그림 1의 예에서 role_group의 id로 정렬해야 한다면 role 테이블에서는 group_id로 정렬하면 되는데, 이 열은 중복되는 role_group 테이블의 id 열 값이다. 저장되었습니다.

3. 서브 데이터베이스와 서브 테이블의 합리적인 활용

서브 데이터베이스와 서브 테이블은 대량 최적화에 활용될 뿐만 아니라, 수직 서브 테이블도 SQL 튜닝에 활용 가능합니다. (여기서는 세로 및 가로 하위 테이블에 대해 설명하지 않습니다. 관심이 있으시면 개인 메시지를 보내주세요.)

예: 기사 테이블의 일반적인 디자인에는 기사 콘텐츠의 넓은 필드가 포함되지 않습니다.

기사 내용의 큰 필드가 별도의 테이블에 배치됩니다.

기사 테이블이 필드를 하나의 테이블로 병합하는 대신 위 디자인을 채택하는 이유는 무엇입니까?

먼저 수학 문제를 계산해 보겠습니다. 기사 크기가 1M이고 기사 내용이 824KB이고 나머지 필드가 200KB라고 가정하면 다음과 같은 총 1백만 개의 기사가 있습니다.

옵션 1, Storage를 사용하는 경우 이 테이블의 크기는 100W*1M=100WM
Option 2, Vertical Table Storage를 사용하는 경우 기본 테이블은 200KBx100W, 컨텐츠 테이블은 824KBx100W

We입니다. 프런트 엔드 페이지에 각각 두 개의 기사 목록과 기사 세부 정보가 있어 데이터베이스에서 직접 관련 콘텐츠를 쿼리한 다음:

계획 1, 기사 목록과 기사 세부 정보는 100WM 데이터에서 쿼리됩니다.
계획 2, 기사 목록은 200KBx100W에서 쿼리되고, 기사 세부 정보는 824KBx100W에서 쿼리됩니다. (현재는 200KBx100W에서도 쿼리해야 할 수도 있습니다.)

이렇게 말하면 모든 사람이 마음속에 명확한 답을 가지고 있어야 한다고 믿습니다! 수직 테이블 분할을 사용하면 다양한 비즈니스 시나리오에서 다양한 양의 데이터를 쿼리할 수 있습니다. 종종 이 데이터 양은 전체 테이블 데이터 양보다 작기 때문에 고정된 크거나 작은 양에서 쿼리하는 것보다 더 유연하고 효율적입니다.

테이블 인덱스 최적화

1. 인덱스 열을 합리적으로 추가하세요

인덱스에 대한 대부분의 사람들의 이해는 "인덱스가 쿼리 속도를 높일 수 있습니다"라고 생각하는데, 용 형제는 이 문장의 후반부를 추가해야 합니다 "인덱스는 속도를 높일 수 있습니다" 쿼리를 실행하거나 데이터 삽입 또는 수정 속도를 늦추세요”.

테이블에 인덱스가 5개 있으면 간단히 인덱스를 테이블로 간주하면 테이블 1개 + 인덱스 테이블 6개 = 테이블 6개에 해당하는데, 이 6개 테이블은 언제 작동할 수 있나요? 계산해 봅시다:

삽입 작업, 데이터가 삽입된 후 5개의 인덱스 테이블에 인덱스 데이터를 삽입해야 합니다
삭제 작업, 데이터가 삭제된 후 5개의 인덱스 테이블에 있는 인덱스를 삭제해야 합니다
update 작업
- 인덱스 열의 데이터가 수정되면 먼저 데이터를 수정하고, 인덱스 테이블의 인덱스도 수정해야 합니다.
- 인덱스 열의 데이터가 수정되지 않은 경우 , 데이터 테이블만 수정해야 합니다
select 연산
- 쿼리 인덱스가 맞으면 인덱스를 먼저 쿼리한 후 데이터 테이블을 확인하세요
- 쿼리 인덱스가 맞지 않으면, 데이터 테이블을 직접 확인해보세요

위의 계산을 통해 기적적으로 인덱스가 많을수록 삽입, 삭제, 업데이트 작업에 영향을 미치고 부정적인 영향을 미친다는 사실을 발견했습니다. . 따라서 무작위로 추가하는 대신 인덱스의 영향이 쿼리의 이점보다 적다고 평가한 다음 추가하는 것이 가능합니다.

2、合理的调配复合索引列个数和顺序

复合索引指的是包括有多个列的索引，它能有效的减少表的索引个数，平衡了多个字段需要多个索引直接的性能平衡，但是再使用复合索引的时候，需要注意索引列个数和顺序的问题。

先说列个数的问题，指的是一个复合索引中包括的列字段太多影响性能的问题，主要是对update操作的性能影响，如下红字：

如果修改了索引列的数据，则先修改数据，还需要修改索引表中的索引，如果索引列个数越多则修改该索引的概率越大
如果没有修改索引列的数据，则只修改数据表

再说复合索引中列顺序的问题，是指索引的最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，这个比较容易理解，就不多做阐述。

那些情况索引会失效？

索引无法存储null值，当使用is null或is not nulli时会全表扫描
like查询以"%"开头
对于复合索引，查询条件中没有给出索引中第一列的值时
mysql内部评估全表扫描比索引快时
or、!=、<>、in、not in等查询也可能引起索引失效

表设计有那些规范？

建表规约

表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型为 unsigned tinyint。说明：任何字段如果为非负数，则必须是 unsigned。
字段允许适当冗余，以提高查询性能，但必须考虑数据一致。e.g. 商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，

避免关联查询

。冗余字段遵循：
- 不是频繁修改的字段；
- 不是 varchar 超长字段，更不能是 text 字段。

索引规约

在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
页面搜索严禁左模糊或者全模糊，如果需要请通过搜索引擎来解决。说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
- 正例：where a=? and b=? order by c; 索引: a_b_c。
- 反例：索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
利用延迟关联或者子查询优化超多分页场景。说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 的行，返回 N 行。当 offset 特别大的时候，效率会非常的低下，要么控制返回的总页数，要么对超过阈值的页数进行 SQL 改写。
建组合索引的时候，区分度最高的在最左边。
SQL 性能优化的目标，至少要达到 range 级别，要求是 ref 级别，最好是 consts。

SQL 语句

不要使用 count(列名) 或 count(常量) 来替代 count()，count() 是 SQL92 定义的标准统计行数的语句，跟数据库无关，跟 NULL 和非 NULL 无关。说明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。
count(distinct column) 计算该列除 NULL 外的不重复行数。注意，count(distinct column1,column2) 如果其中一列全为 NULL，那么即使另一列用不同的值，也返回为 0。
当某一列的值全为 NULL 时，count(column) 的返回结果为 0，但 sum(column) 的返回结果为 NULL，因此使用 sum() 时需注意 NPE 问题。可以使用如下方式来避免 sum 的 NPE 问题。

SELECT IF(ISNULL(SUM(g), 0, SUM(g))) FROM table;

로그인 후 복사

使用 ISNULL() 来判断是否为 NULL 值。说明：NULL 与任何值的直接比较都为 NULL。
외래 키와 캐스케이드는 허용되지 않습니다. 모든 외래 키 개념은 애플리케이션 계층에서 해결되어야 합니다. 설명: 학생과 성적 간의 관계를 예로 들어 보겠습니다. 학생 테이블의 Student_id는 기본 키이고 성적 테이블의 Student_id는 외래 키입니다. 학생 테이블의 학생_ID가 업데이트되고 성적 테이블의 학생_ID가 동시에 업데이트되는 경우 이는 계단식 업데이트입니다. 외래 키 및 계단식 업데이트는 단일 시스템의 낮은 동시성에는 적합하지만 분산 및 동시성 클러스터에는 적합하지 않습니다. 계단식 업데이트는 데이터베이스 업데이트 폭풍의 위험이 있어 데이터베이스 삽입 속도에 영향을 미칩니다. .
저장 프로시저 사용은 금지됩니다. 저장 프로시저는 디버그 및 확장이 어렵고 이식성이 없습니다.
in 작업은 가능하면 피해야 합니다. 피할 수 없다면 이후의 수집 요소 수를 신중하게 평가하여 1000개 이내로 제어해야 합니다. in 操作能避免则避免。若实在避免不了，需要仔细评估 in 后面的集合元素数量，控制在 1000 个之内。