MariaDB 在 500% cpu 時間下瘋狂

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