MySQL

SQL与NoSQL

关系型数据库

采用了关系模型来组织数据，关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织

优点：①容易理解，二维表比网状模型贴近逻辑世界 ②使用方便，支持SQL复杂查询 ③易于维护，通过完整性降低了数据不一致和数据冗余 ④支持事务 ⑤数据存在磁盘中可靠

实体完整性 每个元组唯一、可识别，不允许主键空/重复
引用完整性 元组间的关联，外键为参照的某个元组主键或空
用户定义完整性 指明属性取值范围，防止和应用语义矛盾

缺点：①表结构固定、数据不易扩展 ②海量数据情况下读写效率低 ③ 高并发读写能力差，数据库连接数、IO受限

非关系型数据库

通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定，常用于存储非结构化的数据。分为键值、列族、文档、图形数据库

优点：①支持多种存储格式：kv、图片、文档 ②速度快、效率高，可以用内存作为载体 ③数据不耦合易扩展 ④可以实现数据分布式处理

缺点：①不支持SQL学习使用成本高 ②没有事务处理，无法保证数据完整性和安全性

事务隔离级别

隔离级别	特点	脏读	不可重复读	幻读
READ UNCOMMITTED	事务没提交，其他事务就能看到修改的数据	可能	可能	可能
READ COMMITTED	只能看到其他已提交事务的结果，不能看到正在执行的事务修改	不可能	可能	可能
REPEATABLE READ	事务执行中看到的数据和启动时一致	不可能	不可能	可能
SERIALIZABLE	强制事务串行执行，避免幻读，但大量加锁性能低下	不可能	不可能	不可能

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 RC

与 SQL 标准不同的地方在于：InnoDB 在默认的隔离级别 RR 下使用的是Next-Key Lock 锁算法，能避免幻读，已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE 隔离级别。

MVCC

MVCC 是 InnoDB 实现隔离级别的一种方式，用于实现读已提交和可重复读这两种隔离级别。而读未提交级别总是读取最新的数据行，无需使用 MVCC。可串行化级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。优点是执行普通的SELECT操作时通过访问记录的版本链，使不同事务的读-写操作并发执行，不用加锁

快照读与当前读

在MVCC并发控制中，读操作可以分成两类：

快照读：读取的是快照生成的数据，如简单select操作。不用加锁，通过undo log和MVCC访问历史版本数据。
- 在RR下，事务启动时会生成readView直至事务提交，所以之后其它事务所做的更新版本对当前事务并不可见，实现了可重复读
- 在RC下，每次select都会生成readView，所以事务期间多次读一个数据可能不一致
当前读：读取的是记录的最新版本，如插入/更新/删除操作。要对读的记录上锁，保证其他事务不会再并发修改这条记录，除了lock in share mode / for update 加读锁，其他都加写锁

原理和实现

隐藏字段：每行数据都有隐藏字段，包含了最后一次插入/更新事务DB_TRX_ID、指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。
ReadView：在进行快照读的时候生成的记录快照，保存当前系统中活跃的事务ID，可以解决可见性问题。当要读取记录行的时候，会将该记录行的 DB_TRX_ID 与 Read View 进行比较，判断是否满足可见性条件，否则通过undo log将数据恢复到指定版本：
- 记录行事务ID < up_limit_id（ReadView中最小的事务ID）：该事务在创建 ReadView 前已经提交了，可见
- 记录行事务ID > low_limit_id（ReadView时刻下一个要分配的事务ID）：该事务在创建 ReadView 之后才开启，不可见
- up_limit_id < 记录行事务ID < low_limit_id：判断是否在活跃事务ID列表中，不在说明该事务已经提交了，可见，否则不可见

解决幻读

如果其它事务在当前事务查询范围内插入就会产生幻读，InnoDB存储引擎在 RR 级别下通过 MVCC和 Next-key Lock 来解决幻读问题：

快照读：通过 MVCC 避免幻读。
RR 隔离级别只会在事务第一次查询生成 Read View ，直至事务提交。所以在生成 Read View 之后其它事务所做的插入版本对当前事务并不可见
当前读：通过 Next-key Lock 避免幻读
Record Locks + Gap Locks，前者锁定一个记录上的索引项来锁定当前记录，后者锁定索引之间的间隙来防止其它事务在查询范围内插入数据。当查询的索引含有唯一属性的时候，Next-Key Lock 会降级为Record Lock

