两种存储引擎的区别

1. InnoDB 存储引擎

InnoDB 是 MySQL 默认的存储引擎，它支持事务处理（ACID）、行级锁定以及外键约束等功能，非常适合处理具有高并发和事务需求的应用场景。

主要特点：

  • 事务支持：InnoDB 支持 ACID 事务，可以确保数据的一致性和完整性。

  • 行级锁定：InnoDB 使用行级锁定，这意味着在执行更新操作时，只有被修改的行会被锁定，而其他行仍然可以被访问。这大大提高了并发性能。

  • 外键支持：InnoDB 支持外键约束，可以确保表之间的参照完整性。

  • 支持 MVCC (多版本并发控制)：InnoDB 使用 MVCC 来支持读取已提交（Read Committed）和可重复读（Repeatable Read）的隔离级别，这有助于提高并发性。

  • 支持在线索引创建：可以在不锁定表的情况下创建索引。

  • 支持表空间：InnoDB 允许将数据存储在不同的表空间中，方便管理和备份。

2. MyISAM 存储引擎

MyISAM 是 MySQL 较早版本默认的存储引擎，虽然它不再作为默认引擎，但在某些场景下仍然有用武之地，尤其是当应用程序不需要事务支持且主要进行读操作时。

主要特点：

  • 不支持事务：MyISAM 不支持事务处理，因此在遇到故障时恢复数据较为困难。

  • 表级锁定：MyISAM 使用表级锁定，这意味着在整个表被锁定期间，其他对表的操作（包括读操作）都会被阻塞，这限制了并发性能。

  • 高速存储和检索：由于 MyISAM 不需要支持事务，因此它的存储和检索速度通常比 InnoDB 快。

  • 全文索引支持：MyISAM 支持全文索引，这对于搜索引擎和内容管理系统等需要全文搜索的应用非常有用。

  • 压缩表：MyISAM 支持压缩表来节省磁盘空间。

总结

  • InnoDB 适用于需要事务支持和高并发的应用场景，如在线交易处理（OLTP）系统。

  • MyISAM 适用于不需要事务支持且以读操作为主的场景，如数据分析和报表生成。

数据库的三范式是什么？

数据库的三范式是为了消除数据冗余和提高数据完整性而定义的规范化规则。这三范式分别是：

  1. 第一范式 (1NF): 列不可再分。

  2. 第二范式 (2NF): 表有主键。

  3. 第三范式 (3NF): 所有非主键列直接依赖于主键。

说一下 ACID 是什么？

ACID 是数据库事务处理的四个基本属性，分别代表：

  • Atomicity (原子性): 事务中的所有操作要么全部成功，要么全部失败回滚。

  • Consistency (一致性): 事务执行前后，数据保持一致状态。

  • Isolation (隔离性): 事务之间互相隔离，一个事务的执行不会影响其他事务。

  • Durability (持久性): 一旦事务提交，其对数据库的更改就是永久性的。

char 和 varchar 的区别是什么？

  • CHAR: 固定长度的字符串类型，如果插入的字符串长度小于定义的长度，MySQL 会在字符串尾部填充空格。

  • VARCHAR: 可变长度的字符串类型，根据实际插入的字符串长度占用空间，不会填充空格。

MySQL 的内连接、左连接、右连接有什么区别？

  • 内连接 (INNER JOIN): 返回两个表中匹配的行。

  • 左连接 (LEFT JOIN): 返回左表的所有行，即使右表中没有匹配的行也返回，对于右表中没有匹配的行，右表的列将为 NULL。

  • 右连接 (RIGHT JOIN): 返回右表的所有行，即使左表中没有匹配的行也返回，对于左表中没有匹配的行，左表的列将为 NULL。

MySQL 索引是怎么实现的？

MySQL 中索引的实现通常使用 B+ 树。B+ 树的特点是所有叶子节点都包含键值和指向数据行的指针，而且所有的叶子节点都通过指针连接在一起。这意味着在 B+ 树中搜索一个键值只需要从根节点遍历到叶子节点即可，效率非常高。

怎么验证 mysql 的索引是否满足需求？

可以使用 EXPLAIN 命令来分析查询计划，查看索引是否被正确使用。例如：

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

此外，还可以查看索引统计信息，如使用 SHOW INDEXES FROM table_name; 来查看表上所有索引的信息。

索引失效具体细节

