MySQL - 多列索引

Question

我正在学习MySQL索引，发现索引应该应用于SELECT查询的WHERE子句中命名的任何列。

然后我发现了Multiple Column Index vs Multiple Indexes。

首先，我想知道什么是多列索引。我从Joomla中找到了下面的代码，这是多列索引吗？

CREATE TABLE `extensions` (
    `extension_id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `element` VARCHAR(100) NOT NULL,
    `folder` VARCHAR(100) NOT NULL,
    `client_id` TINYINT(3) NOT NULL,
    ... ...
    PRIMARY KEY (`extension_id`),

    // does code below is multiple column index?

    INDEX `element_clientid` (`element`, `client_id`),
    INDEX `element_folder_clientid` (`element`, `folder`, `client_id`),
    INDEX `extension` (`type`, `element`, `folder`, `client_id`)
)

第二个问题，如果我认为一个多列索引用于一个SELECT，那么我的理解是否正确？

SELECT column_x WHERE element=y AND clinet_id=y; // index: element_clientid

SELECT ex.col_a, tb.col_b
    FROM extensions ex
    LEFT JOIN table2 tb
    ON (ex.ext_id = tb.ext_id)
    WHERE ex.element=x AND ex.folder=y AND ex.client_id=z; // index: element_folder_clientid

- qaharmdz

Answer 1

索引的一般经验法则是将其添加到用于 WHERE 或 JOIN 子句中的任何字段。

话虽如此，您还可以进行一些优化。如果您知道某个特定字段组合是在特定表格的 WHERE 上唯一使用的，则可以仅在这些字段上创建单个多字段键，例如：

INDEX (field1, field2, field5)

对比

INDEX (field1),
INDEX (field2),
INDEX (field5)

在许多情况下，多字段索引比扫描多个索引更有效。缺点是只有当相关字段实际用于 WHERE 子句时，才能使用多字段索引。

对于您的示例查询，由于element和field_id出现在所有三个索引中，最好将它们拆分为自己专用的索引。如果这些是可更改字段，则最好将它们保留在自己的专用索引中。例如，如果您需要批量更改field_id，则数据库必须更新 3 个不同的索引，而不是仅更新一个专用的索引。

但最终还是要进行基准测试-使用各种索引设置测试特定设置，并查看哪种性能最佳。经验法则很方便，但并非总是适用。

- Marc B

---

是的，我正在进行一些基于field1的测试排序，并且其他字段会自动索引。但是我不确定如何独立地拆分元素和field_id。 - qaharmdz