你的位置:首页 > 数据库

[数据库][04] SQL语句优化之索引


1.索引的概念

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

  ROWID存储了行在数据库文件中的具体位置:64位(A-Z, a-z, 0-9, +, /)如AAAc0dAAZAAEqybAAB。

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

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

2.索引的优/缺点

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

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

  (3)每个索引会占用一定的物理空间

3.索引字段选择注意

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

  (2)经常被分组排序的列

  (3)连接两个表的连接字段

  (4)具有高选择性(相同值少)的字段

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

  (6)不会经常更新的字段

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

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

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

4.复合索引建立

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

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

  (3)如果既有单字段索引,又有这几个字段的复合索引,一般可以删除复合索引

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

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

5.索引操作注意

  (1)不要对索引列进行is null, is not null判断

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

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

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

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