MySQL 8.0.26 三张带索引表统计结果查询慢

Question

我的内部管理站点上有一个统计页面，用于显示各个站点的一些流量信息。但是，即使在每个表的键上放置索引，查询也需要将近 80 秒才能运行。

我通常会在运行日期后的 7 天内运行此查询来搜索 session 状态。

SELECT
*, 
  (
    SELECT 
      COUNT(`session_id`) 
    FROM 
      `my-db`.`sessions` 
    WHERE 
      `my-db`.`sessions`.`site_id` = `my-db`.`sites`.`site_id` 
      AND `session_datetime` > '2021-10-17 00:00:00'
  ) as session_count, 
  (
    SELECT 
      `session_datetime` 
    FROM 
      `my-db`.`sessions` 
    WHERE 
      `my-db`.`sessions`.`site_id` = `my-db`.`sites`.`site_id` 
      AND `session_datetime` > '2021-10-17 00:00:00' 
    ORDER BY 
      `session_id` ASC 
    LIMIT 
      1
  ) as first_session, 
  (
    SELECT 
      `session_datetime` 
    FROM 
      `my-db`.`sessions` 
    WHERE 
      `my-db`.`sessions`.`site_id` = `my-db`.`sites`.`site_id` 
      AND `session_datetime` > '2021-10-17 00:00:00' 
    ORDER BY 
      `session_id` DESC 
    LIMIT 
      1
  ) as last_session, 
  (
    SELECT 
      COUNT(`site_profiles_id`) 
    FROM 
      `my-db`.`sites_profiles` 
    WHERE 
      `my-db`.`sites_profiles`.`site_id` = `my-db`.`sites`.`site_id` 
      AND `origin` = 1 
      AND `date_added` > '2021-10-17 00:00:00'
  ) as profiles_originated, 
  (
    SELECT 
      COUNT(`site_profiles_id`) 
    FROM 
      `my-db`.`sites_profiles` 
    WHERE 
      `my-db`.`sites_profiles`.`site_id` = `my-db`.`sites`.`site_id` 
      AND `scanned` = 1 
      AND `date_added` > '2021-10-17 00:00:00'
  ) as profiles_scanned, 
  (
    SELECT 
      COUNT(`site_profiles_id`) 
    FROM 
      `my-db`.`sites_profiles` 
    WHERE 
      `my-db`.`sites_profiles`.`site_id` = `my-db`.`sites`.`site_id` 
      AND `date_added` > '2021-10-17 00:00:00'
  ) as profiles_collected 
FROM 
  `my-db`.`sites` 
WHERE 
  `site_id` in (
    SELECT 
      DISTINCT(`site_id`) 
    FROM 
      `my-db`.`sessions` 
    WHERE 
      `session_datetime` > '2021-10-17 00:00:00'
  ) 
ORDER BY 
  `session_count` DESC 
LIMIT 
  25;

我试图理解 EXPLAIN 的结果，但我认为问题出在日期时间上使用的索引的 RANGE 类型。

值得注意的是，我正在根据管理员用户选择的排序下拉列表动态更改 ORDER BY 子句，以便按 site_id ASC/DESC、session_count ASC/DESC 和 profiles_collected ASC/DESC 对结果进行排序。

与其他相比，profiles_collected DESC 的性能受到显着影响。

网络站点

CREATE TABLE `sites` (
  `site_id` bigint NOT NULL AUTO_INCREMENT,
  `account_id` bigint NOT NULL,
  `site_hash` varchar(128) CHARACTER SET utf8 NOT NULL,
  `site_address` varchar(255) CHARACTER SET utf8 NOT NULL,
  `site_status` int NOT NULL,
  `site_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `site_updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`site_id`),
  UNIQUE KEY `site_id_UNIQUE` (`site_id`),
  UNIQUE KEY `site_hash_UNIQUE` (`site_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

network_profiles_sessions

CREATE TABLE `sessions` (
  `session_id` bigint NOT NULL AUTO_INCREMENT,
  `site_id` bigint NOT NULL,
  `profile_id` bigint DEFAULT NULL,
  `session_hash` varchar(128) CHARACTER SET utf8 NOT NULL,
  `session_ip_address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `session_useragent` text CHARACTER SET utf8,
  `session_page_uri` text CHARACTER SET utf8,
  `session_datetime` datetime DEFAULT CURRENT_TIMESTAMP,
  `session_has_data` tinyint DEFAULT '0',
  `session_processed` tinyint DEFAULT '0',
  `session_queued` tinyint DEFAULT '0',
  PRIMARY KEY (`session_id`),
  UNIQUE KEY `session_id_UNIQUE` (`session_id`),
  KEY `session_has_data` (`session_has_data`,`session_id`),
  KEY `session_processed` (`session_processed`,`session_id`),
  KEY `session_queued` (`session_queued`,`session_id`),
  KEY `session_datetime` (`session_datetime`,`session_id`),
  KEY `session_hash` (`session_hash`,`session_id`),
  KEY `site_id` (`site_id`,`session_id`),
  FULLTEXT KEY `session_page_uri` (`session_page_uri`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

network_sites_profiles

CREATE TABLE `sites_profiles` (
  `site_profiles_id` bigint NOT NULL AUTO_INCREMENT,
  `site_id` bigint NOT NULL,
  `profile_id` bigint NOT NULL,
  `origin` int DEFAULT NULL,
  `scanned` int DEFAULT NULL,
  `date_added` datetime DEFAULT CURRENT_TIMESTAMP,
  `date_lastseen` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`site_profiles_id`),
  UNIQUE KEY `site_users_id_UNIQUE` (`site_profiles_id`),
  KEY `site_id` (`site_id`,`site_profiles_id`),
  KEY `date_added` (`date_added` DESC,`site_profiles_id`),
  KEY `origin` (`origin`,`site_profiles_id`),
  KEY `scanned` (`scanned`,`site_profiles_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Answer 1

PRIMARY KEY(a)
UNIQUE KEY(a)  -- redundant, DROP it

PK 是一个 UNIQUE 键是一个 INDEX。

可以组合最后 3 个子查询:

SELECT  SUM(origin = 1)  AS profiles_originated,
        SUM(scanned = 1) AS profiles_scanned,
        COUNT(*)         AS profiles_collected
    FROM profiles
    WHERE date_added >= '2021-10-17'

然后 JOIN 到那个。但是，存在一些潜在的问题......

• 如何比较 session.datetime 和 date_added？我假设 session 是在它发生之前添加的？
• 我假设您想要包括 10 月 17 日上午的午夜？

前 3 个子查询也许可以类似地简化。请注意，MAX(session_datetime) 足以满足 last_session。