百万级数据下基于多独立表的高效查询存储过程编写方案

一、前置索引优化（核心性能保障） #

针对百万级数据量，必须先建立合适的索引避免全表扫描，否则查询性能会急剧下降：

• 表A：创建联合覆盖索引 idx_tableA_foodid_price (food_id, price)，这样SUM(price)查询可以直接从索引获取数据，无需回表
• 表B：创建两个索引：
  • idx_tableB_drinkid (drink_id)：满足COUNT(*)查询的快速定位
  • idx_tableB_drinkid_typeid_price (drink_id, type_id, price)：满足带type_id条件的SUM(price)覆盖查询
• 表C：确保id（对应表A的food_id）为主键（默认自带主键索引），保证关联查询的效率

二、存储过程代码实现（以MySQL为例） #

DELIMITER //
CREATE PROCEDURE GetRequiredStats()
BEGIN
    -- 声明变量存储各统计结果
    DECLARE col1 DECIMAL(10,2);
    DECLARE col2 INT;
    DECLARE col3 DECIMAL(10,2);
    DECLARE col4 VARCHAR(100);

    -- 利用索引快速查询各值，避免重复扫描表
    SELECT SUM(price) INTO col1 FROM tableA WHERE food_id = 3;
    SELECT COUNT(*) INTO col2 FROM tableB WHERE drink_id = 6;
    SELECT SUM(price) INTO col3 FROM tableB WHERE drink_id = 6 AND type_id = 3;
    -- 修正原需求中的关联错误：表C的id对应表A的food_id，而非a.id
    SELECT c.Name INTO col4 FROM tableA a LEFT JOIN tableC c ON a.food_id = c.id WHERE a.id = 1;

    -- 输出整合后的结果集
    SELECT 
        col1 AS `column 1`,
        col2 AS `column 2`,
        col3 AS `column 3`,
        col4 AS `column 4`;
END //
DELIMITER ;

三、性能优化说明 #

1. 索引驱动查询：所有查询都命中覆盖索引，百万级数据下可将查询耗时从秒级压缩到毫秒级
2. 变量暂存结果：用变量存储每个统计值，避免重复访问表，减少磁盘IO开销
3. 关联逻辑修正：原需求中的表A与表C关联条件错误（原写a.id = c.id），修正为a.food_id = c.id才能正确获取Banana
4. 单结果集返回：将四个指标整合为一个结果集输出，更符合业务系统的使用习惯

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