MySQL 데이터베이스 SQL 문 최적화

문제 SQL 판단

SQL에 문제가 있는지 여부를 두 가지 증상으로 판단할 수 있습니다.

• 시스템 수준 증상
  • CPU 소모가 심함
  • IO 대기가 심함
  • 페이지 응답 시간 만료됨 긴
  • 애플리케이션의 로그에 시간 초과 및 기타 오류

가 있는 경우 sar 명령과 top 명령을 사용하여 현재 로그를 볼 수 있습니다. 시스템 상태. sar命令，top命令查看当前系统状态。

也可以通过Prometheus、Grafana等监控工具观察系统状态。

• SQL语句表象
  • 冗长
  • 执行时间过长
  • 从全表扫描获取数据
  • 执行计划中的rows、cost很大

冗长的SQL都好理解，一段SQL太长阅读性肯定会差，而且出现问题的频率肯定会更高。更进一步判断SQL问题就得从执行计划入手，如下所示：

执行计划告诉我们本次查询走了全表扫描Type=ALL

Prometheus를 사용할 수도 있습니다 , Grafana와 같은 모니터링 도구는 시스템 상태를 관찰합니다.

SQL 문 표현
• 긴
  실행 시간이 너무 깁니다
  • 전체 테이블 스캔에서 데이터 가져오기
  • 실행 계획의 행과 비용이 매우 큽니다
• • 긴 SQL은 이해하기 쉽지만 SQL이 너무 길면 가독성이 떨어집니다. 확실히 가난하고 문제가 발생할 수 있습니다. 빈도는 확실히 더 높을 것입니다. SQL 문제를 더 자세히 파악하려면 아래와 같이 실행 계획부터 시작해야 합니다.
  • 실행 계획에 따르면 이 쿼리는 전체 테이블 스캔 Type=ALL을 거쳤으며 행이 매우 큽니다(9950400). 기본적으로 다음을 수행할 수 있습니다. 이것이 "맛있는" SQL이라고 판단하십시오.
  • SQL 문제 해결
• 데이터베이스마다 이를 얻는 방법이 다릅니다. 다음은 현재 주류 데이터베이스를 위한 느린 쿼리 SQL 획득 도구입니다.
  • MySQL
  • 느린 쿼리 로그
  • 테스트 도구 loadrunner
  • Percona 회사의 ptquery 및 기타 도구

OracleAWR 보고서

테스트 도구 loadrunner 등

v$sql, v$session_wait 등과 같은 관련 내부 뷰

GRID Control 모니터링 도구

Dameng 데이터베이스

AWR 보고서

테스트 도구 로드러너 등

Dameng 성능 모니터링 도구(dem)

v$sql, v$session_wait 등 관련 내부 뷰

SQL 작성 능력

있습니다. SQL 작성을 위한 몇 가지 일반적인 기술은 다음과 같습니다.

• 인덱스의 합리적인 사용

인덱스 수가 너무 적으면 쿼리 속도가 느려지고 인덱스가 너무 많아 공간을 많이 차지하며 추가를 실행할 때 인덱스를 동적으로 유지 관리해야 합니다. , 삭제 및 수정이 성능에 영향을 미칩니다.

선택률이 높고(중복 값이 ​​적음) 트리 인덱스 일반 조인 열을 색인화해야 하는 위치와 B가 자주 참조됩니다. 복잡한 문서 유형 쿼리가 더 많습니다. 전체 텍스트 인덱스를 사용하면 효율적입니다. 인덱스 설정은 쿼리와 DML 성능 간의 균형을 유지해야 합니다. 복합 인덱스를 생성할 때 선행 열이 아닌 쿼리에 주의하세요

• UNION

UNION 대신 UNION ALL을 사용하세요. ALL은 UNION보다 실행 효율성이 높습니다. UNION은 실행 시 중복 제거가 필요합니다. UNION은 데이터 정렬이 필요합니다. 쿼리는 테이블로 반환되어야 하며 포함 인덱스는 사용할 수 없습니다.

• JOIN 필드는 인덱싱하는 것이 좋습니다.

일반적으로 JOIN 필드는 미리 인덱싱됩니다.

• 복잡한 SQL 문 방지

가독성 향상, 여러 개의 짧은 쿼리로 변환될 수 있음 , 업무 종료 처리 사용🎜🎜🎜• 1=1 쓰기를 피하세요🎜🎜• rand()와 유사한 쓰기로 정렬하지 마세요🎜🎜🎜RAND()로 인해 데이터 열이 여러 번 스캔됩니다🎜🎜🎜🎜SQL 최적화 실행 계획🎜🎜🎜SQL 최적화를 완료하기 전에 실행 계획을 꼭 읽어보세요. 실행 계획을 보면 효율성이 낮은 부분과 최적화가 필요한 부분을 알려줍니다. 실행 계획이 무엇인지 알아보기 위해 MYSQL을 예로 들어 보겠습니다. (데이터베이스마다 실행계획이 다르기 때문에 직접 이해하셔야 합니다)🎜🎜🎜🎜

Field	Explanation
id	각각의 독립적으로 실행되는 작업 식별은 id 값이 클수록 먼저 실행되는 개체를 식별합니다. 실행 순서는 위에서 아래로 아래
select_type	쿼리의 각 select 절 유형
table	작업 중인 개체의 이름, 일반적으로 테이블 이름이지만 다른 이름도 있습니다. format
partitions	일치하는 파티션 정보(파티셔닝되지 않은 테이블의 경우 값은 NULL)
type	조인 작업 유형
possible_keys	사용 가능한 인덱스
키	옵티마이저가 실제로 사용하는 인덱스(가장 중요한 열 ) 연결 유형 중 가장 좋은 것부터 나쁜 것까지 const, eq_reg, ref, 범위, 색인 및 모두. ALL가 나타나면 현재 SQL에 "악취"가 있다는 의미 const、eq_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了“坏味道”
key_len	被优化器选定的索引键长度，单位是字节
ref	表示本行被操作对象的参照对象，无参照对象为NULL
rows	查询执行所扫描的元组个数（对于innodb，此值为估计值）
filtered	条件表上数据被过滤的元组个数百分比
extra	执行计划的重要补充信息，当此列出现Using MySQL 데이터베이스 SQL 문 최적화sort , Using temporary 字样时就要小心了，很可能SQL语句需要优化

