MySQL的逻辑架构

MySQL的逻辑架构

• MySQL的逻辑架构（from《高性能MySQL》）

  MySQL逻辑架构，来自：高性能MySQL

MySQL的架构分为三层：

• 客户端层：
  负责连接处理、授权认证、安全服务等功能的处理
• 核心服务层：
  查询解析、分析、优化、调用内置函数等等。所有的跨存储引擎功能也在这一层实现：存储过程、触发器、视图等等
• 存储引擎：
  负责MySQL中的数据存储和提取。中间的服务层通过API与存储引擎通信，屏蔽了不同存储引擎实现上的差异

MySQL的查询过程

• 当MySQL发送一个查询请求的时候，到底做了什么？

  • MySQL查询过程

    

1. 客户端/服务器通信协议：  
在任一时刻， 要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。  
一旦一端开始发送消息，另一端要接收完整个消息才能响应它。  
服务端响应客户端请求时，客户端必须接收整个返回结果。因此在实际开发中，应该尽量保持查询简单且只返回必须的数据，这也是查询中尽量避免使用 `SELECT *` 和 `LIMIT` 的原因之一。

2. 查询缓存  
- 在查询缓存打开的情况下，解析一个查询语句的时候，MySQL会先检查这个查询缓存是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查用户权限之后，会直接返回缓存中的结果，这种情况下查询不会被解析，不会生成执行计划，更不会执行。  
- MySQL将缓存存放在一个引用表（类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。  
- 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。因为同一个函数调用返回的结果可能不同(如 `NOW()` `CURRENT_USER`等)，这样的查询结果缓存起来没有意义。
- 是缓存，就会失效，那查询缓存何时失效呢？  
MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗。除了写操作，读操作也会造成系统消耗：  
    1. 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
    2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗  

    基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。

- 如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

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

语法解析和预处理

• 查询优化
  多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。
  最优执行计划是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。
  有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

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

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

• 查询执行引擎

  • 在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。

• 返回结果给客户端

  • 即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等等。
  • 如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。
  • 结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。

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

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

性能优化建议

• Scheme设计与数据类型优化
  原则： 小而简单。越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。

• 总结几个可能容易理解错误的技巧：

  • 通常来说把可为 NULL 的列改为 NOT NULL 不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为 NOT NULL 。

  • 对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。

  • UNSIGNED 表示不允许负值，大致可以使正数的上限提高一倍。比如 TINYINT 存储范围是-128 ~ 127，而UNSIGNED TINYINT 存储的范围却是0 - 255。

  • 通常来讲，没有太大的必要使用 DECIMAL 数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT 。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用 BIGINT 存储。这样可以避免浮点数计算不准确和 DECIMAL 精确计算代价高的问题。

  • TIMESTAMP 使用4个字节存储空间，DATETIME 使用8个字节存储空间。因而，TIMESTAMP 只能表示1970 - 2038年，比 DATETIME 表示的范围小得多，而且 TIMESTAMP 的值因时区不同而不同。

  • 大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。

  • schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。

  • 大表 ALTER TABLE 非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。