MySQL体系架构

客户端

客户端连接器提供与MySQL服务器建立连接的能力，几乎支持所有主流的服务端语言，例如：Java、C、C++、Python等，各语言都是通过各自的API接口与MySQL建立连接。

MySQL Server

从上图看MySQL服务端总体上可以分为三层：连接层、SQL层和存储引擎层

连接层

当客户端连接MySQL服务器时，最先连接处理的是连接层，连接层包括通信协议、线程处理、用户名密码认证三个部分。

通信协议负责检测客户端版本是否兼容MySQL服务端。

线程处理是指每一个连接请求都会分配一个对应的线程，相当于一条SQL对应一个线程，存储客户端与数据库的连接信息。

用户名密码认证验证创建的账号和密码，以及host主机授权是否可以连接到MySQL服务器。

SQL层

SQL层包含SQL接口、解析器、查询优化器和缓存

SQL接口负责接收客户端发送过来的各种SQL命令，并将SQL命令发送到其他部分，接收其他部分返回的结果数据返回给客户端。

解析器针对 SQL 语句进行解析，判断语法是否正确。

查询优化器对 SQL 进行改写和相应的优化，并生成最优的执行计划。

缓存存储在Query Cache中，包括MySQL的表缓存，记录缓存，MySQL中的权限缓存，引擎缓存等。MySQL中的缓存能够提高数据的查询性能，如果查询的结果能够命中缓存，则MySQL会直接返回缓存中的结果信息。

存储引擎层

MySQL中的存储引擎层主要负责数据的写入和读取，与底层的文件进行交互，相当于MySQL内置的文件系统。

MySQL的5.5以及之前版本默认采用的是MyIsam引擎，在5.6版本及后续默认采用InnoDB引擎。InnoDB相较于MyIsam来说，增加了对几个重要特性的支持，包括MVCC（多版本并发控制）、事务、热备、行级锁、外键等。

系统文件

系统文件主要包括MySQL中存储数据的底层文件，与上层的存储引擎进行交互，是文件的物理存储层。其存储的文件主要有：日志文件、数据文件、配置文件、MySQL的进行pid文件和socket文件等。

日志文件

MySQL中的日志主要包括：错误日志、通用查询日志、二进制日志、慢查询日志等。

• 错误日志

  主要存储的是MySQL运行过程中产生的错误信息。执行 show variables like '%log_error%'; 可获取错误日志存储位置。

• 通用查询日志

  主要记录MySQL运行过程中的一般查询信息，执行 show variables like '%general%'; 可获取通用查询日志存储位置。

• 二进制日志

  主要记录对MySQL数据库执行的插入、修改和删除操作，并且也会记录SQL语句执行的时间、执行的时长，但是二进制日志不记录select、show等不修改数据库的SQL。主要用于恢复数据库的数据和实现MySQL主从复制。

  通过命令 show variables like '%log_bin%'; 查看二进制日志是否开启;

  通过命令 show variables like '%binlog%'; 查看二进制日志的参数;

  通过命令 show binary logs;查看日志文件;

• 慢查询日志

  慢查询主要记录的是执行时间超过指定时间的SQL语句，这个时间默认是10秒。

  通过命令 show variables like '%slow_query%'; 查看是否开启慢查询日志;

  通过命令 show variables like '%long_query_time%';查看慢查询设置的时长

数据文件

数据文件中主要包括了：db.opt文件、frm文件、MYD文件、MYI文件、ibd文件、ibdata文件、ibdata1文件、ib_logfile0和ib_logfile1文件等。

• frm文件

  存储数据表的结构信息，主要是数据表相关的元数据信息，包括数据表的表结构定义信息，每张表都会有一个frm文件。

  值得注意的是：MySQL 8.x版本中的innodb存储引擎的表没有frm文件。

• ibd文件

  存放Innodb存储引擎的数据文件和索引文件，主要存放的是独享表空间的数据和索引，每张表对应一个.ibd文件。

