MySQL知识点汇总

1. 基础

2. 数据库架构

3. 存储引擎

4. 索引

4.1 索引分类

按数据结构分类

存储引擎支持情况

索引类型	InnoDB引擎	MyISAM引擎	Memory引擎
B+Tree	支持	支持	支持
Hash	支持，但索引创建由引擎自动优化创建，不能人为指定	不支持	支持
Full-Text	支持（MySQL 5.6版本后）	支持	不支持

索引比较

Hash索引
- 优点：无哈希冲突时，等值查询速度极快
- 缺点：
  - 哈希索引无序，无法范围查询、无法模糊查询、无法排序、无法分组
  - 不支持联合索引（将索引键合并再哈希，无法对每个索引单独哈希）
  - 大量哈希冲突时，效率极低
Full-Text索引
- 优点：适用于对大量数据的模糊匹配，主要应用于搜索引擎
- 缺点
  - 建立索引的过程时间和空间消耗都很大
  - 存在精度问题
- 原理：对文档建立倒排索引
  - 分词：将文档分为单独的单词，去除标点和停词
  - 格式化：将词元转为小写、缩减或变化为词根形式
  - 建立索引：将词元按字典序建立字典，key为词元，value为出现的文档编号及出现频率
  - 匹配：根据词元在本文档中出现的频次（越高说明越重要）、在所有文档中出现的频次（越高说明越普通），计算词元权重，得到所有文档列表权重向量、用户查询关键词列表权重向量，比较两者夹角（越小说明越相关）
B+Tree索引
- 优点：
  - 单次查询涉及的磁盘IO操作较少
  - 查询效率稳定（每次都到达叶子节点）
  - 支持高效范围查询（从符合条件的某个叶子节点开始遍历即可）
- 缺点：
  - 产生大量随机IO
  - 如果主键不是有序递增，导致每次插入数据产生大量数据迁移和空间碎片
  - 如果主键有序递增，大量写请求的分布仍是随机的

按物理存储分类

聚簇索引（主键索引）

叶子节点存放的是实际数据，包含所有完整的用户记录

辅助索引（二级索引）

叶子节点存放的是主键值，而不是实际数据

按字段特性分类

主键索引：PRIMARY KEY
- 建立在主键字段上的索引
- 一张表最多只有一个主键索引
- 索引列的值不允许有空值
- 在创建表的时候一起创建
唯一索引：UNIQUE KEY
- 建立在 UNIQUE 字段上的索引
- 一张表可以有多个唯一索引
- 索引列的值必须唯一，但是允许有空值
- 可以在创建表时创建，也可以后续增加
普通索引
- 建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE
前缀索引
- 对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引
- 可以建立在字段类型为 char、 varchar、binary、varbinary 的列上
- 目的：为了减少索引占用的存储空间，提升查询效率

按字段个数分类

单列索引
联合索引

4.2 索引失效

对索引使用左或者左右模糊查询
对索引使用函数
对索引进行表达式计算
对索引隐式类型转换（MySQL自动将字符串转为数字）
联合索引非最左匹配
WHERE子句中OR的字段无索引

5. 事务

事务特性

原子性Atomicity
- 一个事务中的所有操作，要么全部完成，要么全都不完成
- 实现：通过undo log（回滚日志）保证
隔离性Isolation
- 多个事务同时使用相同的数据时，不会相互干扰
- 实现：通过MVCC（多版本并发控制）或者锁机制保证
持久性Durability
- 事务处理后，对数据的修改是永久的，系统故障也不会丢失
- 实现：通过redo log（重做日志）保证
一致性Consistency
- 事务操作前后，数据满足完整性约束，数据库保持一致性
- 实现：通过原子性、隔离性、持久性保证

事务隔离级别

读未提交 Read Uncommitted
- 一个事务还未提交时，它的操作就能被其他事务看到
- 可能：脏读、不可重复读、幻读
- 实现：读取最新数据
读提交 Read Committed
- 一个事务提交之后，它的操作才能被其他事务看到
- 可能：不可重复读、幻读
- 实现：在每个语句执行前，重新生成ReadView
可重复读 Repeatable Read
- InnoDB默认的隔离级别
- 一个事务执行过程中，看到的数据，一直跟这个事务开始时看到的数据是一致的
- 可能：幻读
- 实现：事务开始时，生成一个Read View。通过next-key lock锁（行锁和间隙锁的结合），锁住记录和记录之间的间隙，防止其他事务修改。避免了幻读
串行化 Serializable
- 事务冲突时，后开始的事务必须等先开始的事务执行完成，才能继续执行
- 实现：加读写锁

MVCC

Read View字段

creator_trx_id：创建该ReadView的事务id
m_ids：创建ReadView时，当前数据库中活跃且未提交的事务id列表

活跃事务：开始了但未提交的事务

min_trx_id：创建ReadView时，当前数据库中活跃且未提交的最小事务id（ids最小值）
max_trx_id：创建ReadView时，当前数据库中活跃且未提交的最大事务id（不是ids最大值，而是全局事务id最大值加1，即当前数据库应该给下一个事务的id值）

隐藏列

trx_id：改动记录时，将该事务id记录在此列
roll_pointer：指向undo log中旧版本记录

控制过程

如果记录的trx_id小于ReadView中的min_trx_id，说明该版本记录早于ReadView创建，因此对事务可见
如果记录的trx_id大于ReadView中的min_trx_id，说明该版本记录晚于ReadView创建，因此对事务不可见
如果在min和max之间，判断trx_id是否在m_ids中
- 在：代表操作此记录的事务还活跃，因此不可见
- 不在：代表操作此记录的事务已结束，因此可见

并行事务问题

脏读：一个事务读到了另一个未提交事务修改过的数据
不可重复读：在一个事务中多次读取同一个数据，前后两次读到的数据不一样
幻读：在一个事务中，多次查询符合某条件的记录，前后两次读到的记录数量不一样

并发问题严重性：脏读>不可重复读>幻读

6. 锁

7.日志

7.1 类别

通用日志文件

日志名	记录内容
错误日志 error log	记录MySQL启动、运行、关闭过程
慢查询日志 slow query log	记录执行时间超过long_query_time的查询语句
一般查询日志 general log	记录所有对MySQL数据库请求的信息，无论请求是否完成
二进制日志 bin log	记录所有执行的DML和DDL语句（除了select和show），以事件形式记录并保存在二进制文件中

InnoDB特有日志文件

日志名	记录内容
重做日志 redo log	记录事务日志
回滚日志 undo log	记录事务执行前的数据信息，回滚时作相反操作

7.2 bin log与redo log

区别

区别	bin log	redo log
记录范围不同	记录所有存储引擎的日志	只记录InnoDB的日志
记录内容不同	记录事务的具体操作内容，逻辑日志	记录每个页的更改的物理情况
记录时间不同	事务提交前，只写入磁盘一次	事务进行中，不断写入
记录方式不同	追加写入，不会覆盖	循环写入，擦除

redo log 刷盘

写入方式：不直接落到磁盘，设置redo日志缓冲区：redo log buffer连续内存空间，
写入时机
- log buffer内存不足时
- 事务提交时
- 后台线程自动
- 正常关闭服务器时
- 触发checkpoint机制
  
  循环链表，write pos代表当前记录位置，checkpoint代表擦除位置，都不断后移，当两者相遇时，代表已经写满，需要刷盘