数据库知识

发表于 更新于 分类于 ， ， 本文字数： 15k 阅读时长 ≈ 14 分钟

数据库范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。

第一范式 1NF

定义： 属于第一范式关系的所有属性都不可再分，即数据项不可分。

理解： 第一范式强调数据表的原子性，是其他范式的基础。

第一范式

第二范式 2NF

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。

第二范式

第三范式 3NF

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。

第三范式

事务

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行 。事务是一个不可分割的工作逻辑单元 事务必须具备以下四个属性，简称 ACID 属性：

  1. 原子性（Atomicity）

    事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。

  2. 一致性（Consistency）

    当事务完成时，数据必须处于一致状态。

  3. 隔离性（Isolation）

    对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。

  4. 持久性（Durability）

    事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性。

数据库并发策略

并发控制一般采用三种方法，分别是乐观锁和悲观锁以及时间戳。

乐观锁

乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据;悲观锁就刚好相反，觉得自 己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度;时间 戳就是不加锁，通过时间戳来控制并发出现的问题。

悲观锁

悲观锁就是在读取数据的时候，为了不让别人修改自己读取的数据，就会先对自己读取的数据加 锁，只有自己把数据读完了，才允许别人修改那部分数据，或者反过来说，就是自己修改某条数 据的时候，不允许别人读取该数据，只有等自己的整个事务提交了，才释放自己加上的锁，才允 许其他用户访问那部分数据。

时间戳

时间戳就是在数据库表中单独加一列时间戳，比如“TimeStamp”，每次读出来的时候，把该字 段也读出来，当写回去的时候，把该字段加 1，提交之前 ，跟数据库的该字段比较一次，如果比数 据库的值大的话，就允许保存，否则不允许保存，这种处理方法虽然不使用数据库系统提供的锁 机制，但是这种方法可以大大提高数据库处理的并发量，

以上悲观锁所说的加“锁”，其实分为几种锁，分别是:排它锁(写锁)和共享锁(读锁)。

数据库锁

行级锁

行级锁是一种排他锁，防止其他事务修改此行;在使用以下语句时，Oracle 会自动应用行级锁:

  1. INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
  2. SELECT … FOR UPDATE 语句允许用户一次锁定多条记录进行更新
  3. 使用 COMMIT 或 ROLLBACK 语句释放锁。

表级锁

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

页级锁

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

基于 Redis 分布式锁

  1. 获取锁的时候，使用 setnx(SETNX key val:当且仅当 key 不存在时，set 一个 key 为 val 的字符串，返回 1;若 key 存在，则什么都不做，返回 0)加锁，锁的 value 值为一个随机生成的 UUID，在释放锁的时候进行判断。并使用 expire 命令为锁添 加一个超时时间，超过该时间则自动释放锁。
  2. 获取锁的时候调用 setnx，如果返回 0，则该锁正在被别人使用，返回 1 则成功获取 锁。 还设置一个获取的超时时间，若超过这个时间则放弃获取锁。
  3. 释放锁的时候，通过 UUID 判断是不是该锁，若是该锁，则执行 delete 进行锁释放。

Mysql

MySQL 有哪些数据类型？

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。具体可以看看 《MySQL 数据类型》 文档。

  • 正确的使用数据类型，对数据库的优化是非常重要的。

MySQL 中 varchar 与 char 的区别？varchar(50) 中的 50 代表的涵义？

  • 1、varchar 与 char 的区别，char 是一种固定长度的类型，varchar 则是一种可变长度的类型。

  • 2、varchar(50) 中 50 的涵义最多存放 50 个字符。varchar(50) 和 (200) 存储 hello 所占空间一样，

    但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fixed_length 计算 col 长度(memory引擎也一样)。

    所以，实际场景下，选择合适的 varchar 长度还是有必要的。

int(11) 中的 11 代表什么涵义？

int(11) 中的 11 ，不影响字段存储的范围，只影响展示效果。具体可以看看 《MySQL 中 int 长度的意义》 文章。

金额(金钱)相关的数据，选择什么数据类型？

  • 方式一，使用 int 或者 bigint 类型。如果需要存储到分的维度，需要 *100 进行放大。
  • 方式二，使用 decimal 类型，避免精度丢失。如果使用 Java 语言时，需要使用 BigDecimal 进行对应。

