数据库3-架构体系（包含LnnoDB，MyISAM，数据库日志文件说明），数据库设计（包含E-R概念模型转化成特定DBMS所支持的数据模型的过程）

一 架构体系

 

从上面的架构图，我们可以看出MySQL的架构自上向下大致可以分为连接层、服务层、引擎层和文件层四大部分。对外暴露的是连接层，连接层调用SQL查询接口，交给服务层。

 

1 连接层：

位于整个MySQL体系架构的最上层，主要担任客户端连接器的角色。提供与MySQL服务器建立连接的能力，几乎支持所有主流的服务端语言，例如：Java、C、C++、Python、PHP、Go等，各语言都是通过各自的API接口与MySQL的连接层建立连接。连接层包括通信协议、线程处理、用户名密码认证等。

2 服务层：

是整个数据库服务器的核心，在 MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断，SQL接口，解析器，查询优化器(自动优化SQL以匹配索引)， 缓存查询的处理以及部分内置函数执行(如日期,时间,数学运算,加密)等等。往下一层的各个存储引擎提供的功能都集中在这一层操作，如存储过程，触发器，视图等。

 

名称	说明
系统管理和控制工具（Management Services & Utilities）	提供数据库系统的管理和控制功能，例如对数据库中的数据进行备份和恢复， 保证整个数据库的安全性，提供安全管理，对整个数据库的集群进行协调和管理等。
SQL接口（SQL Interface）	用来接收客户端发送的SQL命令，并将SQL命令发送到其他部分，返回用户需要查询的结果。 例如 SELECT、FROM 就是调用 SQL 接口。
解析器（Parser）	也叫解析树，在SQL命令传递到解析器的时候会被解析器验证和解析成一棵“解析树”， 然后根据MySQL中的一些语法规则对“解析树”做进一步的语法验证与识别数据结构，确认SQL命令是否合法。
查询优化器（Optimizer）	SQL 语句在真正的查询操作之前会使用查询优化器对SQL查询语句进行优化， 同时验证用户是否有权限进行查询，缓存中是否有可用的最新数据，查询优化器使用“选取-投影-连接”策略进行查询。 例如 SELECT id, name FROM student WHERE sex = "女";中， SELECT查询先根据WHERE子句进行选取，而不是将表全部查询出来以后再进行 sex 过滤。 SELECT 查询先根据 id 和 name 进行属性投影，而不是将属性全部取出以后再进行过滤， 将这两个查询条件连接起来生成最终查询结果。
缓存（Caches）	如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。 这个缓存机制是由一系列小缓存组成的，比如表缓存、记录缓存、key 缓存、权限缓存等。 在实际开发中，我们一般不适用缓存，所以在mysql8.0以后，实际上这个部分已经逐步被移除了。当然，即便在低版本下，我们也是关闭缓存的。

 

3 引擎层

引擎层主要提供了各种存储引擎（Storage Engine），存储引擎的主要作用是接收上层传下来的指令，负责数据的写入和读取，与底层的表文件进行交互，不同的存储引擎保存的数据格式不一样，使用场景也不一样。MySQL中的存储引擎是插件式的，服务器中的查询执行引擎通过相关的接口与存储引擎进行通信，同时接口屏蔽了不同存储引擎之间的差异。

因为在关系数据库中，数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作表的类型，除了mysql以外，很多其他数据库不叫存储引擎，而是叫表类型）。

 

-- 查看MySQL中支持的存储引擎
show engines;

MySQL中最常用的存储引擎就是InnoDB与MyISAM。

因为Memcached、Redis等内存缓存系统的出现，所以Momory存储引擎现今使用非常少了，MySQL5.5版本之后开始采用InnoDB为默认存储引擎，之前版本默认的存储引擎为MyISAM。

 

3.1 lnnoDB

