Mysql无敌面试笔记

Mysql无敌面试笔记

目录

• Mysql无敌面试笔记
  • 一、mySql架构
  • 二、mysql引擎
  • 三、数据类型
  • 四、索引
    • • 优势
      • 劣势
    • MySQL索引分类
      • 从数据结构角度
      • 从物理存储角度
      • 从逻辑角度
    • MySQL索引结构
      • B+Tree 索引
        • B-Tree索引介绍
        • B+Tree索引介绍
          • MyISAM主键索引与辅助索引的结构
          • InnoDB主键索引与辅助索引的结构
      • Hash索引
      • full-text全文索引
      • R-Tree空间索引
    • 问
  • 五、MySql查询
  • 六、MySql事务
    • ACID — 事务基本要素
      • 并发事务处理带来的问题
    • 事务隔离级别
      • 读未提交Read uncommitted
      • 读已提交Read committed
      • 重复读Repeatable read
      • 序列化Serializable
      • 比较
    • MVCC 多版本并发控制
    • 事务日志
    • 事务的实现
  • 七、MySQL锁机制
    • 锁的分类
    • MyISAM 表锁
    • InnoDB 行锁
      • 加锁机制
      • 锁模式(三种行锁的算法)
    • 死锁
  • 八、MySQL调优
    • 影响mysql的性能因素
    • 性能分析
      • MySQL Query Optimizer
      • MySQL常见瓶颈
      • 性能下降SQL慢 执行时间长 等待时间长 原因分析
    • 性能优化
      • 索引优化
      • 查询优化
      • 数据类型优化
  • 九、分区、分表、分库
    • 分区
  • 分表
    • 分库
  • 十、主从复制
    • 复制的基本原理
    • 复制的基本原则
    • 复制用途
    • 复制的最大问题
    • 解决方法：
  • 十一、其他问题
    • 三个范式
    • 百万级别或以上的数据如何删除

一、mySql架构

---

共分四层架构

• 连接层 第一层：用于与客户端的链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案，可以实现基于ssl的链接
• 服务层 第二层：主要完成大部分的核心服务，查询解析、分析、优化、缓存等等
• 引擎层 第三层：复杂对数据的存储和提取，第二层服务层通过Api与引擎通信，不同引擎具有不同功能
• 存储层第四层：将数据存储在运行mySql的设备的文件系统中，并完成与第三层引擎层的交互

MySQL 的查询流程具体是？or 一条SQL语句在MySQL中如何执行的

客户端请求 ---> 连接器（验证用户身份，给予权限） ---> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） ---> 分析器（对SQL进行词法分析和语法分析操作） ---> 优化器（主要对执行的sql优化选择最优的执行方案方法） ---> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） ---> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

二、mysql引擎

---

一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求，mysql使用可插拔的存储引擎体系结构，可以从运行中的mysql服务器中加载或卸载存储引擎。

mysql 8.0共有9种存储引擎InnoDB， MEMORY， MyISAM， MRG_MYISAM， CSV， FEDERATED， BLACKHOLE， PERFORMANCE_SCHEMA， ARCHIVE， 其中常见的引擎有三种，其中InnoDB为默认引擎。

mysql中每建立一张表，就会在其数据目录对应的数据库目录下新建一张 表名.frm的文件，来保存数据信息，包括结果的定义， 且与存储引擎无关，每个引擎都存在.frm文件!

说说MySQL有哪些存储引擎？都有哪些区别？

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	最小粒度为表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	最小粒度为行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
索引类型	非聚簇索引	聚簇索引
表空间	小	大
关注点	性能	事务

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15，16，17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；

如果表的类型是8.0以下版本的InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失，但8.0版本后做出优化，将自增主键的计数器持久化到redo log中，可实现主键持久化

哪个存储引擎执行 select count(*) 更快，为什么?

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

• 在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。
• 在 InnoDB 存储引擎中，会先把数据读出来，一行一行的累加，最后返回总数量， 因为InnoDB 的事务特性，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

三、数据类型

---

主要包括五大类

• 整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、INT、 BIG INT

• 浮点数类型：FLOAT、DOUBLE、DECIMAL

• 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

