优化SQL究竟是在优化什么?

前言

优化一直是面试最常问的一个问题,因为从优化的角度,优化的思路,完全可以看出一个人的技术积累,当然java也流传着一个HashMap就可以看出一个人java基础的传说.

系统优化

谈到系统优化,假设这么个场景,用户反映系统太卡,那么我们怎么优化?

如果请求过多,判定web服务器的压力过大,增加前端的web服务器,做负载均衡

如果请求静态界面不卡了,但是动态数据还是卡,说明MySQL处理的请求太多了,在应用层增加缓存.

如果请求激增,还是有大量的查询压力到MySQL,这个时候就要想办法解决MySQL的瓶颈

总结起来就是,系统优化的第一步,是绝对轮不到MySQL集群,我们之所以要做MySQL的集群,一般都是在做好了应用级别的缓存,请求还是太多的情况下考虑的问题.

当然读写分离还必须遵循这么几个原则

就算MySQL拆成了多个,也必须分主从,所有的写操作都必须要在主MySQL上完成,所有从MySQL的数据都同步于主MySQL.如果一个业务(service中的一个方法)中,既有读操作,又有写操作,因为写操作一定要在主MySQL上,所以在一个事务中的所有数据来源都只能来自一个MySQL

既然涉及到同步,那一定有延迟,所以能在从MySQL上进行读的操作,一定对实时性有一定的容忍度,例如日志和首页的统计信息等

本篇为后面”读写分离”的启蒙篇,因为只有弄懂了MySQL的执行流程,我们才知道我们平时常说的优化SQL到底是在优化些什么.

MySQL执行流程

先简单粗暴上一执行流程图

MySQL执行SQL的流程.png
文字解析:

1.当我们请求MySQL服务器的时候,MySQL前端会有一个监听,请求到了之后,服务器得到相关的SQL语句,执行之前(虚线部分为执行),还会做权限的判断

2.通过权限之后,SQL就到MySQL内部,他会在查询缓存中,看该SQL有没有执行过,如果有查询过,则把缓存结果返回,说明在MySQL内部,也有一个查询缓存.但是这个查询缓存,默认是不开启的,这个查询缓存,和我们的Hibernate、Mybatis的查询缓存是一样的,因为查询缓存要求SQL和参数都要一样,所以这个命中率是非常低的.

3.如果我们没有开启查询缓存,或者缓存中没有找到对应的结果,那么就到了解析器,解析器主要对SQL语法进行解析

4.解析结束后就变成一颗解析树,这个解析树其实在Hibernate里面也是有的,大家回忆一下,在做Hibernate项目的时候,是不是要拷贝一个antlr.jar,这个就是专门做语法解析的工具.因为在Hibernate里面有HQL,它就是通过这个工具转换成SQL的,我们编程语言之所以有很多规范、语法,其实就是为了便于这个解析器解析,这个学过编译原理的应该知道.

5.得到解析树之后,不能马上执行,这还需要对这棵树进行预处理,也就是说,这棵树,我没有经过任何优化的树,预处理器会这这棵树进行一些预处理,比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等…

6.预处理完毕之后,此时得到一棵比较规范的树,这棵树就是要拿去马上做执行的树,比起之前的那棵树,这棵得到了一些优化

7.查询优化器是MySQL里面最关键的东西,我们写任何一条SQL,比如SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它会怎么去执行?它是先执行username = toby还是password = 1?每一条SQL的执行顺序查询优化器就是根据MySQL对数据统计表的一些信息,比如索引,比如表一共有多少数据,MySQL都是有缓存起来的,在真正执行SQL之前,他会根据自己的这些数据,进行一个综合的判定,判断这一次在多种执行方式里面,到底选哪一种执行方式,可能运行的最快.这一步是MySQL性能中,最关键的核心点,也是我们的优化原则.我们平时所讲的优化SQL,其实说白了,就是想让查询优化器,按照我们的想法,帮我们选择最优的执行方案,因为我们比MySQL更懂我们的数据.MySQL看数据,仅仅只是自己收集到的信息,这些信息可能是不准确的,MySQL根据这些信息选了一个它自认为最优的方案,但是这个方案可能和我们想象的不一样.

8.这里的查询执行计划,也就是MySQL查询中的执行计划,比如要先执行username = toby还是password = 1.

9.这个执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行这一份传过来的计划,到磁盘中的文件中去查询,这个时候重点来了,影响这个查询性能最根本的原因是什么?就是硬盘的机械运动,也就是我们平时熟悉的IO,所以一条查询语句是快还是慢,就是根据这个时间的IO来确定的.那怎么执行IO又是什么来确定的?就是传过来的这一份执行计划.

10.如果开了查询缓存,则返回结果给客户端,并且查询缓存也放一份.