MYSQL-1.整体架构

整体架构

mysql从上往下依次分为：

1. 网络连接层：主要指数据库连接池，负责处理客户端接入工作；

   1. 客户端：只要能与MySQL建立网络连接，都可以被称为是MySQL的客户端，它可以是各类编程语言，如Java、Go、C/C++等，也可以是一些数据库的可视化软件，例如Navicat、SQLyog等，也可以是mysql-cli命令行工具；

2. 系统服务层：主要包含SQL接口、解析器、优化器以及缓存缓冲区四块区域；

3. 存储引擎层：这里是指MySQL支持的各大存储引擎，如InnoDB、MyISAM等；

4. 文件系统层：涵盖了所有的数据、索引文件以及日志，位于系统硬盘上；

网络连接层

1. 连接方式：TCP/IP或者Socket直连

   TCP/IP连接： mysql -h 127.0.0.1 -uroot -p123456

2. 连接建立成功后，MySQL服务端与客户端之间会建立一个session会话，之后会校验账号和密码是否正确、查找权限信息进行授权等操作；

3. 连接采用半双工模式，同一时刻内，单方要么只能发送数据，要么只能接受数据；

4. Mysql会使用一个线程维持与客户端的连接，可以通过show processlist;命令查询所有正在运行的线程（排查问题时常用）；

• Id：当前线程的ID值，可以利用这个ID，使用kill强杀线程。
• User：当前线程维护的数据库连接，与之对应的用户是谁。
• Host：与当前线程保持连接关系的客户端地址（IP+Port）。
• db：目前线程在哪个数据库中执行SQL。
• Command：当前线程正在执行的SQL类型，如：
  • Create DB：正在执行创建数据库的操作。
  • Drop DB：正在执行删除数据库的操作。
  • Execute：正在执行预编译的SQL（PreparedStatement）。
  • Close Stmt：正在关闭一个PreparedStatement。
  • Query：正在执行普通的SQL语句。
  • Sleep：正在等待客户端发送SQL语句。
  • Quit：当前客户端正在退出连接。
  • Shutdown：正在关闭MySQL服务端。
• Time：表示当前线程处于目前状态的时间，单位是秒。
• State：表示当前线程的状态，有如下几种：
  • Updating：当前正在执行update语句，匹配数据做修改操作。
  • Sleeping：正在等待客户端发送新的SQL语句。
  • Starting：目前正在处理客户端的请求。
  • Checking table：目前正在表中查询数据。
  • Locked：当前线程被阻塞，其他线程获取了执行需要的锁资源。
  • Sending Data：目前执行完成了Select语句，正在将结果返回给客户端。
• Info：一般记录当前线程正在执行的SQL，默认显示前一百个字符，查看完整的SQL可以使用show full processlist;命令。

5. 数据库连接池：主要是为了实现重用线程的目的，因为每次在为客户端分配连接对象时都需要经历创建线程、分配栈空间等工作，耗时长、资源开销大，所以使用池化技术解决这些问题；

   1. show variables like '%max_connections%';：查询目前DB的最大连接数。
   2. set GLOBAL max_connections = 200;：修改数据库的最大连接数为指定值。
   3. show status like "Threads%";统计客户端连接数；

系统服务层

MySQL大多数核心功能都位于这一层，包括客户端SQL请求解析、语义分析、查询优化、缓存以及所有的内置函数（例如：日期、时间、统计、加密函数…），所有跨引擎的功能都在这一层实现，譬如存储过程、触发器和视图等一系列服务。

1. sql接口：主要负责处理客户端的sql语句、查询缓存、查询结果集的处理等；是客户端传递sql语句时的入口，返回数据时的出口；

   1. sql语句的类型划分：

      DML：数据库操作语句，比如update、delete、insert等都属于这个分类。

      DDL：数据库定义语句，比如create、alter、drop等都属于这个分类。

      DQL：数据库查询语句，比如最常见的select就属于这个分类。

      DCL：数据库控制语句，比如grant、revoke控制权限的语句都属于这个分类。

      TCL：事务控制语句，例如commit、rollback、setpoint等语句属于这个分类。

   2. 触发器：类似与spring中的AOP，可以在某个事件执行前后插入一定的操作；

      在MySQL中支持INSERT、UPDATE、DELETE三种事件触发，同时也可以通过AFTER、BEFORE语句声明触发的时机，是在操作执行之前还是执行之后；

   3. 存储过程：是指提前编写好的一段较为常用或复杂SQL语句，然后指定一个名称存储起来，然后先经过编译、优化，完成后，这个“过程”会被嵌入到MySQL中。需要手动调用后才可执行；

2. 解析器：

   作用主要是做词法分析、语义分析、生成语法树等工作，将sql语句转换成mysql可以执行的指令；与java、go等语言编译器作用类似；

3. 优化器：

   主要职责：生成执行计划；

   在生成执行计划的过程中，会选择最合适的索引、选择合适的join方式等，最后会选择生成的执行计划中，最优的一个执行计划；

   优化器生成了执行计划后，维护当前连接的线程会负责根据计划去执行SQL，这个执行的过程实际上是在调用存储引擎所提供的API。

