[关闭]
@boothsun 2018-04-01T08:39:19.000000Z 字数 6910 阅读 1070

MySQL（已完）

面试题

MyISAM与InnoDB对比

事务

MyISAM不支持事务，而InnoDB是支持事务的，InnoDB的事务是通过锁 + redo和undo log实现的。（这里可以展开讲一下InnoDB对事务的实现原理）

锁粒度

MyISAM只支持表级锁，而InnoDB支持行级锁+表级锁。（这里可以讲一下InnoDB中行级锁的实现原理和意向锁、共享锁、排他锁，讲一下MVCC的实现原理）

外键

MyISAM不支持外键，但是InnoDB是支持的。

但是互联网公司的实际项目中，还是比较少用外键的。外键使表与表之间太耦合了，update, delete等操作都会涉及相关的表操作。影响sql 的性能，甚至会造成死锁。高并发的情况下，当存在外键约束的时候，每次要去扫描此记录是否合格，扫描的数量很可能指数级增长，常见的解决办法都是通过应用程序来维护这种关联关系，开启事务去处理这些具有外键关联关系的表，有需要还可以进行异步操作来提供性能或者是补充机制。（具体参见为什么很多mysql课程不推荐用物理外键？）

全表行数查询

MyISAM通过内置计数器保存具体行数，但InnoDB是通过全表扫描来计算得出的。但是这只是对select count(*) from tableName这种不带where条件的查询才有效，对于有where条件的count(*)，MyISAM也只能和InnoDB一样做全表扫描。

数据构成上

每个MyISAM在磁盘上存储为三个文件，文件名字都是以表名开头。 主要分为以下三种： .frm（表定义）和 .MYD（MyData 表数据）和 .MYI（My Index 索引文件），从这里就可以看出MyISAM中索引和数据是分开的，并且MyISAM有索引的前缀压缩技术，使得索引会很小，更适合做索引的内存缓存。（这里可以对前缀压缩技术进行展开讲解）

InnoDB则使用了聚簇索引，并且没有使用前缀压缩从而使得InnoDB文件相对MyISAM体积更庞大。（这里可以对聚簇索引展开讲）

索引存储上

MyISAM索引文件中存储的是数据行的指针，但是InnoDB存储的是主键。

  1. 存储指针的缺点：

    • 页分裂可能造成指针变化，会需要更新这些指针，也就是说 update delete insert操作都可能造成指针更新。

  2. 存储指针的优点：

    • 快速查找。

  3. 存储主键的缺点：

    • 二次查找。

  4. 存储主键的优点：

    • 页分裂无须变更。

数据备份

还有就是经常有很多应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的frm.MYD,MYI的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

image.png-20.5kB

InnoDB对事务的实现原理

隔离性实现

InnoDB中隔离性是通过数据库锁实现的。共享锁、排他锁、意向共享锁、意向排他锁。共享锁和排他锁非常简单，意向锁的出现是因为InnoDB既支持表锁也支持行锁，所以为了表锁和行锁存在时，表锁更快地感知到行锁的存在，更快地判定表锁和意向锁是否兼容，如果不能兼容，则表锁将会被阻塞。

InnoDB加锁是针对于索引进行加锁的。只有通过索引条件进行操作或者查询数据才会使用行级锁，否则InnoDB也将使用表锁。另外，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定。

InnoDB中行锁的实现算法设计有：Record Lock、Gap Lock、Next-Key Lock三种，而Gap Lock和Next-Key Lock的出现主要是为了解决幻行的问题。InnoDB默认的事务隔离级别为Repeatable Read，而Repeatable Read模式下，Next-Key Lock是默认的行记录锁定算法。比如，针对于查询：select * from t where a < 6 ，锁定的数据将是(-无穷大，6)。

原子性、持久性、一致性

Redo log：执行过程日志（是新数据的备份）

undo log：旧数据备份（回滚时使用）

在事务COMMIT操作完成之前，InnoDB会要求必须先将该事务的所有操作日志都写入到redo log中，而不需要将数据持久化，如果此时MySQL 服务崩溃，虽然数据还没有持久化，但是redo log已经持久化了，MySQL服务恢复后会根据redo log 将数据恢复到最新的状态。这样就保证了数据的持久性、一致性。

undo log记录了事务修改前的数据状态，是对旧数据的一种备份，异常或者事务回滚时，可以根据undo log将数据还原到之前的状态。这样保证了数据的原子性、持久性、一致性。

Undo log主要分为 Insert_undo log和update_undo log（update和delete）；insert_undo log记录插入的唯一键，update_undo log 记录修改的唯一键值以及old column记录。当事务回滚时，InnoDB将执行相反的操作。对于每个insert，会从undo日志中获取唯一键值来完成delete操作；对于每个delete，InnoDB存储引擎会执行一个insert将 old column记录再次插入进去。对于每个update，InnoDB存储引擎会执行一个相反的update，将修改前的行放回去。

