MySQL 面试题

2023-12-13 12:02:00 浏览数 (1)

1. 数据库三大范式是什么

数据库的三大范式是关系型数据库设计的三个基本原则：

  1. 第一范式（1NF）： 数据表中的所有字段项都是原子性的，不能再分解。即表中的每列都是不可再分的基本数据项，每个字段只包含单一数据值，保证了记录中各个字段的独立性。
  2. 第二范式（2NF）： 在第一范式的基础上，消除了非主属性对于候选键的部分依赖，也就是说一个表里面只描述一件事情。表中的每个实例或者记录必须可以被唯一地区分，通常要求表具有主键，并且非主键字段必须完全依赖于主键，不依赖于主键的一部分（对于组合主键而言）。
  3. 第三范式（3NF）： 在第二范式的基础上，消除了非主属性对于候选键的传递依赖。即除主键外的其他属性不依赖于其他非主要关键字。其宗旨是每列都与主键有直接关系，不存在间接关系。

这些范式的设计目的是为了减少数据冗余、提高数据完整性，并简化数据结构，从而使数据库更加稳定和高效。遵守这些范式可以让数据库设计得到结构化，但也应当注意，在某些情况下，为了提高查询效率，开发者会有意识地违反这些范式来进行数据库的反规范化设计。

2. MySQL 有关权限的表都有哪几个？

在 MySQL 中，权限相关的信息存储在 MySQL 的系统数据库 mysql 的几个表中。这些表统称为授权表（Grant Tables）。截至知识截断日期前，主要的权限表包括：

  1. user：存储用户账号信息及其全局权限。每一行代表一个用户的权限记录。
  2. db：存储数据库级别的权限。具体到某个数据库的权限设置。
  3. host：存储与主机相关的权限信息，例如主机地址与数据库级别的权限。
  4. tables_priv：存储表级别的权限。控制用户对于个别表的权限。
  5. columns_priv：存储列级别的权限。控制对于表中某个列的权限。
  6. procs_priv：存储过程级别的权限。控制用户对于存储过程的权限。
  7. proxies_priv：存储代理权限，控制用户代理执行权限。

这些表定义了 MySQL 数据库服务中各级别（全局，数据库，表和列等）的权限。在管理 MySQL 权限时，常常会使用命令行工具或者图形化界面工具对这些表进行查询或修改，而不直接操作这些表。这是因为 MySQL 提供了如GRANT和REVOKE等 SQL 语句来简化权限管理，当这些语句执行时，它们会自动更新相关的授权表。

当修改了授权表后，需要执行FLUSH PRIVILEGES;命令来立即使更改生效，或者重新启动 MySQL 服务器。这一步骤是必须的，因为 MySQL 服务器在启动时加载这些权限表的内容到内存中。直接更改表内容不会被立即识别，除非执行了上述的命令。

3. MySQL 的 Binlog 由几种录入格式?有什么区别？

MySQL 的 Binary Log(Binlog) 是 MySQL 数据库用于复制和数据恢复的一个重要组件。Binlog 记录了导致数据库更改的所有语句（如 INSERT、UPDATE、DELETE、CREATE 等）以及语句执行的时间。该日志对于复制过程非常必要，因为它允许从服务器执行主服务器上已经执行的相同更改。同时，Binlog 也可以用于数据恢复和增量备份。

MySQL 的 Binlog 共有三种格式：

  1. Statement-based replication(SBR)：这是最传统的形式，记录的是操作语句本身。例如，INSERT INTO mytable VALUES (1,2,3)会被原语记录到 binlog 中。
  2. Row-based replication(RBR)：如名字所示，这种格式记录的是数据更改的结果，确切记录了哪些行被改动了以及改动后的新值。比如说，它不记录 INSERT语句，而是会记录这个语句的效果，即哪一行被插入了哪些数据。
  3. Mixed-based replication(MBR)：顾名思义，Mixed 形式融合了 SRB 和 RBR，会根据不同的操作自动选择是记录语句还是记录行的变化。一般情况下，它会默认使用 Statement-based logging，但是在触发器、存储过程或者函数等无法精确传到变化的场景下会使用 Row-based logging。

这三种格式各有优劣：

  • Statement-based replication 的好处在于 binlog 会较小，因为只记录了执行了 SQL 语句。但是在某些特定情况下可能会产生问题，比如执行带有非确定性的语句(如now()，RAND())时，从服务器可能和主服务器的执行结果不一样。
  • Row-based replication 解决了 SBR 的非确定性问题，因为它记录了具体的行变化。但是，行变更记录可能会让 binlog 变得很大。
  • Mixed-based replication 试图结合两者的优势，但可能在某些极端复杂的情况下仍然要手动调整复制策略。

在实际应用中，选择哪种 binlog 格式通常取决于具体的应用场景以及主从复制的要求。开发者可以根据需求来设置最合适的 binlog 格式。在 MySQL 的配置文件中设置 binlog_format参数来改变这个设置。

4. MySQL 存储引擎 MyISAM 与 InnoDB 区别

MySQL 提供了多种存储引擎，其中 MyISAM 和 InnoDB 是最常见的两种。它们在设计、功能、性能等方面存在一些显著差异。

以下是 MyISAM 和 InnoDB 的一些注意区别：

  1. 事务支持：
  • InnoDB支持事务处理，具备提交（COMMIT）和回滚（ROLLBACK）的能力，以及事务的 ACID 属性，适用于需要高度数据完整性的应用场景。
  • MyISAM 不支持事务处理，更适用于只读或添加新数据的应用场景。
  1. 行锁和表锁：
  • InnoDB 支持行级锁定（row-level locking）和外键。行级锁可以大大减少数据库操作的冲突，提高并发访问性能。
  • MyISAM 只支持表级锁定（table-level locking），当对表执行写操作（INSERT、UPDATE，DELETE）时，会锁定整个表。
  1. 外键约束：
  • InnoDB 支持外键约束，这对于维护数据的完整性非常重要。
  • MyISAM 不支持外键，因此维护引用完整性需要依靠应用层面来实现。
  1. 索引和缓存：
  • InnoDB 将逐渐索引数据和相关行数据在一起存储，易于主键查找，并拥有自适应哈希索引，数据和索引都可以缓存在内存中。
  • MyISAM 将数据文件和索引文件分开，对于全表扫描操作更为高效。只有索引被缓存到内存中，数据是直接从磁盘读取。
  1. MVCC：
  • InnoDB 支持多版本并发控制（MVCC），这意味着读操作可以不加锁，并发性能更好，对于高并发读写的应用优势明显。
  • MyISAM 没有 MVCC 的功能，写入时会锁定表，可能会阻塞读取操作。
  1. 崩溃与恢复：
  • InnoDB 更适合崩溃后的恢复，因为它支持事务日志，提供更好的崩溃恢复能力。
  • MyISAM 在崩溃后重新修复表的速度可能比较满，因为缺乏事务日志。
  1. 全文索引：
  • InnoDB 从 MySQL 5.6 开始支持全文索引。
  • MyISAM 较早地支持全文索引，对于全文检索的支持较为成熟。

根据这些区别，InnoDB 被推荐用于大多数数据库应用环境，尤其是需要高并发和事务支持的应用。而 MyISAM 可能适用于一些特定的、对事务完整性要求不高、以读操作或批量插入为主的场合。

随着 MySQL 的发展，InnoDB 已经成为了默认的存储引擎，MyISAM 逐渐被淘汰。在新的开发工作中，建议优先选择 InnoDB。

