前言：大部分业务研发的学生在90%的工作场合都会和各种各样的数据打交道。 在这个过程中，其中一个棘手的问题就是MySQL。 本文从研究开发学生的角度介绍了使用MySQL所需的各种关键技术。

分别从MySQL体系结构、事务、锁定、索引、性能和部署体系结构几个方面进行介绍； 如果没有特别说明的话是指innodb引擎，希望您看了之后能得到什么。

要了解

体系架构

技术体系，一个非常推荐的方法是首先查看整个系统的架构设计。 这样可以快速预览整个框架，减少陌生人的感受。 然后通过分析典型的执行过程来加强过程控制

整个框架

在MySQL的设计中，可以将整体分为文件系统、存储(索引)、服务层三层。 我们直接面向的模块主要是连接池、SQL、优化程序和缓存等服务层相关模块，通过下图可以大致了解MySQL的配置

查询过程

让我们看看另一个SQL的执行过程。 SQL Cache-解析器-优化器-执行引擎-存储引擎我们设计的高性能服务端架构是通过缓存-业务逻辑-业务数据的吗？ 这个业务逻辑只是在数据库领域

事务

数据库事务必须是大部分程序员在面试中必须经历的主题。 首先，用一句话说明我理解的交易。

在并发方案中，将保障一个会话对数据操作如预期那样(原子性、一致性、隔离性、持续性)工作的方法；

以下分为名词解释和隔离水平，帮助我们了解事务；

名词的解释

如果设想多个客户机(A/B/N )并行操作一个表T1中的数据，则可能会出现以下情况：

1、脏读： a有可能在b会话中读取事务修正未提交的数据(意外的数据结果)

2、不可重复阅读：在一个事物内多次查询同一数据行，结果不一致

3、幻读：范围检索时2次检索可以看到不同的数据行(范围检索不限于比大小，也可以像部分in场景和全表扫描一样被复盖) )。

在上述情况下，MySQL定义了四个隔离级别：

PS :缺省隔离级别为Repeatable_Read

隔离级别

敲重点：

面试必须考试问题：

1、MySQL事务的隔离级别及其特性

2、对不准重复的读音、不准重复的读音、幻读的含义及其出现场景进行说明，能很好地回答上述问题，并在MySQL交易项目中合格。

高级：

1、数据库的默认隔离级别是什么？ 在线数据库使用的是什么级别？ 你为什么选择这个级别？

2、MySQL RR水平如何解决不可重复的读取？

3、MySQL RR水平解决了幻读吗？

要回答高级问题，必须了解MySQL的锁定MVCC

锁 MVCC

锁定是在并发情况下解决共享资源操作冲突问题的常用手段，同样地，MySQL也采用了该战略。 以下分别介绍MySQL的锁定

正式介绍之前先补充一点背景信息。 在MySQL中，读取分为快照读取和当前读取。

快照读取：从t1 where选择*；

当前导入：选择XXX锁定共享模式； 选择XXX为

更新； 插入/更新/删除XXX；

锁定型

钟表锁定

表字义是对标上锁，特点是支出小、上锁快、同时性低； MYISAM引擎只支持表锁

在innodb引擎的写入操作中，2种意向锁定(意向共享锁定和意向排他锁定)也是一种表锁定；

>页锁

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

行锁

1、行锁和字面意思有一点区别，主要是区分于Oracle数据库的行锁是针对物理数据块进行加锁，而MySQL是针对索引进行加锁的，也就意味着只有走索引才能加行锁否则在需要加锁的场景下就是表锁

2、行锁开销大、加锁慢、并发度高（冲突概率低）

3、行锁同样分为共享锁和排他锁

间隙锁

间隙锁是在RR级别下生效，在当前读场景下会锁定索引间的间隙，保证索引间的记录不变；以防止插入或更新间隙之间的记录；

而RR级别下的间隙锁也就部分解决了幻读的问题

nextKey lock

等于 行锁+间隙锁

锁冲突表

. IX IS X S IX 兼容 兼容 冲突 冲突 IS 兼容 兼容 冲突 兼容 X 冲突 冲突 冲突 冲突 S 冲突 兼容 冲突 兼容 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

它是一个矮jzddw；左右两个子树的高度差的绝对值不超过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）构建的 多副本强一致方案也有落地

其他数据库

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

关系型数据库

MySQL/Oracle/PG/SQLServer 等

非关系型数据库

Hbase/MongoDB/Cassandra/LevelDB/Redis等

图数据库

Neo4j/GraphDB等

end：如果你觉得本文对你有帮助的话，记得关注点赞转发，你的支持就是我更新动力。