MySQL索引

Mysql索引

索引的种类/类型：

1、 普通索引：最基本的索引，没有任何限制

2、 唯一索引：与普通索引类似，不同的是索引的列必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一

3、 主键索引：一般用primary key来约束，用于唯一标识数据表中某一条记录，不允许有空值

4、 复合索引：多个字段上建立的索引，能够加速符合查询条件的检索

Mysql索引优化规则：

1、 前导模糊查询不能使用索引

如：select * from dual where title like ’%XX’;

非前导模糊查询可以使用索引；页面搜索严谨左模糊或者全模糊，如果需要可以使用搜索引擎来解决。

2、 union、in、or都能命中索引，建议使用in

3、 负向条件查询不能使用索引，可以优化为in 查询

负向条件有：!=、<>、not in 、not exists、not like等

4、 联合索引最左侧查询原则

如：select uid,login_time from user where login_name=? and passwd=?可以建立（login_name,passwd）的联合索引。

因为业务上几乎没有passwd的单查询需求，而有很多login_name的单查询需求，所以建立(login_name,passwd)的联合索引，而不是(passwd,login_name)

l  建立联合索引的时候，区分度最高的字段在最左边

l  如果建立了(a,b)的联合索引，就不必在单独建立a的索引。同理，如果建立了（a,b,c）的索引，就没必要建立c、(a,b)的索引

l  存在非等号和等号混合的判断条件时，在建立索引时，把等号列前置。如where a>? and b=?，那么即使a的区分度更高，也必须把b放在索引的最前列

5、 范围列可以用到索引（联合索引必须是最左前缀）

l  范围条件有：<、<=、>、>=、between等

l  范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询中有两个范围列则无法全用到索引。

例：假如有联合索引（empno、title、fromdate），那么下面的SQL中empno可以用到索引，而title、fromdate则使用不到：

Select * from emp where empno<’10010’ and title=’’ and fromdate between’’ and ‘’;

6、 更新十分频繁、数据区分不高的字段上不宜建立索引。

l  更新频繁的字段建立索引会大大降低数据库性能

l  区分度不大的字段建立索引没有意义，不能有效过滤数据，性能与全表扫描类似

l  一般区分度在80%以上的时候就可以建立索引，区分度可以使用count(distinct(列名))/count(*)来计算

7、 如果有order by、group by的场景，注意索引的有序性

l  Order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。

l  例如对于语句where a=? and b=? order by c，可以建立联合索引（a,b,c）

l  如果索引中有范围查找，那么索引有序性无法使用,如where a>10 order by b；索引（a,b）无法排序

8、 建立索引的列，不允许为null

单例索引不存null值；复合索引不存全为null的值；如果 列允许为null，可能会得到“不符合预期结果”的结果集，所以，请使用not null的约束及默认值

9、 业务上具有唯一特性的字段，即使是多个字段的组合，也必须成唯一索引

10、超过三个表最好不要join

 需要join的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引

11、如果明确知道只有一条结果返回，limit 1能够提高效率

12、单表索引建议控制在5个以内

13、单索引字段数不允许超过5个

创建索引时应避免的错误观念：

①    索引越多越好，认为一个查询就需要一个索引

②   认为索引会消耗空间、严重拖慢更新和新增速度

③   过早优化，在不了解系统的情况下就开始优化