1 数据库概述
1 分类
2 设计规则
E-R（entity-relationship，实体-联系）模型中有三个主要概念是： 实体集 、 属性 、 联系集 。
表（实体）->类
表中的一条数据 -> 对象
表中的一个列（字段） -> 对象属性
联系：
- 一对多：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键
- 一对一 ：与一对多相似，在外键处加了一个唯一性约束
- 多对多：要表示多对多关系，必须创建第三个表，它将多对多关系划分为两个一对多关系。
- 自我引用 ：通常在表的记录可以具有父子关系时使用
2 基础语法
1 SQL分类
2 基础语法
1 MySQL常用函数
单行函数：参数可以是一列或一个值，接受参数返回一个结果
- 数学函数
- 字符操作函数
- 日期函数
- 流程函数
聚合函数 ：作用于一组数据，并对一组数据返回一个值。
- AVG()、SUM()等
- GROUP BY
- HAVING
- WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件； HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
- 如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。
2 子查询
- in进行子查询时，内层查询语句只能返回一列，这列的值将提供给外层查询语句进行比较操作；exists 关键字后面的参数是一个 任意的子查询
- in在内部表和外部表上都可以使用到索引；exists 查询仅在内部表上可以使用到索引
- 当子查询结果集很大，而外部表较小的时候，exists 的 Block 嵌套循环的作用开始显现，并弥补外部表无法用到索引的缺陷，查询效率会优于；in当子查询结果集较小，而外部表很大的时候，exists的 Block 嵌套循环优化效果不明显，in 的外表索引优势占主要作用，此时 in 的查询效率会优于 exists
3 数据类型
| 整数类型 | TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT | | —- | —- | | 浮点类型 | FLOAT、DOUBLE | | 定点数类型 | DECIMAL | | 位类型 | BIT | | 日期时间类型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP | | 文本字符串类型 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT | | JSON类型 | JSON对象、JSON数组 |
4 约束
NOT NULL 非空约束，规定某个字段不能为空
UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一)约束
FOREIGN KEY 外键约束
CHECK 检查约束
DEFAULT 默认值约束
5 视图
视图是一种 虚拟表 ，本身是 不具有数据的，视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和 修改操作时，数据表中的数据会相应地发生变化，反之亦然。[
](https://blog.csdn.net/weixin_38192427/article/details/120519400)
3 基本架构
1 mysql目录结构
1.文件存放目录//查看MySQL数据库文件的存放路径show variables like 'datadir';2.配置文件目录/etc/my.cnf
2 逻辑架构及执行流程
3 数据库缓冲池
流程：
读
- 查找的数据所在的数据页在缓冲池中时，则将结果返回。否则会把对应的数据页加载到内存中，然后再返回结果。
- 数据都是以页为单位，数据页包含数据页所属的表空间、数据页的编号、缓存页在Buffer Pool
写
- 对于写操作，如果要修改的行所在的数据页在内存中，则修改后返回对应的结果。如果不在的话，则会从磁盘里将该行所对应的数据页读到内存中再进行修改。【注意：此时修改的数据还未持久化到磁盘中，此时磁盘数据与内存中的数据可能不一致】
作用：缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。
结构：包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典 信息等
预读机制：会把需要数的数据页前后的数据页一起加载到缓冲池，当我们需要查前后数据数据时会更加高效
空间管理：基于对 LRU 方法的优化，Msyql 设计了冷热数据分离的处理方案，将LRU链表分为热数据区和冷数据区两部分，这样就可以解决预读失效和缓冲池污染的情况。
4 存储引擎
4 索引
1 索引介绍
索引是帮助mysql高效获取数据的排好序的数据结构。
3 索引原理
1 数据结构选择合理性解析
链表，查询的时间为O(n),即全表遍历一遍；
hash算法，hash只需要一次即可算出数据存储的磁盘地址，但是无法满足范围查询
平衡二叉树，查询的时间可以看作为O(logn),因为树保持平衡会进行自旋也会消耗时间。且当数据量过大的时候树高而会随之增到
B-树和B+树，B-树节点中也有数据，而B+树非叶子节点不存数据，可以存放更多的索引，叶子节点用指针连接，提高区间的访问性能。
B-树存储数据是按照键值大小进行存放，推荐使用自增的整形作为主键（雪花算法生成），不推荐使用UUID，因为UUID是随机生成的16为字符串，比较大小的时候有可能下一行数据的UUID比前一行数据的UUID小，导致生成索引的时候B-树重新排列消耗资源。
2 详细介绍
非聚簇索引（MyISAM）
非聚集索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的数据地址。
聚簇索引与二级索引（InnoDB）
聚集索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。有，而且只有一个。
- 如果存在主键，主键索引就是聚集索引
- 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引
二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个。
4 索引设计原则
前缀索引：在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度
组合索引：在多个字段组合上创建的索引，一般情况下，建议使用组合索引代替单列索引，需要遵循最左前缀原则
回表：通过二级索引找到B+树中的叶子结点，找到索引列的值和主键值。我们需要拿着主键值再去聚簇索引（主键索引）的叶子节点中去拿到完整的记录，这个过程叫做回表。
进行回表的时候，极有可能出现主键id所在的记录在聚簇索引叶子节点中反复横跳的情况，也就是随机IO。
索引覆盖：索引中已经包含了所有需要读取的列数据的查询方式称为覆盖索引。
索引下推：索引下推是把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断，这样能有效减少回表。
5 索引失效原因
5 SQL执行计划
开启慢查询日志(slow query|log)一>设置慢查询时间一>在慢查询日志中找到超时的SQL一>explain分析SQL执行计划一>针对性进行优化((limit分页、设置合理索引)
- 数据量太大，可通过limit分页
- 没有用到索引，补充索引或者检查索引失效原因
| 列名 | 描述 |
| —- | —- |
| id | 每个SELECT关键字都对应一个 唯一的id
id相同，执行顺序由上至下
id不同，id值越大优先级越高，越先被执行 | | select_type | SELECT关键字对应的那个查询的类型
普通查询、联合查询、子查询等复杂查询 | | table | 表名 | | type | 针对单表的访问方法
system > const > eq_ref > ref > range > index > ALL | | possible_keys | 可能用到的索引 | | key | 实际上使用的索引 | | key_len | 实际使用到的索引长度 | | ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 | | rows | 预估的需要读取的记录条数 | | Extra | 一些额外的信息
Using temporary：常见于排序 order by 和分组查询 group by
Using index：使用了覆盖索引
Usingwhere：表明使用了where过滤 |
5 事务
1 事务是什么
事务就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
2 事务的特性
- 原子性(Atomic)：事务要么全部完成，要么全部不完成。通过UNDO LOG实现。
- 一致性(Consist)：事务执行前后的数据完整性保持一致。通过UNDO LOG实现。
- 隔离性(Isolated) ：事务的进行是隔离开来有序执行的，避免了同时对同一数据做操作。通过锁实现。
持久性(Durable)： 事务一旦提交后完成，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。通过REDO LOG实现。
3 事务的并发问题
脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
- 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
- 幻读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了新增或删除并提交，导致事务A多次读取同一数据时，数量不一致。
4 事务的隔离级别
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交（read-uncommitted） | √ | √ | √ |
| 读已提交（read-committed） | × | √ | √ |
| 可重复读（repeatable-read） | × | × | √ |
| 串行化（serializable） | × | × | × |
6 日志
1 重写日志 （redo log）
redo log是InnoDB存储引擎独有的，修改数据的时候会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。
作用：
保证持久性，防止因宕机造成的数据丢失，mysql可以根据redo log恢复数据文件。
流程：
- 先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
- 生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
- 当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加 写的方式
- 定期将内存中修改的数据刷新到磁盘中
2 回滚日志 （undo log）
undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。
作用：
- 提供回滚操作，执行语句的时候会记录一条反向操作的语句，以保证在事务回滚时，将数据还原回去。
- 提供多版本控制(MVCC)，
存储机制：
InnoDB对undo log的管理采用段的方式，也就是 回滚段（rollback segment） 。每个回滚段记录了 1024 个 undo log segment ，而在每个undo log segment段中进行 undo页 的申请。
3 二进制日志 （bin log）
记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、 show等）。
作用：
- 数据恢复
- 数据复制
格式：
- Statement：记录的内容是SQL语句原文，但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。
- Row：包含操作的具体数据的 SQL 语句，但是这种格式，需要更大的容量来记录，比较占用空间。
- Mixed：Statement 和 Row 的混合体，会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式。
写入机制：
事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。
- 上图的 write，是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快
- 上图的 fsync，才是将数据持久化到磁盘的操作
write和fsync的时机，可以由参数sync_binlog控制，默认是0，为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。但是机器宕机，page cache里面的 binlog 会丢失。可以设置为1，表示每次提交事务都会执行fsync，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。
两阶段提交：
InnoDB存储引擎使用两阶段提交方案防止的bin log 和 redo log日志之间逻辑不一致。
将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。
4 错误日志 （error log）
错误日志记录了MySQL Server每次启动和关闭的详细信息以及运行过程中所有较为严重的警告和错误信息。默认开启不能关闭。
5 慢查询日志 （slow query log）
记录MySQL中响应时间超过阈值的语句或者未走索引的语句。慢查询日志主要与explain进行联合分析。
6 通用查询日志 （general log）
通用查询日志用来 记录用户的所有操作 ，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。
7 锁机制
1 事务并发访问
- 读-读：都是读操作不会对数据造成影响，允许并发进行。
- 写-写：都是写操作容易造成丢失修改的问题，加锁处理，事务执行完成后释放锁。
- 读-写：读写操作同时进行的时候容易造成脏读、幻读、不可重复读问题。读操作使用MVCC处理，写操作加锁处理。
2 锁分类
3 读锁与写锁
读锁：也称为 共享锁 、英文用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会 互相影响，相互不阻塞的。
写锁 ：也称为 排他锁 、英文用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。
| 读 | 写 | |
|---|---|---|
| 读 | √ | × |
| 写 | × | × |
MySql的InnoDB引擎，对于insert、update、delete等操作。会自动给涉及的数据加排他锁；
对于一般的select语句，InnoDB不会加任何锁，可以通过语句给显示加读锁或写锁。
-- 读锁
select...lock in share mode --8.0 后使用 for share
-- 写锁
select ... for update
4 表锁
1 表级别的S锁、X锁
2 意向锁
当一个事务带着表锁去访问一个被加了行锁的资源，那么，此时，这个行锁就会升级为意向锁，将表锁住。
常用的意向锁有：意向共享锁，意向排它锁，共享意向排它锁
3 自增锁
事务插入自增类型的列时获取自增锁，如果一个事务正在往表中插入自增记录，所有其他事务的插入必须等待
4 元数据锁
简称MDL锁，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。保证读写的正确性。
5 行锁
1 普通行锁
2 间隙锁
3 临键锁
同时锁住记录(数据)，并且锁住记录前面的“间隙” ，即普通行锁加间隙锁。
//例如记录1,3,5,7，则5上的记录锁会锁住5，5上的gap lock会锁住(3,5)，5上的next-key lock会锁住(3,5]。
6 乐观锁和悲观锁
乐观锁和悲观锁都是定义出来的概念，是抽象概念锁。实际使用别的锁或机制来实现。
1 乐观锁
乐观锁想法很乐观，认为这次的操作不会导致冲突，在操作数据时不加锁，只有提交数据更新时，才会对数据是否冲突进行检测。
乐观锁在数据库上的实现一般是使用版本号机制，在表中加上一个版本号（version）字段，表示数据被修改的次数，当数据被修改时，version值会加一；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。
update table set x=x+1, version=version+1 where id=#{id} and version=#{version};
2 悲观锁
悲观锁对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，别人会修改数据。所以在整个数据处理过程中，需要将数据锁定。
悲观锁的实现，通常依靠数据库提供的锁机制实现，比如mysql的排他锁
8 显式锁和隐式锁
1 显式锁
2 隐式锁
8 死锁
1 死锁
两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。
2 解除方法
--1.查询是否锁表
show OPEN TABLES where In_use > 0;
--2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
show processlist
--3.杀死进程id（就是上面命令的id列）
kill id
============================
--1.查看下在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
--2.杀死进程id（就是上面命令的trx_mysql_thread_id列）
kill 线程ID
8 MVCC
1 MVCC是什么
MVCC(Mutil-Version Concurrency Control)，就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。其基本思想是每次事务生成一个版本的数据既可以实现对事务结果的完整性读取。 可以提高并发的读写性能
2 原理
• 每次操作都会生成一条undo log日志，每条记录都会保存两个隐藏列trx_id（事务id）、roll_pointer（回滚指针）2个字段，回滚指针指向前一条记录
• 查询的时候会读取ReadView：[未提交事务ID的数组]+最大事务ID,并根据这个ReadView从undo log日志中最新记录依次往下查找
• 若当前记录：当前事务id < 未提交事务id，则可读
• 若当前记录：事务最小id < 当前事务id <事务最大id，则判断当前事务id是否在未提交事务id的数组中，若在则不可读（只有当前事务可读）
• 若当前记录：当前事务id > 事务最大id，则不可读
• 可重复读返回的ReadView是第一条记录的，读已提交每次查询都返回新的ReadView
MVCC只针对可重复读与读已提交，读未提交的每次查询最新的记录即可
7 其他调优
8 主从复制
1 主从复制的概念
建立一个或多个和主数据库完全一样的数据库环境（从数据库），用来做数据的热备，读写分离，架构的扩展。
主从形式除了一主一从、一主多从等还有其他形式，请结合实际需求查找相关配置文件。
2 主从复制的原理
- master将改变记录到二进制日志(binary log)中；
- slave将master的binary log events拷贝到它的中继日志(relay log)；
- slave重做中继日志中的事件，更改自己的数据。
9 分库分表
1 分库分表的需求
数据据库数据量是不可控的，随着时间和业务发展，造成表里面数据越来越多，如果再去对数据库表curd操作时，就会有性能问题。
2 分库分表的方式
在数据库设计时候就要考虑垂直分库和垂直分表。
随着数据库数据量增加，不要马上考虑做水平切分。首先考虑缓存处理，读写分离，使用索引等等方式。如果这些方式不能根本解决问题了，再考虑做水平分库和水平分表。垂直分表：如把课程表分为基本信息表和详细信息表。垂直分库：单一数据库按照业务进行划分，专库专表。水平分表：数据过多的表，分开表1，表2。水平分库：使用2个数据库，按特定字段划分，如按时间分，3个月以内的数据和一年前的数据分为两个库。
3 分库分表的实现
可以使用MyCat、ShardingSphere等中间件实现，无需额外学习，参考官网进行按照配置即可使用。
ShardingSphere官方文档：https://shardingsphere.apache.org/document/current/cn/overview/
Sharding-JDBC是轻量级的Java框架，是增强的JDBC驱动。使用时导入依赖，并按照官网配置配置文件即可。
Sharding-Proxy是独立应用，使用时需安装服务，进行分库分表或者读写分离配置、启动。
若有收获，就点个赞吧
0 人点赞