一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把 MySQL 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15？

  • 一般情况下，我们创建的表的类型是 InnoDB ，如果新增一条记录（不重启 MySQL 的情况下），这条记录的 ID 是18 ；但是如果重启 MySQL 的话，这条记录的 ID 是 15 。因为 InnoDB 表只把自增主键的最大 ID 记录到内存中，所以重启数据库或者对表 OPTIMIZE 操作，都会使最大 ID 丢失。
  • 但是，如果我们使用表的类型是 MyISAM ，那么这条记录的 ID 就是 18 。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里面，重启 MYSQL 后，自增主键的最大 ID 也不会丢失。

最后，还可以跟面试官装个 x ，生产数据，不建议进行物理删除记录。

表中有大字段 X(例如：text 类型)，且字段 X 不会经常更新，以读为为主，请问您是选择拆成子表，还是继续放一起?写出您这样选择的理由

  • 拆带来的问题：连接消耗 + 存储拆分空间。

    如果能容忍拆分带来的空间问题，拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序 IO ，减少连接消耗，最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗。

  • 不拆可能带来的问题：查询性能。

    如果能容忍不拆分带来的查询性能损失的话，上面的方案在某个极致条件下肯定会出现问题，那么不拆就是最好的选择。

实际场景下，例如说商品表数据量比较大的情况下，会将商品描述单独存储到一个表中。即，使用拆的方案。

MySQL 有哪些存储引擎？

MySQL 提供了多种的存储引擎：

  • InnoDB
  • MyISAM
  • MRG_MYISAM
  • MEMORY
  • CSV
  • ARCHIVE
  • BLACKHOLE
  • PERFORMANCE_SCHEMA
  • FEDERATED
  • …

具体每种存储引擎的介绍，可以看看 《数据库存储引擎》 。

如何选择合适的存储引擎？

提供几个选择标准，然后按照标准，选择对应的存储引擎即可，也可以根据 常用引擎对比 来选择你使用的存储引擎。使用哪种引擎需要根据需求灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎，将会提高整个数据库的性能。

  1. 是否需要支持事务。

  2. 对索引和缓存的支持。

  3. 是否需要使用热备。

  4. 崩溃恢复，能否接受崩溃。

  5. 存储的限制。

  6. 是否需要外键支持。

    目前开发已经不考虑外键，主要原因是性能。具体可以看看 《从 MySQL 物理外键开始的思考》 文章。

目前，MySQL 默认的存储引擎是 InnoDB ，并且也是最主流的选择。主要原因如下：

  • 【最重要】支持事务。
  • 支持行级锁和表级锁，能支持更多的并发量。
  • 查询不加锁，完全不影响查询。
  • 支持崩溃后恢复。

在 MySQL5.1 以及之前的版本，默认的存储引擎是 MyISAM ，但是目前已经不再更新，且它有几个比较关键的缺点：

  • 不支持事务。
  • 使用表级锁，如果数据量大，一个插入操作锁定表后，其他请求都将阻塞。

请说明 InnoDB 和 MyISAM 的区别

InnoDB MyISAM
事务 支持 不支持
存储限制 64TB 无
锁粒度 行锁 表锁
崩溃后的恢复 支持 不支持
外键 支持 不支持
全文检索 5.7 版本后支持 支持

请说说 InnoDB 的 4 大特性

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

为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢？

对于 SELECT COUNT(*) FROM table 语句，在没有 WHERE 条件的情况下，InnoDB 比 MyISAM 可能会慢很多，尤其在大表的情况下。因为，InnoDB 是去实时统计结果，会全表扫描；而 MyISAM 内部维持了一个计数器，预存了结果，所以直接返回即可。

详细的原因，胖友可以看看 《高性能 MySQL 之 Count 统计查询》 博客。

各种不同 MySQL 版本的 Innodb 的改进？(了解)

MySQL5.6 下 Innodb 引擎的主要改进：

  1. online DDL
  2. memcached NoSQL 接口
  3. transportable tablespace（ alter table discard/import tablespace）
  4. MySQL 正常关闭时，可以 dump 出 buffer pool 的（ space， page_no），重启时 reload，加快预热速度
  5. 索引和表的统计信息持久化到 mysql.innodb_table_stats 和 mysql.innodb_index_stats，可提供稳定的执行计划
  6. Compressed row format 支持压缩表

MySQL5.7 下 Innodb 引擎的主要改进：

  • 1、修改 varchar 字段长度有时可以使用

    这里的“有时”，指的是也有些限制。可见 《MySQL 5.7 online ddl 的一些改进》 。

  • 2、Buffer pool 支持在线改变大小

  • 3、Buffer pool 支持导出部分比例

  • 4、支持新建 innodb tablespace，并可以在其中创建多张表

  • 5、磁盘临时表采用 innodb 存储，并且存储在 innodb temp tablespace 里面，以前是 MyISAM 存储

  • 6、透明表空间压缩功能