值得一提的是InnoDB的恢复策略，我们能想到的恢复策略一般都是下面两种：

  1. 进行恢复时，只重做已经提交了的事务。
  2. 进行恢复时，重做所有事务包括未提交的事务和回滚了的事务。然后通过Undo Log回滚那些未提交的事务。

InnoDB采用的是第二种，虽然看起来比较笨，但是最简单，在重做redo log时，不需要关心事务性。恢复时，也没有BEGIN，也没有COMMIT、ROLLBACK的行为，也不关心每条日志是属于哪个事务的。当然，这样可能会造成重新执行了被回滚的事务，但是这对数据完整性并没有任何影响。

Undo log + redo log的过程如下：

A. 事务开始.
B. 记录A=1到undo log.
C. 修改A=3.
D. 记录A=3到redo log.
E. 记录B=2到undo log.
F. 修改B=4.
G. 记录B=4到redo log.
H. 将redo log写入磁盘。
I. 事务提交

MVCC实现原理

MVCC是一种多版本并发访问控制机制，是为了实现非锁定读操作而产生的。InnoDB进行读操作时，如果发现读取的行被其他线程加上了排他锁（DELETE、UPDATE），这时读取操作不会因此而等待行上锁的释放。相反，InnoDB存储引擎会去读取正在加锁的事务的undo log日志，读该行被加锁前的快照数据。

可以看到，非锁定读的机制大大提高了数据读取的并发性，它也是REPEATABLE READ隔离级别（InnoDB存储引擎默认隔离级别）的默认读取方式，即读取不会占用和等待表上的锁，但是在不同事务隔离级别下，读取的方式不同，并不是每个事务隔离级别下读取的都是一致性读。同样，即使都是使用一致性读，但是对于快照数据的定义也不同。

快照数据其实就是当前行数据之前的历史版本，可能有多个版本。一个行可能有不止一个快照数据。Read Committed和Repeatable Read两种存储引擎下，都使用了MVCC这种非锁定的一致性读，但是对于快照数据的定义却不相同。在Read Committed事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。但是在Reapeatable事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

image.png-17.9kB

具体的执行过程：begin->用排他锁锁定该行->记录redo log->记录undo log->修改当前行的值，写事务编号，回滚指针指向undo log中的修改前的行。

MySQL主从复制原理

现有MySQL主从复制的方式都是在主库上记录二进制日志、在从库重放日志的方式来实现异步的数据复制。因为是异步复制，所以可能会存在延迟问题。（这里可以讲一下canal）。

image.png-71.2kB

  1. 在主库上把数据更改记录到二级制日志（Binar Log）中（这些记录被称为二进制日志事件）。
  2. 从库将主库上的日志复制到自己的中继日志（RelayLog）中。
  3. 从库读取中继日志中的事件，将其重放到从库数据之上。

基于语句块的复制

基于语句的复制就是 主库会记录那些造成数据更改的语句，当备库读取并重放这些事件时，实际上只是把主库上执行过的SQL再执行一遍。

好处：

  1. 简单
  2. Binlog更加小。

缺点：

  1. 有时候可能无法再正确复制：比如插入的时候有时间戳之类的。

基于数据行的复制

基于行的复制，这种方式会将实际增删改数据记录在二进制日志中，跟其他数据库的实现比较相像。

好处：

  1. 能保证所有的行都能被正确且高效地复制，特别是做经过复杂计算才得出的数据，在从库里就不需要再经过计算，而直接写入最终结果值即可。

缺点：

  1. Binlog 可能会非常大，特别是对于批量更新，binlog则会记录每行数据。

InnoDB中锁

分类：共享锁 排他锁 意向共享锁 意向排他锁。 行锁和表锁

意向锁是表级锁，是为了表锁和行锁冲突时，表锁快速感知到行锁的存在，从而判定表锁和行锁是否冲突。

锁算法：
1.Record Lock、Gap Lock、Next-Key Lock 解决幻读问题。

事务特性

  1. 原子性(atomicity)：要么都成功，要么都失败
  2. 一致性(consistency)：从一个一致性状态到另一个一致性状态
  3. 隔离性( Isolation)：并发事务互不干扰
  4. 持久性( Durability)：事务一旦提交，即是物理修改

隔离性是通过锁实现的。原子性、一致性、持久性是通过redo log、undo log实现的。

事务隔离级别

image.png-17.7kB

事务分类

扁平事务

事务要么都执行，要么都回滚，就是我们最常使用的事务。

带有保存点的扁平事务

事务执行过程中，会形成很多的保存点，事务回滚时，可以回滚到指定的保存点，从而实现有针对性的回滚，而非全部回滚。

