MySQL知识总结
1.基础
MySQL架构
2.存储引擎
InnoDB、MyISAM是最主流的存储引擎，目前DB默认的也是InnoDB，并且MySQL8.0宣布InnoDB存储数据字典，MyISAM剥离。
且DB优化的重心其实也是存储引擎层，Server层变动不大 重点关注InnoDB就够了。
二者区别
| 区别 | InnoDB | MyISAM |
| 事务支持 | 支持 | 不支持 |
| 锁粒度 | 行锁 | 表锁 |
| 并发性 | 高并发 | 低并发 |
| 构成结构&缓存机制 |
|
|
| select count(*) | 需要扫描全表，统计所有行数 | 只需从计数器中读出保存的行数即可 |
InnoDB存储结构
InnoDB逻辑存储结构，所有数据都被逻辑地存放在表空间，表空间又由段，区，页组成。
段
段就是上图的segment区域，常见的段有数据段、索引段、回滚段等，在InnoDB存储引擎中，对段的管理都是由引擎自身所完成的。
区
区就是上图的extent区域，区是由连续的页组成的空间，无论页的大小怎么变，区的大小默认总是为1MB。
为了保证区中的页的连续性，InnoDB存储引擎一次从磁盘申请4-5个区，InnoDB页的大小默认为16kb，即一个区一共有64（1MB/16kb=16）个连续的页。
每个段开始，先用32页（page）大小的碎片页来存放数据，在使用完这些页之后才是64个连续页的申请。这样做的目的是，对于一些小表或者是undo类的段，可以开始申请较小的空间，节约磁盘开销。
页
页就是上图的page区域，也可以叫块。页是InnoDB磁盘管理的最小单位。默认大小为16KB，可以通过参数innodb_page_size来设置。
常见的页类型有：数据页，undo页，系统页，事务数据页，插入缓冲位图页，插入缓冲空闲列表页，未压缩的二进制大对象页，压缩的二进制大对象页等。
3.索引
4.事务
事务其实就是一组DML（Insert、delete、update）语句的集合，默认是自提交模式
开启事务必须以begin命令开始 以comment 或者 rollback 命令结束其中隔离级别由低到高是：读未提交 < 读已提交 < 可重复读 < 串行化
事务特性（ACID）
1.原子性（Atomicity）
事务内的所有操作必须全部提交或者回滚，保证数据库是一致的。
A向B转账，A-100 B+100 这2个动作必须全部提交，或者回滚。
2.一致性（Consistency）
3.隔离性（Isolation）
4.持久性（Durability）
事务隔离级别
InnoDB实现SQL标准的4中隔离级别，用来限定事务内外的哪些改变是可见，不可见的。
低级一般支持更高的并发处理，并且系统开销更低。
默认是REPEATABLE-READ（可重复读）
1.读未提交（read uncommitted）
简称 RU。
在一个事务中可以读取到其他事物未提交的数据变化，叫做脏读现象，生产环境不建议使用
2.读已提交（read committed）
简称 RC
在一个事务中 可以读取到别的事务已提交的数据变化，也叫不可重复读，允许幻读现象发生
3.可重复读（repetable read）
简称 RR 默认的隔离级别
一个事务中，知道事务结束前，都可以读到事务刚开始的数据，并一直不会发生变化 避免了脏读、不可重复读、幻读现象的发生。
4.串行（serializable）
在读数据的行上都需要加上表级共享锁。每次写数据都要加上表级排他锁，降低InnoDB的并发能力，大量我的超时和锁竞争就会发生。不建议使用到生产环境中。
5.锁
InnoDB的锁类型主要有：读锁（共享锁）、写锁（排他锁）、意向锁、和MDL锁。
读锁
事务都可以读 不可写
锁行 或者锁行范围 如果想申请写锁，那就会被阻塞。
写锁
简称X锁
事务获取了一个数据行的写锁，其他事务就不能再获取该行的其他锁，写锁优先级最高。
一些DML语句操作都会对行记录加写锁。
比较特殊的就是select for update 会对行记录加写锁，其他事务不能对上锁的行加任何锁，不然会阻塞。
MDL锁
会话A开启事务 自动获取一个MDL锁，会话B就不能执行任何DDL语句操作
意向锁
在InnoDB中是表级锁，作用和MDL类似，防止事务过程中，执行DDL语句操作而导致数据的不一致。
- 意向共享锁（IS）是指给一个数据行加共享锁前必须先取得该表的IS锁
- 意向排他锁（IX）是指在一个数据行加排他锁前必须先获得该表的IX锁
InnoDB行锁种类
单个行记录的锁（record lock）
锁住的是一行数据（行锁）
间隙锁（Gap lock）
锁的是行范围 不包含本身 在范围内不可写
2者组合叫做next-key lock
普通索引下默认的next-key lock 模式
6.优化
7.面试问题
1. MySQL 数据库使用的索引数据结构是什么？
B+树。
B+Tree是Btree的变体。
B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树（多路搜索树）。在B+树中，所有记录节点都是按键值的大
小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。
B+索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在2～4层，这也就是说查
找某一键值的行记录时最多只需要2到4次IO，因为当前一般的机械磁盘每秒至少可以做100次IO，
2～4次的IO意味着查询时间只需0.02～0.04秒。
数据库中的B+树索引可以分为聚集索引（clustered inex）和辅助索引（secondary index）。
2. 假设每条SQL信息为1kb，主键ID为bigint型，一颗高度为4的b+树能存储多少数据？
在 innodb 存储引擎里面，最小的存储单元是页（page），
一个页的大小是 16KB。如果我们在数据库的命令行输入如下
命令，那么可以返回如右图所示。
这就说明了一个页的大小为 16384B, 也就是 16kb。
假设一行数据的大小是 1k，那么一个页可以存放 16 行这样的数据。那如果想查找某个页里面的一个数据的
话，得首先找到他所在的页。innodb 存储引擎我们都知道使用 B + 树的结构来组织数据。如果是在主键上建
立的索引就是聚簇索引，即只有在叶子节点才存储行数据，而非叶子节点里面的内容其实是键值和指向数据
页的指针。
因此，我们首先解决一个简单一点的问题：那么如果是 2 层的 B + 树，最多可以存储多少行数据？
如果是 2 层的 B + 树，即存在一个根节点和若干个叶子节点，那么这棵 B + 树的存放总记录数为：根节点指
针数 * 单个叶子节点记录行数。因为单个页的大小为 16kb，而一行数据的大小为 1kb，也就是说一页可以存
放 16 行数据。然后因为非叶子节点的结构是：“页指针 + 键值”，我们假设主键 ID 为 bigint 类型，长度为 8
字节（byte），而指针大小在 InnoDB 源码中设置为 6 字节（byte），这样一共 14 字节（byte），因为一个
页可以存放 16k 个 byte，所以一个页可以存放的指针个数为 16384/14=1170 个。因此一个两层的 B + 树可
以存放的数据行的个数为：1170*16=18720（行）。
那么对于高度为3的B+树呢？也就是说第一层的页，即根页可以存放 1170 个指针，然后第二层的每个页也可以
存放 1170 个指针。这样一共可以存放 1170*1170 个指针，所以一共可以存放 1170*1170*16=21902400（2千万
左右） 行记录。也就是说一个三层的 B + 树就可以存放千万级别的数据了。而每经过一个节点都需要 IO 一次，
把这个页数据从磁盘读取到缓存，也就是说读取一个数据只需要三次 IO。
继续来说，高度为4的B+树呢？ 1170*1170*1170*16 约等于 2000万*1000。 5个 2000 万是 1个亿。1000个 2000
万就是 200亿。
3. 为什么选用B+树做索引而不选用二叉树或者B树？
b 树（balance tree）和 b + 树应用在数据库索引，可以认为是 m 叉的多路平衡查找树，但是从理论上讲，二叉
树查找速度和比较次数都是最小的，为什么不用二叉树呢？
因为我们要考虑磁盘 IO 的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，
就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少 IO
次数，对于树来说，IO 次数就是树的高度，而 “矮胖” 就是 b 树的特征之一，它的每个节点最多包含 m 个孩子，
m 称为 b 树的阶。
为什么不用B树呢？
b + 树，是 b 树的一种变体，查询性能更好。
b + 树相比于 b 树的查询优势：
1.b + 树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更 “矮胖”。B 树不管叶子节点还是非叶子节
点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况
下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低；
2.b + 树查询必须查找到叶子节点，b 树只要匹配到即可直接返回。因此 b + 树查找更稳定（并不慢），必须查
找到叶子节点；而B树，如果数据在根节点，最快，在叶子节点最慢，查询效率不稳定。
3.对于范围查找来说，b + 树只需遍历叶子节点链表即可，并且不需要排序操作，因为叶子节点已经对索引进行
了排序操作。b 树却需要重复地中序遍历，找到所有的范围内的节点。
4. 为什么用 B+ 树做索引而不用哈希表做索引？
1、模糊查找不支持：哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，
这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个
表。而 B + 树则可以通过最左前缀原则快速找到对应的数据。
2、范围查找不支持：如果我们要进行范围查找，例如查找 ID 为 100 ~ 400 的人，哈希表同
样不支持，只能遍历全表。
3、哈希冲突问题：索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的
哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大
增加。
5. 什么是聚集索引？
InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每
张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为
数据页。每个数据页都通过一个双向链表来进行链接。
由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优
化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于定义了数
据的逻辑顺序，它对于主键的排序查找和范围查找速度非常快。。叶子节点的数据就是用户所要查询的数据
如:用户需要查询一张注册用户的表，查询最后注册的10位用户，由于B+树索引是双向链表的，用户可以快速
找到最后一个数据页，并取出10条记录
SELECT * FROM Profile ORDER BY id LIMIT 10;
虽然使用ORDER BY对主键id记录进行排序，但是在实际过程中并没有进行所谓的filesort操作，而这就是因为
聚集索引的特点。另一个是范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点
的上层中间节点就可以得到页的范围，之后直接读取数据页即可。如：
SELECT * FROM Profile where id > 1 and id <100;
6. 什么是辅助索引？
对于辅助索引（Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点
除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉
InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此
InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索
引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后
再通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引树中查找数据，那
需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引
树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个
数据页。
7. 什么是离散读？
在某些情况下，当执行EXPLAIN命令进行SQL语句的分析时，会发现优化器并没有选择索引去查找数据，而是通
过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况
下。
假设表：t_index 。其中 id 为主键；c1 与 c2 组成了联合索引（c1，c2）；此外，c1 还是一个单独索引。
进行如下查询操作：
SELECT * FROM t_index WHERE c1＞1 and c1＜100000;
可以看到表t_index有（c1，c2）的联合主键，此外还有对于列c1的单个索引。上述这句SQL显然是可以通过
扫描OrderID上的索引进行数据的查找。然而通过EXPLAIN命令，用户会发现优化器并没有按照OrderID上
的索引来查找数据。
在最后的索引使用中，优化器选择了PRIMARY id 聚集索引，也就是表扫描（table scan），而非c1辅助索
引扫描（index scan）。
这是为什么呢？因为如果强制使用c1索引，就会造成离散读。具体原因在于用户要选取的数据是整行信息，
而c1作为辅助索引不能覆盖到我们要查询的信息，因此在对c1索引查询到指定数据后，还需要一次书签访问
来查找整行数据的信息。虽然c1索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因
此变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问
的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。
8.优化器如何优化离散读？你是如何避免离散读的
MySQL 5.6之前，优化器在进行离散读决策的时候，如果数据量比较大，会选择使用聚集索引，全表扫描。
MySQL5.6版本开始支持Multi-Range Read（MRR）优化。Multi-Range Read优化的目的就是为了减少磁盘的
随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极
大的提升。Multi-Range Read优化可适用于range，ref，eq_ref类型的查询。
MRR优化有以下几个好处：
❑MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按
照主键排序的顺序进行书签查找。
❑减少缓冲池中页被替换的次数。（顺序查找可以对一个页进行顺序查找，无需离散加载数据页）
❑批量处理对键值的查询操作。
对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作，MRR的工作方式如下：
❑将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
❑将缓存中的键值根据RowID进行排序。
❑根据RowID的排序顺序来访问实际的数据文件。
9. 什么是 ICP 优化
和Multi-Range Read一样，Index Condition Pushdown同样是MySQL 5.6开始支持的一种根据索引进行查询的
优化方式。之前的MySQL数据库版本不支持Index Condition Pushdown，当进行索引查询时，首先根据索引
来查找记录，然后再根据WHERE条件来过滤记录。在支持Index Condition Pushdown后，MySQL数据库会在
取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎
层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能。
Index Condition Pushdown优化支持range、ref、eq_ref、ref_or_null类型的查询，当前支持MyISAM和InnoDB
存储引擎。当优化器选择Index Condition Pushdown优化时，可在执行计划的列Extra看到Using index
condition提示。
10. MySQL是如何获取索引所在内存中的数据页的
哈希表，利用哈希算法。
哈希算法是一种常见算法，时间复杂度为O（1），且不只存在于索引中，每个数据库应用中都存在该数
据库结构。
数据库中一般采用除法散列的方法，发生碰撞时采用链地址法。
在哈希函数的除法散列法中，通过取k除以m的余数，将关键字k映射到m个槽的某一个去，即哈希函数为：
h(k)=k mod m
InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方
式。对于缓冲池页的哈希表来说，在缓冲池中的Page页都有一个chain指针，它指向相同哈希函数值的页。
而对于除法散列，m的取值为略大于2倍的缓冲池页数量的质数。例如：当前参数innodb_buffer_pool_size
的大小为10M，则共有640个16KB的页。对于缓冲池页内存的哈希表来说，需要分配640×2=1280个槽，
但是由于1280不是质数，需要取比1280略大的一个质数，应该是1399，所以在启动时会分配1399个槽的
哈希表，用来哈希查询所在缓冲池中的页。
InnoDB存储引擎的表空间都有一个space_id，用户所要查询的应该是某个表空间的某个连续16KB的页，即
偏移量offset。InnoDB存储引擎将space_id左移20位，然后加上这个space_id和offset，即关键字
K=space_id＜＜20+space_id+offset，然后通过除法散列到各个槽中去。
11.什么是自适应哈希索引
自适应哈希索引采用之前讨论的哈希表的方式实现。不同的是，这仅是数据库自身创建并使用的，
DBA本身并不能对其进行干预。自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型
的查找非常快速，如：
SELECT*FROM TABLE WHERE index_col='xxx'
但是对于范围查找就无能为力了。
通过命令
SHOW ENGINE INNODB STATUS
可以看到当前自适应哈希索引的使用状况。
12.redo log 和 bin log有什么区别？bin log做什么用的
在MySQL数据库中还有一种二进制日志（binlog），其用来进行POINT-IN-TIME（PIT）的恢复及主从复制
（Replication）环境的建立。从表面上看其和重做日志非常相似，都是记录了对于数据库操作的日志。然而，从
本质上来看，两者有着非常大的不同。
首先，重做日志是在InnoDB存储引擎层产生，而二进制日志是在MySQL数据库的上层产生的，并且二进制日志不
仅仅针对于InnoDB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。
其次，两种日志记录的内容形式不同。MySQL数据库上层的二进制日志 bin log是一种逻辑日志，其记录的是对应
的SQL语句。而InnoDB存储引擎层面的重做日志是物理格式日志，其记录的是对于每个页的修改。
13. 什么是undo log，有什么用？
重做日志记录了事务的行为，可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就
需要undo。因此在对数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。这样如果用
户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息
将数据回滚到修改之前的样子。
redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段（segment）中，这个段称为
undo段（undo segment）。undo段位于共享表空间内。可以通过py_innodb_page_info.py工具来查看当前共享表
空间中undo的数量。
除了回滚操作，undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取
一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。
最后也是最为重要的一点是，undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因
为undo log也需要持久性的保护。
14. MySQL事务的隔离级别
SQL标准定义的四个隔离级别为：
❑READ UNCOMMITTED（导致脏读）
❑READ COMMITTED （导致幻读）
❑REPEATABLE READ （默认使用，避免幻读，也能避免脏读）
❑SERIALIZABLE（更高级别隔离，避免幻读，避免脏读）
InnoDB存储引擎默认支持的隔离级别是REPEATABLE READ，但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事
务隔离级别下，使用Next-Key Lock锁的算法，因此避免幻读的产生。这与其他数据库系统（如Microsoft SQL Server数据库）是
不同的。所以说，InnoDB存储引擎在默认的REPEATABLE READ的事务隔离级别下已经能完全保证事务的隔离性要求，即达到
SQL标准的SERIALIZABLE隔离级别。
隔离级别越低，事务请求的锁越少或保持锁的时间就越短。这也是为什么大多数数据库系统默认的事务隔离级别是READ
COMMITTED。
在InnoDB存储引擎中，可以使用以下命令来设置当前会话或全局的事务隔离级别：
SET[GLOBAL|SESSION]TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
在SERIALIABLE的事务隔离级别，InnoDB存储引擎会对每个SELECT语句后自动加上LOCK IN SHARE MODE，即为每个读取操
作加一个共享锁。因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。
15.说说Innodb 和 mylsam 存储引擎的主要区别
1. 是否支持行级锁 : MyISAM 只有表级锁 (table-level locking)，而 InnoDB 支持行级锁 (row-
level locking) 和表级锁 , 默认为行级锁。
2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性 , 其执
行速度比 InnoDB 类型更快，但是不提供事务支持。但是 InnoDB 提供事务支持事务，外部
键等高级数据库功能。具有事务 (commit)、回滚 (rollback) 和崩溃修复能力 (crash recovery
capabilities) 的事务安全 (transaction-safe (ACID compliant)) 型表。
3. 是否支持外键： MyISAM 不支持，而 InnoDB 支持。
4. 是否支持 MVCC：仅 InnoDB 支持。应对高并发事务 , MVCC 比单纯的加锁更高效 ;MVCC
只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作 ;MVCC 可以使用乐观
(optimistic) 锁和悲观 (pessimistic) 锁来实现 ; 各数据库中 MVCC 实现并不统一。
浙公网安备 33010602011771号