MySQL优化

MySQL架构

• 网络连接层
• 服务层
  • 连接池
  • 系统管理和控制
  • SQL接口
  • 解析器, 对SQL语句检查，生成解析树
  • 查询优化器
  • 缓存
• 存储引擎层
• 文件系统
  • 系统文件
    • 日志文件

-- 错误日志
show variables like ‘%log_error%’;

-- 通用查询日志 
show variables like ‘%general%’;

-- 二进制日志是否开启
show variables like ‘%log_bin%’;

-- 慢查询是否开启
show variables like ‘%slow_query%’;

SQL运行机制

1. 建立连接 。 MySQL客户端与服务端通信 半双工（某一时刻要么发送数据，要么接收数据，不能同时 ）。有一个线程状态标识连接的信息
   show processlist
2. 查询缓存，缓存查询的结果和sql语句（包括参数）
3. 解析
4. 优化，生成执行计划
5. 查询执行引擎

存储引擎

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集群架构

集群架构设计

• 可用性， 保证高可用的方法时冗余，主从模式
• 扩展性，分库分表
• 一致性，增加访问路由层

主从复制原理

1. 主库将数据库的变更操作记录到 Binglog日志文件中
2. 从库读取主库中的 Binglog日志文件，写入到从库的 Relay Log中继日志
3. 从库读取中继日志在从库 进行 replay，更新从库数据信息

MMM架构

Master-Master Replication Manager for MySQL 管理和监控双主复制，支持双主故障自动切换的第三方软件，Perl语言开发。同一时间点只允许一个节点进行写入操作

MHA架构

Master High Availability, 故障切换和主从提升的高可用软件。支持一主多从的架构

分库分表

水平拆分：解决表中记录过多问题
垂直拆分：解决表过多或者是表字段过多问题

分片

表示分配过程，逻辑上的概念，表示如何实现
数据库扩展方案：

• 横向扩展： 一个库变多个库，加机器数量
• 纵向扩展：还是一个库，加高配CPU和内存

性能优化

mysql> show global status like '%innodb_buffer_pool_size%';