mysql总结

一、MyISAM和InnoDB的理解

1、count运算上的区别

2、是否支持事务和崩溃后的快速恢复：MyISAM不支持，InnoDB支持

3、是否支持外键：MyISAM不支持，InnoDB支持

MyISAM更适合读密集的表，InnoDB更适合写密集的表。

二、数据库索引

什么是索引？

索引是一种数据结构，用来帮数据库高效获取数据。一般是key-value结构，key是索引值，value是指向数据库物理地址的指针

MySql索引使用的数据结构主要有B-Tree索引和哈希索引。在绝大多数需求为单挑记录查询时，可以选哈希索引，查询性能最快；其他情况一般使用B树索引。

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引和全文索引。

普通索引是基础的索引，没有任何约束，主要用于提高查询效率。唯一索引就是在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引。主键索引在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，一张表里最多只有一个主键索引。

为什么数据库要用B+树索引？

如果不走索引，那就是全表扫描，效率很低

1、二叉树：key是索引字段的数据，value是索引所在行的磁盘文件地址。缺点：如果是依次递增的数据列，效率和全表扫描差不多

2、哈希表：单条查询效率很高，但是不适用于返回查询，而且可能有哈希冲突的问题

3、红黑树：精确定位和范围查询都很优秀，但是如果数据量很大，树的层级会很高，IO读写的次数会变多，性能劣化严重

4、B树：是一个绝对平衡的多路树。

度：一个节点拥有的子节点（子树）的数量。

阶：一个节点的子节点的最大个数

一棵m阶B树是一个平衡的m路搜索树。它可能是空树，或者满足以下特点：

1、除了根节点和叶子节点外，其他每个节点至少有m/2个子节点（向上取整）

2、每个非根节点包含关键字个数：m/2 - 1 <= j <= m - 1

3、节点关键字从左到右递增排列，有k个关键字的非叶子节点正好有k+1个子节点

4、所有叶子节点位于同一层

与二叉树的区别：二叉树是每个节点上有一个关键字和两个分支，B树上每个节点有k个关键字和k+1个分支

B树的查找分为两步：

1、首先查找节点，由于B树通常是在磁盘上存储的，所以这一步需要IO操作

2、查找关键字，当找到某个节点后，将该节点读入内存，然后通过顺序查找或者折半查找来查找关键字。若没有找到关键字，则需要判断大小来找到合适的分支继续查找。

插入：

都是在叶子节点上操作。如果当前节点的关键字等于m-1，则需要发生节点分裂，以节点中间的关键字将该节点分为左右两部分，中间的关键字放到父节点中即可

5、B+树

和B树的区别：

1、所有子节点一定会出现在叶子节点上

2、相邻的叶子节点之间，会用一个双向链表连接起来

3、非叶子节点只存储索引，不储存数据，就为放更多的索引

查找：B+树最大的优势就在查找上，范围查找更加明显，只需要查找一遍节点，然后查找一次数据，然后遍历往后找就行；而且因为子节点只放索引，可以放更多索引，减少IO操作。单个查找差不多，默认一个磁盘页大小是16k。

插入：当节点内元素数量大于m-1时，按中间元素分裂成左右两部分，中间元素分裂到父节点当作索引，并且分裂到右边。

删除：删除后，要看关键字是否符合大于m/2小于m-1；因为有链表相连，所以先通过兄弟节点移动获得，再更新父节点，如果兄弟节点不够用，就先和兄弟节点合并， 再删除父节点中的关键字

总结：

1、单个节点存储越多元素，IO就越少

2、每次查询都会到达叶子节点，所以性能稳定

3、叶子节点通过双向链表相连，范围查询非常方便

MyISAM索引实现：

使用的是非聚簇的B+树索引，叶子节点存放的value是数据记录的地址而非数据本身

InnoDB索引实现：

使用的是聚簇的B+树索引，表数据本身就是按B+树组织的一个索引结构，data保存了完整的数据记录，key是数据表的主键。所以建议主键不要太长，主键最好自增。

最左前缀原理：

在联合索引中，建立联合索引时回遵循最左前缀索引。

全值匹配：用到了所有的索引值，优化器会自动排序成和索引一样的顺序

匹配最左边的列：只有联合索引中的某个值，遵循最左匹配

匹配列前缀：如果是字符型，那么前缀匹配用的索引，中缀和后缀用的全表扫描

