Java面试-11mysql数据库

1、数据库文件结构

 

网络连接层：提供与mysql服务器建立连接的支持，负责连接管理、授权认证、安全等。
核心服务层：主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和查询缓存六个部分。
存储引擎层：负责Mysql中数据的存储与提取，与底层系统文件进行交互。
文件系统层：负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。

一、网络连接层

当客户端尝试与MySQL建立连接时，MySQL就会派发一个线程来对接这个客户端，来负责处理这个客户端接下来的所有工作。
数据库连接层的工作就是负责所有客户端的连接工作。MySQL的连接一般都是基于TCP/IP协议的连接。

二、SQL层（核心服务层）

功能：主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和查询缓存六个部分。

管理服务和工具组件：从备份和恢复的安全性、复制、集群、管理、配置、迁移和元数据等方面对数据库进行管理。
连接池：负责存储管理客户端与数据库的连接、一个线程负责管理一个连接。
SQL接口：用户SQL命令的接口；进行DML、DDL语句、存储过程、视图、触发器等操作和管理。
解析器：对词汇语义、语法、SQL权限的检测、以及对代码的解析与验证。

查询优化器：对SQL语句查询进行优化、选取。SQL查询语句中有多种执行方式，优化器会对查询代码进行最优化的选取方式来进行查询。
缓存和缓冲区：主要分为了读取缓存与写入缓冲，读取缓存主要是指select语句的数据缓存，当然也会包含一些权限缓存、引擎缓存等信息。MySQL会对经常查询的语句保存在cache中，这样在下次查询时，能够直接使用。

三、存储引擎

InnoDB：支持事务，并且提供行级的锁定。
MyISAM：查询速度快，有较好的索引优化和数据压缩技术。缺点就是不支持事务。

Memory：适合存储临时数据。HEAP存储引擎，数据保存在内存中。支持HASH索引和BTree索引。不支持支持Blog和Text等大字段。
Archive：适合存储历史数据。以zilb压缩表数据。

四、物理文件层

文件系统层是MySQL的基础，是基于物理磁盘的一个文件系统。支持NTFS、UFS、ext2/3、NFS、NAS等文件类型。该文件系统包含了数据文件、日志文件、配置文件、索引文件等各类MySQL运行时所需要的文件。

2、索引的建立

在 MySQL 中，有几种常见的索引类型，包括主键索引、唯一索引、普通索引、全文索引和组合索引。以下是它们的简要介绍：

  1. 主键索引（Primary Key Index）：

    • 主键索引用于唯一标识表中的每一行数据，确保每行数据都有一个唯一的标识符。主键索引可以保证数据的完整性和唯一性。
    • 在创建表时，通过指定某个列为主键（PRIMARY KEY）来创建主键索引。

  2. 唯一索引（Unique Index）：

    • 唯一索引确保索引列中的值是唯一的，但允许空值（NULL），不同于主键索引，唯一索引可以存在多个。
    • 通过创建唯一索引（UNIQUE INDEX）来确保索引列的唯一性。

  3. 普通索引（Normal Index）：

    • 普通索引是最基本的索引类型，它没有任何唯一性或者完整性的限制，只是加快查询速度。
    • 通过创建普通索引（INDEX）来提高特定列的查询性能。

  4. 全文索引（Full-Text Index）：

    • 全文索引用于全文搜索，可以对文本内容进行高效的搜索，支持关键词匹配、自然语言搜索等功能。
    • 在 MySQL 中，只有使用特定的存储引擎（如 InnoDB）并且指定特定的字段类型（如 TEXT 或 VARCHAR）才能创建全文索引。

  5. 组合索引（Composite Index）：

    • 组合索引是将多个列组合起来创建的索引，可以加快涉及到组合列的查询速度。
    • 通过创建组合索引（INDEX）来同时对多个列进行索引，适用于经常作为查询条件的多个列。

索引数据结构