• 日期类型：Date、DateTime、TimeStamp、Time、Year

• 其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

CHAR 和 VARCHAR 的区别？

char是固定长度，varchar长度可变

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。存储时，char(n)不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而varchar(n) 会根据实际存储的数据分配最终的存储空间

相同点：

• char(n)，varchar(n)中的n都代表字符的个数，而不是字节数
• 超过char，varchar最大长度n的限制后，在宽松模式中字符串会被截断，严格模式会报错。

不同点：

• char(n)不论实际存储多少个字符，都会占用n个字符的空间，而varchar(n)只会占用实际字符应该占用的字节空间并加1（实际长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节或两个来记录长度

• char在存储时会截断字符串尾部的空格，varchar不会

建议：

• char是适合存储很短的、一般固定长度的字符串，如MD5值，对于非常短的列，char比varchar在存储空间上也更有效率

BLOB和TEXT有什么区别？

BLOB是一个二进制对象，可以容纳可变数量的数据，如图片视频等二进制流。有四种类型的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB

TEXT是一个不区分大小写的BLOB。四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。

BLOB 保存二进制数据，主要为字节字符串，TEXT 保存字符数据，主要为字符字符串。

四、索引

---

• MYSQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构
• 索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。
• 可以简单的理解为“排好序的快速查找数据结构”
• 除数据本身外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引，其本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上。

优势

• 提高数据检索效率，降低数据库IO成本
• 降低数据排序的成本，降低CPU的消耗

劣势

• 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，对表进行增删改时，不仅需要保存数据，还需要更新索引信息。

MySQL索引分类

从数据结构角度

• B+树索引
• Hash索引
• Full-Text全文索引
• R-Tree索引

从物理存储角度

• 聚集索引（clustered index）

• 非聚集索引（non-clustered index），也叫辅助索引（secondary index）

  聚集索引和非聚集索引都是B+树结构

从逻辑角度

• 唯一索引 --/-- 非唯一索引
• 主键索引： 特殊的唯一索引，在不允许重复的基础上也不允许为空
• 单列索引(普通索引) --/-- 多列索引（复合索引、联合索引）
• *空间索引： 只能在MyISAM引擎中创建

MySQL索引结构

首先要明白索引（index）是在存储引擎（storage engine）层面(第三层)实现的，而不是server层面(第二层)。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。

B+Tree 索引

度数：在树中，每个节点的子节点（子树）的个数就称为该节点的度（degree）。
阶数：（Order）阶定义为一个节点的子节点数目的最大值。（自带最大值属性）

所有数据都存放在叶子节点，且把叶子节点通过指针链接到一起，形成了一条数据链表，以加快相邻数据的检索效率。

B-Tree索引介绍

一棵m阶的B-Tree有如下特性

• 每个节点最多有m个孩子
• 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
• 所有叶子节点都在同一层，且不包含其它关键字信息
• 每个非终端节点包含n个关键字信息（P0，P1，…Pn， k1，…kn）
• 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
• ki(i=1，…n)为关键字，且关键字升序排序
• Pi(i=1，…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)
  

B+Tree索引介绍

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

从的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree特点：

1. 非叶子节点只存储键值信息；
2. 所有叶子节点之间都有一个链指针；
3. 数据记录都存放在叶子节点中

图示为5阶b+树，结点最少为2个key，最多为4个

MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的。如果数据项占的空间越小，则m数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