三、乐观锁和悲观锁

悲观锁：假设会发生并发冲突，屏蔽一切可能违反数据完整性的操作

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。适用于写比较少的情况

1、版本号机制：在数据表中增加version字段，表示被修改次数。当线程A要更改一个数据时，version字段读写前后需要一致才能更新，否则重试

2、CAS算法：是一种无锁算法进行线程之间的变量同步。只有当被修改的内存值等于想要的值A时，才用新值B去更新内存的值

四、事务隔离和MVCC

事务

事务由一个有限的数据库操作序列构成，这些操作要么全部执行，要么全部不执行（原子性）

• 原子性： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部都执行，要么都不执行。
• 一致性： 指在事务开始之前和事务结束以后，数据不会被破坏，应用系统从一个正确的状态到另一个正确的状态。
• 隔离性： 多个事务并发访问时，事务之间是相互隔离的，一个事务不应该被其他事务干扰，多个并发事务之间要相互隔离。。
• 持久性： 表示事务完成提交后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

事务并发可能问题：

脏读：一个事务读到了另外一个并行事务尚未提交的修改。如果另一个事务回滚了操作，那么事务A读到的数据就是不存在的

不可重复读：一个事务读到了另外一个并行事务提交的修改

幻读：一个事务前后两次读数据记录数目不一样，把另一个事务插入的记录也读进来了

事务的隔离级别：

读未提交：事务A可以读到事务B修改过且未提交的数据（三种问题都会发生）

读已提交：事务A可以读到事务B修改过且提交了的数据（解决了脏读的问题）

可重复读：事务A只能在事务B修改并提交后，自己也提交事务后，才能读到事务B修改的内容（解决了脏读和不可重复读，解决了快照读的幻读，但无法解决当前读的幻读，如果事务B插入了一条语句，事务A可以直接更新，并且再次查询会返回对应语句）

可串行化：读读操作不会阻塞；读写，写读，写写操作都会被阻塞（各种问题都不会发生，通过加锁实现）

MVCC

全称是Multi-Version Concurrent Control，即多版本并发控制，在MVCC协议下，每个读操作会看到一个一致性的snapshot，并且可以实现非阻塞的读。MVCC允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务ID，在同一个时间点，不同的事务看到的数据是不同的。

什么是当前读？

它读取的数据库记录，都是当前最新的版本，会对当前读取的数据进行加锁，防止其他事务修改数据。是悲观锁的一种操作。

什么是快照读？

快照读的实现是基于多版本并发控制，即MVCC，既然是多版本，那么快照读读到的数据不一定是当前最新的数据，有可能是之前历史版本的数据。

mvcc实现原理：

在数据库中加了trx_id（最近修改）和roll_pointer（版本链关键）两个字段。trx_id是当前操作该记录的事务的id，roll_pointer是回滚指针，用于配合undo日志，指向上一个旧版本

undo日志

Undo log 主要用于记录数据被修改之前的日志，在表信息修改之前先会把数据拷贝到undo log里。

当事务进行回滚时可以通过undo log 里的日志进行数据还原。

• 保证事务进行rollback时的原子性和一致性，当事务进行回滚的时候可以用undo log的数据进行恢复。
• 用于MVCC快照读的数据，在MVCC多版本控制中，通过读取undo log的历史版本数据可以实现不同事务版本号都拥有自己独立的快照数据版本。

Read View 读视图

事务进行快照读时候产生的视图，在事务执行快照读的那一刻就会在数据库中生成。

记录并维护系统当前活跃事务的ID(trx_id)(没有commit，当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以越新的事务，ID值越大)，是系统中当前不应该被本事务看到的其他事务id列表。

Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

Read View几个属性

trx_ids: 当前系统活跃(未提交)事务版本号集合。
low_limit_id: 创建当前read view 时“当前系统最大事务版本号+1”。
up_limit_id: 创建当前read view 时“系统正处于活跃事务最小版本号”
creator_trx_id: 创建当前read view的事务版本号

可见性判断

1、trx_id < up_limit_id or trx_id == creator_trx_id 可以显示

2、trx_id > low_limit_id 不显示

3、trx_id < low_limit_id 继续判断当前事务在不在活跃事务中（trx_ids）

不存在：说明该read view产生的时候事务已经commit了，可以显示

存在：说明创建rv的时候该事务还在活跃，没有commit，不可以显示