引擎

MyISAM适合：插入不频繁、查询非常频繁，大量SELECT

InnoDB适合：表更新和查询都频繁，大量INSERT或UPDATE；可靠性要求比较高，或者要求事务

MyISAM 只支持表级锁，InnoDB 支持行级别的锁。
MyISAM 不提供外键、事务、MVCC，InnoDB 支持。
MyISAM 索引文件和数据文件分离，而 InnoDB 数据文件本身就是按 B+Tree 组织的一个索引结构，叶节点 data 域保存了完整的数据记录。
MyISAM 不支持崩溃后恢复，而 InnoDB 支持。
InnoDB 的性能比 MyISAM 更强大。

索引

索引是Xxx数据结构、加快查询 -> 不同引擎实现不同 -> 优缺点 -> 类型 -> 使用注意事项
索引原理（数据文件组织成B+Tree） -> 主索引与辅助索引 -> B+Tree是啥 -> 与Hash对比

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以提高查询速度，快速找出在某个或多个列中为特定值的行。索引是在存储引擎中实现的，每种存储引擎的索引都不一定完全相同。MySQL中索引类型有 B+TREE 和 HASH，InnoDB存储引擎只支持 B+TREE 索引。

优点：

创建唯一索引可以保证表中每一行数据的唯一性。
加快查询速度、查询中分组和排序的时间。

缺点：

创建和维护索引要耗费时间
索引需要占磁盘空间

索引类型

普通索引：仅加速查询
唯一索引：列值唯一（可以有null）
主键索引：列值唯一（不可以有null）+ 表中只有一个
组合索引：多列值组成一个索引，专门用于组合搜索
全文索引：对文本的内容进行分词，进行搜索
覆盖索引：select的数据列只用从索引中就能够取得，不必回表查询数据行
聚集索引：索引和数据一起存放，如InnoDB的主键索引。查询速度非常快，比非聚簇索引少了一次读取数据的 IO 操作；但更新代价大，数据被修改导致对应的索引也会被修改
非聚集索引：索引和数据分开存放，如InnoDB的辅助索引、MyISAM 的主键/非主键索引。更新代价小，因为叶子节点不存放数据（指针或主键），但是可能会二次查询(回表)

索引注意事项

尽量用在经常搜索、排序的列上，长期不用及时清理
尽量用覆盖索引，减少 select * 能减少回表次数
多个单列索引每次查询只能使用一个，换成组合索引
索引失效：
- 组合索引不遵循“最左前缀匹配”原则、含NULL
- 在索引列上计算/函数、不等于（>< !=）
- 使用like "%value%"
- or连接
- 字符串不加单引号可能隐式转换

索引原理

InnoDB中表数据文件本身就是按B+Tree组织的一个索引结构，这个索引的key是数据表的主键，因此数据文件本身就是主索引，B+Tree的叶节点data域保存了完整的数据记录。

InnoDB的辅助索引data域存储相应记录主键的值而不是地址，所以辅助索引搜索需要检索两遍索引：先检索辅助索引获得主键，然后用主键到主索引中检索获得记录

B+Tree：多路平衡查找树，所有数据都按键值的大小顺序存在叶结点中，通过指针连接。B+树索引的特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB，B+树的高度一般不超过4层，查找的IO次数很少。

B+树内部结点不保存数据，只用于索引，所有数据都保存在叶结点中
m阶B+树内部结点最多有m-1个关键字、m个子树，叶子结点最多存储m-1个记录
内部结点中的key都按照从小到大的顺序排列，key左子树中的key都小于等于右子树中的key
叶子结点双向链表相连，自小而大有序

Hash索引和B+Tree索引区别

hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围
hash索引不支持使用索引进行排序
hash索引不支持模糊查询以及多列索引的最左前缀匹配
hash索引虽然在等值查询上较快但是不稳定（发生hash碰撞），而B+树的查询效率比较稳定

B+Tree比BTree优势