在 MySQL 中，常见的索引数据结构包括哈希表、有序数组、二分查找、二叉查找树、AVL 树、多路平衡查找树（B-Tree）和 B+ 树。这些数据结构在实现索引时起着不同的作用，下面简要介绍它们：

  1. 哈希表（Hash Table）：

    • 哈希表通过哈希函数将关键字映射到表中的一个位置，实现快速的等值查询。在哈希索引中，索引列的值经过哈希计算后与哈希表的索引进行匹配，用于快速定位数据。

  2. 有序数组（Ordered Array）：

    • 有序数组是一种将数据按照顺序存储的数据结构，可以通过二分查找来快速定位数据。在某些情况下，可以使用有序数组实现索引以加快查询速度。

  3. 二分查找（Binary Search）：

    • 二分查找是一种查找算法，适用于有序数组或有序列表。通过比较中间元素与目标值的大小关系，不断缩小查找范围，最终找到目标值的位置。

  4. 二叉查找树（Binary Search Tree）：

    • 二叉查找树是一种树形结构，左子树上所有节点的值均小于根节点的值，右子树上所有节点的值均大于根节点的值。通过二叉查找树可以实现快速的插入、删除和查找操作。

  5. AVL 树：

    • AVL 树是一种自平衡的二叉查找树，保持树的高度平衡，确保在最坏情况下的查找性能仍然是 O(log n)。在数据库索引中，AVL 树可用于加速数据的检索。

  6. 多路平衡查找树（B-Tree）：

    • B-Tree 是一种多路平衡查找树，能够高效地支持范围查询和有序遍历。在数据库索引中，B-Tree 被广泛应用于提升查询性能。

  7. B+ 树（B+Tree）：

    • B+ 树是 B-Tree 的一种变体，将非叶子节点仅用作索引，而叶子节点包含全部数据。B+ 树通常用于数据库索引，具有更高的查询性能和更好的顺序访问性能。

这些数据结构在 MySQL 数据库中扮演重要的角色，用于构建不同类型的索引以支持高效的数据检索和查询操作。

存储容量

举个例子:假设一条记录是 1K，一个叶子节点(一页)（一个节点存储16K的大小）可以存储 16 条记录。非叶子节点可以存储多少个指针?

 

 

假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点(一页)可以存储 16384/14=1170 个这样的 单元(键值+指针)，代表有 1170 个指针。

树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170*1170*16=21902400。

在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。

所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

查询效率

我们来看一下 B+Tree 的数据搜寻过程:

    1. 例如我们要查找 35，在根节点就找到了键值，但是因为它不是页子节点，所以会继续往下搜寻，25 是[17,35)的左闭右开的区间的临界值，所以会走中间的子节点，然 后继续搜索，它又是[28,34)的左闭右开的区间的临界值，所以会走左边的子节点，最后在叶子节点上找到了需要的数据。
    2. 如果是范围查询，比如要查询从 22 到 60 的数据，当找到 22 之后，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高 了区间查询效率(不需要返回上层父节点重复遍历查找)。
    3. 添加了指向相邻叶节点的指针**，形成了带有顺序访问指针的B+Tree，这样做是为了**提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

B+树特点总结

  1. 变种 B Tree： B+Tree 是 B Tree 的变种，能够解决 B Tree 能解决的问题，并在某些方面有所优化。
  2. 存储能力增强： 每个节点存储更多关键字，且具有更多的分支路数，这使得 B+Tree 在存储大量数据时具有优势。
  3. 扫库、扫表能力增强： 对于全表扫描操作，只需要遍历叶子节点即可获取所有数据，无需遍历整棵 B+Tree，提高了扫描能力。
  4. 磁盘读写能力强： 根节点和枝节点不保存数据区，因此单个节点可以保存更多关键字，每次磁盘加载的关键字数量更多，提高了磁盘读写能力。
  5. 排序能力增强： 叶子节点上有下一个数据区的指针，形成链表结构，使得 B+Tree 在排序操作上具有优势。
  6. 稳定的效率： 由于 B+Tree 永远是在叶子节点获取数据，因此 IO 次数是稳定的，从而提高了查询效率的稳定性。

索引使用原则

索引在数据库中起着至关重要的作用，它们可以显著提高查询效率和性能。在建立索引时，考虑列的离散度和组合索引最左匹配原则是非常重要的两个因素。

1. 列的离散度： 列的离散度指的是该列中不同值的数量与总的行数之比。离散度越高，意味着该列的值分布越均匀，重复值较少，查询时索引的效果会更好。选择具有高离散度的列来建立索引通常会带来更好的性能提升。相反，如果列的离散度较低，例如只有几个不同的取值，那么为该列建立索引可能不会带来太大的性能提升。

2. 组合索引最左匹配原则： 组合索引是指在多个列上建立的联合索引。在使用组合索引时，遵循最左匹配原则非常重要。这意味着在查询时，只有从组合索引的最左边列开始依次匹配，才能充分利用索引的效果。如果查询条件中未包含组合索引的最左边列，那么索引可能无法被使用，导致查询性能下降。