当b+树的数据项是复合的数据结构，比如(name，age，sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三，20，F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20，F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三，F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

MyISAM主键索引与辅助索引的结构

MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。索引文件与数据文件分离，这样的索引称为"非聚簇索引"。主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复。

通过索引查找数据的流程：索引文件中查找到索引节点 ----> 拿到数据的文件指针 ----> 到数据文件中通过指针定位具体数据。辅助索引类似。

• .myi 表索引文件
• .myd 表数据文件
• .frm 表结构文件

InnoDB主键索引与辅助索引的结构

InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为"“聚簇索引”，一个表只能有一个聚簇索引。

主键索引

InnoDB索引是聚集索引，它的索引和数据是存入同一个.idb文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的id、stu_id、name数据项。

索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。

辅助(非主键)索引

这次我们以示例中学生表中的name列建立辅助索引，它的索引结构跟主键索引的结构有很大差别，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。

这就意味着，对name列进行条件搜索，需要两个步骤：

① 在辅助索引上检索name，到达其叶子节点获取对应的主键；

② 使用主键在主索引上再进行对应的检索操作

这也就是所谓的“回表查询”

InnoDB 索引结构需要注意的点

1. 数据文件本身就是索引文件
2. 表数据文件本身就是按 B+Tree 组织的一个索引结构文件
3. 聚集索引中叶节点包含了完整的数据记录
4. InnoDB 表必须要有主键，并且推荐使用整型自增主键

因为 InnoDB 存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据；如果我们在设计表结构时没有显式指定索引列的话，MySQL 会从表中选择数据不重复的列建立索引；如果没有符合的列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，并且这个字段长度为6个字节，类型为整型。

为什么推荐使用整型自增主键而不是选择UUID？

1. UUID是字符串类型，比整形消耗更多的存储空间
2. 在B+树中进行查找时需要跟经过的节点值比较大小，整形数据的比较运算比字符串更快速
3. 自增的整形索引在磁盘中会连续存储，在读取一页数据时也是连续； 而UUID是随机的，所以上下两行数据存储是分散的，不适合执行where id > 5 && id < 20的查询条件。
4. 在插入或删除数据时，自增主键会在叶子结点的末尾建立新的叶子节点，而不会破坏左侧子树结构； 而UUID不能确定，所以B+树为了维持自身的特性，可能会进行结构的重构，消耗更多时间。

为什么非主键索引结构叶子节点存储的是主键值？

保证数据一致性和节省存储空间，在非索引值变更后不需要再次维护索引，存主键后执行回表查询即可，节省了存储空间。

Hash索引

主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选

MySQL目前有Memory引擎和NDB引擎支持Hash索引。

full-text全文索引

​ 全文索引是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

​ 原理：同样使用B-Tree存放索引，但使用的是特定的算法，将字段数据分割后再进行索引(一般4个字节一次分割)，索引文件存储的是分割前的索引字符串集合与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置

R-Tree空间索引

R树是B树向多维空间发展的另一种形式，它将对象空间按范围划分

问

为什么Mysql索引要用B+树不是B树？

用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。

其中在MySQL底层对B+树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。

为何不采用Hash方式？

因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。

哈希索引不支持多列联合索引的最左匹配规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

哪些情况需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段
3. 查询中与其他表关联的字段，外键关系建立索引
4. 单键/组合索引的选择问题： 高并发下倾向创建组合索引
5. 查询中需要排序的字段，排序字段通过索引访问会大幅度提升排序速度
6. 查询中需要统计或分组的字段

哪些情况不要创建索引

1. 表数据太少
2. 需要经常增删改的表，和经常需要更新的字段(加重io负担)
3. 数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据建立索引
4. where条件里用不到的字段不创建索引

MySQL高效索引

覆盖索引（Covering Index），或者叫索引覆盖， 也就是平时所说的 不需要回表操作

即select的数据列只从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含（覆盖）满足查询结果的数据就叫做覆盖索引。

五、MySql查询

---

count(*) 和 count(1)和count(列名)区别 ps：这道题说法有点多

执行效果上：

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
• count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：

• 列名为主键，count(列名)会比count(1)快
• 列名不为主键，count(1)会比count(列名)快
• 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
• 如果有主键，则 select count（主键）的执行效率是最优的
• 如果表只有一个字段，则 select count(*) 最优。

MySQL中 in和 exists 的区别？

exists运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

in查询相当于多个or条件的叠加

如果查询的A，B两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个较大，则子查询表大的用exists，子查询表小的用in

UNION和UNION ALL的区别?

UNION和UNION ALL都是将两个结果集合并为一个，两个要联合的SQL语句 字段个数必须一样，而且字段类型要一致；同时，每个 SELECT 语句中的列的顺序必须相同

• UNION在进行表连接后会筛选掉重复的数据记录（效率较低），而UNION ALL则不会去掉重复的数据记录；
• UNION会按照字段的顺序进行排序，而UNION ALL只是简单的将两个结果合并就返回；

SQL执行顺序

手写顺序	机读顺序
SELECT DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_condition> LIMIT <limit_number>	FROM <left_table> ON <join_condition> <join_type> JOIN <right_table> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> SELECT DISTINCT <select_list> ORDER BY <order_by_condition> LIMIT <limit_number>

mysql 的内连接、左连接、右连接有什么区别？什么是内连接、外连接、交叉连接、笛卡尔积呢？

交叉连接（CROSS JOIN）一般 用来返回连接表的笛卡尔积

当数据表量太多的时候，查询会非常慢，一般使用INNER JOIN 、LEFT JOIN或者RIGHT JOIN

六、MySql事务

---

事务的隔离级别有哪些？MySQL的默认隔离级别是什么？
什么是幻读，脏读，不可重复读呢？
MySQL事务的四大特性以及实现原理
MVCC熟悉吗，它的底层原理？

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

ACID — 事务基本要素

事务是由一组SQL语句组成的逻辑处理单元，具有4个属性，通常简称为事务的ACID属性。

• A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
• C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
• I (Isolation)隔离性：一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰
• D (Durability) 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

并发事务处理带来的问题

更新丢失（Lost Update)： 事务A和事务B选择同一行，然后基于最初选定的值更新该行时，由于两个事务都不知道彼此的存在，就会发生丢失更新问题，详情见csdn.

