MySQL必知必会

目录

什么是MySQL

MySQL是一个开源的关系型数据库管理系统,是非常流行的开源架构中LNMP的常用组件之一,MySQL几乎已经成为开源的标准数据库。在这篇文章,对MySQL的常用知识点进行整理。

MySQL架构

MySQL服务器架构,由以下几个组件构成,如客户端、连接池、查询缓存、分析器、优化器、存储引擎。整体步骤分为3步:

  1. 客户端发起请求->连接/线程处理->分析器/解析器
  2. 分析器/解析器->查询缓存->查询优化器
  3. 优化器从存储引擎查询/更新数据

整体流程见下图:

img

ACID

大多数数据库都会要求事务需要实现ACID特性,所谓的ACID就是:

  1. 原子性(atomicity):事务中的所有操作要么全部提交成功,要么全部失败回滚。
  2. 一致性(consistency):数据库总是从一个一致性状态转换到另一个一致性状态。
  3. 隔离性(isolation):一个事务所做的修改在提交之前对其它事务是不可见的。
  4. 持久性(durability):一旦事务提交,其所做的修改便会永久保存在数据库中。

隔离级别

InnoDB的默认隔离级别是REPEATABLE READ 可重读。

  1. READ UNCOMMITTED 可以读取未提交的事务
  2. READ COMMITED 只能读取已经提交的事务
  3. REPEATABLE READ 重复读取已提交的事务
  4. SERIALIZABLE 对事务进行加锁读
隔离级脏读可能性不可重复读可能性幻读可能性加锁读
READ UNCOMMITED
READ COMMITED
REPEATABLE READ
SERIALIZABLE

MVVC

InnoDB通过MVVC的并发控制解决了幻读问题。所谓的幻读就是,2个不同的线程,线程A执行查询操作时,得到结果集1,这时,线程B执行插入数据的操作并提交事务,线程A再次执行查询操作时,得到结果集2,会发现结果集2中出现新的插入的行,新增返回的行,就是所谓的幻影行。

通常数据的操作有3种,插入、更新、删除,删除内部与更新实现一致,特定位置标记为删除。通过在表的每一行添加3个字段记录相关回滚操作:DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。

  1. DB_TRX_ID字段指示插入或更新该行的最后一个事务的事务标识符。
  2. DB_ROLL_PTR 滚动指针指向写入回滚段的撤消日志记录。如果更新了行,则撤消日志记录包含在更新行之前重建行内容所需的信息。
  3. DB_ROW_ID字段包含在插入新行时单独增加的行ID。

InnoDB支持很多种锁,例如共享锁、排它锁、意向锁、行锁、间隙锁、下一键锁、插入意向锁等。

SELECT ... FROM 默认无锁,在 SERIALIZABLE 隔离级别会加锁,使用唯一索引查询唯一的行会要求索引记录锁定。

SELECT ... FROM FOR UPDATE
SELECT ... FROM LOCK IN SHARE MODE

对于唯一搜索条件的唯一索引,锁定找到的索引记录。对于其他搜索条件和非唯一索引,InnoDB锁定扫描的索引范围。

SELECT ... FROM FOR UPDATE会阻塞其他会话的共享锁,如LOCK IN SHARE MODE。

UPDATE WHERE 设置一个搜索匹配的每一行记录上添加排它的下一键锁定。对于唯一搜索条件的唯一索引,锁定找到的索引记录。

当UPDATE修改聚集索引记录是,对次要的索引记录进行隐式锁定。

DELETE FROM WHERE设置一个搜索匹配的每一行记录上添加排它的下一键锁定。对于唯一搜索条件的唯一索引,锁定找到的索引记录。

INSERT 在插入的行上设置独占锁,是索引记录锁,不会组织其他会话在插入行之前插入间隙。

INSERT ... ON DUPLICATE KEY UPDATE 与INSERT一致,不同在于发生重复键错误时,在要更新的行上采用独占锁而不是共享锁。对重复的主键值采用独占索引记录锁定,对于重复的唯一键值,采用独占的下一键锁定。

REPLACE 与INSERT一致,如果有唯一键冲突,将在要替换的行上放置专用的下一键锁。

INSERT INTO T SELECT ... FROM S WHERE ... 在要插入到T的每一行上设置独占索引记录锁。

REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...) InnoDB设置下一键锁在行上。

AUTO_INCREMENT InnoDB在与自动增量列关联的索引末尾设置独占锁。

InnoDB对定义外键约束的条件的INSERT,UPDATE,DELETE,都会设置一个共享记录锁。

LOCK TABLES 设置一个表锁,由在InnoDB层更高的MySQL层设置的。

死锁的产生

MySQL官方文章,讲解一个死锁的产生案例。

  1. 客户端A请求S lock START TRANSACTION;SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
  2. 这时客户端B 开启一个事务,删除这条数据。START TRANSACTION;DELETE FROM t WHERE i = 1; 删除操作要一个排它锁 X lock。
  3. 然后,客户端A也尝试删除这条数据。DELETE FROM t WHERE i = 1;
  4. 这时就发生了死锁。ERROR 1213 (40001): Deadlock found when trying to get lock;try restarting transaction

InnoDB自动检测事务死锁并回滚一个或者多个事务以打破死锁,默认会回滚较小的事务,其中事务大小取决于插入、更新或者删除的行数。

索引的实现

MySQL的InnoDB存储引擎采用的是B+Tree数据结构实现数据的存储。B+Tree通过降低高度,将节点的索引存储在内存里,非索引的节点数据存储在硬盘上,提升读取效率。在数据结构与算法之美里,也讲解了不同数据结构之间的对比。

