MySQL索引

索引介绍

随着互联网的发展，用户量越来越多，也就意味着数据量越来越多，所以在海量的数据中如何获取自己需要的数据就显得非常的重要。这就需要我们去了解和使用索引了。索引也是我们平时在开发过程中，一个非常要注意的点，一个好的索引可以大幅度的提高系统的性能，相反的如果创建的索引不合理，可能会让整个系统都崩掉。索引就是将数据进行有序的排列，相当于给数据加上一个目录，正常的索引可以处理千万级别的数据。

索引的数据结构

索引的数据结构有很多，像红黑树、B树，B+树，Hash等等。我们在日常开发中，经常使用的索引的类型是B+树。我们为什么选择这个呢，有以下的原因。

1. 和Hash对比，虽然Hash能很快读的获取数据，但是如果面对范围查询的话，就显得不足了。
2. 和B树对比，B+树的所有数据都存在叶子节点，非叶子节点没有数据，叶子节点和叶子节点之间还有指针关联，能够进行范围查询。
3. 数据库的存储引擎有很多，我们经常使用的就是InnoDB引擎，

所以我们现在使用的索引的类型就是B+树，我们接着下的讲解都是以B+树为例。对于索引的一切问题，都是以B+树为根本的，所以脑海中只要有一个B+树的结构，很多索引的问题也就迎刃而解了。

主键索引： 叶子节点保存的全部的数据

二级索引：叶子节点保存的是主键，当叶子节点有重复的时候，相同的节点会有多个，但是对应的id是不同的。

联合索引：下图为联合主键，联合主键叶子节点不会出现重复，联合索引可能会出现重复

索引的类型

索引包括聚簇索引和非聚簇索引，我们这里解析都是以B+树的数据类型为根本的，我个人的理解就是聚簇索引为主键索引，非聚簇索引为非主键索引。还有人将聚簇索引理解为一级索引，非聚簇索引为二级索引。这两种方式的理解都有一定的道理。我接下来为大家说明。

我们在使用mysql的时候，都建议创建主键， 这是为什么呢。因为mysql的机制是就是我们没有创建主键，mysql也会选择其中没有重复数据的列作为主键，如果没有合适的列，就会自动生成一个隐藏列作为主键，mysql的资源是非常宝贵的，所以我们能做的工作我们都自己完成，创建表的时候都创建主键。

聚簇索引就是以主键作为索引的，将主键排序为B+树的数据结构，叶子节点保存的是数据的地址。也就是因为有聚簇索引，所以才能根据主键很快的获取到数据。

非聚簇索引就是我们选择的一个或者多个字段作为索引，将他们排序为B+树的数据结构，叶子节点保存的就是主键的值，我们查询数据的时候都是先查询到主键，之后再根据主键获取数据的地址，再查询数据。因为索引最后都要落到一级索引上，所以mysql将一级索引保存到缓存中，来提高效率。

根据对聚簇索引和非聚簇索引的介绍，我们知道要是查询数据最终都需要进行回表的操作，回表也是耗费时间的。所以为了避免回表操作，这里引入一个概念叫覆盖索引，每次查询数据的时候只查询索引类的字段，这样就不会做回表操作，覆盖索引也是一种提高效率，记性sql优化的一种方式

执行计划

执行计划就是在要执行sql的前面增加一个explain，就会分析出这条sql的执行的情况，简单来说就是看索引是否命中。

索引主要是为了提高查询和排序的效率，尤其是排序，如果排序字段没有命中索引，那造成的后果是非常严重的。所以在看执行计划的结果的时候，我个人的习惯是先查看排序是否走索引列。

查看排序是否命中索引，查看执行计划中的Extra列，如果Extra列的内容是Using filesort的话，说明排序没有命中所以，必须要进行优化调整，让sql能够命中索引。

查看查询是否命中索引，查看type的值，所以type列的值是ALL的情况，说明走的是全表扫描，没有命中索引。我们正常做sql优化的时候，能优化到ref或者range就已经非常可以了，他们一个的区别是range是范围查询，ref不是范围查询。

还有一些是关于联表查询的时候，有一些需要看的地方，但是现在根据阿里的开发规范，都是尽量使用简单的sql解决问题，业务尽量在代码中完成。所以关于关联查询的说明，这里就不做说明了。

索引失效

索引失效的情况，就是违背的B+树的数据结构，所以只要当自己脑海中要是有这个数据结构，索引失效的情况也就可以理解了，不用死记硬背了。

最佳左前缀法则：

