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与索引优化