Mysql相关梳理

MySQL体系结构

Buffer Pool

持久化与刷盘

Binary log

Redo log 与 Undo log属于事务日志

• Redo log 持久化，磁盘一般是两个文件。
• Undo log 记录了数据修改前的值，用作失败回滚。

Binary log 属于逻辑日志，它记录了所有的 DML 和 DDL 语句的操作，可以用来数据恢复以及主从复制。没有固定大小，内容可追加

• 把 Binary log 导出成 SQL 语句，所有操作都执行一遍，来实现数据恢复
• 从数据库 读取 主数据库的 Binary log ，实现主从复制
• 开启 Binary log 配合 Redo log 就为分布式事务的两阶段提交了

崩溃恢复时，判断事务是否提交方式

• Binary log 无记录，Redo log无记录，回滚事务
• Binary log 无记录，Redo log状态 prepare，回滚事务
• Binary log 有记录，Redo log状态prepare，提交事务
• Binary log 有记录，Redo log状态 commit，正常完成，不需要恢复。

索引

• 普通索引Normal，没有什么限制，方便查询
• 唯一索引Unique，唯一索引不可重复
• 主键索引Primary，特殊的唯一索引，值不能为空
• 全文索引Fulltext，针对比较大的数据，像消息内容/文章等用 like 匹配效率低可以用全文索引（只能存文本类型：char/varchar/text）。select * from table match(字段名) against('内容' in natural language mode)

索引存储模型推演

每访问一个节点都需要访问磁盘去进行 IO 操作，因此节点越多磁盘操作就越多，查询效率就低。

Page 页是 16kb，节点的存储是否可以达到 16kb，相对很重要

红黑树可以吗？

• 只有两路，深度太大，会增加IO的访问次数
• 一个节点只能存一个关键字，一次IO只能读取一个关键字
• 它黑平衡，还不是绝对平衡。最长路径比最短路径可以相差两倍

B+Tree落地方式

MyISAM

• 非聚簇索引，数据和文件分开存储（.MYD文件存储数据，.MYI文件存储索引）
• B+Tree中叶子节点存储的是数据文件对应的磁盘地址，因此它的主见索引与非主键索引存储和检索的方式一样

InnoDB

• 聚簇索引，数据和索引一起存储。索引即数据
• B+Tree 中只有聚簇索引的叶子节点存储数据，其他索引都是辅助索引也叫二级索引叶子节点存储的是聚簇索引。所以使用其他索引查询数据会出现回表查询问题（需要使用覆盖索引解决回表问题）

聚簇索引的定义

• 表中存在主键，那么主键就是聚簇索引
• 表中没有主键，那么第一个非空的唯一索引作为聚簇索引
• 如果没主键，也没非空唯一索引。那么会选择隐藏字段 DB_ROW_ID 作为隐藏的聚簇索引，_ROWID 是递增的。

事务

事务是数据库最小的工作单元，可能包含一个或一系列的 DML 语句

MySQL中不是所有的引擎都支持事务，除了集群NDB外，只有InnoDB支持事务

InnoDB 支持的隔离级别与 SQL92 标准一致，但 InnoDB 在 Repeatable Read 可重复读中解决了幻读问题

• 基于锁的并发控制，Lock-Based Concurrent Control 简称 LBCC 中的 间隙锁插入锁定区间 来解决幻读问题。
• 多版本快照的并发控制，Multi-Version Concurrent Control 建成 MVCC 快照，通过Read View可见性视图 来解决幻读问题

InnoDB 为每行记录都实现了三个隐藏字段

• DB_ROW_ID 6字节，行标识，也可以升级为聚簇索引；
• DB_TRX_ID 6字节，创建版本号，事务的ID；用于 ReadView 的事务判断。
• DB_ROLL_PTR 7字节，删除版本号，回滚指针; 用于 ReadView查找Undo log链来确定事务的版本

MVCC

一个事务可以看到的数据版本

• 第一次查询之前，已提交的事务的修改
• 本次事务的修改

一个事务不能看到的数据版本

• 本次事务第一次查询之后创建的事务（事务ID 比我大）
• 活跃的（未提交）的事务

锁机制

Mysql优化

索引优化

• 离散度高（重复值少）的字段上建立；离散度过低的索引查询比全表扫描可能还慢，比如性别
• 在 where/order by/Join on/group by 之上建立索引
• 不要在索引上使用函数或者计算，否则优化器会去全表扫描
• 索引要在有序的字段上建立，尽量避免无序列创建索引导致 B+Tree 的节点关键字大量的分裂合并
• 遵循最左匹配原则，离散度越高越靠前
• 索引个数不要太多，过多浪费空间更新慢；联合索引与单索引的索引冗余问题。
• 覆盖索引，可以解决回表问题。

SQL优化

• 避免使用 IN 或 NOT IN ，尽量用 EXISTS 或 NOT EXISTS来代替；

-- IN 写法
select name from teacher where id in ( select t_id from student);
-- EXISTS 替代
select a.name from teacher t where exists ( select * from student s where s.t_id = t.id )

• 避免使用 OR，可以用 Union all 优化；

-- OR 写法
select name from teacher where id = 10 or s_id = 20;
-- Union all 写法
select name from teacher where id = 10
union all
select name from teacher where s_id = 20;

• Union 先取并集再进行去重排序操作，如何不是必须的情况使用 Union all 替代，省去了去重排序的步骤
• where 条件下避免使用 !=、<> ，这种的不会使用索引
• where 条件下避免用 NULL判断字段，会进行全表扫描，可以设置默认值用 == 来判断
• like 尽量避免使用，容易造成索引失效，字段较长可以使用全文索引。
• 关联查询中尽量用 inner join 代替 left join 使用；因为SQL查询一般都会用小表撬大表，但是业务往往是大表是主表，所以 left join 不太合适，所以 inner join 这种不分主次的方式更为灵活的体现价值。

Mysql 连接优化

• 默认最大连接数 151，最大支持10万，可以适当的调大
• 客户端默认连接超时为8小时，可以适当的调小，即使释放不活动的连接
• 允许的情况下定时重启MySQL
• 表的力度拆细
• 慢SQL分析，Explan 查看之行计划等