MySQL基础

原文地址🔗

drop、delete 与 truncate#

用法不同#

类型不同#

DML 语言：数据库操作语言（Data Manipulation Language），数据库增删改查，针对表中数据
DDL 语言：数据定义语言（Data Definition Language），对数据库内部对象创建、删除、修改的语言，针对数据库结构。

delete 为 DML 语言，执行之后 delete 操作会放到 rollback segement 中，事务提交之后生效，也可回滚。

drop 和 truncate 为 DLL 原，执行之后立即生效，数据不能回滚。

执行速度不同#

drop > truncate > delete

utf8 和 utf8mb4#

utf8 ：
- utf8编码只支持1-3个字节
- 中文占 3 个字节，其他数字、英文、符号占一个字节。
- emoji 符号占 4 个字节，所以 utf8 不能存储 emoji 符号
utf8mb4 ： UTF-8 的完整实现。最多支持使用 4 个字节表示字符。可以用来存储 emoji 符号。

数据库范式#

第一范式 1NF#

原子性：数据库中属性（数据表中的列）不可再分割。

第二范式 2NF#

在第一范式的基础上，表中的每一列都应该与主键关联。

如在同一张表中存储学生基本信息和学生成绩，违背了第二范式。

每个表中的非主属性完全依赖于码

第三范式 3NF#

表中出非主属性之间不能互相依赖

在第二范式的基础上，消除非主属性之间的依赖关系，只保留非主属性与码的依赖关系

MySQL 数据类型#

整形#

tinyint,smallint,middleint,int,bigint，分别占用 8、16、24、32、64 存储空间

浮点数#

float
double
decimal：高精度小数类型。

三种浮点数都可以指定列宽，例如使用decimal(18,9)表示总共 18 位，取 9 位保存小数部分，剩下的保存整数部分。

字符串#

char：定长，会自动去除末尾的空格
varchar：变长，不会删除末尾的空格
blog：使用二进制形式保存
text：使用字符串形式保存

时间#

datatime：保存从 1001 年到 999 年的日期和时间，精度为秒，使用 8 字节存储空间，没有时区信息。
timestamp：保存 1970-01-01 开的时间。使用 4 字节保存，只能表示到 2038 年，有时区信息。

MySQL 基础架构#

MySQL 存储引擎#

使用show engines命令查看 MySQL 的存储引擎

常用的有 InnoDB 和 MyISAM 引擎

默认使用 InnoDB 引擎

MyISAM#

不支持事务
不支持行级锁，只能对整张表加锁。读时加共享锁，写时加排他锁。
数据丢失风险

InnoDB#

支持事务，实现了四个隔离级别，默认级别时可重复读，通过 MVCC（多版本并发控制）+Next-KeyLocking（间隙锁）防止幻读
主索引是聚集索引

MyISAM 和 InnoDB#

锁：MyISAM 使用表级锁，InnoDB 使用行级锁
事务：MyISAM 不支持事务，InnoDb 支持事务
外键：MyISAM 不支持外键，InnoDB 支持外键
崩溃恢复：MyISAM 不支持奔溃后安全恢复，InnorDB 支持奔溃后通过日志安全恢复
索引实现方式不同：两者都使用 B + 树的数据结构。MyISAM 中索引文件和数据文件分离，InnorDB 数据文件同时是索引文件

MySQL 事务#

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

事务的特性#

原子性（Atomicitly）：一个事务包含的所有操作，要么全部成功，要么全部失败。
一致性（Consistency）：事务执行前后状态一致。例如转账：转账完成钱的总数不变。
隔离性（Isolation）：是否读未提交。
持久性（Durability）：事务提交后，数据库的更改是永久的。

简称 ACID

事务引发的问题#

脏读：未提交的事务读到了另一个事务未提交的数据。
不可重复读：一个运行较慢事务在事务读取用一记录，两次读到的数据不同，主要针对 update 和 delete。
幻读：两次读取到数据条数不同，针对 insert。

事务隔离级别#

(READ-UNCOMMITTED) 读未提交：最低级别，允许读取尚未提交的数据变更，可能会导致脏读，不可重复读，幻读。
(READ-COMMITTED) 读已提交：允许读取已提交的数据，只能防止脏读。
(REPEATABLE-READ) 可重复读：一个事务开始，事务过程中所读取到的所有数据不允许被其他事物修改，不能防止幻读。
(SERIALIZABLE) 串行化：最高级别，防止所有问题，所有事务以串行化方式逐个执行。

