Mysql架构概览

---

一、物理架构

• MySQL是通过文件系统对数据和索引进行存储的。

• MySQL从物理结构上可以分为日志文件和数据索引文件。

• 日志文件采用尾部追加，因此是顺序IO方式存储。

• Mysql的数据最终都会刷盘到硬盘中，刷盘分为顺序IO和随机IO，二者查询性能相差较大，因此通常会将随机IO优化为顺序IO来提高查询效率。

• 顺序IO:读写操作访问连续地址，在磁盘中找到数据后，不需要更换磁道或者扇区，HDD所需的磁道搜索时间显着减少，读/写磁头可以以最小的移动访问下一个块。

• 随机IO:与顺序IO对应的就是访问地址不连续，每次IO都需要重新寻址。耗费时间较长。

1、数据文件

执行命令查看data数据文件存放位置。

mysql> SHOW variables like '%datadir%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)

进入数据路径

[root@VM-0-8-centos data]# ll
total 123104
-rw-r----- 1 mysql mysql       56 Feb 15 16:25 auto.cnf
-rw------- 1 mysql mysql     1680 Feb 15 16:25 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Feb 15 16:25 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Feb 15 16:25 client-cert.pem
-rw------- 1 mysql mysql     1676 Feb 15 16:25 client-key.pem
-rw-r----- 1 mysql mysql   152632 Feb 20 05:44 error.log
-rw-r----- 1 mysql mysql      349 Feb 15 17:40 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 15 17:40 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 15 17:40 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Feb 15 16:25 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 15 17:40 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Feb 15 17:37 mandone
drwxr-x--- 2 mysql mysql     4096 Feb 15 16:25 mysql
drwxr-x--- 2 mysql mysql     4096 Feb 15 16:25 performance_schema
-rw------- 1 mysql mysql     1676 Feb 15 16:25 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Feb 15 16:25 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 Feb 15 16:25 server-cert.pem
-rw------- 1 mysql mysql     1676 Feb 15 16:25 server-key.pem
drwxr-x--- 2 mysql mysql    12288 Feb 15 16:25 sys
-rw-r----- 1 mysql mysql        5 Feb 15 17:40 VM-0-8-centos.pid

mysql、mandone、sys等均为数据库名，会生成对应的数据文件夹，选择mandone数据库

[root@VM-0-8-centos mandone]# ll
total 128
-rw-r----- 1 mysql mysql    65 Feb 15 16:41 db.opt
-rw-r----- 1 mysql mysql  8716 Feb 15 17:37 runoob_tbl.frm
-rw-r----- 1 mysql mysql 98304 Feb 20 07:02 runoob_tbl.ibd
-rw-r----- 1 mysql mysql  8654 Feb 20 07:04 tb_emp1.frm
-rw-r----- 1 mysql mysql     0 Feb 20 07:04 tb_emp1.MYD
-rw-r----- 1 mysql mysql  1024 Feb 20 07:04 tb_emp1.MYI

文件名前缀为表名，使用不同的存储引擎会生成不同的对应文件，其中runoob_tbl使用的是innodb存储引擎，tb_emp1使用的是MyIsam存储引擎；

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

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

2、日志文件

查询所有日志：

show variables like 'log_%';
+----------------------------------------+---------------------------------------+
| Variable_name                          | Value                                 |
+----------------------------------------+---------------------------------------+
| log_bin                                | ON                                    |
| log_bin_basename                       | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                          | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                                   |
| log_bin_use_v1_row_events              | OFF                                   |
| log_builtin_as_identified_by_password  | OFF                                   |
| log_error                              | /usr/local/mysql/data/logs/error.log  |
| log_error_verbosity                    | 3                                     |
| log_output                             | FILE                                  |
| log_queries_not_using_indexes          | OFF                                   |
| log_slave_updates                      | OFF                                   |
| log_slow_admin_statements              | OFF                                   |
| log_slow_slave_statements              | OFF                                   |
| log_statements_unsafe_for_binlog       | ON                                    |
| log_syslog                             | OFF                                   |
| log_syslog_facility                    | daemon                                |
| log_syslog_include_pid                 | ON                                    |
| log_syslog_tag                         |                                       |
| log_throttle_queries_not_using_indexes | 0                                     |
| log_timestamps                         | UTC                                   |
| log_warnings                           | 2                                     |
+----------------------------------------+---------------------------------------+
21 rows in set (0.00 sec)

• 开启binlog日志:
  修改文件 /etc/my.cnf
  在[mysqld]后面直接添加
  log-bin = mysql-bin
  server-id = 1
• 开启慢查询日志：
  配置查询

mysql> show variables like 'slow_query%';
+---------------------+----------------------------------------------+
| Variable_name       | Value                                        |
+---------------------+----------------------------------------------+
| slow_query_log      | OFF                                          |
| slow_query_log_file | /usr/local/mysql/data/VM-0-8-centos-slow.log |
+---------------------+----------------------------------------------+
2 rows in set (0.00 sec)

##开启
mysql> set global slow_query_log = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'slow_query%';
+---------------------+----------------------------------------------+
| Variable_name       | Value                                        |
+---------------------+----------------------------------------------+
| slow_query_log      | ON                                           |
| slow_query_log_file | /usr/local/mysql/data/VM-0-8-centos-slow.log |
+---------------------+----------------------------------------------+
2 rows in set (0.01 sec)