5. MyISAM 索引和 InnoDB 索引的区别？

MyISAM 和 InnoDB 这两种 MySQL 存储引擎在索引的使用和实现上由一些关键的区别：

  1. 索引结构：
  • InnoDB 使用的是一种叫做聚簇索引的结构，它将数据存储和索引放在一起，准确地说，InnoDB 的表结构文件本身就是按照主键顺序组织存储的 B 树索引。因此，InnoDB 的表必有主键（如果没有显式指定主键，InnoDB 会选择一个唯一的非空列作为主键；如果这样的列也没有，InnoDB 内部会生成一个隐藏的行 ID 作为主键）。
  • MyISAM 使用的是非聚簇索引，数据文件和索引文件分开。索引文件仅包含键值和执行数据文件中行记录的指针。在 MyISAM 中，MYI 文件存放索引，MYD 文件存放数据。
  1. 主键查找：
  • 在 InnoDB 中，由于行数据直接绑定在主键索引上，这意味着主键查找可以非常快速。
  • MyISAM 进行主键查找时需要两次查找，首先查找索引文件，然后通过索引中的指针找到数据文件中的具体位置。
  1. 辅助索引/二级索引：
  • InnoDB 的辅助索引（非主键索引）存储的是相应记录的主键值，而不是记录的物理位置。这意味着当执行二级索引查询时，InnoDB 需要两次索引查找：先找到主键值，在通过主键值查找实际的行数据。
  • MyISAM 的二级索引直接指向行的物理位置，所以查找的时候只需一次索引即可定位数据。
  1. 全文索引：
  • MyISAM 支持全文索引较早，并且功能较为成熟。
  • InnoDB 从 MySQL 5.6 版本开始支持全文索引。
  1. 表空间：
  • InnoDB 的索引和数据存储在同一个表空间文件中（.ibd 文件），可以有多个表空间。
  • MyISAM 的索引和数据分别存储在不同的文件（.MYI 和.MYD 文件）。
  1. 索引缓冲：
  • InnoDB 为索引和数据使用单一的缓冲池（buffer bool），即数据和文件都存储在内存中，以提高 CRUD 操作的性能。
  • MyISAM 仅将索引缓冲到内存中，而数据缓冲依赖于操作系统的文件缓存。
  1. 锁定机制：
  • InnoDB 支持更先进的行级锁定，这对于索引的并发控制来说是一大优势。
  • MyISAM 只有表级锁定，当进行写操作时，会锁定整张表，这在高并发的环境下会成为性能瓶颈。

随着 InnoDB 功能的不断增强和稳定性的提高，它已经成为 MySQL 的默认存储引擎，并且通常建议使用 InnoDB 而不是 MyISAM，特别是在需要事务处理和高并发的应用中。

6. 什么是索引？

在数据库系统中，索引是一种优化技术，它可以快速定位和访问数据库表中的特定数据，而无需扫描整个表。索引类似于图书中目录或图书馆的查找系统，它使得数据库能够快速找到信息，而不需要逐行查阅表中的所有数据。

以下是关于索引的一些关键点：

  1. 数据结构：索引通常使用高效的数据结构，如 B-树、B 树、哈希表等，这些数据结构支持快速查找、插入和删除删除。
  2. 效率提升：索引的目的是提高查询速度、从而改善数据库的性能。对于大量数据的表来说，使用索引非常关键。
  3. 成本和权衡：尽管索引可以提高查询速度，但它们也有成本。索引需要占用额外的磁盘空间，且在插入、删除和更新记录时需要维护，这可能会降低写操作的速度。
  4. 类型：有多种类型的索引，包括主键索引，唯一索引，复合索引等，它们根据如何影响表中数据的唯一性和结构而有所不同。
  5. 选择性：索引的选择是衡量索引效率的重要因素之一，指的是不重复的索引条目与表中的数据总行数的比率。高选择性的索引对查询性能提升有着显著影响。
  6. 聚簇和非聚簇：聚簇索引将数据行和索引顺序相匹配，只能由一个，通常似乎主键索引；非聚簇索引则是独立于数据行的物理顺序。
  7. 查询优化：数据库查询优化器会使用索引来由优化查询计划，以减少数据访问的数量，并提高查询效率。

在使用索引时，应精心设计并根据查询模式做出明智的选择。索引不是越多越好，需要根据实际应用场景仔细规划索引策略。正确使用索引可以显著提升数据库操作的性能，尤其是对于大型数据库。

7. 索引由哪些优缺点？

数据库索引类似于图书的目录，用于加速数据库表中数据的检索速度。然而，索引的使用同时带来了优点和缺点，需要合理地权衡。

优点：

  1. 提高查询速度：索引可以显著加快数据检索速度，尤其是对于大量数据的表。
  2. 提升排序的速度：索引可以加快 ORDER BY 操作的处理速度，因为所有本身通常就是排序好的。
  3. 加速表连接：在进行表连接时，索引可以提高连接操作的速度。
  4. 有效使用选择查询：当索引具有高选择性时，它可以允许数据库引擎快速过路掉不符合条件的行。
  5. 改进数据的聚合速度：对于 GROUP BY 和 DISTINCT 查询，索引有助于提高处理速度。
  6. 唯一性和完整性的强制实施：通过建立唯一性索引，数据库可以确保列或列组合的唯一性。

缺点：

  1. 降低表的更新速度：索引需要在插入、更新或删除时进行维护，这增加了数据修改操作的开销。
  2. 占用更多存储空间：索引使用额外的磁盘空间存储索引结构本身。
  3. 索引管理：需要精心设计和定期维护以保持其性能，这包括索引重建和碎片整理。
  4. 可能导致优化器选择不当：在某些情况下，数据库优化器可能错误地选择了使用索引，导致性能不如全表扫描。
  5. 复杂性增加：需要对数据库已有的查询模式有深刻理解才能设计出优秀的索引策略。

在考虑使用索引时，应该分析数据库的使用模式，包括查询的频率和类型、表的大小和增长速率、行变更的频率等。索引应该在最厂查询的列上建立，同时避免在数据变化频繁或列中的数据重复度较高的列上创建索引。合理地创建索引可以在不同程度上改善数据库的性能，并且影响应用程序的响应时间。

8. 索引有哪几种类型？

数据库索引有多种不同类型，能够支持不同的数据结构和查询类型。这里是一些最常见的索引类型：

  1. 主键索引（Primary Key Index）：数据库表通常会有一个主键，而主键自动成为一个索引。主键索引用来确保数据的唯一性和快速检索。主键索引是一种特殊的唯一索引，不允许有 NULL 值。
  2. 唯一索引（Unique Index）：该索引不允许任何重复的值。如果一列或列组合被创建为唯一索引，数据库会在插入或更新记录时检查数据的唯一性。
  3. 复合索引（Composite Index）：当一个索引包含多个列时，称为复合索引。复合索引可以基于多个数据列创建，以支持多列的查询条件。
  4. 全文索引（Full-text Index）：专为全文索引设计的索引。MySQL 在 MyISAM 和 InnoDB 存储引擎中支持全文索引，它允许文本内容进行快速搜索。
  5. 聚簇索引（Clustered Index）：在聚簇索引中，表记录的物理顺序和键值的逻辑（索引）顺序相同。有时候表的聚簇索引和主键索引是一回事，因此有的系统每个表只能有一个聚簇索引。
  6. 非聚簇索引（Non-clustere Index）：非聚簇索引中，表的物理顺序和键值的逻辑顺序不同。非聚簇索引存储了物理位置的引用，并且一个表可以拥有多个非聚簇索引。
  7. 覆盖索引（Convering Index）：如果一个索引包含所有需要查询的字段的值，称为覆盖索引。查询可以仅通过索引完成，而无需回表查看实际的数据行。
  8. 分区索引（Patitioned Index）：当表通过分区划分时，索引可以在每个分区上分别创建，以提高大型表的管理和查询效率。
  9. 空间索引（Spatial Index）：主要用于地理数据存储，支持对空间信息的高效查询。它们在处理 GIS（地理信息系统）数据时非常有用。
  10. B 树索引（B-Tree Index）：在多数数据库系统中，默认采用 B-Tree 或其变种结构（如 B 树）来实现索引。适用于全键值、键值范围和键值排序的搜索。
  11. 哈希索引（Hash Index）：利用哈希表实现，适用于等值比较查询。哈希索引的搜索速度非常快，但不支持排序和部分匹配搜索。

