添加冗余索引是否有可能提高查询的性能？几天前我遇到过这样的案例，从那以后我一直在摸不着头脑：怎么办？
MariaDB 10.4 InnoDB 表magic在 上有一个索引(a, b, c)，列是 INT，PK 不是该索引的一部分。
一个表现不佳的 SELECT 看起来像：
select ...
from X
left join magic M1 on M1.a = X.a and M1.b = X.b and M1.c = 100
left join magic M2 on M2.a = X.a and M2.b = X.b and M2.c = 200
left join other O on O.a = M1.a
where
O.a in (123, 234, 345, ..., 789)
（查询是 Magento2 核心的一部分）。
magic一位开发人员在刚才的表中添加了一个新索引(a)（这是一个多余的索引），然后，神奇的是，SELECT 获得了显着的性能提升。
但是，在添加新索引之前和之后，来自 EXPLAIN 的查询计划保持不变。两者（对不起 - 我得到了误导性信息）。(a, b, c)都只使用索引。
这对我来说似乎有点奇怪。我无法理解：为什么以及如何？从理论上讲，这个新索引不会改变任何东西。但在实践中 - 它确实如此。
我发现了这个：https ://blog.pythian.com/duplicate-indexes-in-mysql/ ，但这篇文章没有解释为什么事情会这样。
其他所有地方都重复并同意“可以而且应该删除冗余索引”。
所以有人遇到过和我类似的情况并且知道它是如何工作的吗？
编辑：（现在，我可以访问数据库）
原始查询（由 Magneto2 框架以编程方式构建）：
SELECT `u`.*
FROM ((SELECT DISTINCT `cpe`.`entity_id`,
`dd`.`attribute_id`,
`s`.`store_id`,
COALESCE(ds.value, dd.value) AS `value`,
`cpe`.`entity_id` AS `source_id`
FROM `store` AS `s`
LEFT JOIN `catalog_product_entity_int` AS `dd` ON dd.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `ds`
ON ds.store_id = s.store_id AND ds.attribute_id = dd.attribute_id AND
ds.entity_id = dd.entity_id
LEFT JOIN `catalog_product_entity_int` AS `d2d`
ON d2d.store_id = 0 AND d2d.entity_id = dd.entity_id AND d2d.attribute_id = 97
LEFT JOIN `catalog_product_entity_int` AS `d2s`
ON d2s.store_id = s.store_id AND d2s.attribute_id = d2d.attribute_id AND
d2s.entity_id = d2d.entity_id
LEFT JOIN `catalog_product_entity` AS `cpe` ON cpe.entity_id = dd.entity_id
LEFT JOIN `catalog_product_entity_int` AS `pis`
ON pis.entity_id = cpe.entity_id AND pis.attribute_id = dd.attribute_id AND
pis.store_id = s.store_id
WHERE (s.store_id != 0)
AND ((ds.value IS NOT NULL OR dd.value IS NOT NULL))
AND (COALESCE(d2s.value, d2d.value) = 1)
AND (dd.attribute_id IN (83, 93, 99, 205, 209, 211, 212))
AND (NOT (pis.value IS NULL AND pis.value_id IS NOT NULL))
AND (IFNULL(pis.value, COALESCE(ds.value, dd.value)) IS NOT NULL)
AND (cpe.entity_id IN
(653591, 653603, 653690, 653697, 653701, 653708, 653716, 653728, 653737, 653748, 653752, 653764, 653774,
653775, 689894, 689895, 689896, 689897, 689898, 689899, 689900, 689901, 689902, 689903, 689904, 689905,
689906)))
UNION
(SELECT `cpe`.`entity_id`, `wd`.`attribute_id`, `wd`.`store_id`, `wd`.`value`, `cpe`.`entity_id`
FROM `catalog_product_entity_int` AS `wd`
LEFT JOIN `catalog_product_entity` AS `cpe` ON cpe.entity_id = wd.entity_id
LEFT JOIN `catalog_product_entity_int` AS `d2d`
ON d2d.store_id = 0 AND d2d.entity_id = wd.entity_id AND d2d.attribute_id = 97
LEFT JOIN `catalog_product_entity_int` AS `d2s`
ON d2s.store_id != 0 AND d2s.attribute_id = d2d.attribute_id AND d2s.entity_id = d2d.entity_id
WHERE (COALESCE(d2s.value, d2d.value) = 1)
AND (wd.attribute_id IN (83, 93, 99, 205, 209, 211, 212))
AND (wd.value IS NOT NULL)
AND (wd.store_id != 0)
AND (cpe.entity_id IN
(653591, 653603, 653690, 653697, 653701, 653708, 653716, 653728, 653737, 653748, 653752, 653764, 653774,
653775, 689894, 689895, 689896, 689897, 689898, 689899, 689900, 689901, 689902, 689903, 689904, 689905,
689906)))) AS `u`
;
EXPLAIN（没有额外的索引）：
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"rows": 1485,
"filtered": 100,
"materialized": {
"query_block": {
"union_result": {
"table_name": "<union2,3>",
"access_type": "ALL",
"query_specifications": [
{
"query_block": {
"select_id": 2,
"temporary_table": {
"table": {
"table_name": "s",
"access_type": "index",
"possible_keys": ["PRIMARY"],
"key": "STORE_WEBSITE_ID",
"key_length": "2",
"used_key_parts": ["website_id"],
"rows": 4,
"filtered": 100,
"attached_condition": "s.store_id <> 0",
"using_index": true
},
"block-nl-join": {
"table": {
"table_name": "cpe",
"access_type": "range",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["entity_id"],
"rows": 27,
"filtered": 100,
"attached_condition": "cpe.entity_id in (653591,653603,653690,653697,653701,653708,653716,653728,653737,653748,653752,653764,653774,653775,689894,689895,689896,689897,689898,689899,689900,689901,689902,689903,689904,689905,689906)",
"using_index": true
},
"buffer_type": "flat",
"buffer_size": "64",
"join_type": "BNL"
},
"table": {
"table_name": "dd",
"access_type": "ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "4",
"used_key_parts": ["entity_id"],
"ref": ["magento.cpe.entity_id"],
"rows": 11,
"filtered": 38.742,
"index_condition": "dd.store_id = 0 and dd.attribute_id in (83,93,99,205,209,211,212)"
},
"table": {
"table_name": "ds",
"access_type": "eq_ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "8",
"used_key_parts": ["entity_id", "attribute_id", "store_id"],
"ref": [
"magento.cpe.entity_id",
"magento.dd.attribute_id",
"magento.s.store_id"
],
"rows": 1,
"filtered": 100,
"attached_condition": "trigcond(ds.`value` is not null or dd.`value` is not null)"
},
"table": {
"table_name": "d2d",
"access_type": "eq_ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "8",
"used_key_parts": ["entity_id", "attribute_id", "store_id"],
"ref": ["magento.cpe.entity_id", "const", "const"],
"rows": 1,
"filtered": 9.2263,
"distinct": true
},
"table": {
"table_name": "d2s",
"access_type": "eq_ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "8",
"used_key_parts": ["entity_id", "attribute_id", "store_id"],
"ref": [
"magento.d2d.entity_id",
"magento.d2d.attribute_id",
"magento.s.store_id"
],
"rows": 1,
"filtered": 100,
"attached_condition": "trigcond(coalesce(d2s.`value`,d2d.`value`) = 1) and trigcond(trigcond(d2d.entity_id is not null and d2d.attribute_id is not null))",
"distinct": true
},
"table": {
"table_name": "pis",
"access_type": "eq_ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "8",
"used_key_parts": ["entity_id", "attribute_id", "store_id"],
"ref": [
"magento.cpe.entity_id",
"magento.dd.attribute_id",
"magento.s.store_id"
],
"rows": 1,
"filtered": 100,
"attached_condition": "trigcond((pis.`value` is not null or pis.value_id is null) and ifnull(pis.`value`,coalesce(ds.`value`,dd.`value`)) is not null)",
"distinct": true
}
}
}
},
{
"query_block": {
"select_id": 3,
"operation": "UNION",
"table": {
"table_name": "cpe",
"access_type": "range",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["entity_id"],
"rows": 27,
"filtered": 100,
"attached_condition": "cpe.entity_id in (653591,653603,653690,653697,653701,653708,653716,653728,653737,653748,653752,653764,653774,653775,689894,689895,689896,689897,689898,689899,689900,689901,689902,689903,689904,689905,689906)",
"using_index": true
},
"table": {
"table_name": "wd",
"access_type": "ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "4",
"used_key_parts": ["entity_id"],
"ref": ["magento.cpe.entity_id"],
"rows": 11,
"filtered": 7.4615,
"index_condition": "wd.attribute_id in (83,93,99,205,209,211,212) and wd.store_id <> 0",
"attached_condition": "wd.`value` is not null"
},
"table": {
"table_name": "d2d",
"access_type": "eq_ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "8",
"used_key_parts": ["entity_id", "attribute_id", "store_id"],
"ref": ["magento.cpe.entity_id", "const", "const"],
"rows": 1,
"filtered": 9.2263
},
"table": {
"table_name": "d2s",
"access_type": "ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "6",
"used_key_parts": ["entity_id", "attribute_id"],
"ref": ["magento.d2d.entity_id", "magento.d2d.attribute_id"],
"rows": 1,
"filtered": 50,
"attached_condition": "trigcond(coalesce(d2s.`value`,d2d.`value`) = 1) and trigcond(d2s.store_id <> 0 and trigcond(d2d.entity_id is not null and d2d.attribute_id is not null))"
}
}
}
]
}
}
}
}
}
}
EXPLAIN（带有额外的索引）：
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"rows": 1485,
"filtered": 100,
"materialized": {
"query_block": {
"union_result": {
"table_name": "<union2,3>",
"access_type": "ALL",
"query_specifications": [
{
"query_block": {
"select_id": 2,
"temporary_table": {
"table": {
"table_name": "s",
"access_type": "index",
"possible_keys": ["PRIMARY"],
"key": "STORE_WEBSITE_ID",
"key_length": "2",
"used_key_parts": ["website_id"],
"rows": 4,
"filtered": 100,
"attached_condition": "s.store_id <> 0",
"using_index": true
},
"block-nl-join": {
"table": {
"table_name": "cpe",
"access_type": "range",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["entity_id"],
"rows": 27,
"filtered": 100,
"attached_condition": "cpe.entity_id in (653591,653603,653690,653697,653701,653708,653716,653728,653737,653748,653752,653764,653774,653775,689894,689895,689896,689897,689898,689899,689900,689901,689902,689903,689904,689905,689906)",
"using_index": true
},
"buffer_type": "flat",
"buffer_size": "64",
"join_type": "BNL"
},
"table": {
"table_name": "dd",
"access_type": "ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID",
"key_length": "4",
"used_key_parts": ["entity_id"],
"ref": ["magento.cpe.entity_id"],
"rows": 11,
"filtered": 36.51,
"attached_condition": "dd.store_id = 0 and dd.attribute_id in (83,93,99,205,209,211,212)"
},
"table": {
"table_name": "ds",
"access_type": "eq_ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "8",
"used_key_parts": ["entity_id", "attribute_id", "store_id"],
"ref": [
"magento.cpe.entity_id",
"magento.dd.attribute_id",
"magento.s.store_id"
],
"rows": 1,
"filtered": 100,
"attached_condition": "trigcond(ds.`value` is not null or dd.`value` is not null)"
},
"table": {
"table_name": "d2d",
"access_type": "eq_ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "8",
"used_key_parts": ["entity_id", "attribute_id", "store_id"],
"ref": ["magento.cpe.entity_id", "const", "const"],
"rows": 1,
"filtered": 8.6947,
"distinct": true
},
"table": {
"table_name": "d2s",
"access_type": "eq_ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "8",
"used_key_parts": ["entity_id", "attribute_id", "store_id"],
"ref": [
"magento.d2d.entity_id",
"magento.d2d.attribute_id",
"magento.s.store_id"
],
"rows": 1,
"filtered": 100,
"attached_condition": "trigcond(coalesce(d2s.`value`,d2d.`value`) = 1) and trigcond(trigcond(d2d.entity_id is not null and d2d.attribute_id is not null))",
"distinct": true
},
"table": {
"table_name": "pis",
"access_type": "eq_ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "8",
"used_key_parts": ["entity_id", "attribute_id", "store_id"],
"ref": [
"magento.cpe.entity_id",
"magento.dd.attribute_id",
"magento.s.store_id"
],
"rows": 1,
"filtered": 100,
"attached_condition": "trigcond((pis.`value` is not null or pis.value_id is null) and ifnull(pis.`value`,coalesce(ds.`value`,dd.`value`)) is not null)",
"distinct": true
}
}
}
},
{
"query_block": {
"select_id": 3,
"operation": "UNION",
"table": {
"table_name": "cpe",
"access_type": "range",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["entity_id"],
"rows": 27,
"filtered": 100,
"attached_condition": "cpe.entity_id in (653591,653603,653690,653697,653701,653708,653716,653728,653737,653748,653752,653764,653774,653775,689894,689895,689896,689897,689898,689899,689900,689901,689902,689903,689904,689905,689906)",
"using_index": true
},
"table": {
"table_name": "wd",
"access_type": "ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID",
"key_length": "4",
"used_key_parts": ["entity_id"],
"ref": ["magento.cpe.entity_id"],
"rows": 11,
"filtered": 7.0315,
"attached_condition": "wd.attribute_id in (83,93,99,205,209,211,212) and wd.`value` is not null and wd.store_id <> 0"
},
"table": {
"table_name": "d2d",
"access_type": "eq_ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "8",
"used_key_parts": ["entity_id", "attribute_id", "store_id"],
"ref": ["magento.cpe.entity_id", "const", "const"],
"rows": 1,
"filtered": 8.6947
},
"table": {
"table_name": "d2s",
"access_type": "ref",
"possible_keys": [
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
"CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
"CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
"CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
],
"key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
"key_length": "6",
"used_key_parts": ["entity_id", "attribute_id"],
"ref": ["magento.d2d.entity_id", "magento.d2d.attribute_id"],
"rows": 1,
"filtered": 48.517,
"attached_condition": "trigcond(coalesce(d2s.`value`,d2d.`value`) = 1) and trigcond(d2s.store_id <> 0 and trigcond(d2d.entity_id is not null and d2d.attribute_id is not null))"
}
}
}
]
}
}
}
}
}
}
CREATE TABLE年代：
CREATE TABLE `catalog_product_entity` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
`attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute Set ID',
`type_id` varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'Type ID',
`sku` varchar(64) DEFAULT NULL COMMENT 'SKU',
`has_options` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Has Options',
`required_options` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Required Options',
`created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Creation Time',
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Update Time',
PRIMARY KEY (`entity_id`),
KEY `CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),
KEY `CATALOG_PRODUCT_ENTITY_SKU` (`sku`)
) ENGINE=InnoDB AUTO_INCREMENT=625336 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Table'
CREATE TABLE `catalog_product_entity_int` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Store ID',
`entity_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Entity ID',
`value` int(11) DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
KEY `CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID` (`attribute_id`),
KEY `CATALOG_PRODUCT_ENTITY_INT_STORE_ID` (`store_id`),
KEY `CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE` (`attribute_id`,`store_id`,`value`),
CONSTRAINT `CATALOG_PRODUCT_ENTITY_INT_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE,
CONSTRAINT `CAT_PRD_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
CONSTRAINT `CAT_PRD_ENTT_INT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16823544 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Integer Attribute Backend Table'