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

问题描述

$ b

• PRIMARY - 主键列？


• 您何时使用每个MySQL索引类型？ > UNIQUE - 外键？


• 索引 - ??

索引列可以提高性能吗？

解决方案

小学

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

< h3>唯一

唯一索引确保您的DBMS不接受此列的重复条目。你问外键？不！这是没有用的，因为外键是每个定义容易重复，（一对多，多对多）。

索引

额外的索引可以放在SELECTS（和JOINS）通常用于外键的列上。在很多情况下，如果外键索引，SE​​LECT（和JOIN）查询将会更快。

请注意，正如SquareCog已经阐明的那样 - 索引会更新任何修改到数据，是的，添加更多的索引可能会导致INSERT / UPDATE性能下降。如果索引没有得到更新，您将得到不同的信息，这取决于优化程序是否决定在索引或原始表上运行查询 - 这是非常不可取的情况。

<这意味着，你应该仔细评估指数的使用情况。有一件事是肯定的：未使用的指数必须避免，删除！

When do you use each MySQL index type?

• PRIMARY - Primary key columns?
• UNIQUE - Foreign keys?
• INDEX - ??

For really large tables, do indexed columns improve performance?

解决方案

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.

Unique

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

Index

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.

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