数据库索引

什么是索引？

简单来说，索引就是一个指针，指向表里的数据。打个比喻：数据库里的索引与图书中的索引非常相似。

索引的工作原理、分类及应用：

1.工作原理：

在索引创建之后，它记录与被索引字段相关联的位置值。当表里添加新的数据时，索引里也会添加新项。当数据库执行查询时，如果where条件里指定的字段已经设置了索引值，数据库会首先在索引里搜索where子句里指定的值。如果在索引里找到了这个值，索引就可以返回被搜索数据在表里的实际位置。

2.索引的分类

2.1  单字段索引

单字段索引是索引里最简单、最常见的索引类型。单字段索引是基于一个字段创建的。基本语法如下：

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME)

如：create index index_id on persons(id)

其中index_id为索引的名字    persons 为表名   id为建立索引的表字段

2.2 惟一索引

惟一索引用来改善性能和保证数据完整性。惟一索引不允许表里具有重复值。基本语法如下：

CREATE UNIQUE INDEX NAME_IDX ON EMPLOYEE_TBL(LAST_NAME)

如：create unique index uni_id on employees(id)

其中：unique关键字用来表示创建的是惟一索引

uni_id表示索引的名字

employees是表名

id是表字段

惟一索引的使用场合：如居民的身份证号码，公司员工的工号等。

2.3 组合索引

组合索引是基于一个表里两个或多个字段的索引。在创建组合索引时，要考虑性能问题。因为字段在索引里的次序对数据检索速度有很大的影响。组合索引的创建语法如下：

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN1,COLUMN2,..)

创建在组合索引里的字段是因为我们认为这两个字段经常会出现在where条件联合条件里。

2.4 隐含索引

隐含索引是数据库服务程序在创建对象时自动创建的。比如，数据库会给主键约束和唯一性约束自动创建主键。

3.索引的使用

经常在group by 或sort by 里引用的字段也应该考虑设置索引。

具有大量惟一值的字段，或是在where子句里会返回很小部分记录的字段，都可以考虑设置索引。

4.不适合用索引的场景

索引不应该用于小规模的表。

当字段用于where子句作为过滤器会返回表里的大部分记录时，该字段就不适合设置索引。

经常批量更新的表可以具有索引，但批量操作的性能会由于索引而降低。对于经常会被加载或批量操作的表来说，可以在执行批量操作之前去除索引，在完成操作之后再重新创建索引。这是因为当表里插入数据时，索引也会被更新，从而增加额外的开销。

不应该对包含大量NULL值的字段设置索引。

经常被操作的字段不应该设置索引，因为对索引的维护会变更很繁重。

5.删除索引

删除索引的方法非常简单，具体语法如下：

DROP INDEX INDEX_NAME;

注：Mysql使用ALTER TABLE命令来删除索引。

删除索引的最常见原因是尝试改善性能。在删除索引后，还可以重新创建索引。有时重建索引是为了减少碎片。

小结：

索引可以用于改善查询和事务的整体性能。数据库索引可以迅速地从表里引用特定的数据。

索引跟表一样占据实际的空间。实际上，索引可能比所在的表更大。

重建索引的时间取决于多个因素，如索引的大小、CPU利用率和计算机性能。