目录

1. InnoDb和Myisam的区别

2. 日志

2.1 redolog

3.锁

3.1 InnoDb行锁

3.2 间隙锁

3.3 S锁、X锁

3.4 死锁

4 MVCC

5. 索引

5.2 主键索引

索引下推

各种索引

聚簇索引

主键索引

唯一索引

索引优化

执行计划

bufferpool

mysql架构

主从复制

分库分表

中间件

1. InnoDb和Myisam的区别

myisam支持全文检索，数据压缩，空间函数，只支持表锁，适用于只读场景，分析类

不支持崩溃恢复，索引和数据文件独立（无聚簇索引的说法)

innodb支持行锁，事务，隔离级别，支持外键（互联网项目一般不用外键，用业务代码控制），支持崩溃恢复，聚簇索引里面存储了完整数据。

2. 日志

二进制日志（binlog），重做日志（redolog），回滚日志（undolog），慢查询日志（查询慢long_query_time和没使用索引log_queries_not_using_indexes），错误日志

binlog属于server的，redolog和undolog属于innodb的

2.1 redolog

在数据更新的时候，数据都是分散磁盘各个位置，这里有IO和数据落盘，效率上并不高。所以mysql通过日志先行的方式，来提高落盘效率和实现崩溃恢复，当redolog写入后，持久化就成功了，崩溃恢复通过redolog实现。

redolog是顺序写io所以效率高，redolog处理数据的大致流程是这样，修改数据时，先记录到redologbuffer，然后写入内核的pagecache,然后异步刷盘到redolog文件上，在这过程中刷盘到redolog磁盘文件上，才算真正数据不会丢失。所以在事务提交时，可以配置为三个级别，可以为了提高性能，而修改配置

1. 事务提交时，修改只存在redologbuffer中

2. 事务提交时，修改存在redologbuffer，并且写入页缓存，并且刷盘成功（默认）

3. 事务提交时，修改写入页缓存

异步刷盘的次数也会影响性能，所以redolog有个组提交的概念，一次调用刷盘，刷多个事务。redologfile刷盘写入是循环写入的，比如文件file1，file2,file3，如果file3满了，则会覆盖掉file1，因为file1的真实数据已经落盘，file1的redolog也就没什么用了

3.锁

https://blog.csdn.net/qq_40378034/article/details/90904573

3.1 InnoDb行锁

1、mysql的行锁，是遵循两阶段提交，事务A操作行时，事务B想要写的时候，等A提交后，B事务的update会阻塞到A事务提交后才提交。

2、当事务需要锁多行，会造成锁冲突，把最有可能影响并发的的锁尽量后放，

比如用户需要看电影的时候，用户A买电影票，A账户需要扣钱，影院B需要收入钱，写交易日志。用户C需要买票的话，B会收入钱。那么事务原子性，会让A的3个步骤一起提交，为了较少锁竞争，把A事务的B也会收入钱放到最后一步执行，减少锁竞争

4、可以通过start transaction with consistent snapshot开启快照读

3.2 间隙锁

1. 为了解决幻读问题，引入了间隙锁，简单理解为，不仅锁住行，还要锁住每行之间让其插入数据，或者删除数据。

2. 行锁+间隙锁就等于next-key lock

3. 最开始加锁是next-key lock ，也就是行锁+间隙锁，锁定的数据行是这次事务的最多，会根据不同的规则退化为行锁或者间隙锁。

4. next-key lock 加锁规则

原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间

原则2：查找过程中访问到的对象才会加锁

优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁

3.3 S锁、X锁

SELECT ... LOCK IN SHARE MODE; 这种读取需要对记录上  S 锁。

SELECT ... FOR UPDATE; 这种需要对记录上 X 锁。

叫意向锁（Intention Locks）的东西。

• IS（Intention Shared Lock），共享意向锁

• IX（Intention Exclusive Lock），独占意向锁。

这两个锁是表级别的锁，当需要对表中的某条记录上 S 锁的时候，先在表上加个 IS 锁，表明此时表内有 S 锁。当需要对表中的某条记录上 X 锁的时候，先在表上加个 IX 锁，表明此时表内有 X 锁。

IS 和 IX 的作用就是在上表级锁的时候，可以快速判断是否可以上锁，而不需要遍历表中的所有记录。

3.4 死锁

3、两阶段提交，两个事务，事务A操作两行数据col1,col2，那么事务B也操作col2,col1。这个时候由于两阶段提交，很有可能造成死锁。

