mysql

1.Innodb为什么使用自增id作为主键？

自增 ID 作为主键的优点：

InnoDB 的数据是以 B+树 结构存储的，数据被组织成一个个页

  • 不需要频繁调整现有数据的位置：每次插入新记录时，记录会按照 顺序 添加到当前索引节点的后续位置。
  • 减少页分裂和碎片化的问题：
    • 使用自增id作主键：当一页写满时，新的记录会自动开辟一个新的页来存储，而不会影响现有的页结构。
    • 非自增主键： 插入的主键是随机的，插入点可能会落在现有的任意页中，从而可能导致任意的页发生分裂，也容易出现碎片化的问题。

2.MyISAM和InnoDB两种存储引擎在实现 B+ 树索引方式上的区别

MyISAM：

  • MyISAM使用非聚簇索引，索引文件和数据文件是分离的。B+ 树的叶节点存放的是指向实际数据记录的地址，而不是数据本身。

  • 检索过程：

    • 通过 B+ 树搜索算法找到指定 key 的位置。如果该 key 存在，则取出其 data 域中的值，这个值是指向实际数据记录的地址。
    • 根据这个地址去读取相应的数据记录。

InnoDB：

  • InnoDB使用聚簇索引，索引文件和数据文件是一起存储的。

注：在MyISAM存储引擎中，主键索引树和二级索引树没有区别，存储的都是数据的地址（不涉及回表）。

3.非聚簇索引和聚簇索引的优缺点

