MySQL面经_mysql重新运行视图

1. 什么是MySQL？

• MySQL 是一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息。由于 MySQL 是开源免费并且比较成熟的数据库，因此，MySQL 被大量使用在各种系统中。MySQL 的默认端口号是3306。

2. 数据库三大范式是什么?

• 第一范式条件：必须不包含重复组的关系，即每一列都是不可拆分的原子项。
• 第二范式条件：关系模式必须满足第一范式，并且所有非主属性都完全依赖于主码。
• 第三范式的条件：关系模型满足第二范式，所有非主属性对任何候选关键字都不存在传递依赖。

3. mysql有关权限的表都有哪几个?

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

4. MySQL的binlog有有几种录入格式？分别有什么区别？

• 有三种格式，statement，row和mixed。

  • statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
  • row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
  • mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

5. mysql有哪些数据类型?

6. MySQL存储引擎MyISAM与InnoDB区别?

1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
3. InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一
   

7. MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

8. InnoDB引擎的4大特性?

• 插入缓冲（insert buffer)
• 二次写(double write)
• 自适应哈希索引(ahi)
• 预读(read ahead)

9. 什么是索引？索引有哪些优缺点？索引使用场景（重点）?

• 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

• 索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

• 更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引的优点

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

10. 索引有哪几种类型？

• 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

• 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

• 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

  • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

  • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

• 全文索引： 是目前搜索引擎使用的一种关键技术。

  • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

11. 索引的数据结构（b+树，b树）？B树和B+树的区别?

• B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

B 树& B+树两者有何异同呢？

• B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

12. 创建索引的三种方式，删除索引？

• 第一种方式：在执行CREATE TABLE时创建索引

• 第二种方式：使用ALTER TABLE命令去增加索引

• 第三种方式：使用CREATE INDEX命令创建

• 根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

• 删除主键索引：alter table 表名 drop primary key（需要取消自增长再行删除）

13. MySQL 如何为表字段添加索引？

1. 添加 PRIMARY KEY（主键索引）

   ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
   Copy to clipboardErrorCopied
   1
   2

2. 添加 UNIQUE(唯一索引)

   ALTER TABLE `table_name` ADD UNIQUE ( `column` )
   Copy to clipboardErrorCopied
   1
   2

3. 添加 INDEX(普通索引)

   ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
   Copy to clipboardErrorCopied
   1
   2

4. 添加 FULLTEXT(全文索引)

   ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
   Copy to clipboardErrorCopied
   1
   2

5. 添加多列索引

   ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
   1

13. 使用索引查询一定能提高查询的性能吗？为什么?

• 通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

• 索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。

14. 主键索引与二级索引(辅助索引)的区别?

主键索引

• 一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
• 在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引(辅助索引)

• 二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

• 唯一索引，普通索引，前缀索引等索引属于二级索引。

1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
4. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

16. 什么是最左前缀原则？

• 顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
• 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

17. 什么是聚簇索引？何时使用聚簇索引与非聚簇索引?

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行

何时使用聚簇索引与非聚簇索引

18. 非聚簇索引一定会回表查询吗？

• 不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

• 举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

19. 联合索引是什么？为什么需要注意联合索引中的顺序？

• MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

20. 什么是数据库事务？

• 事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

21. 事物的四大特性(ACID)介绍一下?

• 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
• 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

22. 什么是脏读？幻读？不可重复读？

• 脏读(Drity Read)：一个事务读取了另一个事务未提交的数据。
• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失。
• 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
• 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

不可重复读和幻读区别：

• 不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

23. 什么是事务的隔离级别？MySQL的默认隔离级别是什么？

• 为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。
  

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

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

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

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

• Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别

• InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

24. 对MySQL的锁了解吗?

• 当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

25. 隔离级别与锁的关系?

• 在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

• 在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

• 在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

• SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

26. 按照锁的粒度分数据库锁有哪些？锁机制与InnoDB锁算法?

• 在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

行级锁

• 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

• 特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁

• 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

• 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁

• 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

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

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

• InnoDB是基于索引来完成行锁

• 例: select * from tab_with_index where id = 1 for update;

• for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

28. InnoDB存储引擎的锁？

