온라인 mysql 옵티마이저의 잘못된 판단으로 인한 느린 쿼리 이벤트를 공유해 주세요.

이번 글은 온라인 mysql 옵티마이저의 잘못된 판단으로 인해 발생하는 느린 쿼리 이벤트에 대한 관련 정보와 최종 해결 방법을 주로 소개하고 있습니다.

머리말:

엄청나게 느린 쿼리와 요청 시간 초과 경보를 받았고, 메트릭을 통해 mysql 요청의 예외를 분석했습니다. cli —> show proceslist를 사용하여 느린 쿼리를 많이 보았습니다. 이 SQL은 이전에는 존재하지 않았으나 나중에 데이터 양의 증가로 인해 이러한 문제가 나타났습니다. 피드 테이블이 1억 개에 달하지만 피드 흐름 정보가 최근 핫하다는 특성을 갖고 있기 때문에 innodb_buffer_pool_size 비효율성으로 인해 잦은 IO가 발생하는 것은 아닙니다. 나중에 실행 계획 분석에 대해 자세히 설명한 후 mysql 쿼리 최적화 프로그램이 효율적이라고 생각되는 인덱스를 선택했습니다.

mysql 쿼리 최적화 프로그램은 대부분의 경우 신뢰할 수 있습니다. 그러나 SQL 언어에 여러 인덱스가 포함되어 있으면 최종 결과가 다소 주저되는 경우가 많습니다. mysql은 동일한 SQL에 대해 하나의 인덱스만 사용할 수 있으므로 어떤 인덱스를 선택해야 합니까? 데이터의 양이 적을 경우 MySQL 최적화 프로그램은 기본 키 인덱스를 게시하고 인덱스와 고유에 우선 순위를 부여합니다. 데이터 수준에 도달하면 쿼리 작업이 포함되었기 때문에 mysql 쿼리 최적화 프로그램은 기본 키를 사용할 가능성이 높습니다!

한 문장을 기억하세요. mysql 쿼리 최적화는 시간 비용 고려 사항이 아닌 검색 비용 고려 사항을 기반으로 합니다. 옵티마이저는 실제로 SQL을 실행하는 것이 아니라 기존 데이터 상태를 기준으로 비용을 계산합니다.

따라서 mysql 옵티마이저는 매번 최적화 결과를 얻을 수 없습니다. 비용을 정확하게 추정할 수 없으며, 각 지수를 실행하는 비용을 정확하게 얻으려면 실제로 한 번 실행해야 알 수 있으므로 비용 분석은 추정일 뿐이므로 오판이 발생합니다. .

여기서 이야기하는 테이블은 피드 정보 흐름 테이블이며, 피드 정보 흐름 테이블은 자주 액세스될 뿐만 아니라 많은 양의 데이터를 가지고 있다는 것을 알고 있습니다. 하지만 이 테이블의 데이터 구조는 매우 간단하고 인덱스도 간단합니다. 총 인덱스는 두 개뿐입니다. 하나는 기본 키 인덱스이고 다른 하나는 고유 키 인덱스입니다.

다음과 같이 캐시가 충분하고 여러 가지 이유로 데이터베이스 및 테이블 파티셔닝을 수행할 시간이 없기 때문에 이 테이블의 크기가 1억 레벨에 도달했습니다.

문제는 데이터 크기가 1억 미만일 때 mysql 옵티마이저가 인덱스 인덱스를 사용하도록 선택한다는 것입니다. 쿼리 최적화 프로그램은 기본 키 인덱스를 사용하도록 선택합니다. 이로 인해 발생하는 문제는 쿼리 속도가 너무 느리다는 것입니다.

정상적인 상황입니다.

mysql> explain SELECT * FROM `feed` WHERE user_id IN (116537309,116709093,116709377)     
AND cid IN (1001,1005,1054,1092,1093,1095)  
AND id <= 128384713 ORDER BY id DESC LIMIT 0, 11 \G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: feed
  partitions: NULL
     type: range
possible_keys: PRIMARY,feed_user_target
     key: feed_user_target
   key_len: 6
     ref: NULL
     rows: 18
   filtered: 50.00
    Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

동일한 SQL 문에 대해 데이터 양이 크게 변경된 후 mysql 쿼리 최적화 프로그램 Index 선택도 바뀌었습니다.

mysql> explain SELECT * FROM `feed` WHERE user_id IN (116537309,116709093,116709377)    
AND cid IN (1001,1005,1054,1092,1093,1095)    
AND id <= 128384713 ORDER BY id DESC LIMIT 0, 11 \G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: feed
     type: range
possible_keys: PRIMARY,feed_user_target
     key: PRIMARY
   key_len: 4
     ref: NULL
     rows: 11873197
    Extra: Using where
1 row in set (0.00 sec)

그런 다음 해결책은 강제 인덱스를 사용하여 쿼리 최적화 프로그램이 우리가 제공한 인덱스를 사용하도록 하는 것입니다. 이것은 Python 개발 환경입니다. 일반적인 Python ORM에는 강제 인덱스, 인덱스 무시 및 사용자 인덱스 매개변수가 있습니다.

explain  SELECT * FROM `feed` force index (feed_user_target) WHERE user_id IN (116537309,116709093,116709377) ...

그러면 이 문제를 어떻게 방지해야 할까요? 데이터 증가로 인해 mysql 옵티마이저가 비효율적인 인덱스를 선택하게 된 걸까요?

이 문제에 대해 여러 공장의 DBA에게 조언을 구했는데, 얻은 답변은 우리 방법과 같았습니다. 문제는 나중에 느린 쿼리를 통해서만 발견할 수 있으며, 그런 다음 SQL 문에 강제 인덱스를 지정하여 인덱스 문제를 해결합니다. 또한 이러한 종류의 문제는 온라인으로 전환되는 시스템의 초기 단계에서는 피할 수 있지만 초기 단계에서는 비즈니스 개발자가 DBA의 검토 작업에 협력하는 경우가 많지만 후반 단계에서는 문제를 방지하기 위해 또는 문제가 없다고 생각하면 MySQL 쿼리 사고가 발생합니다.