MySQL

关系型数据库。关系模型表明了数据库中所存储的数据之间的联系

MySQL简要架构图

连接器：身份认证和权限相关

查询缓存：执行查询语句的时候会先查询缓存，MySQL8.0版本之后移除，因为功能不太实用

分析器：查看SQL语句的语句语法是否正确

优化器：按照MySQL认为最优的方案去执行

执行器：执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，没有权限的话，会报错

插件式存储引擎：主要负责数据的存储和读取。采用的插件式架构，支持InnoDB、MyISAM、Memory等多种存储引擎

重点是存储引擎。** MySQL默认的是InnoDB，并且只有InnoDB是事务性存储引擎**，

show table status from db_name where name='table_name' // 查看单表使用的引擎

存储引擎是基于数据库表的，不是基于数据库

1、MyISAM和InnoDB的区别

特性	MyISAM	InnoDB
是否支持行级锁	表级锁	行级锁
是否支持事务	不支持	支持。4个隔离级别()，默认是可重复读(RR)
是否支持外键	不支持	支持
是否支持数据库异常崩溃后安全修复	不支持	支持。依赖于 redo log
是否支持MVCC	不支持	支持。MVCC是行级锁的一个升级，减少加锁操作，提高性能
索引实现	B+Tree结构。索引文件，数据文件分离，表数据文件本身按照B+Tree组织的索引结构，树叶子节点data域保存了完整的数据记录	B+Tree结构。数据文件本身就是索引文件，

2、MySQL查询缓存

不建议使用了

my.cnf 文件加入以下配置，重启MySQL开启查询缓存

query_cache_type = 1
query_cache_size = 600000

3、MySQL事务

逻辑上的一组操作，要么都执行，要么都不执行。

事务：数据库事务；分布式事务

3.1、事务的特性：

ACID。AID是手段，C是目标

A：原子性。最小执行单位，不允许分割。

C：一致性。执行事务前后，数据保持一致。从大局角度看

I：隔离性。一个用户的事务不会被其他事务干扰，各并发事务之间是独立的

D：持久性。一个事务被提交之后。对数据库的改变是持久的，发生故障也没有任何影响

3.2、并发事务带来的问题

脏读：读取到别的事务还未提交的脏数据

更新丢失：事务2修改了事务1中对数据的修改

不可重复读：一个事务内由于别的事务对数据的修改，导致一个事务内多次读取，读取到的数据不一致

幻读：幻读与不可重复度的区别是，幻读指的是新插入的数据；不可重复读指的是修改的数据

3.3、数据库隔离级别

读未提交（RN）：允许读取尚未提交的数据变更，可能会导致脏读、幻读或者不可重复读

读已提交（RC）：允许读取并发事务已经提交的数据，阻止了脏读，还是会出现幻读或者不可重复读

可重复度（RR）：对同一个字段多次读取结果都是一致的，除非事务被本身事务修改，阻止了脏读、不可重复读。还是会出现幻读

串行读(SERIALIZABLE)：最高级别，完全遵循ACID。

3.4、MySQL的隔离级别是怎么实现的？

锁 + MVCC机制

3.5、MySQL默认的隔离级别

select @@tx_isolation
select @@transaction_isolation   // MySQL 8.0之后使用该命令

RR下怎么保证不会幻读：

快照读：由MVCC机制保证不出现幻读
当前读：使用Next-Key Lock加锁保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

todo：怎么保证的？如何实现的？

3.6、MySQL锁

表级锁：针对非索引字段加的锁。
行级锁：针对索引字段加的锁。只针对当前操作的记录进行加锁

3.7、行级锁是怎么使用的？

！！！执行update、delete 语句时，where条件中字段没有命中索引或者索引失效时，会导致扫描全表，对表中所有记录进行加锁。

3.8、共享锁和排他锁

共享锁：读锁。事务读取记录时获取共享锁，其他事务也可获取，可共享
排它锁：写锁。事务在修改记录时获取排它锁，是互斥的

这两种锁的兼容方式：

由于MVCC的存在。InnoDB在 select语句的时候不会加任何锁，

Q：如何加锁？

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

3.9、什么是意向锁

意向锁是表级锁。有两种：