在 MySQL 中，索引是用于加速数据检索的重要机制。然而，在某些情况下，MySQL 优化器可能会决定不使用现有的索引，这种情况被称为“索引失效”。索引失效可能导致查询性能下降，因为没有使用索引时，MySQL 可能需要扫描整个表来查找所需的数据。了解索引失效的原因可以帮助你优化查询和数据库结构，提高性能。

索引失效的常见原因：

  1. 使用或不使用 LIKE 通配符：

    • 当 LIKE 语句的通配符位于开头时（如 %value% 或 %value），MySQL 无法有效地使用索引，因为它需要从索引的末尾开始搜索。

    • 如果通配符位于字符串的末尾（如 value%），则可以使用索引。

  2. 函数调用：

    • 对列应用函数会导致索引失效，因为索引是基于原始列值建立的。例如，SELECT * FROM table WHERE UPPER(column) = 'VALUE' 会绕过索引。

    • 例外情况：如果在创建索引时使用了相同的功能，则仍然可以使用索引。例如，如果索引是基于 UPPER(column) 创建的，则上述查询可以使用索引。

  3. 隐式类型转换：

    • 如果查询中列的类型与比较值的类型不匹配，可能会导致隐式类型转换，从而导致索引失效。例如，SELECT * FROM table WHERE column = 1 如果 column 是字符串类型，则可能导致索引失效。

  4. 常量条件：

    • 如果查询中包含的条件总是返回常量结果，MySQL 可能会选择不使用索引。例如，SELECT * FROM table WHERE 1=1 AND column = 'value'，这里 1=1 是一个常量条件，虽然不影响查询结果，但可能会影响优化器的选择。

  5. 范围条件：

    • 当查询使用了范围条件（如 BETWEEN, <, <=, >, >=），索引仍然可以使用，但是索引的选择性会受到影响。

    • 如果复合索引的前导列使用了范围条件，那么后面的列将不会被使用。

  6. OR 逻辑：

    • 如果查询中使用了 OR 逻辑，且涉及不同的列，那么可能会导致索引失效，除非这些列都被包含在同一个索引中。

    • 例如，SELECT * FROM table WHERE col1 = 1 OR col2 = 2，如果 col1 和 col2 分别有不同的索引，那么这个查询将不会使用索引。

  7. 非等值条件：

    • 如果查询中包含了非等值条件（如 <> 或 !=），那么索引可能不会被使用，因为这些条件不能确定一个具体的索引项。

  8. 全表扫描：

    • 如果表较小，或者索引选择性差（即索引覆盖的数据范围过大），MySQL 可能会选择全表扫描而不是使用索引。

    • MySQL 优化器会根据成本模型来判断是否使用索引。

  9. 不合适的索引类型：

    • 使用不合适类型的索引也可能导致索引失效。例如，对于大量唯一值的列使用哈希索引可能不如 B-Tree 索引有效。

如何检查索引使用情况：

  • 使用 EXPLAIN 关键字来查看查询计划，了解 MySQL 是否使用了索引以及如何使用。

  • 查看 EXPLAIN 输出中的 key 和 type 列，了解哪些索引被使用以及访问类型。

  • 使用慢查询日志来捕捉那些执行时间较长的查询，并分析它们。

说一下数据库的事务隔离？

MySQL 支持以下四种事务隔离级别：

  • READ UNCOMMITTED (读未提交): 事务可以读取未提交的数据。

  • READ COMMITTED (读已提交): 事务只能读取已经提交的数据。

  • REPEATABLE READ (可重复读): 在同一个事务中多次读取相同的记录总是返回第一次读取的结果。

  • SERIALIZABLE (序列化): 最严格的隔离级别，确保事务序列化执行，避免所有并发问题。

说一下 mysql 的行锁和表锁？

  • 行锁 (Row Lock): InnoDB 存储引擎支持行锁，只锁定涉及的行，允许多个事务并发执行。

  • 表锁 (Table Lock): MyISAM 存储引擎支持表锁，锁定整个表，通常导致较高的并发冲突。

说一下乐观锁和悲观锁？

  • 乐观锁 (Optimistic Locking): 假设并发冲突较少，每次更新数据时都会检查数据版本，如果版本不一致，则更新失败。

  • 悲观锁 (Pessimistic Locking): 假设并发冲突较多，获取数据时就会锁定数据，直到事务完成才释放锁。