【重点】什么是索引？

索引，类似于书籍的目录，想找到一本书的某个特定的主题，需要先找到书的目录，定位对应的页码。

MySQL 中存储引擎使用类似的方式进行查询，先去索引中查找对应的值，然后根据匹配的索引找到对应的数据行。

索引有什么好处？

  1. 提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
  2. 降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。

索引有什么坏处？

  1. 占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。
  2. 降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

索引的使用场景？

  • 1、对非常小的表，大部分情况下全表扫描效率更高。

  • 2、对中大型表，索引非常有效。

  • 3、特大型的表，建立和使用索引的代价随着增长，可以使用分区技术来解决。

    实际场景下，MySQL 分区表很少使用，原因可以看看 《互联网公司为啥不使用 MySQL 分区表？》 文章。

    对于特大型的表，更常用的是“分库分表”，目前解决方案有 Sharding Sphere、MyCAT 等等。

索引的类型？

索引，都是实现在存储引擎层的。主要有六种类型：

  • 1、普通索引：最基本的索引，没有任何约束。

  • 2、唯一索引：与普通索引类似，但具有唯一性约束。

  • 3、主键索引：特殊的唯一索引，不允许有空值。

  • 4、复合索引：将多个列组合在一起创建索引，可以覆盖多个列。

  • 5、外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。

  • 6、全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。

    常用的全文索引引擎的解决方案有 Elasticsearch、Solr 等等。最为常用的是 Elasticsearch 。

具体的使用，可以看看 《服务端指南 数据存储篇 | MySQL（03） 如何设计索引》 。

MySQL 索引的“创建”原则？

  • 1、最适合索引的列是出现在 WHERE 子句中的列，或连接子句中的列，而不是出现在 SELECT 关键字后的列。

  • 2、索引列的基数越大，索引效果越好。

    具体为什么，可以看看如下两篇文章：

  • 3、根据情况创建复合索引，复合索引可以提高查询效率。

    因为复合索引的基数会更大。

  • 4、避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。

  • 5、主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。

  • 6、对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间。

MySQL 索引的“使用”注意事项？

  • 1、应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

    注意，column IS NULL 也是不可以使用索引的。

  • 2、应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT id FROM t WHERE num = 10 OR num = 20 。

  • 3、应尽量避免在 WHERE 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

  • 4、应尽量避免在 WHERE 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

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

  • 6、复合索引遵循前缀原则。

  • 7、如果 MySQL 评估使用索引比全表扫描更慢，会放弃使用索引。如果此时想要索引，可以在语句中添加强制索引。

  • 8、列类型是字符串类型，查询时一定要给值加引号，否则索引失效。

  • 9、LIKE查询，%不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引。

关于这块，可以看看 《服务端指南 数据存储篇 | MySQL（04） 索引使用的注意事项》 文章，写的更加细致。

以下三条 SQL 如何建索引，只建一条怎么建？

1
2
3
WHERE a = 1 AND b = 1
WHERE b = 1
WHERE b = 1 ORDER BY time DESC
  • 以顺序 b , a, time 建立复合索引，CREATE INDEX table1_b_a_time ON index_test01(b, a, time)。
  • 对于第一条 SQL ，因为最新 MySQL 版本会优化 WHERE 子句后面的列顺序，以匹配复合索引顺序。

想知道一个查询用到了哪个索引，如何查看?

EXPLAIN 显示了 MYSQL 如何使用索引来处理 SELECT 语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在 SELECT 语句前加上 EXPLAIN 就可以了。感兴趣的胖友，可以详细看看 《MySQL explain 执行计划详细解释》 。

【重点】MySQL 索引的原理？

解释 MySQL 索引的原理，篇幅会比较长，并且网络上已经有靠谱的资料可以看，所以艿艿这里整理了几篇，胖友可以对照着看。

MySQL 有哪些索引方法？

艿艿：这个问题是索引方法 Index Method ，上面的索引类型 Index Type 。

在 MySQL 中，我们可以看到两种索引方式：

  • B-Tree 索引。
  • Hash 索引。

实际场景下，我们基本仅仅使用 B-Tree 索引。详细的对比可以看看 《MySQL BTree 索引和 hash 索引的区别》 。

