数据库MySQL面试点_mysql 数据库面试点

一 索引

1、B+ 树原理
1.1 数据结构
B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节
点位于同一层。
B+ 树是基于 B 树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通
过顺序访问指针来提高区间查询的性能。
在 B+ 树中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key
分别是 key i 和 key i+1，且不为 null，则该指针指向节点的所有 key 大于等于 key i 且小
于等于 key i+1。

 

 

（一）更少的查找次数

平衡树查找操作的时间复杂度和树高 h 相关，O(h)=O(logdN)，其中 d 为每个节点
的出度。
红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比
B+ Tree 大非常多，查找的次数也就更多。

 

简单的说就是B+树更加矮胖，查询效率更高。

 

（二）利用磁盘预读特性

为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程
中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，速
度会非常快。
操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页
为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就
能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

 

 

2、MySQL 索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同
的索引类型和实现。
2.1 B+ 树索引
是大多数 MySQL 存储引擎的默认索引类型。
因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。
除了用于查找，还可以用于排序和分组。
可以指定多个列作为索引列，多个索引列共同组成键。
适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如
果不是按照索引列的顺序进行查找，则无法使用索引。

 

InnoDB 的 B+ 树索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完
整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地
方，所以一个表只能有一个聚簇索引。

 

聚簇索引：是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。
特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引，且一
张表只允许存在一个聚簇索引。
聚簇索引和非聚簇索引的区别：聚簇索引的叶子节点就是数据节点，而非聚簇索引的
叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

 

MyISAM： B+ 树叶节点的 data 域存放的是数据记录的地址【物理地址】。在索引
检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出
其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚
簇索引”。

 

 

InnoDB：其数据文件本身就是索引文件。相比 MyISAM的索引文件和数据文件是分
离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域
保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文
件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅
助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM
不同的地方。在 根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根
据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时
候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会
造成主索引频繁分裂。

 

2.2 哈希索引
哈希索引能以 O(1) 时间进行查找，但是失去了有序性：
1.无法用于排序与分组；
2.只支持精确查找，无法用于部分查找和范围查找。
InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的
非常频繁时，会在 B+ 树索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希
索引的一些优点，比如快速的哈希查找。

 

2.3 全文索引
MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相
等。
查找条件使用 MATCH AGAINST，而不是普通的 WHERE。
全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射 。
InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
2.4. 空间数据索引
MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据
索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。
必须使用 GIS 相关的函数来维护数据。

 

 

三 存储引擎

1、InnoDB
是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它
存储引擎。
实现了四个标准的隔离级别，默认级别是：可重复读（REPEATABLE READ）。在可
重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key Locking）防止
幻影读。
主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能
有很大的提升。
面试问题
MySQL 问题排查都有哪些手段？
如何做 MySQL 的性能优化？
内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且
自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停
止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

 

2、MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操
作，则依然可以使用它。
提供了大量的特性，包括压缩表、空间数据索引等。
不支持事务。
不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时
则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发
插入（CONCURRENT INSERT）。
可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导
致一些数据丢失，而且修复操作是非常慢的。
如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索
引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候
才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主
机崩溃时会造成索引损坏，需要执行修复操作。

 

七、事务

1、原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完
成，要么完全不起作用；
2、一致性： 执行事务前后，数据库从一个一致性状态转换到另一个一致性状态。
3、隔离性： 并发访问数据库时，一个用户的事物不被其他事务所干扰，各并发事务之间
数据库是独立的；
4、持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库 发生
故障也不应该对其有任何影响。
为了达到上述事务特性，数据库定义了几种不同的事务隔离级别：
1、READ_UNCOMMITTED（未提交读）: 最低的隔离级别，允许读取尚未提交的数据变
更，可能会导致脏读、幻读或不可重复读
2、READ_COMMITTED（提交读）: 允许读取并发事务已经提交的数据，可以阻止脏
读，但是幻读或不可重复读仍有可能发生
3、REPEATABLE_READ（可重复读）: 对同一字段的多次读取结果都是一致的，除非数
据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
4、SERIALIZABLE（串行化）: 最高的隔离级别，完全服从ACID的隔离级别。所有的事务
依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、
不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。
脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插
入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。
不可重复读 ：是指在一个事务内，多次读同一数据。
幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查
询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了
幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集
里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少
了。
这里需要注意的是：MySQL
默认采用的 REPEATABLE_READ 隔离级别 Oracle 默认采用的 READ_COMMITTED 隔离
级别。
事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVCC（多版本
并发控制），通过行的创建时间和行的过期时间来支持并发一致性读和回滚等特性。
【讲的很好】https://mp.weixin.qq.com/s/Jeg8656gGtkPteYWrG5_Nw
所谓的 MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是
在使用 READ COMMITTD、REPEATABLE READ 这两种隔离级别的事务在执行普通的
SEELCT 操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发
执行，从而提升系统性能。
READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是生成
ReadView 的时机不同，READ COMMITTD 在每一次进行普通 SELECT 操作前都会生成
一个 ReadView，而 REPEATABLE READ 只在第一次进行普通 SELECT 操作前生成一个
ReadView，之后的查询操作都重复这个 ReadView 就好了。
八、锁机制
MyISAM 和 InnoDB存储引擎使用的锁
MyISAM 采用表级锁(table-level locking)。
InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁。
表级锁和行级锁对比
表级锁： MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源
消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发
MVCC
度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁： MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能
大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁
慢，会出现死锁。
InnoDB 什么时候使用行级锁？什么时候使用表级锁？
只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。
1、只有在你增删改查时，匹配的条件字段带有索引时，InnoDB 才会使用行级锁；
2、在你增删改查时，匹配的条件字段不带有索引时，InnoDB 使用的将是表级锁。因为当
你匹配条件字段不带有索引时，数据库会全表查询，所以这需要将整张表加锁，才能保证
查询匹配的正确性。
在生产环境中我们往往需要满足多人同时对一张表进行增删改查，所以就需要使用行
级锁，所以这个时候一定要记住为匹配条件字段加索引。
提到行级锁和表级锁时我们就很容易联想到读锁和写锁，因为只有触发了读写锁，我
们才会谈是进行行级锁定还是进行表级锁定。那么什么时候触发读锁，就是在你用 select
命令时触发读锁，什么时候触发写锁，就是在你使用 update、delete、insert 时触发写
锁，并且使用 rollback 或 commit 后解除本次锁定。
InnoDB 存储引擎的锁的算法有三种
1、Record lock：单个行记录上的锁
2、Gap lock：间隙锁，锁定一个范围，不包括记录本身
3、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
数据库中锁的详解：
https://blog.csdn.n et/qq_35246620/article/details/69943011