ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > 第65期 MySQL联合索引优化

第65期 MySQL联合索引优化

原创 MySQL 作者：pxbibm 时间：2026-06-10 14:20:01 0 删除 编辑

今天收到一个生产系统的预警，CPU使用率比较高，一直报警，登陆到mysql数据库上，通过show full PROCESSLIST命令发现后台有很多sql在跑，并且执行时间都比较长。

执行时间比较长的sql主要就是和 下面两个表有关系，并且表的数据量也比较大。表结构如下。

CREATE TABLE `callcenter_send` (
  `callcenter_send_id` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `batchno` bigint(20) unsigned zerofill NOT NULL,
  `contno` varchar(20) NOT NULL,
  `prtno` varchar(20) DEFAULT NULL,
  `paycount` int(11) NOT NULL,
  `payyears` int(11) NOT NULL,
  `cvalidate` date DEFAULT NULL,
  `paytodate` date DEFAULT NULL,
  `appntno` varchar(24) DEFAULT NULL,
  `appntname` varchar(120) DEFAULT NULL,
  `appntsex` varchar(1) DEFAULT NULL,
  `appnttel` varchar(20) DEFAULT NULL,
  `appntage` int(11) DEFAULT NULL,
  `appntidtype` varchar(6) DEFAULT NULL,
  `appntid` varchar(20) DEFAULT NULL,
  `insuredname` varchar(120) DEFAULT NULL,
  `mainriskcode` varchar(10) DEFAULT NULL,
  `mainRiskPrem` decimal(12,2) DEFAULT NULL,
  `subriskinfo` varchar(400) DEFAULT NULL,
  `salechnl` varchar(2) DEFAULT NULL,
  `handler` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `managecom` varchar(10) DEFAULT NULL,
  `bankcode` varchar(10) DEFAULT NULL,
  `bankaccno` varchar(40) DEFAULT NULL,
  `bankdealdate` date DEFAULT NULL,
  `bankresult` varchar(200) DEFAULT NULL,
  `insuredyears` varchar(10) DEFAULT NULL,
  `rnflag` varchar(2) DEFAULT NULL,
  `pleasepayflag` varchar(2) DEFAULT NULL,
  `amnt` decimal(12,2) DEFAULT NULL,
  `contstate` varchar(1) DEFAULT NULL,
  `payintv` int(11) DEFAULT NULL,
  `hmflag` varchar(1) DEFAULT NULL,
  `mainamnt` decimal(12,2) DEFAULT NULL,
  `paylocation` varchar(1) DEFAULT NULL,
  `confdate` date DEFAULT NULL,
  `firstsuccflag` varchar(1) DEFAULT NULL,
  `thirdsuccflag` varchar(1) DEFAULT NULL,
  `mainpolyear` int(11) DEFAULT NULL,
  `signdate` date DEFAULT NULL,
  `agentcode` varchar(10) DEFAULT NULL,
  `firstagentcode` varchar(10) DEFAULT NULL,
  `if_zd` varchar(2) DEFAULT NULL,
  `insuredno` varchar(24) DEFAULT NULL,
  `agentname` varchar(200) DEFAULT NULL,
  `firstagentname` varchar(120) DEFAULT NULL,
  `startdate` varchar(200) DEFAULT NULL,
  `statereason` varchar(10) DEFAULT NULL,
  `paytype` varchar(2) DEFAULT NULL,
  `appntaddress` varchar(200) DEFAULT NULL,
  `reapptype` varchar(100) DEFAULT NULL,
  `reoperator` varchar(80) DEFAULT NULL,
  `appntIdEnd` date DEFAULT NULL,
  `insuredIdEnd` date DEFAULT NULL,
  `workContFlag` varchar(255) DEFAULT NULL COMMENT '工单标识 0非工单 1工单',
  `sendflag` varchar(2) DEFAULT NULL COMMENT '推送标识 0-什么都不做，1-未智能推送，2-是已经推送催缴中心，3-是需要推送催缴中心',
  `sumPrem` decimal(12,2) DEFAULT NULL,
  `riskPeriod` varchar(1) DEFAULT NULL,
  `intellectCount` int(11) DEFAULT '0',
  `UnDeleted` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否删除',
  `CreateUser` varchar(80) NOT NULL COMMENT '创建者',
  `CreateDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
  `UpdateUser` varchar(80) NOT NULL COMMENT '修改者',
  `UpdateDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改日期',
  `intellectHandler` varchar(80) DEFAULT NULL,
  `universalAccMoney` decimal(12,2) DEFAULT NULL,
  `universalDJFlag` varchar(1) DEFAULT NULL,
  `firstBankCode` varchar(10) DEFAULT NULL,
  `agentCom` varchar(20) DEFAULT NULL,
  `chnl3Sale` varchar(20) DEFAULT NULL,
  `insuredIDType` varchar(2) DEFAULT NULL,
  `insuredIDNo` varchar(20) DEFAULT NULL,
  `relationToAppnt` varchar(2) DEFAULT NULL,
  `hangUpTypeName` varchar(20) DEFAULT NULL,
  `agentState` varchar(2) DEFAULT NULL,
  `conttype` varchar(1) DEFAULT NULL,
  `agentTel` varchar(20) DEFAULT NULL,
  `rePrem` decimal(12,2) DEFAULT NULL,
  `rnewflag` varchar(2) DEFAULT NULL,
  `sendType` varchar(1) DEFAULT NULL,
  `getnoticeno` varchar(20) DEFAULT NULL,
  `superviseCode` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`callcenter_send_id`) USING BTREE,
  UNIQUE KEY `idx_callcenter_send` (`contno`,`paycount`) USING BTREE,
  KEY `idx_appntno` (`appntno`) USING BTREE,
  KEY `idx_paytodate` (`paytodate`) USING BTREE,
  KEY `idx_sendFlag_salechnl` (`sendflag`,`salechnl`),
  KEY `ind_contNopayman` (`contno`,`paycount`,`managecom`),
  KEY `ind_getNoticeNo` (`getnoticeno`)
) ENGINE=InnoDB AUTO_INCREMENT=34220361 DEFAULT CHARSET=utf8mb4 COMMENT='';
CREATE TABLE `callcenter_workrecord` (
  `workrecord_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '',
  `taskNo` varchar(50) DEFAULT NULL COMMENT '',
  `contNo` varchar(20) NOT NULL COMMENT '',
  `payCount` int(11) NOT NULL COMMENT '',
  `mobile` varchar(20) DEFAULT NULL COMMENT '',
  `taskCode1` varchar(20) DEFAULT NULL COMMENT 'taskcode1',
  `taskCode2` varchar(20) DEFAULT NULL COMMENT 'taskcode2',
  `startTime` datetime DEFAULT NULL COMMENT '',
  `callDuration` varchar(20) DEFAULT NULL COMMENT '',
  `callURL` varchar(500) DEFAULT NULL COMMENT 'url',
  `estimateStates` varchar(20) DEFAULT NULL COMMENT '',
  `transferNode` varchar(200) DEFAULT NULL COMMENT '',
  `workType` varchar(200) DEFAULT NULL COMMENT '',
  `hangupReason` varchar(200) DEFAULT NULL COMMENT '',
  `uncollectedReason` varchar(50) DEFAULT NULL COMMENT '',
  `taskedId` varchar(50) DEFAULT NULL COMMENT '',
  `workRecord` varchar(200) DEFAULT NULL COMMENT '',
  `UnDeleted` tinyint(4) NOT NULL DEFAULT '1' COMMENT '',
  `CreateUser` varchar(80) NOT NULL COMMENT '',
  `CreateDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
  `UpdateUser` varchar(80) NOT NULL COMMENT '修改者',
  `UpdateDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改日期',
  `englishReason` varchar(30) DEFAULT NULL,
  `serialNo` bigint(20) DEFAULT NULL,
  `cifideas` varchar(20) DEFAULT NULL,
  `idno` varchar(20) DEFAULT NULL,
  `smscode` varchar(10) DEFAULT NULL,
  `issendsms` varchar(10) DEFAULT NULL,
  `issign` varchar(10) DEFAULT NULL,
  `signresult` varchar(20) DEFAULT NULL,
  `dialogLength` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`workrecord_id`) USING BTREE,
  KEY `index_workrecord` (`contNo`,`payCount`) USING BTREE,
  KEY `idx_startTime` (`startTime`),
  KEY `idx_callDuration` (`callDuration`),
  KEY `ind_callwork` (`contNo`,`payCount`,`startTime`,`callDuration`),
  KEY `idx_cover_workrecord` (`startTime`,`callDuration`,`contNo`,`payCount`,`hangupReason`)
) ENGINE=InnoDB AUTO_INCREMENT=9802201 DEFAULT CHARSET=utf8mb4 COMMENT='';

执行慢的sql是下面的，通过查看sql的执行计划发现，该sql执行计划都使用上了索引，但是仔细观察发现sql选择的执行计划并不是最优的。 

EXPLAIN 
SELECT t.reason,
               COUNT(t.reason) AS reasoncount,
               ROUND(COUNT(t.reason) * 100.0 /
                     (SELECT COUNT(*)
                        FROM callcenter_workrecord a 
                  INNER JOIN callcenter_send b
                    ON a.contno = b.contNo
                   AND a.payCount = b.payCount
                       WHERE a.startTime   >=   '2026-01-01'
                         AND a.startTime   <   '2026-06-09'
                         AND a.callDuration = '0'),
                     0) AS reasonrate
          FROM (SELECT CASE
                         WHEN a.hangupReason IN
                              ('停机', '关机', '占线', '无应答', '空号') THEN
                          a.hangupReason
                         WHEN a.hangupReason = '被叫不存在' THEN
                          '空号'
                         WHEN a.hangupReason IN ('落地拒接', '落地拒绝', '被叫拒绝') THEN
                          '拒接'
                         ELSE
                          '其它'
                       END AS reason
                  FROM callcenter_workrecord a
                 INNER JOIN callcenter_send b
                    ON a.contno = b.contNo
                   AND a.payCount = b.payCount
                 WHERE a.startTime   >=   '2026-01-01'
                   AND a.startTime   <   '2026-06-09'
                   AND a.callDuration = '0') t
         GROUP BY t.reason
         ORDER BY reasoncount DESC；

其实sql走 idx_cover_workrecord索引的执行计划比较高效，但是 表callcenter_workrecord有索引idx_cover_workrecord，为什么没有使用？

MySQL 5.7 不用它的 4 个真实原因（99% 是前 2 个）

1. 最左前缀 + 范围查询导致优化器 “不敢用”（最常见）

索引最左是 startTime（范围查询 >= <） MySQL 优化器会认为： 范围列后面的字段无法有效过滤，不如全表扫描更快

idx_cover_workrecord索引结构： 

startTime(范围) → callDuration(等值) → ...

MySQL 5.7 优化器 笨，它觉得：

先按时间扫出大量数据，再过滤 callDuration=0，不如直接全表。

2. 统计信息不准确（MySQL 5.7 通病）

表数据量大、更新频繁 → 优化器误以为全表更快 这是企业库最常见原因

3. callDuration 是字符串，你传了数字

你写的是： 

a.callDuration = '0'   vs   a.callDuration = 0

字符集 / 隐式转换 → 索引失效

4. 索引碎片太多

优化器觉得索引 “不整齐”，放弃使用

优化方法：100% 让 MySQL 强制使用这个索引的方法

 

方法 1： 改索引顺序（最根治、一劳永逸）


把 等值查询放最前面， 范围查询放后面 这是 MySQL 索引黄金法则：

-- 先删除旧索引
DROP INDEX idx_cover_workrecord ON callcenter_workrecord;
-- 新建正确顺序：等值 → 范围 → 其他
CREATE INDEX idx_cover_workrecord 
ON callcenter_workrecord (callDuration, startTime, contNo, payCount, hangupReason)ALGORITHM=INPLACE, LOCK=NONE;


为什么这样一定生效？


  • callDuration = '0' 等值查询（放最左）
  • startTime 范围查询 MySQL 会：
  1. 先快速定位所有 callDuration=0
  2. 再在小范围内按时间过滤 100% 命中索引，速度提升几十倍

EXPLAIN 
SELECT t.reason,
               COUNT(t.reason) AS reasoncount,
               ROUND(COUNT(t.reason) * 100.0 /
                     (SELECT COUNT(*)
                        FROM callcenter_workrecord a 
                  INNER JOIN callcenter_send b
                    ON a.contno = b.contNo
                   AND a.payCount = b.payCount
                       WHERE a.startTime   >=   '2026-01-01'
                         AND a.startTime   <   '2026-06-09'
                         AND a.callDuration = '0'),
                     0) AS reasonrate
          FROM (SELECT CASE
                         WHEN a.hangupReason IN
                              ('停机', '关机', '占线', '无应答', '空号') THEN
                          a.hangupReason
                         WHEN a.hangupReason = '被叫不存在' THEN
                          '空号'
                         WHEN a.hangupReason IN ('落地拒接', '落地拒绝', '被叫拒绝') THEN
                          '拒接'
                         ELSE
                          '其它'
                       END AS reason
                  FROM callcenter_workrecord a
                 INNER JOIN callcenter_send b
                    ON a.contno = b.contNo
                   AND a.payCount = b.payCount
                 WHERE a.startTime   >=   '2026-01-01'
                   AND a.startTime   <   '2026-06-09'
                   AND a.callDuration = '0') t
         GROUP BY t.reason
         ORDER BY reasoncount DESC；

方法 2：如果你不想删索引，直接用 FORCE INDEX 强制走索引

SQL 加一句话，立刻生效：

SELECT t.reason,
               COUNT(t.reason) AS reasoncount,
               ROUND(COUNT(t.reason) * 100.0 /
                     (SELECT COUNT(*)
                        FROM callcenter_workrecord a FORCE INDEX (idx_cover_workrecord)
                  INNER JOIN callcenter_send b
                    ON a.contno = b.contNo
                   AND a.payCount = b.payCount
                       WHERE a.startTime   >=   '2026-01-01'
                         AND a.startTime   <   '2026-06-09'
                         AND a.callDuration = '0'),
                     0) AS reasonrate
          FROM (SELECT CASE
                         WHEN a.hangupReason IN
                              ('停机', '关机', '占线', '无应答', '空号') THEN
                          a.hangupReason
                         WHEN a.hangupReason = '被叫不存在' THEN
                          '空号'
                         WHEN a.hangupReason IN ('落地拒接', '落地拒绝', '被叫拒绝') THEN
                          '拒接'
                         ELSE
                          '其它'
                       END AS reason
                  FROM callcenter_workrecord a FORCE INDEX (idx_cover_workrecord)
                 INNER JOIN callcenter_send b
                    ON a.contno = b.contNo
                   AND a.payCount = b.payCount
                 WHERE a.startTime   >=   '2026-01-01'
                   AND a.startTime   <   '2026-06-09'
                   AND a.callDuration = '0') t
         GROUP BY t.reason
         ORDER BY reasoncount DESC

99% 立刻解决索引不生效问题

方法 3：如果你不想删索引，改写sql语句，把 a .callDuration = '0' 改成a.callDuration = 0 走索引 idx_cover_workrecord

         EXPLAIN 
         SELECT t.reason,
               COUNT(t.reason) AS reasoncount,
               ROUND(COUNT(t.reason) * 100.0 /
                     (SELECT COUNT(*)
                        FROM callcenter_workrecord a 
                  INNER JOIN callcenter_send b
                    ON a.contno = b.contNo
                   AND a.payCount = b.payCount
                       WHERE a.startTime   >=   '2026-01-01'
                         AND a.startTime   <   '2026-06-09'
                         AND a.callDuration = 0),
                     0) AS reasonrate
          FROM (SELECT CASE
                         WHEN a.hangupReason IN
                              ('停机', '关机', '占线', '无应答', '空号') THEN
                          a.hangupReason
                         WHEN a.hangupReason = '被叫不存在' THEN
                          '空号'
                         WHEN a.hangupReason IN ('落地拒接', '落地拒绝', '被叫拒绝') THEN
                          '拒接'
                         ELSE
                          '其它'
                       END AS reason
                  FROM callcenter_workrecord a
                 INNER JOIN callcenter_send b
                    ON a.contno = b.contNo
                   AND a.payCount = b.payCount
                 WHERE a.startTime   >=   '2026-01-01'
                   AND a.startTime   <   '2026-06-09'
                   AND a.callDuration = 0) t
         GROUP BY t.reason
         ORDER BY reasoncount DESC


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12798004/viewspace-3173465/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 第64期 ORA-00600: internal error code, arguments: [qosdExpStatRead
下一篇： 第66期 GoldenGate 管理客户端(AdminClient)下载
请登录后发表评论 登录
全部评论
pxbibm
专家姓名：彭小波
Oracle ACE。ACOUG核心成员，Oracle用户组年轻专家。熟悉Oracle的体系结构，擅长Oracle数据库架构规划、SQL优化、OWI方面性能的优化。曾服务于航天、航空、机车、大型制造行业数据库的维护以及系统开发工作,开发过基于Oracle数据库的大型ERP系统。
  • 博文量
    345
  • 访问量
    2477886

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593