大型InnoDB表table_abc性能优化及慢查询问题咨询

表基本信息 #

• 表名：table_abc
• 总记录数：236,364,206条
• 年度记录分布：

年份    记录数
2009    1
2011    4
2012    8
2013    25
2014    70
2015    175
2016    393
2017    1010
2018    332086
2019    7605438
2020    32268473
2021    73318801
2022    100285147
2023    22554330

• 索引信息：包含多个单列及联合索引（原索引详情图已移除）

问题咨询 #

1. 如何在生产服务器上优化该大型InnoDB表？
2. 通过performance_schema.table_io_waits_summary_by_index_usage发现存在count_star = 0的未使用索引，但无法直接删除，担心月/年度任务会用到这些索引，该如何处理？
3. 以下查询执行耗时约7分钟，如何优化？

EXPLAIN SELECT o.*  FROM table_abc o 
WHERE o.cId = '3' AND (o.created <= '2023-04-27 11:45:39' OR o.created IS NULL) AND booking IS NOT NULL AND MOD((o.accId - '19'), 21) = 0 AND o.exported IS NULL 

执行计划：（原执行计划图已移除）

已有思路及疑问 #

1. 考虑归档历史数据、按月/年分区，但对业务不熟悉，不确定可行性；
2. 已通过performance_schema分别在DB运行1天和35天后统计未使用索引，覆盖了月度任务，但年度任务需等待或排查代码，耗时且有遗漏风险，删除未使用索引前需做哪些预检查？
3. 尝试限制时间范围后，返回行数减少但执行时间无明显变化：

EXPLAIN SELECT o.*  FROM table_abc o 
WHERE o.cId = '3' AND ((o.created >= '2022-04-27 11:45:39' AND o.created <= '2023-04-27 11:45:39') OR o.created IS NULL) AND booking IS NOT NULL AND MOD((o.accId - '19'), 21) = 0 AND o.exported IS NULL 

执行计划：（原执行计划图已移除）

表结构 #

CREATE TABLE `table_abc` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `externalNIId` varchar(40) DEFAULT NULL COMMENT '外部NI编号',
  `cId` int(11) unsigned NOT NULL COMMENT '客户ID',
  `accId` int(11) unsigned NOT NULL COMMENT '账户ID',
  `scheId` int(111) DEFAULT NULL COMMENT '计划ID',
  `archiveId` int(11) unsigned DEFAULT NULL COMMENT '归档ID',
  `booking` int(11) unsigned DEFAULT NULL COMMENT '记账编号',
  `created` datetime NOT NULL COMMENT '创建时间',
  `imported` datetime DEFAULT NULL COMMENT '导入时间',
  `accounted` date DEFAULT NULL COMMENT '记账日期',
  `payable` date DEFAULT NULL COMMENT '应付日期',
  `chargedDepositMonth` date DEFAULT NULL COMMENT '押金收取月份',
  `newpayable` date DEFAULT NULL COMMENT '新应付日期',
  `type` enum('押金','发票','费用','退费','结账','奖金','其他','滞纳金','律师催缴费','负债押金','负债发票','负债MMMA','负债催缴费','即时奖金','附加项','电动车','公平奖金','客户','MSB奖金','推荐奖金','额外支付','追加财务支付','减记借方','减记贷方','期望发票费用','灵活奖金','费用收入','断开连接费','重新连接费','馈电报酬','退货交付ES','一次性信用支付','能源成本援助金额','污水押金','水费押金','污水发票','水费发票') NOT NULL COMMENT '记录类型',
  `ene` enum('电','气','水','污水','供暖','预付费水') DEFAULT NULL COMMENT '能源类型',
  `groAm` decimal(16,2) NOT NULL COMMENT '总金额',
  `setAm` decimal(16,2) NOT NULL COMMENT '结算金额',
  `tax` decimal(5,2) NOT NULL COMMENT '税率',
  `noti` varchar(255) DEFAULT NULL COMMENT '通知内容',
  `wriOffRea` char(2) DEFAULT NULL COMMENT '核销原因',
  `datevExpo` datetime DEFAULT NULL COMMENT 'Datev导出时间',
  `firstDu` datetime DEFAULT NULL COMMENT '第一次逾期时间',
  `secondDu` datetime DEFAULT NULL COMMENT '第二次逾期时间',
  `thirdDu` datetime DEFAULT NULL COMMENT '第三次逾期时间',
  `isDe` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
  `isPayblk` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否支付冻结',
  `payBlk` date DEFAULT NULL COMMENT '支付冻结日期',
  `exported` datetime DEFAULT NULL COMMENT '导出时间',
  `exportArch` int(11) DEFAULT NULL COMMENT '导出归档ID',
  `invoid` int(11) NOT NULL DEFAULT '0' COMMENT '作废标记',
  `moddate` datetime DEFAULT NULL COMMENT '修改日期',
  `extConid` varchar(15) DEFAULT NULL COMMENT '外部合同ID',
  `exteyid` varchar(50) DEFAULT NULL COMMENT '外部实体ID',
  `mg_wa` varchar(6) DEFAULT NULL COMMENT '水表管理码',
  `extalMetParNu` bigint(20) DEFAULT NULL COMMENT '外部仪表参数编号',
  `valAdjAmount` decimal(12,2) DEFAULT NULL COMMENT '价值调整金额',
  `txKey` varchar(5) DEFAULT NULL COMMENT '交易密钥',
  `txTyp` varchar(40) DEFAULT NULL COMMENT '交易类型',
  `bokat` date NOT NULL COMMENT '记账日期',
  `isFulset` tinyint(1) DEFAULT NULL COMMENT '是否全额结算',
  `isLoc` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否本地化',
  PRIMARY KEY (`id`),
  UNIQUE KEY `bookingForClient` (`cId`,`booking`),
  KEY `cId` (`cId`),
  KEY `accountId` (`accountId`),
  KEY `scheId` (`scheduleId`),
  KEY `created` (`created`),
  KEY `type` (`type`),
  KEY `exported` (`exported`),
  KEY `client_noti` (`cId`,`noti`),
  KEY `accounted` (`accounted`),
  KEY `moddate` (`moddate`),
  KEY `cId_invoid` (`cId`,`invoid`),
  KEY `invoid_idx` (`invoid`),
  KEY `extConid` (`extConid`),
  KEY `exteyid` (`exteyid`),
  KEY `externalNIId` (`externalNIId`),
  KEY `mg_wa` (`mg_wa`),
  KEY `extalMetParNu` (`extalMetParNu`),
  KEY `account_type` (`accountId`,`type`),
  KEY `groAm` (`groAm`),
  KEY `payable` (`payable`),
  KEY `bokat` (`bokat`),
  KEY `account_receivable_idx_accountid_groAm` (`accountId`,`groAm`),
  KEY `account_receivable_idx_cId_created_exported` (`cId`,`created`,`exported`),
  KEY `isFulset` (`isFulset`),
  KEY `account_receivable_idx_invoid_groAm` (`invoid`,`groAm`),
  KEY `boing` (`booking`),
  KEY `book` (`booking`),
  KEY `account_receivables_idx_cId_isFulset_payable` (`cId`,`isFulset`,`payable`),
  CONSTRAINT `fk_a_acc` FOREIGN KEY (`accountId`) REFERENCES `acco` (`id`),
  CONSTRAINT `fk_a_cli` FOREIGN KEY (`cId`) REFERENCES `clie` (`id`),
  CONSTRAINT `fk_a_sche` FOREIGN KEY (`sched`) REFERENCES `accr` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=254601015 DEFAULT CHARSET=utf8