不同类型的索引适用于不同类型的查询和数据模式，良好的索引设计要基于具体的数据访问需求和查询性能特性。正确使用索引是数据库性能调优的关键方面。

9. MySQL 有几种锁？

MySQL 数据库支持不同的锁机制，用以管理并发操作时数据的一致性和完整性。主要的类型有：

  1. 表锁（Table-level Locks）：MySQL 中最基本的锁策略，并且是开销最小的策略。对整个表加锁，其锁定粒度最大，会锁定整张表，典型的表锁实现包括 MyISAM 和 MEMORY 存储引擎使用的锁。
  2. 行锁（Row-level Locks）：锁定粒度最小，只针对数据中的单独一行进行加速操作。它允许多个线程访问同一表的不同行。典型的行锁实现包括 InnoDB 和 XtraDB 存储引擎使用的锁，以及 NDB Cluster 存储引擎。
  3. 页锁（Page-level Locks）：锁定粒度介于表锁和行锁，锁定相邻的一组行。Berkeley DB 存储引擎是使用页锁的一个例子，不过在 MySQL 中使用较少。

每种类型的锁在不同的应用场景尤其有缺点：

  • 表锁：优点是锁的管理开销小，锁定的速度也最快。缺点是冲突最多，对并发性的限制最大，尤其是在有大量写入操作的环境中。
  • 页锁：优点是提供了比表锁更好的并发读，尤其是在表的大部分区域不会被频繁更新的时候。缺点是锁的管理开销比表锁要大，死锁的概率也比行锁要高。
  • 行锁：优点是并发度最高，锁冲突的概率最小，并且支持完整的事务（SQL 标准的 ACID 特性）。缺点是管理这些锁需要更懂的内存和管理资源，并且如果不恰当的设计事务的话，可能导致死锁。

此外，MySQL 还实现了其他多种锁类型和机制，包括但不限于：

  • 意向锁（Intention Locks）：由 InnoDB 引擎使用，用于知识接下来对表中行的锁定类型（共享或排它）。
  • 自增锁（Auto-Inc Locks）：是 InnoDB 存储引擎中用于自增长列的特殊表锁。
  • 记录锁（Records Locks）：是针对索引记录采取的行锁。
  • 间隙锁（Gap Locks）：锁定一个范围，但不包括记录本身。
  • 临键锁（Next-Key Locks）：是记录锁和间隙锁的组合，锁定一段范围，但包括记录本身。

了解不同的锁类型以及它们在并发环境中的作用多数据库性能调优是至关重要的。这些锁类型帮助数据库维护在多用户访问的情况下数据的完整性和一致性。

10. MySQL 中 InnoDB 支持的事务隔离级别名称？以及逐级之间的区别？

InnoDB 存储引擎支持以下四种事务隔离级别，它们在不同的级别上平衡了性能和一致性：

  1. 读未提交（Read Uncommitted）：在这个隔离级别下，事务可以读取未被其他事务提交的数据变更，也就是所谓的”脏读“。这种级别的隔离性最低，因为它允许事务读取其他事务尚未提交的更改，可能导致不一致的查询结果。
  2. 读提交（Read Committed）：在这个隔离级别下，一个事务只能读取已被其他事务提交的数据变更。这意味着”脏读“是不可能发生的，但是”不可重复度“和”幻读“有可能出现。”不可重复读“是指在同一事务中，多次读取同一数据集合会有不同的结果。
  3. 可重复读（Repeatable Read）：这是 InnoDB 的默认隔离级别。在这个级别下，事务在整个过程中可以多次读取到相同的数据行的同一数据版本，即使其他事务试图对那些数据进行更新。这样，它避免了”不可重读读“，但仍然可能出现”幻读“，即在同一个事务内，两个相等的查询可能因为其他事务插入新行而返回不同行数的结果。
  4. 可串行化（Serializable）：这是最高的隔离级别，它通过强制事务串行执行，从而避免”脏读“，”不可重复度“和”幻读“。在可串行化隔离级别下，可以通过锁定被读取的数据来保证一致性，但这会极大地影响多用户并发操作的性能。

逐级区别：

  • 从读未提交到读提交：增加了对”脏读“的防止，即只能读取到已经提交的数据。
  • 从都提交到可重复读：防止了”不可重读读“，事务在执行过程中看到的数据是一致的，不会因为其他事务的提交而改变。
  • 从可重复读到可串行化：防止了”幻读“，即在同一个事务内，连续两次执行相同的查询，结果集应该完全一致。可串行化通过锁表的方式，避免了插入类的”幻读“问题，但牺牲了并发性能。

应用程序在选择事务隔离级别时需要权衡一致性需求和系统性能。更高的隔离级别可以提供更严格的数据完整性保证，但可能伴随着性能的下降，因为更高的隔离性往往需要更严格的锁策略。

11. char 和 varchar 的区别？

在 MySQL 中，char和 varchar类型都用于存储字符（字符串）数据，但它们在存储机制和使用场景上有以下主要区别：

  1. 存储方式：
  • char是一个固定长度的字段，它总是会占用定义时指定的长度（字节数）。对于char(5)，即使存储的数据少于 5 个字符，MySQL 也会使用空格来填充剩余的空间以保持字段长度不变。
  • varchar 是一个可变长度的字段。它之存储实际的字符，并需要额外的 1 字节或 2 字节来保存值得长度信息：如果列的最大长度在 255 字节以内，那么使用 1 字节表示长度；如果超过 255 字节，则使用 2 字节。
  1. 性能：
  • char类型的性能通常略高于varchatr，因为它的长度是固定的，数据库在处理查询时能够更快地定位每条记录。这对于经常进行固定长度数据的更新和查找更为适用。
  • varchar在含有可变长数据的场景下（如文本数据），由于节省空间，对性能的影响较小。
  1. 存储空间：
  • char可能会浪费存储空间，因为不管实际存储的数据长度如何，它总是使用固定长度的存储空间。
  • varchar则会根据实际需要的存储数据的长度来分配空间，更节省存储。
  1. 使用场景：
  • char更适合存储确切长度的数据，如哈希密码，性别字段，国家代码等。
  • varchar适用于长度变化较大的数据，如姓名，地址和描述等。
  1. 尾部空格处理：
  • 在char字段中，MySQL 会在检索时剥离尾部的空格。
  • 对于varchar字段，MySQL 会保留所有空格，包括尾部空格。
  1. 最大长度：
  • MySQL 中char的最大长度是 255 字符。
  • varchar的最大长度是 65536 字符，这主要取决于最大行大小和字符集。