对于 Hash 索引，我们了解即可，面试重点是掌握 B-Tree 索引的原理。

什么是 B-Tree 索引？

B-Tree 是为磁盘等外存储设备设计的一种平衡查找树。因此在讲 B-Tree 之前先了解下磁盘的相关知识。

  • 系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

  • InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16 KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K ，在 MySQL 中可通过如下命令查看页的大小：

    1
    mysql> show variables like 'innodb_page_size';

  • 而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB 。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率。

B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组 [key, data] ，key 为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵 m 阶的 B-Tree 有如下特性：

  1. 每个节点最多有 m 个孩子。
    • 除了根节点和叶子节点外，其它每个节点至少有 Ceil(m/2) 个孩子。
    • 若根节点不是叶子节点，则至少有 2 个孩子。
  2. 所有叶子节点都在同一层，且不包含其它关键字信息。
  3. 每个非叶子节点包含 n 个关键字信息（P0,P1,…Pn, k1,…kn）
    • 关键字的个数 n 满足：ceil(m/2)-1 <= n <= m-1
    • ki(i=1,…n) 为关键字，且关键字升序排序。
    • Pi(i=0,…n) 为指向子树根节点的指针。P(i-1) 指向的子树的所有节点关键字均小于 ki ，但都大于 k(i-1) 。

B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个 3 阶的 B-Tree：

B-Tree结构

  • 每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的 key 和三个指向子树根节点的 point ，point 存储的是子节点所在磁盘块的地址。两个 key 划分成的三个范围域，对应三个 point 指向的子树的数据的范围域。
  • 以根节点为例，key 为 17 和 35 ，P1 指针指向的子树的数据范围为小于 17 ，P2 指针指向的子树的数据范围为 [17~35] ，P3 指针指向的子树的数据范围为大于 35 。

模拟查找 key 为 29 的过程：

  • 1、根据根节点找到磁盘块 1 ，读入内存。【磁盘I/O操作第1次】
  • 2、比较 key 29 在区间（17,35），找到磁盘块 1 的指针 P2 。
  • 3、根据 P2 指针找到磁盘块 3 ，读入内存。【磁盘I/O操作第2次】
  • 4、比较 key 29 在区间（26,30），找到磁盘块3的指针P2。
  • 5、根据 P2 指针找到磁盘块 8 ，读入内存。【磁盘I/O操作第3次】
  • 6、在磁盘块 8 中的 key 列表中找到 key 29 。

分析上面过程，发现需要 3 次磁盘 I/O 操作，和 3 次内存查找操作。由于内存中的 key 是一个有序表结构，可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。B-Tree 相对于 AVLTree 缩减了节点个数，使每次磁盘 I/O 取到内存的数据都发挥了作用，从而提高了查询效率。

什么是 B+Tree 索引？

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用 B+Tree 实现其索引结构。

下面这一段，面试非常关键。

从上一节中的 B-Tree 结构图中可以看到，每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度。

B+Tree 相对于 B-Tree 有几点不同：

  • 非叶子节点只存储键值信息。
  • 所有叶子节点之间都有一个链指针。
  • 数据记录都存放在叶子节点中。

将上一节中的 B-Tree 优化，由于 B+Tree 的非叶子节点只存储键值信息，假设每个磁盘块能存储 4 个键值及指针信息，则变成 B+Tree 后其结构如下图所示：

B+Tree结构

  • 通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有 22 条数据记录，看不出 B+Tree 的优点，下面做一个推算：

  • InnoDB 存储引擎中页的大小为 16KB，一般表的主键类型为 INT（占用4个字节） 或 BIGINT（占用8个字节），指针类型也一般为 4 或 8 个字节，也就是说一个页（B+Tree 中的一个节点）中大概存储 16KB/(8B+8B)=1K 个键值（因为是估值，为方便计算，这里的 K 取值为〖10〗^3）。也就是说一个深度为 3 的 B+Tree 索引可以维护10^3 10^3 10^3 = 10亿 条记录。
  • 实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 24 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 13 次磁盘 I/O 操作。

B-Tree 有哪些索引类型？

在 B+Tree 中，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

注意，这里的索引类型，和上面的索引类型，还是对的上的噢。

  • 主键索引的叶子节点存的数据是整行数据( 即具体数据 )。在 InnoDB 里，主键索引也被称为聚集索引（clustered index）。

  • 非主键索引的叶子节点存的数据是整行数据的主键，键值是索引。在 InnoDB 里，非主键索引也被称为

    辅助索引

    （secondary index）。

    二级索引的叶节点存储的是主键值，而不是行指针，这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，需要进过两步：

  • 首先，InnoDB 存储引擎会遍历辅助索引找到主键。
  • 然后，再通过主键在聚集索引中找到完整的行记录数据。

