MariaDB CPU 时间高达 500%

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