NingG +

MySQL 最佳实践:常见问题汇总(2)

几个最朴素的问题

几个方面:

  1. 数据存储:
    1. 数据结构
  2. 索引:
    1. 为什么需要索引?如何评价索引?如何优化索引?
    2. 索引原理:索引怎么实现的?
  3. 事务:
    1. 事务如何实现:日志
    2. 事务隔离级别:MVCC 控制
  4. 集群:主从结构(主从复制的)

常见的问题

常见的问题:

  1. MySQL 复制的基本过程?
  2. 加锁的过程:加锁加在哪个地方?
    1. 是否在「索引文件」上加锁?
    2. 如果走了「辅助索引」,是否会同时在「辅助索引」和「聚集索引/聚簇索引」
  3. 事务如何实现?多少种日志?事务隔离级别
    1. 事务的 ACID 分别实现方式
  4. 索引
    1. 索引选择性、索引区分度:不重复行的占比
    2. Explain:
      1. 可用的索引、使用的索引、预估扫描的行数
      2. Extra 中 Using Index(不走聚簇索引)、Using Where(走聚簇索引,优化器需要回表查询)
  5. 数据存储:
    1. B 树 – B+树,没有 B-树,但是 B 树,经常显示为:B-Tree,有人读作 B- 树,是误读
    2. B 树,中间节点存放了数据,导致层数增加; B+ 树 层数更少,每一个节点都是一个页,减少了磁盘IO的次数;
  6. 日志:
    1. Slave 上,是否有 bin Log?
    2. Master 上,bin log 是否有 commit?事务之间是否是交叉的?
    3. bin log 日志的格式:适用场景、复制过程中的优劣
      1. statement
  7. 存储引擎:对比多种引擎
    1. MyISAM:不支持事务、表锁,索引都是非聚簇索引
    2. InnoDB:支持事务、行锁,主索引是聚簇索引
  8. MVCC:过程
    1. 多事务并发执行过程中,每个事务都会对应一个数据版本(snapshot),不同 snapshot 之间,拉链连接;
    2. 不同的事务,只能看到版本对应的 snapshot 数据
    3. MVCC 跟 锁之间的关系
  9. ACID的实现:
    1. 原子性:undo log,日志方式,实现事务回滚
    2. 隔离性:MVCC,多版本并发控制
  10. char、varchar
    1. char:定长,最后空格补齐
    2. varchar:变长,开头一个字节,记录字符长度,实际存储空间是:字符长度 + 1

典型问题剖析

集群:主从复制过程

整体上:异步单线程

细节上,注意 2 个日志文件:Master 上的 bin log、Slave 上的 relay log

MySQL 主从复制的过程:

  1. Master:Master 上,事务提交后,会写入 bin log
  2. 异步复制:异步线程,将 Master 上bin log新增的内容,同步到 Slave,并写入到 Slave 的 relay log 中;
  3. Slave:Slave 上,线程监听 relay log 的变化,并在 Slave 上,replay(重放)事务;

扩展:

  1. MySQL 5.5+:引入半同步复制(semi-sync),解决异步复制场景下,事务提交后, Master 宕机,事务丢失的问题(插件形式支持
    • 半同步复制:至少有一个 Slave 完成复制,Master 才会确认执行成功
    • Master:安装插件,开启半同步,同时,设置超时时间
    • Slave:安装插件,开启半同步
  2. MySQL 5.6+:引入多线程异步复制,改善复制的性能;
    • 多线程异步复制:只支持数据库Database)粒度的并发复制,
    • 解释:
      • 不同数据库的事务操作,能够并发使用多线程,复制到 Slave,
      • 同一个数据库内,执行的事务,只能单线程复制;
  3. MySQL 5.7+:提供半同步复制的备选方案
    • 之前半同步复制的隐患:事务已经同步到 Slave,但 Master 在向 Client 返回成功之前,Master 宕机,此时,Client 会向 Slave 重新提交事务请求,重复提交;问题本质原因:Master 事务提交之后,再向 Slave 同步事务。
    • 半同步复制新增备选方案:类似 2PC,即,Master 事务写入 bin log,先同步到 Slave,收到至少一个 Slave 的响应后,再在 Master 上提交事务
      • 第一阶段:Master 依赖 bin log 向 Slave 复制事务操作
      • 第二阶段:Master 收到至少一个 Slave 响应后,提交事务

