如何将SQL视图转换为表并优化多关联查询性能

一、优化后的物化表创建与数据插入SQL #

1. 创建目标表 #

先创建与视图字段匹配的物理表，字段类型需与原表对应字段保持一致（可根据实际业务调整字段长度、精度）：

CREATE TABLE `party_make_subcat_size_map` (
    `material_name` VARCHAR(255),
    `pk_id` INT,
    `fk_party_id` INT,
    `fk_party_type_id` INT,
    `fk_make_id` INT,
    `fk_sub_cat_id` INT,
    `fk_size_id` INT,
    `status` TINYINT,
    `fk_financial_year_id` INT,
    `fk_company_id` INT,
    `quantity` DECIMAL(10,2),
    `make_name` VARCHAR(255),
    `sub_cat_name` VARCHAR(255),
    `party_name` VARCHAR(255),
    `fk_city_id` INT,
    `fk_state_id` INT,
    `company_type` VARCHAR(50),
    `fk_area` INT,
    `hsn_name` VARCHAR(255),
    `fk_hsn_id` INT,
    `size_name` VARCHAR(255),
    `party_address` TEXT,
    -- 添加联合主键提升查询性能
    PRIMARY KEY (`pk_id`, `fk_make_id`, `fk_sub_cat_id`, `fk_size_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 插入数据（优化后的查询） #

移除冗余的DISTINCT（若关联逻辑不会产生重复行），调整JOIN顺序优先过滤数据量：

INSERT INTO `party_make_subcat_size_map`
SELECT 
    `m`.`part_name` AS `material_name`,
    `pt`.`pk_id` AS `pk_id`,
    `pms`.`fk_party_id` AS `fk_party_id`,
    `pms`.`fk_party_type_id` AS `fk_party_type_id`,
    `k`.`pk_id` AS `fk_make_id`,
    `pms`.`fk_sub_cat_id` AS `fk_sub_cat_id`,
    `pms`.`fk_size_id` AS `fk_size_id`,
    `pms`.`status` AS `status`,
    `pms`.`fk_financial_year_id` AS `fk_financial_year_id`,
    `pms`.`fk_company_id` AS `fk_company_id`,
    `pms`.`quantity` AS `quantity`,
    `k`.`name` AS `make_name`,
    `sc`.`name` AS `sub_cat_name`,
    `pt`.`name` AS `party_name`,
    `pt`.`fk_city_id` AS `fk_city_id`,
    `pt`.`fk_state_id` AS `fk_state_id`,
    `pt`.`company_type` AS `company_type`,
    `pt`.`fk_area` AS `fk_area`,
    `hsn`.`hsn_name` AS `hsn_name`,
    `m`.`fk_hsn_id` AS `fk_hsn_id`,
    `size`.`name` AS `size_name`,
    `pt`.`address` AS `party_address`
FROM `ma_party_make_subcat_map` `pms`
-- 先过滤状态，减少后续关联的数据量
WHERE `pms`.`status` = 2
-- 优先关联主表和一对一/一对多的表
JOIN `ma_company` `pt` ON `pt`.`pk_id` = `pms`.`fk_party_id`
JOIN `ma_make` `k` ON `k`.`pk_id` = `pms`.`fk_make_id`
JOIN `ma_size` `size` ON `size`.`pk_id` = `pms`.`fk_size_id`
JOIN `ma_material_make_map` `mm` ON `mm`.`fk_make_id` = `k`.`pk_id`
JOIN `ma_material` `m` ON `m`.`pk_id` = `mm`.`fk_material_id`
-- 左关联非必填表
LEFT JOIN `ma_sub_category` `sc` ON `sc`.`pk_id` = `pms`.`fk_sub_cat_id`
LEFT JOIN `ma_hsn_tax_map` `hsn` ON `m`.`fk_hsn_id` = `hsn`.`fk_hsn_id`;

二、关键优化点说明 #

• 物化表替代视图：视图每次查询都需重新执行多表关联逻辑，物化表将结果持久化存储，查询时直接读取物理数据，性能提升显著。
• 移除DISTINCT：原视图的DISTINCT会触发排序去重操作，若ma_material_make_map与ma_material是一对一关联，或关联条件已保证无重复行，可直接移除；若确实存在重复，可改用GROUP BY指定唯一键，比DISTINCT更高效。
• 索引优化：在原表的关联字段和过滤字段添加索引，加速关联查询：

  -- 给主过滤字段加索引
  CREATE INDEX idx_pms_status ON `ma_party_make_subcat_map`(`status`);
  -- 给外键字段加索引
  CREATE INDEX idx_pms_fk_party ON `ma_party_make_subcat_map`(`fk_party_id`);
  CREATE INDEX idx_pms_fk_make ON `ma_party_make_subcat_map`(`fk_make_id`);
  CREATE INDEX idx_pms_fk_size ON `ma_party_make_subcat_map`(`fk_size_id`);
  CREATE INDEX idx_mm_fk_make ON `ma_material_make_map`(`fk_make_id`);
  

• 调整JOIN顺序：将带过滤条件的表（ma_party_make_subcat_map）放在最前面，先过滤出符合条件的数据，再与其他表关联，减少后续关联的数据量。
• 数据更新策略：
  • 全量刷新：若数据更新频率低，可定时（如每日凌晨）执行TRUNCATE TABLE后重新插入数据。
  • 增量更新：若数据更新频繁，可通过pms表的更新时间字段（若有），仅插入/更新新增或修改的数据，避免全量刷新的开销。

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