MyISAM 和 InnoDB 存储引擎使用的锁：

• MyISAM 采用表级锁(table-level locking)。
• InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

• 表级锁： MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
• 行级锁： MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

1. Record lock：单个行记录上的锁
2. Gap lock：间隙锁，锁定一个范围，不包括记录本身
3. Next-key lock：record+gap 锁定一个范围，包含记录本身

29. 什么是死锁？怎么解决？

• 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

• 常见的解决死锁的方法

• 1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

• 2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

• 3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

30. 数据库的乐观锁和悲观锁是什么？怎么实现的？

悲观锁

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制
• 读锁：myisam引擎下使用lock table tablename read;进行加读锁，使用unlock tables;进行读锁释放；innodb引擎下可以在select语句最后加上lock in share mode来加意向共享锁(读锁)。
• 写锁：myisam引擎下使用lock table tablename write;进行加写锁，使用unlock tables;进行读锁释放；innodb引擎下可以在select语句最后加上for update来加意向排它锁(写锁)

乐观锁

• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。
• ①version方式（添加版本号）：在数据表中加上一个数据版本号version字段，当数据被修改时，version值加一。在提交更新的时候，会检查当前version与刚才读到的version值是否相等，如果相等才会更新，否则重试更新操作，知道相等为止。
• ②时间戳方式：在数据表中加上一个时间戳字段，与version方式类似，当数据被修改时，时间戳更新。在提交更新时，检查当前时间戳与刚才读到的时间戳是否相等，如果相等才更新，否则重试。

两种锁的使用场景

• 从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

• 但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

31. 什么是存储过程？有哪些优缺点？

• 存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

优点

1）存储过程是预编译过的，执行效率高。

2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3）安全性高，执行存储过程需要有一定权限的用户。

4）存储过程可以重复使用，减少数据库开发人员的工作量。

缺点

1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。

2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。

3）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

32. 为什么要使用视图？什么是视图？

• 1、什么是视图？
  视图是将一段查询sql封装为一个虚拟的表，这个虚拟表只会保存sql逻辑，不会保存任何查询结果，也不会占据空间，所以每次进行查询时都是重新执行sql。可以将视图理解为一个函数，想要调用时直接调用即可

• 2、视图的作用
  ①封装复杂sql，提高复用性
  ②对数据库重构，而不影响程序的运行
  ③只会展现视图结果中的数据，而不会展现引用表中的数据，提高了数据安全性

• 3、视图的缺点？
  ①性能不佳，每次执行视图都会重新执行语句
  ②修改限制，需要对数据进行修改时，必须把视图转化为基本表进行修改，但是对于复杂的视图，可能是不可修改的

33. 什么是触发器？触发器的使用场景有哪些？

• 触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

1. 可以通过数据库中的相关表实现级联更改。
2. 实时监控某张表中的某个字段的更改而需要做出相应的处理。
3. 例如可以生成某些业务的编号。
4. 注意不要滥用，否则会造成数据库及应用程序的维护困难。

34. 六种关联查询?

• 交叉连接（CROSS JOIN）
• 内连接（INNER JOIN）
• 外连接（LEFT JOIN/RIGHT JOIN）
• 联合查询（UNION与UNION ALL）
• 全连接（FULL JOIN）
• 交叉连接（CROSS JOIN）

35. 关系型数据库和非关系型数据库的区别？

36. varchar(50)中50的涵义？int(20)中20的涵义？

• 最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。
• 是指显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示
• 对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

37. MySQL有哪些日志？分别有什么作用？

• redo log是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。
• undo logundo log的作用主要用于回滚，mysql数据库的事务的原子性就是通过undo log实现的。我们都知道原子性是指对数据库的一系列操作，要么全部成功，要么全部失败。
• undo log主要存储的是数据的逻辑变化日志，比如说我们要insert一条数据，那么undo log就会生成一条对应的delete日志。简单点说，undo log记录的是数据修改之前的数据，因为需要支持回滚。那么当需要回滚时，只需要利用undo log的日志就可以恢复到修改前的数据。
• undo log另一个作用是实现多版本控制(MVCC)，undo记录中包含了记录更改前的镜像，如果更改数据的事务未提交，对于隔离级别大于等于read commit的事务而言，不应该返回更改后数据，而应该返回老版本的数据。

