mysql入门-一年水平层次

sql执行顺序 记不住啊记不住，别问我这个

1 from
2 where
3 group by
4 聚合函数
5 having
6 select
7 order by
8 union

架构理解

Mysql架构可以分为连接池，接口层，查询分析器，优化器，全局Cache与buffer，存储引擎和磁盘层。

对于InnoDB存储引擎，会通过自己的四种线程完成自己特定Cache与buffer和磁盘的交换。
后台线程分为io线程（用于读写数据，包括对insert buffer，change buffer的修改以及日志的读写，），purge线程(专门处理undo的回收与使用)，Page Cleaner线程(用于脏页刷新)，还有master Thread.

InnoDB主要把缓冲池区分为数据页，重做日志缓冲，change buffer以及插入缓冲页等。
内存页以LRU的形式进行存放。尾部页，也就是距离上次使用最久的页，在内存有限的时候就会被优先回收。
重做日志缓冲用于快速记录重做日志，当缓存达到上限或者事务提交时，重做日志缓冲才可能会持久化到磁盘层的重做日志中去。
change buffer是用于放置修改的记录，如果当页不在内存的时候，change buffer会把修改记录下来，后续再根据purge操作写入磁盘页。
插入缓冲页先前被称为insert buffer，后功能增加之后改为insert cache，5.7之后取消掉，改成直接往内存页写入，该页有自己的lru链

三大范式与表设计

三范式是一种表设计原理
第一范式：每一列应该保持原子性
第二范式：表应该有主键，并且每一列都需要完全依赖于主键，如果只是部分依赖于主键（主键为多列时只依赖于其中一列），那么需要拆分表
第三范式：每张表应当只存在一个候选键满足第二范式

索引

数据结构

B+树是一种平衡n叉搜索树，平衡意味着子节点深度相差不大于1，搜索树意味着它有序。
每个节点相当于一页，每次想要读取一页，就得从磁盘读到内存。
非叶子节点只有子节点的首主键和指向子节点的引用，只有叶子节点才有数据。
叶子节点除了有主键，还有记录，除此之外，叶子节点之间还有next指针形成循环单链表

比起B树，
1 B+树非叶子节点不放数据，这样可以每次读取更大非叶子节点索引页，从而减少磁盘IO
2 叶子节点间用链表连接，这样可以更好服务于范围搜索

一般而言，某一页只有一半就会和零一页合并，某一页满就会分裂成两页，并且这种变化会自底向上地进行，直到上一层不触发，除此之外，左旋右旋也会帮忙处理深度差问题。

分类

1 聚簇索引，也称为主键索引，是数据在磁盘中的物理排列，每次被选中的页会被拿到内存
2 非聚簇索引，它虽然也是一棵B+树，但数据并不完善，往往需要回溯。
非聚簇索引以一个或多个值建立一颗索引树(其中必定存在主键)，存放着字段1 字段2 ...字段n key，根据索引顺序依次查找，也就是说和select 的字段顺序无关，只与index字段顺序有关

索引可以设定为唯一，非空，比如聚簇索引就一定两者兼备

性能调优

1 覆盖索引，建立多值索引时，可以将经常select的字段加入index中，这样可以避免回表
2 最左匹配原则，由于多值索引从左向右匹配，所以把最频繁的加在最左边，这样可以减少额外创建索引。注意，对于字符串而言，也有最左匹配子字符串。
3 除非KV数据库，尽量使用自增主键，因为自增主键利于B+树顺序插入
4 使用explain select 语句来看是否有使用索引，和是否索引只有部分生效

失效情况

1 函数加在等号左边，导致二级索引无法触发，直接在聚簇里寻找。
2 类型转换导致隐式函数变换，产生第一条
3 where中出现范围查询使后面索引字段无法使用
4 not型，如不等于，NOT LIKE，NOT NULL使得只能直接搜索聚簇索引
5 最左匹配，中间的无法使用上索引字段导致索引没有完全生效

事务

事务保证了一致性，原子性，事务提交的一瞬间会释放自己拥有过的所有锁

读未提交

事务的操作可以直接被另一个事务可以直接查看，适合特别短的操作，但并不适合同步操作

读提交

只有事务提交了另一个事务才能看到操作，但是一个事务如果提交了，另一个事务立刻会发现数据变化。
这并不符合一致性，因为在select后，它的操作中并没有将数据变成这样，然而数据却显示成这样了

可重复读

每个事务生成时会产生自己的transaction Id，当事务操作了一条数据，它会在新数据的row trx_id中记录自己的transaction Id，然后undo Log也会生出对应的语句
在InnoDB中，通过MVCC来完成读一致性
一种无锁的隔离机制，当隔离级别设置为可重复读时，当事务开启时，就会把当前所有还未提交的事务记录下来，并且获取其中最小值作为低水位，将当前数据库最大事务ID+1作为高水位，这样在使用MVCC的时候进行版本比较时，如果事务版本高于高水位，就直接回退，如果事务版本低于低水位，就直接采纳，如果位于两者之间，就看看活跃数组中是否存在。

