本文共 5551 字，大约阅读时间需要 18 分钟。

一、数据库基础知识

1.为什么要使用数据库

• 数据存储在内存中
  • 优点：存取速度快；
  • 缺点：数据不能永久保存
• 数据存储在文件中
  • 优点：数据永久保存
  • 缺点：速度比内存操作慢，频繁的IO操作；查询数据不方便
• 数据存储在数据库
  • 数据可以永久保存；
  • 使用SQL语句，查询方便效率高；
  • 管理数据方便；

2.什么是SQL？

SQL(Structured Query Language)结构化查询语言简称SQL，是一种数据库查询语言。

作用：用于存取数据、查询、更新和管理关系数据库系统。

3.什么是Mysql？

Mysql是一个关系型数据库管理系统， 属于Oracle公司旗下产品。MySQL是最流行的关系型数据库管理系统之一。

4.数据库的三大范式？

**第一范式：**每个列都不可以再拆分。

举例：

如以下表存在可再分项（高级职称），所以不满足第一范式

解决措施： 非规范化转换为规范化的第一范式方法很简单，将表分别从横向、纵向展开即可。将高级职称横向展开即可以得到满足第一范式的表结构。

第二范式： 关系模式必须满足第一范式，并且所有非主属性都完全依赖与主码。注意，符合第二范式的关系模式可能还存在数据冗余、更新异常等问题。

举例： 如关系模型（职工号，姓名，职称，项目号，项目名称）中，职工号->姓名，职工号->职称，而项目号->项目名称。显然依赖关系不满足第二范式，常用的解决办法是差分表格，比如拆分为职工信息表和项目信息表。

第三范式： 关系模式满足第二范式，所有非主属性对任何候选关键字都不存在传递依赖。即每个属性都跟主键有直接关系而不是间接关系，像：a–>b–>c。一般数据库设计中，一般要求达到3NF，第四第五较少涉及。

举例： 比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）这样一个表结构，就存在上述关系。 学号–> 所在院校 --> (院校地址，院校电话)。我们应该拆开来，如下：

（学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）

5.数据库的事务性

除了数据库设计三大范式之外，事务处理也是保证数据完整性的重要手段。事务是单独的工作单元，该单元可以包含多个操作以完成一个完整的任务。**锁是在多用户环境中对数据访问的限制。**事务和锁确保了数据的完整性。

事务处理

提交commit：当所有的操作步骤都被完整执行后，称该事务被提交

回滚rollback：由于某一操作步骤执行失败，导致所有步骤都没有被提交，则事务必须回滚，即回到事务执行前的状态。

事务ACID属性

事务处理的特性，每一个事务都有它们所共有的特性，叫做ACID特性，分别是原子性atomicity，一致性consistency，隔离性Isolation，持久性Durability。

• 原子性：

  事务的原子性表示事务执行过程中，把事务作为一个工作单元处理，一个工作单元可能包括若干个操作步骤，每个操作步骤都必须完成才算完成，若因任何原因导致其中的一个步骤操作失败，则所有步骤操作失败，前面的步骤必须回滚。

• 一致性：

  事务的一致性保证数据处于一致状态。如果事务开始时系统处于一致状态，则事务结束时系统也处于一致状态，不管事务成功还是失败。

• 隔离性：

  事务的隔离性保证事务访问的任何数据不会受到其它事务所做的任何改变的影响，直到该事务完成。

• 持久性：

  事务的持久性保证加入事务执行成功，则它在系统中产生的结果应该是持久的。

6.Mysql有关权限的表都有哪几个？

Mysql服务器通过权限表来控制用户对数据库的访问，权限表存放在Mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别为user,db,table_priv,columns_priv和host。下面分别介绍一下这些表的结构和内容：

• user权限表：记录允许连接到服务器的用户账号信息，里面的权限是全局级的。
• db权限表：记录各个账号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作梗细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

7.MySQL Binlog介绍

7.1 Binlog简介

在MySQL中一般有以下几种日志：

