数据库相关

读过MyBatis源码没有？

聊一聊对分库分表的理解

大众点评设计案例

原大众点评的订单单表早就已经突破两百G，由于查询维度较多，即使加了两个从库，优化索引，仍然存在很多查询不理想的情况。去年大量抢购活动的开展，使数据库达到瓶颈，应用只能通过限速、异步队列等对其进行保护；业务需求层出不穷，原有的订单模型很难满足业务需求，但是基于原订单表的DDL又非常吃力，无法达到业务要求。随着这些问题越来越突出，订单数据库的切分就愈发急迫了。这次切分，我们的目标是未来十年内不需要担心订单容量的问题。先对订单库进行垂直切分，将原有的订单库分为基础订单库、订单流程库等。

垂直切分缓解了原来单集群的压力，但是在抢购时依然捉襟见肘。原有的订单模型已经无法满足业务需求，于是我们设计了一套新的统一订单模型，为同时满足C端用户、B端商户、客服、运营等的需求，我们分别通过用户ID和商户ID进行切分，并通过PUMA（我们内部开发的MySQL binlog实时解析服务）同步到一个运营库。

切分策略
- 查询切分：将ID和库的Mapping关系记录在一个单独的库中。优点：ID和库的Mapping算法可以随意更改。缺点：引入额外的单点。
- 范围切分：比如按照时间区间或ID区间来切分。优点：单表大小可控，天然水平扩展。缺点：无法解决集中写入瓶颈的问题。
- Hash切分：一般采用Mod来切分，下面着重讲一下Mod的策略。数据水平切分后我们希望是一劳永逸或者是易于水平扩展的，所以推荐采用mod 2^n这种一致性Hash。以统一订单库为例，我们分库分表的方案是32*32的，即通过UserId后四位mod 32分到32个库中，同时再将UserId后四位Div 32 Mod 32将每个库分为32个表，共计分为1024张表。线上部署情况为8个集群(主从)，每个集群4个库。为什么说这种方式是易于水平扩展的呢？我们分析如下两个场景。
  - 场景一：数据库性能达到瓶颈
    - 方法一：按照现有规则不变，可以直接扩展到32个数据库集群。
    - 方法二：如果32个集群也无法满足需求，那么将分库分表规则调整为(322^n)(32⁄2^n)，可以达到最多1024个集群。
  - 场景二：单表容量达到瓶颈（或者1024已经无法满足你）
    方法：假如单表都已突破200G，2001024=200T（按照现有的订单模型算了算，大概一万千亿订单，相信这一天，嗯，指日可待！），没关系，32 (32 2^n)，这时分库规则不变，单库里的表再进行裂变，当然，在目前订单这种规则下（用userId后四位 mod）还是有极限的，因为只有四位，所以最多拆8192个表，至于为什么只取后四位，后面会有篇幅讲到。另外一个维度是通过ShopID进行切分，规则8 8和UserID比较类似，就不再赘述，需要注意的是Shop库我们仅存储了订单主表，用来满足Shop维度的查询。
唯一ID方案：这个方案也很多，主流的有那么几种:
- 利用数据库自增ID：优点：最简单。缺点：单点风险、单机性能瓶颈。
- 利用数据库集群并设置相应的步长（Flickr方案）：优点：高可用、ID较简洁。缺点：需要单独的数据库集群。
- Twitter Snowflake：优点：高性能高可用、易拓展。缺点：需要独立的集群以及ZK。
- 一大波GUID、Random算法：优点：简单。缺点：生成ID较长，有重复几率。
- 我们的方案：为了减少运营成本并减少额外的风险我们排除了所有需要独立集群的方案，采用了带有业务属性的方案： > 时间戳+用户标识码+随机数有下面几个好处：
  - 方便、成本低。
  - 基本无重复的可能。
  - 自带分库规则，这里的用户标识码即为用户ID的后四位，在查询的场景下，只需要订单号就可以匹配到相应的库表而无需用户ID，只取四位是希望订单号尽可能的短一些，并且评估下来四位已经足够。
  - 可排序，因为时间戳在最前面。
  当然也有一些缺点，比如长度稍长，性能要比int/bigint的稍差等。
其他问题
- 事务支持：我们是将整个订单领域聚合体切分，维度一致，所以对聚合体的事务是支持的。
- 复杂查询：垂直切分后，就跟join说拜拜了；水平切分后，查询的条件一定要在切分的维度内，比如查询具体某个用户下的各位订单等；禁止不带切分的维度的查询，即使中间件可以支持这种查询，可以在内存中组装，但是这种需求往往不应该在
- 在线库查询，或者可以通过其他方法转换到切分的维度来实现。
数据迁移

数据库拆分一般是业务发展到一定规模后的优化和重构，为了支持业务快速上线，很难一开始就分库分表，垂直拆分还好办，改改数据源就搞定了，一旦开始水平拆分，数据清洗就是个大问题，为此，我们经历了以下几个阶段。
- 第一阶段
  - 数据库双写（事务成功以老模型为准），查询走老模型。
  - 每日job数据对账（通过DW），并将差异补平。
  - 通过job导历史数据。
- 第二阶段
  - 历史数据导入完毕并且数据对账无误。
  - 依然是数据库双写，但是事务成功与否以新模型为准，在线查询切新模型。
  - 每日job数据对账，将差异补平。
- 第三阶段
  - 老模型不再同步写入，仅当订单有终态时才会异步补上。
  - 此阶段只有离线数据依然依赖老的模型，并且下游的依赖非常多，待DW改造完就可以完全废除老模型了。
总结
并非所有表都需要水平拆分，要看增长的类型和速度，水平拆分是大招，拆分后会增加开发的复杂度，不到万不得已不使用。在大规模并发的业务上，尽量做到在线查询和离线查询隔离，交易查询和运营/客服查询隔离。拆分维度的选择很重要，要尽可能在解决拆分前问题的基础上，便于开发。数据库没你想象的那么坚强，需要保护，尽量使用简单的、良好索引的查询，这样数据库整体可控，也易于长期容量规划以及水平扩展。

亿级数据下的分库分表方案

分区

分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。这个方案也不错，它对用户屏蔽了sharding的细节，即使查询条件没有sharding column，它也能正常工作（只是这时候性能一般）。不过它的缺点很明显：很多的资源都受到单机的限制，例如连接数，网络吞吐等。如何进行分区，在实际应用中是一个非常关键的要素之一。在我们的项目中，以客户信息为例，客户数据量5000万加，项目背景要求保存客户的银行卡绑定关系，客户的证件绑定关系，以及客户绑定的业务信息。此业务背景下，该如何设计数据库呢。项目一期的时候，我们建立了一张客户业务绑定关系表，里面冗余了每一位客户绑定的业务信息。基本结构大致如下：

查询时，对银行卡做索引，业务编号做索引，证件号做索引。随着需求大增多，这张表的索引会达到10个以上。而且客户解约再签约，里面会保存两条数据，只是绑定的状态不同。假设我们有5千万的客户，5个业务类型，每位客户平均2张卡，那么这张表的数据量将会达到惊人的5亿，事实上我们系统用户量还没有过百万时就已经不行了。mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。这三个文件都非常的庞大，尤其是.myd文件，快5个G了。下面进行第一次分区优化，Mysql支持的分区方式有四种：

在我们的项目中，range分区和list分区没有使用场景，如果基于绑定编号做range或者list分区，绑定编号没有实际的业务含义，无法通过它进行查询，因此，我们就剩下 HASH 分区和 KEY 分区了， HASH 分区仅支持int类型列的分区，且是其中的一列。看看我们的库表结构，发现没有哪一列是int类型的，如何做分区呢？可以增加一列，绑定时间列，将此列设置为int类型，然后按照绑定时间进行分区，将每一天绑定的用户分到同一个区里面去。这次优化之后，我们的插入快了许多，但是查询依然很慢，为什么，因为在做查询的时候，我们也只是根据银行卡或者证件号进行查询，并没有根据时间查询，相当于每次查询，mysql都会将所有的分区表查询一遍。

然后进行第二次方案优化，既然hash分区和key分区要求其中的一列必须是int类型的，那么创造出一个int类型的列出来分区是否可以。分析发现，银行卡的那串数字有秘密。银行卡一般是16位到19位不等的数字串，我们取其中的某一位拿出来作为表分区是否可行呢，通过分析发现，在这串数字中，其中确实有一位是0到9随机生成的，不同的卡串长度，这一位不同，绝不是最后一位，最后位数字一般都是校验位，不具有随机性。我们新设计的方案，基于银行卡号+随机位进行KEY分区，每次查询的时候，通过计算截取出这位随机位数字，再加上卡号，联合查询，达到了分区查询的目的，需要说明的是，分区后，建立的索引，也必须是分区列，否则的话，Mysql还是会在所有的分区表中查询数据。那么通过银行卡号查询绑定关系的问题解决了，那么证件号呢，如何通过证件号来查询绑定关系。前面已经讲过，做索引一定是要在分区健上进行，否则会引起全表扫描。我们再创建了一张新表，保存客户的证件号绑定关系，每位客户的证件号都是唯一的，新的证件号绑定关系表里，证件号作为了主键，那么如何来计算这个分区健呢，客户的证件信息比较庞杂，有身份证号，港澳台通行证，机动车驾驶证等等，如何在无序的证件号里找到分区健。为了解决这个问题，我们将证件号绑定关系表一分为二，其中的一张表专用于保存身份证类型的证件号，另一张表则保存其他证件类型的证件号，在身份证类型的证件绑定关系表中，我们将身份证号中的月数拆分出来作为了分区健，将同一个月出生的客户证件号保存在同一个区，这样分成了12个区，其他证件类型的证件号，数据量不超过10万，就没有必要进行分区了。这样每次查询时，首先通过证件类型确定要去查询哪张表，再计算分区健进行查询。

作了分区设计之后，保存2000万用户数据的时候，银行卡表的数据保存文件就分成了10个小文件，证件表的数据保存文件分成了12个小文件，解决了这两个查询的问题，还剩下一个问题就是，业务编号呢，怎么办，一个客户有多个签约业务，如何进行保存，这时候，采用分区的方案就不太合适了，它需要用到分表的方案。

分库分表

如何进行分库分表，目前互联网上有许多的版本，比较知名的一些方案：

阿里的TDDL，DRDS和cobar
京东金融的sharding-jdbc
民间组织的MyCAT
360的Atlas
美团的zebra
其他比如网易，58，京东等公司都有自研的中间件。

百花齐放的景象。但是这么多的分库分表中间件方案，归总起来，就两类： client模式和proxy模式。

client模式
和proxy模式

无论是client模式，还是proxy模式，几个核心的步骤是一样的：SQL解析，重写，路由，执行，结果归并。个人比较倾向于采用client模式，它架构简单，性能损耗也比较小，运维成本低。如果在项目中引入mycat或者cobar，他们的单机模式无法保证可靠性，一旦宕机则服务就变得不可用，你又不得不引入HAProxy来实现它的高可用集群部署方案，为了解决HAProxy的高可用问题，又需要采用Keepalived来实现。

我们在项目中放弃了这个方案，采用了shardingjdbc的方式。回到刚才的业务问题，如何对业务类型进行分库分表。分库分表第一步也是最重要的一步，即sharding column的选取，sharding column选择的好坏将直接决定整个分库分表方案最终是否成功。而sharding column的选取跟业务强相关。在我们的项目场景中，sharding column无疑最好的选择是业务编号。通过业务编号，将客户不同的绑定签约业务保存到不同的表里面去，查询时，根据业务编号路由到相应的表中进行查询，达到进一步优化sql的目的。

前面我们讲到了基于客户签约绑定业务场景的数据库优化，下面我们再聊一聊，对于海量数据的保存方案。

垂直拆分

垂直分表

也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的，数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

垂直分库

垂直分库在“微服务”盛行的今天已经非常普及了。基本的思路就是按照业务模块来划分出不同的数据库，而不是像早期一样将所有的数据表都放到同一个数据库中。如下图：

优点

数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于 Web 和应用服务器来讲，是比较难实现“横向扩展”的。数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破 IO、连接数及单机硬件资源的瓶颈，是大型分布式系统中优化数据库架构的重要手段。

缺点

跨库 join 的问题
跨库事务（分布式事务）的问题

解决方式

全局表
- 所谓全局表，就是有可能系统中所有模块都可能会依赖到的一些表。比较类似我们理解的“数据字典”。为了避免跨库 join 查询，我们可以将这类表在其他每个数据库中均保存一份。同时，这类数据通常也很少发生修改（甚至几乎不会），所以也不用太担心“一致性”问题。
字段冗余
- 这是一种典型的反范式设计，在互联网行业中比较常见，通常是为了性能来避免 join 查询。
数据同步
- 定时 A 库中的 tab_a 表和 B 库中 tbl_b 有关联，可以定时将指定的表做同步。当然，同步本来会对数据库带来一定的影响，需要性能影响和数据时效性中取得一个平衡。这样来避免复杂的跨库查询。例如ETL工具。
系统层组装
- 在系统层面，通过调用不同模块的组件或者服务，获取到数据并进行字段拼装。说起来很容易，但实践起来可真没有这么简单，尤其是数据库设计上存在问题但又无法轻易调整的时候。

对于每分钟要处理近1000万的流水，每天流水近1亿的量，如何高效的写入和查询，是一项比较大的挑战。还是老办法，分库分表分区，读写分离，只不过这一次，我们先分表，再分库，最后分区。我们将消息流水按照不同的业务类型进行分表，相同业务的消息流水进入同一张表，分表完成之后，再进行分库。我们将流水相关的数据单独保存到一个库里面去，这些数据，写入要求高，查询和更新到要求低，将它们和那些更新频繁的数据区分开。分库之后，再进行分区。