InnoDB存储引擎，是MySQL当前版本（5.5以后）的默认存储引擎，具有支持事务处理（transaction），外键约束（foreign key），行锁设计，崩溃恢复历史回滚 等特点，是mysql最重要和使用最广泛的存储引擎。innoDB保存表的数据，索引与表结构都在一个ibd文件中。

事务处理	事务可以让多条写操作（添加、删除、修改）的SQL语句以一个整体的方式在mysql内部执行，保证多条SQL语句要么一起执行成功，要么一起执行失败！
外键约束	也叫主外键关联，指代mysql中提供一个监控数据表与表之间进行级联绑定的索引
行锁设计	所谓的锁，就是基于锁的机制，对数据表中的数据进行锁定，保证数据在写入(添加，更新，删除)的时候，不会因为并发导致出现一致性的问题。InnoDB中提供的锁有表级锁，行级锁。 表级锁，指在同一时间内，客户端连接修改一个表数据时，会把当前表进行锁定，其他客户端连接访问操作当前表时会阻塞等待。 行级锁，指在同一时间内，客户端连接修改一个表数据时，会把当前要修改的数据所在那一行数据进行锁定，其他客户端的其他客户端连接访问操作当前表的其他行数据畅通无阻，但修改同一行数据时会阻塞等待。 不管是表级锁还是行级锁，都是针对SQL语句中的写入命令生效，读取数据没有影响。
崩溃恢复	innoDB存储引擎在数据操作时，针对所执行的所有SQL语句，都会记录到一个redo日志文件中。当mysql因为意外而出现系统奔溃或宕机了，那么在mysql重启以后，mysql内部会自动通过redo日志对比丢失的数据，并进行恢复（历史回滚）。

 

3.2 MyISAM

MyISAM存储引擎，是MySQL早期版本（5.5以前）的默认存储引擎，拥有较高的插入、查询速度，表锁设计，支持全文索引的特点，但不支持事务处理和外键约束，也不支持崩溃恢复。MyISAM用3个文件来存储数据，frm文件存储表的定义、MYD文件存放数据、MYI文件存放索引。mysql8.0以后不再使用frm保存数据表的定义，而是叫sdi。

InnoDB与MyISAM的区别：

1. 锁设计，InnoDB支持表级锁（(table-level locking)）与行级锁（row-level locking），而myISAM只支持表级锁。用户在操作myISAM类型表时，select，update，delete，insert等语句都会给表自动加锁，导致其他客户端连接的数据操作都会被阻塞，因此并发访问受限。当然，InnoDB虽然提供了行级锁，但也只是在使用了索引时是有效的，如果没使用索引也会锁全表，行锁大幅度提高了多用户并发操作的性能。同时InnoDB还支持MVCC（Multi-Version Concurrency Control，多版本并发控制）机制，只需要很小的开销，就可以实现非锁定读，从而大大提高数据库系统的并发性能（MVCC 可以看作是行级锁的一个升级版本）。 

2. 事务安全，InnoDB支持完整的事务安全机制（ACID），具有提交(commit)和回滚(rollback)事务的能力，所以在写入数据时可以有效保证数据的安全性以及一致性。

   ACID：Atomicity(原子性)、Consistency(一致性)、Isolation(隔离性)、Durability(持久性)。后面讲到事务再说。 

3. 外键约束，MyISAM不支持，而 InnoDB 支持。但是开发中一般不在数据库使用外键，而是在应用层实现逻辑外键或虚拟外键，使用外键会造成级联更新，且级联更新是强阻塞，存在数据库更新风暴的风险；外键使用时也会影响数据库的插入速度。

   因为插入一张表的时候，mysql会自动去检查关联的外键所在表的全部数据是否一致。

4. 主键约束，MyISAM允许没有主键的表存在。InnoDB表必须有主键，如果没有设定主键，就会自动生成一个用户不可见的6字节隐藏列作为主键列(对于用户而言，MyISAM与InnoDB都可以创建没有外键的表)。