事务隔离级别实现方式#

基于锁和 MVCC 机制实现。

串行化隔离级别通过锁实现，其余隔离级别基于 MVCC 实现。

默认隔离级别#

通过执行SELECT @@transaction_isolation;命令查看

默认隔离级别是可重复读

MySQL 锁#

表级锁和行级锁#

表级锁：锁定粒度最大的锁，针对非索引字段加锁，对整张表枷锁，实现简单，资源消耗少，枷锁快，不会出现死锁。
行级锁：锁定粒度最小的锁，针对索引字段加锁，对操作行加锁。加锁慢，可能出现死锁。

行级锁使用#

InnoDB 使用行级锁针对索引字段加锁。

当执行update，delete方法时，如果where条件字段没有命中唯一索引，或者索引失效时，会导致扫描全表并对全表行加锁。

常见锁🔒 的类型#

共享锁（S 锁）：又称读锁，事务在读取数据（select）时取得共享锁，允许多个事务同时获取（锁兼容）。
排他锁（X 锁）：又称写锁、独占锁，事务在修改记录（update）的时候获得排他锁，不允许多个事务同时获取。如果一个记录已经被加了拍他锁，则其他事务不能在加其它类型的锁（锁不兼容）。

默认不加锁，可以显示声明使用锁
```
SELECT ... LOCK IN SHARE MODE; # 共享锁
SELECT ... FOR UPDATE; # 排他锁
```
意向锁：表级锁，用于快速判断表中有没有行锁。
- 意向共享锁：如果事务有意向对表中数据加共享锁，必须先加意向共享锁。
- 意向排他锁：如果事务有意向丢表中数据加排他锁，必须先加意向排他锁。

InnoDB 中的锁#

InnoDB 有三种行锁：

记录锁（Record Lock）：单个行记录上的锁。
间隙锁（Gap Lock）：锁定一个范围，不包含记录本身。
临键锁（Next-Key Lock）：同时使用记录锁和临键锁。

当前读和快照读（）#

InnoDB 默认隔离级别（可重读）可以解决幻读问题，通过当前读和快照读。

当前读（一致性锁定读）：使用临键锁（Next-Key Lock）进行加锁保证不出现幻读。
快照读（一致性非锁定读）：有 MVCC 机制保障不出现幻读。

两者的区别

快照读：即正常使用select时，使用快照读。如果读取到的数据正在执行update/delete操作，读取操作不会等待记录上的排他锁释放，而是会读取行的上一个快照。

当前读：当在事务隔离级别 RC（读已提交）和 RR（可重读）下，InnoDB 会使用锁定读。

在 RC 级别下：对于快照数据，当前读会读取被锁定行的最新一份快照数据。
在 RR 级别下：对于快照数据，当前读会读取本事务开始时的行数据版本。

快照即记录的历史版本，每行记录可能存在多个历史版本。

MySQL 性能优化#

文件存储#

不建议使用 MySQL 存储二进制文件数据。

建议使用对象存储保存文件，MySQL 保存文件地址。

IP 地址存储#

可以将 IP 地址转换成整形存储。

INET_ATON() ：把 ip 转为无符号整型 (4-8 位)
INET_NTOA() ：把整型的 ip 转为地址

尽可能将列定义为 NOT NULL#

除非有特别的原因使用 NULL 值，应该总是让字段保持 NOT NULL。

索引 NULL 列需要额外的空间来保存，所以要占用更多的空间；
进行比较和计算时要对 NULL 值做特别的处理。

MySQL 索引#

什么是索引#

索引是一种用于快速查找和检索数据的数据结构，常见索引结构有：B 树、B + 树、Hash 表等。

索引的数据结构#

Hash 表#

哈希表时键值对的集合，通过 key 可以快速得出 value。O (1)。通过对 key 使用哈希算法计算索引，在通过索引找到 value。

缺点：

Hash 冲突
Hash 索引不支持顺序和范围查询

B 树#

二叉搜索树，平衡二叉树，红黑树🔗