这是基于业务垂直度进行的分库操作，垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库，以达到系统资源的饱和利用率。这样的分库方案结合应用的微服务治理，每个微服务系统使用独立的一个数据库。将不同模块的数据分库存储，模块间不能进行相互关联查询，如果有，要么通过数据冗余解决，要么通过应用代码进行二次加工进行解决。若不能杜绝跨库关联查询，则将小表到数据冗余到大数据量大库里去。假如，流水大表中查询需要关联获得渠道信息，渠道信息在基础管理库里面，那么，要么在查询时，代码里二次查询基础管理库中的渠道信息表，要么将渠道信息表冗余到流水大表中。

将每天过亿的流水数据分离出去之后，流水库中单表的数据量还是太庞大，我们将单张流水表继续分区，按照一定的业务规则，（一般是查询索引列）将单表进行分区，一个表编程N个表，当然这些变化对应用层是无法感知的。

分区表的设置，一般是以查询索引列进行分区，例如，对于流水表A，查询需要根据手机号和批次号进行查询，所以我们在创建分区的时候，就选择以手机号和批次号进行分区，这样设置后，查询都会走索引，每次查询Mysql都会根据查询条件计算出来，数据会落在那个分区里面，直接到对应的分区表中检索即可，避免了全表扫描。

对于每天流水过亿的数据，当然是要做历史表进行数据迁移的工作了。客户要求流水数据需要保存半年的时间，有的关键流水需要保存一年。删数据是不可能的了，也跑不了路，虽然当时非常有想删数据跑路的冲动。其实即使是删数据也是不太可能的了，delete的拙劣表演先淘汰了，truncate也快不了多少，我们采用了一种比较巧妙方法，具体步骤如下：

创建一个原表一模一样的临时表1 create table test_a_serial_1 like test_a_serial;
将原表命名为临时表2 alter table test_a_serial rename test_a_serial_{date};
将临时表1改为原表 alter table able test_a_serial_1 rename able test_a_serial; 此时，当日流水表就是一张新的空表了，继续保存当日的流水，而临时表2则保存的是昨天的数据和部分今天的数据，临时表2到名字中的date时间是通过计算获得的昨日的日期；每天会产生一张带有昨日日期的临时表2，每个表内的数据大约是有1000万。
将当日表中的历史数据迁移到昨日流水表中去这样的操作都是用的定时任务进行处理，定时任务触发一般会选择凌晨12点以后，这个操作即使是几秒内完成，也有可能会有几条数据落入到当日表中去。因此我们最后还需要将当日表内的历史流水数据插入到昨日表内； insert into test_a_serial_{date}(cloumn1,cloumn2….) select(cloumn1,cloumn2….) from test_a_serial where LEFT(create_time,8) > CONCAT(date); commit;

如此，便完成了流水数据的迁移；
根据业务需要，有些业务数据需要保存半年，超过半年的进行删除,在进行删除的时候，就可以根据表名中的_{date}筛选出大于半年的流水直接删表；

半年的时间，对于一个业务流水表大约就会有180多张表，每张表又有20个分区表，那么如何进实时计算统计行查询呢？由于我们的项目对于流水的查询实时性要求不是特别高，因此我们在做查询时，进行了根据查询时间区间段进行路由查询的做法。大致做法时，根据客户选择的时间区间段，带上查询条件，分别去时间区间段内的每一张表内查询，将查询结果保存到一张临时表内，然后，再去查询临时表获得最终的查询结果。

半年的时间，对于一个业务流水表大约就会有180多张表，每张表又有20个分区表，那么如何进行查询呢？由于我们的项目对于流水的查询实时性要求不是特别高，因此我们在做查询时，进行了根据查询时间区间段进行路由查询的做法。大致做法时，根据客户选择的时间区间段，带上查询条件，分别去时间区间段内的每一张表内查询，将查询结果保存到一张临时表内，然后，再去查询临时表获得最终的查询结果。

以上便是我们面对大数据量的场景下，数据库层面做的相应的优化，一张每天一亿的表，经过拆分后，每个表分区内的数据在500万左右。

水平拆分

水平分表

针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。

某种意义上来讲，有些系统中使用的“冷热数据分离”（将一些使用较少的历史数据迁移到其他的数据库中。而在业务功能上，通常默认只提供热点数据的查询），也是类似的实践。在高并发和海量数据的场景下，分库分表能够有效缓解单机和单库的性能瓶颈和压力，突破 IO、连接数、硬件资源的瓶颈。当然，投入的硬件成本也会更高。同时，这也会带来一些复杂的技术问题和挑战（例如：跨分片的复杂查询，跨分片事务等）。
水平分库分表

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。
水平分库分表切分规则
- RANGE
  - 从0到10000一个表，10001到20000一个表；
- HASH取模
  - 一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。取用户id，然后hash取模，分配到不同的数据库上。
- 地理区域
  - 比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。
- 时间
  - 按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

MySQL主从同步与主主同步

MySQL复制

MySQL内建的复制功能是构建大型，高性能应用程序的基础。将MySQL的数据分布到多个系统上去，这种分布的机制，是通过将mysql的某一台主机的数据复制到其它主机（slave）上，并重新执行一遍来实现。

复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循坏，这些日志可以记录发送到从服务器的更新。当一个从服务器

连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知的更新。

需注意的是：

在进行mysql复制时，所有对复制中的表的更新必须在主服务器上进行。否则必须要小心，以避免用户对主服器上的表进行更新与对从服务器上的表所进行更新之间的冲突。

mysql支持哪些复制

a.基于语句的复制：在主服务器上执行的sql语句，在从服务器上执行同样的语句。mysql默认采用基于语句的复制，效率边角高。一旦发现没法精确复制时，会自动选着基于行的复制。

b.基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从mysql 5.0开始支持

c.混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确复制时，就会采用基于行的复制。

mysql复制解决的问题

a.数据分布（data distribution）

b.负载平衡（load balancing）

c.数据备份（backup），保证数据安全

d.高可用性与容错行（high availability and failover）

e.实现读写分离，缓解数据库压力

mysql主从复制原理

master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，

如果发生改变，则开始一个I/O Thread请求master二进制事件，同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志

中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒。

注意几点：

1–master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。

2–slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和 master数据保持一致了。

3–Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。

4–Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）

5–master和slave两节点间时间需同步

Mysql复制的流程图如下：

如上图所示：

Mysql复制过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

第二部分就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

mysql复制的模式

1–主从复制：主库授权从库远程连接，读取binlog日志并更新到本地数据库的过程；主库写数据后，从库会自动同步过来（从库跟着主库变）；

2–主主复制：主从相互授权连接，读取对方binlog日志并更新到本地数据库的过程；只要对方数据改变，自己就跟着改变；

mysql主从复制优点

1–在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;（主库写，从库读，降压）

2–在从主服务器进行备份，避免备份期间影响主服务器服务;（确保数据安全）

3–当主服务器出现问题时，可以切换到从服务器。（提升性能）

mysql主从复制工作流程细节

a. MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器上已经记录到其二进制日志的保存的更新。当一个从服务器连接主服务器时，它通知主服务器定位到从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。

b. MySQL使用3个线程来执行复制功能，其中两个线程(Sql线程和IO线程)在从服务器，另外一个线程(IO线程)在主服务器。
当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以即为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，由从服务器创建，用于读取中继日志并执行日志中包含的更新。在从服务器上，读取和执行更新语句被分成两个独立的任务。当从服务器启动时，其I/O线程可以很快地从主服务器索取所有二进制日志内容，即使SQL线程执行更新的远远滞后。

总结

主从数据完成同步的过程：

1）在Slave 服务器上执行sartslave命令开启主从复制开关，开始进行主从复制。

2）此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器，并请求从执行binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容

3）Master服务器接收到来自Slave服务器的IO线程的请求后，其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在Master服务器端记录的IO线程。返回的信息中除了binlog中的下一个指定更新位置。

4）当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（Mysql-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容

5）Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点

主从复制条件

1）开启Binlog功能

2）主库要建立账号

3）从库要配置master.info（CHANGE MASTER to…相当于配置密码文件和Master的相关信息）

4）start slave 开启复制功能

需要了解的

1）3个线程，主库IO，从库IO和SQL及作用

2）master.info（从库）作用

3）relay-log 作用

4）异步复制

5）binlog作用（如果需要级联需要开启Binlog）

需要注意

1）主从复制是异步的逻辑的SQL语句级的复制

2）复制时，主库有一个I/O线程，从库有两个线程，I/O和SQL线程

3）实现主从复制的必要条件是主库要开启记录binlog功能

4）作为复制的所有Mysql节点的server-id都不能相同

5）binlog文件只记录对数据库有更改的SQL语句（来自主库内容的变更），不记录任何查询（select，show）语句

彻底解除主从复制关系

1)stop slave;

2)reset slave; 或直接删除master.info和relay-log.info这两个文件；

3)修改my.cnf删除主从相关配置参数。

让slave不随MySQL自动启动

修改my.cnf 在[mysqld]中增加 skip-slave-start 选项。

做了MySQL主从复制以后，使用mysqldump对数据备份时，一定要注意按照如下方式：

mysqldump --master-data --single-transaction --user=username --password=password dbname> dumpfilename

这样就可以保留 file 和 position 的信息，在新搭建一个slave的时候，还原完数据库， file 和 position 的信息也随之更新，接着再start slave 就可以很迅速

的完成增量同步！

需要限定同步哪些数据库，有3个思路：

1）在执行grant授权的时候就限定数据库；

2）在主服务器上限定binlog_do_db = 数据库名；

3）主服务器上不限定数据库，在从服务器上限定replicate-do-db = 数据库名；

如果想实现主-从（主）-从这样的链条式结构，需要设置：

log-slave-updates 只有加上它，从前一台机器上同步过来的数据才能同步到下一台机器。

当然，二进制日志也是必须开启的：

log-bin=/opt/mysql/binlogs/bin-log

log-bin-index=/opt/mysql/binlogs/bin-log.index

还可以设置一个log保存周期：

expire_logs_days=14

下面记录下mysql主从／主主同步环境的实施过程

环境描述

mysql

centos 7.4

master：192.168.0.103

slave： 192.168.0.104

注意下面几点：

1）要保证同步服务期间之间的网络联通。即能相互ping通，能使用对方授权信息连接到对方数据库（防火墙开放3306端口）。

2）关闭selinux。

3）同步前，双方数据库中需要同步的数据要保持一致。这样，同步环境实现后，再次更新的数据就会如期同步了。

主从复制实现过程

(1)设置master数据库的my.cnf文件（my.cnf 查找顺序 /etc/my.cnf ---> $basedir/my.cnf,在[mysqld]配置区域添加下面内容）

[root@master ~]# vim /etc/my.cnf
    ..........
   [mysqld] 
server-id=1        #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin    #开启bin-log，并指定文件目录和文件名前缀
binlog-do-db=liting　  #需要同步liting数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）。
binlog-ignore-db=mysql  #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1      ＃确保binlog日志写入后与硬盘同步
binlog_checksum = none  ＃跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed   ＃bin-log日志文件格式，设置为MIXED可以防止主键重复。

温馨提示：在主服务器上最重要的二进制日志设置是sync_binlog，这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上，即使服务器崩溃也会把事件写入日志中。
sync_binlog这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于”sync_binlog”参数的各种设置的说明如下：
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

(2)导出master数据库多余slave数据库中的数据，然后导入到slave数据库中。保证双方在同步环境实现前的数据一致。[新建环境可忽略次步骤]

导出数据库之前先锁定数据库

mysql> flush tables with read lock;    #数据库只读锁定命令，防止导出数据库的时候有数据写入。unlock tables命令解除锁定
    
导出master数据库中需要同步的库(master数据库的root用户登陆密码：123456)
[root@master ~]#mysqldump -uroot liting -p123456 >/opt/liting.sql
[root@master ~]#rsync  -e "ssh -p22" -avpgolr /opt/liting.sql 192.168.0.104:/opt/   ＃将导出的sql文件上传到slave机器上

(3)在master上设置数据同步权限

mysql> grant replication slave,replication client on *.* to repl@'192.168.0.104' identified by "repl123";  #只允许192.168.0.104使用repl，且密码为"repl123"连接主库做数据同步
 Query OK, 0 rows affected (0.02 sec)                                    #若要所有网段则设置repl@'%' ；部分网段：repl@'192.168.0.%'
 mysql> flush privileges;
 Query OK, 0 rows affected (0.00 sec)

温馨提示：
权限查看方式

1 2	mysql> show grants; mysql> show grants for repl@'192.168.0.104';

(4)查看主服务器master状态(注意File与Position项，从服务器需要这两项参数)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 120    |  liting   |     mysql   |          |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

下面是slave数据库上的操作

(1)设置slave数据库的my.cnf配置文件

root@master ~]# vim /etc/my.cnf
.......
[mysqld]
server-id=2   #设置从服务器id，必须于主服务器不同
log-bin=mysql-bin   #启动MySQ二进制日志系统
replicate-do-db=liting  #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。
replicate-ignore-db=mysql  #不同步test数据库
slave-skip-errors = all   #跳过所有的错误，继续执行复制操作

温馨提示：
当只针对某些库的某张表进行同步时，如下，只同步liting库的haha表和test库的heihei表：