39. 为什么有了MVCC还需要行级锁？

• MVCC 多版本并发控制技术,它使得大部分支持行锁的事务引擎，不再单纯的使用行锁来进行数据库的并发控制，取而代之的是把数据库的行锁与行的多个版本结合起来，只需要很小的开销,就可以实现非锁定读，从而大大提高数据库系统的并发性能

40. MVCC多版本并发控制是什么？

• 多版本并发控制（MVCC） 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

• 简单来说，多版本并发控制 的思想就是保存数据的历史版本，通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。

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

41. 多版本并发控制解决了哪些问题?

1. 读写之间阻塞的问题

• 通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。

2. 降低了死锁的概率

• 因为 InnoDB 的 MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。

3. 解决一致性读的问题

• 一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。

42. 快照读与当前读?

• 快照读（SnapShot Read） 是一种一致性不加锁的读，是InnoDB并发如此之高的核心原因之一。

• 这里的一致性是指，事务读取到的数据，要么是事务开始前就已经存在的数据，要么是事务自身插入或者修改过的数据。

• 不加锁的简单的 SELECT 都属于快照读

  SELECT * FROM t WHERE id=1
  1

• 当前读就是读取最新数据，而不是历史版本的数据。加锁的 SELECT 就属于当前读，例如：

  SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE;
  
  SELECT * FROM t WHERE id=1 FOR UPDATE;
  1
  2
  3

43. InnoDB 的 MVCC底层 是如何工作的?

事务版本号

• 每开启一个事务，我们都会从数据库中获得一个事务 ID（也就是事务版本号），这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。

行记录的隐藏列

• InnoDB 的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段：

• DB_ROW_ID：6-byte，隐藏的行 ID，用来生成默认聚簇索引。如果我们创建数据表的时候没有指定聚簇索引，这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚簇索引的方式可以提升数据的查找效率。

• DB_TRX_ID：6-byte，操作这个数据的事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。

• DB_ROLL_PTR：7-byte，回滚指针，也就是指向这个记录的 Undo Log 信息。

Read View

• Read View 决定了记录是否对本事务可见。包含以下:

• low_limit_id（高水位）

  • 下一个将被分配的事务ID，high water mark，大于等于low_limit_id的事务对于view都是不可见的

• up_limit_id（低水位）

  • 活跃事务列表trx_ids中最小的事务ID，low water mark，小于up_limit_id的事务对于view一定是可见的

• trx_ids

  • Read View创建时其他未提交的活跃读写事务ID列表。后续即使这些活跃事务修改了记录行的值，对于当前事务也是不可见的。不包括当前事务和已提交的事务

• low_limit_no

  • trx_no小于low_limit_no的undo log对于view是可以purge的

• creator_trx_id

  • 当前创建事务的ID

Undo Log

• Undo log中存储的是老版本数据，当一个事务需要读取记录行时，如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本。

• 从图中能看到回滚指针将数据行的所有快照记录都通过链表的结构串联了起来，每个快照的记录都保存了当时的 db_trx_id，也是那个时间点操作这个数据的事务 ID。这样如果我们想要找历史快照，就可以通过遍历回滚指针的方式进行查找。
• undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护

可见性比较算法

• RR隔离级别下，创建一个新事务后，执行第一个select语句的时候，innodb会创建一个Read View，Read View 中会保存系统当前其他活跃事务id列表（即trx_ids）。当用户在这个事务中要读取某个记录行的时候，innodb会将该记录行的DB_TRX_ID(记为trx_id)与该Read View中的一些变量进行比较，判断是否满足可见性条件。