正因为这些区别，开发者通常根据实际数据特性，考虑数据的重复率、固定长度等情况，来选择最合适的数据类型。

12. 主键和候选键有什么区别？

在数据库中，主键（Primary Key）和候选键（Candidate Key）都是用于唯一标识表的行，但它们有以下不同点：

  1. 定义：
  • 候选键：一个表中的一个或多个列，可以唯一标识表中的每一行。一个表可能有多个候选键。
  • 主键：从表的候选键中选出来的一个，用作表的唯一表示。表中只能有一个主键。
  1. 数量：
  • 一个表中可以有多个候选键，但只能有一个主键。
  1. 空值（NULL）和重复值：
  • 候选键不允许重复值，每个候选键都必须是唯一的，但它们可以有空值，虽然这在实践上并不推荐。
  • 主键不允许重复值和空值。
  1. 作用：
  • 候选键的作用是可以称为表的主键。
  • 主键的作用是提供一种快速和可靠的方法来识别行，并在表之间建立关系（如外键）。
  1. 索引：
  • 主键自动称为一个索引，用以提高查询性能。
  • 候选键也可以被索引，但并不是自动的过程，需要手动设置。
  1. 外键关系：
  • 只有主键才能被其他表用作外键建立引用约束。
  • 候选键除非被选作主键，否则不会用于建立外键关系。

简而言之，候选键是拥有唯一识别能力的键的总成，其中一个被选作主键用于唯一确定表中的每一行。如果表设计的时候识别多个候选键，表设计者会根据具体需求和约束来选择其中最为合适的一个作为主键。

13. 如何在 Unix 和 MySQL 时间戳之间进行转换？

在 Unix 系统和 MySQL 数据库之间转换时间戳通常涉及两种时间表示方法：

  1. Unix 时间戳：通常表示为自 1970 年 1 月 1 日（UTC）以来的秒数。
  2. MySQL 时间戳：通常以YYYY-MM-DD HH:MM:SS格式的字符串表示，精确到秒。

从 Unix 时间戳转换为 MySQL 时间戳：假设你有一个 Unix 时间戳，你可以在 MySQL 中是用 FROM_UNIXTIME()函数将其转换为 MySQL 格式的日期时间字符串。

代码语言：javascript复制
SELECT FROM_UNIXTIME(1617235200);
-- 输出：'2021-04-01 00:00:00'

从 MySQL 时间戳转换为 Unix 时间戳：相反，如果你有一个 MySQL 的时间日期字符串，你可以用UNIX_TIMESTAMP()函数将其转换为 Unix 时间戳：

代码语言：javascript复制
SELECT UNIX_TIMESTAMP('2021-04-01 00:00:00')
-- 输出：'1617235200'

注意，这些转换默认假设 MySQL 服务器设置的时区为 UTC。如果服务器的时区设置与 UTC 不同，可能需要额外的步骤来考虑时区差异。你可以使用 SET time_zone = ' 00:00';将当前会话的时区设置为 UTC 进行准确转换。

另外，在编写脚本或应用程序时，通常会有函数库可以处理时间戳和日期时间格式之间的转换，如在 Go 中的 time包，Python 中的datetime模块或在 PHP 中的date和strtotime函数。

14. MyISAM 表类型将在哪里存储，并且还提供其存储格式？

MyISAM 是 MySQL 的一个存储引擎，已经被 InnoDB 引擎在很多方面取代，但它在一些旧版本的 MySQL 中仍然存在。MyISAM 表类型的存储格式较为简单，主要包括三个文件，每个文件的名称以表名为前缀，以下是这些文件及其作用：

  1. .frm 文件：存储表的格式定义。无论是 MyISAM 表还是其他存储引擎的表，每个表都会有一个这样的文件。
  2. .MYD 文件（MYData）：存储表的数据。这是 MyISAM 的表数据文件。
  3. .MYI 文件（MYIndex）：存储表的索引。这个文件包含了所有建立的索引。

这些文件存储在 MySQL 数据目录下的特定于数据库的子目录中。例如，如果你有一个数据库名为my_database和一个 MyISAM 表名为my_table，那么文件将位于以下位置：

代码语言：javascript复制
/var/lib/mysql/my_database/my_table.frm
/var/lib/mysql/my_database/my_table.MYD
/var/lib/mysql/my_database/my_table.MYI

这里的/var/lib/mysql是 MySQL 的默认数据目录，不同的安装可能会有不同的位置。

由于 MyISAM 默认不支持事务处理、外键以及行锁，因此，它逐渐被具备这些特性的 InnoDB 所取代。但是，在只读操作和全文搜索方面，MyISAM 可能仍然看到一些使用，它通常比 InnoDB 要快，特别是在表锁可以容忍的应用场景中。然而，由于缺乏事务和崩溃后的自动恢复能力，MyISAM 引擎不建议用于需要高可靠性系统。

15. MySQL 里记录货币用什么字段类型好

在 MySQL 中记录货币金额的最佳字段类型是 DECIMAL或NUMERIC，这两者在 MySQL 中是同义词。DECIMAL数据类型用于存储固定精度和比例的数字，非常适合财务计算，因为它能够存储精确的小数点数值，它避免了浮点数或双精度类型由于计算精度问题可能导致的舍入错误。

DECIMAL数据类型的语法是：

代码语言：javascript复制
DECIMAL(M, D)

其中：

  • M 是数组的最大位数（精度）。它包括小数点两侧的数字。
  • D 是小数点后面的位数（小数点的位数，也称为比例）。

例如，如果你想存储最高为 999.99 元的货币值，则可以这样创建字段：

代码语言：javascript复制
money DECIMAL(5, 2)

这表示总共最多有 5 位数字，其中包括 2 位小数，所以 123.45 是有效的，1234.567 不是，因为它有 3 位小数。

对于更大的货币数值，你需要根据可能出现的最大数额以及允许的小数位数适当增加M值。

使用DECIMAL类型的考虑因素：

  • 根据需要适当选择精确度（M）和比例（D）的值，理论上合法精度可以非常大，但记住DECIMAL类型字段可以存储最多 65 位数字，其中包括 30 位小数。
  • 保持一致性，特别是在涉及货币计算和比较的应用程序中。
  • 注意，即使列的比例设置为 0，DECIMAL 列也能正确存储小数。

总体而言，DECIMAL 类型是用于财务数据和其他需要精确表示的场合，能够避免在计算中引入浮点误差，是处理货币值的首选字段类型。

16. 创建索引需要注意什么？