B 树推荐阅读🔗

B 树的详细解释以及插入删除操作等相关内容上文有详细说明。

个人对于 B 树的理解：普通二叉树的缺点，每个节点只能有两个子节点，每个节点只能容纳一个数据，这导致了两个问题：1. 树的高度很高；2. 逻辑上挨着的节点数据可能树里离的很远，这对于内存中操作数据来说问题不大。

但是在数据库里，数据首先会存储于磁盘上而不是内存上。过高的树需要多次查找，取出结果，比较，再查找，比较，树的高度越高，需要访问磁盘的次数也越多。对于磁盘来说并不是很好的选择。如果满足 “局部性原理”，即逻辑上相邻的数据，物理上尽量存储在一起。这样可以减少磁盘访问次数。

B 树解决了这一问题，B 树的特点：1. 多路树，不再局限于一个节点只能有两个子节点；2. 所有子节点的高度相同。

B 树每个节点能容纳更多的数据，这样降低了树的高度，同时逻辑上相邻的数据在物理上存储在相邻的磁盘空间中。在查询时减少磁盘交互次数。

B 树：

在上图中，叶子节点有多个数据，查找是会将叶子节点上的数据一次读入内存中，通过在内存中遍历比较，找到需要的数据。

B 树每个节点能保存 4K 的数据，由于磁盘存储数据采用分块的方式，而每个块的大小为 4K。每次读取数据都会读取一个节点，4K 的数据到内存中。

B + 树#

B + 树子节点不保存数据，只保存关键字的索引，所有的数据都保存在叶子节点上。同时所有叶子节点上的数据构成了一个有序链表。

B + 树和 B 树比较#

B + 树查询速度更稳定。B + 树所有数据都存放在叶子节点，而叶子节点高度相同，每次查找次数相同，所以查询速度稳定。
B + 树叶子节点有序。B + 树所有叶子节点数据构成一个有序链表，查询大小区间的数据时更快。
B + 树全节点遍历更快。B + 树只需要遍历叶子节点，B 树需要遍历全部节点。
B 树如果访问的数据离根节点近，此时查找速度快于 B + 树。

MyISAM 和 InnoDB 索引#

两者都是用 B + 树作为索引结构

MyISAM 使用非聚簇索引：B + 树节点 data 域中保存数据的地址，在查找时，先找到节点，再通过节点里的地址找到数据。
InnoDB 使用聚簇索引：B + 树节点 data 域中保存完整数据，索引的 key 是数据库主键。

索引类型#

主键索引#

数据表的主键列使用的是主键索引。

如果一张表没有设置主键，InnoDB 会先查找表中是否有唯一且不允许为空的字段，如果有则使用该字段为主键，如果没有 InnoDB 会自己创建一个 6Byte 的自增主键。

二级索引（辅助索引）#

二级索引的叶子节点保存的数据时主键，通过二级索引可以找到主键。

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

唯一索引（UNIQUE）：数据表中设置了唯一的字段，不允许重复，允许为 null
普通索引（INDEX）：数据表中手动设置的索引字段，可以重复，可以为 null
前缀索引：对于字符串类型的数据，截取字符串前几个字符创建索引。
全文索引（FULLTEXT）：检索大文本数据中的关键字信息。

聚集索引和非聚集索引#

聚集索引#

聚集索引即索引结构和数据存放在一次的索引。主键索引就是聚集索引。特点：查询快，修改慢。

优点：查询速度快，因为叶子节点存放数据并且数据有序，所以查到叶子节点就是查到数据
缺点：
- 依赖于有序的数据：查询依赖于数据的物理存储地址按照索引的顺序排列。
- 更新代价大：由于数据的物理存储地址按照索引的顺序排列，所以每次插入删除都需要移动数据，使数据在物理上有序，连续。

非聚集索引#

索引结构和数据分开存放。二级索引是非聚集索引。二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

优点：更新快，叶子节点不存放数据。
缺点：
- 依赖于有序的数据。
- 可能需要回表二次查询。

覆盖索引#

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

联合索引#

使用表中多个字段创建索引

最左前缀匹配原则#

使用联合索引是，根据联合索引中字段的顺序，从左到右依次匹配查询条件中与联合索引中最左字段匹配的字段。