意向共享锁（IS）：事务有意向对表中某些数据加共享锁（S锁）时，需先取得该表的IS锁
意向排它锁（IX）：事务有意向对表中某写记录加排它锁（X锁）时，需先取得该表的IX锁

意向锁是存储引擎自己维护的，用户无法手动操作意向锁 , 在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的

意向锁和共享、互斥锁之间关系

3.10、InnoDB支持三种行锁方式

记录锁。记录本身加锁
间隙锁。锁定一个范围，不包含记录本身
临键锁。Record Lock+Gap Lock，锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

todo ：需要补充的更详细点

5、常用的命令和注意的点

5.1、定点数

decimal 可变长度
decimal（M，D） M表示总位数，D表示小数位数

5.2、日期类型

datetime： 8字节日期及时间 1000-01-01 00:00:00 到 9999-12-31 23:59:59

date 3字节日期 1000-01-01 到 9999-12-31

timestamp 4字节时间戳 19700101000000 到 2038-01-19 03:14:07

time 3字节时间 -838:59:59 到 838:59:59 （时分秒）

year 1字节年份 1901-2155

5.3、文本类型

text：非二进制字符串
blob：二进制字符串

5.4、整形类型

int：4字节
tinyint： 1字节

6、建表规范

NF：
第一范式。1NF：字段不能再分
第二范式。2NF：满足第一范式情况下，不能出现部分依赖
第三范式。3NF：满足第二范式情况下，不能出现传递依赖。将一个实体信息数据都放在一个表内实现

7、数据库使用建议

数据库基本规范：

单表数据大小尽量控制在500w行以内。可以用归档，分库分表手段控制表数据量大小
尽量做到冷热分离。一个表的字段数最多是4096列，列不要太多
禁止在表中存储图片，文件等大的二进制数据

数据库字段规范：

优先选择符合存储需要的最小的数据类型
- 将字符串换成数字类型存储。例如ip可以换成整形
- 非负型数据，优先使用无符号整形来存储
避免使用TEXT、BLOB数据类型，TEXT可以存储64k的数据。TEXT不能设置默认值
使用 timestamp（4字节）或者 datetime（8字节）类型存储时间
财务相关类的字段必须使用decimal类型，精准浮点数，计算时不会丢失精度。可用于存储比bigint更大的整型数据
每个InnoDB表必须由主键。主键建议使用自增id
对于频繁的查询优先考虑使用覆盖索引。"覆盖索引"：包含了所有查询的字段的索引

索引的目的：通过索引进行数据查找，减少随机IO，增加查询性能，索引能过滤出较少的数据，则从磁盘中读入的数据也就减少

数据库SQL开发规范：

避免数据类型的隐式转换。会造成索引失效
避免使用双%的查询条件
一个sql只能利用到复合索引中的一列进行范围查询
如果 a 列要用到范围查找的话，就要把 a 列放到联合索引的右侧，使用 left join 或 not exists 来优化 not in 操作，not in通常会使用索引失效

数据库操作行为规范

超过100万行的批量写操作要分批多次进行操作。因为大批量的写会造成主从延迟；binlog日志为row格式时会产生大量的日志；避免产生大事务

数据库中时间字段使用 timestamp、bigint、datetime的区别？

排序，性能bigint > timestamp > datetime
查询，bigint > datetime > timestamp
分组，性能timestamp > datetime

6、MySQL索引详解

InnoDB 引擎的表 .ibd 文件包含了表的索引和数据，对于InnoDB引擎表来说，该表的索引的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据

索引是用于快速查询和检索数据的数据结构。常见结构：B树、B+树、和Hash

优点：

加快查找效率
唯一索引，保证了数据库中每一行数据的唯一性

缺点：

创建维护索引需要时间。修改数据时需要需求索引，降低了一些SQL执行效率
索引需要物理文件存储，耗费空间

1、索引存储结构

Hash：

不支持范围查找

B树、B+树的区别：

B树节点存放了key和data；B+树节点存储的是key，叶子节点存放的是Key+data
B树叶子节点是独立的；B+树叶子节点有一条引用链指向与它相邻的叶子节点
B树检索过程，范围内的每个节点的关键字都做二分查找，有可能不达到叶子节点；B+树的检索效率稳定，任何查找都是从根节点到叶子节点