脏读(Dirty Reads)：读取了未被提交的数据，事务A读取了事务B更新但未提交的数据，之后B发生错误并执行回滚操作，那么A读取到的数据是脏数据。

不可重复读（Non-Repeatable Reads)：前后多次读取，数据内容不一致 事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

幻读（Phantom Reads)：A事务开始，并查询"张三"不存在， B事务开启，插入"张三"，并成功提交。 A事务继续插入"张三"， 失败! 事务A查询“张三”，查询不到，插入又不成功，“张三”这条数据就像幻觉一样出现。这就是所谓的“幻读”

“脏读” 、 “不可重复读”和“幻读” ，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决，加锁，或数据多版本并发控制解决

事务隔离级别

• READ-UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

• READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读

数据库的事务隔离越严格，并发副作用越小，但付出的代价就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

读未提交Read uncommitted

一个事务可以读取另一个未提交事务的数据。

时间点	事务A	事务B
1	开启事务
2		开启事务
3	查询数据为100条
4		insert一条数据
5	再查询，结果为101条

在时间点5，事务A再次查询数据时，事务B并没有提交事务，但是，新的数据也被事务A查出来了。这就是脏读。

读已提交Read committed

一个事务要等另一个事务提交后才能读取数据。

时间点	事务A	事务B
1	开启事务
2		开启事务
3	查询数据为100条
4		insert一条数据
5	查询数据为100条
6		提交事务
7	查询数据为101条

我们可以看到，事务B在提交事务之前，事务A的两次查询结果是一致的。事务B提交事务以后，事务A再次查询，查询到了新增的这条数据。在事务A中，导致多次查询的结果不一致，这就是我们说的“不可重复读”。

重复读Repeatable read

在开始读取数据（事务开启）时，不再允许修改操作。 MySQL的默认事务隔离级别

时间点	事务A	事务B
1	开启事务
2		开启事务
3	查询数据“张三”，不存在
4		插入数据“张三”
5		提交事务
6	查询数据“张三”，不存在
7	插入数据“张三”，不成功

事务A查询“张三”，查询不到，插入又不成功，“张三”这条数据就像幻觉一样出现。这就是所谓的“幻读”。

序列化Serializable

高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。简单来说，Serializable会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

比较

事务隔离级别	读数据一致性	脏读	不可重复读	幻读
读未提交（read-uncommitted）	最低级，只能保证不读取回滚的数据	是	是	是
读已提交（read-committed）	语句级	否	是	是
可重复读（repeatable-read）	事务级	否	否	是
串行化（serializable）	最高级别，事务级	否	否	否

MVCC 多版本并发控制

可以认为 MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。

MVCC 的实现是通过保存数据在某个时间点的快照来实现的。也就是说不管需要执行多长时间，每个事物看到的数据都是一致的。典型的MVCC实现方式，分为乐观（optimistic）并发控制和悲观（pressimistic）并发控制。