索引字段创建原则#

需要频繁查询、作查询条件、关联查询的字段
不为 null 的字段
需要频繁排序的字段
不会频繁更新的字段
尽量使用联合索引而不是单列索引

MySQL 日志#

MySQL 日志主要由二进制日志（binlog，归档日志），事务日志（redo log）、回滚日志（undo log）组成

redo log#

Redo log 是 InnoDB 所使用的日志系统，记录在每个数据页上做的修改，通过 redo log 可以用于从错误中恢复数据。保证数据的持久性和完整性。如果 MySQL 服务异常停机，重启 MySQL 之后会通过 redo log 恢复数据。

MySQL 数据查询时，从磁盘读取数据以页为单位，查询时会一次性加载一页数据（16kb）Buffer Pool中。后续查询会优先在Buffer Pool中查找。在执行更新操作时，也是有限更新Buffer Pool中的数据，同时记录重做日志缓存（redo log buffer），之后在适当的时机，将缓存刷盘到 redo log 文件中。

刷盘时机#

刷盘：将数据从内存写入磁盘

使用innodb_flush_log_at_trx_commit参数可以控制刷盘时机：
- 0：每次事务提交时不进行刷盘操作（数据丢失分险）
- 1：每次事务提交都进行刷盘（默认值）（不会有数据丢失，因为丢失的都是未提交的事务）
- 2：每次事务提交都把redo log buffer内容写入系统缓存page cache
  在默认情况下，每当事务提交时都会调用fsunc对 redo log 进行刷盘
同时，InnoDB 每隔 1 s，会自动将redo log buffer内容写入系统缓存page cache，之后调用fsunc刷盘。
当redo log buffer占用的空间超过innodb_log_buffer_size一半时，触发刷盘。

日志文件组#

多个文件存储 redo log 日志，一个文件写满写写一个，最后一个写满再回头写第一个。

binlog#

binlog 记录数据库操作逻辑，记录每次数据表更新。

MySQL 主从同步时，会使用 binlog 来同步数据。

记录格式#

通过binlog_format参数指定记录格式

statement：记录 SQL 语句原文，但是在执行update_time=now()会导致时间不同。
row：记录执行 SQL 的参数，记录具体时间
mixed：自动判断该 SQL 是否会有数据不一致问题，如果有使用 row，没有使用 statement 记录

写入机制#

事务执行过程中，现将日志写到binlog cache，事务提交之后，在将binlog cache写入 binlog。

两阶段提交#

通过 redo log 和 binlog 互相配合来尽量保证数据恢复能力，和数据不丢失。

日志记录顺序。

执行更新操作时先写入 redo log，并将状态设置为 prepare，事务提交后，将更新的操作写入 binlog，在将 redo log 设置为 commit 阶段。

如果在 redo log 状态为 prepare 阶段时，MySQL 服务异常，在恢复数据时由于事务没有提交，没有 binlog，MySQL 会选择回滚这一事务。

如果在事务提交，并且有 binlog 时，MySQL 会选择通过 binlog 恢复数据，并且将 redo log 状态设置为 commit 阶段。

undo log#

undo log 保证事务的原子性，用于在事务异常时回滚操作。

所有的事务操作都会先记录回滚日志，在实际执行操作。同时，回滚日志先于数据持久化到磁盘上。

三种日志的作用#

使用 redo log 保证事务的持久性
使用 undo log 保证事务的原子性
使用 redo log 和 undo log 保证数据不丢失
使用 binlog 完整数据备份，主从，保证数据的一致性

MVCC#

Multi-Version Concurrency Control，多版本并发控制。是一种并发控制的方法，在数据库管理系统中，实现对数据库的并发访问。

实现原理#

隐式字段#

DB_ROW_ID：隐式自增主键，如果数据表没有主见，InnoDB 自动以 DB_ROW_ID 等字段生成聚集索引
DB_TRX_ID：最近插入 / 修改的事务 ID，记录当前记录最后一次修改该记录的事务 ID
DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本
DRFAULT_BIT：删除 flag

undo log#

ReadView#

事务在进行快照操作时生成的读视图，在该事务执行快照的时候，会生成数据库系统当前的快照，记录并维护系统当前活跃事务的 ID

