数据存储/MySQL/MySQL 学习总结

MySQL 学习总结

MysQL 架构

InnoDB 不是 MySQL 自带的存储引擎，是一种可插拔的插件。InnoDB、MyISAM、MEMORY存储引擎是根据表来设置的，同一个库不同的表可以使用不同的存储引擎。

不同的存储引擎在索引、事务、锁的支持上是有区别的

存储引擎	事务	锁	分布式事务
InnoDB	支持	表锁、行锁	支持
MyISAM	不支持	表锁	不支持

MySQL 的锁

排他锁、共享锁、独占锁(MyISAM)、自增锁、间隙锁、意向锁

共享锁：将数据对象变为只读形式，不能进行更新，也称为读取锁定；lock in share mode
拍他锁：当执行 INSERT\UPDATE\DELETE 的时候，其他事务不能读取该数据；for update
间隙锁：对某个范围加锁，这样可以用来解决范围内插入造成幻读的问题

InnoDB 默认情况下是行锁，但是如果表没有索引的时候加的是表锁。

死锁：互相占用需要的资源，MySQL 解决死锁的方式：超时检测；wait for graphy 深度优先遍历检测有没有环

MySQL 日志

binlog：归属于 server 层，stat、row、mixed三种格式
慢查询日志：
redo日志：物理日志，保证所有数据的完整性，针对数据页操作记录
undo日志：逻辑日志，保证单行数据实现原子性，还可以用来实现 mvcc(多版本并发控制)；针对数据行操作

事务

数据库事务是构成单一逻辑工作单元的操作集合

数据库事务可以包含一个或者多个数据库操作，这些操作构成一个逻辑上的整体
构成逻辑整体的这些数据库操作，要么全部执行成功，要么全部不执行
构成事务的所有操作，要么全部都对数据库产生影响，要么全部不影响，即数据库总能保持一致性状态
并发操作下，事务的控制尤其重要

原子性 A

所有操作作为一个原子，要么全部成功，要么全部不成功。

通过 undo 日志来实现，数据库在执行操作时首先将操作之前的数据备份到 undo log 中，然后在事务执行失败后用户执行了 rollback 操作，利用 undo log 将数据恢复到事务执行之前。

undo log 是逻辑日志，undo 日志针对的行数据操作进行记录

一致性

事务的执行结果使数据库从一个一致性状态到另一个一致性状态；一致性状态是指：1、系统的状态满足数据的完整性约束，2、系统的状态反应数据库本应描述的现实世界的状态。

事务的四个特性中，一致性是事务的根本追求。但是事务的并发执行、事务故障和系统故障会对一致性造成破坏。并发控制技术保证了事务的隔离性，使得事务在并发情况下不会破坏一致性；undo 日志恢复技术保证了事务的原子性，使一致性状态不会因为事务或系统故障破坏；redo 日志保证提交的修改不会因为系统奔溃而丢失，保证了事务的持久性。

隔离性

并发自行的事务不会互相影响，和串行化执行时一样。完全的隔离性会导致系统性能降低，降低对资源的利用率，但是降低隔离性后也会导致一致性标准降低，所以根据不同的业务需要也会有不同的隔离级别。

读未提交(READ UNCOMMITED)：存在脏读、不可重复度、幻读问题
读已提交(READ COMMITED)：存在不可重复度、幻读问题
可重复度(REPEATABLE READ)：默认，存在幻读问题；在同一个事务中重复读取数据，读取到的是一致的
串行化(SERIALIZABLE)：

几种问题：

脏读： A 客户端可以读取到 B 客户端事务还没有提交的更改
不可重复读：A 客户端正在修改数据，B 客户端在整个事务过程中读取到的数据不一致(A 事务提交之前和提交之后)
幻读：在 A 客户端中插入数据，在 B 客户端中范围读取数据，当 A 插入的数据在这个范围中时，此时 B 是读取不到的，但是当 B 也插入该数据时有可能会成功有可能会失败(具体看表设计)。

实现原理-基于锁的并发控制流程：

事务根据自己对数据项的操作类型申请相应的锁（读申请共享锁，写申请拍他锁）
申请锁的请求发给锁管理器，锁管理器根据当前数据项是否已经有锁以及申请的和持有的锁是否冲突决定是否为该请求授予锁
若锁被授予，则申请事务的锁可以继续执行；若被拒绝，则申请锁的事务继续等待直到其他事务放弃锁

