MySQL大表关联慢查询优化求助：索引配置后仍运行超时

问题背景 #

涉及两张超大表：bizevent（约3400万行）、bizevent_actions（约1700万行），使用Amazon RDS MySQL 5.7.33（16GB内存、4vCPU）。已为查询及排序字段配置索引，但查询耗时仍过长，执行Explain Extended未找到有效优化方向，需针对性优化建议。

查询语句 #

select
   bizevent0_.id as id1_37_,
   bizevent0_.json as json2_37_,
   bizevent0_.account_id as account_3_37_,
   bizevent0_.createdBy as createdB4_37_,
   bizevent0_.createdOn as createdO5_37_,
   bizevent0_.description as descript6_37_,
   bizevent0_.iconCss as iconCss7_37_,
   bizevent0_.modifiedBy as modified8_37_,
   bizevent0_.modifiedOn as modified9_37_,
   bizevent0_.name as name10_37_,
   bizevent0_.version as version11_37_,
   bizevent0_.fired as fired12_37_,
   bizevent0_.preCreateFired as preCrea13_37_,
   bizevent0_.entityRefClazz as entityR14_37_,
   bizevent0_.entityRefIdAsStr as entityR15_37_,
   bizevent0_.entityRefIdType as entityR16_37_,
   bizevent0_.entityRefName as entityR17_37_,
   bizevent0_.entityRefType as entityR18_37_,
   bizevent0_.entityRefVersion as entityR19_37_
from
   BizEvent bizevent0_
inner join BizEvent_actions actions1_ on
   bizevent0_.id = actions1_.BizEvent_id
where
   bizevent0_.createdOn >= '1969-12-31 19:00:01.0'
   and actions1_.action <> 'SoftLock'
   and (
       actions1_.targetRefClazz = 'com.biznuvo.core.orm.domain.org.EmployeeGroup'
       and actions1_.targetRefIdAsStr = '1'
       or
       actions1_.objectRefClazz = 'com.biznuvo.core.orm.domain.org.EmployeeGroup'
       and actions1_.objectRefIdAsStr = '1'
   )
order by
    bizevent0_.createdOn;

注：原语句的LEFT OUTER JOIN因WHERE子句过滤右表字段，实际等价于INNER JOIN，已修正以帮助优化器选择更优执行计划。

表定义 #

bizevent表 #

