创建索引的目的就是不在进行全表扫描,而是通过搜索索引index获取需求数据
常用索引类型:
单一索引:创建只有一个列的索引,如CREATE INDEX CONCURRENTLY IF NOT EXISTS indec_name ON table_name (table_list)
复合索引:创建多个列的索引,如CREATE INDEX CONCURRENTLY IF NOT EXISTS indec_name ON table_name (table_list1,table_list2...),如果创建索引的列只有一个或者两个成为窄索引,如果大于两个则是宽索引。
两者用法的区别:
单一索引适用于查询条件比较单一,或者查询条件是OR的情况,这种情况较为特殊,后面详述
复合索引适用于查询条件较多的情况,注意不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中,所以一般使用最多的时窄索引。
比较一下单一索引和复合索引的效率
创建索引
CREATE INDEX CONCURRENTLY IF NOT EXISTS indec_name ON table_name (table_list1)
CREATE INDEX CONCURRENTLY IF NOT EXISTS indec_name ON table_name (table_list1,table_list2)
比较select * from table_name where table_list1=a,在使用两种索引情况下的效率是差不多的,如果是select * from table_name where table_list1=a and table_list2=b复合索引的效率就会高很多,这种情况被称为索引覆盖,简单说就是索引都用于查询条件,查询及结果更少,进而效率达到最高。
注意:
1.创建索引的顺序需要与查询语句条件的顺序保持一致,否则索引会不生效
如CREATE INDEX CONCURRENTLY IF NOT EXISTS indec_name ON table_name (table_list1,table_list2,table_list3)
select * from table_name where table_list3=c and table_list2=b and table_list1=a这种情况下,索引是不生效 的。
2.创建复合索引时,索引首列必须为最常用的查询条件,如果使用复合索引的非起始列作为查询条件的话,索引将不会生效
如
CREATE INDEX CONCURRENTLY IF NOT EXISTS indec_name ON table_name (table_list1,table_list2,table_list3)
select * from table_name where table_list3=c and table_list2=b。
总结:
(1)对一张表来说,如果有一个复合索引 on (table_list1,table_list2),就没有必要同时建立一个单索引 on table_list1。
(2)如果查询条件需要,可以在已有单索引 on table_list1的情况下,添加复合索引on (table_list1,table_list2),对于效率有一定的提高。
(3)同时建立多字段(包含5、6个字段)的复合索引没有特别多的好处,相对而言,建立多个窄字段(仅包含一个,或顶多2个字段)的索引可以达到更好的效率和灵活性。
5. 一定需要覆盖性查询么?
通常最好不要采用一个强调完全覆盖查询的策略。如果Select子句中的所有列都被一个非群集索引覆盖,优化程序会识别出这一点,并提供很好的性能。不过,这通常会导致索引过宽,并会过度依赖于优化程序使用该策略的可能性。通常,是用数量更多的窄索引,这对于大量查询来说可以提供更好的性能。