配置与安装¶

基本操作¶

启动停止

```shell net start mysql80 net stop mysql80

linux¶

sudo service mysql start

- 连接到数据库

```shell
    mysql [-h 127.0.0.1] [-P 3306] -u root -p
    参数：
    -h : MySQL服务所在的主机IP(省略默认为本地)
    -P : MySQL服务端口号， 默认3306
    -u : MySQL数据库用户名
    -p ： MySQL数据库用户名对应的密码

查看已经创建的数据库 show databases;
- 连接到数据库 use name
查看数据库中的表 show tables;

导入和导出¶

数据文件导入方式只包含数据，导入规则由数据库系统完成；SQL 文件导入相当于执行该文件中包含的 SQL 语句，可以实现多种操作，包括删除，更新，新增，甚至对数据库的重建。
导入数据文件：LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;
- LOAD DATA INFILE '/var/lib/mysql-files/in.txt' INTO TABLE employee;
- 导入导出大量数据都属于敏感操作，根据 mysql 的安全策略，导入导出的文件都必须在指定的路径下进行
- 查看指定的路径
- 注意到 secure_file_priv 变量指定安全路径为 /var/lib/mysql-files/ ，要导入数据文件，需要将该文件移动到安全路径下。
导入 sql 文件：
- source /home/shiyanlou/Desktop/MySQL-04-01.sql
导出数据 SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;
- SELECT * INTO OUTFILE '/var/lib/mysql-files/out.txt' FROM employee;

备份和恢复¶

备份
- 导出的文件只是保存数据库中的数据；而备份，则是把数据库的结构，包括数据、约束、索引、视图等全部另存为一个文件。
- 备份整个数据库 mysqldump -u root 数据库名>备份文件名;
- 备份数据库中的某一个表 mysqldump -u root 数据库名表名字>备份文件名;
- 不需要进入 mysql 命令行就执行命令
- 生成脚本文件（.sql）
恢复
- 在 mysql 状态下执行 source /home/shiyanlou/Desktop/MySQL-06.sql
- 或者先创建数据库 CREATE DATABASE test;，退出 mysql 后执行 mysql -u root test < bak.sql

八股¶

什么是数据库¶

数据库专门用于管理数据，应用程序通过数据库提供的接口来读写数据。效率高，通用接口
数据：数据是指对客观事件、事物或对象（称为实体）进行记录的可以鉴别的符号。数据只有对实体行为产生影响时才成为信息。信息是数据的内涵，信息是加载于数据之上，对数据作具有含义的解释。
数据库系统的组成：
- 数据库：存储数据（数据文件、控制文件、日志文件、参数等）
- 数据库管理系统：为管理数据库而设计的一套软件系统
- 数据库管理软件：为了提高数据库系统的处理能力所使用的配套和辅助软件
数据库用于存储关联数据，要与数据库管理系统区分（DBMS 如 MySQL、Oracle、MongoDB）数据库管理系统是为管理数据库而设计的一套软件系统，旨在允许用户和应用程序定义、创建、查询、更新和管理数据库。
数据库的分类：
- 关系型数据库：以关系模型组织数据，以表的形式存储数据并使用表之间的关系来维护数据的完整性。可以使用 SQL 语言查询数据
- NoSQL 数据库：又称非关系型数据库，是一种新型的数据库，它可以处理海量、多模式的数据，可以快速访问大量的数据，是面向新一代 web 应用的解决方案。
- NewSQL 数据库：是一种新型的 SQL 数据库，它融合了关系型数据库和 NoSQL 数据库的优点，既具有关系型数据库的可靠性和安全性，同时也具有 NoSQL 数据库的高性能和可扩展性。

基础使用¶

数据库规范化（范式）¶