另外，InnoDB 通过主键聚簇数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义个主键作为聚簇索引。

再另外，可能有胖友有和艿艿的一样疑惑，在辅助索引如果相同的索引怎么存储？最终存储到 B+Tree 非子节点中时，它们对应的主键 ID 是不同的，所以妥妥的。

聚簇索引的注意点有哪些？

聚簇索引表最大限度地提高了 I/O 密集型应用的性能，但它也有以下几个限制：

  • 1、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键。

    关于这一点，可能面试官会换一个问法。例如，为什么主键需要是自增 ID ，又或者为什么主键需要带有时间性关联。

  • 2、更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB 表，我们一般定义主键为不可更新。

    MySQL 默认情况下，主键是允许更新的。对于 MongoDB ，其 主键是不允许更新的。

  • 3、二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

    当然，有一种情况可以无需二次查找，基于非主键索引查询，但是查询字段只有主键 ID ，那么在二级索引中就可以查找到。

  • 4、主键 ID 建议使用整型。因为，每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ，每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

什么是索引的最左匹配特性？

当 B+Tree 的数据项是复合的数据结构，比如索引 (name, age, sex) 的时候，B+Tree 是按照从左到右的顺序来建立搜索树的。

  • 比如当 (张三, 20, F) 这样的数据来检索的时候，B+Tree 会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex ，最后得到检索的数据。
  • 但当 (20, F) 这样的没有 name 的数据来的时候，B+Tree 就不知道下一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。
  • 比如当 (张三, F) 这样的数据来检索时，B+Tree 可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了。

这个是非常重要的性质，即索引的最左匹配特性。

【重点】请说说 MySQL 的四种事务隔离级别？

事务就是对一系列的数据库操作（比如插入多条数据）进行统一的提交或回滚操作，如果插入成功，那么一起成功，如果中间有一条出现异常，那么回滚之前的所有操作。

这样可以防止出现脏数据，防止数据库数据出现问题。

事务的特性指的是？

  1. 原子性 Atomicity ：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
  2. 一致性 Consistency ：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
  3. 隔离性 Isolation ：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  4. 持久性 Durability ：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的并发问题？

实际场景下，事务并不是串行的，所以会带来如下三个问题：

  • 1、脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。
  • 2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
  • 3、幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

MySQL 事务隔离级别会产生的并发问题？

事务定义了四种事务隔离级别，不同数据库在实现时，产生的并发问题是不同的。

不同的隔离级别有不同的现象，并有不同的锁定/并发机制，隔离级别越高，数据库的并发性就越差。

  • READ UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，对其他事务也都是可见的。

    会导致脏读。

  • READ COMMITTED（提交读）：事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。

    会导致不可重复读。

    这个隔离级别，也可以叫做“不可重复读”。

  • REPEATABLE READ（可重复读）：一个事务按相同的查询条件读取以前检索过的数据，其他事务插入了满足其查询条件的新数据。产生幻行。

    会导致幻读。

  • SERIALIZABLE（可串行化）：强制事务串行执行。

MySQL InnoDB 采用 MVCC 来支持高并发，实现结果如下表所示：

关于 Oracle 和 PostgreSQL ，需要胖友自己去搜索资料。

