Mysql学习之优化总结(1)--从mysql查询过程看优化

一、前言

提到mysql查询优化,很多人脑海里可能会想到NOT NULL、合理索引、不使用select *、合适的数据类型等等,可是这些优化技巧是怎么来的?我们是否了解其中真正的原理?这些技巧是否又适合所有场景呢?优化需要注意哪些地方呢?我们来一起探讨下。

二、优化方向

优化有风险,修改需谨慎!!

通常来说,一个业务的稳定和可持续性,比稳定性更重要。

优化如果在一个已经上线很复杂的系统,优化让性能变好的同时,可能会使系统稳定性变差。所以通常来讲优化是由业务需求驱动的。

数据库的优化有两个方向:

  • 安全 :提高数据的稳定性和可持续性
  • 性能 :提高数据访问的高性能性

数据库优化可以从几个维度出发:

  • 优化硬件设施
  • 优化系统配置
  • 优化表结构
  • 优化SQL及索引优化

其中后2者是成本最低而且成效最高的。

三、Mysql工作原理

想要优化数据库就要先了解数据的架构和工作原理,这样才能清晰的知道我们应该遵循什么样的规则和优化的方向,才能让数据库更高效顺畅的工作。

首先来看下mysql的架构图:

  • 当用户发起请求,服务器端连接线程处理器会为客户端分配一个线程并开辟内存空间,开始提供查询和缓存的机制。服务器会负责缓存线程,所以服务层不需要为每个连接新建线程,提升了连接效率。

再来看一下mysql查询过程:

上图是网上一张较为通用的流程图,看似比较复杂,可以分为6个步骤:

一起来看下每个步骤做了些什么,从中可以有些什么优化。

1)请求查询

通信协议

客户端与服务端间的通信协议为“半双工”协议:即在任一时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生。

1、客户端向服务端传送的packet不能过大,否则会抛异常

2、服务端向客户端传输的所有非常多数据客户端必须完整接收,不能只接收一部分而让服务器停止传输。

优化建议

查询语句尽量简单,只查询所需要的数据,尽量避免使用select *和加上分页设置limit。减小传输packet的数量和大小。

2)缓存查询

缓存过程

MySQL将缓存存放在一个引用表,通过一个哈希值索引(通过查询语句本身、查询的数据库、协议版本号等计算)来存放和获取缓存。这个查询缓存,默认是不开启的,因为查询缓存要求SQL和参数都要一样,所以命中率非常低。

如果查询中包含任何用户自定义函数、存储函数、用户变量、时间变量、临时表、mysql库中的系统表,其查询结果都不会被缓存。

缓存失效:

查询涉及到的每个表,如果有数据或结构发生变化,那么和这张表相关的所有缓存数据都将失效。

性能消耗:

  • 有写操作时,会将关联的表的所有缓存设置为失效,当缓存数据很大时,这个系统消耗将会非常大。
  • 打开查询缓存时,任何的查询语句在开始之前都必须经过检查。
  • 查询结果可被缓存存入缓存时,也有一定的系统消耗。

优化建议

缓存收益>性能消耗收益时,才考虑打开缓存。

合理控制缓存大小。

对单个查询语句设置是否缓存,对经常写操作的表不要轻易使用缓存。

批量插入代替循环单条插入

3)解析查询

解析与预处理过程:

如果没有开启查询缓存,或者缓存未命中,那么就到了解析器。

解析器主要对SQL语法进行解析,通过语法规则来验证和解析,比如SQL中关键字是否正确或者关键字的顺序是否正确,解析结束后就变成一颗解析树。

得到解析树之后,不能马上执行,这还需要对这棵树进行预处理,也就是说,这棵树,我没有经过任何优化的树,预处理器会这这棵树进行一些预处理,比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等...预处理还会进一步检查解析树是否合法,比如检查要查询的数据表和数据列是否存在。

预处理完毕之后就得到一棵初步优化的的树。

4)查询优化

查询优化过程:

我们写一条SQL,比如SELECT * FROM Anchor WHERE uin = 12345678 AND nickname = "主播";

它会怎么去执行?它是先执行uin = 12345678还是nickname = "主播"?

每一条SQL的执行顺序查询优化器就是根据MySQL对数据统计表的一些信息,比如索引,比如表一共有多少数据,在真正执行SQL之前,MySQL会根据自己数据进行综合的判定,判断此次查询在多种执行方式里面,选哪种执行方式运行最快.它会选择其中成本最小的一个。

平时优化SQL,就是想让查询优化器按照我们的想法,帮我们选择最优最快的执行方案。因为我们比MySQL更懂我们的数据.MySQL看数据,仅仅只是自己收集到的信息,这些信息可能是不准的,MySQL根据这些信息选了一个它自认为最优的方案,但是这个方案可能和我们想象的不一样.

