为何两个结构近乎一致的MySQL视图中一个性能极差？
2026-6-26
应用查询性能排查：
product_media_types_view资源占用过高分析 正在排查某应用查询运行极慢的问题，发现根源与所用的一个视图相关。该查询规模较大，其中*product_media_types_view视图占用的读取资源异常高。尤为奇怪的是，它与另一个product_families_view*视图结构近乎一致，且未发现明显索引问题。
完整查询语句（省略408个选中列）
SELECT `skus`.`yom_sku` -- omitted columns (408) FROM `skus` LEFT JOIN `product_families_view` ON `product_families_view`.`product_id` = `skus`.`yom_sku` LEFT JOIN `product_profit_projection_view` ON `product_profit_projection_view`.`product_id` = `skus`.`yom_sku` LEFT JOIN `product_media_types_view` ON `product_media_types_view`.`product_id` = `skus`.`yom_sku` LEFT JOIN `product` ON `product`.`yom_sku` = `skus`.`yom_sku` LEFT JOIN `product_listings` ON `product_listings`.`asin` = `skus`.`asin` AND `product_listings`.`seller_sku` = `skus`.`upc` LEFT JOIN `analyst` ON `analyst`.`yom_sku` = `skus`.`yom_sku` LEFT JOIN `collection` ON `collection`.`yom_sku` = `skus`.`yom_sku` LEFT JOIN `supplier_pricing` ON `supplier_pricing`.`yom_sku` = `skus`.`yom_sku` LEFT JOIN `amazon_order` ON `amazon_order`.`yom_sku` = `skus`.`yom_sku` LEFT JOIN `amazon_pricing` ON `amazon_pricing`.`yom_sku` = `skus`.`yom_sku` WHERE `product`.`retired` = false ORDER BY `skus`.`upc` ASC LIMIT 100
两个视图定义对比
-- pricetooldb.product_families_view source CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW `pricetooldb`.`product_families_view` AS select `pfp`.`product_id` AS `product_id`, `pfp`.`part_number` AS `part_number`, group_concat(`pf`.`family` order by `pf`.`family` ASC separator ', ') AS `families` from (`pricetooldb`.`product_families` `pf` join `pricetooldb`.`product_families_products` `pfp` on ((`pfp`.`family_id` = `pf`.`id`))) group by `pfp`.`product_id`; -- pricetooldb.product_media_types_view source CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW `pricetooldb`.`product_media_types_view` AS select `pmt`.`product_id` AS `product_id`, group_concat(`mt`.`name` order by `mt`.`name` ASC separator ' ') AS `media_types` from (`pricetooldb`.`media_types` `mt` join `pricetooldb`.`product_media_types` `pmt` on ((`pmt`.`media_type_id` = `mt`.`id`))) group by `pmt`.`product_id`;
EXPLAIN EXTENDED结果对比
执行EXPLAIN EXTENDED后，发现product_media_types_view的资源消耗远高于结构高度相似的product_families_view，关键数据如下：
[ { "table": { "table_name": "product_families_view", "access_type": "ref", "possible_keys": [ "<auto_key0>" ], "key": "<auto_key0>", "used_key_parts": [ "product_id" ], "key_length": "4", "ref": [ "pricetooldb.skus.yom_sku" ], "rows_examined_per_scan": 10, "rows_produced_per_join": 75142, "filtered": "100.00", "cost_info": { "read_cost": "45073.14", "eval_cost": "15028.53", "prefix_cost": "165159.79", "data_read_per_join": "4M" }, "used_columns": [ "product_id", "part_number", "families" ], "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 2, "cost_info": { "query_cost": "48405.37" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "cost_info": { "sort_cost": "21756.26" }, "nested_loop": [ { "table": { "table_name": "pf", "access_type": "index", "possible_keys": [ "PRIMARY" ], "key": "product_families_family_unique", "used_key_parts": [ "family" ], "key_length": "257", "rows_examined_per_scan": 2653, "rows_produced_per_join": 2653, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "11.00", "eval_cost": "530.60", "prefix_cost": "541.60", "data_read_per_join": "683K" }, "used_columns": [ "id", "family" ] } }, { "table": { "table_name": "pfp", "access_type": "ref", "possible_keys": [ "product_entry_unique", "product_families_products_family_id_foreign" ], "key": "product_families_products_family_id_foreign", "used_key_parts": [ "family_id" ], "key_length": "4", "ref": [ "pricetooldb.pf.id" ], "rows_examined_per_scan": 8, "rows_produced_per_join": 21756, "filtered": "100.00", "cost_info": { "read_cost": "21756.26", "eval_cost": "4351.25", "prefix_cost": "26649.11", "data_read_per_join": "1M" }, "used_columns": [ "id", "product_id", "family_id", "part_number" ] } } ] } } } } }, { "table": { "table_name": "product_media_types_view", "access_type": "ref", "possible_keys": [ "<auto_key0>" ], "key": "<auto_key0>", "used_key_parts": [ "product_id" ], "key_length": "4", "ref": [ "pricetooldb.skus.yom_sku" ], "rows_examined_per_scan": 10, "rows_produced_per_join": 1214678, "filtered": "100.00", "cost_info": { "read_cost": "728729.15", "eval_cost": "242935.67", "prefix_cost": "1321922.89", "data_read_per_join": "27M" }, "used_columns": [ "product_id", "media_types" ], "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 5, "cost_info": { "query_cost": "122384.12" }, "grouping_operation": { "using_filesort": false, "nested_loop": [ { "table": { "table_name": "p", "access_type": "index", "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", "used_key_parts": [ "yom_sku" ], "key_length": "4", "rows_examined_per_scan": 46669, "rows_produced_per_join": 46669, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "867.00", "eval_cost": "9333.80", "prefix_cost": "10200.80", "data_read_per_join": "74M" }, "used_columns": [ "yom_sku" ] } }, { "table": { "table_name": "pmt", "access_type": "ref", "possible_keys": [ "product_media_types_product_id_media_type_id_unique" ], "key": "product_media_types_product_id_media_type_id_unique", "used_key_parts": [ "product_id" ], "key_length": "5", "ref": [ "pricetooldb.p.yom_sku" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 46795, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "46669.22", "eval_cost": "9359.16", "prefix_cost": "66229.17", "data_read_per_join": "731K" }, "used_columns": [ "id", "product_id", "media_type_id" ] } }, { "table": { "table_name": "mt", "access_type": "eq_ref", "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", "used_key_parts": [ "id" ], "key_length": "4", "ref": [ "pricetooldb.pmt.media_type_id" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 46795, "filtered": "100.00", "cost_info": { "read_cost": "46795.79", "eval_cost": "9359.16", "prefix_cost": "122384.12", "data_read_per_join": "731K" }, "used_columns": [ "id", "name" ] } } ] } } } } } ]
相关表DDL（已删减无关列）
-- pricetooldb.product definition CREATE TABLE `product` ( `yom_sku` int(11) unsigned NOT NULL, PRIMARY KEY (`yom_sku`), CONSTRAINT `yom_sku_prod_fk` FOREIGN KEY (`yom_sku`) REFERENCES `skus` (`yom_sku`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- pricetooldb.product_media_types definition CREATE TABLE `product_media_types` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `product_id` int(11) unsigned DEFAULT NULL, `media_type_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `product_media_types_product_id_media_type_id_unique` (`product_id`,`media_type_id`), KEY `product_media_types_media_type_id_foreign` (`media_type_id`), CONSTRAINT `product_media_types_media_type_id_foreign` FOREIGN KEY (`media_type_id`) REFERENCES `media_types` (`id`) ON DELETE CASCADE, CONSTRAINT `product_media_types_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `product` (`yom_sku`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=46247 DEFAULT CHARSET=latin1; -- pricetooldb.media_types definition CREATE TABLE `media_types` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
疑问与求助
目前不清楚product_media_types_view资源占用过高的具体原因，恳请提供相关建议或排查思路。
内容的提问来源于stack exchange，提问作者kliuyyyy
相关产品推荐
相关产品推荐