覆盖索引

回表

在聚簇索引里，通过辅助索引查找数据，先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

例如:select * from user where name = ‘lisi’;

 

 

覆盖索引

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

联合索引的存储结构

如下图所示，表的数据如右图，ID 为主键，创建的联合索引为 (A，B)，注意联合索引顺序，左图是模拟的联合索引的 B+ Tree 存储结构

  • 叶子节点是线性排列，并且每个节点的数据排列顺序和创建索引字段的顺序一致。如 1,1，1,2，3,1 是对应着联合索引 (A，B) 字段顺序的，可以对照右图看
  • 非叶子节点存储完整的索引关键字信息，排列规则和叶子节点一致。如 2,8，4,3 是对应着联合索引 (A，B) 字段顺序的
  • InnoDB 会使用主键索引 B+ Tree 维护索引和数据文件，同样联合索引 (A，B) 也会生成一个 B+ Tree ，只不过联合索引 B+ Tree的 data 部分存储的是联合索引所在行的主键值。如 01，02，102，09 等它们是联合索引所在行的主键值
  • 根据图中叶子节点的数据可以看出，所有的数据都是按照列 A 进行排序的 1，1，1，2，3，3，4，4；B 列的顺序为 1，2，2，1，1，5，1，5，B 列是 全局 无序的。如果使用 B = 1 这种查询条件是没有办法使用到索引的，因为联合索引首先是按 A 排序的（使用 最左 的字段来构建 B+ Tree），B 是无序的
  • 我们还可以发现在 A 值相等的情况下，B 值又是按顺序排列的，但是这种顺序是局部的。如 1,1，1,2，1,2，3,1，3,5。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如 A = 1 and B = 2，A,B 字段都可以使用索引，因为在 A 值确定的情况下 B 是相对有序的，而 A > 1 and B = 2，A 字段可以匹配上索引，但 B 值不可以，因为 A 的值是一个范围，在这个范围中 B 是无序的

联合索引的查询流程

  • InnoDB 会使用主键索引 B+ Tree 维护索引和数据文件，同样联合索引 (A，B) 也会生成一个 B+ Tree ，只不过联合索引 B+ Tree的 data 部分存储的是联合索引所在行的主键值
  • 拿到联合索引所在行的主键值后，在通过主键索引 B+ Tree 就可以直接拿到具体的行数据了

最左前缀匹配原则

最左优先，以最左边的为起点任何 连续 的索引都能匹配上，但遇到范围查询 (>、<、between、like) 就会停止匹配。之所以会有 最左前缀匹配原则和联合索引的索引构建方式及存储结构 是有关系的

  • 联合索引是使用多列索引的第一列（最左）构建的 B+ Tree
  • 用上面 (A，B) 的例子就是使用 A 列构建的 B+ Tree ，当 A 列值相等时再以 B 列进行排序（相对有序）

最左前缀匹配原则说明

  • 如查询 A = 1，那么 A 字段肯定是可以使用索引的，因为所构建的索引 B+ Tree 是依据最左即 A 列构建的，数据自然是全局有序的
  • 如查询 A = 1 and B = 2，那么 A,B 字段都可以使用索引，因为在 A 值确定的情况下 B 是相对有序的
  • 如查询 B= 2 and A = 1，对于这种情况 A,B 字段都可以使用索引的。MySQL 查询优化器会判断纠正这条 sql 语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以 MySQL 查询优化器会最终以这种顺序进行查询执行
  • 另外如查询 B = 2，这种情况 也是会用到索引的；可观察 explain 结果中的 type 字段是一个 index，index 与 ALL 的区别为 index 类型只遍历索引树，这通常比 ALL 快。而上述 3 种查询情况 type 字段是一个 ref。

索引条件下推(ICP)

“索引条件下推”，称为 Index Condition Pushdown (ICP)，这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”，注意我们这里特意强调了“一个”，这是因为这样的索引优化不是用于多表连接而是用于单表扫描，确切地说，是单表利用索引进行扫描以获取数据的一种方式。 它的作用如下

  • 一是说明减少完整记录（一条完整元组）读取的个数；
  • 二是说明对于InnoDB聚集索引无效，只能是对SECOND INDEX这样的非聚簇索引有效。

3、事务管理

1111

4、数据库锁

