MySQL
2023-06-09 14:27:29 2 举报AI智能生成
MySQL 知识点总结
作者其他创作
大纲/内容
性能优化
分析工具/方法
Explain、Profile 分析
const
eq_ref
ref
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
all
查询阻塞
等待 MDL 锁
等待刷写
等待行锁
慢查询（日志）
基础配置
redo log 执行
索引设计问题
SQL 低效
MySQL 选错索引
短连接风暴
主动踢出空闲连接
减少连接消耗
QPS 激增
业务白名单
删除用户
重写 SQL
SQL 优化
减少请求数据量
必要的列
必要的行
优化分页
缓存数据
切分大查询
减少扫描行数
条件字段函数操作
隐式类型转换
隐式字符编码转换
优化聚合
分解关联
统计行数
估算行数
分组查询
优化关联
关联索引
使用临时表
哈希关联
优化删除
批量删除
优化表空间
索引优化
查询条件
独立的列
多列索引顺序
范围查询
排序优化
设计维护
主键索引
自增 ID
业务字段
Snowflake
嵌入应用
独立部署
依赖于系统时间戳
库表分配不均匀
联合索引
覆盖索引
前缀索引
正确清理
避免失效
OR 语句
左端模糊匹配
函数和表达式
类型不一致
IS NOT NULL
选错索引
扫描行数
临时表
排序方式
锁与事务优化
避免死锁
等待超时
死锁检测
逻辑拆分
排他锁与共享锁
自增锁优化
批量申请 id 策略
事务优化
避免长事务
手动提交事务
commit work and chain
库表优化
分库分表
表 - 水平切分
库/表 - 垂直切分
库 - 读写分离
切分中间件
基本功能
SQL 转发
分片库
读写分离
监控告警
限流降级
代理和 MySQL 高可用
扩展功能
配置热加载
SQL 流转
SQL 指纹统计
黑白名单
主要产品
Cobar
TDDL
Atlas
Sharding-JDBC
Mycat
使用须知
分区表
间隙锁
分区策略
应用场景
缓存表
数据类型
整型
浮点型
字符串
时间日期
位
反模式（Anti-Pattern）表
树结构（Naive Tree）
邻接表（Adjacency List）
递归查询（Recursive Query）
枚举路径（Path Enumeration）
嵌套集（Nested Sets）
闭包表（Closure Table）
反范式
冗余存储
用时更新
反缓存
配置优化
磁盘 I/O
InnoDB 缓冲区：innodb_buffer_pool_size
脏页比例：innodb_max_dirty_pages_pct
redolog
InnoDB redo log 大小：innodb_log_file_size
刷写 redo log：innodb_flush_log_at_trx_commit
日志刷写方式：innodb_flush_method
redo log 缓冲区大小：innodb_log_buffer_size
binlog
启用 binlog：log_bin
binlog 文件清理：expire_logs_days
调用 fsync 前累计写入次数：binlog_group_commit_sync_no_delay_count
调用 fsync 前累计写入时间：binlog_group_commit_sync_delay
刷写 binlog：sync_binlog
binlog 缓冲内存：binlog_cache_size
磁盘空间
表与索引存储方式：innodb_file_per_table
网络连接
最大连接数：max_connections
跳过主机名解析：skip_name_resolve
主从相关
服务端 id：server_id
查询性能
查询缓存：query_cache_size
双 1 配置
死锁检测
索引
功能需求
格式化数据或非格式化数据
静态数据或动态数据
内存或硬盘存储
单值或区间查找
单关键词或多关键词组合查找
索引类型
B+ Tree 索引
哈希索引
R-Tree 索引
全文索引
具体实现
InnoDB
数据结构
查找次数更少、效率更稳定
支持范围查询
索引操作
查询索引
维护索引
索引类型
普通索引
唯一索引
匹配方式
覆盖索引
联合索引
最左前缀
索引下推
适用场景
全值匹配
匹配左边的列
匹配范围值
精确匹配 + 范围匹配
排序、分组
MyISAM
独立的数据文件
独立的索引文件
回表效率
锁与事务
锁
粒度
全局锁
flush tables with read lock
set global readonly=true
表级锁
表锁
元数据锁（MDL)
表结构变更
避免长事务
超时重试
行级锁
两段锁协议
三级封锁协议
一级协议：避免丢失修改
二级协议：避免读脏数据
三级协议：避免不可重复读
间隙锁
Record Locks
Gap Locks
Next-Key Locks
类型
读写锁（X-S Locks）
意向锁（Intention Locks）
自增锁（Auto-Inc Locks）
保存策略
修改机制
修改时机
死锁与其检测
发生条件
互斥
请求且保持
不可抢占
环路等待
避免死锁
等待超时
死锁检测
逻辑拆分
顺序访问
应用实例
事务
基本特性（ACID）
原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）
隔离级别
读未提交（read uncommitted）
丢失修改（lost update）
读脏数据（dirty read）
读提交（read committed）
不可重复读（non-repeatable read）
可重复读（repeatable read，默认）
幻读（phantom read）
旧解
可串行化（serializable）
并发控制
多版本并发控制（MVCC）
基本操作
当前读
快照读
Next-Key Locks
Record Locks
Gap Locks
Next-Key Locks
基础知识
基本概念
* E-R 图
实体的三种联系
出现多次的关系
联系的多向性
父类与子类
函数依赖
更新异常
冗余数据
修改异常
删除异常
插入异常
三大范式
第一范式
第二范式
第三范式
SQL
数据定义语言（DDL）
创建：CREATE
删除：DROP
修改：ALTER
清空：TRUNCATE
数据操纵语言（DML）
基本操作
选择：SELECT
去重：DISTINCT
数据源：FROM
条件：WHERE
且：AND
非：NOT
重命名：AS
排序：ORDER BY
倒序：DESC
正序：ASC
集合操作
差集：UNION、UNION ALL
交集：INTERSECT、INTERSECT ALL
差集：EXCEPT、EXCEPT ALL
聚集操作
分组：GROUP BY
条件：HAVING、WHERE
平均值：AVG
最小值：MIN
最大值：MAX
总和：SUM
计数：COUNT
去重：DISTINCT
嵌套子查询
包含：IN、NOT IN
比较：SOME、ALL
全称：ALL
特称：SOME
存在：EXIST
重复：UNIQUE、NOT UNIQUE
死循环插入
连接操作
内连接：INNER JOIN
左连接：LEFT JOIN
右连接：RIGHT JOIN
全连接：FULL OUTER JOIN
外连接：OUTER JOIN
数据控制语言（DCL）
授权：GRANT
拒绝：DENY
取消授权：REVOKE
其他特性
字符编码
表达式
存储过程
函数
窗口函数
......
整体架构
Server 层
连接器
* 查询缓存
分析器
优化器
* 优化类型
重写关联表顺序
外连接改内连接
表达式等价替换
优化聚集操作
预估转换常量表达式
覆盖索引扫描
子查询优化
提前终止优化
等值传播
执行计划
关联查询
排序
索引误判
执行器
调用存储引擎查询数据行
处理数据行
调用存储引擎后处理
日志模块：binlog
写入机制
日志格式
备份恢复
存储引擎层
基本组成
日志模块：redo log
修改缓存：change buffer
执行流程
更新操作
读取操作
merge 操作
使用条件
基本特点
类型
InnoDB
行结构
COMPACT
DYNAMIC
COMPRESSED
REDUNDANT
页结构
B+ 索引
MyISAM
TokuDB
Memory
对比 InnoDB
范围扫描
锁粒度
数据持久性
基本原理
查询操作
基本流程
1. 建立连接
* 查询缓存
2. SQL 解析
3. SQL 优化
4. 执行查询
语句执行顺序
定位数据
临时表
内存临时表
磁盘临时表
排序操作
全字段排序
外部排序
rowid 排序
单表操作
访问方法（access method）
const
ref
range
index
all
ref_or_null
二级索引 + 回表
范围区间
索引合并
交集（Intersection）
并集（Union）
Sort-Union
关联操作
驱动表与被驱动表
Index Nested-Loop Join
* Simple Nested-Loop Join
Block Nested-Loop Join
Batched Key Access
Multi-Range Read
临时表优化
Hash Join
联合操作
执行流程
分组操作
执行流程
generated column
直接排序
全表扫描
Server
InnoDB
增改操作
基本流程
Server 层
1. 请求获取数据行
3. 请求执行数据行修改
5. 记录操作写入 binlog，提交事务
存储引擎
2. 从内存/磁盘返回数据行（change buffer 更新）
4. 将行更新到内存/ change buffer，写入 redo log（prepare）
6. 确认事务提交，修改 redo log（commit）
刷写操作
binlog 写入
redo log 写入
触发条件
redo log 写满
系统内存不足
缓冲池
系统空闲时
正常关闭时
脏页控制策略
磁盘 I/O
InnoDB 刷盘速度
相邻脏页刷新
删除操作
空间复用
行复用
页复用
表重建
online DDL
重建异常
空间管理
数据文件
数据目录
InnoDB
系统表空间
独立表空间
其他表空间
MyISAM
其他文件
文件系统
系统数据库
错误恢复
日志管理
Sever 层：binlog
写入机制
日志格式
备份恢复
存储引擎层：redo log
程序崩溃
双 1 配置
组提交
延迟写入
数据误删
误删行
误删库表
mysqlbinlog
加速恢复
延迟复制备库
误删实例
部署架构
主备架构
基本流程
半同步复制
binlog 格式
数据恢复
互为主备
同步延迟
备库性能较差
备库压力太大
大事务同步
并行复制能力不足
MariaDB
切换策略
可靠性优先
可用性优先
主主架构
双写架构
实时同步
复制状态机
数据双写
对比补偿
灰度切流
0 条评论
下一页
