SQL 调优之二索引调优

一、什么是索引

        SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了系统的性能，加快数据的查询速度，减少系统的响应时间。

        聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。
就像字段，聚集索引是连续的，a后面肯定是b，非聚集索引就不连续了，就像图书馆的某个作者的书，有可能在第1个货架上和第10个货架上。
        还有一个小知识点就是：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

二 、索引的原理

        若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍。若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值按找升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方。

        索引的存储机制：

        聚集索引就是在数据库被开辟一个物理空间存放他的排列的值，例如1-100，所以当插入数据时，他会重新排列整个整个物理空间，而非聚集索引其实可以看作是一个含有聚集索引的表，他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针。

三、创建索引的十大原则

1) 定义主键的数据列一定要建立索引。

2) 定义有外键的数据列一定要建立索引。

3) 对于经常查询的数据列最好建立索引。

4) 对于需要在指定范围内的快速或频繁查询的数据列;

5) 经常用在WHERE子句中的数据列。

6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

7) 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8) 对于定义为text、image和bit的数据类型的列不要建立索引。

9) 对于经常存取的列避免建立索引

9) 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

10) 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

四、如何创建索引

1、创建索引语法

CREATE [UNIQUE][CLUSTERED | NONCLUSTERED] INDEX index_name

ON {table_name | view_name} [WITH [index_property [,....n]]

说明：

UNIQUE: 建立唯一索引。

CLUSTERED: 建立聚集索引。

NONCLUSTERED: 建立非聚集索引。

Index_property: 索引属性。

UNIQUE索引既可以采用聚集索引结构，也可以采用非聚集索引的结构，如果不指明采用的索引结构，则SQL Server系统默认为采用非聚集索引结构。

2、删除索引语法

DROP INDEX table_name.index_name[,table_name.index_name]

说明：table_name: 索引所在的表名称。

index_name : 要删除的索引名称。

3、查询索引语法

SELECT index_name,index-type, tablespace_name, uniqueness FROM all_indexes WHERE table_name ='tablename';

eq: SELECT * FROM all_indexes WHERE table_name ='AC_REGISTERINFO';

五、索引存在的问题

        插入，删除以及更新的时候都需要维护索引，这就意味着向表中插入一行，那么就需要在索引中加入一对于这一行相匹配新值。如果索引列需要进行大量更新的话，这个维护成本就会很高。