MySQL 问题排查都有哪些手段？

  • 慢查询日志 (Slow Query Log): 记录执行时间较长的查询。

  • 错误日志 (Error Log): 记录 MySQL 运行时的错误信息。

  • 二进制日志 (Binary Log): 记录数据库的所有变更。

  • 性能模式 (Performance Schema): 提供详细的运行时性能信息。

  • 使用 EXPLAIN 分析查询计划。

  • 查看系统变量和配置。

如何做 MySQL 的性能优化？

  • 索引优化：合理建立索引，避免不必要的全表扫描。

  • 查询优化：简化查询，避免使用 SELECT *，减少联接操作。

  • 配置调整：调整 MySQL 配置参数，如内存分配、缓冲池大小等。

  • 硬件升级：增加内存、使用 SSD 等提高硬件性能。

  • 分区表：对大型表进行分区，减少查询范围。

  • 读写分离：使用主从复制，将读操作分担到从库上。

  • 使用缓存：使用 Redis 或 Memcached 缓存热点数据。

  • 优化存储引擎：选择合适的存储引擎，如 InnoDB 提供更好的事务支持。

数据库外键

数据库中的外键（Foreign Key）是一种重要的约束机制，用于维护两个表之间的关系，确保数据的一致性和完整性。以下是外键的主要作用：

  1. 保证数据一致性:

    • 外键确保引用的完整性，即如果一个表（子表）中的列值引用了另一个表（父表）中的列值，则该列值必须存在于父表中。

    • 这样可以防止子表中出现不存在于父表中的外键值，从而保持数据的一致性。

  2. 维护数据完整性:

    • 当尝试插入、更新或删除数据时，外键约束可以阻止违反引用完整性的操作。

    • 例如，如果一个外键约束存在，那么不能删除父表中的行，除非先删除所有引用该行的子表中的相关行。

  3. 级联操作:

    • 外键可以定义级联行为，在删除或更新父表中的行时自动执行相应的操作。

    • 比如，可以设置外键约束使得当删除父表中的行时，子表中对应的行也会被自动删除（级联删除）；或者当更新父表中的主键值时，子表中对应的外键值也会被更新（级联更新）。

  4. 导航关系:

    • 外键提供了一种明确的方式去追踪表间的关系，可以通过外键轻松地从一个表导航到另一个表。

    • 这对于构建复杂的查询非常有用，例如连接查询或多表关联。

  5. 数据安全性:

    • 通过强制实施外键约束，可以减少由于不一致的数据引用而产生的潜在问题，从而提高数据的安全性和可靠性。

  6. 约束验证:

    • 在插入或更新包含外键的记录时，数据库管理系统会自动检查这些值是否符合约束条件。

    • 如果不符合，操作将被拒绝，从而防止了无效数据的插入或更新。

数据库外键的好处

使用外键有很多好处，特别是在维护数据库的完整性和一致性方面。以下是使用外键的一些主要优点：

  1. 数据完整性:

    • 参照完整性: 外键确保了子表中的外键字段值要么与父表中的主键值匹配，要么为空（如果允许NULL值）。这有助于保持数据库中数据的一致性，避免悬挂指针或孤儿记录的情况发生。

    • 域完整性: 通过限制子表中的值只能取自父表的有效值集合，外键约束可以防止无效值的插入。

  2. 数据一致性:

    • 外键强制执行表间关系的规则，确保在任何时候数据都保持一致，不会出现逻辑上的错误。

  3. 级联操作:

    • 级联删除: 当删除父表中的行时，可以选择级联删除所有子表中依赖于该行的记录。

    • 级联更新: 当更新父表中的主键值时，可以选择级联更新所有子表中对应的外键值。

  4. 简化查询:

    • 使用外键可以更容易地创建连接查询，因为外键为连接提供了明确的路径。

    • 这有助于简化SQL查询的编写，减少查询的复杂度。

  5. 简化应用程序逻辑:

    • 应用程序不需要手动检查外键约束，因为这些是由数据库管理系统自动处理的。

    • 这减少了应用程序中需要编写的代码量，降低了出错的可能性。

  6. 增强数据安全性:

    • 外键约束可以防止意外的数据修改或删除，因为任何违反外键约束的操作都会被数据库系统阻止。

    • 这有助于保护数据免受意外破坏。

  7. 方便的数据导航:

    • 通过外键，可以轻松地从一个表导航到另一个表，这对于构建多表关联查询特别有用。

  8. 简化数据库设计:

    • 外键约束提供了一个清晰的方式来表示实体之间的关系，有助于简化数据库的设计过程。