查找条件/数据结构散列表平衡二叉查找树跳表B+Tree备注
根据某个值查找数据
根据区间值来查找某些数据
二叉树
m叉树m预先计算

常用优化方法

在数据量达到一定级别,如TB级别,单库单表的容量比较大,会导致查询更新操作较慢。这时候,就需要对数据库进行物理级别的优化。

分库分表

优先独立拆分不同业务的数据库和表,方便后续管理维护,也就是所谓的垂直拆分。

  1. 对不同业务分离不同的数据库,如订单库、用户库、产品库等。
  2. 对较大的表,将常用的字段分离成不同的小表,不常用的字段分离单独的表,提升查询效率。

根据业务特点,将数据库特定的算法分布到不同的库和表,这就是水平拆分。

  1. 在同一个库里,根据hash取摸、范围切分、按时间、区域到不同的表。
  2. 大表拆分为小表,避免性能瓶颈。

范围分表是根据用户ID范围分布,比如1-10000一个表。

hash取模,例如根据用户ID或者订单ID对128取模,平均分布到128个表中。

按时间分表,最简单的是每天一个表,每天数据都存储在不同的表,也可以将3-6个月前的数据分离到其他的库表,毕竟历史数据查询概率较小,当前存储查询访问较大的热数据即可,这也就是所谓的冷热数据分离。

查询优化标准

让查询尽可能快的方法,就是尽可能命中索引。假设有这样的索引key(last_name ,first_name,dob),那么MySQL可以非常高效工作的索引类型如下:

  1. 匹配全部的列 last_name = ‘Allen’ and first_name =’Cuba’ and Dob=’1960-01-01′
  2. 匹配最左前缀 last_name =’Allen’
  3. 匹配列前缀 last_name like “J%”
  4. 匹配范围值 last_name >= “Allen” and last_name <= “Barry”
  5. 精确匹配一部分并且匹配某个范围中的另一部分 last_name = “Allen” and first_name like “K%”
  6. 只访问索引的查询。也就是索引覆盖了全部查询字段的查询。

在阿里巴巴Java开发手册里,有一个SQL性能优化的目标推荐标准:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts最好。

那这几个目标到底是啥意思呢?所谓的range是对索引进行范围检索。那到底什么是对索引进行范围检索呢?从上面例子,可以看出来,所谓的范围检索,就是对索引进行大于某个值且小于某个值的条件查找。

什么是索引全扫描呢?explain的结果就是type=index的查询类型,针对索引全部记录进行查找呗。这个与全表扫描类似,一个对索引所有记录进行扫描,一个是对表所有记录进行扫描。

ref级别,就是指命中最左前缀、主键、唯一索引等多行的查询,在explain显示type=ref。

const级别,指最多有一个匹配行的查询,比如命中主键、唯一索引的查询。

高可用

什么是高可用(High Availability)?人们通常是这样定义的,很少或者几乎没有服务不可用,就是高可用。这其实从逆向来定义的,它不是什么。那到底什么是高可用?有没有一个相对准确的定义?业界常用的测量系统的高可用性,是故障发生到恢复的时间,如下表:

系统可用性%宕机时间/年宕机时间/月宕机时间/周宕机时间/天
90% (1个9)36.5 天72 小时16.8 小时2.4 小时
99% (2个9)3.65 天7.20 小时1.68 小时14.4 分
99.9% (3个9)8.76 小时43.8 分10.1 分钟1.44 分
99.99% (4个9)52.56 分4.38 分1.01 分钟8.66 秒
99.999% (5个9)5.26 分25.9 秒6.05 秒0.87 秒

那MySQL如何实现高可用性呢?较为常用的工具有MHA(Master High Availability Manager and tools for MySQL)。部署MHA后,通过VIP对外提供服务,MHA服务会定期检查master的存活状态,一旦Master发生故障,会尝试如下流程:

  1. 通过SSH保存master的二进制日志binlogs
  2. 选择最新的slave节点
  3. 同步最新slave节点与其他slave节点的差异,也就是应用差异的中继日志到其他slave。
  4. 应用从master保存的二进制日志binlogs
  5. 提升最新的slave为master
  6. 其他的slave连接slave尝试复制。

负载均衡

大多数互联网应用都遵循一个28原则,也就是大约80%的请求都是读请求,20%是写入的请求。常见的采用HAProxy提供两个VIP提供服务,一个VIP负责读请求,一个VIP负责写请求。MySQL集群可以为主从、主主模式。

故障转移/恢复/容灾

针对故障转移这类,按照范围可以分为数据中心内部和外部。

内部一般由于服务器故障或网络等原因导致服务不可用,需要针对服务做自动转移,服务切换。

外部的话,比如光缆挖断了,地震了这类不可控的因素导致服务不可用,必须提前准备容灾措施。

由于金融行业对数据的要求较高,比如阿里云的两地三中心架构,两地指同城和异地,三中心指生产中心、同城容灾中心、异地容灾中心,提供超高可用的服务架构。这类金融行业的架构,成本和收益是成正比的。笔者所在的公司,采用了同地两中心,分别在两个数据中心部署了相关的服务,随时可切换到不同的数据中心,然后对核心数据进行异地的同步备份,定期备份核心业务数据,对监管要求较高的业务,需要保存5年以上的时间。

总体来说,对于同城的数据中心实时同步数据,异地数据进行定期备份。

参考链接

  1. 关于高可用的系统
  2. 金融交易系统异地灾备方案
  3. 金融云存储灾备之两地三中心

img