replicate-do-db = liting
replicate-wild-do-table = liting.haha       //当只同步几个或少数表时，可以这样设置。注意这要跟上面的库指定配合使用；
replicate-do-db = test
replicate-wild-do-table = test.heihei      //如果同步的库的表比较多时，就不能这样一一指定了，就把这个选项配置去掉，直接根据指定的库进行同步。

(2)在slave数据库中导入从master传过来的数据。

mysql> CREATE DATABASE liting CHARACTER SET utf8 COLLATE utf8_general_ci;   #先创建一个liting空库，否则下面导入数据时会报错说此库不存在。
mysql> use liting;
mysql> source /opt/liting.sql;   ＃导入master中多余的数据。
.......

(3）配置主从同步指令

mysql> stop slave;   ＃执行同步前，要先关闭slave
mysql> change master to master_host='192.168.0.103',master_user='repl',master_password='repl123',master_log_file='mysql-bin.000007',master_log_pos=120;
    
mysql> start slave;
mysql> show slave status \G;
.......
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 192.168.0.103
         Master_User: repl
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mysql-bin.000007
     Read_Master_Log_Pos: 120
       Relay_Log_File: mysql-relay-bin.000002
       Relay_Log_Pos: 279
   Relay_Master_Log_File: mysql-bin.000007
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
      Replicate_Do_DB: liting
    Replicate_Ignore_DB: mysql
     .............
   Seconds_Behind_Master: 0

如上，当IO和SQL线程的状态均为Yes，则表示主从已实现同步了！

下面测试下Mysql主从同步的效果
在master主数据库上写入新数据

mysql> use liting;    
mysql>create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into huanqiu.haha values(100,"anhui");
Query OK, 1 row affected (0.00 sec)

然后在slave数据库上查看，发现master上新写入的数据已经同步过来了

mysql> select * from liting.haha;
+-----+-----------+
| id   | name    |
+-----+-----------+
| 100 | anhui   |
+-----+-----------+
1 rows in set (0.00 sec)

至此，主从同步环境已经实现！

注意：
Mysql主从环境部署一段时间后，发现主从不同步时，如何进行数据同步至一致？
有以下两种做法：
1）参考：mysql主从同步(2)-问题梳理中的第（4）步的第二种方法
2）参考：mysql主从同步(3)-percona-toolkit工具（数据一致性监测、延迟监控）使用梳理

主主复制实现过程

根据上面的主从环境部署，master和slave已经实现同步，即在master上写入新数据，自动同步到slave。而从库只能读不能写，一旦从库有写入数据，就会造成主从数据不一致！
下面就说下Mysql主主复制环境，在slave上更新数据时，master也能自动同步过来。

温馨提示：

在做主主同步前，提醒下需要特别注意的一个问题：

主主复制和主从复制有一些区别，因为多主中都可以对服务器有写权限，所以设计到自增长重复问题，例如：

出现的问题（多主自增长ID重复）

1）首先在A和B两个库上创建test表结构;

2）停掉A，在B上对数据表test(存在自增长属性的ID字段)执行插入操作，返回插入ID为1;

3）然后停掉B，在A上对数据表test(存在自增长属性的ID字段)执行插入操作，返回的插入ID也是1;

4）然后同时启动A,B，就会出现主键ID重复

解决方法：

只要保证两台服务器上的数据库里插入的自增长数据不同就可以了
如：A插入奇数ID，B插入偶数ID，当然如果服务器多的话，还可以自定义算法，只要不同就可以了
在下面例子中，在两台主主服务器上加入参数，以实现奇偶插入！
记住:在做主主同步时需要设置自增长的两个相关配置，如下：

1
2

auto_increment_offset     表示自增长字段从那个数开始，取值范围是1 .. 65535。这个就是序号。如果有n台mysql机器，则从第一台开始分为设1，2...n
auto_increment_increment    表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535。如果有n台mysql机器，这个值就设置为n。

在主主同步配置时，需要将两台服务器的：

1 2	auto_increment_increment 增长量都配置为2 auto_increment_offset 分别配置为1和2。这是序号，第一台从1开始，第二台就是2，以此类推.....

这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。（针对的是有自增长属性的字段）

主主同步实现操作过程

1）在master上的my.cnf配置：

[root@master ~]# vim /etc/my.cnf
server-id = 1        
log-bin = mysql-bin  
binlog-ignore-db = mysql,information_schema
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2    
auto-increment-offset = 1   
slave-skip-errors = all     
[root@master ~]# /etc/init.d/mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!

数据同步授权（iptables防火墙开启3306端口，要确保对方机器能使用下面权限连接到本机mysql）

1 2	mysql> grant replication slave,replication client on . to repl@'192.168.0.104' identified by "repl123"; mysql> flush privileges;

最好将库锁住，仅仅允许读，以保证数据一致性；待主主同步环境部署后再解锁；锁住后，就不能往表里写数据，但是重启mysql服务后就会自动解锁！

mysql> FLUSH TABLES WITH READ LOCK;    //注意该参数设置后，如果自己同步对方数据，同步前一定要记得先解锁！
Query OK, 0 rows affected (0.00 sec)
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |   158 |        |         |          |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2）slave数据库上

[root@slave ~]# vim /etc/my.cnf
server-id = 2       
log-bin = mysql-bin  
binlog-ignore-db = mysql,information_schema
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2    
auto-increment-offset = 2   
slave-skip-errors = all
 
[root@slave ~]# /etc/init.d/mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!

数据同步授权（iptables防火墙开启3306端口，要确保对方机器能使用下面权限连接到本机mysql）
同理，slave也要授权给master机器远程同步数据的权限

mysql> grant replication slave ,replication client on *.* to repl@'192.168.0.103' identified by "repl123";  
mysql> flush privileges;
 
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |   256 |       |          |          |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3）执行主张同步操作

先在slave数据库上做同步master的设置。（确保slave上要同步的数据，提前在master上存在。最好双方数据保持一致）

mysql> unlock tables;     //先解锁，将对方数据同步到自己的数据库中
mysql> slave stop；
mysql> change master to master_host='192.168.0.103',master_user='repl',master_password='repl123',master_log_file='master-bin.000001',master_log_pos=158;
 
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.0.103
              Master_User: repl
              Master_Port: 3306
             Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 158
           nelay_Log_File: mysql-relay-bin.000003
             Relay_Log_Pos: 750
        Relay_Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ..................

这样就实现了slave－>master的同步环境。

再在master数据库上做同步slave的设置。（确保slave上要同步的数据，提前在master上存在。最好双方数据保持一致）

mysql> unlock tables;
mysql> slave stop；
mysql> change master to master_host='192.168.0.104',master_user='repl',master_password='repl123',master_log_file='master-bin.000001',master_log_pos=256;
 
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
             Master_Host: 192.168.0.103
             Master_User: repl
             Master_Port: 3306
            Connect_Retry: 60
           Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 256
           Relay_Log_File: mysql-relay-bin.000003
           Relay_Log_Pos: 750
        Relay_Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ..................

这样就实现了master－>slave的同步环境。至此，主主双向同步环境已经实现！

最后测试下Mysql主主同步的效果

在master上写入新数据

mysql> select * from liting.haha;
+-----+-----------+
| id   | name    |
+-----+-----------+
| 100 | anhui   |
+-----+-----------+
1 rows in set (0.00 sec)
 
mysql> insert into huanqiu.haha values(10,"beijing");

在slave数据库中查看，发现master新写入的数据已经同步过来了

mysql> select * from liting.haha;
+-----+------------+
| id  | name    |
+-----+------------+
|  10| beijing  |
| 100 | anhui   |
+-----+------------+
2 rows in set (0.00 sec)

在slave上删除数据

1	mysql> delete from liting.haha where id=100;

在master数据库中查看

mysql> select * from liting.haha;
+-----+------------+
| id  | name    |
+-----+------------+
|  10 | beijing  |
+-----+------------+
3 rows in set (0.00 sec)

以上，主主同步实现

binlog，redo log，undo log区别

binlog是MySQL Server层记录的日志， redo log是InnoDB存储引擎层的日志。两者都是记录了某些操作的日志(不是所有)自然有些重复（但两者记录的格式不同）。
选择binlog日志作为replication我想主要原因是MySQL的特点就是支持多存储引擎，为了兼容绝大部分引擎来支持复制这个特性，那么自然要采用MySQL Server自己记录的日志而不是仅仅针对InnoDB的redo log，因为如果采用了InnoDB redo log复制，那么其他引擎也想复制，此时改怎么办呢？对吧

binlog属于逻辑日志，是逻辑操作。innodb redo属于物理日志，是物理变更。
逻辑日志有个缺点是难以并行，而物理日志可以比较好的并行操作，所以redo复制还是有优势的，也许5.7能搞出来。

binlog

binlog日志用于记录所有更新且提交了数据或者已经潜在更新提交了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。

binlog作用

1.恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。
2.在主复制服务器上记录所有将发送给从服务器的语句。

binlog 主要参数

log_bin
设置此参数表示启用binlog功能，并指定路径名称

innodb_flush_log_at_trx_commit = N：

N=0 – 每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；

N=1 – 每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上；

N=2 – 每事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度；

sync_binlog = N：

N>0 — 每向二进制日志文件写入N条SQL或N个事务后，则把二进制日志文件的数据刷新到磁盘上；

N=0 — 不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定；

推荐配置组合：

N=1,1 — 适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如充值消费系统；

N=1,0 — 适合数据安全性要求高，磁盘IO写能力支持业务不富余，允许备库落后或无复制；

N=2,0或2,m(0<m<100) — 适合数据安全性有要求，允许丢失一点事务日志，复制架构的延迟也能接受；

N=0,0 — 磁盘IO写能力有限，无复制或允许复制延迟稍微长点能接受，例如：日志性登记业务；

Undo Log

Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用UndoLog来实现多版本并发控制(简称：MVCC)。

事务的原子性(Atomicity)

- 事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发了错误，要回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过。

原理

Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为UndoLo）。
然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用UndoLog中的备份将数据恢复到事务开始之前的状态。
除了可以保证事务的原子性，Undo Log也可以用来辅助完成事务的持久化。

事务的持久性(Durability)

事务一旦完成，该事务对数据库所做的所有修改都会持久的保存到数据库中。为了保证持久性，数据库系统会将修改后的数据完全的记录到持久的存储上。

用Undo Log

实现原子性和持久化的事务的简化过程

假设有A、B两个数据，值分别为1,2。
A.事务开始.

B.记录A=1到undolog.

C.修改A=3.

D.记录B=2到undolog.

E.修改B=4.

F.将undolog写到磁盘。

G.将数据写到磁盘。

H.事务提交

这里有一个隐含的前提条件：‘数据都是先读到内存中，然后修改内存中的数据，最后将数据写回磁盘’。

之所以能同时保证原子性和持久化，是因为以下特点：

A.更新数据前记录Undo log。

B.为了保证持久性，必须将数据在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。

C.Undo log
必须先于数据持久化到磁盘。如果在G,H之间系统崩溃，undo log是完整的，可以用来回滚事务。

D.如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。

缺陷：每个事务提交前将数据和Undo Log写入磁盘，这样会导致大量的磁盘IO，因此性能很低。
如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即

Redo log

记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，
但是RedoLog已经持久化。系统可以根据RedoLog的内容，将所有数据恢复到最新的状态。

-Undo+Redo
事务的简化过程

假设有A、B两个数据，值分别为1,2.

A.事务开始.

B.记录A=1到undolog.

C.修改A=3.

D.记录A=3到redolog.

E.记录B=2到undolog.

F.修改B=4.

G.记录B=4到redolog.

H.将redolog写入磁盘。

I.事务提交

-Undo+Redo
事务的特点

A.为了保证持久性，必须在事务提交前将
RedoLog持久化。

B.数据不需要在事务提交前写入磁盘，而是缓存在内存中。

C.RedoLog保证事务的持久性。

D.UndoLog保证事务的原子性。

E.有一个隐含的特点，数据必须要晚于redolog写入持久存

mysql有哪些索引类型？有哪些存储引擎？有什么区别

索引类型
- FULLTEXT：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率较低的问题。
- HASH：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
- BTREE：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
- RTREE：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。
索引种类：
- 普通索引：仅加速查询。
- 唯一索引：加速查询 + 列值唯一（可以有null）
- 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
- 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
- 全文索引：对文本的内容进行分词，进行搜索
存储引擎
- InnoDB
  - InnoDB 也采用 B+Tree这种数据结构来实现 B-Tree索引。而很大的区别在于，InnoDB 存储引擎采用“聚集索引”的数据存储方式实现B-Tree索引，所谓“聚集”，就是指数据行和相邻的键值紧凑地存储在一起，注意 InnoDB 只能聚集一个叶子页（16K）的记录（即聚集索引满足一定的范围的记录），因此包含相邻键值的记录可能会相距甚远。
  - 当InnoDB做全表扫描时并不高效，因为 InnoDB 实际上并没有顺序读取,在大多情况下是在随机读取。做全表扫描时,InnoDB 会按主键顺序扫描页面和行。这应用于所有的InnoDB 表，包括碎片化的表。如果主键页表没有碎片（存储主键和行的页表),全表扫描是相当快，因为读取顺序接近物理存储顺序。但是当主键页有碎片时，该扫描就会变得十分缓慢
  - 遵循ACID原则(atomicity原子性，consistency一致性，isolation隔离性，durability持久性)，具有事务特性的能力：commit，rollback，crash-recovery。
    - 仅InnoDB和NDB(Network DB clustered database engine)支持事务和MVCC
  - 行级锁和Oracle风格的读一致性，提高多用户下的并发度和性能，提供行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”。
    - 只有通过索引条件检索数据，InnoDB才使用行级锁，否则仍然使用表锁
    - 读一致性：query时使用snapshot快照，允许其他事务进行修改，之后再根据undo log调整数据
    - 默认的隔离级别是可重复读，即同一个事务中多次读取，数据相同
  - 使用主键优化查询，主键索引是聚集索引(Clustered index，仅InnoDB支持)，使查询主键时的I/O最小化
    - 聚集索引是指整个表是按照这个索引来组织的，物理存储顺序与索引顺序相同，所以聚集索引字段的修改需要很大开销
    - InnoDB聚集索引的实现方式，同时也体现了一张 innoDB表的结构，可以看到，InnoDB 中，主键索引和数据是一体的，没有分开。
  - 支持外码约束
  - 崩溃后能很好地恢复
    - 未完成的事务将根据redo log的数据重做
    - 已提交但未写入的修改，将从doublewrite buffer重做
    - 系统闲时会purge buffer
  - 维护一个内存中的buffer pool缓冲池，数据被访问时，表和索引数据会被缓存
  - 对增删改的change buffering策略，如果被修改数据的页不在缓冲池中，则这个修改可以存在change buffer中，等相应页被放进缓冲池(发生对该页的访问)时，再写入修改，称为merge
  - adaptive hash index，经常被访问的页会自动在内存建立一个哈希索引，适于=和IN的查询。buffer pool中会预留这种索引需要的内存空间。建立在已有的B树索引基础上，哈希索引可以是部分的，B树索引不需要全部缓存在缓冲池中
  - 使用checksum校验和机制检测内存或硬盘的损坏
  - InnoDB是为处理巨大数据量的最大性能设计
  - 可以在一个查询中join混用InnoDB引擎的表和其他引擎的表
