MySQL性能优化原理 — 前篇

2022-06-29 12:47:15

 

前言

性能优化（Optimize）指的是在保证系统正确性的前提下，能够更快速响应请求的一种手段。而且有些性能问题，比如慢查询等，如果积累到一定的程度或者是遇到急速上升的并发请求之后，会导致严重的后果，轻则造成服务繁忙，重则导致应用不可用。它对我们来说就像一颗即将被引爆的定时炸弹一样，时刻威胁着我们。因此在上线项目之前需要严格的把关，以确保 MySQL 能够以最优的状态进行运行。同时，MySQL 的性能优化无法一蹴而就，必须一步一步慢慢来，从各个方面进行优化，最终性能就会有大的提升。

对 MySQL 优化是一个综合性的技术，主要包括

  • 表的设计合理化(符合3NF)
  • 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
  • 分表技术(水平分割、垂直分割)
  • 读写[写: update/delete/add]分离
  • 存储过程 [模块化编程，可以提高速度]
  • 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
  • mysql服务器硬件升级
  • 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

本篇主要从MySQL的逻辑结构和查询过程进行分析，下一篇重点来梳理MySQL性能优化的几个方面。对于MySQL索引的实现原理可以查看这篇《深入理解MySQL索引底层数据结构与算法》

MySQL逻辑结构

MySQL性能优化原理 — 前篇

MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，如：连接处理、授权认证、安全等功能均在这一层处理。

MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

MySQL 查询过程

为了使 MySQL 能够获取更高的查询性能，我们需要弄清楚 MySQL 是如何优化和执行查询的。一旦理解了这一点就会发现，很多查询优化公众实际上就是遵循一些原则让 MySQL 的优化器能够安装预想的合理方式运行而已。

当客户端向 MySQL 发送一个请求的时候，MySQL到底做了些什么呢？
MySQL性能优化原理 — 前篇

客户端/服务端通信协议

MySQL 客户端 / 服务端通信协议是“半双工”的，在任意时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端需要接收完整消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置 max_allowed_packet 参数。但是需要注意的是，如果查询实在太大，服务端会拒绝接收更多数据并抛出异常。

与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。所以在实际开发中，尽量保持查询简单且返回必需的数据，减少通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一。

通信方式分为：单工通信、半双工、全双工。

  1. 全双工的典型例子是:打电话。电话在接到声音的同时也会传递声音。在一个时刻，线路上允许两个方向上的数据传输。网卡也是双工模式。在接收数据(比如一直在下载东西)的同时，又发送数据(比如请求网页)
  2. 半双工：在同一个时刻只能进行一个动作。在一个时刻，线路上只允许一个方向上的数据传输。
  3. 单工：典型例子就是电视遥控器。接收端和发送端已经固定了的。接收端只能接收数据。没有发送数据的功能。发送端只有发送的功能。没有接收的功能.特点是,线路上的数据流是永远是单方向,固定方向。这样理解的话，显示器与电脑主机之间的工作方式也是单工模式的。

查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么 MySQL 会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL 将缓存存放在一个引用表（不要理解成 table ，可以认为是类似于 HashMap 的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端版本协议版本号等一些可能影响结果的信息计算得来。所以两个查询语句在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

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

比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

既然是缓存，就会失效，那查询缓存何时失效呢？MySQL 的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发送变化，那么和这张表相关的所有缓存数据都会失效。正因为如此，在任何的写操作时，MySQL 必须将对应的表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

  • 任何的查询语句在开始之前都必须经过检查，即使这条 SQL 语句永远不会命中缓存；
  • 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗。

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

  • 用多个小表代替一个大表，注意不要过度设计
  • 批量插入代替循环单条插入
  • 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
  • 可以通过 SQL_CACHE 和 SQL_NO_CACHE 来控制某个查询语句是否需要进行缓存

最后的建议不要轻易打开查询缓存，特别是写密集型应用。如果你想试一下，可以将 query_cache_type 设置为 DEMAND ，这时只有加入 SQL_CACHE 的查询才会走缓存，其他查询则不会，这也可以非常*地控制哪些查询需要被缓存。

当然查询缓存系统本身是非常复杂的，这里讨论的也只是很小的一部分，其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等，可以自行阅读相关资料，这里权当抛砖引玉吧。

语法解析和预处理

MySQL 通过关键字将 SQL 语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如 SQL 中是否使用了错误的关键字或者关键字的顺序是否正确等待。预处理则会根据 MySQL 规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

mysql> select * from sys_log limit 10;  
...省略结果集  
  
mysql> show status like ‘last_query_cost‘;  
+-----------------+-------------+  
| Variable_name   | Value       |  
+-----------------+-------------+  
| Last_query_cost | 6391.799000 |  
+-----------------+-------------+
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

  • 重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）
  • 优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理查看《MySQL索引底层原理》）
  • 提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）
  • 优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）

随着MySQL的不断发展，优化器使用的优化策略也在不断的进化，这里仅仅介绍几个非常常用且容易理解的优化策略，其他的优化策略，大家自行查阅吧。

查询执行引擎

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handlerAPI。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL 仍然会返回这个查询的相关信息，比如查询影响到的行数以及执行时间等等。

如果查询缓存被打开且这个查询可以被缓存，MySQL 也会将结果存放到缓存中。

结果集返回客户端是一个增量且逐步返回的过程。有可能 MySQL 在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果，需要注意的是，结果集中的每一行都会以一个满足 客户端/服务端通信协议 中锁描述的通信协议的数据包发送，再通过 TCP 协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

总结

MySQL 整个查询执行过程，总的来说分为6个步骤：

  • 客户端向MySQL服务器发送一条查询请求；
  • 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入一下阶段；
  • 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划；
  • MySQL 根据执行计划，调用存储引擎的API来执行查询；
  • 将结果返回给客户端，同时缓存查询结果。

MySQL性能优化原理 — 前篇

上一篇：SQL分组聚合查询


下一篇：常用的sql语句