InnoDB 的 MVCC 的实现依赖：隐藏字段、Read View(读视图，和快照一个概念)、Undo log

隐藏字段是在每行记录后面保存的三个隐藏字段

• DB_TRX_ID(6字节)：表示最近一次对本记录行作修改（insert | update）的事务ID。至于delete操作，InnoDB认为是一个update操作，不过会更新一个另外的删除位，将行表示为deleted。并非真正删除。
• DB_ROLL_PTR(7字节)：回滚指针，指向当前记录行的undo log信息
• DB_ROW_ID(6字节)：随着新行插入而单调递增的行ID。如果表有主键或唯一非空索引，聚簇索引就不会包含这个行ID了。这个DB_ROW_ID跟MVCC关系不大。

REPEATABLE READ（可重读）隔离级别下MVCC的工作：

• SELECT

  InnoDB会根据以下两个条件检查每行记录：

  • InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在开始事务之前已经存在要么是事务自身插入或者修改过的
  • 行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除

  只有符合上述两个条件的才会被查询出来

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

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

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

保存这两个额外系统版本号，使大多数操作都不用加锁。使数据操作简单，性能很好，并且也能保证只会读取到符合要求的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

MVCC 只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。

事务日志

事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机 IO。InnoDB 使用日志来减少提交事务时的开销，把随机IO变成顺序IO，一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以重放日志并且恢复已经提交的事务。

InnoDB 使用一个后台线程智能地刷新这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高效率。

事务日志可以帮助提高事务效率：

• 使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到 持久在硬盘上的 事务日志中，而不用每次都将修改的数据本身持久到磁盘。
• 事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。
• 事务日志持久以后，内存中被修改的数据在后台可以慢慢刷回到磁盘。
• 如果数据的修改已经记录到事务日志并持久化，但数据本身没有写回到磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这一部分修改的数据。

目前来说，大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

事务的实现

• 事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有 InnoDB 和 NDB。

• 事务的实现就是如何实现ACID特性。

• 事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现 。

事务是如何通过日志来实现的，说得越深入越好。

事务日志包括：重做日志redo和回滚日志undo

• redo log（重做日志） 实现持久化和原子性

  • 是物理日记，日记记录的内容的是数据页的更改，记录这个页 “做了什么改动”。如：add xx记录 to Page1，向数据页Page1增加一个记录。

  • 更新内存后引擎层写 Redo log(重做日志) 将状态改成 prepare(准备状态) 为预提交第一阶段，Server 层写 Binlog(二进制日志)，再将将状态改成 commit为提交第二阶段。两阶段提交可以确保 Binlog 和 Redo log 数据一致性。

  • 容灾回复机制：

    • 判断 redo log 是否完整，如果判断是完整（commit）的，直接用 Redo log 恢复

    • 如果 redo log 只是预提交 prepare 但不是 commit 状态，这个时候就会去判断 binlog(二进制日志) 是否完整，如果完整就提交 Redo log，用 Redo log 恢复，不完整就回滚事务，丢弃数据。

    • 只有在 redo log 状态为 prepare 时，才会去检查 binlog 是否存在，否则只校验 redo log 是否是 commit 就可以啦。怎么检查 binlog：一个完整事务 binlog 结尾有固定的格式。

  • 当我们在一个事务中尝试对数据进行写时，它会先将数据从磁盘读入内存，并更新内存中缓存的数据，然后生成一条重做日志并写入重做日志缓存，当事务真正提交时，MySQL 会将重做日志缓存中的内容刷新到重做日志文件，再将内存中的数据更新到磁盘上，图中的第4、5步就是在事务提交时执行的。(流程图)

• undo log（回滚日志） 实现一致性

• undo log 主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。

  Undo记录的是已部分完成并且写入硬盘的未完成的事务，默认情况下回滚日志是记录下表空间中的（共享表空间或者独享表空间）

• 如： 在事务中使用的每一条 insert into 都对应了一条 delete ，每一条 update 也对应一条相反的 update 语句

