单列索引
单列索引对where条件中出现的位置无要求,都有效。
@Table(name = "tb_person", indexes = {@Index(name = "name_idx", columnList = "name")})
public class Person implements Serializable {
}
联合索引
select * from mytable where field1=? and field2=? and field3=?;
select * from tb_persion where name="Jack" and score>=60;
@Table(name = "tb_person", indexes = {
@Index(name = "name_score_idx", columnList = "name"),
@Index(name = "name_score_idx", columnList = "score")})
public class Person implements Serializable {
}
如果创建多个单列索引,很浪费资源。可以创建1个联合索引。
如果出了该联合查询外,还会出现filed2=?单独出现的条件查询,则应该把filed2放在联合索引的第一位,这样单独查询时,也无需单独建立field2列的单列索引。
什么情况适合联合索引:
A:等值查询中,查询条件a返回的条目比较多,查询条件b返回的条目比较多,而同时查询a、b返回的条目比较少,那么适合建立联合索引;
B:对于有等值查询的列和范围查询的列,等值查询的列建在前、范围查询的列建在后比较实用;
C:如果联合索引列的前置列与索引单列一致,那么单列查询可以用到索引,这样就避免了再建单列索引,因此联合索引的前置列应尽量与单列一致;设计表索引的原则:
1、表的主键、外键必须有索引;
2、数据量超过300的表应该有索引;
3、经常与其他表进行连接的表,在连接字段上应该建立索引;
4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
5、索引应该建在选择性高的字段上;
6、索引尽量创建在唯一性高的列上。唯一性不高的字段,尽量不要创建索引;
7、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
8、经常查询的列加索引;
9、排序字段上也是应该建立索引;
10、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
(1)正确选择复合索引中的主列字段,一般是选择性较好的字段;
(2)复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
(3)如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
(4)如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
(5)如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
11、频繁进行数据操作的表,不要建立太多的索引;
经常update,delete,insert的列,尽量不要使用索引;
12、删除无用的索引,避免对执行计划造成负面影响;
13、个人建议每张表的索引数不要超过3个。查询语法不当,会导致表索引无效。
正确使用查询语句参考:
会用到索引的表达式:
where id = 1
where id > 1
where id >= 1
where id < 1
where id <= 1
where id between 1 and n
where id like ’1%’
where id in(1,2,3…)
导致索引无效(不会用到索引)的表达式:
where id != 1
where person_id +1 = n
where person_id like ‘%5′
where person_id like ‘%5%’
where person_id abs(15)
数据库查询只能用到一个索引原因:
如果查询中有道多个and条件,虽然创建了多个单列索引,但实际,数据库查询会选择使用一个最严的索引。
因为与全表扫描或只使用一个索引的速度比起来,去分析两个索引二叉树更加耗费时间,所以绝大多数情况下数据库都是是用一个索引。建表索引,必须仔细分析,因为太多的索引与不充分、不正确的索引对性能都毫无益处。
在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。
过多的复合索引,在有单字段索引的情况下、一般都是没有存在价值,反而还会降低数据插入、删除时的性能,特别是对频繁更新的表来说,负面影响更大。所以符合索引要谨慎,尽量用单字索引。