每日八股-6.3Mysql1.COUNT 作用于主键列和非主键列时结果会有不同吗2.MySQL 中的内连接INNER JOIN和外连接OUTER JOIN有什么主要的区别3.能详细描述一下 MySQL 执行一条查询 SQL 语句的完整流程吗4.MySQL 中都有哪些常见的存储引擎你能简单介绍一下吗5.MySQL 支持哪些常见的索引类型呢6.InnoDB 存储引擎的索引底层是基于什么数据结构实现的呢7.能详细说说 B 树这种数据结构都有哪些特点吗8.B 树和 B 树这两种数据结构有什么主要的区别呢9.你觉得为什么 MySQL 会选择使用 B 树作为其索引结构呢10.为什么mysql的innodb引擎的索引要使用B树实现而不是红黑树实现或者说这两者的区别是什么Mysql1.COUNT 作用于主键列和非主键列时结果会有不同吗在大多数情况下COUNT() 作用于主键列和非主键列时如果这些列中不包含 NULL 值那么结果是相同的都表示符合条件的行数。然而关键的区别在于 COUNT(column_name) 的行为COUNT(column_name) 只计算指定列中非 NULL 值的数量。主键列 (Primary Key Column): 根据定义主键列的值不允许为 NULL。因此COUNT(主键列) 实际上等同于 COUNT(*) 或者 COUNT(1)它会统计表中的总行数或者符合 WHERE 子句条件的行数。非主键列 (Non-Primary Key Column):如果该非主键列不允许为 NULL (定义了 NOT NULL 约束)那么 COUNT(非主键列) 的结果也会和 COUNT(*) 相同。如果该非主键列允许为 NULL并且该列中实际存在 NULL 值那么 COUNT(非主键列) 的结果将会小于 COUNT(*)因为它只统计了该列中非 NULL 的行。总结一下 COUNT 的几种常见用法COUNT(*): 计算表中的总行数或者符合 WHERE 子句条件的行数包含 NULL 值的行。这是最常用的方式通常效率也较好。COUNT(1): 效果和 COUNT() 类似也是计算总行数。MySQL 对其进行了优化效率与 COUNT() 相当。COUNT(column_name): 计算指定列 column_name 中非 NULL 值的行数。COUNT(DISTINCT column_name): 计算指定列 column_name 中不重复且非 NULL 值的行数。-- 创建一个示例表CREATETABLEemployees(idINTPRIMARYKEY,-- 主键列 (不允许 NULL)nameVARCHAR(100)NOTNULL,-- 非主键列不允许 NULLdepartmentVARCHAR(50),-- 非主键列允许 NULLsalaryDECIMAL(10,2));-- 插入一些数据INSERTINTOemployees(id,name,department,salary)VALUES(1,Alice,HR,50000.00),(2,Bob,Engineering,70000.00),(3,Charlie,HR,55000.00),(4,David,NULL,60000.00),-- department 是 NULL(5,Eve,Engineering,NULL),-- salary 是 NULL(6,Frank,NULL,75000.00);-- department 是 NULL-- 查看表中的所有数据SELECT*FROMemployees;-- 1. COUNT(*) - 统计所有行SELECTCOUNT(*)AStotal_rowsFROMemployees;-- 预期结果: 6-- 2. COUNT(主键列) - 统计主键列 id (不允许 NULL)SELECTCOUNT(id)AScount_primary_keyFROMemployees;-- 预期结果: 6 (与 COUNT(*) 相同因为主键列不能为 NULL)-- 3. COUNT(非主键列NOT NULL) - 统计非主键列 name (不允许 NULL)SELECTCOUNT(name)AScount_name_not_nullFROMemployees;-- 预期结果: 6 (与 COUNT(*) 相同因为 name 列不允许 NULL)-- 4. COUNT(非主键列允许 NULL) - 统计非主键列 department (允许 NULL)SELECTCOUNT(department)AScount_department_nullableFROMemployees;-- 预期结果: 4 (因为 department 列有两个 NULL 值它们不被计算在内)-- 5. COUNT(非主键列允许 NULL) - 统计非主键列 salary (允许 NULL)SELECTCOUNT(salary)AScount_salary_nullableFROMemployees;-- 预期结果: 5 (因为 salary 列有一个 NULL 值它不被计算在内)2.MySQL 中的内连接INNER JOIN和外连接OUTER JOIN有什么主要的区别内连接 返回两个表中匹配的行交集。外连接 返回匹配的行加上驱动表中不匹配的行。左外连接 保留左表所有行右表不匹配则填充 NULL。右外连接 保留右表所有行左表不匹配则填充 NULL。内连接INNER JOIN它会只返回两个表中连接条件相匹配的行。也就是说只有当连接的两个表中都存在满足 ON 子句条件的记录时这条记录才会被包含在结果集中。内连接关注的是两个表的交集部分。外连接OUTER JOIN外连接则会返回两个表中匹配的行并且还会包含某个表或两个表中不匹配的行。MySQL 外连接主要分为两种左外连接LEFT JOIN 或 LEFT OUTER JOIN它会返回左表中的所有行以及右表中与左表匹配的行。如果右表中没有与左表某行匹配的记录那么在结果集中右表对应的列将会显示为 NULL。左连接适用于需要保留左表所有数据的场景。右外连接RIGHT JOIN 或 RIGHT OUTER JOIN它会返回右表中的所有行以及左表中与右表匹配的行。如果左表中没有与右表某行匹配的记录那么在结果集中左表对应的列将会显示为 NULL。右连接适用于需要保留右表所有数据的场景。-- 创建顾客表CREATETABLECustomers(CustomerIDINTPRIMARYKEY,CustomerNameVARCHAR(100));-- 创建订单表CREATETABLEOrders(OrderIDINTPRIMARYKEY,CustomerIDINT,-- 外键关联到 Customers 表的 CustomerIDOrderDateDATE,AmountDECIMAL(10,2));-- 插入顾客数据INSERTINTOCustomers(CustomerID,CustomerName)VALUES(1,Alice),(2,Bob),(3,Charlie),(4,Diana);-- Diana 没有下过订单-- 插入订单数据INSERTINTOOrders(OrderID,CustomerID,OrderDate,Amount)VALUES(101,1,2024-01-15,150.00),-- Alice的订单(102,2,2024-01-20,200.00),-- Bob的订单(103,1,2024-02-10,75.50),-- Alice的另一个订单(104,5,2024-02-15,300.00);-- 这个订单的CustomerID在Customers表中不存在-- 查看表数据 (可选)SELECT*FROMCustomers;SELECT*FROMOrders;--内连接SELECTc.CustomerName,o.OrderID,o.OrderDate,o.AmountFROMCustomers cINNERJOINOrders oONc.CustomerIDo.CustomerID;--left joinSELECTc.CustomerName,o.OrderID,o.OrderDate,o.AmountFROMCustomers c-- 左表LEFTJOINOrders oONc.CustomerIDo.CustomerID;-- 右表--right joinSELECTc.CustomerName,o.OrderID,o.OrderDate,o.AmountFROMCustomers c-- 左表RIGHTJOINOrders oONc.CustomerIDo.CustomerID;-- 右表3.能详细描述一下 MySQL 执行一条查询 SQL 语句的完整流程吗首先客户端会通过连接器与 MySQL 服务器建立连接。连接器负责处理 TCP 连接、用户身份验证和权限校验。连接成功后MySQL 会先检查查询缓存在 MySQL 8.0 版本之前。如果查询的 SQL 语句和查询条件与缓存中的记录完全一致并且查询结果未过期则会直接从缓存中返回结果避免了后续的执行过程。但由于查询缓存的命中率不高MySQL 8.0 已经移除了这个功能。如果查询缓存没有命中或者版本在 8.0 之后SQL 语句会被发送到解析器。解析器会对 SQL 语句进行词法分析和语法分析识别出语句的类型、表名、字段名等并构建一个抽象语法树AST。接下来是优化器的阶段。优化器会根据一定的规则和成本模型对语法树进行优化生成最优的执行计划。这包括选择合适的索引、决定表的连接顺序等。优化器的目标是找到执行成本最低的方案。优化完成后执行器会根据优化器生成的执行计划调用存储引擎如 InnoDB、MyISAM 等提供的接口来执行实际的数据查询操作。执行器会负责控制数据的读取和返回它就像一个调度员指挥存储引擎完成任务。查询结果返回给执行器后由执行器最终返回给客户端。4.MySQL 中都有哪些常见的存储引擎你能简单介绍一下吗MySQL 中常见的存储引擎主要有 InnoDB、MyISAM 和 Memory。我比较熟悉的是 InnoDB 引擎它也是 MySQL 的默认存储引擎。InnoDB 支持事务具有事务的提交、回滚和崩溃恢复功能这保证了数据的完整性和一致性。此外InnoDB 还支持行级锁这使得在并发环境下可以有更好的性能。MyISAM 引擎是我在学习过程中了解到的它不支持事务只支持表级锁。这意味着当一个会话正在写入 MyISAM 表时其他会话都无法读取或写入该表并发性能相对较差。但 MyISAM 在某些只读或者读多写少的场景下可能会有更高的性能因为它维护了一个表的行数执行 COUNT(*) 操作会非常快。Memory 引擎正如其名它将数据存储在内存中因此读写速度非常快。但是Memory 引擎不支持持久化存储一旦服务器发生故障或者重启数据就会丢失。它通常用于临时存储对性能要求较高的中间结果或者作为缓存。当然MySQL 还支持其他的存储引擎比如 Archive、CSV、NDB Cluster 等5.MySQL 支持哪些常见的索引类型呢MySQL 支持多种索引类型这主要取决于存储引擎的实现。不同的存储引擎支持的索引类型可能会有所不同。从存储结构上来看主要的索引类型有B 树索引这是 MySQL 中最常见和最广泛使用的索引类型尤其是在 InnoDB 和 MyISAM 存储引擎中。它是一种平衡树结构非常适合范围查询、排序查询和等值查询。哈希索引这种索引类型主要由 Memory 存储引擎实现。它的特点是查找速度非常快时间复杂度可以达到 O(1)但它不支持范围查询、排序等操作。全文索引全文索引用于在文本类型的数据中进行关键词搜索可以支持模糊匹配等功能。它主要用于解决 LIKE ‘%keyword%’ 这类查询效率低下的问题。此外我们还可以从其他角度对索引进行分类按存储方式针对 InnoDB 引擎可以分为聚簇索引和非聚簇索引。按字段特性可以分为主键索引、唯一索引、普通索引和前缀索引。按索引列数可以分为单列索引和联合索引。我个人比较常用的是 B 树索引因为它是 InnoDB 引擎默认使用的索引类型而且功能非常全面支持各种常见的查询场景。顺便提一下MySQL 选择使用树结构特别是 B 树作为索引主要是因为树结构能够很好地支持范围查询并且在磁盘 I/O 方面做了优化更适合数据库这种需要频繁进行磁盘操作的场景。像哈希索引虽然查找快但不支持范围查询而跳表在磁盘场景下的适应性不如树结构。6.InnoDB 存储引擎的索引底层是基于什么数据结构实现的呢InnoDB 存储引擎主要采用 B 树作为其索引的数据结构。B 树是一种自平衡的多路查找树。对于一个度数为 m 的 B 树它的每个节点最多可以有 m 个子节点并且所有的数据都存储在叶子节点上。叶子节点之间通常会通过指针连接起来形成一个有序链表在 InnoDB 中这个链表是双向的。B 树的一些关键特性使得它非常适合作为数据库索引的底层结构比如它的平衡性减少了磁盘的IO次数保证了查询效率数据只存储在叶子节点的特性以及叶子节点之间的链表结构都为范围查询提供了便利。7.能详细说说 B 树这种数据结构都有哪些特点吗B 树作为一种常用的索引结构它有几个非常重要的特性首先B 树的中间节点也就是非叶子节点不会存储实际的数据它们只存储索引信息也就是键值以及指向子节点的指针。这样做的好处是在相同的磁盘页大小下中间节点可以存储更多的索引从而使得 B 树更加“矮胖”降低了树的高度。更低的树高度意味着查询时需要访问的磁盘 I/O 次数更少提高了查询效率。其次B 树的所有实际数据都存储在叶子节点上。每个叶子节点包含了索引的键值以及对应的数据记录。第三个重要的特性是B 树的所有叶子节点之间会通过双向指针在 InnoDB 中串联在一起形成一个双向链表。这个特性对于进行范围查询例如查找某个区间内的数据非常高效。我们只需要找到范围的起始叶子节点然后沿着链表顺序遍历即可不需要再回到上层节点进行搜索。同时这个链表也方便进行全表扫描。最后B 树的查询性能非常稳定。因为所有的数据都存储在叶子节点所以每次查询都需要从根节点遍历到叶子节点查询路径的长度是相同的这确保了所有数据项的检索都具有相同的 I/O 延迟。而且由于 B 树通常能够保持很低的树高度例如3-4 层的 B 树可以存储千万级别的数据因此查询效率非常高。8.B 树和 B 树这两种数据结构有什么主要的区别呢B 树和 B 树都是常用的多路平衡查找树它们之间主要的区别可以从以下几个方面来说明数据存储的区别B 树的所有节点包括中间节点和叶子节点都会存储索引和实际的数据而 B 树只有叶子节点才会存储实际的数据中间节点只存储索引信息和指向子节点的指针。这意味着在存储相同数据量的情况下B 树的中间节点可以存储更多的索引因此 B 树通常会比 B 树更加“矮胖”树的高度更低从而减少了查询叶子节点所需的磁盘 I/O 次数。范围查询的区别B 树的所有叶子节点之间会通过双向指针串联在一起形成一个有序链表。这种结构使得 B 树在进行范围查询时非常高效。我们只需要找到范围的起始点然后沿着链表顺序遍历即可。而 B 树并没有将叶子节点用链表连接起来进行范围查询时可能需要通过中序遍历这可能会涉及更多节点的磁盘 I/O 操作因此在范围查询方面B 树通常比 B 树更高效。查询效率的区别在 B 树中如果我们要查找的值恰好在某个非叶子节点上那么在查找到该节点后就可以直接结束查询B 树的优点在于其查找速度可能更快尤其是在要查找的数据位于靠近根节点的非叶子节点时。然而B 树由于数据只存储在叶子节点所以每次查询都必须从根节点搜索到叶子节点查询路径的长度是固定的。从平均时间代价来看B 树可能在某些情况下会比 B 树稍快一些但是 B 树的查询性能更加稳定因为每一次成功的查询都需要访问到叶子节点具有相同的 I/O 延迟。9.你觉得为什么 MySQL 会选择使用 B 树作为其索引结构呢MySQL 选择使用 B 树作为其索引结构我认为主要有以下几个重要的原因首先B 树是一种多路平衡查找树这种结构非常适合磁盘 I/O 操作。数据库的数据通常存储在磁盘上而每次磁盘 I/O 的成本是比较高的。B 树的非叶子节点只存储索引键值和子节点指针而不存储实际的数据这使得每个节点能够存储更多的索引从而显著降低了树的高度。例如一个 3-4 层的 B 树就可以存储千万级别的数据这意味着查询一条数据只需要 3-4 次磁盘 I/O 操作大大提高了查找效率。相比之下像二叉搜索树包括平衡树和红黑树在存储大量数据时树的高度会很高导致更多的磁盘 I/O。其次B 树通过自平衡的机制节点的分裂和合并来保持树的平衡这确保了查询路径的长度是相对稳定的从而保证了查询、插入和删除操作的时间复杂度都是 O(log n)具有较好的性能稳定性。对于频繁进行增删改操作的数据库来说这一点非常重要。第三B 树特别适合范围查询。这是因为 B 树的所有叶子节点都通过链表连接在一起形成一个有序链表。在进行范围查询时我们只需要找到范围的起始叶子节点然后沿着链表顺序扫描即可非常高效。而像 B 树在进行范围查询时可能需要进行多次中序遍历。此外对比其他数据结构例如哈希表虽然等值查询很快但是不支持范围查询跳表在内存中表现良好但在磁盘场景下不如 B 树B 树虽然所有节点都存储数据可能在某些情况下等值查询更快但其范围查询性能不如 B 树且查询性能不如 B 树稳定。综上所述B 树在平衡性、查询效率特别是范围查询、以及对磁盘 I/O 的优化等方面都非常适合数据库索引的需求因此成为了 MySQL 等主流数据库系统的首选索引结构。10.为什么mysql的innodb引擎的索引要使用B树实现而不是红黑树实现或者说这两者的区别是什么从根本上说二者的选择差异源于内存与磁盘之间存取速度的巨大鸿沟。红黑树是一种自平衡的二叉查找树其查找、插入和删除操作的时间复杂度均为对数级别即O(logn)。这在内存中表现优异。然而数据库的索引文件通常非常庞大无法一次性全部加载到内存中需要存储在磁盘上。磁盘I/O输入/输出是数据库性能的主要瓶颈之一。因此数据库索引的设计核心目标之一就是最大限度地减少磁盘I/O次数。以下是B树和红黑树的主要区别以及为何B树更适合作为InnoDB的索引树的高度与磁盘I/OB树 B树是一种“矮胖”的多路平衡查找树。每个节点可以拥有远多于两个子节点这个数量称为“阶”通常一个节点的大小被设定为一个磁盘页的大小在InnoDB中默认为16KB。这意味着一个节点内可以存放大量的索引键值和指向子节点的指针。由于其“矮胖”的特性即使存储海量数据B树的高度也相对较低。例如一棵高度为3的B树通常就能支持千万级别的数据量。查询数据时从根节点到叶子节点最多只需要经过少数几次磁盘I/O极大地提升了查询效率。红黑树 红黑树是严格的二叉树每个节点最多只有两个子节点。当数据量巨大时其树的高度会远高于B树。每一次的节点访问在数据未被缓存的情况下都可能触发一次磁盘I/O。因此使用红黑树进行查询将导致频繁的磁盘读写性能急剧下降。数据存储方式与查询效率B树 B树的一个关键特性是只有叶子节点才存储实际的数据或指向数据的指针而非叶子节点仅存储索引键和指向下一层节点的指针。这样的设计使得非叶子节点可以容纳更多的索引项进一步降低了树的高度。所有的数据都存储在叶子节点层并且所有叶子节点通过一个双向链表相互连接。稳定的查询性能 由于所有数据记录都位于同一层的叶子节点任何一个键值的查找都必须从根节点走到叶子节点查询路径长度基本一致使得查询性能非常稳定。高效的范围查询 叶子节点之间的链表结构使得进行范围查询如 WHERE id BETWEEN 100 AND 200变得极其高效。数据库只需定位到范围的起始点然后通过遍历叶子节点的链表即可获取所有符合条件的数据无需再从树的根部进行多次查找。红黑树 红黑树的每个节点都可能存储数据。不稳定的查询性能 查询一个键值时可能在根节点或任何中间节点就找到了其访问深度不一查询性能不如B树稳定。范围查询效率低下 红黑树没有为范围查询进行优化。要执行范围查找需要在树中进行多次中序遍历式的查找效率远不如B树的链表遍历。补充既然 B 树这么完美为什么 Java 的 TreeMap、C 的 std::map、甚至 Linux 内核的调度器底层用的全都是红黑树而不是 B 树答案在于它们的主战场不同内存 vs 磁盘。特性 红黑树 (主场内存 RAM) B 树 (主场磁盘 Disk)I/O 成本 内存里跟着指针跳跃极快不在乎多跳几次。 磁盘寻道极慢必须尽量减少层数减少跳跃。节点大小 节点很小零散分布在内存各个角落。 节点按照磁盘“页”如 16KB对齐整块读取。插入/删除 二叉树旋转调平的代码相对简单内存里操作极快。 节点分裂、合并涉及大量数据移动代价高昂。