如果是读已提交隔离级别，每次读数据都会重新生成一个read view

如果是可重复读隔离级别，只会在第一次select的时候生成read view

五、数据库锁相关

Mysql中锁的分类按照不同类型的划分可以分成不同的锁，按照**「锁的粒度」划分可以分成：「表锁、页锁、行锁」；按照「使用的方式」划分可以分为：「共享锁」和「排它锁」；按照思想的划分：「乐观锁」和「悲观锁」**。

共享锁和排他锁

InnoDB实现了标准的行级锁，包括共享锁（s锁）和排他锁（x锁）

• 共享锁（S锁）：允许持锁事务读取一行。
• 排他锁（X锁）：允许持锁事务更新或者删除一行。

如果 T1 持有 r 的 x 锁，那么 T2 请求 r 的 x、s 锁都不能被立即允许，T2 必须等待T1释放 x 锁才可以，因为x锁与任何的锁都不兼容。

记录锁

• 记录锁是最简单的行锁，仅仅锁住一行。如： SELECT c1 FROM t WHERE c1=10FOR UPDATE
• 记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB也会隐式的创建一个索引，并使用这个索引实施记录锁。
• 会阻塞其他事务对其插入、更新、删除

间隙锁（Gap Locks）

• 间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。
• 使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
• 间隙锁只阻止其他事务插入到间隙中，他们不阻止其他事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用。

邻键锁：

• Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

RC级别存在幻读分析

RC隔离级别下，加锁的select, update, delete等语句，使用的是记录锁，其他事务的插入依然可以执行，因此会存在幻读~

RR 级别解决幻读分析

RR隔离级别下，加锁的select, update, delete等语句，会使用间隙锁+ 临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录。

六、drop,delete与truncate的区别

drop直接删掉表，truncate删除表中数据，再插入时自增长id又从1开始，delete删除表中数据，可以加where字句。

七、如何对 MySQL 进行服务器扩容

停机扩容

1. APP通知用户在某个时间段停机维护升级。
2. 新建若干个具有高可用的库。
3. 停止当前服务，然后写个数据迁移程序，实现把老库数据全部迁移到新库中。
4. 修改代码路由规则后重新对外提供服务。

在线双写

1. 建立好新到数据库，然后接下来用户在写原有数据库到同时也写一份数据到我们的新库中。
2. 写个数据迁移程序，实现旧库中的历史数据迁移到新库中。
3. 迁移过程中，每次插入数据时，需检测数据的更新情况。比如，如果新的表中没有当前的数据，则直接新增；如果新表有数据并没有我们要迁移的数据新的话，我们就更新为当前数据，只能允许新的数据覆盖旧的数据，推荐使用Canal这样到中间件。
4. 经过一段时间后需要校验新库跟旧库两边数据是否一样。如果检查到一样了，则直接切换即可。

八、分库分表

数据库瓶颈，分为IO瓶颈和CPU瓶颈

IO瓶颈：

磁盘读IO瓶颈，热点数据太多，缓存装不下，每次查询会产生大量的IO -> 分库和垂直分表

网络IO瓶颈，请求的数据太多，网络带宽不够 ->分库

CPU瓶颈：

SQL问题，如SQL中包含join，group by，order by等非索引条件查询，会增加CPU运算的操作 -> SQL优化，建立合适索引，在业务service层进行业务计算

单表数据量太多，查找时扫描的行太多 -> 水平分表

水平分库：

以字段为依据，按照一定的策略（hash， range）将一个库中的数据拆分到多个库中

• 每个库的结构都一样
• 每个库的数据都不一样，没有交集
• 所有库的并集是全量数据

**场景：**系统绝对并发量上来了，分表难以解决问题，并且没有明显的业务归属来垂直分库

分析：库多了，IO和CPU的压力可以成倍缓解

水平分表：

以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中

• 每个表的结构都一样；
• 每个表的数据都不一样，没有交集；
• 所有表的并集是全量数据；

**场景：**系统的绝对并发量没有上来，只是单表数据量太多影响效率，增加了CPU负担

分析：表的数据量减少了，单次SQL执行效率高，减轻CPU负担

垂直分库：

以表为依据，按照业务归属不同，将不同的表拆分到不同的库中

• 每个库的结构都不一样
• 每个库的数据也不一样，没有交集
• 所有库的并集是全量数据

**场景：**系统绝对并发量上来了，并且可以抽象出单独的业务模块

