MySQL日志和存储引擎简单分析篇

一.MySQL文件结构

• MySQL是通过文件系统对数据和索引进行存储的。
• MySQL从物理结构上可以分为日志文件和数据索引文件。

1.1 MySQL服务器日志

         MySQL通过日志记录了数据库操作信息和错误信息。常用的日志文件包括错误日志（Error log）、二进制日志(Binary log)、查询日志(General query log)、慢查询日志(Slow query log) 、重做日志(Redo log)、 回滚日志（Undo log）、中继日志(Relay log) 、DDL日志（DDL log ）等。

• 错误日志（Error log）

        默认是开启的，而且从5.5.7以后无法关闭错误日志，错误日志记录了运行过程中遇到的所有严重的错误信息,以及 MySQL每次启动和关闭的详细信息。错误日志所记录的信息是可以通过log-error和log-warnings来定义的，是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。

•  二进制日志（Binary log）

       bin log记录了数据库所有的ddl语句和dml语句，但不包括select语句，语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息。如果是DDL语句，则直接记录到 binlog日志，而DML语句，必须通过事务提交才能记录到bin log日志中。

      bin log主要用于实现mysql主从复制、数据备份、数据恢复。

• 查询日志（General query log） 

       默认情况下通用查询日志是关闭的。 由于通用查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不 要开启查询日志。

• 慢查询日志（Slow query log）

    慢查询日志由SQL语句组成，long_query_time执行耗时超过几秒钟的查询。

    默认是关闭的

    #开启慢查询日志

    slow_query_log=ON

    #慢查询的阈值

    long_query_time=10

• 重做日志（Redo log）

       确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。事务开始之后产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。当对应事务的脏页写入到磁盘之后，redo log的任务也就完成了，重做日志占用的空间就可以重用了。

• 回滚日志（Undo log）

       保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC）。事务开始之前产生，事务提交之后释放。undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

• 中继日志 (Relay log)

       中继日志与二进制日志一样，由一组编号文件和一个索引文件组成，其中编号文件包含描述数据库更改的事件，而索引文件则包含所有使用的中继日志文件的名称。中继日志用于主从复制架构中的从服务器上，从服务器的 slave 进程从主服务器处获取二进制日志的内容并写入中继日志，然后由 IO 进程读取并执行中继日志中的语句。

• DDL日志（DDL log）

      数据定义语句生成的元数据操作。 

1.2 数据文件

       InnoDB数据文件

• .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息 。
• .ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件。
• ibdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件。

       MyIsam数据文件

• .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
• .myd文件：主要用来存储表数据信息。
• .myi文件：主要用来存储表数据文件中任何索引的数据树。

  1.3 逻辑架构图

    

 

1.Connectors（连接器）

连接器，指的是不同语言中与SQL的交互。

2.Management Serveices & Utilities

系统管理和控制工具。

3.Connection Pool（连接池）

• 管理用户连接，等待处理连接请求。
• 负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每 一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。
• 而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的cache 等。

4.SQL Interface（SQL接口）

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface。

5.Parser（解析器）

SQL命令传递到解析器的时候会被解析器验证和解析。

主要功能：

• 将SQL语句进行词法分析和语法分析，解析成语法树，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
• 如果在分解过程中遇到错误，那么就说明这个sql语句是不合理的。

6.Optimizer（查询优化器）

SQL语句在查询之前会使用查询优化器对查询进行优化。explain语句查看的SQL语句执行计划，就是由查询优化器生成的。

7.Cache和Buffer（查询缓存）

它的主要功能是将客户端提交给MySQL的select请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中，Query Cache对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

8.Pluggable Storage Engines（存储引擎）

与其它数据库例如Oracle 和SQL Server等数据库中只有一种存储引擎不同的是，MySQL有一个被称为 “Pluggable Storage Engine Architecture”(可插拔的存储引擎架构)的特性，也就意味着MySQL数据库提供了多种存储引擎。而且存储引擎是针对表的，用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。也就是说，同一数据库不同的表可以选择不同的存储引擎简而言之，存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

二.MySQL存储引擎

   1.MySQL存储引擎种类

            

 

    2.InnoDB和MyISAM存储引擎区别

            

    3.引擎的选择

     除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该选择InnoDB引擎。

 MySQL 执行计划分析篇：https://blog.csdn.net/Yi_Ran_2018/article/details/115402253