- MyISAM
  - 适用场景：read-only or read-mostly workloads in Web and data warehousing configurations(查询效率很高，适合大量读操作的场景)
  - 每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。MyISAM索引文件【.MYI (MYIndex)】和数据文件【.MYD (MYData)】是分离的，索引文件仅保存记录所在页的指针（物理位置），通过这些地址来读取页，进而读取被索引的行
  - MyISAM 默认会把索引读入内存，直接在内存中操作
  - Innodb强调多功能性，支持的拓展功能比较多，myisam主要侧重于性能
  - 将创建3个文件，一个.frm文件，一个.MYD(MYData)文件存数据，一个.MYI(MYIndex)文件存索引
  - 数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
  - 所有数据值都按小字节(low byte first)存储，因此独立于操作系统(可移植性)。但没有明显降低速度，只是需要多处理一下对齐问题，况且获取列值所花的时间不是最主要的
  - 所有数字键都按大字节(high byte first)存储，利于压缩
  - BLOB和TEXT列可以创建索引
  - 每一个character列可以使用不同的字符编码
  - 会保存表的具体行数
  - 使用B树索引，string索引会被压缩，当string是索引第一项时还会压缩前缀
  - 支持真正的变长字段varchar
  - 支持并发的insert
- 区别
  - InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
  - InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
  - InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
  - Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；
- 如何选择
  - 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
  - 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB。
  - 系统奔溃后，MyISAM恢复起来更困难，能否接受；
  - MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。
- ARCHIVE
  - 适用场景：作为仓库，存储大量的独立的作为历史记录的数据(插入速度快但查询支持较差）
  - 不支持索引
  - 没有存储大小限制(InnoDB是64TB)
  - 能很好地压缩数据
  - 使用行级锁
  - 支持INSERT, REPLACE, SELECT, 不支持 DELETE, UPDATE
  - 使用zlib 无损数据压缩。数据insert后即被压缩，放在一个压缩缓冲区中，select操作会导致清空缓冲区，此时数据被真正存储。支持批处理insert。
  - 行会根据需要解压，不设缓冲。select会导致全表扫描。select是读一致性的。大量查询during insertion会影响压缩。使用REPAIR TABLE或OPTIMIZE TABLE能获取更好的压缩。
- BLACKHOLE
  - 适用场景:转发器（会保存SQL语句的日志，并且复制给slave servers）
  - 过滤器（设置使用黑洞引擎的“dummy” slave进程，依据一定规则将master的日志进行过滤并在BLACKHOLE表写一个新的日志，再复制给slaves，这样只会导致很少的开销）
  - 像黑洞一样接受数据但不存储
  - 创建table A会生成一个A.frm表文件，没有其他文件
  - 支持所有索引
  - 会保存SQL语句的日志，并且复制给slave servers，适合做转发器或过滤器
  - 会导致错误，因为不论log文件是row-based还是statement-based，blackhole表不会存储自增列的数据，所以在slaves上insert时会出现重复的主码错误
  - 使用row-based replication时，如果slaves的表的字段比master少，那么过滤机制其实是在slaves上。如果缺失字段是私密的，不能给slaves获取的；或是有很多slaves，需要在发送数据前就把数据过滤掉以减少网络负载，就不适合这种方式。BLACKHOLE表就能实现在master上进行过滤。
- MRG_MYISAM
  - 适用场景：Good for VLDB environments such as data warehousing
  - 要求多个Mylsam表要有相同的列信息(包括顺序)和索引信息(包括索引的order)
  - 这些信息不同不会影响表合并
    - 列名和索引名
    - 所有的备注comment
    - 表的选项，例如 AVG_ROW_LENGTH, MAX_ROWS, or PACK_KEYS
  - 创建merge表时会创建2个文件，一个是存数据的.frm文件，一个是.mrg文件(存储哪些表应当merge起来使用)
  - merge表中的表可以存于不同的数据库中
  - 支持merge表的增删改查，前提是必须拥有处理其中所有表的权限
  - drop table只是删除了merge表，实际存储数据的表不会被删除
  - 建表需要指定UNION=(list-of-tables)表明使用哪些表，以及INSERT_METHOD=LAST/FIRST表明在哪一个表中插入数据，否则无法执行insert操作
  - merge表没有主键，因为不能强制实行唯一索引
- FEDERATED
  - 适用场景：Very good for distributed or data mart environments
  - 数据不存储在本地，而是在远程数据库，本地访问时会pull远程数据库的数据
  - 远程数据库的表可以是任何存储引擎的表
  - 本地表和远程表应有相同的定义
  - 本地用.frm文件存储表定义，并且包含一个指向远程数据库的连接字符串
  - 本地执行操作时，会发送给远程去执行，使用MySQL client API
  - 远程表可以是一个FEDERATED表，但注意不要造成一个循环
  - FEDERATED表不支持一般意义上的索引，要远程表上有索引才有效
  - 如果一个查询语句不能使用远程表的索引，会导致全表扫描，本地数据库会获取全表数据（存在本地内存中，如果数据量过大会引起交换和挂起），再在本地进行过滤
  - 不支持alter table或drop table，执行drop table只会删除本地FEDERATED表
  - 不支持分区
  - 如果远程表改变，本地表无法获知
- PERFORMANCE_SCHEMA
  - 关注收集mysql server运行中的性能数据，会监视server的所有events
  - performance_schema数据库名及其表名都是小写的，查询时要用小写
  - 很多表都是只读的，对数据库所有表的GRANT ALL授权是不允许的
  - 数据库中表的更改不会写在日志中
  - 是完全in-memory的，不占用磁盘空间，mysql服务启动时表会被重新填充，关闭服务时便丢弃
  - 数据收集的实现是在源码中添加”监控点”(instrumentation)，没有用额外的线程(不像”复制”或”事件调度”)
  - 用户不能创建存储该类型的表
- MEMORY
  - 适用场景：存储临时、不重要的数据，例如作为缓存，适合大量读的情形 (limited updates)
  - 不支持变长的数据类型variable-length data types (including BLOB and TEXT)
  - 不支持外码约束
  - 不支持压缩
  - 不支持MVCC
  - 支持哈希索引和B树索引，不支持全文索引和T树索引
  - mysql服务关闭或重启，数据会消失(表还在)
  - 数据量不能超过内存大小
  - 性能限制
    - 单线程执行
    - 表更新用表级锁(高并发读写情形下，表级锁严重降低性能，还不如InnoDB快)
  - 内置的临时表(也在内存中)太大时会自动转成磁盘存储，但用户自创的内存表永远不会转化
  - 可以从persistent data source装载数据到内存表
  - 被删除的row会放进一个链表(不会回收内存)，等插入新数据时拿出来复用，只有整个表被删除后才会回收内存。采用定长的行存储，即使是varchar也是定长存储的。
  - 默认使用哈希索引，并且允许非唯一的哈希索引(但如果字段含大量重复值，性能会很低，这种情况最好用B树索引)，被索引字段可以有NULL。
- CSV
  - 创建一个csv表，除了.frm文件外，还创建一个.csv文件用于存储数据，还有一个.csm文件存储表状态、行数等信息，称为metafile
  - 所有字段都必须NOT NULL
  - 不支持索引、分区

对索引的理解，组合索引，索引的最佳实践

索引类型

B-Tree 索引：MySQL 中最主要的索引；
RTREE 索引：仅仅是 MyISAM，GIS；
哈希索引：MyISAM，5.6 开始的 Innodb。

BTREE 索引能做什么？

直接查看 KEY=5 的所有列；
找到 KEY > 5 的列，范围查找；
查找 5<KEY<10 之间的所有列，封闭范围查找；
不能找到 KEY 的最后一个数字是 0 的列（这个不是范围查找）。

字符串索引

字符串索引实际上也没什么不同，按照字典顺序排列，例如 ”AAAA” < “AAAB”;
like 前缀是一个特殊排序，例如 LIKE “ABC%” 意味着 “ABC[LOWEST]”<KEY<“ABC[HIGHEST]”，但是 LIKE “%ABC” 不走索引。

多列索引

按照定义的顺序从左往右进行比较，例如在 KEY(col1,col2,col3) 中，(1,2,3) < (1,3,1)；
多列索引仍然是一个 BTREE 索引，但不是每列都是一个单独的 BTREE。

MySQL 怎么使用索引

在数据查询中使用索引

用了索引 LAST_NAME
SELECT * FROM EMPLOYEES WHERE LAST_NAME=“Smith”;

用了索引 (DEPT,LAST_NAME)

1
2
3

SELECT * FROM EMPLOYEES WHERE
 LAST_NAME=“Smith” AND
 DEPT=“Accounting”

这里虽然索引字段顺序和查询的顺序颠倒，依然会走索引，不是因为最左匹配不走索引。

多列索引会变的困难，对于索引 (A,B,C)：

下面的条件会走索引:
- A>5
- A=5 AND B>6
- A=5 AND B=6 AND C=7
- A=5 AND B IN (2,3) AND C>5
- 下面的条件不走索引，因为不符合最左匹配，缺少第一列
- B > 5
- B = 6 AND C = 7

在 MySQL5.7 中使用 explain 执行了一下，发现还是会走索引的，估计 MySQL 底层做了什么优化？

下面条件会走部分索引
- A>5 AND B=2
- A=5 AND B>6 AND C=2

SQL 优化的第一原则：

MySQL 在多列索引中，一遇到（<,>,between）就会停止使用 key，然而能继续使用 key 直到 in 范围的右边。

在排序中使用索引

排序

SELECT * FROM PLAYERS ORDER BY SCORE DESC LIMIT 10

该 SQL 会使用建立在 SCORE 列上的索引；
如果排序的时候没有使用索引，将会导致非常耗时的文件排序；
在排序中经常会考虑组合索引，例如下面的 SQL 可以考虑(COUNTRY,SCORE) 索引：

SELECT * FROM PLAYERS WHERE COUNTRY=“US” ORDER BY SCORE DESC LIMIT 10
使用多列索引进行高效的排序，在排序中使用索引有很多的限制，对于 KEY(A，B）:

下面排序会使用索引：
- ORDER BY A：主列索引；
- A=5 ORDER BY B：通过第一列过滤数据，第二列进行排序；
- ORDER BY A DESC, B DESC：用相同的排序进行排序；
- A>5 ORDER BY A：主列上进行查询和排序
下面的语句不会使用索引：
- ORDER BY B ：非主列索引排序；
- A>5 ORDER BY B：第一列上使用范围，第二列进行排序；
- A IN(1,2) ORDER BY B：第一列上用 IN；
- ORDER BY A ASC, B DESC：两列的排列顺序不同。

使用索引进行排序的规则

两列的排列顺序不能不一致；
非排序的列中索引部分只能用 =，in 也不能用。

表中存在多个索引

MySQL 中可以存在多个索引：会有索引合并；
SELECT * FROM TBL WHERE A=5 AND B=6：该语句能分别使用在 A 和 B 上的索引，但是在（A,B) 上建立索引是更好的；
SELECT * FROM TBL WHERE A=5 OR B=6：该语句使用两个独立的索引，但不会使用在（A,B) 上建立的索引

前缀索引

可以在索引最左边一列上建立前缀索引：

ALTER TABLE TITLE ADD KEY(TITLE(20));
需要在 BLOB/TEXT 上建立索引；
能显著的提升效率；
不能被用作覆盖索引；
选择合适的前缀长度是一个问题。

Explain命令详解

EXPLAIN 输出格式

EXPLAIN 命令的输出内容大致如下:

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

各列的含义如下:

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type: SELECT 查询的类型.
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息

接下来我们来重点看一下比较重要的几个字段.

select_type

select_type 表示了查询的类型, 它的常用取值有:

SIMPLE, 表示此查询不包含 UNION 查询或子查询
PRIMARY, 表示此查询是最外层的查询
UNION, 表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT, UNION 的结果
SUBQUERY, 子查询中的第一个 SELECT
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

最常见的查询类别应该是 SIMPLE 了, 比如当我们的查询没有子查询, 也没有 UNION 查询时, 那么通常就是 SIMPLE 类型, 例如:

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

如果我们使用了 UNION 查询, 那么 EXPLAIN 输出的结果类似如下:

mysql> EXPLAIN (SELECT * FROM user_info  WHERE id IN (1, 2, 3))
    -> UNION
    -> (SELECT * FROM user_info WHERE id IN (3, 4, 5));
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | user_info  | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    3 |   100.00 | Using where     |
|  2 | UNION        | user_info  | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    3 |   100.00 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

table

表示查询涉及的表或衍生表

type

type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是全表扫描还是索引扫描等.

type 常用类型

type 常用的取值有:

system: 表中只有一条数据. 这个类型是特殊的 const 类型.
const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.

例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的.

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高. 例如:

mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 314
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: test.order_info.user_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询.
例如下面这个例子中, 就使用到了 ref 类型的查询:

mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: ref
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 9
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.01 sec)

range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.

例如下面的例子就是一个范围查询:

mysql> EXPLAIN SELECT *
    ->         FROM user_info
    ->         WHERE id BETWEEN 2 AND 8 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段会显示 Using index.

例如:

mysql> EXPLAIN SELECT name FROM  user_info \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: name_index
      key_len: 152
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

上面的例子中, 我们查询的 name 字段恰好是一个索引, 因此我们直接从索引中获取数据就可以满足查询的需求了, 而不需要查询表中的数据. 因此这样的情况下, type 的值是 index, 并且 Extra 的值是 Using index.

ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.
下面是一个全表扫描的例子, 可以看到, 在全表扫描时, possible_keys 和 key 字段都是 NULL, 表示没有使用到索引, 并且 rows 十分巨大, 因此整个查询效率是十分低下的.

mysql> EXPLAIN SELECT age FROM  user_info WHERE age = 20 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

type 类型的性能比较

通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

possible_keys

possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

key

此字段是 MySQL 在当前查询时所真正使用到的索引.

key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:

字符串
- char(n): n 字节长度
- varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
数值类型:
- TINYINT: 1字节
- SMALLINT: 2字节
- MEDIUMINT: 3字节
- INT: 4字节
- BIGINT: 8字节
时间类型
- DATE: 3字节
- TIMESTAMP: 4字节
- DATETIME: 8字节

字段属性: NULL 属性占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

我们来举两个简单的栗子:

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: range
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 9
          ref: NULL
         rows: 5
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

上面的例子是从表 order_info中查询指定的内容, 而我们从此表的建表语句中可以知道, 表 order_info 有一个联合索引:

1	KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

不过此查询语句 WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' 中, 因为先进行 user_id 的范围查询, 而根据最左前缀匹配原则, 当遇到范围查询时, 就停止索引的匹配, 因此实际上我们使用到的索引的字段只有 user_id, 因此在 EXPLAIN 中, 显示的 key_len 为 9. 因为 user_id 字段是 BIGINT, 占用 8 字节, 而 NULL 属性占用一个字节, 因此总共是 9 个字节. 若我们将user_id 字段改为 BIGINT(20) NOT NULL DEFAULT '0', 则 key_length 应该是8.

上面因为最左前缀匹配原则, 我们的查询仅仅使用到了联合索引的 user_id 字段, 因此效率不算高.

接下来我们来看一下下一个例子:

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: ref
possible_keys: user_product_detail_index
          key: user_product_detail_index
      key_len: 161
          ref: const,const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

这次的查询中, 我们没有使用到范围查询, key_len 的值为 161. 为什么呢? 因为我们的查询条件 WHERE user_id = 1 AND product_name = 'p1' 中, 仅仅使用到了联合索引中的前两个字段, 因此 keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161

rows

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

Using filesort
- 当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

例如下面的例子:

mysql> EXPLAIN SELECT * FROM order_info ORDER BY product_name \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: user_product_detail_index
      key_len: 253
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

我们的索引是

1	KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

但是上面的查询中根据 product_name 来排序, 因此不能使用索引进行优化, 进而会产生 Using filesort.
如果我们将排序依据改为ORDER BY user_id, product_name, 那么就不会出现 Using filesort 了. 例如:

mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: user_product_detail_index
      key_len: 253
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Using index
- “覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
Using temporary
- 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

数据库和缓存的一致性问题。先更新数据库，再更新缓存，若更新完数据库了，还没有更新缓存，此时有请求过来了，访问到了缓存中的数据，怎么办？

产生原因

主要有两种情况，会导致缓存和 DB 的一致性问题：

并发的场景下，导致读取老的 DB 数据，更新到缓存中。
缓存和 DB 的操作，不在一个事务中，可能只有一个操作成功，而另一个操作失败，导致不一致。

当然，有一点我们要注意，缓存和 DB 的一致性，我们指的更多的是最终一致性。我们使用缓存只要是提高读操作的性能，真正在写操作的业务逻辑，还是以数据库为准。例如说，我们可能缓存用户钱包的余额在缓存中，在前端查询钱包余额时，读取缓存，在使用钱包余额时，读取数据库。

更新缓存的设计模式

Cache Aside Pattern(旁路缓存)

这是最常用最常用的pattern了。其具体逻辑如下：

失效：应用程序先从cache取数据，没有得到，则从数据库中取数据，成功后，放到缓存中。
命中：应用程序从cache中取数据，取到后返回。
更新：先把数据存到数据库中，成功后，再让缓存失效。

一个是查询操作，一个是更新操作的并发，首先，没有了删除cache数据的操作了，而是先更新了数据库中的数据，此时，缓存依然有效，所以，并发的查询操作拿的是没有更新的数据，但是，更新操作马上让缓存的失效了，后续的查询操作再把数据从数据库中拉出来。而不会像文章开头的那个逻辑产生的问题，后续的查询操作一直都在取老的数据。

要么通过2PC或是Paxos协议保证一致性，要么就是拼命的降低并发时脏数据的概率，而Facebook使用了这个降低概率的玩法，因为2PC太慢，而Paxos太复杂。当然，最好还是为缓存设置上过期时间。

Read/Write Through Pattern

在上面的Cache Aside套路中，我们的应用代码需要维护两个数据存储，一个是缓存（Cache），一个是数据库（Repository）。所以，应用程序比较啰嗦。而Read/Write Through套路是把更新数据库（Repository）的操作由缓存自己代理了，所以，对于应用层来说，就简单很多了。可以理解为，应用认为后端就是一个单一的存储，而存储自己维护自己的Cache。

Read Through

Read Through 套路就是在查询操作中更新缓存，也就是说，当缓存失效的时候（过期或LRU换出），Cache Aside是由调用方负责把数据加载入缓存，而Read Through则用缓存服务自己来加载，从而对应用方是透明的。

Write Through

Write Through 套路和Read Through相仿，不过是在更新数据时发生。当有数据更新的时候，如果没有命中缓存，直接更新数据库，然后返回。如果命中了缓存，则更新缓存，然后再由Cache自己更新数据库（这是一个同步操作）

下图自来Wikipedia的Cache词条。其中的Memory你可以理解为就是我们例子里的数据库。

Write Behind Caching Pattern

Write Behind 又叫 Write Back。write back就是Linux文件系统的Page Cache的算法。

Write Back套路，一句说就是，在更新数据的时候，只更新缓存，不更新数据库，而我们的缓存会异步地批量更新数据库。

这个设计的好处就是让数据的I/O操作飞快无比（因为直接操作内存嘛），因为异步，write backg还可以合并对同一个数据的多次操作，所以性能的提高是相当可观的。

但是，其带来的问题是，数据不是强一致性的，而且可能会丢失（我们知道Unix/Linux非正常关机会导致数据丢失，就是因为这个事）。在软件设计上，我们基本上不可能做出一个没有缺陷的设计，就像算法设计中的时间换空间，空间换时间一个道理，有时候，强一致性和高性能，高可用和高性性是有冲突的。软件设计从来都是取舍Trade-Off。

另外，Write Back实现逻辑比较复杂，因为他需要track有哪数据是被更新了的，需要刷到持久层上。操作系统的write back会在仅当这个cache需要失效的时候，才会被真正持久起来，比如，内存不够了，或是进程退出了等情况，这又叫lazy write。

在wikipedia上有一张write back的流程图，基本逻辑如下：

缓存架构设计

更新缓存 VS 淘汰缓存

更新缓存：数据不但写入数据库，还会写入缓存；优点：缓存不会增加一次miss，命中率高

淘汰缓存：数据只会写入数据库，不会写入缓存，只会把数据淘汰掉；优点：简单

这两者的选择主要取决于“更新缓存的复杂度”。

例如，上述场景，只是简单的把余额money设置成一个值，那么：

（1）淘汰缓存的操作为deleteCache(uid)

（2）更新缓存的操作为setCache(uid, money)

更新缓存的代价很小，此时我们应该更倾向于更新缓存，以保证更高的缓存命中率

如果余额是通过很复杂的数据计算得出来的，例如业务上除了账户表account，还有商品表product，折扣表discount

account(uid, money)

product(pid, type, price, pinfo)

discount(type, zhekou)

业务场景是用户买了一个商品product，这个商品的价格是price，这个商品从属于type类商品，type类商品在做促销活动要打折扣zhekou，购买了商品过后，这个余额的计算就复杂了，需要：

（1）先把商品的品类，价格取出来：SELECT type, price FROM product WHERE pid=XXX

（2）再把这个品类的折扣取出来：SELECT zhekou FROM discount WHERE type=XXX

（3）再把原有余额从缓存中查询出来money = getCache(uid)

（4）再把新的余额写入到缓存中去setCache(uid, money-price*zhekou)

更新缓存的代价很大，此时我们应该更倾向于淘汰缓存。

总之，淘汰缓存操作简单，并且带来的副作用只是增加了一次cache miss，建议作为通用的处理方式。

先操作数据库 vs 先操作缓存

当写操作发生时，假设淘汰缓存作为对缓存通用的处理方式，又面临两种抉择：

（1）先写数据库，再淘汰缓存

（2）先淘汰缓存，再写数据库

对于一个不能保证事务性的操作，一定涉及“哪个任务先做，哪个任务后做”的问题，解决这个问题的方向是：如果出现不一致，谁先做对业务的影响较小，就谁先执行。

由于写数据库与淘汰缓存不能保证原子性，谁先谁后同样要遵循上述原则。

假设先写数据库，再淘汰缓存：第一步写数据库操作成功，第二步淘汰缓存失败，则会出现DB中是新数据，Cache中是旧数据，数据不一致。

假设先淘汰缓存，再写数据库：第一步淘汰缓存成功，第二步写数据库失败，则只会引发一次Cache miss。

结论：数据和缓存的操作时序：先淘汰缓存，再写数据库。

缓存架构优化

上述缓存架构有一个缺点：业务方需要同时关注缓存与DB，主要有两种优化方案：

一种方案是服务化：加入一个服务层，向上游提供帅气的数据访问接口，向上游屏蔽底层数据存储的细节，这样业务线不需要关注数据是来自于cache还是DB。

另一种方案是异步缓存更新：业务线所有的写操作都走数据库，所有的读操作都总缓存，由一个异步的工具来做数据库与缓存之间数据的同步，具体细节是：

（1）要有一个init cache的过程，将需要缓存的数据全量写入cache

（2）如果DB有写操作，异步更新程序读取binlog，更新cache

在（1）和（2）的合作下，cache中有全部的数据，这样：

（a）业务线读cache，一定能够hit（很短的时间内，可能有脏数据），无需关注数据库

（b）业务线写DB，cache中能得到异步更新，无需关注缓存

这样将大大简化业务线的调用逻辑，存在的缺点是，如果缓存的数据业务逻辑比较复杂，async-update异步更新的逻辑可能也会比较复杂。

结论

（1）淘汰缓存是一种通用的缓存处理方式

（2）先淘汰缓存，再写数据库

（3）服务化是向业务方屏蔽底层数据库与缓存复杂性的一种通用方式

缓存和DB一致性的解决方案

先淘汰缓存，再写数据库

因为先淘汰缓存，所以数据的最终一致性是可以得到有效的保证的。因为先淘汰缓存，即使写数据库发生异常，也就是下次缓存读取时，多读取一次数据库。

但是，这种方案会存在缓存和 DB 的数据会不一致的情况，参照《缓存与数据库一致性优化》所说。

我们需要解决缓存并行写，实现串行写。比较简单的方式，引入分布式锁。

在写请求时，先淘汰缓存之前，获取该分布式锁。
在读请求时，发现缓存不存在时，先获取分布式锁。

这样，缓存的并行写就成功的变成串行写落。写请求时，是否主动更新缓存，根据自己业务的需要，是否有，都没问题。

先写数据库，再更新缓存

按照“先写数据库，再更新缓存”，我们要保证 DB 和缓存的操作，能够在“同一个事务”中，从而实现最终一致性。

基于定时任务来实现

首先，写入数据库。
然后，在写入数据库所在的事务中，插入一条记录到任务表。该记录会存储需要更新的缓存 KEY 和 VALUE 。
【异步】最后，定时任务每秒扫描任务表，更新到缓存中，之后删除该记录。

基于消息队列来实现