查询条件或者排序的字段顺序要和联合索引的顺序保持一致，出现顺序不一致或者索引字段的中间字段确实都有可能引起索引的失效。

在测试的时候，会发现当查询条件的顺序和索引字段的顺序不一致的时候，也会命中索引，这是因为mysql内部有优化器，执行的时候，mysql内部会自动的帮你调整字段的顺序，保证命中索引。但是mysql的资源是何等宝贵，所以我们在编写sql的时候，尽量自己优化就完事了，别麻烦mysql了，让他们去处理别的事吧。

在索引列使用函数或者计算上：

在索引字段上使用函数或者计算就等于在索引字段的值改变了，自然就和B+树节点上的值对不上了，自然也就无法命中索引了，所以一定不要再索引字段上使用函数或者计算。

索引列使用范围查询的右侧索引列无法命中索引：

还是和B+树的数据结构有关，当其中一个联合索引的索引列使用范围查询之后，之后的索引列的值就无法确定，无法按照规定的顺序排序，所以范围查询的右侧索引列无法命中索引。

使用不等于导致索引失效：

索引都是按照值的排序的，使用不等于无法确定值，所以使用不等于会导致索引失效。

like查询使用like“%value”:

使用ike“value%”不会导致索引失效，因为这样可以去B+树的节点中查询数据，如果是“%like”，无法确定数据的值，只能全表扫描了。

or或者in也可能会导致索引失效：

mysql会整体评估，使用or或者in的查询，全表扫描的效率会高于走索引。当数据量不大的时候，就会走全表扫描，不走索引。

索引特殊情况

1. 使用联合索引的时候，当第一个索引是范围查询的时候，可能不走索引，mysql可能认为第一个索引就使用范围查询，数据量肯定比较大，回表率比较高，所以直接使用全表扫描了。
2. 强制索引，当联合索引的第一个索引字段使用范围查询的时候，可以使用强制索引走索引，虽然可以让row减少，但是效率不会提高很多。
3. 覆盖索引：简答来说，就是查询的字段都是索引字段，不需要进行回表操作，这样可以很大程度上提高效率，联合索引要是第一个字段是范围查询的时候，也可以使用这种方式优化。
4. 使用in或者or的时候，当数据量比较小的时候，可能也不会走索引。
5. like “value%”一般情况会走索引， 这里引入一个概念叫索引下推， 因为使用like过滤了第一个索引字段之后，第二个或者第三个索引的字段就没法过滤了，因为顺序就被打乱了，在5.6版本之前，直接使用回表的方式查询，这样会降低效率。在5.6版本之后，引入了索引下推的功能，在使用第一个索引字段进行过滤之后，在根据第二个或者第三个索引字段，在索引过滤一部分数据，剩余在再进行回表，这样可以有效的降低回表的次数，提高查询的效率。

索引优化

排序优化

说明例子： user表，联合索引(name,age,position)

排序可以说是比较耗费资源的，如果排序要是优化的不好，那sql执行起来就会非常的慢，所以我们把排序的优化单独拿出来。

1. 排序的方法，我们在使用explain来执行自己的sql时候，在Extra中有两种情况，一种是Using index，说明走的是索引排序，效率比较高。一种是Using filesort，说明走的是文件排序，没有走索引，效率比较低。
2. 排序走索引，需要和查询条组合或者单独满足最佳左前缀法则。比如：
   1. select * from user where name = “Lilei” order by age,position。这种情况下，查询条件和排序字段满足了最佳左前缀法则，所以可以排序走索引。
   2. select * from user where name = “Lilei” order by position,age。这种情况下，position，age顺序反了，无法满足最佳左前法则，所以排序不能走索引。
   3. select * from user where name=”Lilei” and position = 1 order by age。这种情况下，position 虽然在age的前边，但是他不走索引，name已经走索引了，之后是age，所以这种情况下的排序是走索引的。
   4. select * from user where name = “Lilei” and age=14 order by position,age。这种情况下，虽然position在age前边，但是因为age就相当于一个常量一样，所以这种情况下，排序也是走索引的。
   5. select * from user where name = “Lilei” order by age asc,position desc。 这种情况下，因为age和position 排序方式不同，所以也无法走索引。mysql8可以使用降序索引来实现。
3. 当第一个字段是是范围查询的时候，需要使用覆盖索引才能让排序走索引。

排序优化总结

1. 排序最好再索引列上排序
   1. order by 满足最左前法则
   2. where 子句和order by 子句满足最左法则