集群:主从延时

主从延时:MySQL 的 MS 结构,采用异步复制,因此,Master 上提交的事务,Slave 同步到后,会有延时,这段时间内,Slave 跟 Master 的数据不一致,一般是 ms 级别。

主从延时的时间:Master 和 Slave 上事务提交的时间的时间差值

具体分析:延时时间的构成:

  1. Master 上,事务在 bin log排队时间;(生产者-消费者模型)
  2. 网络传输时间
  3. Slave 上,事务执行时间

主要解决:

  1. Master 上排队时间:业务上,减少多线程写的概率
  2. Slave 上事务执行时间:业务上,拆解大事务,变为小事务

详细分析,看这里:MySQL 技术内幕:主从同步和主从延时

基础:字段类型

几个典型问题:

Re:分开来说

基础:日志

MySQL 的日志,分为几类:

简单说几类日志:

MySQL 的 InnoDB 存储引擎,支持事务,而且支持主从同步复制,整个过程中,涉及多个日志:

基础:bin log 日志

MySQL 的 bin log 的日志格式,有 3 种:

  1. statement:事务 SQL,Slave 同步之后,会重新执行 SQL,达到跟 Master 一致的数据
  2. row:记录每行被修改的内容
  3. mixed:statement 和 row 自动切换

几种 bin log 日志格式的优劣:

  1. statement:事务SQL
    • 优点:bin log 的数据量小,节省磁盘资源,主从复制过程中,也节省了网络 IO 资源
    • 缺点:bin log 中,除了记录原始的 SQL,还需要记录每一行 SQL 执行的上下文信息,保证 Slave 上能够精确的重放;MySQL 版本演进很快,SQL 比较复杂时(last_insert_id()),主从复制出现 bug;
  2. row:每行数据变更内容
    • 优点:主从复制精确,稳定、问题少
    • 缺点:bin log 数据量大,耗费磁盘资源、网络IO资源
  3. mixed:根据 SQL,自动选择 statement 还是 row。

基础:锁的实现

场景:

MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的?

Re:

  1. InnoDB 的行锁是添加在主索引上的;
  2. 原因:InnoDB 的主索引聚簇索引,索引文件跟数据文件存放在一起,所有需要读取具体数据内容的操作,都需要经过主索引,即,主索引是唯一的数据访问入口。

基础:数据结构

B+ 树中:平衡的多路查找树

  1. 整体上时间复杂度,相对平衡二叉查找树,有一点优化(因为是多路查找树)
    1. 树高度降低
    2. 中间节点,二分查找
  2. 更多的是:中间节点,只存储索引字段,不存储数据信息,一个页(Page)内,可以存储更多的索引信息,每次页的读取,都需要一次磁盘 IO,使用 B+ 树,减少磁盘 IO 次数,节省时间。(磁盘 ms 级别)

实践:MySQL 状态异常

背景:

mysql数据库cpu飙升到500%

关键要定位问题、解决问题,具体:

  1. 定位进程:top (下文假设定位到了 MySQL
  2. 查看 MySQL 当前执行的线程,以及状态:show processlist,执行时长、等待锁的状态
  3. 其次,分析慢查询日志

实践:Explain 分析 SQL

explain 命令,分析 SQL 语句的执行计划,具体参数:

更多细节参考: MySQL的 explain

实践:大字段处理

背景:

表中有大字段FieldX(例如:text类型),且字段 FieldX 不会经常更新,以读为为主

思考:拆成子表,还是继续放一起?

Re:拆成子表 + lazy-load 方式

原因:

  1. B+树叶子节点对应一个
  2. 每个页包含的数据行越多,效率越高
  3. lazy-load方式,需要的时候,才去读取大字段

详说:

实践:SELECT 语句写法

背景:

2种写法 SELECT *SELECT 全部字段 有何优缺点

  1. 前者要解析数据字典,后者不需要
  2. 结果输出顺序:前者与建表列顺序相同,后者按指定字段顺序。
  3. 字段改名,前者不需要修改,后者需要改
  4. 后者的可读性比前者要高

实践:日期字段,是否适合创建索引

MySQL 中日期类型字段有:

对于日期字段,索引的使用需要特别注意:

因此,日期字段,创建索引时,要特别注意 SQL 优化。

更多索引使用情况,参考:如何提高查询速度

参考资料

TODO:

  1. 高性能 MySQL

TODO:

Top