二种日志均可以视为一种恢复操作，redo_log是恢复提交事务修改的页操作，而undo_log是回滚行记录到特定版本。二者记录的内容也不同，redo_log是物理日志，记录页的物理修改操作，而undo_log是逻辑日志，根据每行记录进行记录。

你知道MySQL 有多少种日志吗？

错误日志：记录出错信息，也记录一些警告信息或者正确的信息。

查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。

慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。

二进制日志：记录对数据库执行更改的所有操作。

中继日志：中继日志也是二进制日志，用来给slave 库恢复

事务日志：重做日志redo和回滚日志undo

分布式事务相关问题

一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。

在MySQL中，使用分布式事务涉及一个或多个资源管理器和一个事务管理器。

如图，MySQL 的分布式事务模型。模型中分三块：应用程序（AP）、资源管理器（RM）、事务管理器（TM）

• 应用程序：定义了事务的边界，指定需要做哪些事务；
• 资源管理器：提供了访问事务的方法，通常一个数据库就是一个资源管理器；
• 事务管理器：协调参与了全局事务中的各个事务。

分布式事务采用两段式提交（two-phase commit）的方式：

• 第一阶段所有的事务节点开始准备，告诉事务管理器ready。
• 第二阶段事务管理器告诉每个节点是commit还是rollback。如果有一个节点失败，就需要全局的节点全部rollback，以此保障事务的原子性。

七、MySQL锁机制

---

数据库的乐观锁和悲观锁？

MySQL 中有哪几种锁，列举一下？

MySQL中InnoDB引擎的行锁是怎么实现的？

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

锁是计算机协调多个进程或线程并发访问某一资源的机制。是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

锁的分类

从对数据操作的类型分类：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
• 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分类：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；

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

适用：从锁的角度来说，表级锁更适合于以查询为主，用于只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量的按索引的条件并发更新少量的不同的数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

	行锁	表锁	页锁
MyISAM		√
BDB		√	√
InnoDB	√	√
Memory		√

MyISAM 表锁

MyISAM 的表锁有两种模式：

• 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

InnoDB 行锁

InnoDB 实现了以下两种类型的行锁：

• 读锁：共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 写锁：排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

索引失效会导致行锁变表锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

加锁机制

乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题

乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式

悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

在业务中如果真的大概率不会发生并发冲突，就使用乐观锁，即多读的情况

锁模式(三种行锁的算法)

• 记录锁(Record Locks)： 单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；

SELECT * FROM table WHERE id = 1 FOR UPDATE;
复制代码

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行

在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

-- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;

• 间隙锁（Gap Locks）： 当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做“间隙”。InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。为了防止同一事务的两次当前读，出现幻读的情况。间隙锁基于Next-Key Locking 算法：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