数据库设计目标：消除数据冗余、确保数据完整、易于修改维护
第一范式：标的列具有原子性不可再分解，也就是说一个列不应该有多个值（如联系方式就不应该在同一列存储电话和邮件）
- 解决了重复列的问题：查询单个值、更新、插入数据都会比较麻烦
第二范式：每个表必须有一个主键并且非主键必须完全依赖与整个主键
- 解决了非主键列对主键的部分函数依赖问题。减少了数据冗余，并避免了各种数据维护异常
- 一个表中包含字段：学生 ID、课程 ID、学生姓名、课程名称、课程成绩。（学生 ID, 课程 ID）构成复合主键。学生姓名仅依赖于学生 ID，课程名称仅依赖于课程 ID，这两个依赖都是部分函数依赖。
- 拆分为：学生表（学生 ID，学生姓名）；课程表（课程 ID，课程名称）；成绩表（学生 ID，课程 ID，课程成绩）
第三范式：一个数据库表中不包含已在其它表中已包含的非主键字段（表的信息如果能够被推导出来，就不应该单独的设计一个字段来存放）
- 减少重复存储
- 表：教师 ID（主键）；教师姓名；所教科目 ID；科目名称；科目所在部门
- 拆分：教师表：包含教师 ID、教师姓名、所教科目 ID；科目表：包含科目 ID、科目名称、科目所在部门。
范式：通过一系列规范化步骤将一个关系数据库表设计成更加符合规范的形式。
- 优点：减少数据冗余；数据查询优化；提高数据完整性、一致性、可靠性
- 缺点：数据表过多（拆分细）需要进行大量关联查询
反范式：将一个关系数据库表设计成非范式化的形式
- 优点：查询效率高，减少关联查询
- 缺点：数据冗余、一致性问题等

连接¶

优点
- 可以让我们轻松地从多个表中获取数据，并且通过连接操作将数据整合到一个查询结果中，提高查询效率和数据处理效率。
- 可以在数据之间建立关联关系，实现数据的逻辑和物理关联。这对于复杂的数据分析和查询操作非常有用，可以让我们更加灵活地进行数据分析和挖掘。
缺点
- 在连接多个表时，会涉及到大量的数据传输和处理操作，可能会导致性能问题。因此，在使用连接操作时，需要仔细考虑表的大小、关联关系等因素，避免出现性能瓶颈。
- 在使用外连接操作时，需要注意未匹配数据的处理，避免产生空值或者重复数据等。
- 联接查询，跨业务，两个不相关的表，因为业务放在一起。对后续的拆表、拆库、拆服务，会造成很大的麻烦。
替代方法：
- 先查出一个表的数据，然后通过在另一个表中的条件限制的方式进行查询
- 减少数据冗余
- 利用 ES 建立一个宽表
- 通过内存缝合装配

查询优化¶

性能开销较大的部分：
- 较大的表
- 连接操作
- 聚合操作
性能分析：
- MySQL 提供了 EXPLAIN 语句来分析查询语句
- EXPLAIN SELECT * FROM table_name WHERE conditions;
慢查询优化的基本步骤：
- 定位问题
- 针对优化
- 结果检验
优化的方式
- 语句优化：避免过多的 JOIN、DISTINCT、*、减少子查询
- 缓存优化
- 索引优化：在 MySQL 建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。尽量选择区分度高的列作为索引，区分度越高比例越大我们扫描的记录数越少，扫描越少，就越快。

分库分表¶

随着数据量和访问量的不断增长，大表和单一数据库往往成为性能瓶颈。这时我们经常通过分库分表来优化数据库结构，提高系统的性能、增加可用性、减少数据库管理的复杂性。
垂直分表
- 将一个大数据表按数据列（字段）的维度进行拆分。垂直分表适用于数据表列数过多、列之间关联较小的场景。
- 减少了 IO 冲突和锁表的几率（对不同表查询互不干扰）
垂直分库
- 将一个大型数据库中的数据按照业务模块进行拆分，将不同的业务模块分配到不同的数据库上。垂直分库适用于业务模块复杂、数据表之间关联较小的场景，每个库可以部署在不同的服务器上，从而达到多个服务器共同分摊压力的效果
- 解决业务层面的耦合，业务清晰。
- 能对不同业务的数据进行分级管理、维护、监控、扩展等。
- 高并发场景下，垂直分库一定程度的提升 IO、减少数据库连接数、降低单机硬件资源的瓶颈。
水平分表
- 是将一个大表按照数据行的维度进行拆分，将数据行分配到不同的数据表中。水平分表适用于数据表行数过多、数据访问量分散的场景。
- 优化单一表数据量过大而产生的性能问题
- 避免 IO 争抢并减少锁表的几率
水平分库
- 将一个大型数据库依据一定规则，按数据行的维度进行切分，将数据行分配到不同的数据库上，每个库可以部署在不同的服务器上。水平分库是对数据行的拆分，不影响表结构。
- 解决了单库数据量大和高并发的性能瓶颈。
- 提高了系统的稳定性及可用性。稳定性提升体现在 IO 冲突减少，锁定减少，可用性指如果某个库出问题，数据库仍部分可用。
- 但由于同一个表被分配在不同的数据库，需要额外进行数据操作的路由工作，因此大大提升了系统复杂度。

