500% CPU 시간에 MariaDB가 미쳤습니다.

Question 1

CPU 사용량이 높다는 것은 일반적으로 메모리 내 테이블 검색이 많다는 것을 의미합니다.

느린 쿼리 중 하나를 살펴보면 다음과 같습니다.

Time=5.64s (5s)  Rows_sent=0.0 (0)  Rows_examined=4088258.0 (4088258)
SELECT  udownload_id AS retval 
FROM hb_udownloads 
WHERE  user_id = 415064  
AND file_id = 78499  
LIMIT 1

쿼리가 전체 4M 행 테이블을 읽고 있지만 최대값을 반환합니다.하나그 중 (그리고 이 경우에는 전혀 없습니다).
당신은 필요인덱스이 쿼리를 지원하기 위해 이 테이블에 있습니다. 복합적인 것이 켜져 user_id있고 file_id좋은 시작이 될 것입니다.

또 다른 성능 킬러는 다음과 같습니다.

Time=20.58s (41s)  Rows_sent=4089261.5 (8178523)  Rows_examined=4089261.5 (8178523)
SELECT /*!40001 SQL_NO_CACHE */ * 
FROM `hb_udownloads`

"를 절대 사용하지 마세요.선택하다 *"를 프로덕션 코드에서 사용합니다.
언제나원하는 열을 명시적으로 지정하세요. 나는 이 테이블이 최근에 "성장"(열 획득)하고 있다고 추측합니다.
좋아, where 절이 없으면 어쨌든 테이블 스캔이 될 것이지만, 그러면 질문이 생깁니다. 불쌍한 클라이언트 [애플리케이션]이 무엇을 하게 될까요?하다이 쿼리가 "던지는" 4M 행이 있습니까?

Answer

CPU 사용량이 높다는 것은 일반적으로 메모리 내 테이블 검색이 많다는 것을 의미합니다.

느린 쿼리 중 하나를 살펴보면 다음과 같습니다.

Time=5.64s (5s)  Rows_sent=0.0 (0)  Rows_examined=4088258.0 (4088258)
SELECT  udownload_id AS retval 
FROM hb_udownloads 
WHERE  user_id = 415064  
AND file_id = 78499  
LIMIT 1

쿼리가 전체 4M 행 테이블을 읽고 있지만 최대값을 반환합니다.하나그 중 (그리고 이 경우에는 전혀 없습니다).
당신은 필요인덱스이 쿼리를 지원하기 위해 이 테이블에 있습니다. 복합적인 것이 켜져 user_id있고 file_id좋은 시작이 될 것입니다.

또 다른 성능 킬러는 다음과 같습니다.

Time=20.58s (41s)  Rows_sent=4089261.5 (8178523)  Rows_examined=4089261.5 (8178523)
SELECT /*!40001 SQL_NO_CACHE */ * 
FROM `hb_udownloads`

"를 절대 사용하지 마세요.선택하다 *"를 프로덕션 코드에서 사용합니다.
언제나원하는 열을 명시적으로 지정하세요. 나는 이 테이블이 최근에 "성장"(열 획득)하고 있다고 추측합니다.
좋아, where 절이 없으면 어쨌든 테이블 스캔이 될 것이지만, 그러면 질문이 생깁니다. 불쌍한 클라이언트 [애플리케이션]이 무엇을 하게 될까요?하다이 쿼리가 "던지는" 4M 행이 있습니까?

Question 2

이러한 쿼리 중 일부는 최적화가 필요한 것처럼 보입니다.

예를 들어:

Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), server***_fusion[server***_fusion]@localhost
  SELECT *,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=0) as selled,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=1) as used FROM tbl_keygroup
  order by `displayorder` asc

이 쿼리는 30000000개의 행을 검색합니다.

예를 들어 이 쿼리를 두 개의 쿼리로 변경할 수 있습니다.

SELECT * FROM tbl_keygroup ORDER by `displayorder` asc;
SELECT count(id) FROM tbl_swkey WHERE used IN (0,1) GROUP BY used;

쿼리 자체에는 쓸모없는 조인과 절반으로 줄어들 수 있는 두 개의 전체 테이블 스캔이 포함되어 있기 때문입니다.

지금까지 살펴본 내용의 대부분은 쿼리 최적화 문제를 가리키고 있으며 저는 느린 쿼리 로그를 보고 최소한의 테이블 스캔으로 해당 데이터를 검색하는 가장 좋은 방법이 무엇인지 판단할 것입니다.

또한 동일한 목적을 위해 많은 데이터를 색인화하고 싶을 수도 있습니다.

Answer

이러한 쿼리 중 일부는 최적화가 필요한 것처럼 보입니다.

예를 들어:

Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), server***_fusion[server***_fusion]@localhost
  SELECT *,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=0) as selled,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=1) as used FROM tbl_keygroup
  order by `displayorder` asc

이 쿼리는 30000000개의 행을 검색합니다.

예를 들어 이 쿼리를 두 개의 쿼리로 변경할 수 있습니다.

SELECT * FROM tbl_keygroup ORDER by `displayorder` asc;
SELECT count(id) FROM tbl_swkey WHERE used IN (0,1) GROUP BY used;

쿼리 자체에는 쓸모없는 조인과 절반으로 줄어들 수 있는 두 개의 전체 테이블 스캔이 포함되어 있기 때문입니다.

지금까지 살펴본 내용의 대부분은 쿼리 최적화 문제를 가리키고 있으며 저는 느린 쿼리 로그를 보고 최소한의 테이블 스캔으로 해당 데이터를 검색하는 가장 좋은 방법이 무엇인지 판단할 것입니다.

또한 동일한 목적을 위해 많은 데이터를 색인화하고 싶을 수도 있습니다.

Question 3

** 이렇게 하면 첫 번째 작업이 훨씬 빨라집니다.

SELECT  k.*, s.sold, s.used
    FROM ( SELECT group_id,
                  SUM(used = 0) AS sold,
                  SUM(used = 1) AS used
               FROM tbl_swkey
         ) AS s
    JOIN tbl_keygroup AS k  ON s.group_id = k.id
    ORDER BY displayorder

그리고

tbl_swkey:    INDEX(group_id, used)
tbl_keygroup: INDEX(displayorder)

이 쿼리에 대해 더 자세히 논의해야 하는 경우 SHOW CREATE TABLE테이블, 테이블 크기 및 EXPLAIN SELECT ....

** 이것

SELECT  udownload_id AS retval
    FROM  hb_udownloads
    WHERE  user_id = 415064
      AND  file_id = 78499
    LIMIT  1

훨씬 더 빠르려면 3열 복합 인덱스가 필요합니다.

INDEX(user_id, file_id, udownload_id)

어떤 일치 행을 얻게 되는지 신경쓰지 않으시나요? 아니면 을 추가해야 합니까 ORDER BY? 을 추가하면 ORDER BY내 색인 조언을 변경해야 할 수도 있습니다.

** 이 내용이 에서 나온 것인가요 mysqldump?

SELECT /*!40001 SQL_NO_CACHE */ * FROM `hb_udownloads`

침입성 덤프가 얼마나 마음에 들지 않으면 dba.stackexchange.com에서 해당 주제에 대한 여러 토론이 있습니다.

Answer