当前位置：文江博客话题详情

MySQL：创建 3 个字段的复合索引，还是创建 3 个单独的索引？

发布于 2024-10-02 08:03:33 字数 110 浏览 2 评论 0原文

我有一个 MySQL 表，其中包含时间戳、类型和 user_id 等属性。
所有这些都可以搜索和/或排序。
是为每个索引创建一个索引更好，还是为所有三个索引创建一个复合索引，或者两者都创建？

原文

分享到QQ

分享到微博

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

发布评论

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

无妨# 2024-10-09 08:03:33

如果您要单独对这些字段执行搜索，您可能需要单独的索引来使查询运行得更快。

如果您有这样的索引：

mysql> create index my_idx on my_table(tstamp, user_id, type);

并且您的查询是：

mysql> select * from my_table where type = 'A';

那么 my_idx 对您的查询不会有太大帮助，MySQL 最终将进行全表扫描来解决它。

If you are going to perform searches on those fields separately, you will probably need separate indexes to make your queries run faster.

If you have an index like this:

mysql> create index my_idx on my_table(tstamp, user_id, type);

And you query is:

mysql> select * from my_table where type = 'A';

Then my_idx won't be that helpful for your query and MySQL will end up doing a full table scan to resolve it.

回复收藏 0 原文

神妖 2024-10-09 08:03:33

巴勃罗的答案是正确的，但也许您不会意识到复合索引可能是合理的。

您可以拥有多个索引，并且拥有 idx1(tstamp, user_id) 并不排除您拥有 indx2(tstamp, type) 或 idx1reverse(user_id, tstamp)< /code> 等等...

复合索引在涵盖查询中的所有条件时最有用，因此您建议的索引将最有用。

SELECT * FROM my_table WHERE tstamp = @ts1 AND user_id = @uid AND type = @type

如果您想提高此类查询的性能，您可以考虑添加复合索引指数。

索引的缺点是它会减慢所有更新操作的速度。然而，大多数通用应用程序会执行更多的选择然后更新（无论是在事务方面，即语句数量，还是特别是在受影响/检索的记录方面），同时更能容忍较慢的更新（用户主要判断更新的速度）系统不是根据需要更新记录的时间，而是根据需要检索记录的时间；同样，YMMV 并且有些应用程序不遵守此类规则）。

最好的情况是，如果您有某种方法来测试典型工作负载方面的数据库性能（创建一些典型的 SQL 脚本；独立且可重复，或者在应用程序级别创建单元测试），然后您可以客观地调整数据库。

编辑
还要认识到可以添加和删除索引，而不会影响系统的功能。因此，您可以稍后在系统的实际使用过程中调整索引 - 通常您会收集并分析慢速 SQL 查询，以查找可以从添加索引中受益的条件。

Pablo's answer is correct, but maybe you'll fail to realize that a compound index might be justified.

You can have multiple indexes and having idx1(tstamp, user_id) does not exclude you from having indx2(tstamp, type) or idx1reverse(user_id, tstamp) and so on...

Compound indexes are most useful when they cover all of the conditions in your query, so the index you propose will be most useful for

SELECT * FROM my_table WHERE tstamp = @ts1 AND user_id = @uid AND type = @type

If you want to improve performance of such queries you can consider adding a composite index.

The downside of the indexes is that it slows down all update operations. However, most general applications do many more selects then updates (both in terms transactions i.e. number of statements and especially in terms of of records affected/retrieved) and at the same time are much more tolerant of slower updates (users mostly judge the speed of the system not by the time it is necessary to update a record, but by the time necessary to retrieve records; again YMMV and there are applications that don't play by such rules).

The best would be if you had some way to test the database performance in terms of typical workloads (create some typical SQL scripts; independent and repeatable, or create unit tests at the application level) and then you can objectively tune your database.

EDIT
Also realize that indexes can be added and dropped without affecting the system in terms of functionality. Therefore, you can tune your indexes later, during actual usage of the system - and normally you would collect and profile the slow SQL queries looking for conditions that could benefit from adding indexes.

回复收藏 0 原文

~没有更多了~