数据库的设计¶

设计主键¶

业务主键（自然主键）：在数据库表中把具有业务逻辑含义的字段作为主键
逻辑主键（代理主键）：在数据库表中采用一个与当前表中逻辑信息无关的字段作为其主键
- 通常使用：避免由于业务信息变动造成的主键变动
复合主键（联合主键）：通过两个或者多个字段的组合作为主键。
通常使用 ID 或 UUID 作为主键
自增 ID
- 数据库自动编号，速度快，而且是递增式增长，汇总的主键按顺序存储，对检索非常有利。
- 当系统与其他系统集成，需要导入数据时，很难保证原系统的 ID 没有主键冲突。（容易重复发生冲突）当在一个产生唯一标识符的并发环境中，每个增量值都必须对这个全局值进行锁定和解锁，以确保增量值的唯一性。这就产生了一个并发瓶颈，降低了查询性能。
UUID：是一个在机器上生成的字符串，保证对同一时空的所有机器都是唯一的。在 UUID 算法中，可以使用诸如网卡 MAC 地址、IP、主机名、进程 ID 等信息来保证其独立性。
- 全局唯一性，安全性，可移植性。
- UUID 的无序性带来性能下降，并且 UUID 较长，占用内存（一次可以加载到内存的数目下降）

原理¶

SQL 语句的执行¶

解析请求：进行语法分析和语义分析，转化为抽象语法树
查询优化：对语法树进行优化，生成最优执行计划，提高查询性能
执行计划：执行引擎分局执行计划进行查询
结果：引擎完成查询，将查询结果发送给客户端

MySQL 体系结构¶

Client-Sever 结构：Sever 包含：服务处理层，存储引擎、文件系统
服务器层
- 负责 MySQL 关系数据库管理系统的所有逻辑功能
- MySQL 服务和实用程序：包括备份和恢复，安全，分区等。
- SQL: 用于查询 MySQL 服务器。
- SQL 解析器
- 优化器
- 缓存：如果任何客户端发出的查询与缓存中已有的查询相同，服务器将跳过解析、优化甚至执行，它只是简单地显示缓存中的输出。（这也是为什么二次查询会很快）
存储引擎层
- 负责为数据库执行实际的数据 I/O 操作，并启用和实施针对特定应用程序需求的某些功能集。
- 用 MyISAM，InnoDB 还有一群其他的 Federated，Mrg_MyISAM，Memory
数据存储层

存储引擎¶

MySQL 可插拔存储引擎架构使数据库专业人员能够为特定应用程序需求选择专门的存储引擎，同时完全无需管理任何特定应用程序编码要求。
常用的引擎：InnoDB 和 MyISAM
InnoDB
- 事务支持：InnoDB 提供了对事务的支持，允许行级锁定，这对于需要处理大量并发写入操作的应用来说非常重要。
- 数据完整性：通过外键约束支持，InnoDB 有助于维护数据库的数据完整性。
- 恢复能力：支持崩溃后的恢复机制。
- 行级锁定：InnoDB 支持行级锁定，减少了数据库操作时的锁争用，提高了并发处理能力。
- 支持 MVCC：支持多版本并发控制（MVCC），提高读取效率，特别是在高并发场景下。
- 默认引擎：从 MySQL 5.5 开始，InnoDB 是 MySQL 的默认存储引擎。
MyISAM
- 速度快：在不需要事务处理和外键约束的场景下，MyISAM 的简单设计使其读取速度非常快。
- 表级锁定：MyISAM 在操作表时使用表级锁定，这意味着当执行写入（INSERT、UPDATE、DELETE）操作时，会锁定整个表。
- 全文索引：直到 MySQL 5.6 引入 InnoDB 全文索引之前，MyISAM 支持全文索引是其一个主要优势。
- 不支持事务：MyISAM 不支持事务处理，也不支持外键。
- 压缩表：MyISAM 支持表数据的压缩，以减少磁盘空间的使用。
对比
- 事务支持：InnoDB 支持事务处理，而 MyISAM 不支持。这是两者之间最重要的区别之一。
- 并发：InnoDB 通过行级锁定和 MVCC 提供了更好的并发处理能力，而 MyISAM 的表级锁定可能会成为性能瓶颈。
- 数据完整性：InnoDB 支持外键约束，有助于保持数据的完整性，而 MyISAM 则不支持。
- 恢复能力：InnoDB 提供了更强的恢复能力，可以处理系统崩溃后的数据恢复，MyISAM 在这方面较为脆弱。
- 存储要求：MyISAM 通常需要更少的磁盘空间和内存，这是因为它的结构相对简单。