1. 如果 trx_id < up_limit_id(低水位), 那么表明“最新修改该行的事务”在“当前事务”创建快照之前就提交了，所以该记录行的值对当前事务是可见的。跳到步骤5。
2. 如果 trx_id >= low_limit_id(高水位), 那么表明“最新修改该行的事务”在“当前事务”创建快照之后才修改该行，所以该记录行的值对当前事务不可见。跳到步骤4。
3. 如果 up_limit_id <= trx_id < low_limit_id, 表明“最新修改该行的事务”在“当前事务”创建快照的时候可能处于“活动状态”或者“已提交状态”；所以就要对活跃事务列表trx_ids进行查找（源码中是用的二分查找，因为是有序的）：
   1. 如果在活跃事务列表trx_ids中能找到 id 为 trx_id 的事务，表明可能是下面两种情况。这些情况下，这个记录行的值对当前事务都是不可见的，跳到步骤4
      1. 在“当前事务”创建快照前，“该记录行的值”被“id为trx_id的事务”修改了，但没有提交
      2. 在“当前事务”创建快照后，“该记录行的值”被“id为trx_id的事务”修改了（不管有无提交）
      3. 在活跃事务列表中找不到，则表明“id为trx_id的事务”在修改“该记录行的值”后，在“当前事务”创建快照前就已经提交了，所以记录行对当前事务可见，跳到步骤5。
4. 在该记录行的 DB_ROLL_PTR 指针所指向的undo log回滚段中，取出最新的的旧事务号DB_TRX_ID, 将它赋给trx_id，然后跳到步骤1重新开始判断。
5. 将该可见行的值返回。
   

• 对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

• 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；

• 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

• 如果落在黄色部分，那就包括两种情况

  • 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
  • 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

1. 版本未提交，不可见；
2. 版本已提交，但是是在视图创建后提交的，不可见；
3. 版本已提交，而且是在视图创建前提交的，可见。

隔离级别不同，Read View机制也不同

• innodb中的Repeatable Read级别, 只有事务在begin之后，执行第一条select（读操作）时, 才会创建一个快照(read view)，将当前系统中活跃的其他事务记录起来；并且事务之后都是使用的这个快照，不会重新创建，直到事务结束。
• 在innodb中的Read Committed级别, 事务在begin之后，执行每条select（读操作）语句时，快照会被重置，即会重新创建一个快照(read view)。

41. MySQL如何保证事务？(原子性,一致性，隔离性，持久性)

• redo log重做日志用来保证事务的持久性
• undo log回滚日志保证事务的原子性
• undo log+redo log保证事务的一致性
• MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。

42. 覆盖索引?

• 覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。
  

43. 什么是索引下推？

• 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

• 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。

• 在多个条件查询中（一般在普通索引中使用），使用索引下推不会忽略其他条件，在本次查询中就过滤出来，无需再回表进行其他条件的判断，可以有效减少回表的次数，大大提升了查询的效率。

• 如有(name, age)索引时：查询name like ‘陈%’ and age > 20

• 如果不使用索引下推：将满足name like '陈%'的所有记录返回，再根据age的条件进行第二次回表查询

• 如果使用了索引下推：在判断name like '陈%'后，并没有忽略age字段，在索引内部将不满足age条件的记录直接跳过，此时就无需回表二次查询。

# 关闭索引下推
set optimizer_switch='index_condition_pushdown=off';
1
2

44. 什么是回表？

• 与索引覆盖互逆，当一个查询语句的字段不能从索引中直接取得，那么需要从其他数据表中获取，这个过程称之为回表

45. 什么是谓词下推？

• 在进行表关联时，先获取SQL语句中涉及到的字段（即select语句后面的字段），再进行表关联。这样做可以避免表的字段过多，先进行表关联会导致浪费，因为无效数据过多。采用谓词下推可以避免关联过多的字段从而造成的浪费

46. 怎么验证 mysql 的索引是否满足需求？

• 可以使用explain进行sql分析，查看索引是否使用到，查看是否进行了全表扫描，还能查看一些排序是否使用了Filesort，这些情况都是需要避免的

47. explain常用参数有哪些？

• ①type：可以查看查询的类型，看看是否ALL全表扫描
• ②possible_keys：已有的索引，列出可能用到的索引
• ③key：真正使用的索引
• ④key_len：索引长度，查看是否充分地用上了索引，越大越好
• ⑤rows：物理扫描的行数，越小越好
• ⑥Extra：额外重要信息

48. 索引失效的情况？

