MySQL表索引

单列索引

单列索引对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条件，虽然创建了多个单列索引，但实际，数据库查询会选择使用一个最严的索引。

因为与全表扫描或只使用一个索引的速度比起来，去分析两个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是是用一个索引。

建表索引，必须仔细分析，因为太多的索引与不充分、不正确的索引对性能都毫无益处。

在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。

过多的复合索引，在有单字段索引的情况下、一般都是没有存在价值，反而还会降低数据插入、删除时的性能，特别是对频繁更新的表来说，负面影响更大。所以符合索引要谨慎，尽量用单字索引。