程序员的MySQL攻略

前言：大部分业务研发同学在工作的90%的场景下会和各种数据打交道，在此过程中迈不过去的一个坎就是 MySQL 了，这篇文章从研发同学的视角来讲述在MySQL使用中需要了解的各种关键技术； 下面我会分别从MySQL的体系架构、事务、锁、索引、性能、部署架构几个方面来介绍; 非特殊注明的情况下专指innodb引擎，希望各位在看完之后能够有所收获！

体系架构

想要了解一个技术体系非常建议的一种方式是先看系统整体的架构设计，这样能够快速的预览整个框架，降低陌生感；然后通过分析一个典型的执行过程增强对过程的把控；

整体框架

在MySQL的设计中整体可以分为三层：文件系统、存储（索引）、服务层；我们所直接面向主要是服务层相关的模块如：连接池、 SQL 、优化器、缓存等，可以通过下图对MySQL的组成有个大概的了解

查询流程

再看一下一个SQL的执行过程，SQL Cache->解析器->优化器->执行引擎->存储引擎；是不是类似于我们设计的高性能服务端架构通过缓存->业务逻辑->业务数据;只是这个业务逻辑是在数据库领域的而已；

事务

数据库事务应该是大部分 程序员 在面试时的必经题目了，先一句话描述一下我所理解的事务： 在并发场景保障一次会话对数据操作符合预期（原子性、一致性、隔离性、持久性）的方式； 下面会分为名词解释和隔离级别两趴来帮助我们理解事务；

名词解释

想象一下我们有多个客户端（A/B/N）在并行的操作一张表T1中的数据，那么就可能会存在如下几种情况：

1、脏读：A可能读取到B会话中未提交事务修改的数据（不可预期的数据结果） 2、不可重复读：一个事物内多次查询同一数据行，结果是不一致 3、幻读：范围检索时两次检索会看到不同的数据行（范围检索不仅限于大于小于，部分的in场景或者全表扫描一样会被覆盖）

在以上场景下，MySQL定义了如下四种隔离级别： PS：默认隔离级别是Repeatable_Read

隔离级别

敲重点：

面试必考题: 1、MySQL事务的的隔离级别及其特性 2、解释一下来脏读、不可重复读、幻读的含义及其出现的场景 能够很好的回答以上问题，在MySQL事务单项上就及格了； 进阶： 1、数据库默认的隔离级别是什么？你们线上数据库用的是哪个级别?为什么选择这个级别？ 2、MySQL RR 级别是如何解决不可重复读？ 3、MySQL RR 级别是否解决了幻读？

想回答好进阶问题，就要对MySQL的锁 & MVCC有一定的了解了

锁 & MVCC

锁是并发场景下解决共享资源操作冲突问题的常见手段，同理MySQL也采用了该种策略，下面会分别介绍一下MySQL的锁 在正式介绍之前补充一点背景信息，对于MySQL而言，读分为快照读和当前读， 快照读：select * from t1 where ; 当前读：select xxx lock in share mode;select xxx for update;insert/update/delete xxx;

锁类型

表锁

表锁字义是对标进行加锁，特点是开销小，加锁快，并发度低； MYISAM 引擎仅支持表锁 在innodb引擎写操作中两种意向锁（意向共享锁和意向排他锁）也是一种表锁；

页锁

BDB引擎支持的锁类型，引擎和相关资料都比较少，锁粒度介于行和表之间

行锁

1、行锁和字面意思有一点区别，主要是区分于Oracle数据库的行锁是针对物理数据块进行加锁，而MySQL是针对索引进行加锁的，也就意味着只有走索引才能加行锁否则在需要加锁的场景下就是表锁 2、行锁开销大、加锁慢、并发度高（冲突概率低） 3、行锁同样分为共享锁和排他锁

间隙锁

间隙锁是在RR级别下生效，在当前读场景下会锁定索引间的间隙，保证索引间的记录不变；以防止插入或更新间隙之间的记录； 而RR级别下的间隙锁也就部分解决了幻读的问题

nextKey lock

等于 行锁+间隙锁

锁冲突表

MVCC

MVCC翻译过来是多版本并发控制；在RC和RR级别下生效，就是解决上面所提到不同隔离级别下的不可重复读和幻读的问题； 我们假设自己来来实现这个效果，一般的思路是不是在一个事物开启的时候 1、通过记录当前记录的快照，在事物结束之前一直读该快照来保障单次事物内结果是可预期的； 2、再加上锁来阻止其他并发更新 思路是正确的但是MySQL对快照的处理上不像我们想象的一样真的插入一条记录而是通过undo_log+readview的机制来实现的

数据结构

innodb引擎在数据中增加了 事务号DB_TRX_ID（创建、更新、删除） 回滚段指针DB_ROLL_PTR（用于追溯历史版本数据） DB_ROW_ID（随着新行插入而单调递增的行ID）

undo_log

insert/update/delete时会产生undo log undo log中包含重建该行记录被更新之前内容

readview

RC:事务中每条select语句都会创建一个快照(read view); RR：事务在begin/start transaction之后的第一条select读操作后, 会创建一个快照(read view), 将当前系统中活跃的其他事务记录记录起来 而这个区分也就决定了RR和RC两个级别下可见性的区别；

补充说明一下，有一种说法是幻读在当前读场景下是解决了; 但是在快照读的场景下还是还是存在对方已经提交但是在当前session读不出来该数据，所以是部分解决了（对细节感兴趣的话可以开两个session 自行验证一下);