• ①不满足最左匹配原则，比如创建的索引是a,b,c，但进行查询时where条件的顺序是b c或者a c，也就是检索条件不是从a开始，或者中间有断层，都会导致索引失效
• ②where筛选的条件使用了函数、计算和类型转换等
• ③范围查询右边的字段索引失效
• ④使用不等于<>
• ⑤使用不为空not null
• ⑥like查询中的首字母为通配符%，即’%zhangsan’等等，也可以理解为%也是范围查询
• ⑦类型不匹配，假设name字段是varchar类型的，而检索时未加引号，虽然检索能成功，但其实是全表扫描

49. 如何查找慢sql并优化？

• 开启慢查询日志slow_query_log，可以设置执行时间超过long_query_time的加入日志，系统运行一段时间后，查看日志文件中频繁出现的sql，对这些sql进行详细的优化

50. 非聚簇索引与聚簇索引的非叶子节点存放的是什么？

• 存放的是索引key值和向下的指针

51. 为什么索引能加快查找速度？

• 因为索引已经为我们排好序了，查找时可以直接使用写好的二分查找进行查询，可以避免全表扫描

52. 使用UUID作为主键会有什么问题？

• 对于聚簇索引来说，主键会进行排序，但是由于uuid的无序性，每次添加可能会产生巨大的io压力，B+树可能也会频繁分裂和调整，可以将uuid设计为逻辑主键，物理主键还是使用自增id

53. 解释一下记录锁、间隙锁、临键锁？

• 这三个锁都是行锁。
• 如果想update一个含有索引的字段进行where精确检索（也可以使用select的for update加锁），那么会对这个行记录上加记录锁record lock；
• 如果进行where范围检索，则会将该范围中的所有不存在的行记录上加间隙锁；
• 而临键锁(next-key lock)是记录锁和临键锁的合体，假设查询0<id<10的记录，而数据表中只有2, 3, 5, 8记录，因此这2, 3, 5, 8记录将会加记录锁，而1, 4, 6, 7, 9则会加临键锁。如果这是一个更新操作(也就是加的写锁)，那么在锁释放前，尽管操作数据表中没有的字段如1, 4, 6, 7, 9这些，均会被阻塞

54. 说一下 mysql 的行锁和表锁？

• myisam只支持表锁，每次进行查询，都会加表锁，也就是对整张表进行加锁，其他客户端不能对其再做任何操作，只有等当前锁释放。
  innodb支持行锁，也支持表锁，默认是行锁。当对一个数据进行操作时，只会对该行进行上锁，其他行是没有任何影响的，其他事物能操作其他行。但前提是进行检索时用到了索引，如果未使用索引，则会进行全表扫描，意味着此时加的是表锁。如果索引失效了，也会将行锁升级为表锁。

55. 可重复读隔离级别下是如何解决幻读问题的？

• 快照读可以解决部分幻读问题，也就是在第一次select时生成一个快照，以后每次查询都使用此快照，也就是使用MVCC多版本控制来避免幻读的；
• 如果是当前读，可以通过临键锁或间隙锁来避免幻读，也就是对范围查询的间隙加间隙锁，当想要对间隙进行插入数据时，会阻塞，因此一定程度上也避免了幻读。

56. 大表怎么优化？某个表有近千万数据，CRUD比较慢，如何优化？

1. 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；
2. 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
3. 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
4. 分库分表的方式进行优化，主要有垂直分表和水平分表

57. MySQL的主从复制原理以及流程？

1. Master将数据改变记录到二进制日志(binary log)中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件(binary log events)
2. Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)
3. Slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)

58. MySQL如何实现悲观锁?

1. 实现悲观锁利用select … for update加锁, 操作完成后使用commit来释放锁；

2. innodb引擎时, 默认行级锁, 当有明确字段时会锁一行, 如无查询条件或条件；字段不明确时, 会锁整个表，条件为范围时会锁整个表；

3. 查不到数据时, 则不会锁表。

59. 一条 SQL 语句在 MySQL 中如何执行的?

• MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有。
• 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
• 查询语句的执行流程如下：权限校验（如果命中缓存）—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎
• 更新语句执行流程如下：分析器---->权限校验---->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit状态)