Mysql优化第二弹：无招胜有招

人世仙家本自殊，何须相见向中途。惊鸿瞥过游龙去，漫恼陈王一事无。
嗨，大家好，我是洛神，性别男。一个来自快乐星球的程序员。
欢迎大家专注我的公众号【程序员洛神】，我会不定期发放福利哟~

前言

这几天因为工作比较忙，所以写文章的时间比较少，比计划的推迟了几天才更，请各位小伙伴原谅洛帅帅（委屈巴巴），这篇文章还是接着上一次的往下写，主要内容是索引和SQL的优化技巧，不感兴趣的小伙伴，可以直接~~~划到最下面，无情的点个赞+转发+收藏，然后帅气离开了~哈哈哈，开个玩笑，咱们进入正题啦！

正文

一、索引优化

这个时候请允许洛帅帅多啰嗦几句，学习新技能的时候，一定要有规划的学习，要有步骤，这是个什么？为什么要用？怎么
用？有什么注意事项？把每一项都整明白了，你就真的掌握这个技能点了。

什么是索引？
索引是帮助MySQL高效获取数据的 排好序的数据结构，可以简单理解为书的目录页，通过目录可以快速找到你想看的章节内容。

索引结构

为了方便理解，暖男洛给你们每种结构都贴了图，可以结合图进行理解。（动图中用的工具是旧金山大学开发的可视化数据结构和算法展示工具，感兴趣的同学到文末获取地址~）

1.二叉树
这是最基础的树结构，它可以比顺序结构更高效的查询数据，它的特点就是：从第一个父节点开始跟新的值比较，比当前父节点大就放到右边进行比较，比当前父节点小就放到左边进行比较，反复进行操作直到移动到目标节点位置。

缺点：
对于某些特殊情况下（例如数据库中自增id），如下图所示，叶子节点会一直添加到右侧，最终会退化成链表，等于没有加索引。

2.红黑树
属于平衡二叉树（AVL树）的一种（平衡二叉树是二叉树的升级，AVL树自平衡性太强，总是要求左右节点的高度差不能大于1，超过1就会左旋/右旋进行调整，大数据量情况下效率较低，所以一般都使用红黑树，例如TreeMap底层就是红黑树），主要解决了二叉树特殊情况下的树退化问题，会左旋、右旋进行调整，并且始终保证了父节点大于左子节点、小于右子节点的规则。

缺点：
红黑树的每个父节点只能存在两个子节点，所以当数据量很大的时候，树也会变得很高，会产生相当大的磁盘IO消耗(MySQL是基于磁盘的数据库，索引是以索引文件的形式存在磁盘中，所以索引的寻找过程就会牵扯到磁盘的IO)，效率较低。

PS：
左旋：以某个结点作为旋转结点，其右子结点变为旋转结点的父结点，右子结点的左子结点变为旋转结点的右子结点，其左子结点保持不变。
右旋：以某个结点作为旋转结点，其左子结点变为旋转结点的父结点，左子结点的右子结点变为旋转结点的左子结点，其右子结点保持不变。
这句话一定要搞清楚，听起来比较绕，建议自己动手画个图来理解一下。	

3.Hash
对数据进行Hash运算，将运算结果作为数据文件的指针。这样我们查询的时候，就可以根据一次Hash运算，得到目标数据所在的文件地址，进而得到具体的数据，效率相当之高。
缺点：
不支持范围查询，只能进行等式查询（where id = 3）。

好了，有了上面的知识做铺垫，接下来就开始今天的重头戏咯~

4.B-Tree（是B树 不是B减树）
既然我们都知道红黑树的缺点是树的高度太高，那么，我们就设计一个树结构，让每个树节点可以存放多个数据节点即可。但是也要设定一个合理的阈值，避免每个节点的数量太大，造成额外的消耗，于是乎，B-Tree就诞生了。

在B-Tree的结构里，每个节点包含了索引值和表记录信息（data），也可以把每个节点看作一个Map，key是索引，value是具体的data数据。B-Tree的出现解决了使用红黑树时，由于数据量过大造成的树的高度太高的问题。相同数据量的数据只需要更少的层级。

缺点：
如果进行范围查询，按照B-Tree的结构，每次都要从根节点查询一遍，效率不是太高。

5.B+Tree

这个就是目前MySQL索引在用的存储结构了。B+Tree只有最下层的叶子节点才存储data数据，其他的所有非叶子节点只存储索引值，这样做可让单个节点存放更多的索引值，从而降低树的高度。
但是这种结构会在上层非叶子节点存储一部分冗余数据，不过这种问题都是可以容忍的，因为冗余的都是索引数据，对内存的消耗不大。