非聚簇索引：

  • 优点：
    • 一个表中可以建立多个非聚簇索引，且数据的物理存储顺序不受索引的影响，灵活性更好
  • 缺点：
    • 增加了一次额外的磁盘I/O (使用从索引中获得的地址来访问实际的数据页）

聚簇索引：

  • 优点：

    • 减少了磁盘I/O操作次数，因为一次索引查找即可定位到所需的数据行。
    • 范围查询非常高效，数据行在物理上相邻存放。

  • 缺点：

    • 存在回表的问题。

4.Drop、Delete与Truncate的区别

  • Drop: 直接删掉表，操作不能回滚
  • Delete: 删除表中数据，可以加where字句，可以通过rollback来撤销删除
  • Truncate: 删除表中的所有数据，再插入数据时，自增长id从1开始计数

5.数据库隔离级别

  • 未提交读：存在脏读
  • 提交读： 不存在脏读，存在不可重复读
  • 可重复读： 不存在不可重复读，存在部分幻读
  • 串行化： 不存在幻读

6.为什么数据库索引通常使用B+ 树？

  • 磁盘I/O次数少：B+树的非叶子节点只存储关键字，节点中能存储的关键字个数就更多，树的高度就会更小，使用的磁盘I/O次数就会更少。
  • 查询时间更稳定：B树每个节点既存储关键字也存储数据，因此离根节点越近的数据查询就越来。而B+树所有数据都存储在叶子节点上，所以找对应数据的时间比较平均。
  • 更适合范围查询：B+树的所有叶子节点连成了有序链表，适合整表查询或范围查询。

注：B+树的范围查询（如 WHERE id BETWEEN 10 AND 100）：

  • 只需找到起始键（10），然后沿着链表遍历即可
  • 时间复杂度 = O(log N)（查找） + O(M)（遍历）

7.乐观锁和悲观锁的原理和应用场景？

悲观锁：

  • 定义：操作数据之前先加锁，确保其他事务无法修改数据。
  • 实现方式： 使用 SELECT ... FOR UPDATE 语句来实现。这条语句会在查询数据时对涉及的数据行加上行级锁，阻止其他事务对该数据进行修改。

  • 应用场景

    • 高并发写操作：当多个事务频繁更新同一份数据时，适合使用悲观锁，可以避免冲突和数据不一致。
    • 敏感数据操作：例如银行账户余额、库存管理等场景，数据一致性要求非常高。
    • 短事务：由于悲观锁会阻塞其他事务的访问，因此适合事务处理时间较短的场景。

  • 优缺点

    • 优点：保证强一致性，避免并发冲突。
    • 缺点：性能较低，容易导致死锁或事务阻塞。

乐观锁：

  • 定义： 在操作数据时不加锁，仅在最后更新数据时检查数据是否被其他事务修改过。

  • 实现方式：

    • 版本号机制：在表中增加一个 version 字段，每次更新数据时都会检查版本号是否匹配。如果版本号不匹配，则说明数据已经被其他事务修改。
    • 时间戳机制：类似版本号机制，但使用时间戳字段记录最后一次更新的时间。
    • CAS机制：通过比较旧值和新值来判断是否需要更新。

  • 应用场景

    • 低并发写操作：当数据更新频率较低时，适合使用乐观锁，因为它不会阻塞其他事务。
    • 读多写少的场景：例如商品详情页展示、用户信息查询等场景，数据以读为主，写操作较少。

  • 优缺点

    • 优点：性能较高，不会阻塞其他事务。
    • 缺点：在高并发写操作场景下，可能导致大量更新失败

8.数据库引擎InnoDB与MyISAM的区别

InnoDB：

  • 事务支持：InnoDB是MySQL默认的事务型存储引擎，支持ACID属性（原子性、一致性、隔离性、持久性），适用于需要高数据一致性的应用。它实现了四个标准的隔离级别，默认为可重复读(REPEATABLE READ)。
  • 并发控制：在可重复读隔离级别下，通过多版本并发控制(MVCC)来防止部分幻读问题，从而提升并发处理能力。
  • 索引结构：主索引是聚簇索引，意味着数据直接存储在索引节点
  • 支持行级锁

MyISAM：

  • MyISAM不支持事务，也不支持行级锁，只能对整张表加锁
  • 索引是非聚簇索引

9.介绍一下InnoDB的自适应哈希的作用

加速查询：自适应哈希索引的主要目的是加快对数据页的访问速度。当查询可以通过哈希索引直接定位到所需的数据行时，可以大大减少搜索时间，尤其对于频繁访问的数据页。

10.关系型数据库的范式的作用和局限性

数据库范式的好处：

  • 减少数据冗余：这是应用数据库范式最主要的目的。通过消除重复的数据存储，可以节省存储空间，并且减少了因多处修改/删除同一数据而引发的不一致性问题。

数据库范式的局限性：

  • 表连接使得SQL效率降低： 高度规范化的数据库往往需要更多的表来存储数据，增加了表连接的复杂度，从而会导致SQL效率的降低。

11.介绍一下常见的范式

  • 第一范式（1NF）：

    • 每个列保持原子特性，即表中的每一列都应该是单一值而不是集合或者列表。
    • 不满足第一范式不能称作关系型数据库

  • 第二范式（2NF）：

    • 非主键字段必须完全依赖于整个主键，而不是主键的一部分。
    • 消除了部分依赖关系。

  • 第三范式（3NF）：

    • 所有列都直接依赖于主键，不存在传递依赖。非主键字段间不能存在依赖关系。
    • 消除了传递依赖关系。

  • BCNF范式 ：

    • 每个表中只有一个候选键（在一个数据库中每行的值都不相同，则可称为候选键）

12.索引的优点和缺点？

优点：提高查询效率

缺点：索引并非越多越好，过多的索引会增加CPU使用率，并且由于数据更新时需要同步更新索引，这会导致额外的磁盘I/O操作，从而加重CPU负荷

13.MySql中有哪些日志文件？各有什么作用？

  • 错误日志
  • 查询日志： 客户端连接以及执行的SQL语句
  • 慢查询日志： 执行时间超过指定阈值（由long_query_time参数定义）的查询
  • 二进制日志: 包含了对数据库进行更改的所有操作(用于主从复制或数据恢复)
  • Redo Log：（对缓冲池中的数据进行任何修改之前，这些修改首先会被记录到Redo Log中）用于崩溃恢复，当数据库意外关闭时，它能确保已提交但未写入磁盘的数据能够被正确地恢复。
  • Undo Log: 支持事务回滚