事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
读已提交（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是（x）
串行化（serializable） 否 否 否

  • MySQL 默认的事务隔离级别为可重复读（repeatable-read） 。

  • 上图的 <X> 处，MySQL 通过 MVCC + 事务第一次调用 SELECT 语句才生成快照，实现其在可重复读（repeatable-read）的隔离级别下，不存在幻读问题。也就是说，上图 <X> 处，需要改成“否”！！！！想要进一步了解的，可以看看 《MySQL InnoDB 事务 —— 一致性读(快照读)》、《MYSQL 当前读和快照读》、《【MySQL】当前读、快照读、MVCC》 文章。

    艿艿：实际上，艿艿在看完上述几篇文章，仿佛理解了，仿佛又有一点懵逼。后来在看完丁奇老师的 《MySQL 实战 45 讲》 的「08 | 事务到底是隔离的还是不隔离的？」后，稳了，通透了。

  • 😈 记住这个表的方式，我们会发现它是自左上向右下是一个对角线。当然，最好是去理解。

  • 具体的实验，胖友可以看看 《MySQL 的四种事务隔离级别》 。

  • 有些资料说可重复读解决了幻读，实际是存在的，可以通过 SELECT xxx FROM t WHERE id = ? FOR UPDATE 的方式，获得到悲观锁，禁止其它事务操作对应的数据，从而解决幻读问题。感兴趣的胖友，可以看看如下文章：

    • 必读 《MySQL 幻读的详解、实例及解决办法》 案例性更强，易懂。

      其实 RR 也是可以避免幻读的，通过对 select 操作手动加 行X锁（SELECT … FOR UPDATE 这也正是 SERIALIZABLE 隔离级别下会隐式为你做的事情），同时还需要知道，即便当前记录不存在，比如 id = 1 是不存在的，当前事务也会获得一把记录锁（因为InnoDB的行锁锁定的是索引，故记录实体存在与否没关系，存在就加 行X锁，不存在就加 next-key lock间隙X锁），其他事务则无法插入此索引的记录，故杜绝了幻读。

    • 选读 《MySQL 的 InnoDB 的幻读问题》 原理性更强，读懂会很爽。

    • 随意 《Innodb 中 RR 隔离级别能否防止幻读？》 一个简单的讨论。

【重点】请说说 MySQL 的锁机制？

表锁是日常开发中的常见问题，因此也是面试当中最常见的考察点，当多个查询同一时刻进行数据修改时，就会产生并发控制的问题。MySQL 的共享锁和排他锁，就是读锁和写锁。

  • 共享锁：不堵塞，多个用户可以同时读一个资源，互不干扰。
  • 排他锁：一个写锁会阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。

锁的粒度？

  • 表锁：系统开销最小，会锁定整张表，MyIsam 使用表锁。
  • 行锁：最大程度的支持并发处理，但是也带来了最大的锁开销，InnoDB 使用行锁。

什么是悲观锁？什么是乐观锁？

1）悲观锁

它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

艿艿：悲观锁，就是我们上面看到的共享锁和排他锁。

2）乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

艿艿：乐观锁，实际就是通过版本号，从而实现 CAS 原子性更新。

什么是死锁？

多数情况下，可以认为如果一个资源被锁定，它总会在以后某个时间被释放。而死锁发生在当多个进程访问同一数据库时，其中每个进程拥有的锁都是其他进程所需的，由此造成每个进程都无法继续下去。简单的说，进程 A 等待进程 B 释放他的资源，B 又等待 A 释放他的资源，这样就互相等待就形成死锁。

虽然进程在运行过程中，可能发生死锁，但死锁的发生也必须具备一定的条件，死锁的发生必须具备以下四个必要条件：

  • 互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。
  • 请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。
  • 不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
  • 环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合 {P0，P1，P2，•••，Pn} 中的 P0 正在等待一个 P1 占用的资源；P1 正在等待 P2 占用的资源，……，Pn 正在等待已被 P0 占用的资源。

下列方法有助于最大限度地降低死锁：

  • 设置获得锁的超时时间。

    通过超时，至少保证最差最差最差情况下，可以有退出的口子。

  • 按同一顺序访问对象。

    这个是最重要的方式。

  • 避免事务中的用户交互。

  • 保持事务简短并在一个批处理中。

  • 使用低隔离级别。

  • 使用绑定连接。

MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成(或称实现)的？为什么是这样子的？？

InnoDB 是基于索引来完成行锁。例如：SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE 。

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

【重要】MySQL 查询执行顺序？

MySQL 查询执行的顺序是：

1
2
3
4
5
6
7
8
9
10
(1)     SELECT
(2)     DISTINCT <select_list>
(3)     FROM <left_table>
(4)     <join_type> JOIN <right_table>
(5)     ON <join_condition>
(6)     WHERE <where_condition>
(7)     GROUP BY <group_by_list>
(8)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

具体的，可以看看 《SQL 查询之执行顺序解析》 文章。

【重要】聊聊 MySQL SQL 优化？

可以看看如下几篇文章：

另外，除了从 SQL 层面进行优化，也可以从服务器硬件层面，进一步优化 MySQL 。具体可以看看 《MySQL 数据库性能优化之硬件优化》 。

【加分】什么是 MVCC ？

多版本并发控制（MVCC），是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

推荐可以看看如下资料：

编写 SQL 查询语句的考题合集

因为考题比较多，艿艿就不一一列举，瞄了一些还不错的文章，如下：