MySQL初学者 - 多列索引 [英] MySQL beginner - multiple column index

问题描述

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

I'm learning MySQL index and found that index should be applied to any column named in the WHERE clause of a SELECT query.

然后我找到多列索引与多个索引。

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

First Q, I was wondering what is multiple column index. I found code bellow from Joomla, is this Multiple Column Index?

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`)
)

第二个Q，如果我认为在一个SELECT上使用了一个多列索引，我是否正确？

Second Q, am I correct if thinking that one Multiple Column Index is used on one 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

推荐答案

索引的一般经验法则是将一个打到一个 WHERE 或 JOIN 子句中使用的任何字段。

General rule of thumb for indexes is to slap one onto any field used in a WHERE or JOIN clause.

话虽如此，你可以做一些优化。如果您知道某个字段组合是唯一一个将在特定表的WHERE中使用的字段，那么您可以在这些字段上创建单个多字段键，例如

That being said, there are some optimizations you can do. If you KNOW that a certain combination of fields are the only one that will ever be used in WHERE on a particular table, then you can create a single multi-field key on just those fields, e.g.

INDEX (field1, field2, field5)

vs

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

在许多情况下，多字段索引可以更有效率扫描多个索引。缺点是多字段索引仅在有问题的字段实际用于WHERE子句时才可用。

A multi-field index can be more efficient in many cases, v.s having to scan multiple indexes. The downside is that the multi-field index is only usable if the fields in question are actually used in a WHERE clause.

使用示例查询，因为元素和 field_id 在所有三个索引中，最好将它们拆分为自己的专用索引。如果这些是可更改的字段，那么最好将它保留为自己的专用索引。例如如果您必须批量更改 field_id ，则数据库必须更新3个不同的索引，v。只更新一个专用的。

With your sample queries, since element and field_id are in all three indexes, you might be better off splitting them off into their own dedicated index. If these are changeable fields, then it's better to keep it their own dedicated index. e.g. if you ever have to change field_id in bulk, the DB has to update 3 different indexes, v.s. updating just one dedicated one.

但这一切都归结为基准测试 - 用各种索引设置测试你的特定设置，看看哪个表现最好。拇指规则很方便，但100％的时间不起作用。

But it all comes down to benchmarking - test your particular setup with various index setups and see which performs best. Rules of thumbs are handy, but don't work 100% of the time.

这篇关于MySQL初学者 - 多列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！