MySQL的查询优化器的优化策略:

   重新定义表的关联顺序(多张表关联查询时,并不一定按照SQL中指定的顺序进行,但有一些技巧可以指定关联顺序)

   优化MIN()和MAX()函数(找某列的最小值,如果该列有索引,只需要查找B+Tree索引最左端,反之则可以找到最大值)

  提前终止查询(比如:使用Limit时,查找到满足数量的结果集后会立即终止查询)

  优化排序(在老版本MySQL会使用两次传输排序,即先读取行指针和需要排序的字段在内存中对其排序,然后再根据排序结果去读取数据行,而新版本采用的是单次传输排序,也就是一次读取所有的数据行,然后根据给定的列排序。对于I/O密集型应用,效率会高很多)

从这里可以看出我们可以通过优化表结构,优化索引等来优化查询优化的过程。

5)查询执行

这里的查询执行计划,也就是MySQL查询中的执行计划,比如要先执行uin = 12345678还是nickname = "主播"

在解析查询和优化后,MySQL会生成对应的执行计划,这个执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行这一份传过来的计划,到磁盘中的文件中去查询。

影响这个查询性能最根本的原因是什么?就是硬盘的机械运动,也就是平时熟悉的IO操作,所以一条查询语句是快还是慢,就是根据这个时间的IO来确定的.那怎么执行IO就是根据传过来的执行计划去操作的。

6、结果返回

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

即使没有数据,MySQL仍然会返回这个查询的相关信息,比如该查询影响到的行数以及执行时间等等。

前面提到过,服务器端向客户端返回数据是通过一些数据包的。这些数据包需要符合客户端/服务器通信协议。如果数据量过大,在传输过程中,可能对MySQL的数据包进行缓存然后批量发送。

四、总结

 了解了mysql的查询过程,可以让我们知道mysql执行过程中时间消耗到了哪里,帮助我们理解一些常用到的优化技巧背后的原理。

未来篇:

mysql学习之优化总结(2)--查询优化

mysql学习之优化总结(3)--解读索引的数据结构和算法,了解索引原理和设计优化

mysql学习之优化总结(4)--优化表的设计

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java一日一条

单机数据库优化的一些实践

数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表。...

1052
来自专栏思考的代码世界

Python网络数据采集之存储数据|第04天

存储媒体文件有两种主要的方式:只获取文件 URL 链接,或者直接把源文件下载下来。

4617
来自专栏北京马哥教育

思路决定出路 | 101个MySQL调试和优化技巧

MySQL是一个功能强大的开源数据库。随着越来越多的数据库驱动的应用程序,人们一直在推动MySQL发展到它的极限。这里是101条调节和优化 MySQL安装的技巧...

2808
来自专栏大数据和云计算技术

hive拉链工具实战

这个丁延明同学写的一个实战工具,坚持用代码解决问题,推荐! 有相关业务的同学可以一起讨论,下面是正文。 ---- 1、背景 大家好 最近由于公司业务需要写了一篇...

3307
来自专栏文渊之博

SQL Server内存

背景 最近一个客户找到我说是所有的SQL Server 服务器的内存都被用光了,然后截图给我看了一台服务器的任务管理器。如图 ? 这里要说明一下任务管理器不会完...

3597
来自专栏数据和云

Real-time materialized view,面向开发者的12.2新特性

题记:在12.2之前,如果使用on command刷新物化视图,必须得有个job来定时的刷,那么,在一次job运行之后,下一次job到来之前,如果基表有数据变化...

3084
来自专栏沃趣科技

Oracle Real Time SQL Monitoring

术语说明 TableQueue,消息缓冲区,在并行操作中使用,用于PX进程之间的通信,或者PX进程与QC进程之间的通信,是内存中的一些page,每个消息缓冲区的...

4538
来自专栏MYSQL轻松学

MySQL层相关优化

1.1、关于版本选择 官方版本我们称为ORACLE MySQL,这个没什么好说的,相信绝大多数人会选择它。 Percona分支版本,它是一个相对比较成熟的、优秀...

3878
来自专栏乐沙弥的世界

Oracle Time Model Statistics(时间模型统计)

下图为 DB Time in Overall User Response Time

1202
来自专栏liuchengxu

在 Golang 开发中使用 Makefile

使用 Golang 已经有一阵了,在 Golang 的开发过程中,我已经习惯于不断重复地手动执行 go build 和 go test 这两个命令. 不过,现...

1761

扫码关注云+社区

领取腾讯云代金券