方便扫库，B树只能中序遍历
查找效率更稳定，叶结点存放数据内容，每次查询路径都相同
磁盘读写代价更低，因为内部结点只包含关键字，体积小，一个盘块内容纳的关键字数量多，IO读写次数就少

三种日志

bin log：逻辑日志，把对数据库的所有修改操作（SQL语句）以二进制的形式记录在日志文件中，包括了SQL语句的执行时间和消耗资源，以及相关的事务信息。
- 用途：主从同步、数据恢复（但无法判断哪些数据已刷盘）
- 刷盘策略：先写到 bin log cache 中，① 每次刷盘 ② 只写到页缓存，不刷盘 ③ 写到页缓存，N 次事务提交后才刷盘
redo log：事务持久性 物理日志，把对数据库页的物理修改操作（包括页地址和修改前后的数据值）记录到二进制文件中，当事务进行修改操作时，采用 WAL 机制，先写 redo log 日志，再写数据。这些记录会先写入到内存中的redo log buffer，后续刷盘到redo log文件中。redo log 分配了固定大小，循环且顺序写入，写满了就等待脏页刷新到磁盘。
- 用途：① 崩溃恢复，记录了未刷盘的数据页变化 ② 优化磁盘随机I/O->顺序I/O，减少无效I/O（避免每次刷16KB）
- 事务提交时刷盘策略：① 立刻刷盘fsync() ② 立即写页缓存，但每秒刷盘 ③ 每秒写页缓存并刷盘
undo log：事务原子性和一致性，逻辑日志，记录了事务所做的更改的相反操作。数据修改前，根据SQL生成相应的 undo log 记录保存到buffer中，后台线程定期刷盘。
- 用途：事务回滚、MVCC：当读取记录时，若该记录当前版本对该事务不可见，则通过 undo log 读取之前的版本数据，从而避免锁竞争，提高并发性能

ACID的实现

A 原子性：undo log 事务的操作要么全成功、要么全失败回滚

D 持久性：redo log 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

I 隔离性：多个并发事务之间要相互隔离，不能干扰，包括四个级别

写对写：锁机制保证隔离性
事务先获得锁才可以修改数据，期间其他事务等待。InnoDB支持表锁和行锁，出于性能考虑通常使用行锁。
写对读：MVCC保证隔离性
InnoDB默认的隔离级别是RR，使用MVCC解决脏读、不可重复读、幻读等问题，优点是读不加锁，因此读写不冲突，并发性能好

C 一致性：数据库本身确保插入数据类型、长度正确，应用层面如转账逻辑。其余三个是一致性的前提。

锁

表级锁

表锁
元数据锁：防止其他线程修改表结构，不用手动，而是由数据库自动加上
意向锁：加读/写锁前要加读/写意向锁，用于快速判断表里是否有记录被加锁，从而在加独占表锁时不用去遍历每条记录是否加了锁

行级锁

记录锁：实际是给索引项加锁，不同事务可以使用不同的索引锁定不同行。注意：只有通过索引条件检索数据才使用行锁，否则会把表里的索引项都加锁，相当于锁了整表。
间隙锁：为了解决可重复读隔离级别下幻读
Next-Key Lock：前两者结合
插入意向锁：插入一条记录时，如果插入位置已被其他事务加了间隙锁，插入操作会生成一个插入意向锁并阻塞。不属于意向锁，而是一种特殊的间隙锁，只锁住一个点。

主从复制

主从复制流程

主库db的更新事件(update、insert、delete)被写到binlog
从服务器 I/O 线程向主服务器请求 binlog 文件，主库用 binlog dump 线程发给从库。
从库接收 binlog 内容并写入到relay log（中继日志）
从库创建一个SQL线程，从relay log里面读取事件并执行，使主从库数据一致

用途

读写分离：主库写、从库读，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作
数据实时备份：当系统中某个节点发生故障时，可以方便的故障切换
架构扩展：把IO访问的负载分布到多个节点

高性能

数据库结构优化（联合查询的建立中间表、合理加冗余字段）

加缓存层、加索引

主从读写分离

数据库拆分、分布式架构

分库分表

优势：分表解决单表海量数据的查询性能问题，分库解决单台数据库的并发访问压⼒问题

缺点：表在不同结点上难以join查询、需要各自排序最后汇总重排，自增主键重复，分布式事务