在数据库中创建索引可以显著提高查询效率，但如果不当使用则可能产生负面影响。在创建索引时，应该考虑以下几个要点：

  1. 选择合适的列：
  • 通常应该为经常用于 WHERE子句，JOIN操作中的条件或者时经常进行排序和分组操作的字段创建索引。
  • 唯一性：具有高唯一值的列（Cardinality 高）通常时创建索引的好候选。
  1. 避免过度索引：
  • 创建太多索引会降低写操作（INSERT、UPDATE、DELETE）的性能，因为每次写操作数据库都需要更新这些索引。
  • 每个新增的索引都会消耗更多的存储空间。
  1. 使用复合索引策略：
  • 如果经常在多个列上进行查询，考虑创建复合索引。复合索引是基于两个或多个列的索引。
  • 确保复合索引中的列的顺序反映了查询模式。
  1. 考虑索引的类型：
  • 根据你的需要选择合适的索引类型，比如 B-Tree（默认类型，适用于全值匹配，值范围或前缀搜索），Hash（适用于全值匹配），FULLTEXT（使用与全文搜索）等。
  1. 分析查询模式：
  • 仔细分析应用程序的查询模式和数据访问模式，为最常用的查询路径创建索引。
  1. 维护索引和表的统计信息：
  • 定期检查索引的有效性和选择性，并清理不再使用或重复的索引。
  • 利用数据库提供的工具和命令（如 MySQL 中的 ANALYZE TABLE），可以帮组合更新索引和表的统计信息。
  1. 考虑索引列的排序：
  • 在复合索引中，通常先按照等值条件列（非范围查询）、再按照范围查询列、最后按排序和分组列的顺序进行索引。
  1. 了解前缀索引和覆盖索引：
  • 对于文本类的长字符串，可以使用前缀索引来减少索引的大小和提高效率。
  • 覆盖索引是指索引包含所有需要的字段信息，查询只需要访问索引而不必读取数据行。
  1. 使用部分索引和过滤索引：
  • 如果一个列中只有一小部分数据常常被查询，可以创建部分索引来只索引这部分数据。
  1. 考虑业务需要和数据变化：
  • 随着业务的发展和数据的增加，适时更新和优化索引策略。

合理地创建和管理索引对于维持数据库性能至关重要。需要做的不仅仅是在初始设计时建立索引，还包括对数据库使用模式的持续观察和对索引进行相应的调整。

17. 使用索引查询一定能提高查询的性能吗？为什么？

使用索引并不总是能提高查询性能，原因取决于多种因素：

  1. 查询类型和频率：对于经常进行的点查询（检索单个行数据），索引通常会提高性能。对于经常变更数据的表，或者查询很少发生时，索引可能不会带来明显的性能提升。
  2. 数据量的大小：在小型数据集上，顺序扫描可能比使用索引更快。如果数据集很大，索引通常会大幅提高性能。
  3. 索引选择性：如果索引的选择性不好（即一个索引值对应的记录非常多），那么查询优化器可能决定不适用索引。这种情况下，全表扫描可能更高效。
  4. 索引覆盖：如果一个查询的列完全由一个索引覆盖，那么这个查询可能非常快。如果不是，可能需要额外的 I/O 操作来获取数据行，这可能会减慢查询速度。
  5. 数据分布和读取模式：如果数据时随机分布的，索引可能减少查询所需的 I/O 操作。但是，如果行数据分布不均匀或者顺序读取，则索引可能不会帮助提高性能。
  6. 索引和数据的物理存储：如果索引过大，而且经常更新 ，那么它可能会分散在硬盘上，查询时需要多次磁盘 I/O，会影响性能。
  7. 工作负载类型：在写密集型系统中，维护索引需要额外的写操作，这可能导致性能下降。在读密集型系统中，索引通常能明显提高查询速度。
  8. 数据库服务器的内存：如果索引不能完全适用于内存，那么查询时可能不得不频繁地从磁盘读取数据，导致性能下降。
  9. 查询中似乎用的运算符和功能：像 LIKE %keyword%这样的查询不会很好地利用索引，因为它在搜索字符串的中间部分，除非索引时 FULLTEXT 类型的。
  10. 索引维护：随着时间的推移，索引可能因为插入、更新和删除操作导致碎片化。碎片化的索引可能会减慢查询速度，需要定期重新组织优化。

总之，索引是一个强大的工具，它可以在许多情况下显著提高查询性能，但也由可能成为性能问题的根源。合理地设计和使用索引是数据库性能调优的重要方面。在实际应用中，应仔细考虑是否创建索引，创建何种类型的索引，并使用数据库的查询执行计划工具来分析具体查询的性能。

18. 百万级别或以上的数据如何删除？

关于索引：由于索引需要额外的维护成本，因为索引文件时单独存在的文件，所以当我们对数据的增加、修改和删除，都哦会产生额外的对索引文件的操作，这些操作需要消耗额外的 IO，会降低增改删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询 MySQL 官方手册得知删除数据的速度和创建的索引数量是成正比的。

  • 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多种）
  • 然后删除其中无用的数据（此过程需要不到 2 分钟）
  • 删除完成后重新创建索引（此时数据较少了），创建索引也非常快，约十分钟左右。
  • 与之前的直接删除绝对是要快速很多，更别说万一删除中断，一切删除会回滚，那就更坑了。

19. 什么是最左前缀原则？什么是最左匹配原则？

最左前缀原则：这是关于复合索引如何工作的一个原则，即在使用复合索引进行查询时，索引从左至右的顺序很重要，数据库查询优化器会使用这个顺序来匹配查询中 WHERE 子句的条件。最左前缀原则表面，对于复合索引，索引条件中使用的字段必须是索引中从最左边开始的连续字段。

示例：

假设有一个复合索引（A，B，C）。根据最左前缀原则，查询可以利用以下索引前缀：

  • （A）
  • （A，B）
  • （A，B，C）

但无法利用以下顺序的列作为索引：

  • （B）
  • （B，C）
  • （A，C）

只有当 (A) 被包含，且条件按照索引列的顺序出现时，索引才会被采用。最左匹配原则：最左匹配原则是对最左前缀原则的扩展，它不仅关注 WHERE 子句中使用的列，还关注这些列的匹配方式。当使用 LIKE 或者范围查询（比如>，<，BETWEEN，!=）时，索引可以被多个列使用，但一旦碰到第一个范围查询，该查询之后的列（即使在复合索引中定义了）将不会被用作索引查询。

示例：

继续考虑上一个复合索引（A，B，C）。对于查询：

代码语言：javascript复制
SELECT * FROM WHERE A = 1 AND B > 2 AND C > 3;

索引会用到 A 和 B 字段，但一旦使用到了 > 这个范围查询，C 会被忽略，即使是 C 是索引的一部分。

在面试中清晰地阐述这些原则不仅展现了你对数据库索引的理解，也显示了你在设计查询和优化数据库性能时的考虑程度。这些原则对数据库性能优化至关重要，遵循它们可以大幅提高查询效率。

20. 什么时聚簇索引？何时使用聚簇索引与非聚簇索引？

聚簇索引：聚簇索引并不仅仅是一个索引，而是决定表中数据存储和排序方式的数据结构。在聚簇索引中，表中数据物理上按索引键顺序存储。换句话说，聚簇索引定义了数据在磁盘上的物理顺序。每个表只能由一个聚簇索引，因为不能有两种物理数据顺序。合适使用聚簇索引：

  1. 主键查询：经常通过主键进行查询的表，适合使用聚簇索引，因为它提供了对主键的快速查找能力。
  2. 范围查询：在进行范围查询（如BETWEEN, >, <）时，聚簇索引可以快速地顺序访问连续的数据。
  3. 全表扫描：聚簇索引对于全表扫描特别有效，因为它使得磁盘上的数据顺序和索引相匹配，减少了磁盘 I/O。

非聚簇索引：非聚簇索引与聚簇索引相反，这个索引的存储是分离于数据本身的。非聚簇索引包含索引键和执行数据所在行的指针。一个表可以有多个非聚簇索引，因为它们只是执行数据的一种方式，而不决定数据的物理顺序。

