[TOC]
MySQL知识点整理
引擎
常用
InnoDB，行锁，支持事务、回滚，主流引擎；MyISAM老版本5.5-的默认引擎，表锁，不支持事务、回滚，适合大量读取极少数写入场景
了解
Archive:行锁，只支持INSERT/SELECT操作，不支持索引，压缩率高，用于存储冷数据如日志、历史订单等，小众方案。业务场景日志一般用Elasticsearch
和ES比较：MEMORY:表锁，基于内存，用于临时表、会话缓存NDB:行锁，基于内存，支持分布式高可用，数据自动分片，适合大规模集群，小众方案。一般业务场景实现用**MyCat、ShardingSphere +ProxySQL**较多
索引
索引的类型
- 数据结构分类
- InnoDB存储方式
- 约束类型、场景
- 主键索引：唯一、非空，如id
- 唯一 索引：唯一，允许为空
- 普通索引：无唯一约束，用于加速查询
- 联合索引：多列组合形成索引，**最左前缀原则**
- 全文索引：全文搜索用
- 空间索引：GIS数据
数据结构
树
B 树
节点包含数据+子节点指针
B+树
B树的优化版本，非叶子结点只存储主键索引不存储数据，数据全在叶子结点，叶子结点通过双向链表相连，为范围查询提供支撑，MySQL的B+树非叶子结点也有双向链表这是MySQL实现的优化
B+树数据存储量的计算
InnoDB默认页大小为16KB（16384B），固定开销154B，默认预留空间约1230B，真实可用的有效空间以15KB计算。
页物理结构
| 部分名称 | 固定占用空间 | 核心作用 | 可变 |
|---|---|---|---|
| File Header（文件头） | 38B | 标识页的类型、归属表空间、水平指针等，是页的「身份证」 | ❌ |
| Page Header（页头） | 56B | 记录页的内部状态（如页中记录数、B + 树层级、空闲空间位置等） | ❌ |
| Infimum + Supremum | 26B | 页内的「哨兵记录」，标记记录的最小 / 最大值，避免边界判断 | ❌ |
| User Records（用户记录） | 存储真正的业务数据（聚簇索引的行数据、二级索引的 关键字+主键），垂直指针 |
✅ | |
| Free Space（空闲空间） | 页内未使用的空间，用于新增记录，空闲空间耗尽会触发页分裂 | ✅ | |
| Page Directory（页目录） | >26B | 存储用户记录的「槽位指针」，加速页内记录查找 | ✅ |
| File Trailer（文件尾） | 8B | 校验页的完整性（防止页损坏） | ❌ |
不同数据类型主键的占用
| 主键类型 | 占用字节 |
|---|---|
| TINYINT | 1 |
| SMALLINT | 2 |
| MEDIUMINT | 4 |
| BIGINT | 8 |
| CHAR(n) utf8 | n*3(utf8),n*4(utf8m64) |
| VARCHAR(n) | 实际长度+1~2 |
| DATE | 3 |
| DATETIME | 8 |
假设的存储表
CREATE TABLE tablename (
id INT PRIMARY KEY, -- 4 字节
name VARCHAR(10), -- 最多 30 字节（10×3）
age TINYINT, -- 1 字节
create_time DATETIME -- 8 字节
);单条数据43字节，每条记录需要固定开销27字节（事务字段6+回滚指针7+变长字段2+NULL标识位1+记录头11）合计单条数据 占用70字节
计算
- 叶子结点可存储数据量：15KB/70B = 214条数据
- 非叶子结点存储量，取阶数m = 15KB / （主键大小BIGINT8B + 垂直指针8B）= 937
- 三层B+树可存储数据量约为 937^2*214 = 1.8亿的数据量
B*树
B+树的变形,强化了节点的空间利用率,减少分裂次数
- 继承B+树的特性,飞叶子节点只存索引,叶子结点存数据+双向链表
- 增加兄弟节点指针,非叶子结点也用链表相连,方便节点间数据转移
- B+树节点满了会直接分裂,B树节点满了会优先向*兄弟节点借空间,借不到再分裂
- 非叶子结点的最小结点树从m/2提升到2m/3,提高空间利用率
R树
区域树
日志
binlog
redolog
undolog
分析、优化
MySQL性能优化步骤
性能监控、诊断
SHOW PROCESSLIST/SHOW FULL PROCESSLISTPerformance Schemasys schema（推荐使用）slow query log（慢查询日志）EXPLAIN/EXPLAIN FORMAT=JSON- 监控工具：
pt-query-digest,Percona Toolkit,Prometheus + Grafana,Zabbix
-- 查看当前运行的线程 SHOW PROCESSLIST; -- 开启慢查询日志（my.cnf 中配置） SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 超过1秒记录 SET GLOBAL log_output = 'TABLE'; -- 或 'FILE' -- 查看慢查询日志内容（如果输出到表） SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;分析慢SQL
EXPLAIN SELECT ... EXPLAIN FORMAT=JSON SELECT ...type: 访问类型（ALL 全表扫描最差，index、range、ref、eq_ref、const 更好）key: 是否命中索引rows: 扫描行数（越少越好）Extra: 是否有Using filesort,Using temporary等危险操作
表索引策略优化、SQL 语句优化
- 表索引优化，为条件字段建立合理的索引，删减无用索引
- 避免SELECT * 查询，检查条件索引生效情况
调整MySQL配置参数
参数 建议值（示例） 说明 innodb_buffer_pool_size物理内存的 70%~80% 缓存数据和索引，最重要 innodb_log_file_size1G ~ 2G 提高写性能，减少 checkpoint max_connections500~2000 根据并发连接数调整 tmp_table_size/max_heap_table_size64M~256M 内存临时表大小 sort_buffer_size2M~4M 每连接排序缓存，不宜过大 innodb_flush_log_at_trx_commit1（安全），2（折中），0（快但不安全） 控制事务持久性 query_cache_type0（关闭） MySQL 8.0 已移除，5.7 可关闭 表结构、存储引擎优化
读写分离、分库分表、前置
redis、消息队列、本地缓存等
事务的异常情况分析//TODO
三方组件、工具
ElasticSearch
MyCat
ShardingSphere
ProxySQL
常见问题、术语定义
术语定义
B+树的术语定义
节点存储的内容
关键字（key）：用于排序、查找的和核心值，是索引的标识
主键索引：特殊关键字，对应主键字段如ID，是聚簇索引的核心关键字
指针：内存/磁盘地址的引用，B+树分为子节点指针、数据指针
回表
回表指的是二级索引需要通过主键索引二次查询获取其他字段，因为B+树非叶子结点只存储主键和指针
联合主键、单主键
一个主键多个字段 = 联合主键；
单主键 = 单字段
阶数m
阶数 -1 <= 可用空间 /索引关键字大小
叶子结点可存储数据量 = （叶大小-固定开销）/单条数据大小
最左前缀原则
查询索必须从左到右匹配，第一个条件非索引字段会直接扫全表
[索引失效有哪些情况](#5. 索引失效的情形有哪些？)
事务 //TODO
事务是数据库操作的一个逻辑单元，由一组SQL语句组成，要么全部执行，要么全部回滚，保证数据的一致性和完整性
事务的特性ACID
- Atomicity：原子性，不可分割，要么全做要么全部做
- Consistency：一致性，事务执行前后数据库从一个一致状态转移到另一个一致状态
- Isolation：隔离性，多事务并发执行，彼此互不干扰
- Durability：持久性，一旦事务提交，对数据库的修改是永久性的，即便系统崩溃也不会丢失
事务的隔离级别
| 名称 | 优点 | 问题 |
|---|---|---|
| 读未提交 | 极致的性能优先 | 可靠性极差 |
| 读已提交 | 不可重复读、幻读 | |
| 可重复读 | 幻读、间隙锁容易引发死锁增加运维成本 | |
| 串行读 | 串行解决所有并发问题 | 无并发能力 |
MVCC是什么
锁 //TODO
常见问题
1. MySQL为什么选择B+树作为索引结构，没有选择B*树？
B树因为节点存储数据，导致节点的存储效率低（单节点存储的关键字数量远小于只存关键字），树高会更高，磁盘IO会更多；并且B树的范围查询性能极差，节点相互独立，范围查询需要多次回溯、遍历不同分支，效率低下。B树更适合内存数据库，在内存中IO开销几乎可以忽略
B+树是多叉树，树矮检索3层B+树就可以存储近2000w数据（如何计算一棵树能存储多少数据）；
非叶子结点只存储key和指针，节省空间（点查询劣于B树，范围查询优于B树）
叶子结点、非叶子结点双向链表串联，方便范围查询、寻址
B*树的出现是为了提高节点的空间利用率，[节点填充率](#3. B+树是节点满了之后会分裂，所谓的最小填充率1/2是什么意思？)2/3，并不是节点填充率越高越好；在节点满的时候不会直接分裂而是会先遍历兄弟节点、判断、移动数据，而这个操作的时间成本远大于B+树节点分裂的时间，在时间和空间的倾向上，B*树更倾向于空间，而B+树则在空间和时间的选择上更均衡。
2. 如何计算一棵树能存储多少数据
根据不同索引不同的计算方式
聚簇索引：单条数据*阶层m^(树高n-1)
非聚簇索引：(二级索引+主键索引)*阶层m^(树高n-1)
参考B+树
3. B+树是节点满了之后会分裂，所谓的最小填充率1/2是什么意思？
填充率是一个节点的存储红线，它存在的目的是为了防止节点过于稀疏，避免树高膨胀。如果小于这个存储阈值，就会触发节点合并。
节点填充率越高，空间利用率越高，节点的合并、数据迁移 触发概率更高；
填充率越低，节点越松散，合并、迁移概率越低
4. 联合主键和多主键的区别
在关系型数据库中多主键是不存在的，联合主键的含义是一个主键
5. 索引失效的情形有哪些？
函数包裹索引字段会使索引失效，隐式类型转换其实进行了
CAST函数强制转换使用!= 或 <> 、NOT IN 或者 NOT EXISTS、IS NOT NULL等取反操作通常无法有效利用索引
B+树是
有序的单向区间索引，优化器是成本优先原则，而!=是筛选出除了某个值之外的所有数据，B+树天生不擅长查反向、离散的全集假设强制走索引，取非值，首先要在索引查询<x;再索引查询>x;再合并去重；最后拿着所有id批量回表走聚簇索引。这个成本是2次完整的B+树索引+内存合并计算+回表聚簇索引，成本远高于1次磁盘舒徐IO
OR 连接任意两个字段都会使索引 失效
两个索引用
or相连，如果要走索引他会先走A索引的B+树，拿到主键id；再走B索引的B+树拿到主键id；两次查询的主键id去重合并；再回表走聚簇索引查业务数据。这个过程包含2次B+树查询+内存去重+批量回表，效率远低于全表扫描，MySQL的优化器会判定全表扫描更划算，放弃两个单列索引
只有在两个字段是联合索引的情况下OR才会索引生效
模糊匹配
%开头违背最左前缀原则，索引失效数据量过小或索引选择性差
索引字段参与运算
SQL查询不规范 如SELECT * 会强制回表，当回表代价过高时索引效率降低