• 临键锁(Next-key Locks)： 临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。(临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC(读已提交)，临键锁则也会失效。

对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

MySQL 遇到过死锁问题吗，你是如何解决的？

死锁

死锁产生：

• 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
• 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
• 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

MyISAM避免死锁：

• 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

InnoDB避免死锁：

• 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会

八、MySQL调优

---

日常工作中你是怎么优化SQL的？

SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？

如何写sql能够有效的使用到复合索引？

一条sql执行过长的时间，你如何优化，从哪些方面入手？

什么是最左前缀原则？什么是最左匹配原则？

影响mysql的性能因素

• 业务需求对MySQL的影响(合适合度)
• 存储定位对MySQL的影响
  • 不适合放进MySQL的数据
    • 二进制多媒体数据
    • 流水队列数据
    • 超大文本数据
  • 需要放进缓存的数据
    • 系统各种配置及规则数据
    • 活跃用户的基本信息数据
    • 活跃用户的个性化定制信息数据
    • 准实时的统计信息数据
    • 其他一些访问频繁但变更较少的数据
• 模式设计对系统的性能影响
  • 尽量减少对数据库访问的请求
  • 尽量减少无用数据的查询请求
• 硬件环境对系统性能的影响

性能分析

MySQL Query Optimizer

MySQL 中有专门负责优化 SELECT 语句的优化器模块，当客户端向 MySQL 请求一条 Query，命令解析器模块完成请求分类，区别出是 SELECT 并转发给 MySQL Query Optimize 读时，MySQL Query Optimizer 首先会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint(提示) 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。

MySQL常见瓶颈

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈

性能下降SQL慢 执行时间长 等待时间长 原因分析

• 查询语句写的烂
• 索引失效（单值、复合）
• 关联查询太多join（设计缺陷或不得已的需求）
• 服务器调优及各个参数设置（缓冲、线程数等）

查询中哪些情况不会使用索引？

性能优化

索引优化

1. 全值匹配我最爱，最左前缀要遵守；

• 建立几个复合索引字段，最好是都使用上并且按照顺序来。（走全部索引，当然不按照顺序来也可以实现按照顺序查询出来的结果）
• 如果设置了多列索引，要遵循最左前缀原则。具体指查询从索引的最左列开始，不跳过索引中间列，最左侧的列不可以没有。（同2）

2. 带头大哥不能死，中间兄弟不能断；

3. 索引列上少计算，范围之后全失效；

   • 不要在索引列上做任何操作计算、函数、（自动或手动）类型转换，这样会导致索引失效而转向全表扫描。
   • 存储引擎不能使用索引中范围条件右边的列。范围条件的列相当于中间的兄弟断了，但是有点区别，范围列的索引还是有效的，范围列之后的索引全部失效。

4. Like百分写最右，覆盖索引不写星；

   • 要模糊查询只有在查询条件的后面加%索引才生效。但是只在查询条件后面加%明显不符合我们的需求，可以通过覆盖索引的方式解决索引丢失的问题，但是也是存在局限性的，如果查询字段中包含了非索引字段，那么索引还是会失效。

5. 不等空值还有or，索引失效要少用；

   • MySQL在使用不等于（!=或者<>）的时候无法使用索引导致索引范围扫描。is null和not null和or也不可以使用索引

6. VAR引号不可丢，SQL高级也不难！

   • var引号不可丢是说查询的索引字段的如果是字符串类型的需要加上单引号虽然不加也能查到，但是索引会失效。

查询优化

永远小表驱动大表（小的数据集驱动大的数据集）

select * from A where id in (select id from B)
select * from A where exists (select 1 from B where B.id=A.id)
# 当 B 表的数据集小于 A 表的数据集时，用 in 优于 exists, 当 B 表的数据集大于 A 表的数据集时，用 exists优于用 in

order by关键字优化

• 尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀
• 语句使用索引最左前列或使用where子句与ORDER BY子句条件列组合满足索引最左前列

GROUP BY关键字优化

• group by实质是先排序后进行分组，遵照索引建的最佳左前缀

• where高于having，能写在where限定的条件就不要去having限定了

数据类型优化

MySQL 支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

• 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。

  简单就好：简单的数据类型通常需要更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较复杂。

• 尽量避免NULL：通常情况下最好指定列为NOT NULL

九、分区、分表、分库

---

分区

当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率

能干嘛

• 逻辑数据分割
• 提高单一的写和读应用速度
• 提高分区范围读查询的速度
• 分割数据能够有多个不同的物理文件路径
• 高效的保存历史数据

看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

• 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
• 一旦数据并发量上来，如果在分区表实施关联，就是一个灾难
• 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

分表

分表有两种分割方式，一种垂直拆分，另一种水平拆分。

• 垂直拆分

  垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。

• 水平拆分(数据分片)

  单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。

  水平分割的几种方法：

  • 使用MD5哈希，做法是对UID进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。
  • 还可根据时间放入不同的表，比如：article_201601，article_201602。
  • 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。
  • 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。

分库

为什么要分库?

数据库集群环境后都是多台 slave，基本满足了读取操作； 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

分库是什么？

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

优点：

• 减少增量数据写入时的锁对查询的影响
• 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

分库分表后的难题

分布式事务的问题，数据的完整性和一致性问题。

• 用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。
• 跨库联合查询的问题，可能需要两次查询。
• 跨节点的count、order by、group by以及聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并。
• 额外的数据管理负担，如：访问数据表的导航定位。
• 额外的数据运算压力，如：需要在多个节点执行，然后再合并计算程序编码开发难度提升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。

可以使用成熟的中间件：ShardingSphere（客户端，jar包集成）、Mycat（中间件，需要单独部署）解决。

分库分表后id主键如何处理

使用snowflake(雪花) 算法是 twitter 开源的分布式 id ⽣成算法，采⽤ Scala 语⾔实现，是把⼀个 64 位的 long 型的 id划分成多段：1 个 bit 是不⽤的，⽤其中的 41 bit 作为毫秒数，⽤ 10 bit 作为⼯作机器 id，12 bit 作为序列号。一毫秒内能够生成4096个id。

SnowFlake算法的优点：

1. 生成ID时不依赖于DB，完全在内存生成，高性能高可用。

2. ID呈趋势递增，后续插入索引树的时候性能较好。

SnowFlake算法的缺点：

​ 依赖于系统时钟的一致性。如果某台机器的系统时钟回拨，有可能造成ID冲突，或者ID乱序。

十、主从复制

---

复制的基本原理

• slave (从库)会从 master(主库) 读取 binlog(二进制日志文件) 来进行数据同步

• 三个步骤

  1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
  2. salve 将 master 的 binary log events 拷贝到它的中继日志（relay log）；
  3. slave 重做中继日志中的事件，将改变应用到自己的数据库中。MySQL 复制是异步且是串行化的。

从库生成两个线程，一个I/O线程，一个SQL线程；

• i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；
• 主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；
• SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

复制的基本原则

• 每个 slave只有一个 master
• 每个 salve只能有一个唯一的服务器 ID
• 每个master可以有多个salve

复制用途

• 实时灾备，用于故障切换
• 读写分离，提供查询服务
• 备份，避免影响业务

复制的最大问题

• 主库宕机后，数据可能丢失
• 从库只有一个sql Thread，主库写压力大，复制很可能延时

解决方法：

• 半同步复制—解决数据丢失的问题

  半同步复制原理

  • 事务在主库写完binlog后需要从库返回一个已接受，才放回给客户端；
  • 5.5集成到mysql，以插件的形式存在，需要单独安装
  • 确保事务提交后binlog至少传输到一个从库
  • 不保证从库应用完成这个事务的binlog
  • 性能有一定的降低
  • 网络异常或从库宕机，卡主库，直到超时或从库恢复

• 并行复制—-解决从库复制延迟的问题

  • 社区版5.6中新增
  • 并行是指从库多线程apply binlog库级别并行应用binlog，同一个库数据更改还是串行的(5.7版并行复制基于事务组)设置

十一、其他问题

---

三个范式

1．第一范式1NF(确保每列保持原子性)

• 每一列属性都是不可再分的属性值，确保每一列的原子性

• 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。

  根据实际需求设计的字段值不可再分解。比如“部门”和“角色”在需求中都较常用，应该分开两个字段。

  

2．第二范式2NF(确保表中的每列都和主键相关)（拆多表）

• 第二范式是说一张表中包含了多种不同的实体属性，那么要必须分成多张表

• 一个数据库表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。订单表和商品表不能设计在一张表里，应该分开两个表，再加一个多对多的商品id和订单id关联表。（一个表只描述一件事情）

3．第三范式3NF(在2NF的基础上加外键)

• 第三范式（3NF）：必须先满足第二范式（2NF），要求：表中的每一列只与主键直接相关而不是间接相关，（表中的每一列只能依赖于主键）；

• 例如：订单表中需要有客户相关信息，在分离出客户表之后，订单表中只需要有一个用户id即可（外键），而不能有其他的客户信息。因为其他的客户信息直接关联于用户id，而不是直接与订单id直接相关。

• 订单表中的用户信息不直接设计字段，设计一个用户id字段再外键关联用户表即可。

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件，所以当我们对数据的增加，修改，删除，都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候先删除索引，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

1. 所以我们想要删除百万数据的时候可以先保存当前索引的DDL再删除索引（此时大概耗时三分多钟）
2. 然后根据使用的删除条件建立一个临时的索引，以此建立提高删除效率的索引，打造围绕一个字段的聚集型索引，以此为基地大大提升删除删除其中无用数据的效率（此过程需要不到两分钟）
3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断，一切删除会回滚。那更是坑了。