mysql逻辑架构

mysql原理 - 图1

  1. 1).Connectors
  2. MySQL首先是一个网络程序，其在TCP之上定义了自己的应用层协议。所以要使用MySQL，我们可以编写代码，跟MySQL Server建立TCP连接，之后按照其定义好的协议进行交互。当然这样比较麻烦，比较方便的办法是调用SDK，比如Native C API、JDBC、PHP等各语言MySQL Connector，或者通过ODBC。但通过SDK来访问MySQL，本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互。
  4. 2).Connection Management
  5. 每一个基于TCP的网络服务都需要管理客户端链接，MySQL也不例外。MySQL会为每一个连接绑定一个线程，之后这个连接上的所有查询都在这个线程中执行。为了避免频繁创建和销毁线程带来开销，MySQL通常会缓存线程或者使用线程池，从而避免频繁的创建和销毁线程。
  6. 客户端连接到MySQL后，在使用MySQL的功能之前，需要进行认证，认证基于用户名、主机名、密码。如果用了SSL或者TLS的方式进行连接，还会进行证书认证。
  8.  3).SQL Interface
  9. MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口。
  11. 4).Parser
  12. MySQL会解析SQL查询，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQl查询进行语法上的优化，进行查询重写。
  14. 5).Optimizer
  15. 语法解析和查询重写之后，MySQL会根据语法树和数据的统计信息对SQL进行优化，包括决定表的读取顺序、选择合适的索引等，最终生成SQL的具体执行步骤。这些具体的执行步骤里真正的数据操作都是通过预先定义好的存储引擎API来进行的，与具体的存储引擎实现无关。
  17. 6).Caches & Buffers
  18. MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条Select语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了。
  20. 7).Pluggable Storage Engine
  21. 存储引擎的具体实现，这些存储引擎都实现了MySQl定义好的存储引擎API的部分或者全部。MySQL可以动态安装或移除存储引擎，可以有多种存储引擎同时存在，可以为每个Table设置不同的存储引擎。存储引擎负责在文件系统之上，管理表的数据、索引的实际内容，同时也会管理运行时的Cache、Buffer、事务、Log等数据和功能。

mysql原理 - 图2

  1. Server 层包括连接器、查询缓存、分析器、优化器、执行器等，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能，比如存储过程、触发器、视图等。
  3. 存储引擎层负责数据的存储和提取。InnoDB、MyISAM、Memory 等多个存储引擎、
  5. 1.2 SERVER 层
  6. 连接器
  7. 负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的：
  9. mysql -h$ip -P$port -u$user -p
  10. 1
  11. 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接
  12. 里面的权限判断逻辑，都将依赖于此时读到的权限。对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置
  14. processlist 参数查看连接用户
  15. wait_timeout 参数设置空闲连接自动断开
  17. 在使用中要尽量减少建立连接的动作，多使用长连接
  19. 长连接导致内存占用的解决方案：
  21. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
  22. 如果用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执
  23. 行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权
  24. 限验证，但是会将连接恢复到刚刚创建完时的状态。
  25. 查询缓存
  26. MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
  27. 参数 query_cache_type 设置成 DEMAN,对默认SQL语句都不使用查询缓存，对于要使用查询缓存的语句，使用 SQL_CACHE 显示指定，例如
  29. SELECT SQL_CACHE * FROM T WHERE ID=10;
  30. 1
  31. MYSQL 8.0 后不支持查询缓存
  33. 分析器
  34. 1.词法分析：从"SELECT" 识别是查询语句，把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”
  35. 2.语法分析：根据与语法规则判断SQL语句是否满足MySQL语法
  36. 一般语法错误会提示第一个出错位置，要关注的是紧接“use near”的内容
  38. 优化器
  39. 优化器在表里有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
  41. 执行器
  42. 1.在执行之前，判断时候有执行的权限
  43. 2.命中查询缓存，在返回结果时做权限验证
  44. 3.查询会在优化器之前调用precheck验证权限
  46. 举例：
  48. SELECT * FROM T WHERE ID=10
  49. 1
  50. 执行器流程：
  51. 1.调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如
  52. 果是则将这行存在结果集中；
  53. 2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
  54. 3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
  55. 对有索引的表，第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。
  57. 在数据库慢查询日志中看到 rows_examined 字段
  59. 小结
  60. 如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in
  61. ‘where clause’” 这个错误是在我们上面提到的哪个阶段报出来的呢？
  63. 2.日志系统：一条SQL更新语句是如何执行的？
  64. 2.1 更新语句的执行流程
  65. UPDATE T  SET  c=c+1 WHERE ID=2;
  66. 1
  67. 1.先连接器连接
  68. 2.分析器通过词法和语法分析是一条更新语句
  69. 3.优化器决定要是使用ID的索引

B+Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度

mysql原理 - 图3

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 10^3 10^3 = 10亿 条记录

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据

一、Mysql索引主要有两种结构：B+Tree索引和Hash索引

(a) Inodb存储引擎 默认是 B+Tree索引
(b) MyISAM 存储引擎 默认是Fulltext索引；
(c)Memory 存储引擎 默认 Hash索引；

Hash索引
mysql中，只有Memory(Memory表只存在内存中，断电会消失，适用于临时表)存储引擎显示支持Hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B+Tree索引。Hash索引把数据以hash形式组织起来，因此当查找某一条记录的时候，速度非常快。但是因为hash结构，每个键只对应一个值，而且是散列的方式分布。所以它并不支持范围查找和排序等功能。

B+Tree索引
B+Tree是mysql使用最频繁的一个索引数据结构，是Inodb和Myisam存储引擎模式的索引类型。相对Hash索引，B+Tree在查找单条记录的速度比不上Hash索引，但是因为更适合排序等操作，所以它更受欢迎。毕竟不可能只对数据库进行单条记录的操作。
带顺序访问指针的B+Tree

B+Tree所有索引数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都有指向相邻叶子节点的指针。
这样做是为了提高区间效率，例如查询key为从18到49的所有数据记录，当找到18后，只要顺着节点和指针顺序遍历就可以以此向访问到所有数据节点，极大提高了区间查询效率。
大大减少磁盘I/O读取
数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点需要一次I/O就可以完全载入

提到的“四个脚本”的方法，我非常推崇。这四个脚本分别是：备份脚本、

执行脚本、验证脚本和回滚脚本。如果能够坚持做到，即使出现问题，也是可以很快恢复的，一定能降低出现故障的概率。

参考文章：

http://blog.codinglabs.org/articles/theory-of-mysql-index.html
https://blog.csdn.net/weixin_38990431/article/details/89050101 一条SQL查询语句是如何执行的？