垂直分表：

以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

• 每个表的结构都不一样；
• 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
• 所有表的并集是全量数据

**场景：**系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。

**分析：**可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

九、其他

数据库高并发解决方案

短距离，少数据，分散压力。

短距离

所谓的短距离，指的是从前端到数据库的路径要短。

1. 页面静态。有些页面的数据是在某些时段是不变的，那么这个页面可以静态化，这样可以提高访问的速度。
2. 使用缓存。缓存大家都知道，快的原因就是基于内存。所以使用基于内存的缓存的话，可以减少对数据库的访问，同时加速访问速度。
3. 批量读取。高并发的情况下，可以将多个请求的查询合在一次进行，以减少对数据库的访问速度。
4. 延迟修改。延迟修改的意思高并发的情况西可能是将多次修改数据放在缓存中，然后定时将缓存中的数据过更新到数据库；也可以是通过缓存的同步策略通过解析异步同步到数据库中。
5. 使用索引。这个不用说了，索引有着比较多的类型，例如普通索引/主键索引/组合索引/全文索引等。

少数据

所谓的少数据，其实是查询的数据要少。

1. 分表。所谓的分表，其实有水平切分和垂直拆分。玩过单机的小伙伴都知道，往往一些具有历史性的表单，都会有成百上千万级别的数据。这样子对于 MySQL 来说，即使是加了索引，SQL 方面继续优化，也很难做到更快的查询速度。那么我们可以通过分表的操作来实现。例如说最常见的我们可以根据时间的维度来进行表的水平拆分，今年的数据保持下来，而去年的数据可以存在另外一个表里。
2. 分离活跃数据。其实这个有点类似缓存，但是不同之处在于数据还是在 MySQL 上面的。例如一个查询商品的业务，有一些火爆/经常被搜索的商品可以存在一张活跃表。查询的时候先查询活跃表，没有的话再查询总商品表。
3. 分块。这个分块有点类似于算法里面的“索引顺序查找”。通过数据层面的优化，将数据放在不同的块中，而我们只需要计算找到对应的块就行了。

分散压力

所谓的分散压力，其实是分散不同数据库服务器的压力

1. 集群。集群的概念相信大家都很清楚，对于业务服务器来说其实就是具备相同业务流程的服务器部署多台，通过负载均衡或其他方式来将请求分配到不同服务器。而数据库也一样，通过特定的规则策略将数据导向特定的数据库服务器上。
2. 分布式。所谓的分布式，其实就是将原本处于同个流程的业务逻辑分配到不同的服务器上面执行，达到了“并发”执行的效果，加快执行速度。
3. 分库分表。分库分表主要是水平拆分和垂直拆分。对于访问频率高而数据量巨大的单表，可以减少单表的数据，根据特定的维度进行水平拆分，增加数据库的吞吐量，这就是分表水平拆分；而对于业务耦合性低的多表来说，可以将不同的表存储在不同的数据库上，对数据库进行垂直拆分，提高数据库写的能力，即分库的垂直拆分。
4. 建立主从。建立主从的目的其实就是为了读写分离。我们都知道，只要数据库的事务级别够高，那么并发读是不会影响到数据的混乱，而并发写则会。所以建立主从一般来说，写会留在主服务器上写，而会在从服务器上读。所以基本上让主服务器进行事务性操作，从服务器进行 select 查询。这样子的话，事务性操作（增加/删除/修改）导致的改变更新同步到集群中的从数据库。

一条sql语句的执行过程

写操作

1、连接器

验证用户名和密码是否正确（数据库mysql的user表中进行验证），如果错误返回错误通知，如果正确，则会去 mysql 的权限表（mysql中的 user、db、columns_priv、Host 表，分别存储的是全局级别、数据库级别、表级别、列级别、配合 db 的数据库级别）查询当前用户的权限。

2、缓存

也称为查询缓存，存储的数据是以键值对的形式进行存储，如果开启了缓存，那么在一条查询sql语句进来时会先判断缓存中是否包含当前的sql语句键值对，如果存在直接将其对应的结果返回，如果不存在再执行后面一系列操作。如果没有开启则直接跳过。

3、分析器

对客户端传来的 sql 进行分析，这将包括预处理与解析过程，并进行关键词的提取、解析，并组成一个解析树。具体的解析词包括但不局限于 select/update/delete/or/in/where/group by/having/count/limit 等，如果分析到语法错误，会直接抛给客户端异常：ERROR:You have an error in your SQL syntax.这个阶段也会做一些校验（表是否存在，字段是否存在）

