高性能 MySQL

[zhangwt-cn]

No description provided.

[zhangwt-cn]

第一章 MySQL 架构和历史

锁 MySQL InnoDB 官方文档

          graph TB
          lock-type[锁类型] --> 加锁机制[加锁机制]
          lock-type[锁类型] --> 锁颗粒度[锁颗粒度]
          lock-type[锁类型] --> 兼容性[兼容性]
          lock-type[锁类型] --> 加锁模式[加锁模式]
          加锁模式[加锁模式] --> Intention-Locks[意向锁]
          加锁模式[加锁模式] --> Record-Locks[行锁]
          加锁模式[加锁模式] --> Gap-Locks[间隙锁]
          加锁模式[加锁模式] --> Next-Key-Locks[Next-Key Locks]
          加锁模式[加锁模式] --> Insert-Intention-Locks[插入意向锁]
          兼容性[兼容性] --> read-lock[读锁/共享锁 S ]
          兼容性[兼容性] --> write-lock[写锁/独占锁 X ]
          锁颗粒度[锁颗粒度] --> 表级锁[表级锁]
          锁颗粒度[锁颗粒度] --> 行级锁[行级锁]
          加锁机制[加锁机制] --> 乐观锁[乐观锁]
          加锁机制[加锁机制] --> 悲观锁[悲观锁]

Loading

间隙锁、行锁、Next-Key Locks

1. 间隙锁(Gap Locks)
   仅在可重复读隔离级别下生效，用来锁定范围内索引，不可插入记录
2. 行锁(Record Locks)
   行锁实际是根据索引记录来进行锁定
3. Next-Key Locks
   Next-Key Locks 是间隙锁和行锁的组合，用来防止出现幻读，锁定范围扩大查询范围下一条记录，当查询字段具有唯一索引或只有唯一行会退化为行锁(Record Locks)

意向锁

意向锁是表级锁，用来标记操作意向，提高性能

1. 意向共享锁(IS)
   在获取共享锁之前，需要先获取意向共享锁
2. 意向独占锁(IX)
   在获取独占锁之前，需要先获取意向独占锁

插入意向锁(Insert Intention Locks)

插入意向锁不是意向锁，而是一种特殊的间隙锁，只需要插入行记录并不冲突，就可以在同一区间范围内插入

锁类型兼容性

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	兼容	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

事务

          graph TB
          事务[事务] --> 原子性[原子性]
          事务[事务] --> 隔离性[隔离性]  
          事务[事务] --> 一致性[一致性]
          事务[事务] --> 持久性[持久性]
          隔离性[隔离性]  --> 未提交读[READ UNCOMMITTED]
          隔离性[隔离性]  --> 提交读[READ COMMITTED]
          隔离性[隔离性]  --> 可重复读[REPEATABLE READ]
          隔离性[隔离性]  --> 串行[SERIALIZABLE]

Loading

一致性非锁定读(Consistent Nonlocking Reads)

一致性非锁定读是按照版本号读取数据，无需加锁就能读取数据，在不同的隔离级别有不同的行为。

1. 在 READ COMMITTED 隔离级下读取的最新版本数据
2. 在 REPEATABLE READ 隔离级别下读取的快照版本的数据，以快照读的方式提升普通 Select 查询场景并发查询性能，也能解决部份情况下的幻读，但如果一个事务中出现锁定读(读取最新数据)，那么 Consistent Nonlocking Reads 就会读取最新版本数据，可能出现幻读。

死锁

在 MySQL InnoDB 中检测到事务等待列表超过200个也会被视为死锁，一旦检测到发生死锁，InnoDB 会回滚最小事务(由插入、更新或删除的行数决定)

1. 产生条件
   互斥条件（Mutual exclusion）
   请求与保持条件（Hold and wait）
   不可剥夺条件（No pre-emption）
   环路/循环等待条件（Circular wait）
2. 如何有效避免死锁
   15.7.5.3 如何最小化和处理死锁

Buffer Pool

1. Buffer Pool 是什么
   Buffer Pool 是InnoDB引擎内存中一块缓存空间，用于缓存查询数据page，加快查询速度，增加查询并发。
2. 怎么实现的
   Buffer Pool 数据结构使用的是链表，链表分为new 和 old 两个部分，使用基于LRU算法管理，Buffer Pool 只在用户操作（例如SQL 查询，但不只是SQL查询）或者查询时预读数据才会加入数据，数据第一次进入首先插入old链表顶部，在Buffer Pool 中第一次访问之后会移动到new链表顶部

[zhangwt-cn]

第五章 索引

InnoDB索引的结构

1. 索引是什么
   索引是一种快速查找数据的方式，设计的目的主要是为了加快查询速度。
2. 索引实现原理
   InnoDB索引底层是B+树，叶子结点使用链表相关联。
3. 索引为什么这样设计
   使用B+树具有查询速度快，在插入数据和删除数据时索引B+树重构范围小，适合磁盘存储，B+数据存储紧凑，利用磁盘顺序查找速度快的特点。减少磁盘I/O。
4. 这样设计的缺点
   不适合高并发场景、不适合频繁随机插入和删除的场景、内存占用高

常用索引类型

1. 组合索引
   组合索引是多列组合生成的索引

# a 和 b 是组合索引，（a, b）
# 想要使用使用这个组合索引必须要有a，但是也不是有查询a的条件就一定会走索引
select * from tb where a = 10 and b = 20;

EXPLAIN 的使用
MySQL官方文档

索引优化

1. ORDER BY

• 单列索引支持DESC和ASC，在单列索引ORDER BY支持正向扫描也支持反向扫描
• 多列索引混合DESC、ASC，可以使用降序索引来解决文件重排问题