什么时候应该使用唯一约束而不是唯一索引？

Question

jacek.ciach

Asked: 2022-09-25 01:22:39 +0800 CST2022-09-25 01:22:39 +0800 CST 2022-09-25 01:22:39 +0800 CST

冗余索引可以提高 SELECT 性能吗？

772

添加冗余索引是否有可能提高查询的性能？几天前我遇到过这样的案例，从那以后我一直在摸不着头脑：怎么办？

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'

2 个回答

Voted

ypercubeᵀᴹ · Answer 1 · 2022-09-25T08:08:50+08:00

The MariaDB 10.4 InnoDB table magic has an index on (a, b, c), the columns are INT, the PK is not part of this index.
...
A developer added a new index in the magic table on just (a) (which is a redundant one) and then, magic, the SELECT got substantial performance boost.

The index on (a) is not redundant.

Why the index (a) on an existing covering index (a,b,c) is not redundant? As far as I know the multicolumn index covers the left one. And it is mentioned on MariaDB Redundancy? That is, what if you have both of these: INDEX(a), INDEX(a,b)? Answer: Redundanccy costs something on INSERTs; it is rarely useful for SELECTs. Is this case one of those rarely cases when Redundancy index is useful on Selects?

The reason that is not redundant is related to these two facts:

The MariaDB 10.4 InnoDB table magic has an index on (a, b, c), the columns are INT, the PK is not part of this index.

The detail that is relevant here is that all InnoDB tables are clustered, i.e. rows are stored in an order defined by the clustering index. The clustering index is always the PK if there is one, otherwise the first UNIQUE index with non-nullable columns - and if the table has none of those, a hidden 6-byte index is created for this purpose.

Also, this clustering index is attached in every other (secondary) index.

So, going to your case - and as you assert the PK is not part of this index - let's say that the PK is based on some column: (magic_id). Your two indexes (a, b, c) and (a) are essentially and other the hood and for all purpose really (a, b, c, magic_id) and (a, magic_id).

Now you can see that if a query really needs just the a column values, the indexes are redundant, any of the two will find the values of a and in the same order.

But consider a query like:

SELECT a FROM magic ORDER BY a, magic_id ;

We can find the a and magic_id values from either index but only the second index on (a) will get the a values in the wanted order. So the second index is better for this query.

This example is likely not relevant to your case but we'll need the CREATE TABLE statements and the EXPLAIN plans to be sure. The example is certainly one case that MySQL/MariaDB developers had in mind when they added that "it is rarely useful for SELECTs" in the documentation.

There is however another small (or big!) catch, which is not due to InnoDB clustering. If the size of columns b or c are large, the size of the two indexes will be much different. In all cases - that I have encountered - that a query needs just the a column values, the optimizer will pick the index on (a) rather than the index on (a, b, c) because it knows that it will need to read much less blocks from disk/memory.

And this is likely another case that MySQL/MariaDB developers had in mind when they added that "it is rarely useful for SELECTs" in the documentation.

For your scenario, we can't be sure without the asked details (CREATE TABLE statements including indexes plus EXPLAIN plans) but I think it likely falls into the second case (small index size). The query has this join between M1 and O:

from ...
  left join magic M1 on ...
  ...
  left join other O on O.a = M1.a

and it's possible that some plan will need just the values of magic.a for the join with O and thus be faster with the smaller index.

Rick James · Answer 2 · 2022-09-25T10:05:07+08:00

Rick James

2022-09-25T10:05:07+08:002022-09-25T10:05:07+08:00

Assuming each table has PRIMARY KEY(id) (which is unclear from the phrasing in the question), you effectively added

INDEX(a, id)

Meanwhile,

left join other O on O.a = M1.a

turns into

INNER join other O on O.a = M1.a

because of two things: the explicit WHERE O.a IN ... and the fact that the Optimizer can transfer that to M1.a.

Next, since (apparently) only O and M1 are in the WHERE clause, the Optimizer will start with M1 (and turn it into INNER). But now INDEX(a, id) is optimal because it needs to fetch things from M1 before going on to the rest or the tables. M2, on the other hand, will like the composite (a,b,c).

To provide more details and, I hope, confirm what I just said, please provide

EXPLAIN FORMAT=JSON SELECT ...

And please provide the CREATE TABLEs and the full SELECT.

0

冗余索引可以提高 SELECT 性能吗？

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

冗余索引可以提高 SELECT 性能吗？

2 个回答

相关问题