首先，写入数据库。
然后，发送带有缓存 KEY 和 VALUE 的事务消息。此时，需要有支持事务消息特性的消息队列，或者我们自己封装消息队列，支持事务消息。
【异步】最后，消费者消费该消息，更新到缓存中。

这两种方式，可以进一步优化，可以先尝试更新缓存，如果失败，则插入任务表，或者事务消息。

另外，极端情况下，如果并发写执行时，先更新成功 DB 的，结果后更新缓存：

理论来说，希望的更新缓存顺序是，线程 1 快于线程 2 ，但是实际线程1 晚于线程 2 ，导致数据不一致。

图中一直是基于定时任务或消息队列来实现异步更新缓存，如果网络抖动，导致【插入任务表，或者事务消息】的顺序不一致。

那么怎么解决呢？需要做如下三件事情：

1、在缓存值中，拼接上数据版本号或者时间戳。例如说：value = {value: 原值, version: xxx} 。

2、在任务表的记录，或者事务消息中，增加上数据版本号或者时间戳的字段。

3、在定时任务或消息队列执行更新缓存时，先读取缓存，对比版本号或时间戳，大于才进行更新。当然，此处也会有并发问题，所以还是得引入分布式锁或 CAS 操作。

关于 Redis 分布式锁，可以看看《精尽 Redis 面试题》的「如何使用 Redis 实现分布式锁？」问题。

关于 Redis CAS 操作，可以看看《精尽 Redis 面试题》的「什么是 Redis 事务？」问题。

基于数据库的 binlog 日志

重客户端

写入缓存：

应用同时更新数据库和缓存
如果数据库更新成功，则开始更新缓存，否则如果数据库更新失败，则整个更新过程失败。
判断更新缓存是否成功，如果成功则返回
如果缓存没有更新成功，则将数据发到MQ中
应用监控MQ通道，收到消息后继续更新Redis。

问题点：如果更新Redis失败，同时在将数据发到MQ之前的时间，应用重启了，这时候MQ就没有需要更新的数据，如果Redis对所有数据没有设置过期时间，同时在读多写少的场景下，只能通过人工介入来更新缓存。

读缓存：

如何来解决这个问题？那么在写入Redis数据的时候，在数据中增加一个时间戳插入到Redis中。在从Redis中读取数据的时候，首先要判断一下当前时间有没有过期，如果没有则从缓存中读取，如果过期了则从数据库中读取最新数据覆盖当前Redis数据并更新时间戳。具体过程如下图所示：

客户端数据库与缓存解耦

上述方案对于应用的研发人员来讲比较重，需要研发人员同时考虑数据库和Redis是否成功来做不同方案，如何让研发人员只关注数据库层面，而不用关心缓存层呢？请看下图：

应用直接写数据到数据库中。
数据库更新binlog日志。
利用Canal中间件读取binlog日志。
Canal借助于限流组件按频率将数据发到MQ中。
应用监控MQ通道，将MQ的数据更新到Redis缓存中。

可以看到这种方案对研发人员来说比较轻量，不用关心缓存层面，而且这个方案虽然比较重，但是却容易形成统一的解决方案。

PS：下面这两种比较实用

“先淘汰缓存，再写数据库”的方案，并且无需引入分布式锁。

“先写数据库，再更新缓存”的方案，并且无需引入定时任务或者消息队列。

使用缓存过程中，经常会遇到缓存数据的不一致性和脏读现象。一般情况下，采取缓存双淘汰机制，在更新数据库的前淘汰缓存。此外，设定超时时间，例如三十分钟。

极端场景下，即使有脏数据进入缓存，这个脏数据也最存在一段时间后自动销毁。

主从DB与cache一致性优化

聚簇索引/非聚簇索引，MySQL索引底层实现，为什么不用B-Tree，为什么不用hash，叶子结点存放的是数据还是指向数据的内存地址，使用索引需要注意的几个地方？

二叉搜索树

1.所有非叶子结点至多拥有两个儿子（Left和Right）；

2.所有结点存储一个关键字；

3.非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；

如：

二叉搜索树的搜索，从根结点开始，如果查询的关键字与结点的关键字相等，那么就命中；

否则，如果查询关键字比结点关键字小，就进入左儿子；如果比结点关键字大，就进入

右儿子；如果左儿子或右儿子的指针为空，则报告找不到相应的关键字；

如果二叉搜索树的所有非叶子结点的左右子树的结点数目均保持差不多（平衡），那么二叉搜索树

的搜索性能逼近二分查找；但它比连续内存空间的二分查找的优点是，改变二叉搜索树结构

（插入与删除结点）不需要移动大段的内存数据，甚至通常是常数开销；

如：

但二叉搜索树在经过多次插入与删除后，有可能导致不同的结构：

右边也是一个二叉搜索树，但它的搜索性能已经是线性的了；同样的关键字集合有可能导致不同的

树结构索引；所以，使用二叉搜索树还要考虑尽可能让二叉搜索树保持左图的结构，和避免右图的结构，也就是所谓的“平衡”问题；

实际使用的二叉搜索树都是在原二叉搜索树的基础上加上平衡算法，即“平衡二叉树”；如何保持二叉搜索树

结点分布均匀的平衡算法是平衡二叉树的关键；平衡算法是一种在二叉搜索树中插入和删除结点的策略；

B-树

是一种多路搜索树（并不是二叉的）：

1.定义任意非叶子结点最多只有M个儿子；且M>2；

2.根结点的儿子数为[2, M]；

3.除根结点以外的非叶子结点的儿子数为[M/2, M]；

4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）

5.非叶子结点的关键字个数=指向儿子的指针个数-1；

6.非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

7.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；

8.所有叶子结点位于同一层；

如：（M=3）

B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果

命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为

空，或已经是叶子结点；

B-树的特性：

1.关键字集合分布在整颗树中；

2.任何一个关键字出现且只出现在一个结点中；

3.搜索有可能在非叶子结点结束；

4.其搜索性能等价于在关键字全集内做一次二分查找；

5.自动层次控制；

由于限制了除根结点以外的非叶子结点，至少含有M/2个儿子，确保了结点的至少

利用率，其最底搜索性能为：

其中，M为设定的非叶子结点最多子树个数，N为关键字总数；

所以B-树的性能总是等价于二分查找（与M值无关），也就没有B树平衡的问题；

由于M/2的限制，在插入结点时，如果结点已满，需要将结点分裂为两个各占M/2的结点；删除结点时，需将两个不足M/2的兄弟结点合并；

B+树

B+树是B-树的变体，也是一种多路搜索树：

1.其定义基本与B-树同，除了：

2.非叶子结点的子树指针与关键字个数相同；

3.非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树

（B-树是开区间）；

5.为所有叶子结点增加一个链指针；

6.所有关键字都在叶子结点出现；

B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在

非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；

B+的特性：

1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好

是有序的；

2.不可能在非叶子结点命中；

3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储

（关键字）数据的数据层；

4.更适合文件索引系统；

B*树

是B+树的变体，在B+树的非根和非叶子结点再增加指向兄弟的指针；

B*树定义了非叶子结点关键字个数至少为(2/3)*M，即块的最低使用率为2/3

（代替B+树的1/2）；

B+树的分裂：当一个结点满时，分配一个新的结点，并将原结点中1/2的数据

复制到新结点，最后在父结点中增加新结点的指针；B+树的分裂只影响原结点和父

结点，而不会影响兄弟结点，所以它不需要指向兄弟的指针；

B*树的分裂：当一个结点满时，如果它的下一个兄弟结点未满，那么将一部分

数据移到兄弟结点中，再在原结点插入关键字，最后修改父结点中兄弟结点的关键字

（因为兄弟结点的关键字范围改变了）；如果兄弟也满了，则在原结点与兄弟结点之

间增加新结点，并各复制1/3的数据到新结点，最后在父结点增加新结点的指针；

所以，B*树分配新结点的概率比B+树要低，空间使用率更高；

小结

B树：二叉树，每个结点只存储一个关键字，等于则命中，小于走左结点，大于走右结点；

B-树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；

B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；

B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；

聚簇索引

所谓聚簇索引，就是指主索引文件和数据文件为同一份文件，聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中B+Tree的叶子节点上的data就是数据本身，key为主键，如果是一般索引的话，data便会指向对应的主索引，如下图所示：

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

非聚簇索引

非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

MyisAM顺序储存数据，索引叶子节点保存对应数据行地址，辅助索引跟主键索引相差无几（主键索引key不能相同）；InnoDB主键节点同时保存数据行，其他辅助索引保存的是主键索引的值；

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调(可能是指“非递增”的意思)的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调(可能是指“非递增”的意思)的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

为什么选用B+/-Tree

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

B-Tree：如果一次检索需要访问4个节点，数据库系统设计者利用磁盘预读原理，把节点的大小设计为一个页，那读取一个节点只需要一次I/O操作，完成这次检索操作，最多需要3次I/O(根节点常驻内存)。数据记录越小，每个节点存放的数据就越多，树的高度也就越小，I/O操作就少了，检索效率也就上去了。

B+Tree：非叶子节点只存key，大大滴减少了非叶子节点的大小，那么每个节点就可以存放更多的记录，树更矮了，I/O操作更少了。所以B+Tree拥有更好的性能。

MySQL默认的事务隔离级别，MVCC、RR怎么实现的？RC如何实现的？

MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

mysql 默认的隔离级别:可重复读
Oracle 默认的隔离
级别:读已提交

MVCC

MVCC(Multi Version Concurrency Control的简称)，代表多版本并发控制。与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。
MVCC最大的优势：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能

了解MVCC前，我们先学习下Mysql架构和数据库事务隔离级别

MYSQL 架构

MySQL从概念上可以分为四层，顶层是接入层，不同语言的客户端通过mysql的协议与mysql服务器进行连接通信，接入层进行权限验证、连接池管理、线程管理等。下面是mysql服务层，包括sql解析器、sql优化器、数据缓冲、缓存等。再下面是mysql中的存储引擎层，mysql中存储引擎是基于表的。最后是系统文件层，保存数据、索引、日志等。

MVCC是为了解决什么问题?

大多数的MYSQL事务型存储引擎,如,InnoDB，Falcon以及PBXT都不使用一种简单的行锁机制.事实上,他们都和MVCC–多版本并发控制来一起使用。
大家都应该知道,锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销。

MVCC具体实现

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
下面看一下在REPEATABLE READ隔离级别下，MVCC具体是如何操作的。

SELECT
- InnoDB会根据以下两个条件检查每行记录：
  - 1、InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
  - 2、行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
  - 只有符合上述两个条件的记录，才能返回作为查询结果。
INSERT
- InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
DELETE
- InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE
- InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。
- 保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作

举例Demo

1
2
3

create table riemann( 
id int primary key auto_increment, 
name varchar(20));

transaction 1:

start transaction;
insert into riemann values(NULL,'riemann');
insert into riemann values(NULL,'chow');
commit;

假设系统初始事务ID为1；

ID	NAME	创建时间(事务ID)	过期时间(事务ID)
1	riemann	1	undefined
2	chow	1	undefined

transaction 2:

start transaction;
select * from riemann ;  //(1)
select * from riemann ;  //(2)
commit

SELECT

假设当执行事务2的过程中，准备执行语句(2)时，开始执行事务3：

transaction 3:

1
2
3

start transaction;
insert into riemann values(NULL,'peng');
commit;

ID	NAME	创建时间(事务ID)	过期时间(事务ID)
1	riemann	1	undefined
2	chow	1	undefined
3	peng	3	undefined

事务3执行完毕，开始执行事务2 语句2，由于事务2只能查询创建时间小于等于2的，所以事务3新增的记录在事务2中是查不出来的，这就通过乐观锁的方式避免了幻读的产生。

UPDATE

假设当执行事务2的过程中，准备执行语句(2)时，开始执行事务4：

transaction session 4:

1
2
3

start transaction;
update riemann set name = 'edgar' where id = 2;
commit;

InnoDB执行UPDATE，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要UPDATE的行的删除时间。

ID	NAME	创建时间(事务ID)	过期时间(事务ID)
1	riemann	1	undefined
2	chow	1	4
3	edgar	4	undefined

事务4执行完毕，开始执行事务2 语句2，由于事务2只能查询创建时间小于等于2的，所以事务修改的记录在事务2中是查不出来的，这样就保证了事务在两次读取时读取到的数据的状态是一致的。

DELETE

假设当执行事务2的过程中，准备执行语句(2)时，开始执行事务5：

transaction session 5:

1
2
3

start transaction;
delete from riemann where id = 2;
commit;

ID	NAME	创建时间(事务ID)	过期时间(事务ID)
1	riemann	1	undefined
2	chow	1	5

事务5执行完毕，开始执行事务2 语句2，由于事务2只能查询创建时间小于等于2、并且过期时间大于等于2，所以id=2的记录在事务2 语句2中，也是可以查出来的,这样就保证了事务在两次读取时读取到的数据的状态是一致的。

RR

MVCC(Multi-Version Concurrent Control)：多版本并发控制，只作用于RC和RR隔离级别，主要是为了避免脏读、非重复读，而非幻读，很多文章说通过MVCC避免幻读，其实这种说法是不完善的，RR隔离级别是通过next-key lock 来避免幻读。

优点：避免了许多需要加锁的情形

缺点：需要维护每行记录版本号，造成额外资源消耗

怎么避免脏读、不可重复读、幻读？