如图所示，每个叶子节点之间都有一个指针指向相邻的节点，这样做的目的就是为了提高区间访问的性能，例如我们要查20-45之前的所有数据，找到20后，只要按照顺序遍历一次即可得到所有的数据，极大的提升了查询效率。(这也是对比B-Tree 它的优势所在）。

目前MySQL索引相关的数据结构大概就这些，每种结构的巧妙之处都值得大家刨根问底的去学习和研究，时间原因，洛帅帅就不花大篇幅去讲解啦。知道了索引的结构以后，我们来聊一下索引具体的类型。

索引类型

1. 主键索引（PRIMARY KEY）

专门为主键字段创建的索引，是一种特殊的唯一索引，值不允许重复或空。不能使用create index来创建

2. 唯一索引（UNIQUE）

和普通索引类似，区别就是唯一索引的字段值不允许重复，允许为空，常用于流水号、单据号等业务字段。

3. 普通索引（INDEX）

最基本的索引类型，没有任何限制。

4. 组合索引

指在一个索引上添加多个字段。
create index index_test (name,age)；

5. 全文索引

与其说它是一个索引，不如说更像是个搜索引擎，用来搜索文本中的关键字。因为它的场景主要就是大文本数据量下的数据匹配，聪明的小伙伴听到数据匹配，可能马上就想到了LIKE关键字了，为啥放着LIKE不用?用全文索引呢？效率，效率，还是效率。like对于大数据量的场景下，效率是很低的。
当然，这种索引其实日常开发中其实用的很少，真正的大数据量的场景一般都是推荐使用专业搜索引擎来做的（solr，ElasticSearch等等）

为什么要建索引？

耐心看完上面内容的优秀小伙伴们，肯定能总结出原因了：
1.加快数据检索速度
2.减少磁盘IO（用B+Tree作为索引结构）
3.利用索引的特性来约束数据库（唯一性索引，强约束该字段数据不重复）

既然索引是个好东西，那我们给每个字段都加上索引，那SQL执行效率是不是就很高了? 傻瓜，当然不是了，福兮祸所伏，祸兮福所倚
凡事有利有弊，接下来，洛帅帅给你讲一下建立索引后带来的问题。

1.创建索引后会生成对应的索引表用来存放索引数据，索引表是需要占用物理表空间的，数据量增加后，索引表也会越来越大。
2.对表中的数据进行增删改的时候，对应的索引表数据也需要动态维护，这会降低数据的操作速度。

暖心洛帮给你总结了建立索引需要注意的地方：

1.建立索引一定要根据业务需求来设计，每个表的索引数量最好不要超过5个；
2.优先考虑建立组合索引，提升索引利用率。要注意哦，建立组合索引的时候一定要遵循最左匹配原则~

啊？最左匹配原则又是啥子嘞？

最左优先，以最左边为起点任何连续的索引都能匹配上。

不好理解？么的事，按照洛帅帅的风格，话不多说，直接举例子

建立一个组合索引 index_test(a,b,c)

select  * from test where b = 1

那么，上面的组合索引是不会生效的，这就是最左匹配原则第一条：最左边的列一定要先被用到，否则整个索引都不会生效。

select * from test where a = 1  and  c = 1  

结果？ a会走索引 c不会 这就是第二条原则：组合索引生效都需要时连续性的，中间出现了中断，那么，就只有中断前的有效了。

select * from test where a<2  and  b = 1  

猜猜结果？ 没错，a会走索引，b不会 这就是第三条原则：当任意一个列使用了范围查询后，那么后面的列都不会生效了。

3.字段值重复率低的尽量不要建立索引

这一条我要解释一下，为什么介么说呢？你想一下哈，假如我给“性别”字段加个索引，性别只有几种类型，要么男，要么女，要么帅男洛，如果我的查询筛选条件是where 性别 = ‘男’，
数据库里假设有500W条数据的性别是男，那就等于我要扫描索引表500W次，还要再扫描数据表500W次，这么一看，还不如直接扫描数据表来的划算，最起码去掉了额外的索引表扫描开销。这么说懂了吧乖乖？

好了，耐心看到这里已经很棒棒啦，不过，打起精神来，下面开始实战的重头戏咯~

二、SQL优化

关于SQL优化，其实更多的是考验你的经验，实际业务中SQL的形式太多了，洛帅帅也见过一些项目的业务报表中一百多行SQL，你说这种SQL的优化能一下子教会吗？只能一步一步拆开进行分析，不断的拼接SQL尝试，最终达到满意的响应速度。

暖男洛总结出了一些平时总结的一些SQL优化的经验，当然这些是远远不够的，希望小伙伴们在工作中不断磨练，不断总结，最终形成一套自己的SQL优化思路。

1.查询时只返回自己需要的结果
做SELECT查询时，切记不要使用select *，选择你需要的列进行查询，原因有二：

• select * 的查询，到数据库层需要有一个查询字段隐式转换的过程，转换成实际的字段名称，这个过程时需要消耗资源的。
• 大部分情况下并不需要查询所有的字段（至少对于大部分的OLTP系统来说是）。

2.对于使用索引的列，尽量使用覆盖索引

what??啥子是覆盖索引？上面你讲索引的时候没有提过这个啊！脑瓜子疼！

首先声明一下覆盖索引并不是像唯一索引这样的实际结构（以下内容仅限于innoDB引擎）。
覆盖索引指的是复合索引的列个数和顺序跟select 的一致，这样就可以避免回表的发生。

那啥子是回表？

了解这个之前先请小伙伴们去了解下主键索引和辅助索引，回来后我们继续往下。

好了，现在大家都知道了，主键索引就是叶子节点保存数据，而辅助索引就是叶子节点保存主键值。什么意思，就是当我要使用辅助索引查询数据时，需要先根据保存的主键值去查询主键信息，然后再使用主键的值去主键索引上查数据，这个就称为回表，回表是需要产生额外IO的，而覆盖索引的作用就是避免回表的发生。

还不够清楚？举个栗子~

我现在有个索引index_test(a(主键),b)

select a,b,c from  table  where b = 1

这个查询你猜猜会不会回表? 当然会了! 因为b的索引上只存储了ab的数据，c的数据是需要根据主键a的主键信息去查询记录的。
我只需要给索引添加个字段c，index_test(a,b,c) 这样只需要一次查询 就可以查到所有数据了。

3.避免使用or来做条件筛选，or的时间复杂度是O(n)

4.避免使用 != 、IS NULL、 IS NOT NULL等，这会造成索引失效。

5.使用模糊搜索尽量避免%xxx查询，同样也会造成索引失效，如果非要用这种搜索， 建议给查询的字段建立覆盖索引。

6.查询数据避免查全表，根据业务情况返回数据条数。

7.避免不同类型的等值查询,例如 where a(varchar类型) = 2 这会造成MySQL进行自动隐式转换，降低效率。

三、Explain（解释执行计划）

当我们建立好了合适的索引以及合理的数据表结构后，SQL一定就没问题了吗?
当然不是了，每次写完SQL后，我们都要使用explain帮我们提前分析出SQL出可能存在的问题，从而进行SQL优化。孩子咳嗽老不好？多半是SQL写的有问题。

二话不说，先怼个SQL出来。

在MySQL如何使用Explain？没错! 就是像这样，在你写的SQL前面加一个Explain关键字，执行就可以看到下面这个样几~

怎么这么多奇奇怪怪的英文单词啊！老妹儿心态直接崩了啊！

千万别被它们吓倒，洛帅帅这不是还没开始讲呢，跟着洛帅帅的思路走下来，你会发现这些东西啊，啥也不是~简单的要死。

来来来，先让暖男洛给你们提炼一下里面需要特别关注的几个属性：

id        type     key        rows        Extra     

这几个是平时需要特别关注的，但是，毕竟我是暖男啦，当然都会给你讲一遍啦 来 跟着洛帅帅开始肝！

• id

id代表的查询结果的序列号，其实就是MySQL告诉你它是按照什么顺序来执行的，具体顺序分为以下三种情况：
1.id相同 如上图所示，id相同，执行顺序就是从上往下按照顺序执行了。

2.id不同 id不同时，id数值越高，执行的优先级越高

3.id又相同又不相同 欸? 什么意思嘞？来，看图！

同一个查询里，有相同的id，又有不同的id，那么，请记住一句话：首先看id是否相同，相同的id为一组，从上到下执行。再看所有的组，id越大，越先执行。

• select_type

表示的是这次的查询是一个什么类型的查询（简单的select查询？还是联合查询？还是包含子查询？）查询类型一共有六种：

1.SIMPLE 就是简单的select查询，没有任何的子查询或者联合查询。(select * from student)

2.PRIMARY 查询中只要非简单查询，最晚层的查询都会被标记为PRIMARY

3.SUBQUERY 在select或where列表中存在子查询

4.DERIVED(衍生) 在from后面存在子查询，就会被标记为DERIVED，它表示mysql会递归执行这些子查询，并且把查询结果放到临时表中。

5.UNION 如果存在第二个select查询并且出现在union之后，那么这个查询会被标记为UNION。

6.UNION RESULT 表示从union后的表中获取结果的select语句

上面这几条也不需要死记硬背，业务中写sql的时候，多留心去explain以下，看看不同sql反馈的select_type，时间长了就记住了。

• type

相当相当相当重要的参数，它代表的是访问类型，想知道是否走索引了，就是看这个参数。下面我会按照类型的好坏从高到低讲（越低的代表执行效率越差）

1.system 可以忽略不计，因为平时没机会看到它，它代表查询的表中只有一行记录（等于系统表,是const类型的特例）

2.const 表示通过索引一次就知道了，const用于比较primary key（主键索引），因为只匹配一行数据，所以很快。

3.eq_ref 常见于使用唯一索引来进行表关联查询，因为只有一条记录匹配，所以这个的效率也是相当高的。

4.ref 和eq_ref的区别就是它用的是非唯一索引来进行关联查询，一条记录可能会匹配多条数据，所以效率没有eq_ref高

5.range 只检索给定范围内的数据 简单来说就是 在索引表里的范围查询（between或者in之类的 ）

6.index 基于整个索引表的全表扫描 （最低忍耐限度）

7.all 基于整张表的全表扫描，简称作死式查询

PS：一般来说开发环境下，能达到ref已经是很不错的了。

• possible_keys

MySQL猜测的你这次查询可能会用到的索引（注意是"可能"，不一定会用到）

• key

表示的是实际用到的索引列

• key_len

表示索引使用的字节数，可通过该列计算使用的索引的长度。在同样的查询结果下，长度越短越好，因为越长，代表使用的索引越多

• ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

• rows

表示的是MySQL估算出的找到所需记录需要读取的行数

• Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:使用了where条件来过滤索引数据

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

Using filesort：当查询中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”，如下图所示，我有一个索引是name字段的，但是我根据年龄排序，这就需要额外的外部排序才能满足。

Using join buffer：联表的条件没有用到索引，并且需要使用连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used：查询语句中使用from dual 或不含任何from子句

慢日志查询

我数据库设计的很好了，索引也建好了，SQL也explain过了，这下子肯定没问题了吧？

傻瓜，别这么早下定论，有经验的小伙伴肯定遇到过一种情况：测试环境下的接口或者是刚上生产的接口运行的很正常，但是当线上跑了一段时间后，接口响应突然变慢了，这时候你就要注意了，可能是SQL的问题了，那线上如何排查SQL？总不能找运维要个线上数据库权限吧?

呐，和暖男洛一样贴心的MySQL为你提供了一个神器-----慢查询日志！

顾名思义，慢查询日志，就是将运行缓慢的SQL通过日志的形式输出，然后通过排查发现SQL的问题，进而进行优化（好多公司的开发绩效是和慢SQL的出现次数挂钩的哟~~~所以小伙伴们更要注意了）

这里我先说一下，MySQL中的日志文件是很全面的，除了马上要讲的慢查询日志意外，还有：
重做日志(redo log)、回滚日志（undo log）、二进制日志（binlog 主从同步的底层就是基于binlog实现的）、错误日志（error log）等等 感兴趣的大家可以自行查询学习，这里不再进行详细讲解，好了，回归正题。

慢日志查询主要是依赖于事先设置好的数据库配置
有几个核心的命令给大家大概说一下：
show variables like ‘slow_query%’ 查询所有慢查询日志相关的配置 时间阈值、log存放位置都会在这里展示
通过set global long_query_time = N 来配置执行多久的sql会被算作慢sql 。

总结

1.知其然并知其所以然，合理使用索引的前提，一定是要先了解索引的结构，才可以谈笑自若的面对各种问题。

2.SQL优化场景太多，需要你在工作中不断积累经验，不断总结，形成一套自己的优化大法。

3.一定要养成一个Explain SQL的习惯，尽可能的将问题消灭在萌芽之中。

4.线上接口响应缓慢，在考虑业务代码层面的时候，应优先排除是否是慢SQL造成的。

结尾

好了，到这里，索引和sql的优化扫盲，讲的已经差不多了，女朋友表示已经去可以和面试官对线了~有遗漏的或者是讲的不对的地方欢迎大家留言评论。

文章末尾献上上面提到的数据结构和算法的可视化工具网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

人世仙家本自殊，何须相见向中途。惊鸿瞥过游龙去，漫恼陈王一事无。我是洛神，我们下期见。