Mysql：如何在表上建立索引

发布于 2024-10-19 10:38:32 字数 225 浏览 2 评论 0原文

我想知道当我必须放置多个索引时，我应该将它们放在同一个“索引”下还是不同的索引下？

INDEX `field1` (`field1`)
INDEX `field2` (`field2`)

或者

INDEX `field1` (`field1`,`field2`)

有什么区别吗？

谢谢

原文

I would like to know when i have to put multiple index should i make them under the same "index" or on a different index?

INDEX `field1` (`field1`)
INDEX `field2` (`field2`)

INDEX `field1` (`field1`,`field2`)

Are there any differneces?

thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

弥繁 2024-10-26 10:38:32

有一个区别。由两列（或更多列）组成的索引是根据您指定的列的顺序构建的。仅当您搜索前 X 个字段时才可以使用它。假设您有一个索引，

exampleIndex (`a`, `b`, `c`)

它将在运行如下查询时使用：

SELECT * FROM tbl WHERE a = 1;
SELECT * FROM tbl WHERE a = 1 AND b = 2;
SELECT * FROM tbl WHERE a = 1 AND b = 2 AND c = 3;

在运行如下查询时不会使用它：

SELECT * FROM tbl WHERE b = 2;
SELECT * FROM tbl WHERE c = 3;
SELECT * FROM tbl WHERE b = 2 AND c = 3;

在第一种类型的查询中，拥有 3 字段索引将比拥有单独的索引更快，因此决定如何构建索引取决于您需要的查询类型。

There is a difference. An index made out of two columns (or more) is built according to the order of columns you specified. It can be used only when you are searching for the first X fields. Let's say you have an index

exampleIndex (`a`, `b`, `c`)

It will be used when running queries like:

SELECT * FROM tbl WHERE a = 1;
SELECT * FROM tbl WHERE a = 1 AND b = 2;
SELECT * FROM tbl WHERE a = 1 AND b = 2 AND c = 3;

It will NOT be used when running queries like:

SELECT * FROM tbl WHERE b = 2;
SELECT * FROM tbl WHERE c = 3;
SELECT * FROM tbl WHERE b = 2 AND c = 3;

On queries of the first type, having the 3 field index will be quicker than having separate indexes, so deciding how to build the indexes depends on which types of queries you are going to need.

回复收藏 0 原文