CREATE TABLE `bizevent` (
   `id` bigint(20) NOT NULL,
   `json` longtext,
   `account_id` int(11) DEFAULT NULL,
   `createdBy` varchar(50) NOT NULL,
   `createdon` datetime(3) DEFAULT NULL,
   `description` varchar(255) DEFAULT NULL,
   `iconCss` varchar(50) DEFAULT NULL,
   `modifiedBy` varchar(50) NOT NULL,
   `modifiedon` datetime(3) DEFAULT NULL,
   `name` varchar(255) NOT NULL,
   `version` int(11) NOT NULL,
   `fired` bit(1) NOT NULL,
   `preCreateFired` bit(1) NOT NULL,
   `entityRefClazz` varchar(255) DEFAULT NULL,
   `entityRefIdAsStr` varchar(255) DEFAULT NULL,
   `entityRefIdType` varchar(25) DEFAULT NULL,
   `entityRefName` varchar(255) DEFAULT NULL,
   `entityRefType` varchar(50) DEFAULT NULL,
   `entityRefVersion` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
    KEY `IDXk9kxuuprilygwfwddr67xt1pw` (`createdon`),
    KEY `IDXsf3ufmeg5t9ok7qkypppuey7y` (`entityRefIdAsStr`),
    KEY `IDX5bxv4g72wxmjqshb770lvjcto` (`entityRefClazz`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

bizevent_actions表 #

CREATE TABLE `bizevent_actions` (
    `BizEvent_id` bigint(20) NOT NULL,
    `action` varchar(255) DEFAULT NULL,
    `objectBizType` varchar(255) DEFAULT NULL,
    `objectName` varchar(255) DEFAULT NULL,
    `objectRefClazz` varchar(255) DEFAULT NULL,
    `objectRefIdAsStr` varchar(255) DEFAULT NULL,
    `objectRefIdType` int(11) DEFAULT NULL,
    `objectRefVersion` int(11) DEFAULT NULL,
    `targetBizType` varchar(255) DEFAULT NULL,
    `targetName` varchar(255) DEFAULT NULL,
    `targetRefClazz` varchar(255) DEFAULT NULL,
    `targetRefIdAsStr` varchar(255) DEFAULT NULL,
    `targetRefIdType` int(11) DEFAULT NULL,
    `targetRefVersion` int(11) DEFAULT NULL,
    `embedJson` longtext,
    `actions_ORDER` int(11) NOT NULL,
   PRIMARY KEY (`BizEvent_id`,`actions_ORDER`),
     KEY `IDXa21hhagjogn3lar1bn5obl48gll` (`action`),
     KEY `IDX7agsatk8u8qvtj37vhotja0ce77` (`targetRefClazz`),
     KEY `IDXa7tktl678kqu3tk8mmkt1mo8lbo` (`targetRefIdAsStr`),
     KEY `IDXa22eevu7m820jeb2uekkt42pqeu` (`objectRefClazz`),
     KEY `IDXa33ba772tpkl9ig8ptkfhk18ig6` (`objectRefIdAsStr`),
   CONSTRAINT `FKr9qjs61id11n48tdn1cdp3wot` FOREIGN KEY (`BizEvent_id`) REFERENCES `bizevent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

优化建议 #

1. 重构WHERE条件并优化索引 #

原WHERE条件的逻辑可合并提取公共部分，同时为bizevent_actions创建复合覆盖索引，避免回表查询：

• 创建两个针对性复合索引：

  -- 针对target分支的索引
  CREATE INDEX idx_actions_target ON bizevent_actions (action, targetRefClazz, targetRefIdAsStr, BizEvent_id);
  -- 针对object分支的索引
  CREATE INDEX idx_actions_object ON bizevent_actions (action, objectRefClazz, objectRefIdAsStr, BizEvent_id);
  

  这两个索引直接覆盖WHERE条件的过滤字段，同时包含关联用的BizEvent_id，无需回表读取全表数据。

2. 调整JOIN执行顺序 #

由于bizevent_actions的过滤条件更具针对性（限定了特定的refClazz和refId），数据量远小于全表，可强制优化器优先扫描bizevent_actions，再关联bizevent。可通过STRAIGHT_JOIN指定顺序：

select ...
from BizEvent_actions actions1_
straight_join BizEvent bizevent0_ on bizevent0_.id = actions1_.BizEvent_id
where ...
order by bizevent0_.createdOn;

3. 优化bizevent表的排序效率 #

当前bizevent的createdon已有单列索引，但如果查询返回数据量较大，排序仍可能耗时。若业务允许，可考虑：

• 若createdon与id递增趋势一致，可改为按id排序（主键是聚簇索引，排序成本更低）；
• 若必须按createdon排序，确保innodb_buffer_pool_size足够大（建议设置为10-12GB，16GB内存的RDS），让createdon索引能完全加载到内存。

4. 数据类型优化 #

• targetRefIdAsStr和objectRefIdAsStr当前为varchar(255)，但实际值为数字字符串（如'1'），可改为int(11)，减少索引存储体积，提升查询匹配速度；
• 若action字段的可选值有限，可改为enum类型，进一步缩小索引大小。

5. RDS参数调优 #

• innodb_buffer_pool_size：设置为实例内存的70%-80%（16GB内存建议设为12GB），让更多数据和索引缓存到内存；
• innodb_flush_log_at_trx_commit：若业务无需强一致性，设为2，提升写入性能（对查询也有间接帮助）；
• sort_buffer_size：适当调大（如设置为2M），提升排序效率，避免磁盘临时表。

6. 分批处理结果 #

若查询返回大量数据，避免一次性读取全量结果，采用分页（LIMIT offset, size）或游标分批获取，降低内存占用和传输耗时。

内容的提问来源于stack exchange，提问作者Joseph Robert Kolagani