SQL优化之添加索引的时机与场景

• 数据库建立索引的原则 
  • 确定针对该表的操作是大量的查询操作还是大量的增删改操作。 
  • 尝试建立索引来帮助特定的查询。检查自己的sql语句，为那些频繁在where子句中出现的字段建立索引。 
  • 尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间，同时，复合索引也占磁盘空间。 
  • 对于小型的表，建立索引可能会影响性能 。
  • 应该避免对具有较少值的字段进行索引。 
  • 避免选择大型数据类型的列作为索引。 
  • 索引需要平衡query和DML的需要，常用于(子)查询的表应建立索引； 
  • 把索引建到不同的表空间中。
  • 用统一的字段字节extent大小；。
  • 创建索引考虑用NOLOGGING参数，重建索引的时候也一样； 
  • 创建索引时INITRANS值应该比相应的table的值高一些； 
  • 对常用SQL语句的where条件中的列建立唯一索引或组合索引，组合条件查询中相应的组合索引更有效； 
  • 对于组合索引，根据列的唯一值概率，安排索引顺序； 
  • 如果一个列具有很低的数据基数，并且或者可具有空值，不应作为索引列； 
  • 如果where语句中不得不对查询列采用函数查询，如upper函数，最好建立相应函数索引； 
  • 对于低基数集的列，并包含OR等逻辑运算，考虑用Bitmap索引，对于从大量行的表中返回大量的行时也可以考虑Bitmap索引； 
  • 避免在有大量并发DML运算的表中使用Bitmap索引；
• 实际中的一些常用的原则： 
  • 在经常用作过滤器的字段上建立索引。
  • 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引。
  • 在不同值较少的字段上不必要建立索引，如性别字段。
  • 对于经常存取的列避免建立索引。
  • 用于联接的列（主健/外健）上建立索引。
  • 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定。
  • 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，如：含有有限数目（不是很少）唯一的列；进行大范围的查询；充分的利用索引可以减少表扫描I/0的次数，有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中，也取决于DBA的所设计的数据库结构。 
• 上百万数据表怎样建立索引，建立什么类型的索引呢，应该按照几个方面来考虑。 
  • 先要了解业务需求，总结出应用会按照哪几个字段来进行查询。例如一个人员查询系统可能会按照（姓名，身份证件号码，性别，住址，民族等），当然可能是几个字段的组合查询。 
  • 确定了第一步后要估计数据的分布。相同值有多少，是不是均匀。如姓名字段就会有大量的重复情况；性别可能只有两个值（1，2 分别代表男，女），民族会有56 个。 
  • 确定索引的类型。选择性高的字段建立B- 树索引最好，如果数据量太大，可考虑把索引分区，在并发情况下通常会表现很好。相当于把一棵很大的B树拆开成了多棵小树。只有几个值的字段如性别并且数据分布比较均匀，查询的平均命中率要是非常高就不需要建立索引，否则可以建立位图索引（但会影响并发）。 
• 使用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 ;

• 如何解决了您的问题，欢迎关注我！还希望来JAVA WEB开发交流群：958923746，有问题欢迎共享，共同提升！