유용한 정보를 공유해보세요! MySQL 느린 쿼리에 대한 실제 분석 요약

MySQL의 느린 쿼리(전체 이름은 느린 쿼리 로그)는 MySQL에서 제공하는 로그 레코드로, MySQL에서 응답 시간이 임계값을 초과하는 명령문을 기록하는 데 사용됩니다. 정적인 내용을 소개할 예정이며, 필요하시면 참고하시면 됩니다.

一 왜 이렇게 해야 할까요?

1 느린 SQL이란?

이것은 MySQL의 느린 쿼리, 특히 실행 시간이 long_query_time 값을 초과하는 SQL을 의미합니다.

일반적인 MySQL 바이너리 로그에는 binlog, 릴레이 로그 Relaylog, 다시 실행 롤백 로그 redolog, undolog 등이 포함된다는 말을 자주 듣습니다. 느린 쿼리의 경우, 응답 시간이 MySQL의 임계값을 초과하는 명령문을 기록하는 데 사용되는 느린 쿼리 로그인 Slowlog도 있습니다.

느린 쿼리 로그는 select 문만 기록한다고 생각하지 마세요. 실제로 실행 시간이 long_query_time에 설정된 임계값을 초과하는 삽입, 업데이트 및 기타 DML 문도 기록됩니다.

# 查看慢SQL是否开启
show variables like "slow_query_log%";

# 查看慢查询设定的阈值 单位:秒
show variables like "long_query_time";

우리가 사용하는 AliSQL-X-Cluster인 XDB의 경우 기본적으로 느린 쿼리가 활성화되어 있으며 long_query_time은 1초로 설정되어 있습니다.

2 느린 쿼리로 인해 오류가 발생하는 이유는 무엇입니까?

실제로 느린 SQL은 다수의 행 스캔, 임시 파일 정렬 또는 빈번한 디스크 플러시를 동반하는 경우가 많습니다. 직접적인 영향은 디스크 IO가 증가하고 일반 SQL도 느린 SQL이 되며 대규모 실행 시간이 초과된다는 것입니다.

작년 Double 11 이후 기술적인 측면에서 노출된 문제에 대응하여 Cainiao CTO 라인은 CTO-D가 각각 후원자로 하나씩 여러 특별 거버넌스 프로젝트를 시작했고, 저의 대규모 팀이 느린 SQL 거버넌스.

2 어느 정도까지 달성해야 합니까?

1 애플리케이션에서 느린 SQL의 심각도를 측정하는 방법은 무엇입니까?

미시 평균

sum(aone应用慢SQL执行次数)
-----------------------
sum(aone应用SQL执行次数)

우리는 값이 클수록 영향이 크다고 믿습니다. 값이 작을수록 영향은 작을 수 있습니다.

극단적인 경우는 애플리케이션에서 실행되는 모든 SQL이 모두 느린 SQL이고 값이 1입니다. 애플리케이션에서 실행되는 모든 SQL이 느린 SQL이 아니며 값이 0입니다.

그러나 이 지표가 가져오는 문제는 판별력이 좋지 않다는 것입니다. 특히 SQL QPS가 매우 높고 SQL이 대부분의 경우 느린 쿼리가 아닌 상황에서는 가끔 느린 SQL이 압도될 수 있습니다.

또 다른 질문입니다. 가끔 느린 SQL이 정말 느린 SQL인가요? 느린 쿼리 로그에 기록되는 많은 SQL이 실제로 다른 느린 SQL, MySQL 디스크 지터, 최적화 프로그램 선택 및 기타 이유로 영향을 받을 수 있으므로 일반 쿼리의 성능은 분명히 느린 SQL이 아닙니다. 하지만 SQL이 느려집니다.

매크로 평균

sum(慢SQL 1执行次数)    sum(慢SQL n执行次数)
-----------------  +  ------------------
sum(SQL 1执行次数)      sum(SQL n执行次数)
---------------------------------------
                   n

이 알고리즘은 포착된 느린 SQL에 특정 실행 횟수가 있다는 사실을 기반으로 하며, 이는 잘못된 느린 SQL의 영향을 줄일 수 있습니다.

일부 애플리케이션의 QPS가 매우 낮은 경우, 즉 하루에 SQL 실행 횟수가 매우 적은 경우, 잘못된 SQL이 발견되면 통계 오류가 발생합니다.