• 错误日志：
  错误日志记录了运行过程中遇到的所有，严重的错误信息,以及 MySQL每次启动和关闭的详细信息。
• 通用查询日志：

mysql> show variables like '%general_log%';
+------------------+-----------------------------------------+
| Variable_name    | Value                                   |
+------------------+-----------------------------------------+
| general_log      | OFF                                     |
| general_log_file | /usr/local/mysql/data/VM-0-8-centos.log |
+------------------+-----------------------------------------+
2 rows in set (0.00 sec)
##开启
mysql> set global general_log = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%general_log%';
+------------------+-----------------------------------------+
| Variable_name    | Value                                   |
+------------------+-----------------------------------------+
| general_log      | ON                                      |
| general_log_file | /usr/local/mysql/data/VM-0-8-centos.log |
+------------------+-----------------------------------------+
2 rows in set (0.00 sec)

二、逻辑架构

1、连接器

用于数据库的连接以及权限管理，该连接使用tcp三次握手连接，后面开始进行对用户名和密码校验，密码错误或者如果/etc/my.cnf 中没有加–skip-grant-tables配置而没有输入密码的情况下，都会提示Access denied for user ‘root’@‘localhost’。
密码验证成功后，会从用户权限表中查到当前用户的权限，并且在后面的连接中，该权限都不会变，即使中间有对权限做变更操作，只要连接不中断，角色权限都不会变。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

• 使用长连接的弊端：
  可能会导致内存暴涨：在长连接中产生的数据都是基于内存存储，只有连接中断后才会释放这些内存，因此会导致OOM，最终会致使Mysql异常重启。
• 解决方案：
  1、更改程序，如果发现占用大内存的语句执行后重新连接一次。
  2、在 Mysql5.7之后，新增了mysql_reset_connection参数，执行该才做可释放内存切不需要重新连接以及权限校验。

连接操作，执行show processlist 操作可以看见，其中如果长时间没有做后续操作，在processlist，该连接就会处于空闲状态sleep

mysql> -h$ip -P$port -u$user -p
mysql> show processlist;
+----+------+----------------------+---------+---------+------+----------+------------------+
| Id | User | Host                 | db      | Command | Time | State    | Info             |
+----+------+----------------------+---------+---------+------+----------+------------------+
|  2 | root | localhost            | mandone | Query   |    0 | starting | show processlist |
|  3 | root | localhost            | mandone | Sleep   |  537 |          | NULL             |
+----+------+----------------------+---------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

在创建连接后，开始执行sql语句。在查询的时候，如果不开启缓存，那么就会先去缓存查询。在上一条sql查询语句中，查询的结果会以key-value的形式缓存在内存中。key是查询语句，value是查询的结果，如果缓存中存在这个key值，就可以直接返回查询结果。虽然缓存看似很有效的提升了查询速度，但是用过缓存的都知道，缓存只有在大量查询的数据都是静态不变的时候才会很有效的改善性能，sql查询也是如此，如果是一张几乎不会改动的数据表可以考虑开启缓存。

• 缓存相关配置变更：
  修改etc/my.cnf文件中mysqld后面添加配置:query_cache_type=1(一定要加在mysqld后面，否则不生效)

## 2、查询缓存
mysql> show variables like "%query_cache%";
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)

此外Mysql提供了按需查询缓存的功能
set global query_cache_type = ‘DEMAND’;默认的语句不使用缓存查询，如果需要使用缓存可以自行在sql语句中添SQL_CACHE

mysql> select SQL_CACHE * from runoob_tbl;

注：Mysql 8.0之后已经删除该功能。

3、分析器

• 词法分析：
  将sql语句中所有的字符串与具有sql语义的字符串映射起来。例如词语中runoob_tbl映射成表名runoob_tbl。
• 语法分析：
  判断该sql语句是否满足规定的语法。包括字段名是否存在等。如果存在错误语法则报错You have an error in your SQL syntax。

4、优化器

• 优化器
  在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

5、执行器

• 先进行表权限判断，判断该用户在该表上所具有的权限，例如只可以执行DML，而对于一些敏感的DDLsql语句则需要有更高权限的用户来执行（如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限）。
• 调用存储引擎接口执行，将查询的数据存放到结果集中，最终返回。

6、存储引擎

主要负责提供接口给执行器来调用，目前主流的存储引擎有:InnoDb,MyIsam,Memory。

总结

该篇文章主要是对Mysql的架构包括物理架构以及逻辑架构进行了一个简单的描述。并无过于深入的研究和讨论。Mysql是一个很庞大的体系，学习Mysql的架构对于后面的深入研究会有很大的帮助，在这里我列出了暂时想到的学习目录(可能会有改动)由浅到深，逐步击破。

• Mysql事务
• Mysql锁机制
• 索引以及查询优化
• Mysql 日志文件(binlog与redolog)
• mybatis框架
• mybatis plus以及tkmybatis使用
• mybatis 插件原理以及实战
• Mysql 分表分库实现
• 集群搭建
• 高可用架构设计

引用

1、Mysql实战45讲
2、高性能Mysql
3、Mysql官方手册

---

文章仅用于学习记录分享，如有问题，感谢纠正！！！