在数据库系统中，锁是用来管理对数据的并发访问的机制。通过使用不同类型的锁，可以确保数据的一致性和完整性，避免并发访问导致的问题。共享锁和排他锁是两种常见的锁类型，它们在处理并发读写时起着重要作用。

  • 共享锁（Shared Lock）：也称为读锁（Read Lock），允许多个事务同时以共享模式访问同一资源，但阻止其他事务以排他模式访问该资源。共享锁之间互不阻塞，因为多个事务可以同时读取数据而不会相互干扰。共享锁适用于读取操作，可以提高系统的并发性能。

  • 排他锁（Exclusive Lock）：也称为写锁（Write Lock），当一个事务获取了对资源的排他锁后，其他事务无法再以任何方式访问该资源，直到持有排他锁的事务释放锁。排他锁用于写入操作，确保在给定时间内只有一个事务可以对资源进行写操作，防止数据的混乱或损坏。

通过合理地运用共享锁和排他锁，数据库系统可以实现对数据的有效管理，保证数据的一致性和完整性，同时最大程度地提升系统的并发性能和安全性。

 

 

根据锁的粒度，可以将锁分为不同的类型，包括全局锁、表级锁和行级锁：

  1. 全局锁（Global Lock）：

    • 特点：全局锁会锁定整个数据库实例，阻止对整个数据库的读写操作。一旦数据库被全局锁定，其他会话无法对数据库进行任何修改。
    • 用途：通常用于在备份数据库时或者进行数据库结构变更（比如表结构变更）时使用，以确保在操作期间不会有其他会话对数据库进行修改。
    • MySQL中的实现：可以通过FLUSH TABLES WITH READ LOCK命令来实现全局锁。

  2. 表级锁（Table-level Lock）：

    • 特点：表级锁会锁定整个表，阻止对该表的并发写操作，但允许其他会话对表中的其他行进行读取操作。
    • 用途：通常用于需要对整个表进行操作时，比如进行大规模数据的修改或者数据表的重建等操作。
    • MySQL中的实现：MySQL中的表级锁包括表锁读（Table Read Lock）和表锁写（Table Write Lock），可以通过锁定表的方式来实现。

  3. 行级锁（Row-level Lock）：

    • 特点：行级锁会锁定表中的单行数据或行索引，允许针对某一行的并发读写操作。行级锁可以提高并发性能，因为只有受影响的行会被锁定，其他行仍然可以被并发访问。
    • 用途：通常用于需要精细控制单行数据的并发访问的操作，可以提高系统的并发性能。
    • MySQL中的实现：InnoDB存储引擎支持行级锁，可以通过在事务中使用SELECT ... FOR UPDATE语句来实现行级锁。

不同粒度的锁适用于不同的场景，全局锁适用于需要锁定整个数据库实例的操作，表级锁适用于需要锁定整个表的操作，而行级锁则适用于需要精细控制单行数据的并发访问的操作。正确选择锁的粒度可以提高系统的并发性能，并确保数据的完整性和一致性。

根据锁的功能，可以将锁分为共享锁（S锁，也称为读锁）和排他锁（X锁，也称为写锁）：

  1. 共享锁 (Shared Locks, S锁，读锁)：共享锁允许多个事务同时对同一资源进行读取操作，并且不阻止其他事务对该资源加共享锁。在MySQL中，使用SELECT ... LOCK IN SHARE MODE语句可以给记录加上共享锁，从而允许其他事务再加读锁。

  2. 排他锁 (Exclusive Locks, X锁，写锁)：排他锁用于确保在给定时间内只有一个事务可以对资源进行写操作，并且不允许其他事务再加读锁或者写锁。在MySQL中，使用SELECT ... FOR UPDATE语句可以给记录加上排他锁，从而阻止其他事务对该记录进行读取或写入操作。

通过使用共享锁和排他锁，可以实现对数据的并发访问控制，确保数据的一致性和完整性。共享锁适用于读取操作，可以提高系统的并发性能，而排他锁适用于写入操作，可以确保数据的正确性。

行锁四兄弟

