同执行计划MySQL查询服务器与本地性能差异排查求助

云上老周

2026-8-18

MySQL查询性能环境差异排查与问题解答

问题描述

一条SELECT查询在服务器执行耗时15秒，本地环境仅需0.297秒，两者的EXPLAIN执行计划完全一致。现咨询两个问题：

需要检查哪些服务器参数以定位性能差异？
EXPLAIN结果的表执行顺序是否会影响查询性能？

相关表结构

CREATE TABLE `Exam_Registration` ( 
    `examregnid` int(3) NOT NULL AUTO_INCREMENT, 
    `accountid` bigint(20) unsigned NOT NULL DEFAULT '0', 
    `examid` int(10) NOT NULL DEFAULT '0', 
    `examtype` int(1) NOT NULL DEFAULT '1' COMMENT '1-Regular,2-Supply,3-Betterment,4 -Notional', 
    `studentid` bigint(20) unsigned NOT NULL, 
    `colid` int(3) NOT NULL, 
    `courseid` int(3) NOT NULL, 
    `exrgstatus` int(1) NOT NULL COMMENT '1-Entry level,2-Apr by DEO,4-apr by Principal', 
    `internal_mark_status` int(1) NOT NULL COMMENT '1-Entry ,2-apr by DEO,3->apr by Principal', 
    `csid` int(6) NOT NULL, 
    `prjid` int(3) NOT NULL, 
    `examregn_supply` int(6) DEFAULT NULL, 
    `jr_appr_status` int(6) unsigned NOT NULL DEFAULT '0' COMMENT '1-Apr by JR', 
    `examreg_type` varchar(1) DEFAULT NULL COMMENT 'T - Provisonal Registration,P-Permanent Registration', 
    PRIMARY KEY (`examregnid`), 
    KEY `Index_2` (`accountid`,`examregnid`,`examid`,`colid`,`csid`,`prjid`) USING BTREE, 
    KEY `Index_3` (`accountid`), 
    KEY `Index_4` (`examregnid`), 
    KEY `Index_5` (`examid`), 
    KEY `Index_6` (`colid`) USING BTREE, 
    KEY `Index_7` (`csid`) USING BTREE, 
    KEY `Index_8` (`exrgstatus`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1623337 DEFAULT CHARSET=latin1; 

CREATE TABLE `student_exam_external` ( 
    `stud_exm_ext_id` int(11) NOT NULL AUTO_INCREMENT, 
    `examid` int(11) NOT NULL, 
    `examregnid` int(11) NOT NULL, 
    `stud_paper_id` int(6) unsigned NOT NULL COMMENT '1 for internal only, 2 for external only, 3 for internal and external', 
    `accountid` bigint(20) unsigned NOT NULL COMMENT '1 for internal only, 2 for external only, 3 for internal and external', 
    `subid` int(6) NOT NULL, 
    `prent_abscent` char(2) NOT NULL DEFAULT 'P' COMMENT 'P for present, A for abscent', 
    `falsenumber` varchar(100) DEFAULT NULL, 
    `generated_falsenumber` varchar(100) DEFAULT NULL, 
    `falsenumber_mapped_user` int(10) unsigned DEFAULT NULL, 
    `falsenumber_mapped_date` datetime DEFAULT NULL, 
    `falsenumber_map_status` int(2) unsigned DEFAULT '1' COMMENT '0-not verified; 1 verified by ACO; 2 Verification failed; 3 duplicate entry; 4 infected barcode', 
    `theory_ext_weighted_grade_point` int(10) DEFAULT NULL, 
    `theory_ext_grade_point` decimal(10,2) DEFAULT NULL, 
    `theory_ext_grade` char(2) DEFAULT NULL, 
    `theory_ext_grade_entred_user` int(10) DEFAULT NULL, 
    `theory_ext_grade_entred_date` datetime DEFAULT NULL, 
    `theory_ext_grade_status` int(2) DEFAULT NULL, 
    `grade_verification` int(1) DEFAULT NULL COMMENT 'NULL- not verified, 1-ACO Verified, 2-CO Verified', 
    `additional_code` varchar(100) DEFAULT NULL, 
    `current_false_number` varchar(100) NOT NULL, 
    PRIMARY KEY (`stud_exm_ext_id`), 
    KEY `Index_5` (`examid`) USING BTREE, 
    KEY `Index_6` (`subid`) USING BTREE, 
    KEY `Index_7` (`examregnid`) USING BTREE, 
    KEY `Index_8` (`accountid`) USING BTREE, 
    KEY `Index_9` (`falsenumber`) USING BTREE, 
    KEY `Index_10` (`stud_paper_id`) USING BTREE, 
    KEY `Index_11` (`falsenumber_map_status`) USING BTREE, 
    KEY `Index_12` (`stud_exm_ext_id`) USING BTREE, 
    KEY `Index_13` (`stud_exm_ext_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5464239 DEFAULT CHARSET=latin1;

CREATE TABLE `Qpcode_Master` ( 
    `qpcode` int(11) NOT NULL DEFAULT '0', 
    `examid` int(6) NOT NULL DEFAULT '0', 
    `subid` int(6) NOT NULL DEFAULT '0', 
    `semid` int(3) NOT NULL DEFAULT '0', 
    `generated_date` datetime NOT NULL, 
    `verification_status` int(1) NOT NULL, 
    `approve_date` datetime NOT NULL, 
    `qpid` int(6) unsigned NOT NULL AUTO_INCREMENT, 
    `ug_qpcode` int(11) unsigned DEFAULT NULL, 
    PRIMARY KEY (`qpid`), 
    UNIQUE KEY `Index_5` (`qpcode`,`examid`,`subid`), 
    KEY `Index_6` (`examid`) USING BTREE, 
    KEY `Index_7` (`qpcode`) USING BTREE, 
    KEY `Index_8` (`subid`) USING BTREE, 
    KEY `Index_9` (`semid`)
) ENGINE=InnoDB AUTO_INCREMENT=171008480 DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `camp_falseno_examiner_map` ( 
    `cmpexm_id` int(6) unsigned NOT NULL AUTO_INCREMENT, 
    `camp_id` int(6) unsigned NOT NULL, 
    `examid` int(6) DEFAULT NULL, 
    `qpcode` int(10) unsigned NOT NULL DEFAULT '0', 
    `st_range` int(20) unsigned NOT NULL DEFAULT '0', 
    `en_range` int(20) unsigned NOT NULL DEFAULT '0', 
    `chief_code` varchar(20) NOT NULL, 
    `additional_code` varchar(20) NOT NULL, 
    `entered_by` int(11) unsigned NOT NULL, 
    `entered_datetime` datetime NOT NULL, 
    PRIMARY KEY (`cmpexm_id`), 
    KEY `Index_2` (`examid`) USING BTREE, 
    KEY `Index_3` (`qpcode`) USING BTREE, 
    KEY `Index_4` (`st_range`) USING BTREE, 
    KEY `Index_5` (`en_range`) USING BTREE, 
    KEY `Index_6` (`camp_id`) USING BTREE, 
    KEY `Index_7` (`chief_code`) USING BTREE, 
    KEY `Index_8` (`additional_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=227704 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

CREATE TABLE `User_Details` ( 
    `user_tbl_id` int(50) unsigned NOT NULL AUTO_INCREMENT, 
    `User_Id` varchar(50) NOT NULL DEFAULT '', 
    `User_Type` char(8) NOT NULL, 
    `User_Name` varchar(50) NOT NULL, 
    `Password` varchar(100) NOT NULL, 
    `Password_old` varchar(100) NOT NULL, 
    `Status` char(1) NOT NULL, 
    `Camp` varchar(4) NOT NULL, 
    `college_id` int(11) DEFAULT NULL, 
    `prjid` int(1) unsigned NOT NULL, 
    `colid` int(3) unsigned NOT NULL, 
    `pwdstatus` int(1) unsigned NOT NULL DEFAULT '0' COMMENT '0-pwd not changed 1 -pwd changed', 
    `email` varchar(50) DEFAULT NULL, 
    `emailstatus` int(1) unsigned NOT NULL DEFAULT '0' COMMENT '0-email not updated 1 -email updated', 
    PRIMARY KEY (`User_Id`,`user_tbl_id`) USING BTREE, 
    UNIQUE KEY `Index_4` (`user_tbl_id`,`User_Id`) USING BTREE, 
    KEY `Index_3` (`user_tbl_id`) USING BTREE, 
    KEY `Index_5` (`User_Type`) USING BTREE, 
    KEY `Index_6` (`Status`) USING BTREE, 
    KEY `Index_7` (`User_Id`) USING BTREE, 
    KEY `Index_8` (`prjid`) USING BTREE, 
    KEY `Index_9` (`colid`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=104180 DEFAULT CHARSET=latin1;

查询语句

SELECT  distinct
        SE.stud_exm_ext_id,SE.falsenumber,            
        concat(UD.User_Name, ' - ',UD.User_Id)additional,
        UD.User_Id,
SE.theory_ext_weighted_grade_point,
        SE.theory_ext_grade_point,
SE.theory_ext_grade,
        SE.grade_verification, SE.additional_code
    FROM  Exam_Registration E
    INNER JOIN  student_exam_external SE
            ON SE.examid=E.examid
      and  SE.examregnid=E.examregnid
      and  SE.accountid=E.accountid
      and  SE.examid=47
    inner join  Qpcode_Master QM  ON QM.examid=E.examid
      and  QM.examid=E.examid
      AND  QM.subid=SE.subid
      and  QM.examid=47
      and  QM.qpcode=21101986
    inner join  camp_falseno_examiner_map CFE
              ON CFE.qpcode=QM.qpcode
      AND  CFE.examid=SE.examid
      AND  CFE.examid=QM.examid
      and  CFE.examid=E.examid
      and  CFE.examid=47
      and  CFE.qpcode=21101986
      and  CFE.st_range<=SE.falsenumber
      and  CFE.en_range>=SE.falsenumber
    inner join  User_Details UD -- use index(Index_7,PRIMARY)
            ON UD.User_Id=CFE.additional_code
      and  UD.User_Id=SE.additional_code
      and  UD.prjid=2
    where  QM.qpcode=21101986
      and  SE.examid=47
      and  SE.falsenumber>=314465
      and  SE.falsenumber<=314566
      and  SE.theory_ext_weighted_grade_point is not null
    order by  SE.falsenumber;

问题解答

1. 需要检查的服务器参数及系统指标

缓存相关参数

innodb_buffer_pool_size：InnoDB核心缓存，服务器该值过小会导致频繁磁盘读，本地环境数据量小可能缓存命中率更高。建议设置为服务器可用内存的50%-70%。
key_buffer_size：MyISAM索引缓存，你的Exam_Registration和User_Details是MyISAM表，若服务器该值不足，索引会频繁刷盘。
query_cache_size：仅针对MySQL 5.6及更早版本，若开启需检查缓存命中率，新版本已废弃该功能。

查询执行相关参数

sort_buffer_size：查询用到ORDER BY SE.falsenumber，若该值过小会触发磁盘排序，服务器压力大时可能出现性能瓶颈。
join_buffer_size：多表连接缓冲区，不足会导致临时表生成，拖慢连接速度。
optimizer_switch：确认服务器和本地的优化器开关完全一致，比如derived_merge、index_merge等关键选项。

系统与IO指标

CPU、内存使用率：用top、vmstat查看是否有其他进程抢占资源，导致MySQL无法分配足够资源。
磁盘IO性能：用iostat查看磁盘读写延迟，服务器可能是机械盘或存在IO队列积压，本地则可能用SSD或负载低。
网络延迟：若服务器是远程连接，需检查带宽和延迟，但50倍性能差大概率不是网络因素。

其他参数

max_connections、thread_cache_size：连接数过高会导致线程创建销毁开销大，检查是否有线程竞争。
innodb_flush_log_at_trx_commit、sync_binlog：严格的日志刷盘策略会增加IO开销，影响读查询性能。

2. EXPLAIN结果顺序对性能的影响

EXPLAIN的输出顺序（通过id列和表排列顺序）代表MySQL执行表的顺序，但如果两台环境的EXPLAIN所有字段完全一致（包括type、key、rows、Extra等），则执行计划逻辑相同，不会直接导致性能差异。

需注意：

即使执行顺序相同，服务器的缓存命中率、磁盘IO速度、系统负载等环境因素，会导致实际执行时间差异巨大。
仔细核对EXPLAIN的Extra字段，比如是否有Using temporary、Using filesort，以及rows列的预估行数是否一致。若有差异，可能是服务器表统计信息过时，需执行ANALYZE TABLE更新。

额外优化建议

清理查询冗余条件：多处重复的SE.examid=47、QM.qpcode=21101986可合并到一处，减少解析开销。
优化索引：
- 给student_exam_external创建联合索引：(examid, falsenumber, theory_ext_weighted_grade_point, examregnid, accountid, subid, additional_code)，覆盖查询所需字段，避免回表。
- 给camp_falseno_examiner_map创建联合索引：(examid, qpcode, st_range, en_range, additional_code)，加速范围匹配和连接。
替换MyISAM为InnoDB：MyISAM不支持事务、行级锁，并发场景性能差，建议将Exam_Registration和User_Details改为InnoDB引擎。

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

同执行计划MySQL查询服务器与本地性能差异排查求助

问题描述 #

相关表结构 #

查询语句 #

问题解答 #

1. 需要检查的服务器参数及系统指标 #