带有保存点的扁平事务过程大致如下：

begin --> 隐含保存点1(save work 1) --> A --> B(save work2)--> C --> D(rollback work2) --> commit。

链式事务

链式事务是指多个事务之间传递必要的上下文，这就意味着下一个事务可以看到上一个事务的结果，就好像在一个事务中。链式事务中会把提交事务操作和开始下一个事务操作合并为一个原子操作。

嵌套事务

可以理解为一棵事务树，顶层事务控制着下面各个层次的子事务，所有的叶子节点都是扁平事务，实际工作是由叶子节点完成的。子事务既可以提交也可以回滚。但是它的提交操作并不马上生效，除非其父事务已经提交。

也就是说，任何子事务都在顶层事务提交后才真正的提交。树中的任意一个事务的回滚会引起它的所有子事务一同回滚，故子事务仅保留A、C、I特性，不具有D的特性。

分布式事务

分布式环境下运行的扁平事务。

InnoDB支持链式事务和扁平事务。

数据库范式

查询性能优化

优化思路

  1. 优化数据访问：
    • 程序是否在检索大量超过需要的数据：过多的行或者过多的列。 减少访问的行数，分页等。
    • MySQL服务器是否在分析大量超过需要的数据行：通常是索引不高效或者没有正确使用索引。建立合适的索引。

具体优化方案

  1. 建立合适的索引：索引可以减少扫描的行数，加快order by 和group by等的速度。

  2. 遵循索引的最左前缀原则：避免使用like ‘%Query%’、!=、<>。

  3. 尽量避免在where子句中对字段进行表达式计算和函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2。如：select id from t where substring(name,1,3)=’abc’，name以abc开头的id应改为:select id from t where name like ‘abc%’

  4. 只查询需要的行和列，比如：select * from t

  5. 尽量避免在where子句中使用or来连接查询，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20。

  6. In和not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)，对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3。

  7. 如果在 where子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num

  8. 不要在 where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

  9. Limit分页：MySQL的做法是查询前n条，然后截取指定数量的条数。类似于游标，设置起始点，然后进行分页，尽可能减少查询的数据量。还有一种方法是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要与原表做一次关联操作返回需要的列。对于偏移量很大的时候，这样的效率会提升非常。考虑下面的查询：SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
    如果这个表非常大，那么这个查询最好改写成下面的这样子：

    1. SELECT film.film_id, film.description FROM sakila.film
    2. INNER JOIN 
    3. (SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5) AS lim
    4. USING(film_id);

  10. 主键尽量单调，否则可能造成页分裂问题。

MySql查询性能优化

MySQL 索引实现原理

B树 & B+树 介绍

  1. 二叉排序树：

    • 若左子树不空，则左子树上所有节点的值均小于它的根节点的值
    • 若右子树不空，则右字数上所有节点的值均大于它的根节点的值
    • 它的左、右子树也分别为二叉排序树（递归定义）
    • 不平衡问题

  2. 平衡二叉树：

    • 每个节点至多可以拥有m棵子树。
    • 根节点，只有至少有2个节点（要么极端情况，就是一棵树就一个根节点，单细胞生物，即是根，也是叶，也是树)。
    • 非根非叶的节点至少有的Ceil(m/2)个子树(Ceil表示向上取整，图中5阶B树，每个节点至少有3个子树，也就是至少有3个叉)。
    • 非叶节点中的信息包括[n,A0,K1,A1,K2,A2,…,Kn,An]，，其中n表示该节点中保存的关键字个数，K为关键字且Ki
    • 从根到叶子的每一条路径都有相同的长度，也就是说，叶子节在相同的层，并且这些节点不带信息，实际上这些节点就表示找不到指定的值，也就是指向这些节点的指针为空。
    • 关键字集合分布在整颗树中。
    • 任何一个关键字出现且只出现在一个节点中。
    • 搜索有可能在非叶子节点结束。
    • 其搜索性能等价于在关键字集合内做一次二分查找。
    • B树在插入删除新的数据记录会破坏B-Tree的性质，因为在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质。

  3. B+树：

    • 有n棵子树的节点含有n个关键字（也有认为是n-1个关键字）。
    • 所有的关键字全部存储在叶子节点上，且叶子节点本身根据关键字自小而大顺序连接。
    • 非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字。

  4. B+树 Plus版：
    带有顺序访问指针的B+Tree。在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree

为什么采用B+树

  1. 磁盘缺点：由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，因此为了提高效率，要尽量减少磁盘I/O。
  2. 局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。所以，程序运行期间所需要的数据通常应当比较集中。数据越紧凑，预读性越高。B-Tree在每次新建节点时，直接申请一个页的空间，保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

具体存储引擎对索引的实现方式

聚簇索引和非聚簇索引。辅助索引保存指针还是主键。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）

添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注