2、MySQL总结

一、基础部分

（一）、概念

关系型数据库

（二）、SQL编写

CRUD（查询、插入、更新、删除）

左右连接、内连接、子查询

（三）、存储过程、存储函数

存储过程和函数（一）-CSDN博客

（四）、触发器

二、高阶部分

（一）、索引

1. 索引底层结构

InnoDB行格式（行格式：一条记录的存储结构）

1.1. 变长字段长度列表

非必须，逆序存放

1.2. null值列表

非必须，逆序存放

1.3. 记录头信息

delete_mask：标示这条记录是否被删除 

next_record：下条记录的位置

1.4. 真实数据

row_id：指定了主键或者唯一约束列（不包含null值的唯一列），则无row_id；非必需，占6字节。

trx_id：这条数据由哪个事务生成的；事务id，必须，占6字节。

roll_pointer：这条记录上个版本的指针，必须，占7字节。

2. B树、B+树、二叉树、红黑树、Hash

B+🆚二叉树：相同的数据二叉树需要更多的IO次数，二叉树只有两个分支，存储相同的数据，树更高，IO次数更多；

B+🆚B树：B树非叶子节点也存放数据，B+树只存放索引，所以B+树单个节点的数据量更小，相同的IO次数，查询到更多的节点；B+树叶子节点间有双向链表，方便范围查询，B树没有；

红黑树：存放数据有限，只有二个分支，每个节点只存放一个数据；B+树高度更低，IO次数更少，检索速度更快；

Hash：只适合等值查询。

3. 索引类型

数据结构：B+索引、Hash索引、全文索引

物理结构：聚簇索引（叶子节点保存了所有的记录）、非聚簇索引（叶子节点只保存主键，不保存完整记录）

字段特性：唯一索引、主键索引、普通索引、前缀索引

字段个数：联合索引、单列索引

4. 覆盖索引

查询字段与联合索引一致，可以直接在非聚簇索引树的叶子结点上找到，无需回表（回表：拿着非聚簇索引树上找到的主键去聚簇索引树上找记录）。

如果一个索引包含（或者说覆盖）所有需要查询的字段的值。

5. 索引下推

联合索引，对联合索引包含的字段先做判断，直接过滤掉不满足的记录，减少回表次数。

6. 索引失效

函数计算、类型转换、最左匹配原则、like左匹配、不等于、where子句中的OR

（二）、事务

1、概念

要么不执行、要么全部执行，没有中间状态。

2、四大特性

原子性、隔离性、持久性、一致性（目的）

3、问题

脏读、不可重复读、幻读

4、隔离级别

读未提交、读已提交、可重复读（默认）、串行化

（三）、锁+MVCC（多版本并发控制）

1、锁

表锁：意向锁、自增锁（保持主键唯一自增）、元数据锁（当对一个表CRUD时，自动加读锁；表结构变更时，自动加写锁；读锁之间不互斥，写写、读写均互斥）

行锁：记录锁、间隙锁、临键锁、插入意向锁

页锁

乐观锁

显式锁、隐式锁

全局锁

死锁

2、MVCC

解决了脏读且不加锁：在读取时生成版本号，等到其他事务commit之后，才会读取最新已commit的版本号数据

RR+MVCC 解决了幻读

快照读🆚当前读

    •    当前读：每次读取的都是当前最新的数据，但是读的时候不允许写，写的时候也不允许读。select....for update语句。

    •    快照读：读写不冲突，每次读取的是快照数据。读已提交下，快照读和当前读读取的数据是一样的，都是最新的。RR下有可能读取的不是最新的数据。普通的select语句。

针对快照读：因为RR下只生成一个read view，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入数据，是查询不出这条数据的。

针对当前读：是通过临键锁解决了幻读。因为当执行select....for update（插入X锁），会加上临键锁，如果有其他事务在临键锁范围内插入一条记录，那么这个插入语句会阻塞，无法成功插入，避免了幻读。

 

（四）、日志

redo log innodb存储引擎独有的，当mysql宕机时，读取redo log来故障恢复。

undo log 用于事务回滚操作，当事务执行时发生错误或者需要回滚操作时，执行undo log，恢复到事务开始前的状态。

binary log 数据备份，用于主备、主主、主从。

（五）、Explain工具

1、type

访问类型 由高到低排序 一般至少是range

system：表中只有一条数据且统计数据是精确的

const：表示通过索引一次就找到了。主键或唯一二级索引列，与常数进行等值匹配，对单表的访问。

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

ref：非唯一性索引扫描，返回匹配某个单独值的所有行。普通的二级索引列与常量进行等值匹配。

range：范围区间的记录，in，key>'a' and key<'b'，between

index：出现index是sql使用了索引但是没有通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。

all：全表扫描

2、key_len

主要针对联合索引，实际用到的索引长度

3、rows

预估要读取的记录数，越小越好

4、Extra

索引覆盖 using index；条件下推 using index condition；使用文件排序 using filesort