5. 崩溃恢复，MyISAM不支持，而InnoDB支持。使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log（重做日志）、undo log（回滚日志）、bin log（二进制日志）。

 

4 文件层

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

 

4.1 日志文件

日志文件主要用于记录程序运行过程中的操作历史，便于对程序的运行过程进行监控与维护。MySQL中的日志主要包括：错误日志（error log）、通用查询日志（general query log）、二进制日志（binary log）、慢查询日志（slow query log）、中继日志等。

错误日志（error log）	主要存储的是MySQL运行过程中产生的错误信息 -- mysql终端下执行 SHOW VARIABLES LIKE 'log_error';
通用查询日志（General query log）	任何执行的sql语句都会写入这个日志中。默认是关闭的，可以通过下面的SQL语句来查看MySQL中的通用查询日期的开启状态与日志存储路径 -- 查看MySQL中的通用查询日期的开启状态与日志存储路径。 SHOW VARIABLES LIKE '%general%'; -- OFF表示关闭，相当于0 -- 临时开启 set global general_log = 1; -- 开启日志， -- 1相当于'ON'，表示开启 set global general_log_file = '日志文件的绝对路径'; -- 设置日志路径，一般默认即可，不要更改路径。 -- 永久开启 -- bash终端下，sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf general_log = 1 general_log_file = /var/lib/mysql/ubuntu.log
二进制日志（binary log）	是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（并不会记录查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间。binlog 的主要目的是复制和恢复。默认关闭，但是生产环境中务必开启bin-log的。启用binlog虽然会使数据库服务器性能降低，但是binlog日志能恢复误删数据，能复制数据的这些用处而言，这点性能的损耗不算什么。 # 设置永久开启 。配置文件修改 # server-id 必须唯一 server-id=1 log-bin=mysql-bin log-bin-index=mysql-bin.index # binglog日志的最大有效期，一般不设置 # binlog_expire_logs_seconds=2592000 -- 查看二进制日志的开启情况 show variables like "%log_bin%"; -- 查看所有二进制日志文件 -- show master logs; show binary logs; -- 等价于上面一句 -- 查看mysql中最新的一个二进制日志的存储信息[常用语用于主从配置，集群配置的] show master status; -- 查看具体某个二进制日志中的记录内容 show binlog events in 'binlog.000003'; -- binlog.000003 仅仅是举例，具体要通过 show binary logs; 来查看具体二进制日志的文件名。 -- 删除指定日志文件【慎用！慎用！慎用！】 purge master logs to 'binlog.000003'; -- binlog.000003 仅仅是举例，具体要通过 show binary logs; 来查看具体二进制日志的文件名。
慢查询日志（slow query log）	记录数据库中运行时长超过指定时间（long_query_time，慢查询时间）的SQL语句，用于数据库优化。默认关闭的。 # 设置永久开启 # bash终端下，sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf slow_query_log=1 slow_query_log_file=/var/lib/mysql/ubuntu-slow.log long_query_time = 2   -- 查看慢查询日志的开启情况 show variables like '%slow_query%'; -- 设置临时开启 set global slow_query_log=1; set long_query_time=2; # mysql终端下可以通过sleep睡眠函数模拟慢查询 select sleep(10); # 查看所有慢查询SQL语句的记录信息 sudo mysqldumpslow /var/lib/mysql/ubuntu-slow.log # 得到返回记录数量最多的20个： sudo mysqldumpslow -s r -t 20 /var/lib/mysql/ubuntu-slow.log # 得到平均访问次数最多的20条： sudo mysqldumpslow -s ar -t 20 /var/lib/mysql/ubuntu-slow.log # 得到平均访问次数最多,并且里面含有 xxx字符的20条 sudo mysqldumpslow -s ar -t 20 -g "xxx" /var/lib/mysql/ubuntu-slow.log 慢日志分析工具(mysqldumpslow）参数说明

 

二 数据库设计

