索引3 -索引设计的最佳实践and 索引失效

9.7 索引设计的最佳实践

首先要了解系统的使用环境，是什么系统，OLTP，OLAP。是读多，还是写多。数据库上跑少量查询要比数据库跑大量查询需要更少的索引。

1 考虑给每个表设置聚集索引-整个表就变成有顺序的

   使用聚集索引表中的数据就是按聚集索引键的顺序存在而不再以堆存放。使用聚集索引的好处是使得数据按照聚集索引键的顺序存放，并使得后插入的元素依然保持这个顺序。

每个表都应该有主键，因此，每一个表都应该有一个或多个索引，让其中的一个索引成为聚集索引。聚集索引本身并不会使得表上多了一个索引，而是让表的结构更好的组织。

    选择聚集索引键时，聚集索引键应该唯一，短和尽量不需要改动。

2表设置主键

    虽然SQL Server并不强制要求设置主键。但一个没有主键的表无论在OLTP还是OLAP环境下都是一件危险的事，因为没有主键就不能保证每行是唯一的。这时你就无法知道同一行数据是否在表中存在两条，尤其是在你还没有足够的信息去分析这点时。

    尽管SQL Server不强制要求设置主键，但主键是关系数据库的一个关键理论。如果没有主键约束，那么与之关联的唯一索引或是连接操作就有可能产生意料之外的性能问题。

 

3 使用填充因子来减少页分裂

考虑使用填充因子来减少页分裂

加入表中的数据已经达到了页所能容纳的最大值。那么再插入数据就会导致页分裂了。因此重建索引时可以使用填充因子，如果数据库写大于读的话，设置填充因子为75，如果读写大致相等的话，设置填充因子为90到95.

4 表的主键、外键必须有索引

将外键设置为聚集索引的最左列就是将表中的数据按照这列的值进行汇总和组织，这也是查询所需。比如说你用信用卡消费这个行为是和卡关联最强的的，而不是和你刷卡的商场以及处理这笔消费的银行。则将信用卡号作为消费记录表中聚集索引的最左列，使得所有同一张卡的消费信息就会存在连续的页中。

当然了，你还需要另外一个很少变动的列和这个信用卡号列组合起来保证聚集索引键的唯一性。

5 建立索引的原则

A 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引;

B 经常与其他表进行连接的表，在连接字段上应该建立索引;

C 索引应该建在选择性高的字段上;

D 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

E 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

F 复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是，则可以建立复合索引;否则考虑单字段索引;

G 频繁进行数据操作的表，不要建立太多的索引；

H 删除无用的索引，避免对执行计划造成负面影响;

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

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

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

 

9.8 索引失效

1 查询谓词没有使用索引的主要边界,换句话说就是select *，可能会导致不走索引。

2、表的数据库小或者需要选择大部分数据，不走索引

3 字符型字段为数字时在where条件里不添加引号。

4、！=或者<>(不等于），not in ,not exist导致不走索引，

5、索引列上有函数运算或者运算，导致不走索引

6、隐式转换导致不走索引。SELECT * FROM T WHERE Y = 5，SELECT * FROM T WHERE TO_NUMBER(Y) = 5,这个时候也是有可能用不到索引的。

7.建立组合索引，但查询谓词并未使用组合索引的第一列，单独引用复合索引里非第一位置的索引列。

8、like '%liu' 百分号在前

9.条件中用or，即使其中有条件带索引，也不会使用索引查询，可以使用union

 

索引失效原因, 全值匹配我最爱, 最佳左前缀法则, 不要在索引列上做任何操作, 范围条件后列上索引失效, 减少使用select *,使用不等于（！= 或者<>）不能使用索引, 使用 is null 或者 is not null 也不能使用索引, like 已通配符开头（%abc）导致索引失效 , .少用or