Execution times

sum(aone应用慢SQL执行次数)
-----------------------
           7

지난 주 동안 하루에 느린 SQL 실행의 평균 횟수를 계산합니다. 이를 통해 매크로 평균화로 인한 잘못된 SQL 문제를 제거할 수 있습니다.

느린 SQL 템플릿 수

위의 차원에는 모두 시간 제한이 있습니다. 느린 SQL의 처리 기록을 추적하기 위해 전역 느린 SQL 템플릿 수량 차원도 도입했습니다.

count(distinct(aone应用慢SQL模板) )

2 목표

• 핵심 응용 프로그램: 느린 SQL 모두 해결

• 일반 응용 프로그램: 미시 평균 지표가 50% 감소

3 CTO 보고서

CTO로서 - D는 위의 다차원지표 통계요약 어플리케이션의 가중평균을 기준으로 낮은 순위부터 높은 순위까지 ​​순위를 매겨 상위 3위까지 강조하여 매주 방송하는 단위입니다.

세 가지 왜 해야 할까요

이 추측은 제 배경과 관련이 있을 수 있습니다. 저는 이전 회사에서 회사 수준의 원격 다중 활성 아키텍처의 설계 및 구현을 담당했습니다. 그리고 저는 MySQL에 대해 많이 알고 있습니다.

또한, 관심으로 인한 것이 아닐 수도 있습니다. 저희 소규모 팀의 비즈니스는 이제 막 시작했고, 느린 SQL이 없기 때문에 다양한 비즈니스 라인에 삽입될 수 있습니다.

4가지 액션 지원

1 그룹 MySQL 프로토콜

인덱스 프로토콜 발췌:

[필수] 3개 이상의 테이블에 대해서는 조인이 금지됩니다. 조인해야 하는 필드의 데이터 유형은 절대적으로 일관되어야 합니다. 다중 테이블 상관 관계를 쿼리할 때 상관 관계가 있는 필드에 인덱스가 있어야 합니다.

참고: 더블 테이블을 조인할 때도 테이블 인덱스와 SQL 성능에 주의해야 합니다.

[필수] varchar 필드에 인덱스를 생성할 때 인덱스 길이를 지정해야 합니다. 인덱스 길이는 실제 텍스트 구분에 따라 결정됩니다.

참고: 인덱스의 길이와 구별은 모순된 쌍입니다. 일반적으로 문자열 유형 데이터의 경우 길이가 20인 인덱스의 경우 구별이 90% 이상 높아집니다. count(distinct)를 사용할 수 있습니다. left(열명, 인덱스 길이) )/count(*).

[필수] 페이지 검색 시 왼쪽 흐림 또는 전체 흐림 사용을 엄격히 금지합니다. 필요한 경우 검색 엔진을 사용하여 문제를 해결하세요.

참고: 인덱스 파일에는 B-Tree의 가장 왼쪽 접두사 일치 기능이 있습니다. 왼쪽의 값이 결정되지 않으면 이 인덱스를 사용할 수 없습니다.

【권장】다양한 필드 유형으로 인한 암시적 변환을 방지하여 인덱스 오류가 발생합니다.

[참고] 인덱스 생성 시 다음과 같은 극단적인 오해를 피하세요:

1) 인덱스가 부족한 것보다는 너무 많은 것이 낫다

认为一个查询就需要建一个索引。

2） 吝啬索引的创建

认为索引会消耗空间、严重拖慢更新和新增速度。

3） 抵制唯一索引

认为唯一索引一律需要在应用层通过“先查后插”方式解决。

2  DB变更标准

DDL需要控制变更速度，注意灰度和并发控制，变更发布需要在规定的变更发布窗口内。

五  分享一些我参与优化的例子

1  数据分布不均匀

1）分库分表不合理

该业务数据分了8个库，每个库分了16张表，通过查看表空间可以看到数据几乎都分布在各个库的某2张表中。分库分表的策略有问题，另外过高预估了业务增量，这个持保留意见。

2）索引不合理

单表创建了idx_logistics_corp_id_special_id的联合索引，但即便这样区分度依然太低，根据实验及业务反馈(logistics_corp_id,transport_type_id)字段组合区分度非常高，且业务存在transport_type_id的单查场景。

