优化MySQL存储过程执行速度：将1分钟延迟降至毫秒级

现有存储过程mmyf_least_polling_booths_by_mandal_test执行耗时约1分钟，延迟显著，需对该存储过程及关联表结构进行优化，将执行时间缩短至毫秒级。

原存储过程代码 #

DELIMITER $$
CREATE PROCEDURE `mmyf_least_polling_booths_by_mandal_test`(IN `mandalid` INT)
BEGIN
    SELECT 
        b.id as booth_id,
        b.name as booth_name,
        b.booth_no,
        b.location as booth_location,
        b.sorting,
        COUNT(lp.voter_id) as voted,
        COUNT(v.id) as total_voters,
        (COUNT(v.id) - COUNT(lp.voter_id)) as not_voted,
        (CASE 
            WHEN COUNT(v.id) IS NULL THEN 0 
            WHEN COUNT(lp.voter_id) IS NULL THEN 0 
            WHEN COUNT(lp.voter_id) = 0 THEN 0 
            WHEN COUNT(v.id) = 0 THEN 0 
            ELSE ROUND((COUNT(lp.voter_id) / COUNT(v.id)) * 100) 
        END) as voting_per
    FROM mmyf_live_polling lp
    RIGHT JOIN mmyf_voters v ON v.id = lp.voter_id AND v.mandal_id = mandalid
    RIGHT JOIN booths b ON b.id = v.booth_id
    WHERE 
        v.mandal_id = mandalid
    GROUP BY v.booth_id
    ORDER BY voting_per ASC;
END$$
DELIMITER ;

关联表创建语句 #