合适使用非聚簇索引：

  1. 特定列查询：如果查询经常涉及表中特定的几列，而这些列并不是聚簇索引的一部分，那么创建非聚簇索引可以加速这些查询。
  2. 索引覆盖查询：当查询可以直接通过索引中的数据得到结果，即所需数据全包包含在索引中（覆盖索引），这是使用非聚簇索引能极大提高性能。
  3. 插入性能：由于非聚簇索引不要求按照索引键存储数据，所以在频繁插入数据的表中使用非聚簇索引，可以提高插入速度。
  4. 外键列：在外键列上使用非聚簇索引，可以加速对关联表的 JOIN 操作。

在设计数据库和查询时，适当地使用聚簇索引和非聚簇索引能显著改善性能，选择何时使用哪种索引应基于数据访问模式和查询性能的需要。通常，一个好的起点是在频繁查询的列上建立索引，并根据实际的查询性能来调整索引策略。

21. MySQL 连接器

首先需要在 MySQL 客户端登陆才能使用，所以需要一个连接器来连接用户和 MySQL 数据库，我们一般使用：

代码语言：javascript复制
mysql -u用户名 -p密码

来进行 MySQL 登陆，和服务端建立连接。在完成 TCP 握手后，连接器会根据你输入的用户名和密码验证你的登陆身份。如果用户名和或密码错误，MySQL 会提示：Access denied for user，来结束执行；如果登陆成功，MySQL 会根据权限表中的记录来判定你的权限。

22. MySQL 查询缓存

MySQL 的查询缓存是一个特性，它可以缓存 SELECT 查询的结果集和对应的 SQL 语句。然而，这个特性在 MySQL 8.0 及更高版本中已经被废弃。如果面试官问到查询缓存，他们可能想了解你对该功能的历史理解以及当前的最佳实践。

当查询缓存有效时：

  • 何时使用：在数据变动不频繁且重复执行相同查询的场景下，查询缓存能显著提升性能。
  • 工作原理：当一个 SELECT 查询被执行时，数据库会检查查询缓存；如果查询与缓存中的一个条目匹配，MySQL 将立即返回存储的结果集，而不是重新执行查询。
  • 失效场景：任何对表的修改（INSERT、UPDATE、DELETE）都会导致存储有该表数据的所有缓存结果集被清除，这意味着查询缓存对于更新频繁的数据库来说不太有用。

当查询缓存被废弃后：

  • 性能策略转变：鉴于查询缓存的去除，性能优化策略更多地依赖于数据库设计（如规范化）、索引策略、查询优化（如避免全表扫描）以及缓存层的外部实现（如 Redis、Memcached）。
  • 应用程序层面的缓存：应用程序层面经常实现自己的缓存机制。例如，应用服务器可能缓存常见查询的结果集，或者使用专业的缓存系统，如 Redis，来缓存数据。
  • 传统优化技术：不依赖查询缓存的优化技术，如使用更有效的数据类型、使用索引、优化查询语句，成为性能提升的关键所在。
  • 配置优化：数据库管理员可以通过调整如 InnoDB 缓冲池大小、IO 配置、复制策略等来提高数据库性能。

面对这个问题的正确方式是要意识到查询缓存在 MySQL 最新版本中的变动，以及理解今天如何在没有查询缓存的情况下有效地优化查询和数据库性能。在面试中能够展示对过去功能的理解以及如何适应现代数据库最佳实践的能力，可以体现出你作为数据库专业人士的深度和广度。

23. MySQL 分析器

在面试中，当涉及到 MySQL 分析器（Analyzer），通常是在谈论 MySQL 查询执行过程中的一个环节。MySQL 分析器是一个内部的数据库软件组件，功能是分析和处理 SQL 语句，并生成一个对数据库执行的操作计划。以下是你可以在面试中讨论的关于 MySQL 分析器的几个关键点：

  1. SQL 解析：当一个 SQL 语句提交给数据库后，分析器首先对其进行解析，校验语法是否正确。
  2. 预处理：之后，分析器进行预处理，检查 SQL 语句中的表和列在数据库中是否存在，以及用户是否有权限对其进行操作。
  3. 查询优化：分析器会根据不同的策略选择一个最有效的执行计划。这涵盖了对查询条件的重写，决策如何利用索引，以及多个表的联接顺序等。
  4. 生成执行计划：分析器将选择一个最优的查询执行计划，这通常是一系列的操作算子，如表扫描、索引扫描、排序、联结等。

面试时可以讨论如何使用EXPLAIN或EXPLAIN ANALYZE命令来输出 MySQL 分析器生成的执行计划。这些命令对于开发人员和数据库管理员来说是优化查询和调试性能问题的有力工具。

24. MySQL 优化器

MySQL 优化器是数据库的一部分，它负责处理 SQL 语句并生成最佳的执行计划。这里是关于 MySQL 优化器的一些要点，你可以在面试中使用：

  1. 生成执行计划：MySQL 优化器在解析了 SQL 语句并进行预处理后，会评估多个可能的执行计划，并选择成本最低的那个。成本基于多种因素，包括行的预计数量（基于统计信息）、是否使用索引、联接的类型和顺序，以及 I/O 开销等。
  2. 查询优化：优化器会重写查询以改进性能。示例包括将 OR 条件改写为 IN 列表，使用等价转换规则，或者将子查询改写为联接。
  3. 索引选择：优化器会决定哪些索引可以用于加速查询，并选择最适合当前查询的索引。
  4. 联接优化：针对涉及多个表的查询，优化器需要确定表的联接顺序，这是个复杂的决策，涉及到可能的联接方法（如嵌套循环、排序-合并联接、哈希联接等）。
  5. 使用 EXPLAIN 命令：通过使用EXPLAIN命令或其变体（如EXPLAIN ANALYZE），可以查看优化器为特定查询生成的执行计划。这对于理解和优化查询至关重要。
  6. 优化器提示：开发者可以使用优化器提示来影响优化器的选择，如指定使用某个索引或改变表的联接顺序。

25. MySQL 执行器

MySQL 执行器是 MySQL 中负责执行查询语句并生成结果的组件。当一个 SQL 语句从客户端发出后，它经历几个步骤才最终被执行，这些步骤大致分为：

  1. 解析器（Parser）：首先，接收到的 SQL 语句会被解析器解析，检查其语法是否符合 MySQL 的规则。如果语法错误，将返回错误信息。
  2. 优化器（Optimizer）：SQL 查询可能有多种执行方式都能得到相同的结果。优化器的任务是评估这些可能的执行计划，并选择一个成本最低（执行时间最短、资源使用最少）的计划。
  3. 执行器（Executor）：执行器在解析和优化阶段完成后启动工作。它对优化器选定的执行计划进行操作，首先会检查用户是否有权限执行这些操作，如果没有权限则拒绝执行，并返回错误信息。如果用户有权限，执行器会根据优化器提供的计划，以逐步的方式执行计划中的操作。

具体到执行的过程中，执行器会进行以下步骤：

  • 打开表：根据查询需要打开一个或多个表。
  • 遍历行：对打开的表进行行遍历，如果涉及到多表联查，则进行相应的行组合。
  • 检查行：执行查询条件，对遍历的行进行筛选，保留符合搜索条件的行。
  • 返回结果：将通过行检查的结果集以表的形式返回给用户。

MySQL 执行器的效率在很大程度上取决于优化器选择的执行计划，以及数据本身的组织和索引。良好的数据设计和合理的索引可以显著提高查询效率。

26. 什么是临时表，何时删除临时表？

