MYSQL索引，用于什么? [英] MYSQL indexes, What for?

问题描述

可能重复:
如何使用MySQL索引列?

Possible Duplicate:
How to use MySQL index columns?

问题说明了一切， 他们是干什么的? 有人可以举个例子吗?

The question says it all, What are they for? Could someone please give an example?

推荐答案

以下是索引的优缺点

索引是MySQL更有用的功能之一. MySQL允许使用几种类型的索引，例如主键索引，唯一索引，普通索引(也称为非唯一索引"，普通索引，无约束索引)和全文本索引，这些索引当然可以提高SELECT查询的速度但是，它们确实也有一些明显的缺点. MySQL索引的优点

Indexing is one of the more useful features of MySQL. MySQL allows several types of indexes like primary key index, unique index, normal index also known as ("non-unique index", ordinary index, index without constraints") and full-text index. Of course, the indexes improve SELECT queries speed tremendously. but, they do have some considerable disadvantages as well. Advantages of MySQL Indexes

一般来讲，MySQL索引数据库具有三个优点:

Generally speaking, MySQL indexing into database gives you three advantages:

* Query optimization: Indexes make search queries much faster.
* Uniqueness: Indexes like primary key index and unique index help to avoid duplicate row data.
* Text searching: Full-text indexes in MySQL version 3.23.23, users have the opportunity to optimize searching against even large amounts of text located in any field indexed as such.

MySQL索引的缺点

Disadvantages of MySQL indexes

在列上创建索引时，MySQL还会创建一个单独的文件进行排序，并且仅包含您要对其进行排序的字段.

When an index is created on the column(s), MySQL also creates a separate file that is sorted, and contains only the field(s) you're interested in sorting on.

首先，索引占用磁盘空间.通常，空间使用并不重要，但是由于在每种可能的组合中的每一列上都创建了索引，因此索引文件的增长将比数据文件快得多.如果表的大小很大，则索引文件可能会达到操作系统的最大文件大小.

Firstly, the indexes take up disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file would grow much more quickly than the data file. In the case when a table is of large table size, the index file could reach the operating system’s maximum file size.

第二，索引减慢了诸如INSERT，UPDATE和DELETE之类的查询编写速度.因为MySQL必须在内部维护实际数据文件中插入行的指针"，所以在上述写查询的情况下，必须付出性能代价，因为每次记录更改时，索引都必须更新.但是，您可以以不会引起明显的性能下降的方式编写查询.

Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the "pointers" to the inserted rows in the actual data file, so there is a performance price to pay in case of above said writing queries because every time a record is changed, the indexes must be updated. However, you may be able to write your queries in such a way that do not cause the very noticeable performance degradation.

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