mysql通过两种配置，一种是配置事务超时，默认50s，一种是死锁检测，主动回滚某一个死锁链，让事务执行下去。

而死锁检测，在高并发场景中，很多事务来更新同一行记录的时候，都会进行死锁检测，如果确认是不会产生死锁，可以关掉死锁检测。或者将一行改成逻辑多行，这样减少冲突概率，比如影院账户总和，改成10行，这样就锁冲突就会变成1/10。

4 MVCC

1. mvcc多版本并发控制，为了提高并发性能，用最好的手段处理读写冲突，让即使有读写冲突，也可以不通过加锁实现非阻塞并发读。维持一个数据的多个版本，让读写不冲突

2. 数据库并发场景有三种

   1. 读-读

   2. 读-写，事务隔离性，会造成脏读，不可重复读，幻读

   3. 写-写，会存在更新丢失的问题，A事务撤销后，把已经提交的B事务覆盖了

3. 当前读是读取最新版本，快照读读某个版本的快照

4. mvcc通过三个隐式字段，readview,undolog实现的。

5. 三个隐式字段是当前事务ID，row_id，回滚指针

6. readview,就是当时事务开始的那一刻，生成的历史快照，记录并维护当前活跃事务的id列表，readview是维护数据可见性的关键

   1. readview记录当前快照最小事务id，当前快照最大事务id，和当前快照下所有活跃事务id列表，还有当前事务id

   2. 当查询的事务id等于当前事务id，则可见

   3. 当查询的事务id大于等于最大事务id，则不可见

   4. 当查询的事务id在最大和最小之间，如果存在于活跃事务ids列表中，则未提交不可见，如果不存在则事务提交了，可见

   5. 当查询的事务id小于最小事务id，则说明已经提交，可见

7. RR和RC的区别

   1. RR是通过快照读，得到当前事务下的整体快照，然后生成一个readview，后续的查询都是在这个readview得到的，不会得到超过当前事务的的新事务。

   2. RC快照读生成的readview都是最新的

8. mvcc如何在RR隔离级别下解决的幻读，通过间隙锁解决的。mvcc在一种情况下，RR也会出现幻读，就是a事务执行查询，查询，和不加条件更新（比如set name='xhc'），再查询，B事务在第一次查询之后，提交之前，进行提交。

5. 索引

5.1 B树特性和B+树特性

对于一个M阶的B树的基本定义：

1. 所有叶子节点都在同一层级；
2. 除了根节点以外的其他节点包含的key值数量在[m/2]-1到m-1的数据范围；
3. 除了根节点和叶子节点外，所有中间节点至少有m/2个孩子节点；
4. 根节点如果不是叶子节点的话，它必须包含至少2个孩子节点；
5. 拥有n-1个key值非叶子节点必须有n个孩子节点；
6. 一个节点的所有key值必须是升序排序的；

B+树既然是对B树的一种变种树，它与B树的差异在于：

• 有k个子结点的结点必然有k个关键码；
• 非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中。
• 树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录。

5.2 主键索引

非叶子节点存储主键索引和页号

叶子节点存储完整的数据

叶子节点间是双向链表，便于范围查询

叶子节点内部有页目录，内部记录是单链表链接，通过二分查找记录

B+树只能快速定位到页目录，而不是记录，内部需要二分查找

页的大小是16k，是按照主键大小排序的，如果无序的记录插入，会导致插入到页中间，又因为容量有限还有可能导致页分裂，性能较差，所以要求主键有序

索引下推

只能作用在辅助索引上，如果通过辅助索引查询出来的数据需要回表，而where又不止一个查询条件，为了减少回表的数量，并且在回表得到数据后在server层做过滤，那么会在辅助索引的时候就进行where的过滤

索引结构

各种索引

