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

Answer 1

索引的一般经验法则是在 WHERE 或 JOIN 子句中使用的任何字段上添加一个索引。

也就是说，您可以进行一些优化。如果您知道特定的字段组合是唯一会在特定表的 WHERE 中使用的字段，那么您可以只在这些字段上创建一个多字段键，例如

INDEX (field1, field2, field5)

对比

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

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

对于您的示例查询，由于 element 和 field_id 在所有三个索引中，您最好将它们拆分到自己的专用索引中。如果这些是可变字段，那么最好将其保留在自己的专用索引中。例如如果您必须批量更改 field_id，数据库必须更新 3 个不同的索引，v.s.只更新一个专用的。

但这一切都归结为基准测试 - 使用各种索引设置测试您的特定设置，看看哪个表现最好。经验法则很方便，但并非 100% 有效。