MySQL每日面经【01】

1、MySQL中的myisam与innodb的区别？
2、MySQL的逻辑架构
3、MySQL日志binlog、redo log
4、事务隔离级别有哪些？
5、何为索引？有什么作用？
6、索引的优缺点?
7、索引的数据结构
8、什么是事务？
9、事务的四大特性
10、为什么MySQL数据库使用B+树不使用B树？
11、锁分类
12、表级锁和行级锁了解吗？有什么区别？
13、行级锁的使用有什么注意事项？

1、MySQL中的myisam与innodb的区别？

（1）InnoDB存储引擎支持事务，而MyISAM不支持事务；
（2）InnoDB支持行级锁，而MyISAM只支持表级锁；
（ InnoDB行锁是通过给索引加锁实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表级锁！行级锁在每次获取锁和释放锁的操作需要比表级锁消耗更多的资源。

MySQL表级锁有两种模式：表共享读锁和表独占写锁。就是说对MyIASM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，会阻塞其他用户对同一表的读和写操作。）

（3）InnoDB支持外键，而MyISAM不支持外键；
（4）InnoDB不保存数据库表中表的具体行数，而MyISAM会保存；
（ 也就是说，执行 select count(*) from table 时，InnoDB要扫描一遍整个表来计算有多少行，而MyISAM只需要读出保存好的行数即可（内部维护了一个计算器，可以直接调取）。【注】：当count(*)语句包含where条件时，两种表的操作是一样的。也就是上述介绍到的InnoDB使用表锁的一种情况。）

对于select ,update ,insert ,delete 操作：
如果执行大量的SELECT，MyISAM是更好的选择（因为MyISAM不支持事务，使得MySQL可以提供高速存储和检索，以及全文搜索能力）；

如果执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表（因为InnoDB支持事务，在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了）。

2、MySQL的逻辑架构

MySQL的逻辑架构图：

大体来说，MySQL 可以分为 Server 层 和 存储引擎层 两部分。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB 、 MyISAM 、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB ，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

3、MySQL日志binlog、redo log

3.1重要的日志模块： redo log

当有一条记录需要更新的时候， InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时， InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是1GB ，那么这块 “ 粉板 ” 总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是 “ 粉板 ” 上还空着的部分，可以用来记录新的操作。如果 write pos追上 checkpoint ，表示 “ 粉板 ” 满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint 推进一下。

有了 redo log ， InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe 。

3.2重要的日志模块： binlog

前面我们讲过， MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的 redo log 是InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog （归档日志）。

我想你肯定会问，为什么会有两份日志呢？
因为最开始 MySQL 里并没有 InnoDB 引擎。 MySQL 自带的引擎是 MyISAM ，但是 MyISAM 没有crash-safe 的能力， binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统 —— 也就是redo log 来实现 crash-safe 能力。

3.3这两种日志有以下三点不同：

1. redo log 是 InnoDB 引擎特有的； binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是 “ 在某个数据页上做了什么修改 ” ； binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如 “ 给 ID=2 这一行的 c 字段加 1 ” 。
3. redo log 是循环写的，空间固定会用完； binlog 是可以追加写入的。 “ 追加写 ” 是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

4、事务隔离级别有哪些？

先了解下几个概念：脏读、不可重复读、幻读。

脏读 是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
不可重复读 是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。
幻读 是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录。对幻读的正确理解是一个事务内的读取操作的结论不能支撑之后业务的执行。假设事务要新增一条记录，主键为id，在新增之前执行了select，没有发现id为xxx的记录，但插入时出现主键冲突，这就属于幻读，读取不到记录却发现主键冲突是因为记录实际上已经被其他的事务插入了，但当前事务不可见。
不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

MySQL数据库为我们提供的四种隔离级别：

• Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
• Repeatable read (可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。
• Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。
• Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。

查看隔离级别：

select @@transaction_isolation;

设置隔离级别：

set session transaction isolation level read uncommitted;

5、何为索引？有什么作用？

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。常见的索引结构有: B 树， B+树和 Hash。

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

6、索引的优缺点？

优点：

• 加快数据查找的速度
• 为用来 排序 或者是分组的字段添加索引，可以加快分组和排序的速度
• 加快表与表之间的连接

缺点：

• 建立索引需要占用物理空间
• 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

7、索引的数据结构

索引的数据结构主要有 B+树 和 哈希表 ，对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引。

B+树索引

B+ 树是基于B 树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

进行查找操作时，首先在根节点进行 二分查找 ，找到 key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行 二分查找 ，找出 key所对应的数据项。

MySQL 数据库使用最多的索引类型是BTREE索引，底层基于B+树数据结构来实现。

mysql> show index from blog\G;
*************************** 1. row ***************************
        Table: blog
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: blog_id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

哈希索引

哈希索引是基于 哈希表 实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希 算法 要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为 哈希表 的key值，将指向数据行的指针作为 哈希表 的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

8、什么是事务？

提到事务，你肯定不陌生，和数据库打交道的时候，我们总是会用到事务。最经典的例子就是转账，你要给朋友小王转100块钱，而此时你的银行卡只有100块钱。

转账过程具体到程序里会有一系列的操作，比如查询余额、做加减法、更新余额等，这些操作必 须保证是一体的，不然等程序查完之后，还没做减法之前，你这100块钱，完全可以借着这个时 间差再查一次，然后再给另外一个朋友转账，如果银行这么整，不就乱了么？这时就要用到“事 务”这个概念了。

简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事 务支持是在引擎层实现的。你现在知道，MySQL是一个支持多引擎的系统，但并不是所有的引 擎都支持事务。比如MySQL原生的MyISAM引擎就不支持事务，这也是MyISAM被InnoDB取代 的重要原因之一。

9、事务的四大特性

事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
• 一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。
• 隔离性。跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。
• 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

10、为什么MySQL数据库使用B+树不使用B树？

当存储同数量级的数据的时候，B+树的高度比B树的高度小，这样的话进程IO操作的次数就少，效果就高。因为B+树的所有非叶子节点只存索引，数据存在叶子节点，一般3层的树高度，即可存千万级别的数据，而B数不行。(具体的计算可以到网上去看看，有面试官可能会问你怎么算出来的。)

11、锁分类

从锁的类别上来分,有 共享锁 和 排他锁。

• 共享锁: 又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个。
• 排他锁: 又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只可以加一个,他和其他的排他锁,共享锁都相斥。

从锁的粒度（加锁范围）来分，行级锁、页级锁、表级锁。他们的加锁开销从大大小,并发能力也是从大到小。

• 行级锁
• 页级锁
• 表级锁

MyISAM采用表级锁，InnoDB支持行级锁和表级锁,默认为行级锁。表级锁和行级锁对比：

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

InnoDB存储引擎的锁的算法有三种：

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

12、表级锁和行级锁了解吗？有什么区别？

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。

InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

表级锁和行级锁对比 ：

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

13、行级锁的使用有什么注意事项？

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中索引或者索引失效的话，就会导致扫描全表对表中的所有记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！

不过，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因。