2  索引问题

SELECT
  COUNT(0) AS `tmp_count`
FROM(
    SELECT
      `table_holder`.`user_id`,
      `table_holder`.`sc_item_id`,
      SUM(
        CASE
          `table_holder`.`inventory_type`
          WHEN 1 THEN `table_holder`.`quantity`
          ELSE 0
        END
      ) AS `saleable_quantity`,
      SUM(
        CASE
          `table_holder`.`inventory_type`
          WHEN 1 THEN `table_holder`.`lock_quantity`
          ELSE 0
        END
      ) AS `saleable_lock_quantity`,
      SUM(
        CASE
          `table_holder`.`inventory_type`
          WHEN 401 THEN `table_holder`.`quantity`
          ELSE 0
        END
      ) AS `transfer_on_way_quantity`,
      `table_holder`.`store_code`,
      MAX(`table_holder`.`gmt_modified`) AS `gmt_modified`
    FROM
      `table_holder`
    WHERE(`table_holder`.`is_deleted` = 0)
      AND(`table_holder`.`quantity` > 0)
      AND `table_holder`.`user_id` IN(3405569954)
      AND `table_holder`.`store_code` IN('ZJJHBHYTJJ0001', '...1000多个')
    GROUP BY
      `table_holder`.`user_id`,
      `table_holder`.`sc_item_id`
    ORDER BY
      `table_holder`.`user_id` ASC,
      `table_holder`.`sc_item_id` ASC
  ) `a`;

这个case对应的表有store_code索引，因此认为没问题，没办法优化了。实则通过执行计划，我们发现MySQL选择了全表扫描。针对该case实践发现，当范围查询的个数超过200个时，索引优化器将不再使用该字段索引。

最终经过拉取最近一段时间的相关查询SQL，结合业务的数据分布，我们发现采用(is_deleted,quantity)即可解决。

判断执行计划采用的索引长度：key_len的长度计算公式(>=5.6.4)

char(10)允许NULL      =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 1(NULL)
char(10)不允许NULL    =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1)
varchr(10)允许NULL    =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 1(NULL) + 2(变长字段)
varchr(10)不允许NULL  =  10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 2(变长字段)
int允许NULL           =  4 + 1(NULL)
int不允许NULL         =  4
timestamp允许NULL     =  4 + 1(NULL)
timestamp不允许NULL   =  4
datatime允许NULL      =  5 + 1(NULL)
datatime不允许NULL    =  5

3  被人影响

用到了索引却依然被爆出扫描2千万行：

索引字段区分度很高：

同时期常规SQL变为了慢查询：

DB数据盘访问情况：

排查共用物理机其他实例的情况，发现有个库在问题时间附近有很多慢sql需要排序，写临时文件刚好写入了2GB：

多个MySQL实例leader节点混合部署在同一台物理机，虽然通过docker隔离了CPU、MEM等资源，但目前还没有做到buffer io的隔离。

4  无法解决

通过汇总分析高频的查询并结合业务得出合适的索引往往能够解决日常遇到的慢查询，但这并不是万能的。

比如有可能索引越加越多，乃至成了这样：

有些场景，比如支持多个字段组合查询，又没有必填项，如果都要通过索引来支持显然是不合理的。

查询场景下，将区分度较高的字段设定为必填项是个好习惯；查询组合很多的情况下考虑走搜索支持性更好的存储或者搜索引擎。

六  日常化处理

随着各个CTO-D线的深入治理，各项指标较之前均有非常大的改观，比如核心应用完成慢查询清零，影响最大的一些慢SQL被得以解决，而我所在的团队排名也由最初的尾部top3进入到头部top3。
慢SQL治理进入日常化，通过每周固定推送慢SQL工单、owner接手处理、结单，基本形成了定期清零的习惯和氛围，慢SQL治理专项也被多次点名表扬。

7개 요약

뒤늦게 요약한 내용입니다. 지금 돌이켜보면 전략 수립, 문제 분석, 해결의 과정은 모두와 공유할 가치가 충분히 있다고 생각합니다.

관련 추천: "mysql 튜토리얼"

위 내용은 유용한 정보를 공유해보세요! MySQL 느린 쿼리에 대한 실제 분석 요약의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!