CREATE TABLE `booths` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ac_id` int(10) unsigned DEFAULT NULL,
  `village_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `booth_no` smallint(10) unsigned DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `sorting` smallint(5) unsigned NOT NULL DEFAULT '0',
  `total_voters` int(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `mmyf_voters` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `name_v1` varchar(255) DEFAULT NULL,
  `relation_name` varchar(255) DEFAULT NULL,
  `relation_name_v1` varchar(255) DEFAULT NULL,
  `relation_type` tinyint(3) unsigned DEFAULT NULL COMMENT '1=父亲;2=丈夫;3=监护人',
  `mobile` varchar(32) DEFAULT NULL,
  `card_no` varchar(32) DEFAULT NULL,
  `booth_no` smallint(5) unsigned DEFAULT NULL,
  `serial_no` smallint(5) unsigned DEFAULT NULL,
  `house_no` varchar(64) DEFAULT NULL,
  `old_house_no` varchar(64) DEFAULT NULL,
  `incharge_id` smallint(5) unsigned DEFAULT NULL,
  `age` smallint(5) unsigned DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL COMMENT '1=男;2=女;3=其他',
  `address` varchar(255) DEFAULT NULL,
  `ward_no` int(10) unsigned DEFAULT NULL,
  `ac_id` int(10) unsigned DEFAULT NULL,
  `mandal_id` int(10) unsigned DEFAULT NULL,
  `village_id` int(10) unsigned DEFAULT NULL,
  `booth_id` int(10) unsigned DEFAULT NULL,
  `section_no` int(10) unsigned DEFAULT NULL,
  `ac_name` varchar(255) DEFAULT NULL,
  `ac_name_v1` varchar(255) DEFAULT NULL,
  `part_name` varchar(255) DEFAULT NULL,
  `part_name_v1` varchar(255) DEFAULT NULL,
  `psb_building_name` varchar(255) DEFAULT NULL,
  `tahsil_name_v1` varchar(255) DEFAULT NULL,
  `postoff_pin` int(11) DEFAULT NULL,
  `pcname` varchar(255) DEFAULT NULL,
  `pcname_v1` varchar(255) DEFAULT NULL,
  `profile_pic` varchar(255) DEFAULT NULL,
  `registered` tinyint(5) unsigned DEFAULT '0',
  `voter_update_count` tinyint(5) DEFAULT NULL,
  `voter_survey_count` tinyint(5) DEFAULT NULL,
  `mobile_verify` int(15) DEFAULT NULL,
  `verified_status` tinyint(3) DEFAULT NULL,
  `verified_by` int(45) DEFAULT NULL,
  `verified_at` datetime DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

CREATE TABLE `mmyf_live_polling` (
  `id` int(45) NOT NULL AUTO_INCREMENT,
  `voter_id` varchar(255) DEFAULT NULL,
  `card_no` varchar(255) DEFAULT NULL,
  `ac_id` int(45) DEFAULT NULL,
  `mandal_id` int(45) DEFAULT NULL,
  `village_id` int(45) DEFAULT NULL,
  `booth_no` int(45) DEFAULT NULL,
  `ward_no` int(45) DEFAULT NULL,
  `party_id` int(45) DEFAULT NULL,
  `religion` int(45) DEFAULT NULL,
  `gender` int(45) DEFAULT NULL,
  `age` smallint(5) DEFAULT NULL,
  `incharge_id` int(125) DEFAULT NULL,
  `incharge_user_id` int(125) DEFAULT NULL,
  `caste_id` int(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL COMMENT'输入代码',
  PRIMARY KEY (`id`)
);

优化方案 #

1. 索引优化 #

• mmyf_voters表：添加复合索引，覆盖过滤、关联字段，避免回表查询：

  CREATE INDEX idx_mandal_booth_id ON mmyf_voters(mandal_id, booth_id, id);
  

• mmyf_live_polling表：添加voter_id索引，提升关联统计效率：

  CREATE INDEX idx_voter_id ON mmyf_live_polling(voter_id);
  

2. 存储过程逻辑优化 #

调整JOIN顺序简化逻辑，减少不必要的数据扫描：

DELIMITER $$
CREATE PROCEDURE `mmyf_least_polling_booths_by_mandal_test`(IN `mandalid` INT)
BEGIN
    SELECT 
        b.id as booth_id,
        b.name as booth_name,
        b.booth_no,
        b.location as booth_location,
        b.sorting,
        COUNT(lp.voter_id) as voted,
        COUNT(v.id) as total_voters,
        (COUNT(v.id) - COUNT(lp.voter_id)) as not_voted,
        CASE 
            WHEN COUNT(v.id) = 0 THEN 0
            ELSE ROUND((COUNT(lp.voter_id) / COUNT(v.id)) * 100) 
        END as voting_per
    FROM booths b
    INNER JOIN mmyf_voters v ON b.id = v.booth_id AND v.mandal_id = mandalid
    LEFT JOIN mmyf_live_polling lp ON v.id = lp.voter_id
    GROUP BY b.id, b.name, b.booth_no, b.location, b.sorting
    ORDER BY voting_per ASC;
END$$
DELIMITER ;

优化说明：

• 从booths表出发内连接指定选区的选民，缩小数据范围
• 简化CASE判断，内连接保证v.id不会为NULL，减少冗余逻辑
• GROUP BY包含所有非聚合字段，符合MySQL严格模式要求

3. 数据类型优化 #

• 修正mmyf_live_polling.voter_id类型，与关联表字段一致，消除隐式类型转换：

  ALTER TABLE mmyf_live_polling MODIFY COLUMN voter_id mediumint(8) unsigned DEFAULT NULL;
  

• 将mmyf_live_polling中冗余的int(45)类型调整为合理范围（如int(10) unsigned），减少存储开销

4. 预计算优化（可选） #

若投票数据更新不频繁，可新增汇总表定时统计，实现毫秒级查询：

CREATE TABLE booth_voting_summary (
    booth_id int(10) unsigned NOT NULL PRIMARY KEY,
    mandal_id int(10) unsigned NOT NULL,
    total_voters int(10) unsigned NOT NULL DEFAULT 0,
    voted int(10) unsigned NOT NULL DEFAULT 0,
    voting_per tinyint(3) unsigned NOT NULL DEFAULT 0,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_mandal_id(mandal_id)
);

通过定时任务更新汇总表后，存储过程直接查询该表：

DELIMITER $$
CREATE PROCEDURE `mmyf_least_polling_booths_by_mandal_test`(IN `mandalid` INT)
BEGIN
    SELECT 
        b.id as booth_id,
        b.name as booth_name,
        b.booth_no,
        b.location as booth_location,
        b.sorting,
        s.voted,
        s.total_voters,
        (s.total_voters - s.voted) as not_voted,
        s.voting_per
    FROM booths b
    INNER JOIN booth_voting_summary s ON b.id = s.booth_id
    WHERE s.mandal_id = mandalid
    ORDER BY s.voting_per ASC;
END$$
DELIMITER ;

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