索引

根据最上面的MySQL的体系架构图可以得知数据最终是存储在硬盘文件系统中的，计算机体系中硬盘数据是访问最慢的，但是受限于内存数据的空间，在这种约束下通过针对具体业务场景，对高频数据访问场景通过增加索引的方式提高数据访问效率

索引类型

聚簇索引

以主键创建的索引 1、聚集索引在叶子节点存储的是表中的数据

非聚簇索引（二级索引）

非主键创建的索引 1、索引叶子节点存储的是主键和索引列 2、在检索列中包含非索引列时，需要用叶子节点的主键，再去表中检索其他列（回表）

索引结构

数据结构应该都不陌生，而索引就是通过不同数据结构的特性：查询复杂度、空间、查询和新增性能等来做出的选择； 在一个海量数据的场景下我们想要保障查询的效率很直接的一种方式就是根据构建hash表； 另外一种相对可控的就是树形结构可以通过控制树的深度来保证查询的效率；

B+ tree

它是一个矮胖子；左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树; 这样的结构保障了查询效率的相对高效和稳定； 但是我们看一下在更新和新增场景下由于要维持以上特性，需要进行分裂或合并，这样对插入性能来说是有一定损失的；

Hash表

hash表的好处是查询复杂度理论值是o(1)，,但是带来的问题如下 1、空间的消耗 2、不支持范围和区间查询 3、大数据量场景下的hash冲突，可能会导致退化成链表 在目前的MySQL版本中hash索引是MySQL自适应的无法干预

匹配原则

基于以上索引的设计可以看出，可以分析出在索引使用的过程中有如下特点

一次只能走一个索引

一个表可能创建多个索引，但是在最终执行时仅采用一个索引

最左匹配

索引选择上根据 where 条件中的列字段，从左至右的匹配，直到遇到区间或范围查询列； 注意此处和SQL中where 后 in、=条件的列字段顺序无关；

查询列不参与计算

如果对列字段添加了计算函数，则无法走该字段的索引

where 和 order by limit 场景下的索引

在存在where 和 order by的场景下，本以为会按照where条件走索引，但是在实际情况下可能会发现最终索引是order by 字段，优先选择order by字段索引； 原因是：limit存在时，查询的顺序就有可能发生变化，查询过程不是先通过where过滤再 排序 再limit而是根据order by索引反向取进而然后匹配where 条件看看是否满足，有点类似于全表扫描了；

性能

即使是知道了以上各种原则，但在实际过程中还是可能遇到不符合预期的情况，这时就需要MySQL提供的debug利器执行计划了

执行计划

type index: 扫描全部索引树 range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询 ref: 非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找 eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描 const, system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。system是const类型的特例，当查询的表只有一行的情况下， 使用system。 NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。

possible_keys：可选的索引列 key：当前选择的索引列 ref：连接匹配条件,如果走主键索引的话,该值为: const, 全表扫描的话,为null值 rows：扫描行数

extra： using index表示在相应的select中使用了覆盖索引。 usingwhere表示存储引擎搜到记录后进行了后过滤(POST-FILTER)，如果查询未能使用索引，usingwhere的作用只是提醒我们mysql要用where条件过滤z结果集。 using temporay表示用临时表来存储结果集，常见于排序和分组查询。 usingfilesort，mysql中无法用索引完成的排序成为文件排序。

优化

索引区分度

选择高区分度列 覆盖索引：查询列尽量包含在索引中，否则需要回表

SQL优化

大表翻页：查询列尽量是索引列，否则建议用join或子查询的方式通过主键完成

乐观锁

select for update 和 基于version的乐观锁,在极限冲突场景下到底哪种性能更高待验证

数据量控制

分库分表 冷热数据分离 读写分离：备库可以考虑承担一部分读流量

部署架构

物理或者逻辑架构的设计对于面向客户提供的可靠性至关重要，作为数据的核心数据的不可丢失以及提供高性能的解决方案，从架构层面有一些设计方式；

主从

可靠性的一个重要保障方式就是做副本，一种经典的MySQL部署方式master-slave模式，部署两个节点：master和slave； master节点提供读写服务，通过解析binlog把数据变更复制到另外一台slave节点；存在ms级的延时，在某种程度上保障了数据的物理隔离，降低单点故障带来的损失；

分库

分库其实是一种逻辑上的架构，不过一般来说比较建议不同的逻辑库可以散列分布，来提高吞吐性能，在各个逻辑库上又可以做主从架构，每一个节点做一个备份。 不管是分库还是分表都要注意维度，按照业务场景来评估，尽量保证检索场景覆盖了拆分维度。

异地多活 & 多副本一致

传统上而言主备都是在同地域甚至是同一个机房来降低同步延时，但是在一些极限场景下对数据的可用性以及吞吐量要求更高，这个时候跨地域的逻辑库以及多副本的一致性架构就开始出现了，阿里巴巴电商交易在15年开始规划跨地域数据解决方案，其中主要用到的策略 1、按router路由，保障同一用户落在同一地域 2、跨地域的数据同步 3、同一sequence生产方式（集中式、分布式） 当前基于最新分布式一致协议（Paxos）构建的 多副本强一致方案也有落地

其他数据库

了解一些其他类型的数据库，在做技术选型和决策的时候可能会有所帮助，目前主流的大概是这些