日志系统¶

BinLog（MySQL 服务层）
- 是 Server 层生成的日志，主要用于数据备份和主从复制；
UndoLog（InnoDB ）
- 实现了事务中的原子性，主要用于事务回滚和 MVCC。
RedoLog（InnoDB ）
- 实现了事务中的持久性，主要用于掉电等故障恢复；

事务¶

数据库事务是由有限的数据库操作序列构成，要么都完成要么就都取消（如果事务中有的操作没有成功完成，则事务中的所有操作都需要回滚）

MVVC 多版本控制机制¶

事务开启时，会先申请一个事务 id 当一个事务修改一行数据时，MySQL 会保留修改前数据的 undo 回滚日志，并将事务 id：transaction_id 赋给 trx_id 版本记录中的字段。将这些 undo log 日志串联起来，形成一条历史版本链
一致性视图：实现可重复读、读已提交
- 对事务按照时间轴划分：已提交、未提交、未开始
- 如果当前事务 id 落在紫色部分，说明这个版本是一个 committed 事务或者是当前事务自己生成的，这个数据是可见的。
- 如果当前事务 id 落在蓝色部分，说明这个版本是以后开始的事务产生的，肯定是看不见的。
- 如果当前事务 id 落在橙色部分，则包括两种情况：
  - A、如果该行在 trx_id 数组中，说明这个版本是由一个尚未提交的事务生成的，不可见。
  - B、如果该行 trx_id 不在数组中，说明这个版本是由一个已提交的事务生成的，可见。
- 每个事务都会获得一个唯一的时间戳（或版本号）。这个时间戳用来标记事务修改或查询数据的版本。事务在访问数据时，系统会根据事务的时间戳和数据版本的时间戳决定哪个版本的数据对该事务可见。

索引¶

索引是一种特殊的数据结构，它可以帮助数据库快速地定位要查询的数据，从而提高查询性能和效率
键和索引的区别：
- 键是用于标识表中唯一行的列或列组合
- 索引是一种特殊的数据结构, 用于快速查找数据库表中的的数据
分类
- 按数据结构分类可分为：B+tree 索引、Hash 索引、Full-text 索引。
- 按物理存储分类可分为：聚簇索引、二级索引（辅助索引）。
- 按字段特性分类可分为：主键索引、普通索引、前缀索引。
- 按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。
聚簇索引：
- 聚簇索引直接将数据行存储在索引的叶节点上。
- 进行数据检索时，由于数据行就在索引的叶节点上，所以可以更快地访问数据。对于执行范围查询或需要按顺序访问数据的操作，聚簇索引能显著提高性能。
- 聚簇索引不宜包含经常修改的列，因为这会导致物理重新排列数据，影响性能。
二级索引：
- 叶节点不直接包含数据行。相反，它们包含键值以及指向聚簇索引中行的指针
优点：
- 提升查询性能，降低资源消耗
缺点：
- 占用数据库存储空间
- 降低数据修改性能，修改数据时需要维护索引
为什么索引数据结构选择了 B+而不是 hash 或者 B、红黑树
- B+叶节点之间相互连接，支持更加高效的范围查询（而哈希表不支持范围查找）；B 树效率也相对较低
- 更低的树高：红黑树的树高远高于 B+ 导致 IO 开销较大
相比 B 树 B+树的优势
- B+树中的节点不存储数据，叶子节点中存储的所有数据导致查询时间复杂度固定为 log n。
- B+叶子节点成对连接，可以大大增加区间可达性，可用于范围查询。
- B+树更适合外存（存放磁盘数据）。由于内部节点没有数据字段，每个节点可以索引更大更精确的范围。（更低的树高）
建立索引的原则
- 选择具有较高查询频率的列作为索引，以确保索引的有效性。
- 避免在拥有大量重复值的列上创建索引，因为它们消耗大量空间而且不能提高查询性能。
- 对于关联的表，应在外键上创建索引，以提高 JOIN 查询的性能

B树 ¶

锁¶