4. 缓存和缓冲；

   1. 读取缓存：

      1. 主要指的是select语句的数据缓存和部分权限缓存、引擎缓存等；

      2. MySQL会对于一些经常执行的查询SQL语句，将其结果保存在Cache中，因为这些SQL经常执行，因此如果下次再出现相同的SQL时，能从内存缓存中直接命中数据；

         show global variables like "%query_cache_type%";：查询缓存是否开启。
         show global variables like "%query_cache_size%";：查询缓存的空间大小。
         
         

      3. show status like'%Qcache%'; 命令查询缓存相关的统计信息;

         

         • Qcache_free_blocks：查询缓存中目前还有多少剩余的blocks。
         • Qcache_free_memory：查询缓存的内存大小。
         • Qcache_hits：表示有多少次查询SQL命中了缓存。
         • Qcache_inserts：表示有多少次查询SQL未命中缓存然后走了磁盘。
         • Qcache_lowmem_prunes：这个值表示有多少条缓存数据从内存中被淘汰。
         • Qcache_not_cached：表示由于自己设置了缓存规则后，有多少条数据不符合缓存条件。
         • Qcache_queries_in_cache：表示当前缓存中缓存的数据数量。
         • Qcache_total_blocks：当前缓存区中blocks的数量。

      4. 在高版本的MySQL中，移除了查询缓存区；

   2. 写入缓冲：

      1. 设计主要是为了通过内存的速度来弥补磁盘速度较慢对数据库造成的性能影响。

      2. 当在数据库中读取某页数据操作时，会先将从磁盘读到的页存放在缓冲区中，后续操作相同页的时候，可以基于内存操作。

         当对数据库进行写操作时，都会先从缓冲区中查询所需页，如果有，则直接对内存中的数据页进行操作（例如修改、删除等），对缓冲区中的数据操作完成后，会直接给客户端返回成功的信息，然后MySQL会在后台利用一种名为Checkpoint的机制，将内存中更新的数据刷写到磁盘；

存储引擎层

1. 负责具体的数据操作以及执行工作，也会负责库表管理、索引管理等；
2. 通过show variables like '%storage_engine%';命令来查看当前所使用的引擎;
3. MySQL中所有与磁盘打交道的工作，最终都会交给存储引擎来完成。

文件系统层

1. 本质上就是基于机器物理磁盘的一个文件系统，其中包含了配置文件、库表结构文件、数据文件、索引文件、日志文件等各类MySQL运行时所需的文件，负责数据的最终存储与持久化工作；
2. 日志模块：在MySQL中主要存在七种常用的日志类型，如下：
   1. binlog二进制日志，主要记录MySQL数据库的所有写操作（增删改）。
   2. redo-log重做/重写日志，MySQL崩溃时，对于未落盘的操作会记录在这里面，用于重启时重新落盘（InnoDB专有的）。
   3. undo-logs撤销/回滚日志：记录事务开始前[修改数据]的备份，用于回滚事务。
   4. error-log错误日志：记录MySQL启动、运行、停止时的错误信息。
   5. general-log常规日志，主要记录MySQL收到的每一个查询或SQL命令。
   6. slow-log慢查询日志，主要记录执行时间较长的SQL。
   7. relay-log中继日志，主要用于主从复制做数据拷贝。
3. 数据模块：有数据最终都会落盘（写入到磁盘），而不同的数据在磁盘空间中，存储的格式也并不相同；常见的数据文件类型：
   1. db.opt文件：主要记录当前数据库使用的字符集和验证规则等信息。
   2. .frm文件：存储表结构的元数据信息文件，每张表都会有一个这样的文件。
   3. .MYD文件：用于存储表中所有数据的文件（MyISAM引擎独有的）。
   4. .MYI文件：用于存储表中索引信息的文件（MyISAM引擎独有的）。
   5. .ibd文件：用于存储表数据和索引信息的文件（InnoDB引擎独有的）。
   6. .ibdata文件：用于存储共享表空间的数据和索引的文件（InnoDB引擎独有）。
   7. .ibdata1文件：这个主要是用于存储MySQL系统（自带）表数据及结构的文件。
   8. .ib_logfile0/.ib_logfile1文件：用于故障数据恢复时的日志文件。
   9. .cnf/.ini：MySQL的配置文件，Windows下是.ini，其他系统大多为.cnf。

SQL的执行过程

1. 诞生：

   1. 开发者自己手动编写；
   2. ORM框架自动生成：一般情况下，MySQL运行过程中收到的大部分SQL都是，根据用户请求参数，然后经过业务层编码，最后由ORM框架生成的；
      1. ORM框架：比如Java中的MyBatis、Hibernate框架等。

