mysql数据库高级面试题-2024

my_styles

已于 2024-03-29 10:37:39 修改

阅读量2.6k

点赞数 24

CC 4.0 BY-SA版权

文章标签： mysql 数据库

于 2024-03-27 10:40:54 首次发布

本文链接：https://blog.youkuaiyun.com/qq_36961226/article/details/137064606

   第一范式：列不可再分
   第二范式：行可以唯一区分，主键约束，要求表中的每个非主键列完全依赖于主键列
   第三范式：表的非主属性不能依赖与其他表的非主属性外键约束
   且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

在MySQL中，索引按照索引列的类型可以分为以下几种：

以上就是MySQL常见的四种索引，这些不同类型的索引在数据库中起到了加速数据检索操作的作用，可以根据具体的需求和使用场景选择适当的索引类型。同时，需要注意索引的创建对写操作（如插入、更新、删除）可能会产生额外的开销，因此需要权衡索引的使用与数据操作的平衡。

MyISAM和InnoDB是MySQL中两种常见的存储引擎，它们在索引实现上存在以下区别：

存储方式：MyISAM使用非聚簇索引，索引文件和数据文件是分开的；而InnoDB使用聚簇索引，将索引和数据一起存储在同一个文件中。
锁机制：MyISAM采用表级锁定，意味着当对表进行写操作时，整个表都会被锁定，因此可能导致并发写操作的性能较差。而InnoDB采用行级锁定，只锁定需要修改的行，可以提供更好的并发性能和多用户写入的支持。
事务支持：MyISAM不支持事务处理，而InnoDB支持事务和ACID特性（原子性、一致性、隔离性和持久性），可以进行事务管理、回滚和恢复操作。
引用完整性：MyISAM不支持外键约束，而InnoDB支持外键约束，可以设置关联关系来保证数据的完整性。
性能特点：MyISAM在读取频繁、插入和更新较少的场景下性能较好，特别适合于读密集型应用；而InnoDB在并发写入和更新较多的情况下性能较好，适合于写入密集型应用或需要事务支持的场景。

索引下推（Index Condition Pushdown，简称ICP）是一种数据库查询优化技术，它利用了数据库引擎中的索引和过滤条件，将部分过滤工作下推到存储引擎层面进行处理，从而减少不必要的数据读取和传输。

在传统的查询执行过程中，数据库引擎首先根据索引定位到符合过滤条件的数据行，并将这些行读取到内存中，然后再进行进一步的过滤操作。而索引下推则在这一步骤中尽可能地将过滤操作下推到存储引擎层面，避免将不符合条件的数据行读取到内存中。

具体实现方式可以是通过存储引擎提供的接口或者钩子函数，让存储引擎在读取索引页时就进行额外的过滤操作。

通过索引下推，数据库系统可以在存储引擎层面根据索引和过滤条件提前过滤掉不符合条件的数据，减少了需要传递给查询引擎的数据量和内存消耗。这样可以大大减少磁盘 I/O 和数据传输的开销，提升查询性能和整体系统效率。

需要注意的是，索引下推并不是对所有类型的查询都适用，它更适用于复杂查询条件、多列条件的查询中，能够有效地减少不必要的数据读取和传输。

并发事务可以带来以下几个问题：

脏读（Dirty Read）：一个事务读取了另一个事务未提交的数据。假设事务A修改了一条数据但未提交，事务B却读取了这个未提交的数据，导致事务B基于不准确的数据做出了错误的决策。
不可重复读（Non-repeatable Read）：一个事务在多次读取同一数据时，得到了不同的结果。假设事务A读取了一条数据，事务B修改或删除了该数据并提交，然后事务A再次读取同一数据，发现与之前的读取结果不一致，造成数据的不一致性。
幻读（Phantom Read）：一个事务在多次查询同一范围的数据时，得到了不同数量的结果。假设事务A根据某个条件查询了一组数据，事务B插入了符合该条件的新数据并提交，然后事务A再次查询同一条件下的数据，发现结果集发生了变化，产生了幻觉般的新增数据。
丢失修改（Lost Update）：两个或多个事务同时修改同一数据，并且最终只有一个事务的修改被保留，其他事务的修改被覆盖或丢失。这种情况可能会导致数据的部分更新丢失，造成数据的不一致性。

不可重复读和幻读区别：

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

CHAR和VARCHAR的区别可以总结如下：

综上所述，CHAR适合存储长度固定且固定大小的数据，而VARCHAR适用于长度可变的数据。

在SQL查询中，COUNT(1)、COUNT(*)和COUNT(列名)是用于计算行数的聚合函数，但它们在实际应用时有一些区别。

COUNT(1)：这种写法中，1表示一个常量值，它会被用于计算查询结果集的行数。由于1是一个常量，在执行COUNT(1)时，数据库不会去访问或读取任何实际的数据，仅仅是对满足条件的行进行计数，因此执行速度相对较快。
COUNT(*)：这种写法中，*表示选取所有列，它会对满足条件的行进行计数。与COUNT(1)不同的是，执行COUNT(*)时，数据库需要读取每一行的数据，然后进行计数操作，因此它可能会比COUNT(1)稍微慢一些。不过，在大多数数据库中，优化器会对COUNT(*)进行特殊处理，可以通过索引等方式进行优化，使得性能相对较好。
COUNT(列名)：这种写法中，列名表示具体的表列，它会对非空（NULL）值的行进行计数。相比于COUNT(1)和COUNT(*)，COUNT(列名)会跳过值为NULL的行，只统计非空值的行数。这在某些特定的情况下可能更符合实际需求，例如统计某个列的非空值个数。

总体来说，COUNT(1)和COUNT(*)的性能较好且结果一致，而COUNT(列名)则对非空值进行计数。在实际使用时，可以根据具体的查询需求和性能要求选择适当的写法。