创建索引原则及SQL添加索引

一、数据库建立索引的原则

1.确定针对该表的操作是大量的查询操作还是大量的增删改操作。

2.尝试建立索引来帮助特定的查询。检查自己的sql语句，为那些频繁在where子句中出现的字段建立索引。

3.尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间，同时，复合索引也占磁盘空间。

4.对于小型的表，建立索引可能会影响性能

5.应该避免对具有较少值的字段进行索引。

6.避免选择大型数据类型的列作为索引。

A.mysql索引建立的原则

索引查询是数据库中重要的记录查询方法，要不要进入索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑，下面给出实际中的一些通用的原则：

1. 在经常用作过滤器的字段上建立索引；

2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；

3. 在不同值较少的字段上不必要建立索引，如性别字段；

4. 对于经常存取的列避免建立索引；

5. 用于联接的列（主健/外健）上建立索引；

6. 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定；

7. 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，如：含有有限数目（不是很少）唯一的列；进行大范围的查询；充分的利用索引可以减少表扫描I/0的次数，有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中，也取决于DBA的所设计的数据库结构。

B.oracle 索引建立的若干原则

我们首先要考虑的是数据量，数据量级别的不同，要考虑的问题有很大区别。几千条记录建不建索引其实都无所谓了，差个几豪秒也感觉不出来，但数据量一旦要增长到百万，千万级别，索引的重要性就体现出来了。没有索引一个查询可能要几个小时甚至几天才能出来，对数据库的影响不仅仅是查询速度的降低，在io 上的花费和对数据库连接资源的占用甚至会拖跨数据库。那么怎样建立索引，建立什么类型的索引呢，应该按照几个方面来考虑。

1．先要了解业务需求，总结出应用会按照哪几个字段来进行查询。例如一个人员查询系统可能会按照（姓名，身份证件号码，性别，住址，民族等），当然可能是几个字段的组合查询。

2．确定了第一步后要估计数据的分布。相同值有多少，是不是均匀。如姓名字段就会有大量的重复情况；性别可能只有两个值（1，2 分别代表男，女），民族会有56 个。

3．确定索引的类型。选择性高的字段建立B- 树索引最好，如果数据量太大，可考虑把索引分区，在并发情况下通常会表现很好。相当于把一棵很大的B树拆开成了多棵小树。只有几个值的字段如性别并且数据分布比较均匀，查询的平均命中率要是非常高就不需要建立索引，否则可以建立位图索引（但会影响并发）。

oracle建立索引原则

1.索引需要平衡query和DML的需要，常用于(子)查询的表应建立索引；

2.把索引建到不同的表空间中；

3.用统一的extent大小；

4.五个block的倍数或者tablespace指定的MINIMUM EXTENT的倍数；

5.创建索引考虑用NOLOGGING参数，重建索引的时候也一样；

6.创建索引时INITRANS值应该比相应的table的值高一些；

7.对常用SQL语句的where条件中的列建立唯一索引或组合索引，组合条件查询中相应的组合索引更有效；

8.对于组合索引，根据列的唯一值概率，安排索引顺序；

9.如果一个列具有很低的数据基数，并且或者可具有空值，不应作为索引列；

10.如果where语句中不得不对查询列采用函数查询，如upper函数，最好建立相应函数索引；

11.对于低基数集的列，并包含OR等逻辑运算，考虑用Bitmap索引，对于从大量行的表中返回大量的行时也可以考虑Bitmap索引；

12.避免在有大量并发DML运算的表中使用Bitmap索引；

二、使用CREATE 语句创建索引

CREATE INDEX index_name ON table_name(column_name,column_name) include(score)

普通索引

CREATE UNIQUE INDEX index_name ON table_name (column_name) ;

非空索引

CREATE PRIMARY KEY INDEX index_name ON table_name (column_name) ;

主键索引

使用ALTER TABLE语句创建索引

alter table table_name add index index_name (column_list) ;

alter table table_name add unique (column_list) ;

alter table table_name add primary key (column_list) ;

删除索引

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;