ReadView 的三个属性

trx_list：未提交事务 ID 列表
up_limit_id：记录 trx_list 列表中最小的事务 ID
low_limit_id：下一个事务 ID，就是当前最大 ID + 1

SQL 执行流程#

连接器：数据库连接，身份认证，权限校验
分析器：词法分析（提取关键词，select、表命、查询条件等）、语法分析（检查 SQL 是否符合 MySQL 规范）
优化器：使用（IO 成本 + CPU 成本）最小的索引来执行查询
执行器：调用存储引擎接口执行 SQL
- 查询 BufferPool 中是否有需要查询的数据，如果没有去磁盘查询，之后将数据放入 BufferPool
- if 执行的是 update，记录 undo log，在 BufferPool 中 udate 数据。（BufferPool 中的数据又可能和数据库中的数据不一致）
- BufferPool 中的数据已更新，记录 redo log（先写入到 redo log buffer 中，再在合适时间写入磁盘中。）
- 记录 bin log
- 将 bin log 文件名和当前语句更新内容记录到 redo log
- redo log 后添加 commit（两阶段提交）

索引失效#

操作符（ = ）左右两边的数据类型不同时，会发生隐式转换。

失效的情况#

当 where 左边为数值类型时会发生隐式转换，索引不会失效，对效率影响不大。
当 where 左边为字符类型时会发生隐式转换，索引会失效，查询变成全表扫描，效率低。

字符串 => 数值转换方式：#

所有不以数字开头的字符串都转换为 0。（例如abc，abc123 => 0）
以数字开头的字符只截取读一个数字段。（例如123abc4 => 123，012ab4 => 12）

失效案例#

索引失效案例：num2 为varchar类型

SELECT * FROM `test1` WHERE num2 = 10000;

执行这条 SQL 时索引失效，发生全表扫描，因为'10000a'，'010000'，'10000'等等都能转为浮点数10000，这样的情况下，是不能用到索引的

索引失效#

上述例子中，由于类型转换导致索引失效
使用！= > < 导致索引失效
模糊查询，如 % S%，会导致索引失效，S% 不会导致索引失效

MySQL 中语句执行优先级#

单表#

form > where > group by > select > order by > limit

多表#

from > join > on > where > group by > AVG,SUM > having > select > distinct > order by > limit

慢查询优化#

使用 EXPLINE 关键字分析查询语句

查询使用索引#

使用 LIKE 模糊查询时，第一个时 % 查询不走索引

优化数据库结构#

将字段较多的表拆分，将不经常查询的数据新建表

相关概念#

drop、delete 与 truncate#

用法不同#

类型不同#

执行速度不同#

utf8 和 utf8mb4#

数据库范式#

第一范式 1NF#

第二范式 2NF#

第三范式 3NF#

MySQL 数据类型#

整形#

浮点数#

字符串#

时间#

MySQL 基础架构#

MySQL 存储引擎#

MyISAM#

InnoDB#

MyISAM 和 InnoDB#

MySQL 事务#

事务的特性#

事务引发的问题#

事务隔离级别#

事务隔离级别实现方式#

默认隔离级别#

MySQL 锁#

表级锁和行级锁#

行级锁使用#

常见锁🔒 的类型#

InnoDB 中的锁#

当前读和快照读（）#

MySQL 性能优化#

文件存储#

IP 地址存储#

尽可能将列定义为 NOT NULL#

MySQL 索引#

什么是索引#

索引的数据结构#

Hash 表#

B 树#

B + 树#

B + 树和 B 树比较#

MyISAM 和 InnoDB 索引#

索引类型#

主键索引#

二级索引（辅助索引）#

聚集索引和非聚集索引#

聚集索引#

非聚集索引#

覆盖索引#

联合索引#

最左前缀匹配原则#

索引字段创建原则#

MySQL 日志#

redo log#

刷盘时机#

日志文件组#

binlog#

记录格式#

写入机制#

两阶段提交#

undo log#

三种日志的作用#

MVCC#

MVCC#

实现原理#

隐式字段#

undo log#

ReadView#

SQL 执行流程#

索引失效#

失效的情况#

字符串 => 数值转换方式：#

失效案例#

索引失效#

MySQL 中语句执行优先级#

单表#

多表#