2. 执行前的准备：

   1. 客户端线程池：当Java在创建数据库连接时，会先去读取配置文件中的链接地址、账号密码等信息。之后根据这些信息发起网络请求去获取连接对象，开销较大；所以一般会在客户端也维护一个连接池，来复用数据库连接，解决频繁创建数据库连接带来的性能损耗；

      1. Java中的数据库连接池：C3P0、Druid、DBCP等

   2. 当网络连接建立成功后，也就等价于在MySQL中创建了一个客户端会话；

      1. 验证客户端的账号和密码是否正确；

      2. 得到一条数据库连接对象，一般从Mysql连接层中维护的线程池中获取；

         1. 判断mysql线程池中是否存在空闲连接：

            有：直接复用；没有：需要判断是否达到最大连接数；

            达到：等待线程释放；没有达到：创建一个新的数据库连接；

      3. 工作线程会先查询MySQL自身的用户权限表，获取当前登录用户的权限信息并授权。

SQL在数据库中的执行

1. 查询SQL的执行过程：

   

   1. sql接口：
      1. 接收客户端发送的sql，并根据sql的哈希值去查询缓存，若缓存存在则直接返回，没有则交由解析器处理；
      2. 对结果集的处理；
         • 筛选出所需字段；
         • 多表连接查询时数据合并工作；
   2. 解析器：
      1. 通过词法分析、语法分析、语法规则等判断sql是否正确且合规，若不正确会返回ERROR 1064 (42000): You have an error in your SQL syntax; check...错误信息；
      2. 通过关键字进行分析，解析出sql具体工作信息如：根据select、update、delete等关键词判断sql类型，根据from确定sql操作的表、根据where确定筛选条件等；
      3. 生成对应的语法树，类似于java中.java源代码生成.class字节码文件；
   3. 优化器：
      1. 针对解析器工作后得到的语法树，给出多个执行计划并选择一个执行效率最高、最省资源、最短时间的计划，交由工作线程去执行；
      2. 优化器的一些优化准则：
         • 多条件查询时，重排条件先后顺序，将效率更好的字段条件放在前面。
         • 当表中存在多个索引时，选择效率最高的索引作为本次查询的目标索引。
         • 使用分页Limit关键字时，查询到对应的数据条数后终止扫表。
         • 多表join联查时，对查询表的顺序重新定义，同样以效率为准。
         • 对于SQL中使用函数时，如count()、max()、min()…，根据情况选择最优方案。
           • max()函数：走B+树最右侧的节点查询（大的在右，小的在左）。
           • min()函数：走B+树最左侧的节点查询。
           • count()函数：如果是MyISAM引擎，直接获取引擎统计的总行数。
         • 对于group by分组排序，会先查询所有数据后再统一排序，而不是一开始就排序。
      3. 虽然有优化器，但对效率影响最大的还是sql本身；
   4. 存储引擎：
      1. 根据优化器生成的执行计划，工作线程会调用存储引擎提供的API，来进行磁盘数据的读写操作，得到sql的执行结果（执行状态、接口耗时、数据等）；
      2. 存储引擎从磁盘中检索出目标数据后，并不会将所有数据全部得到后再返回，而是会逐条返回给SQL接口，避免撑满内存；

2. 写入sql的执行过程：

   

   1. sql接口：与查询类似，不同点在于对缓存的操作由查询变为了删除（确保缓冲的强一致性）；

   2. 解析器：同查询；

   3. 优化器：同查询；

   4. 日志记录：记录undo-log日志和redo-log（prepare）日志；

      1. undo-log日志：记录回滚sql，用于事物回滚操作；

      2. redo-log日志：InnoDB专属，记录事物的执行过程，用于服务器或者mysql宕机时的数据恢复，存在prepare和commit两个状态，与bin-log形成两阶段提交，主要保证事物的原子性和持久性；

         存在redo-log缓冲区，异步刷写进磁盘，由innodb_flush_log_at_trx_commit参数控制；

         • 0: 有事物提交的情况下，每间隔一秒刷入一次磁盘；
         • 1: 每次事物提交时，刷入磁盘（性能最差，最安全、默认）；
         • 2: 每次事物提交时，将日志放入内核缓冲区，由操作系统控制刷写时机（性能最佳）；

      3. bin-log日志：mysql自带的日志，记录数据库执行的操作信息，以二进制的形式保存在磁盘中；

         存在bin-log缓冲区，由sync_binlog参数控制。

         • 0: 同上述参数2（默认）;
         • 1: 同上述参数1；

   5. 缓冲区（BufferPool）：在缓冲区（内存）中查找是否存在当前需要操作的行记录或者表数据；

      • 存在：直接对缓冲区的数据进行操作，后续通过checkpoint机制将数据刷入磁盘中；

   6. 存储引擎：调用存储引擎API对磁盘中的数据进行写操作；

   7. 日志记录：记录bin-log日志，同时将redo-log日志中的记录改为commit状态(两阶段提交)；

   8. 将执行结果（执行状态、耗时、受影响行数）返回给SQL接口；

3. 执行结果返回客户端

   1. 结果集处理好了之后，直接将结果集封装成TCP数据报，然后根据Host（show processlist; 查看）中记录的地址，然后返回即可；