也可以通过时间戳、有效性检查和快照隔离实现并发性控制

持久性

事务一旦提交，其对数据库的更新就是持久的，任何事务或者系统故障都不会导致数据丢失。

利用 redo log 来实现，事务提交前，先将 redo log 持久化，再持久化数据。当系统奔溃时，系统可以根据 redo log 将数据恢复。InnoDB 根据 innodb_flush_log_at_trx_commit 参数来设置：

事务提交，每秒写入 OS Buffer，并调用 fsync() 写入磁盘
事务提交，每次提交时写入 OS Buffer，并调用 fsync() 写入磁盘
事务提交，每次提交时写入 OS Buffer，并调用 fsync() 写入磁盘

索引

索引帮助高效获取数据的数据结构，存储在文件系统中，文件的存储形式与存储引擎有关(InnoDB-.idb；MyISAM-.myd,*.myi)。主要有 hash 索引、二叉树、B-Tree、B+Tree。InnoDB 索引主要使用B+Tree，但是在 InnodB 内部，当某索引访问次数过多时会将其转化为自适应的哈希索引。

索引的结构

哈希索引

数组+链表来实现，查询的时候需要先计算查询条件的哈希值，然后找到数组对应位置，然后链表比对查找。

缺点

哈希算法需要尽可能避免哈希碰撞，哈希碰撞会导致哈希索引性能退化；
等值查询的时候哈希索引才能生效，范围查找哈希索引无效；
哈希索引需要将数据文件存储在内存中，比较耗费空间；

二叉树、红黑树索引

当树的深度过深而造成 IO 次数变多影响效率。

B 树索引

所有键值分布在整个树中，找到节点就找到了数据，不用再去映射；key 存储节点地址信息，value 存储对应的行数据
搜索可能在非叶子节点就找到，性能较高
每个节点最多 m 个子树，根节点至少 2 个子树
分支节点拥有 m/2 棵子树，
所有叶子节点都在同一层，每个叶子节点最多可以有 m-1 个 key，并且以升序排列。

缺点

每个节点既要存储地址信息还有 data 数据，如果 data 数据过大会导致 IO 的压力过大，增加 IO 次数也会导致性能下降。

B+ 树

在 B 树的基础上，所有的非叶子节点不再存储 data 数据，叶子节点存储 key 和 data 数据。
B+ 树有两个头指针，一个指向根节点，一个指向关键字最小的叶子节点
叶子结点是一种链式环结构因此可以对 B+ 树进行两种查找：一种是对于主键的范围查找和分页查找；一种是从根节点开始的随机查找

这里的 key 是在建表的时候，如果有主键则用主键，否则用唯一键，如果还没有则会生成一个 6 字节的 rowId 来用。

B+ 每个叶子结点块节点个数是有限的，所以如果要插入数据则有可能引起叶子节点块儿分裂，分裂后还要调整上层的非叶子节点，所以在设置主键的尽量可以设置成自增的；当删除的时候又可能需要进行合并。

MyISAM 和 InnoDB 都使用了 B+ 树，但是在叶子节点上，InnoDB 存储了实际的行数据，MyISAM 存储的是数据地址

索引的分类

主键索引

可以通过主键在索引中直接查找，找到后即可拿到数据

辅助索引

给非主键列添加索引，索引的实现结构还是 B+ 树，但是叶子节点存储的不是实际数据了而是对应的主键值；查找的时候先根据普通索引查找到对应的主键，然后在逐渐索引中查找对应的数据

唯一索引

主键：唯一且非空，唯一键可以空

全文索引

全文索引更适合做搜索相关

组合索引

组合索引在存储的时候先判断第一列然后判断第二列。。。将其构建为 B+ 树

索引技术名词

回表

普通索引查询后需要根据主键索引去查找对应的数据叫做回表

最左匹配

组合索引中先从组合索引左列开始匹配，如果没有使用最左列的条件，则组合索引失效；如果查询条件都使用了组合索引的列但是顺序不是组合索引的顺序，MySQL 的优化器会将其进行优化。

索引覆盖

在查询的辅助索引的时候，如果叶子节点保存的刚好是要查找的字段数据叫做索引覆盖

索引下推

现通过索引讲符合记录筛选出来然后在回表查询对应数据，减少回表查询的数据行