MySQL 中的临时表是为了存储临时数据，常在复杂的查询操作中使用，特别是那些涉及到重复引用数据集的地方。临时表可以是内存中的表，也可以是磁盘上的表，这取决于临时表的大小以及系统的配置。临时表有以下特点：

  • 在当前会话中可见，对其他会话不可见。
  • 临时表可以有与现有表相同的名字，不会造成冲突，因为 MySQL 会根据上下文判断应该操作哪一个表。
  • 当你关闭连接时，相关的临时表会自动删除。也就是说，当客户端的 session 结束时，其创建的所有临时表都会被自动删除。

何时删除临时表：

  1. 手动删除：你可以像操作普通表一样使用 DROP TABLE 语句来删除一个临时表。
  2. 自动删除：
  • 当用户的会话结束时，临时表会被自动删除。
  • 如果是在存储过程中创建的临时表，存储过程完成执行后，临时表也会自动删除。

在数据库操作中，你可能需要使用临时表来执行以下操作：

  • 存储复杂查询的中间结果。
  • 存储结果集，用于在复杂事务处理中的多个步骤之间传递。
  • 作为复杂 JOIN 操作的一部分，将数据集拆分成易于管理的部分。
  • 进行数据转换、行到列的转换或其他形式的数据处理。

总结来说，临时表是会话（session）敏感的，创建它们的目的是为了在会话期间临时存储数据，它们在会话结束时被自动删除，或者也可以被用户显式地删除。

27. 谈谈 SQL 优化的经验

  1. 索引优化：
  • 使用索引来提高查询速度，特别是在大表中。
  • 确定何时创建复合索引以及如何根据查询模式来设计索引。
  • 避免非必要的全表扫描，尤其是在 WHERE 子句和 JOIN 条件中使用索引列。
  1. 查询改写：
  • 优化 WHERE 子句，确保使用有效的条件筛选，避免无意义的比较和计算。
  • 消除子查询，当可能时使用连接(JOINs)代替子查询，提高效率。
  • 使用聚合函数时做好数据筛选以减少计算量。
  1. 逻辑查询优化：
  • 理解并利用 SQL 执行计划，找到查询的瓶颈。
  • 利用 CASE 语句，尝试减少复杂逻辑的次数。
  • 减少数据搜索的范围，比如使用 LIMIT 语句返回只需要的行数。
  1. 表结构优化：
  • 正确选择数据类型，保持尽可能的紧凑。
  • 确定是否需要垂直或水平分割大表。
  • 使用适合的存储引擎来优化读写性能。
  1. 数据库配置：
  • 根据应用负载调整数据库服务器的配置。
  • 关注缓存参数，例如查询缓存，以及如何影响性能。
  1. 避免资源争用：
  • 最小化锁争用，通过合理设计事务和索引。
  • 理解并发性如何影响性能，适度的使用并发控制技巧。
  1. 使用批处理和事务控制：
  • 组合多个操作到一个大的事务中，减少事务提交的次数。
  • 对需要大量插入的数据进行分批处理，避免一次大量数据操作。
  1. 系统层面优化：
  • 监控系统性能，使用性能分析工具定位瓶颈。
  • 考虑 CPU、内存、磁盘 I/O 等资源使用情况。

28. 什么叫外连接

外连接（Outer Join）是 SQL 中的一种连接查询，用来从两个表中返回匹配的行，以及在另一个表中没有匹配的行。外连接主要有两种类型：左外连接（Left Outer Join）和右外连接（Right Outer Join）。还有一个全外连接（Full Outer Join），但并不是所有的数据库系统都支持全外连接。

  1. 左外连接（Left Outer Join）:
    • 返回左表（LEFT JOIN 关键字左侧的表）的所有行，即使右表中没有匹配的记录。
    • 如果右表中没有匹配，结果中右表的部分会包含 NULL。
    • 通常，左外连接用于获取“有数据或可能没有匹配数据”的表的全部记录，以及它们在关联表中的任何匹配记录。
  2. 右外连接（Right Outer Join）:
    • 返回右表的所有记录以及左表中匹配的那些记录。
    • 如果左表中没有匹配，结果中左表的部分会包含 NULL。
    • 右外连接不如左外连接常见，因为你总是可以通过改变 JOIN 的顺序来实现同样的效果。
  3. 全外连接（Full Outer Join）:
    • 返回左表和右表中的所有行。
    • 当左表中某行没有匹配右表时，或者右表中某行没有匹配左表时，结果集中对于缺失的匹配会用 NULL 填充。
    • 这种类型的 JOIN 合并了左外连接和右外连接的结果。

在 SQL 查询中，外连接通常通过LEFT OUTER JOIN、RIGHT OUTER JOIN或者FULL OUTER JOIN关键词实现。这是在处理数据库中的关系性数据时，尤其是在报表生成或数据分析时非常有用的一个特性，可以提供比内连接（Inner Join）更广泛的视图，因为它包含了没有关联的数据行。

例如，如果你想列出所有员工以及他们可能参加的所有项目，即使有些员工没有参加任何项目，你可以使用左外连接（Left Outer Join），将员工表作为左表，项目分配表作为右表。

在执行这种左外连接时，SQL 语句可能看起来像这样：

代码语言：javascript复制
SELECT employees.*, projects.*
FROM employees
LEFT OUTER JOIN project_assignments ON employees.id = project_assignments.employee_id;

在这个例子中：

  • employees 是左表，project_assignments 是右表。
  • LEFT OUTER JOIN 关键字指示我们想要执行左外连接。
  • ON employees.id = project_assignments.employee_id 定义了两个表之间的关联条件。

返回的结果集将包含 employees 表的所有行。对于 project_assignments 表：

  • 如果发现 employee_id 匹配，对应的 project_assignments 表中的数据会与 employees 表中的记录一同出现在结果集中。
  • 如果 employee_id 没有匹配，project_assignments 表相关的列会以NULL填充。

这意味着即使某些员工未分配任何项目，他们的信息仍会出现在查询结果中。

类似地，右外连接会包含右表（在本例中是 project_assignments）的所有记录，无论它们是否在左表（employees）中有匹配。如果数据库支持全外连接的话，结果集将包含两个表中的所有记录，并且没有匹配的记录将用NULL填充相应的列。但正如之前提到的，全外连接并不是所有数据库系统都支持，特别是 MySQL，它不支持全外连接语法。

外连接特别适用于需要一起考虑匹配和不匹配记录的情况，常用于报表制作、数据分析以及需要将不同数据源的信息组合起来时的情况。

29. 什么叫内连接

内连接（Inner Join）是数据库 SQL 语言中最常见的类型的连接，它用于返回两个或多个表中符合连接条件的记录。

当进行内连接时，只有当两个表中的记录在指定的连接条件上相匹配时，这些记录才会出现在查询结果中。如果在一个表中有记录而在另一个表中没有相匹配的记录，则这些记录不会出现在最终的结果集中。 简单来说，内连接返回的是两个表交集的部分。

语法通常是：

代码语言：javascript复制
SELECT column_list
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

这里：

  • column_list 指定希望从连接的表中选择哪些列。
  • table1 和table2 是进行连接的表。
  • INNER JOIN 是连接的类型。
  • ON table1.column_name = table2.column_name 表明了连接使用的条件，即指出了两个表中应该如何匹配记录。

例如，如果你有一个 employees 表和一个 departments 表，每个员工都分配给一个部门，而你想列出所有员工及其相应的部门名称。如果只对那些确实被分配了部门的员工感兴趣（假设没有员工在没有部门的情况下），则可以使用内连接：

代码语言：javascript复制
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

