MySQL-多列索引 [英] MySQL - 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`)
)

第二个问题，如果认为在一个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.

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

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.

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

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屋！