SQL 入门(利用索引改善性能)

1、什么是索引

索引就是一个指针，指向表里的数据。

索引在数据库里指向数据在表里的准确物理位置。实际上，我们被引导到数据在数据库底层文件里的位置，但从表面上来看，我们是在引用一个表。

当数据库没有索引时，它所进行的操作通常被称为全表扫描。

索引通常与相应的表是分开保存的，其主要目的是提高数据检索的性能。索引的创建与删除不会影响到数据本身，但会影响数据检索的速度。

索引也会占据物理存储空间，而且可能会比表本身还大。因此在考虑数据库的存储空间时，需要考虑索引要占用的空间。

索引通常以一种树形结构保存信息，因此速度比较快。

2、CREATE INDEX 命令

CREATE INDEX INDEX_NAME ON TABLE_NAME

索引的类型

数据库里的表可以创建多种类型的索引，它们的目标是一样的：通过提高数据检索速度来改善数据库性能。

单字段索引

对某个字段的索引是索引中最简单、最常见的形式。显然，单字段索引是基于一个字段创建的，其基本语法如下所示：

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME)

提示：如果某个字段经常在WHERE子句作为单独的查询条件，它的单字段索引是最有效的。适合作为单字段索引的值有个人标识号码、序列号或系统指派的键值。

惟一索引

惟一索引用于改善性能和保证数据完整性。惟一索引不允许表里具有重复值，除此之外，它与普通索引的功能一样。其语法如下所示：

CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME)

注意：惟一索引只能用于在表里没有重复值的字段。换句话说，如果现有表已经包含被索引关键字的记录，就不能再对它创建惟一索引了。

组合索引

组合索引是基于一个表里两个或多个字段的索引。在创建组合索引时，我们要考虑性能的问题，因为字段在索引里的次序对数据检索速度有很大的影响。一般来说，最具有限制的值应该排在前面，从而得到最好的性能。但是，总是会在查询里指定的字段应该放在首位。组合索引的语法如下所示：

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN1,COLUMN2)

提示：对于经常在查询的WHERE子句里共同使用的字段，组合索引是最有效的。

隐含索引

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

何时考虑使用索引

一般来说，大多数用于表结合的字段都应该设置索引。

经常在ORDER BY 和GROUP BY 里引用的字段也应该考虑使用索引。

另外，具有大量惟一值的字段，或是在WHERE子句里会返回很小部分记录的字段，都可以考虑设置索引。这主要是为了测试或避免错误。

注意：表和索引都应该进行事先的规划。不要认为使用索引就能解决所有的性能问题，索引可能根本不会改善性能（甚至可能降低性能）而只是占据磁盘空间。

何时应该避免使用索引

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

当字段用于WHERE子句作为过滤器会返回表里的大部分记录时，该字段就不适合设置索引。举例来说，图书里的索引不会包括像the或and这样的单词。

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

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

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

警告：对于特别长的关键字创建索引时要十分谨慎，因为大量I/O开销会不可避免地降低数据库性能。

提示：索引对于提高性能大有帮助，但在有些情况下也会降低性能。我们应该避免对只包含很少不同值的字段创建索引，比如性能、州名等。

索引是否像表一样占据实际的空间？

是的。索引在数据库里占据物理空间。实际上，索引可能比所在的表更大。

如果为了让批处理工作更快地完成而删除了索引，需要多长时间才能重新创建索引？

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

全部索引都必须是惟一索引吗？

不是。惟一索引不允许存在重复值，而在表里有时是需要有重复值的。