MySQL 索引应用及命中索引分析。

在MySQL中， 索引有两种分类方式：逻辑分类和物理分类。

按照逻辑分类，索引可分为：

主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；

唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，但是一个唯一索引只能包含一列，比如身份证号码、卡号等都可以作为唯一索引；

普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

全文索引：让搜索关键词更高效的一种索引。

按照物理分类，索引可分为：

聚集索引：一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为 NULL 的唯一索引，如果还是没有的话，就采用 Innodb 存储引擎为每行数据内置的 6 字节 ROWID 作为聚集索引。每张表只有一个聚集索引，因为聚集索引的键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引在精确查找和范围查找方面有良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增 id 充当聚集索引）；

非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同（非主键的那一列），一个表中可以拥有多个非聚集索引。

在目前用的最多的mysql的InnoDB存储引擎中，是使用B+Tree索引方法来进行索引建立的。

一、索引的优缺点

优点：

1、索引能够提高数据检索的效率，降低数据库的IO成本。

2、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性，创建唯一索引

3、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

4、加速两个表之间的连接，一般是在外键上创建索引

缺点：

1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大

3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

二、索引何时应该使用

需创建索引的情况：

1.主键，自动建立唯一索引
2.频繁作为查询的条件的字段
3.查询中与其他表关联的字段存在外键关系
4.查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
5.查询中统计或者分组字段

避免创建索引的情况：

1.数据唯一性差的字段不要使用索引
比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描。

2.频繁更新的字段不要使用索引
比如登录次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率。

3.字段不在where语句出现时不要添加索引
只有在where语句出现，mysql才会去使用索引

4.数据量少的表不要使用索引
使用了改善也不大

三、哪些sql能命中索引

1.前导模糊查询不能使用索引，如name like ‘%nowjava’

2、Union、in、or可以命中索引，建议使用in。

3、负条件查询不能使用索引，可以优化为in查询，其中负条件有！=、<>、not in、not exists、not like等

4、联合索引最左前缀原则，又叫最左侧查询，如果在（a，b，c）三个字段上建立联合索引，那么它能够加快a|（a，b）|（a，b，c）三组的查询速度。

5、建立联合查询时，区分度最高的字段在最左边

6、如果建立了（a，b）联合索引，就不必再单独建立a索引。同理，如果建立了（a，b，c）索引就不必再建立a，（a，b）索引

7、存在非等号和等号混合判断条件时，在建索引时，要把等号条件的列前置

8、范围列可以用到索引，但是范围列后面的列无法用到索引。

索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。范围条件有：<、<=、>、>=、between等。

9、把计算放到业务层而不是数据库层。在字段上计算不能命中索引，

10、强制类型转换会全表扫描，

如果phone字段是varcher类型，则下面的SQL不能命中索引。Select * from user where phone=1380000000

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

更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。

“性别”这种区分度不太大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

一般区分度在80%以上就可以建立索引。区分度可以使用count（distinct（列名））/count（*）来计算。

12、利用覆盖索引来进行查询操作，避免回表。

被查询的列，数据能从索引中取得，而不是通过定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速度查询。

13、建立索引的列不能为null，使用not null约束及默认值

14、利用延迟关联或者子查询优化超多分页场景，