InnoDB使用的是B+树

1.1、聚簇索引和非聚簇索引

MyISAM和InnoDB使用B+树做索引的区别：

MyISAM 中data域存放的是数据记录的地址。通过key找到data域的地址，然后去指定地址读取数据。也成为"非聚簇索引"
InnoDB中表数据文件本身就是按照B+树组织的一个索引结构。树的叶子节点data保存了完整的数据记录，索引是key的主键，因此InnoDB表数据文件本身就是主索引。也成为"聚簇索引"。其余列的索引都是辅助索引，辅助索引叶子节点是主键值，需要二次回表查主键对应的完整data

2、索引类型

2.1、主键索引

主键列使用的索引就是主键索引，一张表只能有一个主键

2.2、二级索引（辅助索引）

二级索引的叶子节点存储的数据是主键

二级索引包含：唯一索引、普通索引、前缀索引、全文索引

2.3、聚集索引和非聚集索引

聚集索引：索引结构和数据一起存放的索引。主键索引就是聚集索引
非聚集索引：索引结构和数据分开存放的索引。二级索引属于非聚集索引。更新代价小；缺点是需要回表查

2.4、联合索引

使用表中多个字段创建的索引就叫联合索引

1、最左前缀匹配原则

使用联合做引时，MySQL会根据联合索引的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引最左侧字段相匹配的字段，会使用该字段过滤一批数据。直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 > 、 < 、between 和 以%开头的like查询 等条件，才会停止匹配

7、MySQL三大日志文件

1、redo log

作用：支持MySQL拥有了崩溃恢复能力。MySQL实例挂了或者宕机时，重启后，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性和完整性

1.2、buffer pool：数据加载缓冲池。MySQL加载数据是以页为单位

刷盘时机：redo log的刷盘策略由参数 innodb_flush_log_at_trx_commit 决定

=0：每次事务提交时不进行刷盘操作
=1：每次事务提交时都执行刷盘操作默认值
=2：每次事务提交时都只把redo log buffer内容写入到**page cache** 文件系统缓存

1.3、3种刷盘策略：

默认情况下是=1。每次事务提交时都执行刷盘操作
InnoDB引擎后台有一个线程，每隔1s，会把 **redo log buffer**中的内容写到文件系统缓存，然后调用fsync 刷盘
当**redo log buffer** 占用的空间即将达到 **innodb_log_buffer_size**一半的时候，后台线程会主动刷盘

1.4、日志文件组：

硬盘上的**redo log** 日志文件不止一个，是以日志文件组形式出现。每个redo 日志文件大小都一样。存储方式是环形数组形式

write pos 当前记录的位置，一边写一边往后移
checkpoint 当前要擦除的位置，往后推移

2、binlog

作用：物理日志。记录修改了什么数据，修改了什么。只要发生了表数据更新，都会产生binlog日志。记录的是语句的原始逻辑，并且是顺序写

主要用于主从数据同步，监听表结构变更的消费动作

2.2、记录格式

通过**binlog_format**参数指定

statement：记录sql原文。但是一些动态函数容易导致数据不一致
row：记录的内容不再是简单的SQL语句了，还包含操作的具体数据，需要通过**mysqlbinlog**工具解析内容默认值
mixed：MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式。

2.3、写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中

3、两阶段提交

redo log和binlog如何保证数据的一致性。

将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

8、一致性非锁定读和锁定读

4、undo log

作用：

回滚事务时用于将数据恢复到修改前的样子

用于MVCC。读取记录时，若该记录被其他事务占用或者当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

InnoDB存储引擎中undo log分为两种：insert undo log 和**update undo log**

insert undo log ：insert操作下产生的undo log，insert操作只对本事务可见，事务执行完成后可删除undo log
update undo log :update 或 delete 操作中产生的 undo log。该 undo log可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。提交时放入 undo log 链表，等待 purge线程 进行最后的删除

一致性非锁定读：通常做法是加一个版本号或者时间戳字段，更新数据时版本号 +1 或者更新时间戳。

select ... lock in share mode ,select ... for update）则会使用 一致性非锁定读（MVCC）

在 Repeatable Read 下 MVCC 防止了部分幻读，这边的 “部分” 是指在 一致性非锁定读 情况下，只能读取到第一次查询之前所插入的数据（根据 Read View 判断数据可见性，Read View 在第一次查询时生成）