1. 索引如何可以提高查询速度，把数据比作书的内容，那么索引就是书的书签，肯定通过书签找到书中具体的内容是最快的，那么索引是如何实现的呢。

   1. 首先我们得知道两个常识，读内存是比读磁盘快得多，索引文件和数据文件都是存在磁盘中的

   2. 在每一次查询的时候，innodb，需要将磁盘中索引文件中的内容加载到内存中，磁盘每次加载最小单位页为4kb，mysql设计内存的最小单位页也是4kb，mysql一页大小某人为16kb。

   3. 比如磁盘索引B+树高度为3的时候，它把B+树节点加载到到内存中，就进行一次I/O，而高度为3的这棵树，就只需要三次读磁盘。

   4. 当经过3次io的时候，已经定位到索引的位置，通过索引去定位行数据快速得多了

   5. 而通过分析B+树的特点，我们可以得出以下几个结论

      1. 只有叶子节点有数据，这样非叶子节点一次i/o的数据加载得就多得多了，这样可以减少树的高度，就是减少查询磁盘

      2. B+树的叶子节点的是双向链表，在进行范围查询，比如索引列（id=5）,进行过3次i/o定位到5所在的节点后，通过双向链表的前后可以对5这一列进行范围查询和定位，这也是为什么要定义自增主键的一个原因。定义自定义主键还有个原因就是，如果不是自增主键，为了保证索引有序，需要把数据放在正确的位置，这样就会出现在一页数据满了，新增一页还要移动上一页的部分数据，效率极低，主键有序不会往中间的页插入数据。

      3. B+树的非叶子节点之间也是双向链表，在进行范围查询时，上层的节点能更快的定位范围。并且在走多个条件范围索引时，进行查询优化时，快速的通过上层节点和部分叶子节点，快速估算出所有数据量，然后来判断走哪一个索引。

      4. 页的内部是槽，通过二分定位到槽，槽再去查看记录，记录里面是单链表。

   6. 而B+树，就是查询算法的一个实现，其中还有其他查询算法比如redis zet底层跳表实现，二分查找实现的二叉树，各种平衡二叉树等，B+树能作为数据库索引的最大原因就是他可以有效的减少磁盘查询。

聚簇索引

它的叶子节点除了索引还有数据行。

1. 有主键的情况下 , 主键就是聚簇索引

2. 没有主键的情况下 , 第一个非空null的唯一索引就是聚簇索引

3. 如果上面都没有 , 那么就是有一个隐藏的row-id作为聚簇索引

主键索引

其他非主键索引都是辅助索引，辅助索引在B+树上叶子节点内容就是当前索引+主键索引，在辅助索引查询的时候，如果在没有使用到覆盖索引的情况下，需要通过主键索引去回表进行查询

唯一索引

和普通索引，当唯一索引定位到后，不会继续查询，而普通索引需要往后遍历。

索引优化

索引设计的原则？
1.适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2.基数较小的类，索引效果较差，没有必要在此列建立索引
3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
4.不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行
更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
创建索引的原则（重中之重）
索引虽好，但也不是无限制的使用，好符合一下几个原则
1）左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、
between、like)就停止匹配，比如a=1andb=2andc>3andd=4如果建立(a,b,c,d)顺序的索引，d是用不到
索引的，如果建立
(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2）较频繁作为查询条件的字段才去创建索引
3）更新频繁字段不适合创建索引
4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，多也就三种，区分度实在太低)
5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改
原来的索引即可。

6）定义有外键的数据列一定要建立索引。
7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。8）对于定义为text、image和bit的数据类型的列不要建立索引。

使用B+树的好处
由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更
快地缩小查找范围。B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需
要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每
一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

数据库为什么使用B+树而不是B树
B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本
身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引
查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引
使用，其内部结点比B树小，盘块能容纳的结
点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读
写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；
B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，
只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺
序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的
查找路径长度相同，导致每一个关键字的查询效率相当。
B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序
连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁
的，而B树不支持这样的操作。
增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以
有序的链表结构存储，这样可很好提高增删效率。

MySQL中InnoDB引擎的行锁是怎么实现的？
答：InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id不是索引键那么InnoDB将
完成表锁，并发将无从谈起
InnoDB存储引擎的锁的算法有三种
Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身
相关知识点：
1. innodb对于行的查询使用next-key lock
2. Next-locking keying为了解决Phantom Problem幻读问题
3. 当查询的索引含有唯一属性时，将next-key lock降级为record key
4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
5. 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A.
将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

大表数据查询，怎么优化
1. 优化shema、sql语句+索引；
2. 第二加缓存，memcached, redis；
3. 主从复制，读写分离；
4. 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
5. 水平切分，针对数据量大的表，这一步 麻烦， 能考验技术水平，要选择一个合理的sharding key,
为了有好的查询效率，表结构也要改动，
做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全
部的表；

一个复杂查询还是多个简单查询
MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多
使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。
切分查询将一个大的查询分为多个小的相同的查询
一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销。
分解关联查询，让缓存的效率更高。
执行单个查询可以减少锁的竞争。
在应用层做关联更容易对数据库进行拆分。
查询效率会有大幅提升。
较少冗余记录的查询.