采用RR隔离级别，结合MVCC特性，可以避免脏读、非重复读，有些文章说MVCC用来避免幻读，其实这是不准确的，MVCC通过多版本并发控制来避免非重复读，像幻读定义所说的情况即使有MVCC还是会存在。RR隔离级别是通过禁用innodb_locks_unsafe_for_binlog，在搜索和扫描索引的时候使用next-key locks来避免幻读（下面有对锁说明）。也就是为什么RR隔离级别下，非主键索引DML的操作并发性能会下降的原因了。

为了减少Next-key lock影响，可以设置innodb_locks_unsafe_for_binlog=1，就是disable Next-Key lock，但是并不建议。

想要真正避免幻读只能采取serializable串行化隔离级别，因为都要加表级共享锁或排他锁，所以性能会很差，一般不会采用。

MVCC如何避免非重复读：

MVCC为查询提供了一个基于时间的点的快照。这个查询只能看到在自己之前提交的数据，而在查询开始之后提交的数据是不可以看到的。

在每行记录后面记录两个隐藏的列，一个记录创建时间，一个记录删除时间，记录的是版本号，这里可以理解为事物号。

INSERT：Innodb 为新插入的每一行保存当前系统版本号作为行版本号；

DELETE：Innodb 为删除的每一行保存当前系统版本号作为行删除标识；

UPDATE：Innodb 为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

RR隔离级别下锁介绍

Record Lock

在主键或唯一索引上对单行记录加锁

Gap Lock

针对非唯一索引而言，锁定一个范围的记录，但不包括记录本身。锁加在未使用的空闲空间上，可能是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

如果更新两端的记录会影响到间隙锁，那么操作会被挂起，等待间隙锁释放。

比如锁定范围（4，7），update table set v1=6 where v1=1; 虽然1不在此范围，但是6在（4，7）范围还是会锁定。

Next-Key Lock

针对非唯一索引而言，行记录锁与间隙锁组合起来用就叫做Next-Key Lock。锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

通过一个例子介绍间隙锁

表test5中存在如下数据：

select * from test5 where v1=45 for update; 对v1=45的行加X锁，此时会对(40,45][45,50)加间隙锁，其他事物不能操作在此范围内的数据。

但是为什么在左侧值为40，右侧值为50的时候，有时候操作会被挂起，有时候操作不会挂起呢？

update table set v1=41 where v1=40;41在(40，50)范围会被锁定。

update table set v1=39 where v1=40; 39不在(40，50)范围不会被锁定。

update table set v1=42 where v1=1; 42在(40，50)范围会被锁定。

update table set v1=30 where v1=45; 30不在(40，50)范围，但是45行上面存在的行级record lock，45行记录也被加了锁。

insert into table(id,name) values(14,40);可以插入

insert into table(id,name) values(20,40);不可以插入

insert into table(id,name) values(13,50);不可以插入

insert into table(id,name) values(21,40);可以插入

当插入左侧值的时候，即插入v1=40的时候，要求插入的id值小于id=16的范围。当v1=40的记录有多条的时候，插入的id值要小于其中的最大id值。则可以成功插入；

当插入右侧值的时候，即插入v1=50的时候，要求插入的id值要大于id=18的范围。当v1=50的记录有多条的时候，插入的id值要大于其中的最小id值。则可以成功插入。

所以为什么RR隔离级别下并发性能会有所下降，就是因为存在间隙锁。我们应该尽量使用主键或唯一索引，因为唯一索引会把Next-Key Lock降级为Record Lock。

AUTO-INC Lock

只针对存在主键的insert操作，由innodb_autoinc_lock_mode参数决定锁粒度。

在了解自增锁前需要知道mysql都有哪些insert操作：

锁类型	描述
INSERT-like	所有可以向表中增加行的语句
Simple inserts	可以预先确定要插入的行数insert…values…
Bulk inserts	事先不知道要插入的行数（INSERT…SELECT,REPLACE…SELECT,LOAD DATA）
Mixed-mode inserts	一些是“Simple inserts”语句但是有一些是null的自增值

innodb_autoinc_lock_mode= 0 传统锁定模式（所有insert采用传统AUTO-INC机制），所有“INSERT-like”语句获得一个特殊的表级AUTO-INC锁，在存在自增列的表获得一个特殊的表级AUTO-INC锁，(statement-based replication)操作是安全。

innodb_autoinc_lock_mode= 1 默认锁定模式（bulk-insert采用表级锁）

“bulk inserts”仍然使用AUTO-INC表级锁,并保持到语句结束；“Simple inserts”（要插入的行数事先已知）通过在mutex（轻量锁）的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁，只在分配的时间内持有，不是整个语句，(statement-based replication)操作是安全。

