MySQL思维导图
2020-12-28 22:04:41 44 举报AI智能生成
MySQL思维导图
作者其他创作
大纲/内容
体系架构
Client Connectors层
处理客户端的连接请求
MySQL Server层
Connection Pool
负责处理和存储数据库与客户端创建的连接
Service & utilities
管理服务&工具集，包括备份恢复、安全管理、集群管理服务和工具
SQL interface
负责接收客户端发送的各种 SQL 语句
Parser解析器
对 SQL 语句进行语法解析生成解析树
Optimizer 查询优化器
查询优化器会根据解析树生成执行计划，并选择合适的索引，然后按照执行计划执行 SQL 语言并与各个存储引擎交互
Caches 缓存
包括各个存储引擎的缓存部分
InnoDB 存储的 Buffer Pool、MyISAM 存储引擎的 key buffer 等，Caches 中也会缓存一些权限，也包括一些 Session 级别的缓存
InnoDB 存储的 Buffer Pool、MyISAM 存储引擎的 key buffer 等，Caches 中也会缓存一些权限，也包括一些 Session 级别的缓存
存储引擎层
MyISAM
InnoDB
内存类型的 Memory
归档类型的 Archive
列式存储的 Infobright
是文件的物理存储层，包括二进制日志、数据文件、错误日志、慢查询日志、全日志、redo/undo 日志
存储引擎
支持事务的 InnoDB（5.6 版本以后默认）
MVCC、锁、锁算法和分类、事务、表空间和数据页、内存线程以及状态查询
Write Ahead Logging
先写日志后写磁盘，日志成功写入后事务就不会丢失，后续由 checkpoint 机制来保证磁盘物理文件与 Redo 日志达到一致性
利用 Redo 记录变更后的数据，即 Redo 记录事务数据变更后的值
利用 Undo 记录变更前的数据
SQL语句解析执行过程
事务与锁
ACID：原子性、一致性、隔离性和持久性
一致性：事务开始之前和事务结束之后，数据库的完整性限制未被破坏
原子性：事务的所有操作，要么全部完成，要么全部不完成，不会结束在某个中间环节
持久性：事务完成之后，事务所做的修改进行持久化保存，不会丢失
隔离性：当多个事务并发访问数据库中的同一数据时，所表现出来的相互关系
读未提交（RU，Read Uncommitted）
读提交（RC，Read Committed）
可重复读（RR，Repeatable Read）
可串行化
并发事务控制
锁分类
表级锁
行级锁
页级锁
InnoDB 中的锁
共享锁（S）
排他锁（X
意向共享锁（IS）
意向排他锁（IX）
自增锁（AUTO-INC Locks）
InnoDB 行锁
Record Lock 锁：单个行记录的锁（锁数据，不锁 Gap）
Gap Lock 锁：间隙锁，锁定一个范围，不包括记录本身（不锁数据，仅仅锁数据前面的Gap）
Next-key Lock 锁：同时锁住数据，并且锁住数据前面的 Gap
InnoDB 死锁
互斥条件
请求与保持条件
不剥夺条件
循环等待条件
数据库表设计
范式
第一范式
第二范式
第三范式
反范式设计
业务场景
相应时间
字段冗余
基本设置规则
必须指定默认存储引擎为 InnoDB，并且禁用 MyISAM 存储引擎
默认字符集 UTF8mb4
关闭区分大小写功能。设置 lower_case_tables_name=1
规范命名
索引设计
索引原理
Hash 索引
B+Tree 索引
聚簇索引
辅助索引
索引类型
哈希索引（Memory/InnoDB adaptive Hash index/NDB）
B+Tree 索引（MyISAM/InnoDB）
全文索引（MyISAM/InnoDB）
空间索引（MyISAM R-Tree）
分形树索引（TokuDB Fractal Tree Index）
提高查询性能
MySQL 查询优化器
SELECT 执行过程
ICP
ICP 是 Index Condition Pushdown 的简称，是 MySQL 使用索引从表中检索行数据的一种优化方式。目的是减少从基表中全记录读取操作的数量，从而降低 IO 操作
MRR
MRR 是 Multi-Range Read 的简称，是 MySQL 优化器将随机 IO转化为顺序 IO 以降低查询过程中 IO 开销的一种手段
BKA 和 BNL
BKA 是 Batched Key Access 的简称，是 MySQL 优化器提高表 join 性能的一种手段，它是一种算法。而 BNL 是 Block Nested Loop 的简称，它是默认的处理表 join 的方式和算法
MySQL 执行计划分析
查看 SQL 执行计划
explain SQL；
desc 表名；
show create table 表名
desc 表名；
show create table 表名
通过 Profile 定位 QUERY 代价消耗
set profiling=1；
执行 SQL；
show profiles; 获取 Query_ID。
show profile for query Query_ID; 查看详细的 profile 信息
执行 SQL；
show profiles; 获取 Query_ID。
show profile for query Query_ID; 查看详细的 profile 信息
通过 Optimizer Trace 表查看 SQL 执行计划树
set session optimizer_trace='enabled=on'；
执行 SQL；
查询 information_schema.optimizer_trace 表，获取 SQL 查询计划树；
set session optimizer_trace=‘enabled=off'；开启此项影响性能，记得用后关闭
执行 SQL；
查询 information_schema.optimizer_trace 表，获取 SQL 查询计划树；
set session optimizer_trace=‘enabled=off'；开启此项影响性能，记得用后关闭
数据库服务器硬件优化
CPU
系统配置选择 Performance Per Watt Optimized（DAPC）
CPU优先选择高主频以提高运算能力；其次选择核数多，可以多线程并发处理和多实例部署
关闭 C1E（增强型空闲电源管理状态转换）和 C states，DB 服务器不需要节能和省电运行，默认是开启状态，DB 服务器建议关闭以提高 CPU 效率
数据库服务器选择高主频多核数 CPU 类型，同时开启最大性能和关闭 CPU CIE 和 C States。 高频加速 SQL 执行，多核解决并发
内存
优先选择大内存，同时开启最大性能并关闭NUMA
参数优化
Redo Log
innodb_flush_log_at_trx_commit
Replication
主库 Master 将数据库的变更操作记录在二进制日志 Binary Log 中
备库 Slave 读取主库上的日志并写入到本地中继日志 Relay Log 中
备库读取中继日志 Relay Log 中的 Event 事件在备库上进行重放 Replay
性能优化
连接池优化
架构优化
缓存
集群
主从复制
分库分表
优化器
SQL语句分析优化
慢查询日志
show processlist
show status
explain
type类型
system>const>eq_ref>ref>range>index>all
SQL与索引优化
存储引擎
0 条评论
下一页
