[04] SQL语句优化之索引

1.索引的概念

　　数据库的索引就类似于书本的目录，索引指向查询内容所在的位置（如书中的页码），减少了磁盘I/O次数。数据库程序不必扫描整张表，直接通过索引就可以迅速找到要查找的内容。　

　　聚集索引：对于我们认识的字可以通过拼音进行查找，拼音也即是索引，索引的顺序也就是数据的物理顺序。我们把这种数据按照索引的顺序进行存储，确定表中数据的物理顺序的方式称为聚集索引。因为数据的物理顺序只有一种，所以一个表只能有一个聚集索引。聚集索引效率高，但对数据更新影响大，不适用于频繁更新的列。

　　非聚集索引：对于我们不认识的字，需要查找偏旁部首目录找到该字所在的页码，然后通过页码找到相应的字。我们把这种数据存储在一个地方，索引存储在另一个地方，索引带有一指针指向数据的存储位置的方式称为非聚集索引。聚集索引效率低，影响小。　　

　　ROWID存储了行在数据库文件中的具体位置：64位（A-Z, a-z, 0-9, +, /），ROWID由四部分组成：OOOOOOOFFFBBBBBBRRR

　　　　OOOOOO：数据对象编号（6位显示）

　　　　FFF：相关数据文件编号（3位显示）

　　　　BBBBBB：数据块编号（6位显示）

　　　　RRR：数据块中行编号（3位显示）

2.索引的优/缺点

　　（1）大大提高数据检索、分组、排序的速度

　　（2）对数据库表进行增、删、改操作需要动态维护索引需要耗费时间，数据量越大，耗时越多

　　（3）每个索引会占用一定的物理空间

　　（1）WHERE、ORDER BY子句中使用最频繁的字段

　　（2）经常被分组排序的列

　　（3）连接两个表的连接字段

　　（4）具有高选择性（相同值少）的字段

　　（5）小字段上（不要在大的文本字段甚至超长字段上建立索引）

　　（6）不会经常更新的字段

　　（7）索引字段尽量使用数字型字段，字符类型会逐个比较字符串中的每个字符，而数字只需比较一次。

　　（8）尽可能使用varchar/nvarchar代替char/nchar，因为变长字段存储空间小，效率高些。

　　（9）如果单列索引中包含空值，索引中将不存在此记录。如果复合索引的每个列都为空，索引中不存在此记录；至少有一个列不为空，此记录存在于索引中。

　　（1）若几个字段经常同时以AND方式出现在WHERE子句中，且单字段查询比较少，则考虑建立复合索引。

　　（2）复合索引的字段个数一般不要超过3个

　　（3）如果既有单字段索引，又有这几个字段的复合索引，一般可以删除复合索引

　　（4）考虑将WHERE子句中使用最频繁的字段放在复合索引的第一位。若使用频率相同，数据在物理上按某一个字段排序的，则将这个字段放在复合索引的第一位；若使用频率相同，则将最具选择性的字段排在最前面，将最不具选择性的字段排在最后面。

　　（5）复合索引的第一列作为条件才能保证系统使用该索引，且让条件中的字段顺序与索引顺序一致。

　　（1）不要对索引列进行is null, is not null判断

　　（2）不要对索引列使用!=, <>, >操作符和NOT操作　　

　　（3）不要对索引列进行函数、算术或其他表达式（如+, ||）运算

　　（4）不要对索引使用带通配符%的like操作

　　（5）显示转换数据类型。当比较不同数据类型的数据时， ORACLE自动对其进行类型转换，当字符和数值比较时, ORACLE会优先将数值类型转换成字符类型。因为内部发生的类型转换, 这个索引将不会被使用。