innodb_autoinc_lock_mode= 2 轻量锁定模式(所有insert采用轻量级）

所有类INSERT(“INSERT-like” )语句都不会使用表级AUTO-INC lock，”批量插入”时，在由任何给定语句分配的自动递增值中可能存在间隙，(statement-based replication)操作是不安全。

可以汇总为如下表格：

示例：innodb_autoinc_lock_mode= 1时不连续

创建一个表id为自增主键：

CREATE TABLE `test6` (

id int(11) NOT NULL AUTO_INCREMENT,
name int(11),
modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

先插入一条记录，然后再多次自插入数据，发现id没有5、10~12，如下：

这种情况就是上面锁说的，insert…select…属于Bulk insert，不能预判要插入多少条数据，所以在自增值分配上每次都会按照2^n-1分配：

第一次，先分配一个自增值，因为只有一条数据，正好

第二次，先分配一个自增值3，发现还有数据，继续按2^n-1分配，分配4、5，此时只剩一条数据4，但5已经被分配出去。

第三次，因为5已经被分配出去，此时只能从6开始，以此类推。

Dead lock

是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象。

死锁检测开关innodb_deadlock_detect 5.7.15后引入，关闭会提升性能，一般应用在秒杀等场景。

出现死锁场景很多，绝大多数是高并发下同时操作一行数据，加锁顺序相反引起。

先删再插，两条insert当需要进行唯一性冲突检测时，需要先加一个S锁，也会产生死锁。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。

MySQL 中RC和RR隔离级别的区别

MySQL数据库中默认隔离级别为RR，但是实际情况是使用RC 和 RR隔离级别的都不少。好像淘宝、网易都是使用的 RC 隔离级别。那么在MySQL中 RC 和 RR有什么区别呢？我们该如何选择呢？为什么MySQL将RR作为默认的隔离级别呢？

RC 与 RR 在锁方面的区别

1> 显然 RR 支持 gap lock(next-key lock)，而RC则没有gap lock。因为MySQL的RR需要gap lock来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR；

2> RC 隔离级别，通过 where 条件过滤之后，不符合条件的记录上的行锁，会释放掉(虽然这里破坏了“两阶段加锁原则”)；但是RR隔离级别，即使不符合where条件的记录，也不会是否行锁和gap lock；所以从锁方面来看，RC的并发应该要好于RR；另外 insert into t select … from s where 语句在s表上的锁也是不一样的，参见下面的例子2；

下面是来自 github 的一个例子：

MySQL5.6, 隔离级别RR，autocommit=off;

表结构：

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  `e` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_t1_bcd` (`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

表数据：

mysql> select * from t1;
+---+---+---+---+------+
| a | b | c | d | e    |
+---+---+---+---+------+
| 1 | 1 | 1 | 1 | a    |
| 2 | 2 | 2 | 2 | b    |
| 3 | 3 | 2 | 2 | c    |
| 4 | 3 | 1 | 1 | d    |
| 5 | 2 | 3 | 5 | e    |
| 6 | 6 | 4 | 4 | f    |
| 7 | 4 | 5 | 5 | g    |
| 8 | 8 | 8 | 8 | h    |
+---+---+---+---+------+
8 rows in set (0.00 sec)

session 1:

1	delete from t1 where b>2 and b<5 and c=2;

执行计划如下：

mysql> explain select * from t1 where b>2 and b<5 and c=2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: idx_t1_bcd
          key: idx_t1_bcd
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using index condition
1 row in set (0.00 sec)

session 2:

1	delete from t1 where a=4

结果 session 2 被锁住。
session 3:

mysql> select * from information_schema.innodb_locks;
+---------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 38777:390:3:5 | 38777       | X         | RECORD    | `test`.`t1` | PRIMARY    |        390 |         3 |        5 | 4         |
| 38771:390:3:5 | 38771       | X         | RECORD    | `test`.`t1` | PRIMARY    |        390 |         3 |        5 | 4         |
+---------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+

根据锁及ICP的知识，此时加锁的情况应该是在索引 idx_t1_bcd 上的b>2 and b<5之间加gap lock, idx_t1_bcd上的c=2 加 X锁主键 a=3 加 x 锁。
应该a=4上是没有加X锁的，可以进行删除与更改。
但是从session3上的结果来，此时a=4上被加上了X锁。
求大牛解惑，谢谢。

要理解这里为什么 a=4 被锁住了，需要理解 gap lock，锁处理 RR 隔离级别和RC隔离级别的区别等等。

这里的原因如下：

很简单，我们注意到：key_len: 4 和 Extra: Using index condition
这说明了，仅仅使用了索引 idx_t1_bcd 中的 b 一列，没有使用到 c 这一列。c 这一列是在ICP时进行过滤的。所以：

delete from t1 where b>2 and b<5 and c=2 其实锁定的行有：

mysql> select * from t1 where b>2 and b<=6;
+---+---+---+---+------+
| a | b | c | d | e    |
+---+---+---+---+------+
| 3 | 3 | 2 | 2 | c    |
| 4 | 3 | 1 | 1 | d    |
| 6 | 6 | 4 | 4 | f    |
| 7 | 4 | 5 | 5 | g    |
+---+---+---+---+------+
4 rows in set (0.00 sec)

所以显然 delete from t1 where a=4就被阻塞了。那么为什么 delete from t1 where a=6 也会被阻塞呢？？？

这里 b<=6的原因是，b 列中没有等于 5 的记录，所以 and b<5 实现为锁定 b<=6 的所有索引记录，这里有等于号的原因是，如果我们不锁定 =6 的索引记录，那么怎么实现锁定 <5 的gap 呢？也就是说锁定 b=6 的索引记录，是为了实现锁定 b< 5 的gap。也就是不能删除 b=6 记录的原因。
而这里 b >2 没有加等于号(b>=2) 的原因，是因为 b>2的这个gap 是由 b=3这个索引记录(的gap)来实现的，不是由 b=2索引记录(的gap) 来实现的，b=2的索引记录的gap lock只能实现锁定<2的gap，b>2的gap锁定功能，需要由 b=3的索引记录对应的gap来实现(b>2，b<3的gap)。
所以我们在session2中可以删除：a=1,2,5,8的记录，但是不能删除 a=6(因为该行的b=6)的记录。

如果我们使用 RC 隔离级别时，则不会发生阻塞，其原因就是：

RC和RR隔离级别中的锁处理不一样，RC隔离级别时，在使用c列进行ICP where条件过滤时，对于不符合条件的记录，锁会释放掉，而RR隔离级别时，即使不符合条件的记录，锁也不会释放(虽然违反了“2阶段锁”原则)。所以RC隔离级别时session 2不会被阻塞。

Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

例子2：insert into t select ... from s where 在RC 和 RR隔离级别下的加锁过程

下面是官方文档中的说明：

INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally.

CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.

When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s.

insert inot t select ... from s where ...语句和 create table ... select ... from s where加锁过程是相似的(RC 和 RR 加锁不一样)：

1> RC 隔离级别时和 RR隔离级别但是设置innodb_locks_unsafe_for_binlog=1 时，select ... from s where对 s 表进行的是一致性读，所以是无需加锁的；

2> 如果是RR隔离级别(默认innodb_locks_unsafe_for_binlog=0)，或者是 serializable隔离级别，那么对 s 表上的每一行都要加上 shared next-key lock.

这个区别是一个很大的不同，下面是生成中的一个 insert into t select ... from s where导致的系统宕机的案例：

一程序猿执行一个分表操作：

1
2
3

insert into tb_async_src_acct_201508 select * from tb_async_src_acct 

where src_status=3 and create_time>='2015-08-01 00:00:00' and create_time <= '2015-08-31 23:59:59';

表 tb_async_src_acct有4000W数据。分表的目的是想提升下性能。结果一执行该语句，该条SQL被卡住，然后所有向 tb_async_src_acct的写操作，要么是 get lock fail, 要么是 lost connection，全部卡住，然后主库就宕机了。

显然这里的原因，就是不知道默认RR隔离级别中 insert into t select ... from s where语句的在 s 表上的加锁过程，该语句一执行，所有符合 where 条件的 s 表中的行记录都会加上 shared next-key lock(如果没有使用到索引，还会锁住表中所有行)，在整个事务过程中一直持有，因为表 tb_async_src_acct 数据很多，所以运行过程是很长的，所以加锁过程也是很长，所以其它所有的对tb_async_src_acct 的insert, delete, update, DDL 都会被阻塞掉，这样被阻塞的事务就越来越多，而事务也会申请其它的表中的行锁，结果就是系统中被卡住的事务越来越多，系统自然就宕机了。

RC 与 RR 在复制方面的区别

RC 隔离级别不支持 statement 格式的bin log，因为该格式的复制，会导致主从数据的不一致；只能使用 mixed 或者 row 格式的bin log; 这也是为什么MySQL默认使用RR隔离级别的原因。复制时，我们最好使用：binlog_format=row
MySQL5.6 的早期版本，RC隔离级别是可以设置成使用statement格式的bin log，后期版本则会直接报错；

RC 与 RR 在一致性读方面的区别

简单而且，RC隔离级别时，事务中的每一条select语句会读取到他自己执行时已经提交了的记录，也就是每一条select都有自己的一致性读ReadView; 而RR隔离级别时，事务中的一致性读的ReadView是以第一条select语句的运行时，作为本事务的一致性读snapshot的建立时间点的。只能读取该时间点之前已经提交的数据。

RC 支持半一致性读，RR不支持

RC隔离级别下的update语句，使用的是半一致性读(semi consistent)；而RR隔离级别的update语句使用的是当前读；当前读会发生锁的阻塞。

1> 半一致性读：

A type of read operation used for UPDATE statements, that is a combination of read committed and consistent read. When an UPDATE statement examines a row that is already locked, InnoDB returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again, and this time InnoDB either locks it or waits for a lock on it. This type of read operation can only happen when the transaction has the read committed isolation level, or when the innodb_locks_unsafe_for_binlog option is enabled.

简单来说，semi-consistent read是read committed与consistent read两者的结合。一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足 update的where条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)。semi-consistent read只会发生在read committed隔离级别下，或者是参数innodb_locks_unsafe_for_binlog被设置为true(该参数即将被废弃)。

对比RR隔离级别，update语句会使用当前读，如果一行被锁定了，那么此时会被阻塞，发生锁等待。而不会读取最新的提交版本，然后来判断是否符合where条件。

半一致性读的优点：

减少了update语句时行锁的冲突；对于不满足update更新条件的记录，可以提前放锁，减少并发冲突的概率。

具体可以参见：

Oracle中的update好像有“重启动”的概念。

MySQL间隙锁有没有了解，死锁有没有了解，写一段会造成死锁的SQL语句，死锁发生了如何解决，MySQL有没有提供什么机制去解决死锁

间隙锁

之前我们介绍了排他锁，其实innodb下的记录锁（也叫行锁），间隙锁，next-key锁统统属于排他锁。

行锁
记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。

生活中的间隙锁
编程的思想源于生活，生活中的例子能帮助我们更好的理解一些编程中的思想。
生活中排队的场景，小明，小红，小花三个人依次站成一排，此时，如何让新来的小刚不能站在小红旁边，这时候只要将小红和她前面的小明之间的空隙封锁，将小红和她后面的小花之间的空隙封锁，那么小刚就不能站到小红的旁边。
这里的小红，小明，小花，小刚就是数据库的一条条记录。
他们之间的空隙也就是间隙，而封锁他们之间距离的锁，叫做间隙锁。

Mysql中的间隙锁
下表中（见图一），id为主键，number字段上有非唯一索引的二级索引，有什么方式可以让该表不能再插入number=5的记录？

图一
根据上面生活中的例子，我们自然而然可以想到，只要控制几个点，number=5之前不能插入记录，number=5现有的记录之间不能再插入新的记录，number=5之后不能插入新的记录，那么新的number=5的记录将不能被插入进来。

那么，mysql是如何控制number=5之前，之中，之后不能有新的记录插入呢（防止幻读）？
答案是用间隙锁，在RR级别下，mysql通过间隙锁可以实现锁定number=5之前的间隙，number=5记录之间的间隙，number=5之后的间隙，从而使的新的记录无法被插入进来。

间隙是怎么划分的？

注：为了方面理解，我们规定（id=A,number=B）代表一条字段id=A,字段number=B的记录，（C，D）代表一个区间，代表C-D这个区间范围。

图一中，根据number列，我们可以分为几个区间：（无穷小，2），（2，4），（4，5），（5，5），（5,11），（11，无穷大）。
只要这些区间对应的两个临界记录中间可以插入记录，就认为区间对应的记录之间有间隙。
例如：区间（2，4）分别对应的临界记录是（id=1,number=2），（id=3，number=4），这两条记录中间可以插入（id=2,number=3）等记录，那么就认为（id=1,number=2）与（id=3，number=4）之间存在间隙。

很多人会问，那记录（id=6，number=5）与（id=8，number=5）之间有间隙吗？
答案是有的，（id=6，number=5）与（id=8，number=5）之间可以插入记录（id=7，number=5），因此（id=6,number=5）与（id=8,number=5）之间有间隙的，

间隙锁锁定的区域
根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。
图一中，where number=5的话，那么间隙锁的区间范围为（4,11）；

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）

innodb自动使用间隙锁的条件：
（1）必须在RR级别下
（2）检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

接下来，通过实际操作观察下间隙锁的作用范围

案例一

session 1:
start  transaction ;
select  * from news where number=4 for update ;

session 2:
start  transaction ;
insert into news value(2,4);#（阻塞）
insert into news value(2,2);#（阻塞）
insert into news value(4,4);#（阻塞）
insert into news value(4,5);#（阻塞）
insert into news value(7,5);#（执行成功）
insert into news value(9,5);#（执行成功）
insert into news value(11,5);#（执行成功）

检索条件number=4,向左取得最靠近的值2作为左区间，向右取得最靠近的5作为右区间，因此，session 1的间隙锁的范围（2，4），（4，5），如下图所示：

间隙锁锁定的区间为（2，4），（4，5），即记录（id=1,number=2）和记录（id=3,number=4）之间间隙会被锁定，记录（id=3,number=4）和记录（id=6,number=5）之间间隙被锁定。

因此记录（id=2,number=4），（id=2,number=2），（id=4,number=4），（id=4,number=5）正好处在（id=3,number=4）和（id=6,number=5）之间，所以插入不了，需要等待锁的释放，而记录(id=7,number=5)，（id=9,number=5），（id=11,number=5）不在上述锁定的范围内，因此都会插入成功。

案例二

session 1:
start  transaction ;
select  * from news where number=13 for update ;

session 2:
start  transaction ;
insert into news value(11,5);#(执行成功)
insert into news value(12,11);#(执行成功)
insert into news value(14,11);#(阻塞)
insert into news value(15,12);#(阻塞)
update news set id=14 where number=11;#(阻塞)
update news set id=11 where number=11;#(执行成功)

检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，因此，session 1的间隙锁的范围（11，无穷大），如下图所示：

此表中没有number=13的记录的，innodb依然会为该记录左右两侧加间隙锁，间隙锁的范围（11，无穷大）。

有人会问，为啥update news set id=14 where number=11会阻塞，但是update news set id=11 where number=11却执行成功呢？

间隙锁采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入，此图间隙锁锁定的区域是（11，无穷大），也就是记录（id=13,number=11）之后不能再插入记录，update news set id=14 where number=11这条语句如果执行的话，将会被插入到（id=13,number=11）的后面，也就是在区间（11，无穷大）之间，由于该区间被间隙锁锁定，所以只能阻塞等待，而update news set id=11 where number=11执行后是会被插入到（id=13,number=11）的记录前面，也就不在（11，无穷大）的范围内，所以无需等待，执行成功。

案例三

session 1:
start  transaction ;
select  * from news where number=5 for update;

session 2:
start  transaction ;
insert into news value(4,4);#(阻塞)
insert into news value(4,5);#(阻塞)
insert into news value(5,5);#(阻塞)
insert into news value(7,11);#(阻塞)
insert into news value(9,12);#(执行成功)
insert into news value(12,11);#(阻塞)
update news set number=5 where id=1;#(阻塞)
update news set id=11 where number=11;#(阻塞)
update news set id=2 where number=4 ;#（执行成功）
update news set id=4 where number=4 ;#（阻塞）

检索条件number=5,向左取得最靠近的值4作为左区间，向右取得11为右区间，因此，session 1的间隙锁的范围（4，5），（5，11），如下图所示：

有人会问，为啥insert into news value(9,12)会执行成功？间隙锁采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入，（id=9,number=12）很明显在记录（13,11）的后面，因此不再锁定的间隙范围内。

为啥update news set number=5 where id=1会阻塞？
number=5的记录的前面，后面包括中间都被封锁了，你这个update news set number=5 where id=1根本没法执行，因为innodb已经把你可以存放的位置都锁定了，因为只能等待。

同理，update news set id=11 where number=11由于记录（id=10,number=5）与记录（id=13,number=11）中间的间隙被封锁了，你这句sql也没法执行，必须等待，因为存放的位置被封锁了。

案例四

session 1:
start  transaction;
select * from news where number>4 for update;

session 2:
start  transaction;
update news set id=2 where number=4 ;#(执行成功)
update news set id=4 where number=4 ;#(阻塞)
update news set id=5 where number=5 ;#(阻塞)
insert into news value(2,3);#(执行成功)
insert into news value(null,13);#(阻塞)

检索条件number>4,向左取得最靠近的值4作为左区间，向右取无穷大，因此，session 1的间隙锁的范围（4，无穷大），如下图所示：

session2中之所以有些阻塞，有些执行成功，其实就是因为插入的区域被锁定，从而阻塞。

next-key锁

next-key锁其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身，InnoDB默认加锁方式是next-key 锁。
上面的案例一session 1中的sql是：select * from news where number=4 for update ;
next-key锁锁定的范围为间隙锁+记录锁，即区间（2，4），（4，5）加间隙锁，同时number=4的记录加记录锁。

死锁

概念

MySQL有三种锁的级别：页级、表级、行级。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

算法：

next KeyLocks锁，同时锁住记录(数据)，并且锁住记录前面的Gap
Gap锁，不锁记录，仅仅记录前面的Gap

Recordlock锁（锁数据，不锁Gap）

所以其实 Next-KeyLocks=Gap锁+ Recordlock锁

什么情况下会造成死锁

所谓死锁<DeadLock>: 是指两个或两个以上的进程在执行过程中,
因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.
此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程.
表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB.

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

一些常见的死锁案例

案例一

需求：将投资的钱拆成几份随机分配给借款人。

起初业务程序思路是这样的：

投资人投资后，将金额随机分为几份，然后随机从借款人表里面选几个，然后通过一条条select for update 去更新借款人表里面的余额等。

抽象出来就是一个session通过for循环会有几条如下的语句：
Select * from xxx where id='随机id' for update

基本来说，程序开启后不一会就死锁。
这可以是说最经典的死锁情形了。

例如两个用户同时投资，A用户金额随机分为2份，分给借款人1，2
B用户金额随机分为2份，分给借款人2，1由于加锁的顺序不一样，死锁当然很快就出现了。

对于这个问题的改进很简单，直接把所有分配到的借款人直接一次锁住就行了。

Select * from xxx where id in (xx,xx,xx) for update

在in里面的列表值mysql是会自动从小到大排序，加锁也是一条条从小到大加的锁

例如（以下会话id为主键）：

Session1:

mysql> select * from t3 where id in (8,9) for update;

+----+--------+------+---------------------+

| id | course | name | ctime               |

+----+--------+------+---------------------+

|  8 | WA     | f    | 2016-03-02 11:36:30 |

|  9 | JX     | f    | 2016-03-01 11:36:30 |

+----+--------+------+---------------------+
rows in set (0.04 sec)

 

 

Session2:

select * from t3 where id in (10,8,5) for update;

锁等待中……

其实这个时候id=10这条记录没有被锁住的，但id=5的记录已经被锁住了，锁的等待在id=8的这里。

 

不信请看

Session3:

mysql> select * from t3 where id=5 for update;

锁等待中

 

Session4:

mysql> select * from t3 where id=10 for update;

+----+--------+------+---------------------+

| id | course | name | ctime               |

+----+--------+------+---------------------+

| 10 | JB     | g    | 2016-03-10 11:45:05 |

+----+--------+------+---------------------+
row in set (0.00 sec)

 

在其它session中id=5是加不了锁的，但是id=10是可以加上锁的。

案例2

在开发中，经常会做这类的判断需求：根据字段值查询（有索引），如果不存在，则插入；否则更新。

以id为主键为例，目前还没有id=22的行

Session1:

select * from t3 where id=22 for update;

Empty set (0.00 sec)

 

session2:

select * from t3 where id=23  for update;

Empty set (0.00 sec)

 

Session1:

insert into t3 values(22,'ac','a',now());

锁等待中……

 

Session2:

insert into t3 values(23,'bc','b',now());

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

当对存在的行进行锁的时候(主键)，mysql就只有行锁。

当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁）

锁住的范围为：

(无穷小或小于表中锁住id的最大值，无穷大或大于表中锁住id的最小值)

如：如果表中目前有已有的id为（11 ， 12）

那么就锁住（12，无穷大）

如果表中目前已有的id为（11 ， 30）

那么就锁住（11，30）

对于这种死锁的解决办法是：

insert into t3(xx,xx) on duplicate key update `xx`='XX';

用mysql特有的语法来解决此问题。因为insert语句对于主键来说，插入的行不管有没有存在，都会只有行锁。

案例3

mysql> select * from t3 where id=9 for update;

+----+--------+------+---------------------+

| id | course | name | ctime               |

+----+--------+------+---------------------+

|  9 | JX     | f    | 2016-03-01 11:36:30 |

+----+--------+------+---------------------+
row in set (0.00 sec)

 

Session2:

mysql> select * from t3 where id<20 for update;

锁等待中

 

Session1:

mysql> insert into t3 values(7,'ae','a',now());

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这个跟案例一其它是差不多的情况，只是session1不按常理出牌了，

Session2在等待Session1的id=9的锁，session2又持了1到8的锁（注意9到19的范围并没有被session2锁住），最后，session1在插入新行时又得等待session2,故死锁发生了。

这种一般是在业务需求中基本不会出现，因为你锁住了id=9，却又想插入id=7的行，这就有点跳了，当然肯定也有解决的方法，那就是重理业务需求，避免这样的写法。