mysql查询需要优化

Question

以下查询大约需要 14 秒才能完成。我有一个包含 1M 个条目的人员表。谁能建议我如何使查询更快并减少执行时间(例如 1、2 或 3 秒)？我在下面附上详细说明。

SELECT p.id, 
COUNT(CASE WHEN p.active=1 THEN 1 END) AS active_users_count, 
COUNT(CASE WHEN DATE_FORMAT(p.created_date,'%Y-%m-%d') =     DATE_FORMAT(NOW(),'%Y-%m-%d') THEN 1 END) AS today_install_count,  
COUNT(CASE WHEN DATE_FORMAT(p.created_date,'%Y-%m-%d') =    DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY), '%Y-%m-%d') THEN 1 END) AS     yesterday_install_count,  
COUNT(CASE WHEN DATE_FORMAT(p.created_date,'%Y-%m-%d') BETWEEN    DATE_SUB(CURDATE(),INTERVAL DAY(LAST_DAY(NOW())) DAY) AND CURDATE() THEN 1 END)         AS month_install_count,  
COUNT(CASE WHEN DATE_FORMAT(p.created_date,'%Y-%m-%d') BETWEEN     DATE_SUB(CURDATE(),INTERVAL 1 YEAR) AND CURDATE() THEN 1 END) AS     year_install_count, 
COUNT(CASE WHEN DATE_FORMAT(p.created_date,'%Y-%m-%d') BETWEEN     DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE() THEN 1 END) AS     week_install_count, 
COUNT('x') AS total_users_count
FROM person p 
WHERE p.app_id IN (SELECT p2.id FROM project p2 ) GROUP BY p.app_id

返回 239 行

执行时间:13.504秒传输时间:0.001秒总时间:13.505 秒

shw 为人员和项目创建表

person  CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `device_push_token` longtext NOT NULL,
  `created_date` datetime NOT NULL,
  `since_last_login` datetime NOT NULL,
  `platform` smallint(6) NOT NULL,
  `hwid` varchar(255) NOT NULL,
  `app_id` bigint(20) NOT NULL,
  `since_last_push` datetime NOT NULL,
  `no_of_pushes` smallint(6) NOT NULL DEFAULT '0',
  `language` varchar(50) DEFAULT NULL,
  `timezone` bigint(20) DEFAULT '0',
  `since_last_hour_push` datetime DEFAULT NULL,
  `version` bigint(20) NOT NULL DEFAULT '1',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `hwid` (`hwid`,`app_id`),
  KEY `fk_person_platform` (`platform`),
  KEY `fk_person_project` (`app_id`),
  CONSTRAINT `fk_person_platform` FOREIGN KEY (`platform`) REFERENCES `platform` (`id`),
  CONSTRAINT `fk_person_project` FOREIGN KEY (`app_id`) REFERENCES `project` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1310384 DEFAULT CHARSET=latin1