但如果数据遭遇写(读)事件，这时又会变成纯粹当前写(读)，上写(读)锁，不允许其它事务访问。

列数不同被称为幻读，由insert导致，由于新插入的数据transaction id 必定处于数组或高水位上，MVCC解决了这个问题，但是并不是每个数据库或每个引擎都使用MVCC解决可重复读问题。
所以一般意义上，我们说可重复读隔离级别并不能解决幻读问题。

串行化

每一行都会上锁，并且涉及到未有数的范围会上间隙锁

锁

锁的自动解开

1 事务结束，释放所有锁
2 客户端取消请求或断开连接

全局锁

一般就是上读锁，这样可以禁止修改数据库数据

表锁

X锁和S锁

就是在表上增加写锁和读锁
加锁lock tables ... read/write
解锁unlock tables ... read/write

意向锁

意向锁的存在是告诉修改表的语句，有事务正在拥有写锁或读锁，而不用去逐行检查
IS锁是共享锁，IX锁是独占锁。

页锁

行与表之间级别的锁，以一个数据页为单位，仅作了解

行锁

注意：行锁并不没有释放语句，而是等事务完成才释放锁
由于写锁是独占式，所以上写锁应该尽可能在后面

读写锁

select xx in share mode 同时会在表级别上IS意向锁
select xx for update 同时会在表级别上IX意向锁

间隙锁

锁住数据行之间的空隙

邻键锁

也称为next-key lock，意为锁到下一个键的锁，是读写锁和间隙锁的合体

其实三者相辅相成

举个栗子
假设目前有一个id为25
那么

select * from table
where id=25

会调用普通读写锁

select * from table
where id>25

会锁住(25,正无穷),也就是间隙锁

select * from table
where id>=25

会锁住[25,正无穷)，也就是邻键锁

死锁（理论）

事物之间相互占据锁，不会主动释放导致的

死锁检测

1 设置事务处理时间，到时间直接回滚事务
2 构建锁图的数据结构，将事务及其依赖的锁按箭头标记，如果可能形成循环，说明死锁

死锁优化

将经常访问的值分成多份，这样每次写可以选择其中一个写，如果要读可以读总和值，从而减少并发

持久化与崩溃恢复

持久化的四大核心

checkpoint

checkpoint是标识重做日志记录对应数据是否写入数据页，它与LSN配合，可以完成redolog的恢复，checkPoint的更新只发生在重做日志不够用时，重做日志需要更大空间时，redoLog会完成checkpoint之后逐条记录的比对，如果这条记录的LSN小于数据页的LSN，那么可以直接跳过去，如果这条记录的LSN大于数据页的LSN，那么就会把对应的脏页刷回数据库。

LSN

LSN是一个8字节数字

在两个时机会写入LSN
1 每次重做日志写入，会产生LSN并将LSN写入这条记录。
2 当脏页写入的时候，会产生LSN并将LSN写入这一页。
脏页写入会发生在至少以下三种情况
1 当buffer满的时候，我们需要把脏页刷回数据库，这时候会产生新的LSN，被写入的脏页都会记录这个LSN。
2 如果redo log文件满了，那么需要把整个文件重新清空，此时需要比较每一条记录的LSN与它操作页的LSN，如果这条记录操作的页的LSN大于记录的LSN，那么不更新。否则的话，执行脏页写入，和2一样，脏页写入需要记录新的LSN。
3 空闲时后台线程会完成脏页写入

当崩溃恢复的时候，我们从checkPoint开始逐行判断redo log记录，如果当前redo log中记录的LSN是小于这一页的LSN，说明redo log修改的数据早已刷新回磁盘了，那么我们就不需要恢复这条数据的redo log，如果是大于，那么就会把对应页提到内存进行执行

redo log

redo log是先写在redo log buffer，再写到page cache 再写到disk的
redo log的写入时机由四个因素决定
1 innodb_flush_log_at_trx_commit，这个是在事务提交时触发的，如果为0，那么不会write，如果为2，只会write到page cache，如果为1，那么会直接持久化到disk
2 后台线程每秒都会把buffer中的数据先write再fsync到disk
3 buffer满一半的时候，会直接触犯write
4 如果把innodb_flush_log_at_trx_commit设置为1或者2，那么其它线程在事务提交时，会把整个buffer给进行fsync或者write

binlog

binlog也分为buffer，page cache和disk
在使用sync_binlog时，会将数据完成到page cache和disk的写入

崩溃恢复

首先恢复数据文件，然后对重做日志进行执行。
从checkPoint开始对数据进行以下操作，
先判断这条数据是否需要重做，也就是LSN判断是否大于数据页LSN，如果大于，就执行redo log
将redo log完全执行到write pos之后，
完成上面步骤之后事务就已经恢复了，但是执行事务的线程肯定是挂了，所以后续是否还有sql需要执行，还是直接commit页没人知道了，mysql对于这种情况采取的策略是，如果binlog已经记录了commit，那么就把这个事务完成commit，如果binlog没有记录commit，那么就使用undo log将事务回滚。

参考

《Mysql45讲》 林晓斌
尚硅谷 宋红康