体系结构
①通过客户端/服务器通信协议与 MySQL 建立连接。
②查询缓存，这是 MySQL 的一个可优化查询的地方，如果开启了 Query Cache 且在查询缓存过程中查询到完全相同的 SQL 语句，则将查询结果直接返回给客户端；如果没有开启Query Cache 或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成解析树。
③预处理器生成新的解析树。
④查询优化器生成执行计划。
⑤查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的 API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由MySQL Server 过滤后将查询结果缓存并返回给客户端。若开启了 Query Cache，这时也会将SQL 语句和结果完整地保存到 Query Cache 中，以后若有相同的 SQL 语句执行则直接返回结果。
存储引擎概述
存储引擎是 MySQL 中具体与文件打交道的子系统，它是根据 MySQL AB 公司提供的文件访问层抽象接口定制的一种文件访问机制，这种机制就叫作存储引擎，下面是一些常用的存储引擎，有远古时期的 MyISAM、支持事务的 InnoDB、内存类型的 Memory、归档类型的 Archive、列式存储的 Infobright，以及一些新兴的存储引擎，以 RocksDB 为底层基础的 MyRocks 和 RocksDB，和以分形树索引组织存储的 TokuDB，当然现在还有极数云舟出品的分布式存储引擎 ArkDB，如下图所示。
在 MySQL 5.6 版本之前，默认的存储引擎都是 MyISAM，但 5.6 版本以后默认的存储引擎就是 InnoDB 了。
InnoDB 存储引擎的具体架构如下图所示。上半部分是实例层（计算层），位于内存中，下半部分是物理层，位于文件系统中。
用户读取或者写入的最新数据都存储在 Buffer Pool 中，如果 Buffer Pool 中没有找到则会读取物理文件进行查找，之后存储到 Buffer Pool 中并返回给 MySQL Server。Buffer Pool 采用LRU 机制，具体的内存队列和刷新机制建议你课后学习了解下，这里不详细讲述。
Buffer Pool 决定了一个 SQL 执行的速度快慢，如果查询结果页都在内存中则返回结果速度很快，否则会产生物理读（磁盘读），返回结果时间变长，性能远不如存储在内存中。但我们又不能将所有数据页都存储到 Buffer Pool 中，比如物理 ibd 文件有 500GB，我们的机器不可能配置能容得下 500GB 数据页的内存，因为这样做成本很高而且也没必要。在单机单实例情况下，我们可以配置 Buffer Pool 为物理内存的 60%~80%，剩余内存用于 session 产生的 sort 和 join 等，以及运维管理使用。如果是单机多实例，所有实例的buffer pool总量也不要超过物理内存的80%。开始时我们可以根据经验设置一个 Buffer Pool 的经验值，比如 16GB，之后业务在 MySQL 运行一段时间后可以根据 show global status like ‘%buffer_pool_wait%’ 的值来看是否需要调整 Buffer Pool 的大小。
若参数 innodb_file_per_table=ON，则表示用户建表时采用用户独立表空间，即一个表对应一组物理文件，.frm 表定义文件和 .ibd 表数据文件。
当然若这个参数设置为 OFF，则表示用户建表存储在 ibdata 文件中，不建议采用共享表空间，这样会导致 ibdata 文件过大，而且当表删除后空间无法回收。独立表空间可以在用户删除大量数据后回收物理空间，执行一个 DDL 就可以将表空间的高水位降下来了。
MyISAM和InnoDB的区别
- MyISAM表的存储文件为3个 InnoDB为2个
- MyISAM不支持事物 innodb支持
- MyISAM不支持外键 innodb支持
- MyISAM表的查询效率高于innodb 但是innodb的安全性高于MyISAM
- InnoDB 支持 4 种事务隔离级别，默认是可重复读 Repeatable Read 的，MyISAM 不支持
MyISAM文件
- .frm文件 存储当前表结构的文件
在innodb和MyISAM中都存在 - .MYD: 即MY DATA 存储表数据的文件
- .MYI:即 MY INDEX 存储表索引的文件
InnoDB文件
- .frm文件 存储当前表结构的文件
在innodb和MyISAM中都存在 - .ibd 存储表数据和索引
ARIES 三原则，是指 Write Ahead Logging（WAL）。
- 先写日志后写磁盘，日志成功写入后事务就不会丢失，后续由 checkpoint 机制来保证磁盘物理文件与 Redo 日志达到一致性；
- 利用 Redo 记录变更后的数据，即 Redo 记录事务数据变更后的值；
- 利用 Undo 记录变更前的数据，即 Undo 记录事务数据变更前的值，用于回滚和其他事务多版本读。
事务
MySQL 中默认的事务隔离级别是 RR
高性能数据表如何设计
数据库三范式
第一范式
第一范式无重复的列，表中的每一列都是拆分的基本数据项，即列不能够再拆分成其他几列，强调的是列的原子性.。
如果在实际场景中，一个联系人有家庭电话和公司电话，那么以“姓名、性别、电话”为表头的表结构就没有达到 1NF。要符合 1NF 我们只需把电话列拆分，让表头变为姓名、性别、家庭电话、公司电话即可。
第二范式
第二范式属性完全依赖于主键，首先要满足它符合 1NF，另外还需要包含两部分内容：
表必须有一个主键；
没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。即要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
第三范式
第三范式属性不传递依赖于其他非主属性，首先需要满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
第二范式和第三范式的区别
第二范式：非主键列是否依赖主键（包括一列通过某一列间接依赖主键），要是有依赖关系就是第二范式；
第三范式：非主键列是否直接依赖主键，不能是那种通过传递关系的依赖。要是符合这种依赖关系就是第三范式。
通过对前三个范式的了解，我们知道 3NF 是 2NF 的子集，2NF 是 1NF 的子集。
范式与反范式
范式化模型：
数据没有冗余，更新容易；
当表的数量比较多，查询数据需要多表关联时，会导致查询性能低下。
反范式化模型：
冗余将带来很好的读取性能，因为不需要 join 很多表；
虽然需要维护冗余数据，但是对磁盘空间的消耗是可以接受的。
Mysql使用原则和设计规范
确保存储和计算分离
尽量单表查询，减少跨表查询
杜绝大事务，大sql、大批量、大字段
- 大事务，运行步骤较多，涉及的表和字段较多，容易造成资源的争抢，甚至形成死锁。一旦事务回滚，会导致资源占用时间过长。
- 大 SQL，复杂的 SQL 意味着过多的表的关联，MySQL 数据库处理关联超过 3 张表以上的 SQL 时，占用资源多，性能低下。
- 大批量，意味着多条 SQL 一次性执行完成，必须确保进行充分的测试，并且在业务低峰时段或者非业务时段执行。
- 大字段，blob、text 等大字段，尽量少用。必须要用时，尽量与主业务表分离，减少对这类字段的检索和更新
使用UTF8mb4字符集
关闭区分大小写功能，设置 lower_case_tables_name=1，即可关闭区分大小写功能，即大写字母 T 和小写字母 t 一样。
根据业务场景需求，选择合适的类型，最短的长度；确保字段的宽度足够用，但也不要过宽。所有字段必须为 NOT NULL，空值则指定 default值，空值难以优化，查询效率低。比如：人的年龄用 unsigned tinyint（范围 0~255，人的寿命不会超过 255 岁）；海龟就必须是 smallint，但如果是太阳的年龄，就必须是 int；如果是所有恒星的年龄都加起来，那么就必须使用 bigint。
禁用 ENUM、SET 类型。
兼容性不好，性能差。
解决方案：使用 TINYINT，在 COMMENT 信息中标明被枚举的含义。is_disable TINYINT UNSIGNED DEFAULT ‘0’ COMMENT ‘0:启用 1:禁用 2:异常’。
禁用列为 NULL。
MySQL 难以优化 NULL 列；
NULL 列加索引，需要额外空间；
含 NULL 复合索引无效。
解决方案：在列上添加 NOT NULL DEFAULT 缺省值。
禁止 VARBINARY、BLOB 存储图片、文件等。
禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统中，数据库中存储路径。
不建议使用 TEXT/BLOB：
处理性能差；
行长度变长；
全表扫描代价大。
解决方案：拆分成单独的表。
主键列，无负数，建议使用 INT UNSIGNED 或者 BIGINT UNSIGNED；预估字段数字取值会超过 42 亿，使用 BIGINT 类型。
短数据使用 TINYINT 或 SMALLINT，比如：人类年龄，城市代码。
使用 UNSIGNED 存储非负数值，扩大正数的范围。
附录
undo log和redo log
https://www.cnblogs.com/xinysu/p/6555082.html
ACID
若有收获，就点个赞吧
0 人点赞