project CREATE TABLE `project` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(300) NOT NULL,
  `name` longtext NOT NULL,
  `description` longtext,
  `ios_configure` bigint(20) DEFAULT NULL,
  `android_configure` bigint(20) DEFAULT NULL,
  `freq_push` bigint(20) DEFAULT NULL,
  `hour_push` bigint(20) DEFAULT NULL,
  `push_sent` bigint(20) DEFAULT '0',
  `push_opened` bigint(20) DEFAULT '0',
  `version` bigint(20) NOT NULL DEFAULT '1',
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `project_apprater` bigint(20) DEFAULT NULL,
  `type` smallint(6) NOT NULL DEFAULT '1',
  `status` bigint(20) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_id` (`unique_id`),
  KEY `fk_project_ios_config` (`ios_configure`),
  KEY `fk_project_android_config` (`android_configure`),
  KEY `fk_project_freq_push` (`freq_push`),
  KEY `fk_project_hour_push` (`hour_push`),
  KEY `fk_project_apprater` (`project_apprater`),
  KEY `fk_project_platform` (`type`),
  KEY `name` (`status`),
  CONSTRAINT `fk_project_android_config` FOREIGN KEY (`android_configure`) REFERENCES `project_configure_android` (`id`),
  CONSTRAINT `fk_project_apprater` FOREIGN KEY (`project_apprater`) REFERENCES `project_apprater` (`id`),
  CONSTRAINT `fk_project_freq_push` FOREIGN KEY (`freq_push`) REFERENCES `freq_push` (`id`),
  CONSTRAINT `fk_project_hour_push` FOREIGN KEY (`hour_push`) REFERENCES `hour_push` (`id`),
  CONSTRAINT `fk_project_ios_config` FOREIGN KEY (`ios_configure`) REFERENCES `project_configure_ios` (`id`),
  CONSTRAINT `fk_project_platform` FOREIGN KEY (`type`) REFERENCES `platform` (`id`),
  CONSTRAINT `name` FOREIGN KEY (`status`) REFERENCES `project_status` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=313 DEFAULT CHARSET=latin1



id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY p   index   \N  fk_person_project   8   \N  1158770 Using where
2   DEPENDENT SUBQUERY  p2  unique_subquery PRIMARY PRIMARY 8   func    1   Using index

更新了完整查询

SELECT 
  p3.id AS id,
  COALESCE(pug.active_users_count, 0) AS userCount, 
  p3.unique_id AS uniqueId,
  p3.name,
  p3.description,
  DATE_FORMAT(p3.created_date, '%m-%d-%Y %T') AS createdDate,
  p3.android_configure AS androidConfigure,
  p3.ios_configure AS iosConfigure,
  (SELECT 
    fp.active 
  FROM
    freq_push fp 
  WHERE fp.id = p3.freq_push) AS freqActive,
  (SELECT 
    hp.active 
  FROM
    hour_push hp 
  WHERE hp.id = p3.hour_push) AS hourActive,
  COALESCE(pug.total_users_count, 0) AS totalUserCount,
  COALESCE(pug.today_install_count, 0) AS todayInstallCount,
  COALESCE(pug.yesterday_install_count, 0) AS yesterdayInstallCount,
  COALESCE(pug.month_install_count, 0) AS monthInstallCount,
  COALESCE(pug.year_install_count, 0) AS yearInstallCount,
  COALESCE(pug.week_install_count, 0) AS weekInstallCount,
  (SELECT 
    plat.name 
  FROM
    platform plat 
  WHERE plat.id = p3.type) AS project_type ,
  ps.name
FROM 
  (SELECT 
    p.app_id,
    COUNT(
      CASE
        WHEN p.active = 1 
        THEN 1 
      END) AS active_users_count,
    COUNT(
      CASE
        WHEN DATE(p.created_date) = CURDATE() 
        THEN 1 
      END
    ) AS today_install_count,
    COUNT(
      CASE
        WHEN DATE(p.created_date) = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) 
        THEN 1 
      END
    ) AS yesterday_install_count,
    COUNT(
      CASE
        WHEN DATE(p.created_date) BETWEEN DATE_SUB(
          CURDATE(),
          INTERVAL DAY(LAST_DAY(NOW())) DAY
        ) 
        AND CURDATE() 
        THEN 1 
      END
    ) AS month_install_count,
    COUNT(
      CASE
        WHEN DATE(p.created_date) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) 
        AND CURDATE() 
        THEN 1 
      END
    ) AS year_install_count,
    COUNT(
      CASE
        WHEN DATE(p.created_date) BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
        AND CURDATE() 
        THEN 1 
      END
    ) AS week_install_count,
    COUNT('x') AS total_users_count 
  FROM
    person p 
    INNER JOIN project p2 
      ON p.app_id = p2.id 
  GROUP BY p.app_id) AS pug 
  RIGHT JOIN project p3 
    ON p3.id = pug.app_id     
  INNER JOIN project_status ps  
  ON p3.status = ps.id
ORDER BY userCount DESC,
  createdDate DESC

Answer 1

根据之前的版本修改此答案。基本上Mark B就在 comments受到质疑。幸运的是，OP 已经取得了进展，时间已经从 13 秒减少到略低于 6 秒。OP 说(在他自己的答案下的评论和聊天中)如果时间可以减少到 1 秒以下，他会考虑其他方法。就像我和他谈论的关于接受有些过时的指标，他可以选择过时的持续时间。用户的陈旧性和速度之间的权衡。

这是一种解决方法。

一个使用 Create Event创建一个事件，该事件在他选择的每个 nnn(时间段)Interval 内自动触发。该事件更新了最终用户访问的表。事件本身根据他的答案运行他的查询，您将看到嵌入在下面的事件中。

架构更改

create table appIdMetrics
(   -- this is the table Users hit against
    appId int not null primary key,
    active_users_count int not null,
    today_install_count int not null,
    yesterday_install_count int not null,
    month_install_count int not null,
    year_install_count int not null,
    week_install_count int not null,
    total_users_count int not null
);

create table evt_appIdMetrics
(   -- this is the worktable that only the Event uses
    -- while it puts together the refreshed data
    -- perhaps once every 5 minutes
    appId int not null primary key,
    active_users_count int not null,
    today_install_count int not null,
    yesterday_install_count int not null,
    month_install_count int not null,
    year_install_count int not null,
    week_install_count int not null,
    total_users_count int not null
);

事件创建

drop event updateAppIdMetrics;
DELIMITER $$
CREATE EVENT updateAppIdMetrics
    ON SCHEDULE
        EVERY 5 MINUTE

DO BEGIN
    truncate table evt_appIdMetrics;    -- this is the table that only the evt has access to

    -- time to refresh this table (approx 6 seconds)
    -- 280 rows (count as per OP comments)
    insert into evt_appIdMetrics
    (appId,active_users_count,today_install_count,yesterday_install_count,
    month_install_count,year_install_count,week_install_count,total_users_count)
    select p.app_id, 
    COUNT(CASE WHEN p.active=1 THEN 1 END) AS active_users_count, 
    COUNT(CASE WHEN DATE(p.created_date)= CURDATE() THEN 1 END) AS today_install_count,  
    COUNT(CASE WHEN DATE(p.created_date) = DATE(DATE_SUB(NOW(),INTERVAL 1 DAY)) THEN 1 END) AS     yesterday_install_count,  
    COUNT(CASE WHEN DATE(p.created_date) BETWEEN DATE_SUB(CURDATE(),INTERVAL DAY(LAST_DAY(NOW())) DAY) AND CURDATE() THEN 1 END)         AS month_install_count,  
    COUNT(CASE WHEN DATE(p.created_date) BETWEEN     DATE_SUB(CURDATE(),INTERVAL 1 YEAR) AND CURDATE() THEN 1 END) AS     year_install_count, 
    COUNT(CASE WHEN DATE(p.created_date) BETWEEN     DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE() THEN 1 END) AS     week_install_count, 
    COUNT('x') AS total_users_count
    FROM person p 
    INNER JOIN project p2 ON p.app_id = p2.id 
    GROUP BY p.app_id;

    -- BEGIN LOCK (important)
    -- figure out a locking scheme (work-in-progress, not completed yet)
    truncate table appIdMetrics;    -- this is the table users access

    -- the following should take a split second on the approximately 280 rows (count as per OP comments)
    insert into appIdMetrics
    (appId,active_users_count,today_install_count,yesterday_install_count,
    month_install_count,year_install_count,week_install_count,total_users_count)
    select appId,active_users_count,today_install_count,yesterday_install_count,
    month_install_count,year_install_count,week_install_count,total_users_count
    from evt_appIdMetrics;
    -- complete locking schema (work-in-progress, not completed yet)
    -- END LOCK (important)
END;$$
DELIMITER ;
-- evt creation succeeded by passing Syntax Error check

用户与表appIdMetrics进行交互。当我有机会时，我会调整提到的锁定方案。用户的用户体验应该是瞬间的。数据刷新间隔可通过 OP 针对过时因素进行调整。根据我的经验，该事件将在第一个时间段间隔之后第一次触发。所以这意味着 5 分钟。

稍后我将提供一个事件管理的链接。 编辑:here这是。必须启用事件。