• ibdata文件

  存放Innodb存储引擎的数据文件和索引文件，主要存放的是共享表空间的数据和索引，所有表共用一个（或者多个）.ibdata文件，可以根据配置来指定共用的.ibdata文件个数。

• ibdata1文件

  MySQL的系统表空间数据文件，主要存储MySQL的数据表元数据、Undo日志等信息。

• ib_logfile0和ib_logfile1文件

  MySQL数据库中的Redo log文件，主要用于MySQL实现事务的持久性。如果在某个时间点MySQL发生了故障，此时如果有脏页没有写入到数据库的ibd文件中，在重启MySQL的时候，MySQL会根据Redo Log信息进行重做，将写入Redo Log并且尚未写入数据表的数据进行持久化操作。

• db.opt文件

  主要记录当前数据库使用的字符集和检验规则等信息。

• MYD文件

  MyISAM存储引擎专用的文件格式，主要存放MyISAM存储引擎数据表中的数据，每张MyISAM存储引擎表对应一个.MYD文件。

• MYI文件

  MyISAM存储引擎专用的文件格式，主要存放MyISAM存储引擎数据表的索引信息，每张MyISAM存储引擎表对应一个.MYI文件。

配置文件

用于存在MySQL所有的配置信息，在Unix/Linux环境中是my.cnf文件，在Windows环境中是my.ini文件。

pid文件

pid文件是存放MySQL进程运行时的进程号的文件，主要存在于Unix/Linux环境中，具体的存储目录可以在my.cnf文件中配置。

socket文件

socket文件和pid文件一样，都是MySQL在Unix/Linux环境中运行才会有的文件。客户端可以直接通过socket来连接MySQL。

MySQL优化

参数优化

公共参数

• max_connections = 151，同时处理最大连接数，建议设置最大连接数是上限连接数的80%左右：

• sort_buffer_size = 2M，查询排序时缓冲区大小，只对order by和group by起作用，建议增大为16M

• open_files_limit = 1024，限制MySQL服务能够同时打开的文件描述符数量，设置过低可能导致卡死。

MyISAM参数默认值

• key_buffer_size = 16M，索引缓存区大小，一般设置物理内存的30-40%

• read_buffer_size = 128K，读操作缓冲区大小，建议设置16M或32M

• query_cache_type = ON，打开查询缓存功能，用于缓存SELECT查询结果

• query_cache_limit = 1M，查询缓存限制，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖

• query_cache_size = 16M，查询缓冲区的大小，下一次有同样SELECT查询将直接从缓存池返回结果，可适当成倍增加此值

InnoDB参数默认值

• innodb_buffer_pool_size = 128M，索引和数据缓冲区大小，建议设置物理内存的70%左右

• innodb_buffer_pool_instances = 1，缓冲池实例个数，推荐设置4个或8个

• innodb_file_per_table = OFF，是否共享表空间，会影响I/O性能，建议开启独立表空间，可以实现单表在不同数据库中移动

• innodb_log_buffer_size = 8M，日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M

• innodb_flush_log_at_trx_commit = 1，关键参数，对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显

  1 2 3

  0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。 1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。 2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。

安全优化

数据库安全是项目中最重要的部分，信息泄露会造成重大事故，所以要重视安全问题，防止信息被盗取、破坏。具体建议如下：

1
2
3
4
5
6
7
8
9
10
11

数据库使用 mysql 用户启动，权限为700，登录时在命令行暴漏密码，备份脚本中如果有密码，给设置 700，属主和用户组为 mysql或root

初始删除无用的用户，只保留 root 127.0.0.1 和 root localhost，删除默认存在的 test 库

授权用户对应的主机不要用%，权限不要给 all，最小化授权，从库只给select 权限，不要给一个用户管所有的库，尽量专库专用户

清理 mysql 操作日志文件~/mysql_history

禁止开发获取到 web 连接的密码，禁止开发连接操作生产对外的库

服务器禁止设置外网IP