接下来我们用一段实际优化案例来说明SQL优化的过程及优化技巧。

优化案例

• 表结构

• CREATE TABLE `a` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_id` bigint(20) DEFAULT NULL, `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `b` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_name` varchar(100) DEFAULT NULL, `user_id` varchar(50) DEFAULT NULL, `user_name` varchar(100) DEFAULT NULL, `sales` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `c` ( `id` int(11) NOT NULLAUTO_INCREMENT, `user_id` varchar(50) DEFAULT NULL, `order_id` varchar(100) DEFAULT NULL, `state` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) );

• 三张表关联，查询当前用户在当前时间前后10个小时的订单情况，并根据订单创建时间升序排列，具体SQL如下

• select a.seller_id,          a.seller_name,          b.user_name,          c.state   from a,        b,        c   where a.seller_name = b.seller_name     and b.user_id = c.user_id     and c.user_id = 17     and a.gmt_create       BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)       AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)   order by a.gmt_create

• 查看数据量

  

• 原执行时间

  

• 原执行计划

  

• 初步优化思路

• SQL中 where条件字段类型要跟表结构一致，表中user_id 为varchar(50)类型，实际SQL用的int类型，存在隐式转换，也未添加索引。将b和c表user_id 字段改成int类型。

• 因存在b表和c表关联，将b和c表user_id创建索引

• 因存在a表和b表关联，将a和b表seller_name

• key_len
  옵티마이저가 선택한 인덱스 키의 길이, 단위는 바이트

• 
  ref

는 이것을 연산 중인 행의 참조 객체입니다. 비참조 객체는 NULL

• rows

  쿼리 실행으로 스캔된 튜플 수(innodb의 경우 이 값은 추정치입니다)

• filtered
  조건 테이블의 데이터 필터링된 튜플의 비율

  

extra

• 실행 계획의 중요한 보충 정보입니다. Using MySQL 데이터베이스 SQL 문 최적화sort, Using temporary라는 단어를 사용할 때는 주의하세요. code>가 이 열에 나타납니다. 예, SQL 문을 최적화해야 할 가능성이 매우 높습니다

  다음으로 실제 최적화 사례를 사용하여 SQL 최적화 프로세스와 최적화 기술을 설명합니다.

• 최적화 사례

  
  

• 테이블 구조

• alter table b modify `user_id` int(10) DEFAULT NULL;   alter table c modify `user_id` int(10) DEFAULT NULL;   alter table c add index `idx_user_id`(`user_id`);   alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);   alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

현재 사용자의 주문 상태를 현재 시간 10시간 전후로 쿼리하고, 오름차순으로 정렬하는 세 개의 테이블이 관련되어 있습니다. 구체적인 주문 생성 시간은 다음과 같습니다

alter table a modify "gmt_create" datetime DEFAULT NULL

• 데이터량 보기

원래 실행 시간

1. 원래 실행 계획
2. 
4. 초기 최적화 아이디어
6. SQL의 where 조건 필드 유형은 테이블 구조와 일치해야 합니다. 테이블의 user_id는 varchar(50) 유형입니다. SQL에 사용되는 실제 int 유형에는 암시적 변환이 있으며 인덱스가 추가되지 않습니다. 테이블 b와 c의 user_id 필드를 int 유형으로 변경합니다.

테이블 b와 테이블 c가 연관되어 있으므로 테이블 b와 테이블 c user_id에 인덱스를 생성합니다.

테이블 a와 테이블 b가 연관되어 있으므로 테이블 a와 테이블 b >seller_name 필드 생성 인덱스🎜🎜🎜🎜복합 인덱스를 사용하여 임시 테이블 및 정렬 제거🎜🎜🎜🎜SQL의 초기 최적화🎜🎜🎜🎜rrreee🎜🎜🎜최적화 후 실행 시간 보기🎜🎜🎜🎜🎜🎜 🎜 최적화 계획 후 실행 보기🎜🎜🎜🎜🎜🎜🎜경고 정보 보기🎜🎜🎜🎜🎜🎜🎜계속 최적화🎜🎜🎜🎜rrreee🎜🎜🎜실행 시간 보기🎜🎜🎜🎜 🎜 🎜🎜🎜🎜실행 계획 보기🎜🎜 🎜🎜🎜🎜🎜 🎜최적화 요약🎜🎜🎜🎜실행 계획 보기 설명🎜🎜경고 정보가 있으면 알람 정보를 확인하여 경고 표시🎜🎜SQL에 관련된 테이블 구조 및 인덱스 정보 보기🎜🎜최적화 가능성 생각해보기 실행 계획에 따른 포인트🎜🎜가능한 최적화 시점에서 테이블 구조 변경, 인덱스 추가, SQL 재작성 등의 작업을 수행🎜🎜최적화된 실행 시간 및 실행 계획 보기🎜🎜🎜최적화 효과가 확실하지 않은 경우 네번째 단계🎜🎜"🎜mysql 동영상 튜토리얼🎜" 추천🎜

위 내용은 MySQL 데이터베이스 SQL 문 최적화의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!