如何使用 MySQL 索引列? [英] How to use MySQL index columns?

问题描述

你什么时候使用每种 MySQL 索引类型?

When do you use each MySQL index type?

• PRIMARY - 主键列?
• 唯一 - 外键?
• 索引 - ??

对于非常大的表，索引列会提高性能吗?

For really large tables, do indexed columns improve performance?

推荐答案

Primary

主键 - 顾名思义 - 表的主键，应该是通常用于选择该表行的列.主键始终是唯一键(唯一标识符).主键不限于一列，例如在引用表(多对多)中，主键包含两列或更多列通常是有意义的.

Primary

The primary key is - as the name suggests - the main key of a table and should be a column which is commonly used to select the rows of this table. The primary key is always a unique key (unique identifier). The primary key is not limited to one column, for example in reference tables (many-to-many) it often makes sense to have a primary key including two or more columns.

唯一索引可确保您的 DBMS 不接受此列的重复条目.你问'外键?不！这不会有用，因为外键根据定义容易重复，(一对多，多对多).

A unique index makes sure your DBMS doesn't accept duplicate entries for this column. You ask 'Foreign keys?' NO! That would not be useful since foreign keys are per definition prone to be duplicates, (one-to-many, many-to-many).

附加索引可以放置在经常用于 SELECTS(和 JOINS)的列上，外键通常就是这种情况.在许多情况下，如果对外键进行索引，SE​​LECT(和 JOIN)查询会更快.

Additional indexes can be placed on columns which are often used for SELECTS (and JOINS) which is often the case for foreign keys. In many cases SELECT (and JOIN) queries will be faster, if the foreign keys are indexed.

但是请注意 - 正如 SquareCog 所澄清的那样 - 索引会根据对数据的任何修改进行更新，所以是的，添加更多索引会导致 INSERT/UPDATE 性能下降.如果索引没有得到更新，您将获得不同的信息，具体取决于优化器是决定对索引还是原始表运行查询——这是一种非常不希望的情况.

Note however that - as SquareCog has clarified - Indexes get updated on any modifications to the data, so yes, adding more indexes can lead to degradation in INSERT/UPDATE performance. If indexes didn't get updated, you would get different information depending on whether the optimizer decided to run your query on an index or the raw table -- a highly undesirable situation.

这意味着，您应该仔细评估索引的使用情况.在此基础上可以确定一件事:必须避免使用未使用的索引.已删除！

This means, you should carefully assess the usage of indices. One thing is sure on the basis of that: Unused indices have to be avoided, resp. removed!

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