4、优化器

进入优化器说明sql语句是符合标准语义规则并且可以执行。优化器会根据执行计划选择最优的选择，匹配合适的索引，选择最佳的方案。

5、执行器

三个日志的比较（undo、redo、bin）

1、undo log是用于事务的回滚、保证事务隔离级别读已提交、可重复读实现的。redo log是用于对暂不更新到磁盘上的操作进行记录，使得其可以延迟落盘，保证程序的效率。bin log是对数据操作进行备份恢复（并不能依靠 bin log 直接完成数据恢复）。

2、undo log 与 redo log 是存储引擎层的日志，只能在 InnoDB 下使用；而bin log 是 Server 层的日志，可以在任何引擎下使用。

3、redo log 大小有限，超过后会循环写；另外两个大小不会。

4、undo log 记录的是行记录变化前的数据；redo log 记录的是 sql 的数据页修改逻辑以及 change buffer 的变更；bin log记录操作语句对具体行的操作以及操作前的整行信息（5.7默认）或者sql语句。

5、单独的 binlog 没有 crash-safe 能力，也就是在异常断电后，之前已经提交但未更新的事务操作到磁盘的操作会丢失，也就是主从复制的一致性无法保障，而 redo log 有 crash-safe 能力，通过与 redo log 的配合实现 “三步提交”，就可以让主从库的数据也能保证一致性。

6、redo log 是物理日志，它记录的是数据页修改逻辑以及 change buffer 的变更，只能在当前存储引擎下使用，而 binlog 是逻辑日志，它记录的是操作语句涉及的每一行修改前后的值，在任何存储引擎下都可以使用。

MySQL 是 WAL（Write-Ahead Logging）机制，也就是写操作会先存入日志，然后再写入磁盘，这样可以避开高峰，提高数据库的可用性。

读操作

在 MySQL 5.6开始，引入了一种索引优化策略——索引下推，其本质优化的就是 Where 条件的提取。在提取时，会将 Where 条件拆分为 Index Key（First Key & Last Key）、Index Filter 与 Table Filter。

1、Index Key

用于确定 SQL 查询在索引中的连续范围（起始点 + 终止点）的查询条件，被称之为Index Key；由于一个范围，至少包含一个起始条件与一个终止条件，因此 Index Key 也被拆分为 Index First Key 和 Index Last Key，分别用于定位索引查找的起始点以终止点

index First Key

用于确定索引查询范围的起始点；提取规则：从索引的第一个键值开始，检查其在 where 条件中是否存在，若存在并且条件是 =、>=，则将对应的条件加入Index First Key之中，继续读取索引的下一个键值，使用同样的提取规则；若存在并且条件是 >，则将对应的条件加入 Index First Key 中，同时终止 Index First Key 的提取；若不存在，同样终止 Index First Key 的提取

Index Last Key

用于确定索引查询范围的终止点，与 Index First Key 正好相反；提取规则：从索引的第一个键值开始，检查其在 where 条件中是否存在，若存在并且条件是 =、<=，则将对应条件加入到 Index Last Key 中，继续提取索引的下一个键值，使用同样的提取规则；若存在并且条件是 < ，则将条件加入到 Index Last Key 中，同时终止提取；若不存在，同样终止Index Last Key的提取

2、Index Filter

ndex Filter 用于索引范围确定后，确定 SQL 中还有哪些条件可以使用索引来过滤；提取规则：从索引列的第一列开始，检查其在 where 条件中是否存在，若存在并且 where 条件仅为 =，则跳过第一列继续检查索引下一列，下一索引列采取与索引第一列同样的提取规则；若 where 条件为 >=、>、<、<= 其中的几种，则跳过索引第一列，将其余 where 条件中索引相关列全部加入到 Index Filter 之中；若索引第一列的 where 条件包含 =、>=、>、<、<= 之外的条件，则将此条件以及其余 where 条件中索引相关列全部加入到 Index Filter 之中；若第一列不包含查询条件，则将所有索引相关条件均加入到 Index Filter之中

3、Table Filter

这个就比较简单了，where 中不能被索引过滤的条件都归为此中；提取规则：所有不属于索引列的查询条件，均归为 Table Filter 之中