本次的二进制日志Binlog可以说是Mysql最重要的日志，其记录了所有的DDL和DML语句(除了数据查询语句select，show等)，以事件形式记录，还包含语句所执行的消耗时间，Mysql的二进制日志是事务安全型的。binlog的主要目的是复制和恢复。

7.2 Binlog日志的两个最重要的使用场景

• **Mysql主从复制：**Mysql Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
• **数据恢复：**通过使用mysqlbinlog工具来恢复数据。

7.3 启用Binlog日志

一般来说开启binlog日志大概会有1%的性能损耗。

启用binlog，通过配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 选项：

在配置文件中加入 log-bin 配置，表示启用binlog，如果没有给定值，写成 log-bin=，则默认名称为主机名。（注：名称若带有小数点，则只取第一个小数点前的部分作为名称）

[mysqld]log-bin=my-binlog-name12

也可以通过 SET SQL_LOG_BIN=1 命令来启用 binlog，通过 SET SQL_LOG_BIN=0 命令停用 binlog。启用 binlog 之后须重启MySQL才能生效。

7.4 常用的BinLog操作命令

# 是否启用binlog日志show variables like 'log_bin';# 查看详细的日志配置信息show global variables like '%log%';# mysql数据存储目录show variables like '%dir%';# 查看binlog的目录show global variables like "%log_bin%";# 查看当前服务器使用的biglog文件及大小show binary logs;# 查看主服务器使用的biglog文件及大小# 查看最新一个binlog日志文件名称和Positionshow master status;# 事件查询命令# IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)# FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)# LIMIT [offset,] ：偏移量(不指定就是0)# row_count ：查询总条数(不指定就是所有行)show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];# 查看 binlog 内容show binlog events;# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）show binlog events in 'master.000003';# 设置binlog文件保存事件，过期删除，单位天set global expire_log_days=3; # 删除当前的binlog文件reset master; # 删除slave的中继日志reset slave;# 删除指定日期前的日志索引中binlog日志文件purge master logs before '2019-03-09 14:00:00';# 删除指定日志文件purge master logs to 'master.000003';

7.5 Binlog的写入时机

对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

• 如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
• 如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
• 设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。

如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。

在MySQL 5.7.7之前，默认值 sync_binlog 是0，MySQL 5.7.7和更高版本使用默认值1，这是最安全的选择。一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。

7.6 Binlog文件

binlog日志包括两类文件:

• 二进制日志索引文件（文件名后缀为.index）用于记录所有有效的的二进制文件
• 二进制日志文件（ 文件名后缀为.00000* ）记录数据库所有的DDL和DML语句事件

binlog是一个二进制文件集合，每个binlog文件以一个4字节的魔数开头，接着是一组Events:

• 魔数：0xfe62696e对应的是0xfebin；
• Event：每个Event包含header和data两个部分；header提供了Event的创建时间，哪个服务器等信息，data部分提供的是针对该Event的具体信息，如具体数据的修改；
• 第一个Event用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式；
• 其余的Event按照第一个Event的格式版本写入；
• 最后一个Event用于说明下一个binlog文件；
• binlog的索引文件是一个文本文件，其中内容为当前的binlog文件列表

当遇到以下3种情况时，MySQL会重新生成一个新的日志文件，文件序号递增：

• MySQL服务器停止或重启时
• 使用 flush logs 命令；
• 当 binlog 文件大小超过 max_binlog_size 变量的值时；

max_binlog_size 的最小值是4096字节，最大值和默认值是 1GB (1073741824字节)。事务被写入到binlog的一个块中，所以它不会在几个二进制日志之间被拆分。因此，如果你有很大的事务，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的日志都记录到当前日志文件中，直到事务结束，你可能会看到binlog文件大于 max_binlog_size 的情况。

7.7 Mysql的binlog有几种记录格式？分别有什么区别？

记录在二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型：

• STATEMENT：基于SQL语句的复制（statement-based replication, SBR）
• ROW：基于行的复制（row-based replication, RBR）
• MIXED：混合模式复制（mixed-based replication, MBR）

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默认值是 ROW。日志格式通过 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

• statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
  据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

转载地址：https://codingchaozhang.blog.csdn.net/article/details/115280228 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！