防止当多用户改写数据库时造成数据不一致与冲突。当有一个用户对数据库内的数据进行操作时，在读取数据前先锁住数据，这样其他用户就无法访问和修改该数据，直到这一数据修改并写回数据库解除封锁为止。
表锁：
- 在表上施加，会将表完全锁的，从而防止其他用户对表中的数据进行任何操作，表锁是一种粗粒度的锁。
- 开销小加锁快，不容易出现死锁，但是并发度低。
- 表锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如 Web 应用。
行锁：
- 锁定索引记录
- 可能出现死锁，但是锁冲突几率低，并发程度高。占用资源、内存更多。加锁过程慢
- 行锁只在存储引擎层实现，而 MySQL 服务器层没有实现。行锁更适合于有大量按索引条件并发更新少量不同数据， 同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
并发控制方式：乐观锁与悲观锁
乐观锁：
- 假设多用户并发的事务在处理时不会彼此互相影响, 各事务能够在不产生锁的情况下处理各自影响的那部分数据
- 在提交数据更新之前, 每个事务会先检查在该事务读取数据后, 有没有其他事务又修改了该数据。如果其他事务有更新的话, 正在提是交的事务会进行回滚。（即都尝试修改，觉得不会出问题，如果出现问题再回退）
- 乐观并发控制多数用于数据竞争不大、冲突较少的环境中，适合读操作多的场景，相对来说写的操作比较少。
- 优点：发生数据竞争的概率较低，吞吐量更大
- 缺点：有可能会遇到不可预期的结果, 例如两个事务都同时读取了数据库的某一行, 经过修改以后写回数据库, 这时就遇到了问题。
悲观锁：
- 阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作读某行数据应用了锁, 那只有当这个事务把锁释放, 其他事务才能够执行与该锁冲突的操作。
- 悲观并发控制主要用于数据争用激烈的环境, 以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。悲观锁适合写操作多的场景，因为写的操作具有排它性。
- 优点：先取锁再访问"的保守策略, 为数据处理的安全提供了保证。
- 缺点：加锁的机制会让数据库产生额外的开销, 还有增加产生死锁的机会; 并且也会增加系统负载降低并行性
避免死锁的发生
- 如果事务涉及多个表，操作比较复杂，尽量一次锁定所有的资源，而不是逐步来获取
- 不同事务并发读写多张数据表，可以约定访问表的顺序，采用相同的顺序降低死锁发生的概率
- 如果事务需要更新数据表中的大部分数据，数据表又比较大，这时可以采用锁升级的方式，比如将行级锁升级为表级锁，从而减少死锁产生的概率；

高可用高性能：分布式集群¶

主从同步：
- 当一个写入请求到达 Master 数据库时，Master 数据库执行写入操作，然后 Master 向客户端返回写入成功，同时异步的复制写入操作给 Slave 数据库。
- 读写分离：主库负责写、从库负责读，这样即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
优点
- 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
- 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；增加冗余，提高可用性。
- 读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。
- 高可用：随着系统中业务访问量的增大，如果是单机部署数据库，就会导致 I/O 访问频率过高；有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘 I/O 访问的频率，提高单个机器的 I/O 性能。
缺点：存在很多限制，比如：不支持外键、部署、管理、配置很复杂、占用磁盘空间大、内存大、备份和恢复不方便、重启的时候，数据节点将数据 load 到内存需要很长的时间。

监控¶

监控指标
- DB 可用性监控：进程存在，能对外服务（能执行 SQL）
- DB 性能的监控：QPS、TPS、并发线程数（小于数据库连接数）、缓存命中率
- DB 异常的监控：innodb 阻塞和死锁、慢查询
- 主从相关监控：链路状态、主从延迟、数据一致性（定期检查）
- 服务器的监控：CPU、内存、swap 分区（内存不够的时候它来顶包）、网络 IO、磁盘空间（数据目录和日志目录）

配置与安装¶

基本操作¶

linux¶

导入和导出¶

备份和恢复¶

八股¶

什么是数据库¶

基础使用¶

数据库规范化（范式）¶

连接¶

查询优化¶

分库分表¶

数据库的设计¶

设计主键¶

原理¶

SQL 语句的执行¶

MySQL 体系结构¶

存储引擎¶

日志系统¶

事务¶

MVVC 多版本控制机制¶

索引¶

B树¶

锁¶

高可用高性能：分布式集群¶

监控¶

B树 ¶