sql查询mysql cpu使用率

转载

mob64ca1415f0ab 2024-09-06 21:32:35

文章标签 sql查询mysql cpu使用率 mysql 算法索引数据库 文章分类 MySQL 数据库

1. 业务背景：

有一个对应用热度排行的需求，要求对应用的热度从高到低排序，取前10个最热的应用，涉及到的表有四张：

app_info应用信息表（数据量有50条）、app_classification应用分类表（数据量有4条）、app_source应用来源表（数据量有4条）、app_collection应用收藏表（数据量有26万）。上述四张表的表结构如下所示：

（1）app_info：

sql查询mysql cpu使用率_数据库

（2）app_classification:

sql查询mysql cpu使用率_mysql_02

（3）app_source:

sql查询mysql cpu使用率_索引_03

（4）app_collection:

sql查询mysql cpu使用率_算法_04

2、生产环境sql分析

（1）生产环境的sql

SELECT
      o.*,d.SourceId,d.SourceName,d.SourceCode
      ,a.id,a.module_code,a.module_name,
      CASE
      WHEN
      ( COUNT(*) ) IS NULL
      THEN 0
      ELSE ( COUNT(*) )
      END hotcount
      FROM
      app_info o
      LEFT JOIN
      app_source d
      ON o.source = d.SourceId
      LEFT
      JOIN
      app_classification a
      ON o.module_id = a.id
      LEFT JOIN
      app_collection c
      ON c.appid = o.appId
      WHERE o.status='2'
      GROUP
      BY o.appId ORDER BY COUNT(*) DESC limit 10;

（2）生产sql用时：5~6s

（3）对sql进行explain查看执行计划

sql查询mysql cpu使用率_算法_05

explain的结果分析：app_info、app_source、app_collection三张表都是全表扫描。app_info作为驱动表，Using temporary表示使用了temporary临时表用于group by分组，Using filesort 表示使用了sort buffer 用于排序。app_source 和 app_collection 中的Using join buffer(Block Nested Loop) ，说明表关联是没有用到索引，导致join时都用了BNL算法，在join buffer中做的运算都是M*N级别的。

3、对生产sql的优化分析

（1）分析：

首先，查看表关联字段有无索引。发现app_collection表的appid字段没有加索引，故先在该表的appid字段上加个索引。加索引后，再次执行原生产sql，执行时间为1~2s，虽然执行速度已经快了很多，但是执行速度依然不理想，对原生产sql进行explain，查看其执行计划。如下图：

sql查询mysql cpu使用率_数据库_06

再次查看执行计划，发现app_source和app_collection表都不走全表扫描了，并用上了索引，而且之前两个表的BNL算法，现在也都变为了Index Nested-Loop join算法。

（2）加索引后，依然慢的原因分析：

因为app_collection的表数据量为26万，虽然走了索引，并且关联使用了Index Nested-Loop join算法，但是执行速度依然不理想，那么我又想到，先把app_collection这个表变小一点。那么就使用子查询，将app_collection的数据量变的更小。执行

SELECT c.appid, count( * ) AS num FROM `application_collection` c GROUP BY c.`appid` ORDER BY null

语句后，数据量为50条左右。数据量大大变小。
（3）再次优化后的sql如下所示

SELECT
 o.*,
 d.SourceId,
 d.SourceName,
 d.SourceCode,
 a.id,
 a.module_code,
 a.module_name,
 e.num as hotcount
FROM
  app_info o
 LEFT JOIN
 ( SELECT c.appid, count( * ) AS num FROM `application_collection` c GROUP BY c.`appid` ORDER BY null) e
 on e.appid = o.appId
 LEFT JOIN app_source d ON o.source = d.SourceId
 LEFT JOIN app_classification a ON o.module_id = a.id
WHERE
 o.STATUS = '2' group by o.appId order by hotcount desc limit 10;

再次优化后的sql为100~200ms之间，已经达到了要求。

再使用explain查看执行计划：

sql查询mysql cpu使用率_算法_07