锁定读：

读取的是最新版本，也成为当前读。会对读取到的记录加锁

select ... lock in share mode。对记录加S锁
select ... for update、insert、update、delete 操作。会对记录加X锁

锁定读下还是会出现幻读。所以， InnoDB 在实现Repeatable Read 时，如果执行的是当前读，则会对读取的记录使用 Next-key Lock ，来防止其它事务在间隙间插入数据

9、InnoDB对MVCC的实现

9.1、隐藏字段

InnoDB引擎会为每行数据添加是三个字段：

DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

9.2、ReadView

class ReadView {
  /* ... */
private:
  trx_id_t m_low_limit_id;      /* 大于等于这个 ID 的事务均不可见 */
  trx_id_t m_up_limit_id;       /* 小于这个 ID 的事务均可见 */
  trx_id_t m_creator_trx_id;    /* 创建该 Read View 的事务ID */
  trx_id_t m_low_limit_no;      /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */
  ids_t m_ids;                  /* 创建 Read View 时的活跃事务列表 */
  m_closed;                     /* 标记 Read View 是否 close */
}

事务可见性示意图：

9.3、数据可见性算法：

在 InnoDB 存储引擎中，创建一个新事务后，执行每个 select 语句前，都会创建一个快照（Read View），快照中保存了当前数据库系统中正处于活跃（没有 commit）的事务的 ID 号。其实简单的说保存的是系统中当前不应该被本事务看到的其他事务 ID 列表（即 m_ids）。当用户在这个事务中要读取某个记录行的时候，InnoDB 会将该记录行的 DB_TRX_ID 与 Read View 中的一些变量及当前事务 ID 进行比较，判断是否满足可见性条件

算法执行过程：

如果记录 DB_TRX_ID < m_up_limit_id，那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之前就提交了，所以该记录行的值对当前事务是可见的
如果 DB_TRX_ID >= m_low_limit_id，那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之后才修改该行，所以该记录行的值对当前事务不可见。跳到步骤 5
m_ids 为空，则表明在当前事务创建快照之前，修改该行的事务就已经提交了，所以该记录行的值对当前事务是可见的
如果 m_up_limit_id <= DB_TRX_ID < m_low_limit_id，表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照的时候可能处于“活动状态”或者“已提交状态”；所以就要对活跃事务列表 m_ids 进行查找（源码中是用的二分查找，因为是有序的）
- 如果在活跃事务列表 m_ids 中能找到 DB_TRX_ID，表明：① 在当前事务创建快照前，该记录行的值被事务 ID 为 DB_TRX_ID 的事务修改了，但没有提交；或者 ② 在当前事务创建快照后，该记录行的值被事务 ID 为 DB_TRX_ID 的事务修改了。这些情况下，这个记录行的值对当前事务都是不可见的。跳到步骤 5
- 在活跃事务列表中找不到，则表明“id 为 trx_id 的事务”在修改“该记录行的值”后，在“当前事务”创建快照前就已经提交了，所以记录行对当前事务可见
在该记录行的 DB_ROLL_PTR 指针所指向的 undo log 取出快照记录，用快照记录的 DB_TRX_ID 跳到步骤 1 重新开始判断，直到找到满足的快照版本或返回空

在 RC 隔离级别下的 每次select 查询前都生成一个Read View (m_ids 列表)。会导致不可重复读的情况出现
在 RR 隔离级别下只在事务开始后 第一次select 数据前生成一个Read View（m_ids 列表）

10、MVCC+Next-key-Lock防止幻读

1、执行普通 select，此时会以 MVCC 快照读的方式读取数据

RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交

2、执行 select...for update/lock in share mode、insert、update、delete 等当前读

在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 Next-Key-Lock 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据

11、SQL语句的执行过程

MySQL简要架构图：

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器： 执行语句，然后从存储引擎返回数据

redo log和binlog如何保证数据一致性：

采用 redo log 两阶段提交的方式就不一样了，写完 binlog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binlog 也已经写完了，这个时候发生了异常重启会怎么样呢？这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

判断 redo log 是否完整，如果判断是完整的，就立即提交。
如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

MySQL基础知识学习