数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS（数据库管理系统），为这个业务系统构造出最优的数据存储模型，并建立数据库中的表结构以及表与表之间的关联关系的过程。数据库设计能有效的对项目中的数据进行存储，并可以高效的对已存储的数据进行访问。好的数据库设计可以减少数据冗余，降低数据维护成本，节约存储空间，并提供高效的访问数据能力。

 

2.1 范式理论

范式理论（normalization，规范化理论）是在关系型数据库设计过程中，为了消除重复数据减少冗余数据，从而让数据库更好更科学地组织数据，让磁盘空间得到更有效利用的一种规范化设计理论。范式理论满足高等级的范式的先决条件是满足低等级范式，其中等级最低的是第一范式（1NF），在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。

范式理论仅仅是指导我们设计好的数据库的一种指导理论，并非语法标准，所以在实际开发中，有时候为了更合理的开发出更优秀的项目，也会存在逆范式（Denormalization）设计。

第一范式（1NF）	数据表的每一列都是不可分割的基本数据项，有原子性特点。即数据表中的某个字段列不能有多个成员值。（不可分割）
第二范式（2NF）	确保表中的每个非主键列完全依赖于主键列，不能出现多行重复且与主键不完全依赖的非主键列。（不可重复）
第三范式（3NF）	确保数据表中的每一列数据都和主键列直接相关，而不能间接相关，即消除传递依赖，去除冗余字段。（不可冗余）
逆范式	通过增加冗余或重复的数据来提高数据库的读性能，减少运行时带来的查询时间消耗。（以空间换时间）使用廉价的硬盘空间来换取更快的查询数据速度。

数据表间的关系

一对一（1:1）	A表中的1条记录对应着B表中的0~1条记录，而B表中的1条记录也对应着A表中的1条记录，则A表和B表为一对一关系
一对多（1:n）	A表中的1条记录对应着B表中的0~多条记录，而B表中的1条记录只对应着A表中的1条记录，则A表和B表为一对多关系。
多对多（n:m）	A表中的1条记录对应着B表中的0~多条记录，而B表中的1条记录对应着A表中的0~多条记录，则A表和B表为多对多关系。

 

2.2 设计步骤

数据库设计的步骤一般可以分成4个阶段或6个阶段，这里我们只讨论四个阶段的情况：

需求分析阶段	数据是什么；数据具有哪些属性；数据与数据之间是否存在关联	在这个阶段中我们首先需要熟悉实际业务流程，以此分析出软件系统中需要存储的数据（实体），分析这些数据的特点（实体具有哪些属性），最后了解数据的生命周期以及数据与数据之间的联系（Relationship），也就是实体（数据表）之间的关联关系。在后续的物理设计阶段，实体就会转换成数据表保存到数据库中，属性就会转换成数据列保存到数据库中。 如：用户实体拥有属性【id、姓名、年龄、性别、手机号码】
逻辑分析阶段	使用E-R图对数据库进行逻辑建模(或抽象建模)，不需要考虑我们所选用的数据库管理系统	基于上一阶段的分析结果使用E0R图对实体、实体的属性与实体间的关系进行逻辑建模。 E-R图主要有3要素构成，要素与要素之间的从属关系以及关联关系使用实线来绑定。 常用的E-R画图工具有：亿图、visio、processon、draw.io、mastergo等。 如
物理设计阶段	根据数据自身的特点把逻辑设计转换为物理设计, 结合对应的数据库管理系统的特点，进行数据库设计	基于上一阶段的E-R概念模型，将概念模型结构转换成特定DBMS所支持的数据模型的过程。 物理模型的构建工具：navicat、workbanch、Powerdesigner等。
维护设计阶段	1.对新的需求进行建表；2.索引优化；3. 架构升级；4.大表拆分	经过了上面几个阶段设计以后，数据库应用系统经过试运行后即可投入正式运行。在数据库系统运行过程中因为时间、业务、数据量不断增长，必须不断地对其进行监控、维护、调整、优化、升级。