mysql索引的理解

概述

       我们在使用MySQL的时候最经常能想到的优化方案可能就是加索引了吧，在表数据量很大的时候，添加索引可以帮我们极大的提升数据查询效率，那么索引是什么？你对MySQL的索引真的了解吗？

1、MySQL存储引擎

提到索引的类型就要先提到MySQL的存储引擎了，我们经常用到的会是myisam和innodb了，那么他们有什么区别呢，我们打开MySQL在磁盘上的存储文件会发现myisam是由表结构文件*.frm，数据文件*.myd和索引文件*.myi三部分组成的，
他的数据文件和索引文件是分开的，也就是我们说的非聚集索引，而innodb是只有表结构文件*.frm和数据文件*.idb组成，他的数据和索引是存储在一起的，也就是聚集索引；还有就是myisam不支持事务，
而innodb支持事务，myisam只支持表锁，innodb支持表锁和行锁（默认），因此在实际使用中如果是读取的操作比较多，可是使用myisam，其他大多数场景都是使用的innodb了，目前mysql默认的也都是innodb存储引擎了。

2、索引是什么

        聚集索引也就是数据和文件是存储在一起的，在数据文件中直接存储的就是根据索引字段排好序的B+tree结构的数据，我们如果是根据主键查询的话直接就可以快速的定位到B+tree中主键字段对应的叶子节点然后获取数据，如果是非主键索引的话就要先找到非主键字段的叶子节点（非主键的叶子节点并没有储存数据），然后再链接到主键的节点从而找到数据，效率相对于主键查找要慢一点，因为聚集索引的数据是按照索引排好序存储的，因此使用的时候必须要有主键或者唯一约束，如果没有的话，系统会帮我们创建一个默认的rowNum作为唯一约束来存储数据；再来说说非聚集索引吧，
非聚集索引就是数据和索引是分开储存的，索引中b+tree的叶子节点并没有存储数据，而是存的地址引用，具体的数据是在另外一个数据文件中存储的，查询数据时根据索引查询到叶子节点中的地址，然后再根据地址从数据文件中查询到对应的数据返回，与聚集索引相比，因为数据是分开存储的，因此就不需要一定要有主键或者唯一约束了，对于主键或者是其他索引字段，在定位到叶子节点后通过地址直接就可以查到数据了，查询数据的效率是一样的。

3、什么时候创建索引

       我们创建索引的时候，一般会有单一索引和复合索引，其实单一索引也可以算作是只有一个字段的复合索引，正如大家都知道的那样，我们创建索引都会针对于经常使用的查询条件、排序、分组字段进行创建，索引也不是越多越好， 对于使用比较少的字段就没必要创建了，每个索引都需要在文件中增加一个数据结构，不仅会占用磁盘空间，在对数据添加删除修改时还需要去维护索引，都是有成本的。索引还有个非常重要的原则就是最左前缀原则，例如有A,B,C三个字段
我们创建复合索引(A,B,C),那么在使用的时候，如果是where A=‘1’ and B=‘2’ and C='3’来使用时完全可以用到索引的，但如果是where A=‘1’ and C=‘3’ 那么就只会用到字段A这一部分索引了，那要如果是where B=‘2’ and C=‘3’ 那么索引就会失效了，造成全表扫描。其实索引失效还有很多种情况的，我们接下来分析。

4、索引使用情况分析

通常是用explain来分析sql的执行计划的：

mysql数据库索引文件在哪 mysql的索引文件_mysql

  1. id:表示需要查询序列号，如果存在子查询的话就会有递增的多个，id越大的会越先执行，如果id相同的 话按照从上到下的顺序执行
  2. select_type：表示查询类型：
  • SIMPLE：表示简单的查询，
  • PRIMARY：如果包含子查询的话，外层的查询就会被标识为PRIMARY，
  • UNION：联合查询后面的SELECT语句，
  • DEPENDENT UNION：和union类似，都是存在于子查询中，如果联合查询是存在于子查询中，查询的结果会影响外层的结果时，会出现DEPENDENT UNION
  • union result ：union联合查询的结果，包含素有被union的所有查询结果
  • SUBQUERY：子查询中的第一个select 语句
  • DERIVED 派生表的子查询，from 后面子句中的子查询
  1. table:显示查询的表名，也有可能是查询结果的简称
  2. type:这个是最重要的一列结果了，显示的是对表的访问方式
  • ALL:全表扫描，是最差的结果了
  • index：扫描整个索引树,在建立了复合索引，并且查询条件没有使用到索引，查询结果中只包含索引字段的时候会使用index，比ALL的全表扫描要好一点
  • range ：在使用索引查询一定范围的数据的时候，会使用range
  • ref ：作为查询条件的索引列的值是一个常量的时候，查询效率是比较高了
  • eq_ref : 和ref类似，区别在于eq_ref是关联的字段是唯一索引列或者主键，最多会有一条结果返回，算是使用索引最好的结果了
  • const：作为查询条件的列是唯一索引或者是主键的时候，因为只会返回一条结果，mysql优化器就能将该查询转换为常量，效率是非常快的
  • system：system是const的一种，被查询的表只有一条记录满足的时候就会是system了，例如把包含const的查询的结果作为表进行查询

5、索引失效的情况

结合mysql索引的结构，我们就会发现它就是一个帮我们吧数据排好序的树状结构，我们正常使用的时候就会按照排好序去取数据，是很快的，但是如果下面几种情况就会破坏索引的规则了，也就是索引会失效

mysql数据库索引文件在哪 mysql的索引文件_java_02

  • 在查询条件中使用or，根据索引的储存结构就会发现，如果是or的前后都是索引字段的话，是可以使用索引的，否则就会索引失效，全表扫描了
  • 在索引列上使用函数计算，在数据的储存结构中是按照索引字段的值作为B+tree的节点的，如果进行计算的话，就不是索引结构中的储存的值了，自然也就失效了，还有一种情况就是索引字段值得类型不匹配，会导致查询的时候
  • mysql做隐式的转换，也是会失效的
  • 使用like，在使用like的时候，如果是%在后面的话，是可以在索引树匹配到部分数据的，可以使用到索引，如果是%在前面，那么就会索引失效了
  • 还有就是我们有种说法是使用is null,is not null 或者！=null的情况会索引失效，其实这种说法是不正确的
  • 最后再说一下覆盖索引，覆盖索引就是需要查询的结果可以直接从索引结构的叶子节点上取到，不需要再通过主键索引二次查找了，如果我们查询的结果字段刚好是查询条件的索引字段的话，就会用到覆盖索引，查询效率是非常高的，因此返回结果尽量少写*，使用覆盖索引。