MySQL 8.0.29索引未生效且查询性能低下问题求助

原查询语句 #

select  journey_level, 
        src_screen_name from_entity_name, 
        src_screen_id from_entity_id, 
        dest_screen_name to_entity_name, 
        dest_screen_id to_entity_id, 
        dest_screen_id to_entity_id_list, 
        concat(journey_level, '-', src_screen_id, '-', src_screen_name) from_entity, 
        concat(journey_level + 1, '-', dest_screen_id ,'-', dest_screen_name) to_entity, 
        count(1) cnt, 
        sum(ifnull(is_crash, 0)) crash_cnt, 
        sum(ifnull(is_anr, 0)) anr_cnt, 
        sum(ifnull(is_rage, 0) ) rage_cnt, 
        case dest_screen_id when -1 then 'Red' else '' end node_color 
from ue_summary.user_journey_screens_1943 s 
where first_session_created_at >= '2022-09-17 00:00:00' and first_session_created_at <='2022-10-17 10:42:35' 
and s.platform = '1' 
and journey_level <= '4' 
group by  journey_level, from_entity_id, from_entity_name, to_entity_id, to_entity_name;

初始执行计划 #

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: ALL
possible_keys: date_platform_level,platform_date,idx_platform
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 556941160
     filtered: 1.67
        Extra: Using where; Using temporary

表结构 #

CREATE TABLE `user_journey_screens_1943` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `app_id` int DEFAULT NULL,
  `platform` tinyint DEFAULT NULL,
  `asi` bigint DEFAULT NULL,
  `first_session_created_at` datetime DEFAULT NULL,
  `user_task_id` bigint DEFAULT NULL,
  `app_version_id` int DEFAULT NULL,
  `journey_level` int DEFAULT NULL,
  `src_screen_id` bigint DEFAULT NULL,
  `src_screen_name` varchar(300) DEFAULT NULL,
  `src_capture_time` bigint DEFAULT NULL,
  `src_capture_time_relative` bigint DEFAULT NULL,
  `dest_screen_id` bigint DEFAULT NULL,
  `dest_screen_name` varchar(300) DEFAULT NULL,
  `dest_capture_time` bigint DEFAULT NULL,
  `dest_capture_time_relative` bigint DEFAULT NULL,
  `is_crash` tinyint DEFAULT NULL,
  `is_anr` tinyint DEFAULT NULL,
  `is_rage` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `app_id_date_level` (`app_id`,`first_session_created_at`,`journey_level`),
  KEY `asi` (`asi`),
  KEY `date_platform_level` (`first_session_created_at`,`platform`,`journey_level`),
  KEY `platform_date` (`platform`,`first_session_created_at`),
  KEY `idx_platform` (`platform`,`first_session_created_at`,`journey_level`,`src_screen_id`,`src_screen_name`,`dest_screen_id`,`dest_screen_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2100241774 DEFAULT CHARSET=latin1

已尝试操作及结果 #

• 强制使用platform_date、date_platform_level索引
• 移除int类型字段的引号

但查询性能仍无明显改善，强制索引后的执行计划如下：

强制使用platform_date索引的执行计划 #

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: range
possible_keys: platform_date
          key: platform_date
      key_len: 8
          ref: NULL
         rows: 278684615
     filtered: 33.33
        Extra: Using index condition; Using where; Using temporary

强制使用date_platform_level索引的执行计划 #

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
   partitions: NULL
         type: range
possible_keys: date_platform_level
          key: date_platform_level
      key_len: 13
          ref: NULL
         rows: 278691741
     filtered: 33.33
        Extra: Using index condition; Using where; Using temporary

优化建议 #

1. 打造精准覆盖索引 #

当前idx_platform索引缺少聚合所需的is_crash、is_anr、is_rage字段，导致走索引仍需回表。创建包含所有查询字段的覆盖索引：

CREATE INDEX idx_optimized ON ue_summary.user_journey_screens_1943 
(platform, first_session_created_at, journey_level, 
 src_screen_id, src_screen_name, dest_screen_id, dest_screen_name,
 is_crash, is_anr, is_rage);

• 索引顺序逻辑：先放等值匹配的platform，再放范围查询的first_session_created_at，接着是范围过滤的journey_level，最后是分组、聚合需要的所有字段，确保MySQL无需回表就能获取全部数据。

2. 修正分组字段，避免别名歧义 #

GROUP BY中的from_entity_id、from_entity_name本质就是src_screen_id、src_screen_name，直接使用原字段分组，避免别名解析问题，同时符合MySQL 8.0的GROUP BY严格模式要求：

GROUP BY journey_level, src_screen_id, src_screen_name, dest_screen_id, dest_screen_name;

3. 更新表统计信息，让优化器做正确选择 #

如果表统计信息过时，优化器可能选错索引。执行以下命令更新统计信息：

ANALYZE TABLE ue_summary.user_journey_screens_1943;

更新后重新执行EXPLAIN，查看优化器是否自动选择最优索引。

4. 优化临时表配置，减少磁盘IO #

执行计划显示Using temporary，说明需要创建临时表处理分组。调整MySQL配置，让临时表优先在内存中创建：

tmp_table_size = 2G
max_heap_table_size = 2G

（根据服务器内存大小调整，比如16G内存服务器设置2G是合理值）

5. 考虑分区表优化 #

表数据量超过5亿行，可按first_session_created_at做按月分区，查询指定日期范围时仅扫描对应分区，大幅减少扫描数据量：

ALTER TABLE ue_summary.user_journey_screens_1943 
PARTITION BY RANGE (TO_DAYS(first_session_created_at)) (
    PARTITION p202209 VALUES LESS THAN (TO_DAYS('2022-10-01')),
    PARTITION p202210 VALUES LESS THAN (TO_DAYS('2022-11-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

6. 简化函数计算，统一写法 #

把ifnull替换为标准SQL的COALESCE，功能一致且兼容性更好；确保数值计算的字段类型正确，避免隐式转换：

sum(COALESCE(is_crash, 0)) crash_cnt, 
sum(COALESCE(is_anr, 0)) anr_cnt, 
sum(COALESCE(is_rage, 0)) rage_cnt

内容的提问来源于stack exchange，提问作者RRQ