MySQL数据库cpu飙升到500%的话他怎么处理？
当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出
占用高的进程，并进行相关处理。
如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在
运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。
一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、
改 sql、改内存参数)之后，再重新跑这些 SQL。
也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情
况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整,比如说限制连接数等

执行计划

bufferpool

bufferpool通过冷热分区和时间窗口来提高缓存命中率

因为磁盘的访问实在是过于慢，而其他程序也要使用页缓存（存在swap的可能）mysql为了提高查询效率，自己划分了一个bufferpool

bufferpool自身容量有限，使用lru进行内存淘汰，在此基础上有一个理论

时间局部性：如果一个数据被访问了，那么接下来被访问的概率很大

空间局部性：如果一个数据被访问了，它周围的数据被访问的概率很大

所以在这个理论之上，有预读功能，读取周边数据，而预读数据+普通lru，会导致原先热点数据变成不够热，而且预读的数据也许没用

在此基础上提出了冷热分区，第一次加载到bufferpool的数据，放在冷区，这样预读的数据页不会顶掉热点数据，在下一次再加载这批数据时，则移动到热区。

而冷热分区，遇到全表扫描时，由于全表扫描后，很快就会再加载一次，但是后面又不会使用这些数据了，这就替换了很多热点数据，降低了缓存命中率。所以为了解决这个问题，加了一个时间窗口，再次访问的时候，时间间隔超过1s，才会将冷区的数据晋升到热区。

mysql架构

主从复制

可以用作故障恢复或者读写分离，一般情况下是访问量大，单机数据库不能支持并发读写，这情况下，可以通过从库来实现读写分离，从库读，主库写。

在主从情况下，可以把主库的索引删除，从库使用索引，达到主库写入优化（因为索引需要维护的，写入新数据，不仅要维护二级索引，还有维护聚簇索引），从库查询优化

读写分离，可以通过代码层面AOP来实现，实现简单，业务灵活

也可以通过中间件来实现，客户端与中间件，中间件与数据库通过sql协议通信，而中间件的出现需要多一个系统维护，还要考虑中间件的单点故障

常用开源中间件：mycat ,mysql官方proxy，360的atlas

主从同步机制

主库：事务提交，更新数据，写入binlog，给客户端相应，dump线程推送binlog给从库

从库：IO线程写binlog进relaylog，sql线程重放relaylog写入数据，给主库返回响应

在这里又三种同步

异步复制：如果数据还未同步到从库，主库挂了，从库晋升到从库，数据丢失

同步复制：当从库返回相应后，才给客户端响应，性能差

半同步复制：成功同步了几个从库后就返回响应

主从同步延迟解决方案

1.二次查询，如果从库查询不到数据，则去主库查询

2.强制写完立马读的查询走主库

3.关键业务都走主库，非关键业务都走从库

分库分表

单机的连接数，网络，io都会成为吞吐量的受限关键，并且数据量越大，查询就慢下来了。

分库：

一个数据库实例拆分为多个数据库实例，这就是分库

一般来说，并发量大起来后，把请求分布到多头实例上，分摊压力

一般情况是按照业务，比如订单库，用户库这种，有时候针对特殊的库，比如某些活动的库，并发比较高，也需要单独分库。

分库的话，存在事务的问题，这里就需要分布式事务姐姐

表join的问题，要么通过业务代码解决，要么通过表字段冗余来解决

分表：

单表数据量大，查询慢，需要拆分为多个表

垂直拆分

把一些不常用的，大的字段拆分出去，这样内存就能存放更多的数据，性能就高了

这里会有关联的问题

水平拆分

把表从数据的维度给拆分出去，而水平拆分有排序、count、分页问题

路由问题

hash路由，对某列hash取值，然后对子表取模路由，然后数据均匀的分布在子表上

缺点:增加子表有问题，需要迁移数据

范围路由

根据不同的范围实现，比如日期，用户id来区分，会存在数据不均匀，某个子表热点时间段数据量特别大的问题

路由表

专门用过路由表，来保存路由关系，比如通过用户id，存储不同的表，优点就是灵活，而缺点就是会多一次查询

全局主键问题

通过指定特定步长来实现，

通过twitter的雪花算法实现，

通过redis的自增也可以实现

通过数据库独立的表实现

分表的时候，sharding key（分表那一列）怎么选择

如果订单表，用UserId作为分表列，如果想要商家id查询属于自己所有的的订单，那么需要查询3分表后的所有子表。这种情况效率非常低，一般做常用查询的数据冗余或者数据加载到其他地方提供查询

中间件