MySQL思维导图
2020-12-28 22:04:41 44 举报
AI智能生成
登录查看完整内容
为你推荐
查看更多
MySQL思维导图
作者其他创作
大纲/内容
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 级别的缓存
存储引擎层
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 表名
通过 Profile 定位 QUERY 代价消耗
set profiling=1;执行 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';开启此项影响性能,记得用后关闭
数据库服务器硬件优化
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 条评论
回复 删除
下一页