解答 #

1. 生产环境大型InnoDB表优化方案

• 数据归档：从年度分布看，2018年及之前记录仅约33万，占比极低，可将这些历史数据归档到独立的归档表或数据库。操作步骤：
  1. 新建与原表结构一致的归档表（如table_abc_archive）；
  2. 用INSERT ... SELECT分批迁移历史数据（每次迁移1-5万条，避免锁表）；
  3. 迁移完成后删除原表对应数据，同时建立归档表的必要索引。
• 分区优化：按created字段做RANGE分区，比如按年或季度分区。优势是查询历史数据时仅扫描对应分区，备份/维护更高效。注意：
  • 分区键要选查询频繁的字段（这里created符合）；
  • 先在测试环境验证业务兼容性，再在生产执行。
• 配置调优：调整InnoDB参数，比如增大innodb_buffer_pool_size（建议设为服务器内存的50%-70%）、开启innodb_file_per_table、调整innodb_log_file_size（建议设为1-4G，根据业务写入量）。
• 索引精简：见问题2的解答，删除冗余/未使用索引，减少写操作的索引维护开销。

2. 未使用索引的处理方案

• 延长统计周期：既然月度任务已覆盖，可再统计一个完整年度的索引使用情况（比如12个月），确保覆盖所有年度任务。
• 代码排查：搜索代码库中所有涉及该表的SQL，检查是否有未触发的年度任务SQL使用这些索引。重点看定时任务、报表类脚本。
• 软删除替代直接删除：
  1. 先将未使用的索引重命名（如idx_unused_old），保留3-6个月；
  2. 监控这段时间内的数据库错误日志，若没有索引缺失报错，再彻底删除；
  3. 若有报错，立即恢复原索引名称。
• 预检查清单：
  • 确认所有定时任务（月/季/年度）的SQL执行计划，是否依赖这些索引；
  • 检查备份/恢复脚本，是否有依赖索引的操作；
  • 在测试环境模拟删除索引，运行所有核心业务流程，验证无异常。

3. 慢查询优化方案

原查询的瓶颈在于MOD((o.accId - 19), 21) = 0这个条件无法利用索引，且OR created IS NULL会导致索引失效。优化步骤：

• 拆分查询：将OR条件拆分为两个独立查询，用UNION ALL合并结果，避免索引失效：

SELECT o.* FROM table_abc o 
WHERE o.cId = 3 
  AND o.created <= '2023-04-27 11:45:39' 
  AND booking IS NOT NULL 
  AND MOD((o.accId - 19), 21) = 0 
  AND o.exported IS NULL
UNION ALL
SELECT o.* FROM table_abc o 
WHERE o.cId = 3 
  AND o.created IS NULL 
  AND booking IS NOT NULL 
  AND MOD((o.accId - 19), 21) = 0 
  AND o.exported IS NULL

• 创建针对性联合索引：针对拆分后的查询，创建索引idx_cid_exported_created_accid（cId, exported, created, accId），让查询可以快速过滤出符合cId=3、exported IS NULL的记录，再处理created和accId的条件。
• 优化MOD条件：将MOD((accId -19),21)=0转化为计算列并建索引：
  1. 新增计算列：ALTER TABLE table_abc ADD COLUMN accId_mod21 INT AS ((accId -19) %21) STORED;
  2. 建立索引：CREATE INDEX idx_accId_mod21 ON table_abc(accId_mod21);
  3. 查询中用accId_mod21 =0替代原MOD条件，这样可以利用索引。
• 避免SELECT *：只查询需要的字段，减少数据传输和内存开销。

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