mysql - MYSQL Select 查询卡在“发送数据”中
问题描述
我有一个大约 1M 记录的选择查询，我正在使用 Magento 1.9 数据库。
SELECT IF(sup_ap.is_percent = 1, TRUNCATE(mt.value + (mt.value * sup_ap.pricing_value / 100), 4),
mt.value + SUM(sup_ap.pricing_value)) AS `value`,
75 AS `attribute_id`,
`supl`.`product_id` AS `entity_id`,
`cs`.`store_id`
FROM `catalog_product_entity_decimal` AS `mt`
LEFT JOIN `catalog_product_super_attribute` AS `sup_a` ON mt.entity_id = product_id
INNER JOIN `catalog_product_super_attribute_pricing` AS `sup_ap`
ON sup_ap.product_super_attribute_id = sup_a.product_super_attribute_id
INNER JOIN `catalog_product_super_link` AS `supl` ON mt.entity_id = supl.parent_id
INNER JOIN `catalog_product_entity_int` AS `pint`
ON pint.entity_id = supl.product_id and pint.attribute_id = sup_a.attribute_id and
pint.value = sup_ap.value_index
INNER JOIN `core_store` AS `cs` ON cs.website_id = sup_ap.website_id
WHERE (mt.entity_id in (select product_id from catalog_product_super_attribute))
AND (mt.attribute_id = '75')
GROUP BY `entity_id`, `cs`.`store_id`
LIMIT 500
我的解释：
+------+-------------+---------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
| 1 | PRIMARY | cs | index | IDX_CORE_STORE_WEBSITE_ID | IDX_CORE_STORE_WEBSITE_ID | 2 | NULL | 7 | Using index; Using temporary; Using filesort |
| 1 | PRIMARY | sup_ap | ref | UNQ_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID_VAL_IDX_WS_ID,IDX_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID,IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRICING_WEBSITE_ID | IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRICING_WEBSITE_ID | 2 | cs.website_id | 11 | |
| 1 | PRIMARY | sup_a | eq_ref | PRIMARY,UNQ_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID | PRIMARY | 4 | sup_ap.product_super_attribute_id | 1 | |
| 1 | PRIMARY | mt | ref | UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID,IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID | 6 | sup_a.product_id,const | 1 | |
| 1 | PRIMARY | catalog_product_super_attribute | ref | UNQ_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID | UNQ_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID_ATTRIBUTE_ID | 4 | sup_a.product_id | 1 | Using index; FirstMatch(mt) |
| 1 | PRIMARY | supl | ref | UNQ_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID | IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID | 4 | sup_a.product_id | 4 | |
| 1 | PRIMARY | pint | ref | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 6 | supl.product_id,sup_a.attribute_id | 1 | Using where |
+------+-------------+---------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
我没有关于优化选择查询的经验，因为卡在发送数据上，我尝试将选择查询更新为：
SELECT IF(sup_ap.is_percent = 1, TRUNCATE(mt.value + (mt.value * sup_ap.pricing_value / 100), 4),
mt.value + SUM(sup_ap.pricing_value)) AS `value`,
75 AS `attribute_id`,
`supl`.`product_id` AS `entity_id`,
`cs`.`store_id`
FROM (select entity_id, `value` from `catalog_product_entity_decimal` where attribute_id = '75') AS `mt`
LEFT JOIN `catalog_product_super_attribute` AS `sup_a` ON mt.entity_id = product_id
INNER JOIN `catalog_product_super_attribute_pricing` AS `sup_ap`
ON sup_ap.product_super_attribute_id = sup_a.product_super_attribute_id
INNER JOIN `catalog_product_super_link` AS `supl` ON mt.entity_id = supl.parent_id
INNER JOIN `catalog_product_entity_int` AS `pint`
ON pint.entity_id = supl.product_id and pint.attribute_id = sup_a.attribute_id and
pint.value = sup_ap.value_index
INNER JOIN `core_store` AS `cs` ON cs.website_id = sup_ap.website_id
WHERE (sup_a.product_id is not null)
GROUP BY `entity_id`, `cs`.`store_id`
LIMIT 500;
新解释：
+------+-------------+--------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | cs | index | IDX_CORE_STORE_WEBSITE_ID | IDX_CORE_STORE_WEBSITE_ID | 2 | NULL | 7 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | sup_ap | ref | UNQ_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID_VAL_IDX_WS_ID,IDX_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID,IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRICING_WEBSITE_ID | IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRICING_WEBSITE_ID | 2 | cs.website_id | 11 | |
| 1 | SIMPLE | sup_a | eq_ref | PRIMARY,UNQ_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID | PRIMARY | 4 | sup_ap.product_super_attribute_id | 1 | Using where |
| 1 | SIMPLE | catalog_product_entity_decimal | ref | UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID,IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID | 6 | sup_a.product_id,const | 1 | |
| 1 | SIMPLE | supl | ref | UNQ_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID,IDX_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID | IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID | 4 | sup_a.product_id | 4 | |
| 1 | SIMPLE | pint | ref | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID | UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 6 | supl.product_id,sup_a.attribute_id | 1 | Using where |
+------+-------------+--------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
更新 1：表结构
1：catalog_product_entity_decimal；
CREATE TABLE `catalog_product_entity_decimal` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`entity_type_id` smallint(5) unsigned NOT NULL COMMENT 'Entity Type ID',
`attribute_id` smallint(5) unsigned NOT NULL COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL COMMENT 'Store ID',
`entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
`value` decimal(12,4) DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID` (`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID` (`entity_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID` (`attribute_id`),
CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_DEC_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_DEC_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=28087876 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Decimal Attribute Backend Table'
2：catalog_product_super_attribute_pricing
CREATE TABLE `catalog_product_super_attribute_pricing` (
`value_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`product_super_attribute_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Product Super Attribute ID',
`value_index` varchar(255) NOT NULL COMMENT 'Value Index',
`is_percent` smallint(5) unsigned DEFAULT '0' COMMENT 'Is Percent',
`pricing_value` decimal(12,4) DEFAULT NULL COMMENT 'Pricing Value',
`website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website ID',
PRIMARY KEY (`value_id`),
UNIQUE KEY `UNQ_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID_VAL_IDX_WS_ID` (`product_super_attribute_id`,`value_index`,`website_id`),
KEY `IDX_CAT_PRD_SPR_ATTR_PRICING_PRD_SPR_ATTR_ID` (`product_super_attribute_id`),
KEY `IDX_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRICING_WEBSITE_ID` (`website_id`),
CONSTRAINT `FK_CAT_PRD_SPR_ATTR_PRICING_WS_ID_CORE_WS_WS_ID` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CDE8813117106CFAA3AD209358F66332` FOREIGN KEY (`product_super_attribute_id`) REFERENCES `catalog_product_super_attribute` (`product_super_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Super Attribute Pricing Table'
3：catalog_product_super_link
CREATE TABLE `catalog_product_super_link` (
`link_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Link ID',
`product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Product ID',
`parent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Parent ID',
PRIMARY KEY (`link_id`),
UNIQUE KEY `UNQ_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID_PARENT_ID` (`product_id`,`parent_id`),
KEY `IDX_CATALOG_PRODUCT_SUPER_LINK_PARENT_ID` (`parent_id`),
KEY `IDX_CATALOG_PRODUCT_SUPER_LINK_PRODUCT_ID` (`product_id`),
CONSTRAINT `FK_CAT_PRD_SPR_LNK_PARENT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`parent_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_SPR_LNK_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1200 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Super Link Table'
4：catalog_product_entity_int
CREATE TABLE `catalog_product_entity_int` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`entity_type_id` int(10) unsigned NOT NULL COMMENT 'Entity Type ID',
`attribute_id` smallint(5) unsigned NOT NULL COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL COMMENT 'Store ID',
`entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
`value` int(11) DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID` (`attribute_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_INT_STORE_ID` (`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID` (`entity_id`),
CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_INT_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_INT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=36351339 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Integer Attribute Backend Table'
5：core_store
CREATE TABLE `core_store` (
`store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Store Id',
`code` varchar(32) DEFAULT NULL COMMENT 'Code',
`website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website Id',
`group_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
`name` varchar(255) NOT NULL COMMENT 'Store Name',
`sort_order` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Sort Order',
`is_active` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Activity',
PRIMARY KEY (`store_id`),
UNIQUE KEY `UNQ_CORE_STORE_CODE` (`code`),
KEY `IDX_CORE_STORE_WEBSITE_ID` (`website_id`),
KEY `IDX_CORE_STORE_IS_ACTIVE_SORT_ORDER` (`is_active`,`sort_order`),
KEY `IDX_CORE_STORE_GROUP_ID` (`group_id`),
CONSTRAINT `FK_CORE_STORE_GROUP_ID_CORE_STORE_GROUP_GROUP_ID` FOREIGN KEY (`group_id`) REFERENCES `core_store_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CORE_STORE_WEBSITE_ID_CORE_WEBSITE_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='Stores'
非常感谢您能花点时间考虑关注上述问题。
更新 2
我发现我有一个包含 2 个两列的连接条件具有不同类型的问题。
pint.value = sup_ap.value_index
解决方案
我想你可以摆脱
LEFT因为WHERE (sup_a.product_id is not null)一些复合（和覆盖）索引：
sup_a: (product_id, attribute_id, product_super_attribute_id) pint: (value, attribute_id, entity_id) supl: (product_id, parent_id) mt: (attribute_id, entity_id, value) -- perhaps the most imporant
还， ...
混合数字和字符串数据类型时要小心，其中一个是常量：
char_col = "123" -- good char_col = 123 -- performs poorly (won't use index) int_col = 123 -- good int_col = "123" -- this happens to be 'good' int_col = "123ABC" -- may give strange results比较时确保
COLLATION匹配VARCHARs。