在这个查询中：

  • 结果集将只包括那些在 employees 表中有对应 department_id 且 department_id 在 departments 表中也存在的 employees 记录。
  • 如果 employees 表中的某个员工没有对应的 department_id 与 departments 表中的 id 相匹配，则这个员工的信息不会出现在查询结果中。

内连接提供了一种高效的方式来获得不同表中相关联的数据，是数据库关系模型中实现关联查询的基石。

30. 使用 union 和 union all 时需要注意什么？

在 SQL 中，UNION和UNION ALL都是用来合并两个或多个 SELECT 语句的结果集，但它们处理重复行的方式不同，而且在性能方面也有差异。使用这两个操作时，你需要注意以下几点：

  1. 列的数量和顺序必须相同：
  • UNION或UNION ALL要求所有被联合的 SELECT 语句中选出的列数目必须一致。
  • 各个查询中相对应的列数据类型也需要兼容，以便能够合并结果集。
  1. 处理重复的行：
  • UNION会合并多个 SELECT 语句的结果集，并且去除重复的行（相当于隐式地使用了DISTINCT关键字）。因此，它通常涉及额外的排序或比较操作来确保结果集中的唯一性。
  • UNION ALL将直接合并结果集，包含所有的重复行。由于不需要额外的去重操作，UNION ALL在性能上通常优于UNION。
  1. 性能考虑：
  • 如果不需要去重，使用UNION ALL来避免不必要的排序操作，可以获得更好的查询性能。
  • 如果你确实需要去重，使用UNION，但请知晓这会增加额外的计算成本。
  1. 使用场景：
  • 使用UNION适合需要合并两个结果集并返回不重复记录的情况。
  • UNION ALL适用于合并结果集时，不关心是否有重复记录，或者知道不存在重复记录的场合。
  1. 索引使用情况：
  • UNION操作可能影响到数据库优化器是否能够有效地使用索引，尤其是当涉及去重时。
  • UNION ALL运行时不需要对结果集做排序或去重，因此通常更有可能利用到索引。
  1. 排序和分组：
  • 你可能会希望对最终的联合结果进行排序(ORDER BY)或分组(GROUP BY)。这种操作应用于全部联合的结果集之外。
  • 当使用UNION时，排序操作需要放在最外围的查询中。

当准备面试时，清楚地说明你知道何时使用 UNION 和 UNION ALL，以及它们之间差异的知识表明你有扎实的 SQL 知识基础，能够为不同的数据合并需求选择合适的工具。这些知识点在需要数据汇总和报告生成时特别重要。

31. MyISAM 存储引擎的特点

MyISAM 是 MySQL 数据库管理系统中一个较早的存储引擎，后来被 InnoDB 所取代成为默认的存储引擎。但是，由于其独特的特性和优势，在某些特定的应用场景中，MyISAM 仍然有其用武之地。下面是一些 MyISAM 存储引擎的主要特点：

  1. 表锁定（Table-level Locking）:
  • MyISAM 在执行查询（SELECT）和更新（INSERT, UPDATE, DELETE）操作时会对整个表进行锁定。
  • 这意味着在写入时，其他的写入操作必须等待当前写入完成，这可能会导致在高并发写入情况下性能降低。
  1. 不支持事务（Transactions）:
  • MyISAM 不支持事务处理，这对于需要原子性、一致性、隔离性和持久性（ACID 属性）这些事务特性的应用来说是一个重要的限制。
  1. 全文索引（Full-text Indexing）:
  • MyISAM 支持全文索引，在需要执行全文搜索的应用中这一点非常有用。
  • 直到 MySQL 5.6 版本，InnoDB 才开始支持全文索引，因此在此之前 MyISAM 常常是执行全文搜索的首选。
  1. 压缩表（Compressed tables）:
  • MyISAM 支持表的压缩，这可以在不需要频繁更新数据的存档和只读数据库中减少磁盘空间的使用。
  1. 存储空间和内存使用:
  • MyISAM 通常比 InnoDB 使用更少的磁盘空间和内存。但是，这可能是以牺牲数据完整性和恢复能力为代价的。
  1. 读操作优化:
  • 在主要进行读取操作的场景下（如静态内容），MyISAM 的性能通常高于支持事务处理的存储引擎。
  1. 数据和索引分离:
  • MyISAM 存储引擎将数据和索引存储在两个分离的文件中（.MYD文件用于数据，.MYI文件用于索引），这使得可以独立进行备份或恢复。
  1. 易于备份:
  • 由于 MyISAM 的表是独立存储的，这使得对单一表的备份和恢复变得比较简单。

尽管有这些特点，MyISAM 的局限性，尤其是不支持事务和表锁定，意味着它在现代的应用程序中越来越少地被选择。尤其是随着 InnoDB 在性能和功能上的不断改进，许多原本使用 MyISAM 的场景都转向了 InnoDB。由于 InnoDB 提供了更先进的功能，如行级锁定和事务支持，它通常是新应用的更佳选择。

32. InnoDB 存储引擎的特点

InnoDB 是 MySQL 数据库的默认存储引擎，尤其适用于处理大量数据的应用程序。它提供了诸多对于现代数据库系统来说不可或缺的特性：

  1. 事务支持（ACID 兼容）:
  • InnoDB 提供了对数据库事务的支持，遵循 ACID（原子性、一致性、隔离性、持久性）模型。这对于需要保证数据完整性的应用尤为重要。
  1. 行级锁定（Row-level locking）和 MVCC（多版本并发控制）:
  • InnoDB 通过行级锁定和 MVCC 来降低锁定的开销，从而提高并发操作时的性能。这使得在多用户环境中，读操作不会阻塞写操作，反之亦然。
  1. 外键约束（Foreign Key Constraints）:
  • InnoDB 支持外键约束，这是强制数据完整性的一种方式。在相关表中添加、更新和删除操作时，外键约束确保了参照完整性。
  1. 崩溃恢复（Crash Recovery）:
  • InnoDB 包含自动崩溃恢复的能力。在系统意外崩溃后，InnoDB 能够恢复到一个一致的状态，而不需要通过复杂的过程重构数据库。
  1. 表空间（Tablespace）管理：
  • InnoDB 使用表空间来存储数据。这意味着 InnoDB 表的大小只受限于磁盘大小，而非文件大小的限制。
  1. 缓冲池（Buffer Pool）:
  • InnoDB 维护一个内存中的缓冲池，用于缓存表数据和索引，从而减少磁盘 I/O 操作。这对性能是一个重大的提升。
  1. 可配置性：
    • InnoDB 是高度可配置的，数据库管理员可以根据系统的具体需求和性能特点调整缓冲池大小、IO 容量等。
  2. 数据和索引的物理存储:
  • InnoDB 将数据和索引存储在单一的表空间文件中（尽管可以配置为每个表使用独立的文件），这使得数据管理更为一体化。
  1. 更好的 Blob 支持：
  • InnoDB 针对大型二进制对象（Blob 字段）提供更优的性能，并允许更有效的更新。
  1. 顺序和随机读取的优化:
  • InnoDB 对于具有高并发读取、写入以及大量 JOIN 操作的应用程序通常会有更好的性能表现。

这些特性使得 InnoDB 成为适用于需要高可靠性和高性能的生产数据库环境的理想选择。在准备面试的过程中，展示对 InnoDB 特性的具体理解，尤其是与其他存储引擎（如 MyISAM）的对比，可以帮助面试官评估你对 MySQL 数据库系统的掌握程度。

数据库 存储 mysql 数据 索引

0 人点赞