垂直切分

垂直分库：根据业务耦合性，将关联度低的不同表存储在不同的数据库
垂直分表：如果某个表字段较多，可以将不常用或长度大的字段拆分到扩展表中。
- 通过”大表拆小表”，更便于开发与维护
- 避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外开销
- 数据库以行为单位加载到内存中，表中字段长度越短内存能加载行就越多、命中率更高，减少磁盘IO

水平切分

水平分表：按照某规则（HASH、RANGE）把一张行数多的大表分到多张表，仍在同一个数据库
水平分库：分到多个数据库中
- 没有减轻MySQL服务器压力，仍然竞争一个CPU、内存、IO

优化

查询优化

使用索引：如果查询时没有使用索引，查询语句将扫描表中的所有记录，使用索引则可以快速定位到待查询记录。但几种特殊情况下索引可能失效。
优化子查询：子查询嵌套SELECT执行效率不高，因为 MySQL 需要为内层查询语句的结果建立一个临时表，用于外层查询语句从临时表中查询记录，最后撤销临时表。可以使用连接查询来替代子查询，无需建立临时表，如果查询中使用索引性能会更好。

插入优化

禁用唯一性检查
禁用外键检查
禁止事务的自动提交

慢查询优化

索引失效情况
优化数据库结构
- 低频字段分离出来形成新表
- 经常联合查询可以建立中间表
分解关联查询
对每个表进行单表查询，然后将查询结果在应用程序中进行关联。
优化LIMIT分页
当偏移量非常大的时候，如 limit 10000,20，前面的10000条记录都将被舍弃，代价很高。对此要尽可能用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。
1
2
3
4
select * from table
where id in (
select id from table where age > 20 limit 1000000,10
)

表数据量过大

优化SQL
增加索引、缓存
读写分离，如主从复制
使用MySQL自带的分区表，这对应用是透明的，无需改代码，但SQL语句是要针对分区表做优化的；
分库分表

Explain

Explain关注的结果：

列名	备注
type	本次查询表联接类型，从这里可以看到本次查询大概的效率
key	最终选择的索引
key_len	用到的索引实际长度，越短越好
rows	预计需要扫描的记录数，越小越好
Extra	额外附加信息

Type关注以下几种结果：

类型	备注
ALL	全表扫描，最差
range	利用索引进行范围查询
ref	基于索引的等值查询
const	基于主键或唯一索引查询，最多返回一条结果
system	查询对象表只有一行数据，最好

Extra关注以下两种结果：

关键字	备注
Using filesort	将用外部排序而不是按照索引顺序排列结果，在磁盘完成排序代价非常高
Using temporary	将创建临时表来存储结果，通常由于对没有索引的列进行GROUP BY

其他

范式

1NF：数据库表的每一列都是不可分割的原子数据项。如果不能满⾜第⼀范式，就不称为关系型数据库
2NF：非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
3NF：非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

自增ID

如果使用自增ID，每次就不把新的纪录插入到当前索引结点的后续位置，一页写满再开辟下一页。若不使用自增ID（如身份证号），则每次插入的主键随机，记录会被插入索引页中间的位置，导致频繁移动、分页造成大量碎片，后续不得不用OPTIMIZE TABLE来重建表并优化填充页面

执行SQL过程

客户端请求->
连接器（验证用户身份，给予权限） ->
查询缓存（存在缓存则直接返回，不存在则执行后续操作）->
分析器（对SQL进行词法分析和语法分析操作） ->
优化器（主要对执行的sql优化选择最优的执行方案方法） ->
执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）->
去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

海量数据排序

数据库海量数据排序（外排）

在分割段的阶段，使用内部排序，生成n个大小等于可用内存的顺串，最后再进行归并，使得数据整体有序

但是，为了避免 I/O 操作带来的影响，可以使用替换-选择排序的方式，使得在分割段阶段生成的顺串大小大于可用内存大小。

同时为了能够再次减小 I/O 开销，合并阶段可以适量增加归并的路数，不过增大路数就意味着内部缓冲区数量增加（一个缓冲区一个顺串），每次要在更多的缓冲区中选取最小值，所以引入败者树将比较次数从 O(k) 降到 O(logk)