MySQL架构
- 网络连接层
- 服务层
- 连接池
- 系统管理和控制
- SQL接口
- 解析器, 对SQL语句检查，生成解析树
- 查询优化器
- 缓存
- 存储引擎层
- 文件系统
- 系统文件
- 日志文件
- 系统文件
-- 错误日志
show variables like ‘%log_error%’;
-- 通用查询日志
show variables like ‘%general%’;
-- 二进制日志是否开启
show variables like ‘%log_bin%’;
-- 慢查询是否开启
show variables like ‘%slow_query%’;
SQL运行机制
- 建立连接 。 MySQL客户端与服务端通信 半双工（某一时刻要么发送数据，要么接收数据，不能同时 ）。有一个线程状态标识连接的信息
show processlist - 查询缓存，缓存查询的结果和sql语句（包括参数）
- 解析
- 优化，生成执行计划
- 查询执行引擎
存储引擎
InnoDB 和MyISAM对比
-
事务和外键
InnoDB 支持事务和外键
MyISAM 不支持事务和外键，访问速度快 -
锁机制
InnoDB 支持行级锁，基于索引来加锁
MyISAM 支持表级锁 -
索引结构
InnoDB 使用聚集索引，索引和记录在一起存储，
MyISAM 使用非聚集索引，索引和记录在分开存储 -
并发处理
MyISAM 使用表锁，写并发低
InnoDB 读写阻塞与隔离级别有关 -
存储文件
InnoDB对应 .frm(表结构) .ibd（数据文件），最大64TB
MyISAM 对应 .frm .MYD（表数据文件）.MYI（索引文件）
最大256TB
InnoDB内存结构
Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大组件。
InnoDB数据文件
表空间—>对应多个ibd数据文件-->Segment（段）-->64个Extent（区）-->Page（页，大小16k）—>Row（行）
Redo Log
Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做 日志。
在事务提交时会将产生 Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入 到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）
Redo Log和BinLog区别
- Redo Log属于InnoDB引擎功能，Bin Log属于MySQL server自带功能
- Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
- Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不 会覆盖使用。
- Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力。
索引原理
存储引擎用于快速查找记录的一种数据结构
- 索引会降低增删改操作速度，索引维护需要代价
- 索引是物理数据页存储，利用数据页存储
存储结构划分索引
- B Tress 索引
- Hash索引
- Full Text 全文索引
- R Tree索引
B+ Tree结构
- 非叶子节点不存储 data数据，只存储索引值
- 叶子节点包含所有索引值和data 数据
- 叶子节点用指针连接，提高区间访问性能
聚簇索引
一种数据存储方式，InnoDB的聚簇索引按照主键顺序构建 B+ Tree，叶子节点就是行记录，通常主键索引就是聚簇索引
辅助索引
根据索引列构建 B+ Tree，叶子节点只存放了索引列和主键信息
非聚簇索引
索引文件和数据分开， B+ Tree 叶子节点存放主键
Explain type分析
type：查询数据采用的方式
- All 全表扫描
- index 基于索引的全表扫描
- range 索引范围查询
- ref 非唯一索引等值查询
- eq_ref 多表 join查询，一对一关系查询
- const 主键或唯一索引等值查询
- NULL 不用访问表
回表查询
通过索引查询主键值，再去聚簇索引查询记录信息
覆盖索引
一棵索引数上就能获取SQL查询所需的字段，无需回表
分页查询机制
每一次都从第一条记录开始扫描，越往后查询越慢，查询数据越多越慢
MySQL 事务
ACID
- 原子性（Atomicity）
Redo日志保证修改的数据生效，Undo日志保证不该存在的数据撤销 - 一致性（Consistency）
- 隔离性（Isolation）
- 持久性（Durability），一旦事务提交，对数据库中的数据改变就是永久性的
MVCC（Multi Version Concurrency Control）
Copy on Write 思想， 每次事务修改操作之前，都会在Undo日志中记录修改之前的数据状态和事务号， 该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚
锁的分类
锁的粒度
- 表级锁
- 行级锁
操作的类型
- 读锁
- 写锁
操作的性能
- 悲观锁，数据更新提交的时候才会进行冲突 检测，如果发现冲突了，则提示错误信息
- 乐观锁，修改数据之前先锁定， 再修改的控制方式
行锁的原理
InnoDB行锁是通过对索引数据页上的记录加锁实现的。SQL操作含有唯一索引是，会对Next-key Lock（记录锁+范围锁）进行优化，降级为RecordLock,仅锁住索引本身而非范围。
MySQL集群架构
集群架构设计
- 可用性， 保证高可用的方法时冗余，主从模式
- 扩展性，分库分表
- 一致性，增加访问路由层
主从复制原理
- 主库将数据库的变更操作记录到 Binglog日志文件中
- 从库读取主库中的 Binglog日志文件，写入到从库的 Relay Log中继日志
- 从库读取中继日志在从库 进行 replay，更新从库数据信息
MMM架构
Master-Master Replication Manager for MySQL 管理和监控双主复制，支持双主故障自动切换的第三方软件，Perl语言开发。同一时间点只允许一个节点进行写入操作
MHA架构
Master High Availability, 故障切换和主从提升的高可用软件。支持一主多从的架构
分库分表
水平拆分：解决表中记录过多问题
垂直拆分：解决表过多或者是表字段过多问题
分片
表示分配过程，逻辑上的概念，表示如何实现
数据库扩展方案：
- 横向扩展： 一个库变多个库，加机器数量
- 纵向扩展：还是一个库，加高配CPU和内存
性能优化
mysql> show global status like '%innodb_buffer_pool_size%';
网友评论