行锁四兄弟是指MySQL中的四种行级锁：记录锁（Record Lock）、间隙锁（Gap Lock）、临键锁（Next-Key Lock）和插入意向锁（Insert Intention Lock）。这些锁主要用于InnoDB存储引擎中，用于保证并发事务操作时数据的一致性。下面详细说明它们的使用场景和应用场景：

  1. 记录锁（Record Lock）：

    • 使用场景：当事务对某行数据进行读取并加上排他锁（X锁）时，会产生记录锁，阻止其他事务对同一行数据进行写操作。
    • 应用场景：适用于需要对单个记录进行更新或删除操作时，确保其他事务不能同时修改该行数据，以维护数据完整性。

    • 在数据库中，特定记录通常指的是通过唯一标识符（如主键）或具有唯一性约束的字段来确定的记录。以下是一些确定特定记录的常见方法：

      1. 使用主键：如果表定义了主键，那么主键对应的记录就是特定记录。主键保证了每条记录的唯一性，因此可以通过主键来确定特定记录。

      2. 使用唯一索引：除了主键外，某些表可能会定义唯一索引，这样索引字段的取值也具有唯一性，从而可以通过唯一索引来确定特定记录。

      3. 使用其他唯一性约束：表中可能存在其他字段或字段组合上的唯一性约束，也可以通过这些约束来确定特定记录。

      4. 使用其他唯一性字段：即使没有显式定义主键或唯一索引，某些字段的取值可能也是唯一的，可以通过这些字段来确定特定记录。

    • sql案例： 
      START TRANSACTION;
SELECT * FROM table_name WHERE id = 10 FOR UPDATE;
-- 对id为10的记录加排他锁，其他事务无法对该行进行修改操作
COMMIT;

  2. 间隙锁（Gap Lock）：

    • 使用场景：当事务使用范围条件进行查询时，在索引范围内的间隙会被加上间隙锁，阻止其他事务在这个范围内插入数据。
    • 应用场景：适用于防止幻读情况的发生，保证范围查询的准确性，避免其他事务在查询范围内插入新数据。
    • sql案例：

      START TRANSACTION;
      SELECT * FROM table_name WHERE id > 100 AND id < 200 FOR UPDATE;
      -- 在id范围为(100, 200)的间隙上加间隙锁，阻止其他事务在这个范围内插入数据
      COMMIT;

       

  3. 临键锁（Next-Key Lock）：

    • 使用场景：在InnoDB中，临键锁是记录锁和间隙锁的组合，用于锁定一个范围内的记录和间隙，同时阻止其他事务插入新数据并保护已有数据。
    • 应用场景：适用于范围查询操作，保证范围内的数据读取和写入的一致性，同时防止幻读和数据不一致的情况发生。
    • sql案例：

      START TRANSACTION;
      SELECT * FROM table_name WHERE id > 100 AND id < 200 FOR UPDATE;
      -- 在id范围为(100, 200)的记录和间隙上加临键锁，确保持有该范围内的读写一致性
      COMMIT;

       

  4. 插入意向锁（Insert Intention Lock）：

    • 使用场景：当事务准备向表中插入新数据时，会先获取插入意向锁，表示事务准备在表中插入新数据。
    • 应用场景：适用于多个事务同时向同一表插入数据时，保证插入的顺序和数据的正确性，避免死锁和数据错乱。
    • sql案例： 
      START TRANSACTION;
INSERT INTO table_name (id, name) VALUES (150, 'New Record');
-- 在准备插入新记录之前，获取插入意向锁，表示事务将要在表中插入新数据
COMMIT;

这些行锁在InnoDB存储引擎中起着重要的作用，通过精细控制数据的访问和修改，保证了事务的并发性能和数据的一致性。合理使用这些行锁可以避免数据竞争和并发异常，提高系统的稳定性和可靠性。

5、日志文件类型

11111

6、性能调优

11111

7、sql执行过程

MySQL 整个查询执行过程，总的来说分为 6 个步骤 :

  1. 连接：客户端向 MySQL 服务器发送一条查询请求，与connectors交互：连接池认证相关处理。
  2. 缓存：服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段。
  3. 解析：服务器进行SQL解析(词法语法)、预处理。
  4. 优化：再由优化器生成对应的执行计划。
  5. 执行：MySQL 根据执行计划，调用存储引擎的 API来执行查询。
  6. 结果：将结果返回给客户端，同时缓存查询结果。

 

8、mybatis相关使用注意事项

 

 

参考文献：

MySQL锁篇 - 知乎 (zhihu.com)

MySQL索引基础到原理讲解，一篇文章就够了！ - 知乎 (zhihu.com)

MySQL数据库架构_mysql数据库架构方案-CSDN博客

 MySQL架构详解 - 知乎 (zhihu.com)

MySQL事务&&锁&&MVCC (baidu.com)

posted @ 2024-03-24 10:09  扰扰  阅读(3)  评论(0)  编辑  收藏  举报