2. 能使用覆盖索引尽量使用覆盖索引
3. group by和order by 一样，先排序后分组。where执行顺序高于having, 如果要是where中能筛选，能不having就不要having。

索引设计原则

1. 代码先行， 索引后上
2. 尽量使用联合索引，联合索引尽量要使用覆盖索引
3. 不要在小计数段上创建索引，比如性别这种只有两种数据，可能全表扫面都比他们走的快。
4. 长字符串尽量我们可以使用前缀索引
5. 当where 和order冲突的时候，优先使用where，优先where会减少数据量。

复杂sql优化

分页优化

分页查询是我们日常开发中经常用到的，分页是为了提高查询的效率的，mysql常用的分页方式就是limit。假如将数据分成100页，每页10条数据。如果要查询第98页，实际上是将98页的数据都查询出来，然后取最后的10条。也就意味着如果分页的数量过多的情况，就会出现查询效率比较低情况，所以为了提高效率，就需要对分页进行优化。

很多面试宝典上说到这个问题的时候，给的解决方案都是，使用自增id，查询条件的时候增加id >分页的开始的值。虽然可以提高效率，但是限定比较大，第一，主键只能使用自增ID, 自增ID有很多的弊端。第二，id不能中断，要是中断的话，就会出现查询的数据条数不对的情况。所以在实际开发中，这种方式应用的不是很多。

我个人认为，实际开发中出现大于1万页的情况也不是多，如果要是出现这么多页的话，就需要考虑缩小结果集，假如一开始是查询默认查询一年的时候，改成查询100的数据，缩小结果集，这样就可以降低页数。

使用覆盖索引，先查询id然后在通过inner join 查询全部的数据。这样也可以有效的提高效率。

增加一个日志号来分页，每次查询的时候，查询条件增加一个日志号查询，日志号就是上次查询的最后一条数据的日志号，查询时候只要查询大于这个日志号的数据，然后limit 页大小，这样可以避免查询全部数据，也解决页数过多的问题。

关联sql优化

常用的关联sql有inner join、left join、right join具体的使用我们在这里就不说了，我们说说如何优化。对于关联查询，我们都知道的一个原则是小表驱动大表。就是小表作为驱动表，大表作为被驱动表。先查询驱动表，之后再根据驱动表的数据逐一的去被驱动表中查询对应的数据，合并之后返回结果集。什么是小表呢，就是数据量比较小的表，什么是大表呢，就是数据量比较大的表。

inner join 和其他的2个最大的区别就是，inner join可以自动选择小表和大表。比如： select * from t2 inner join t1 on t2.a = t1.b。虽然t2在前边，并不代表t2是驱动表，t1和t2哪个表的数据量比较小，那个表就是驱动表。mysql会自己选择。我们比较常用的left join, 就没有这个机制，不管大表小表，左侧就是驱动表，右侧就是被驱动表。比如，select * from t2 left joion t1 on t2.a = t1.b。

两个表之间的关联字段必须创建索引，两个表关联查询是以其中的一个表为准，去另一表去查询数据，所以只有关联字段上加上索引才能提高查询的速度。

count查询优化

对于count查询，有的人写count(1)，有的人写count(*)很少有人去关注他们之间有什么区别，虽然在写查询sql的时候，最大的机会就是写*。但是count查询不一样，mysql对于count(*)做了优化，所以count(*)查询是效率最高的，就放心大胆的使用。

慢SQl优化

SQL优化是实际应用中必然会用到的东西，面试中问的频录也是很高，这个是一个综合的问题，不仅仅要考虑到业务，对数据库的原理也要有一定的理解，才能对sql作出最合理的优化。

SQL优化可以先从SQL表面分析，观察一下是不是有明显的错误，比如select *这种特别明显的错误，看看查询的字段是不是都是有用的，有的字段是没有必要的，看看是不是缩小一下结果集。

sql表面看出来的话，就观察索引的，使用explain执行计划，看看是否命中索引，观察的循序是先看Extra，看看排序是否命中索引，排序不命中索引是一个非常大的问题，如果排序命中索引之后，就要考虑查询是否命中索引，排序命中索引的话，而且可以优化到ref或者range，索引这就已经优化的可以了。

索引和数据是分开的两个空间，如果索引中不存在数据，需要去数据磁盘中查询，有一个概念，叫覆盖索引，就是查询的字段，都是索引字段，可以也会大大的提高效率。

该做的优化如果都已经做完了，还是比较慢的情况下，这种情况下，就只能考虑使用分库分表了，分库分表的设计的几个重要的概念，使用的组件，分片键，分片算法都是我们要考虑的事情。