MySQL面试题及答案整理
1. MySQL中有哪几种锁？
MySQL中有多种锁类型，它们可以根据不同的分类标准进行划分。以下是一些主要的锁类型：
- 按粒度分：
- 表锁：每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。常用于整表数据迁移的场景。
- 行锁：对表中一行或多行记录进行加锁控制的方式。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
- 页锁：在页的粒度上进行锁定，只有BDB存储引擎支持页锁。开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度也介于表锁和行锁之间，并发度一般。
- 按类型分：
- 读锁（共享锁，S锁）：多个读操作对同一个资源共享同一个锁，多个读操作可以同时进行而不会互相影响。
- 写锁（排他锁，X锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
- 意向锁：属于表级锁，包括意向读锁（IS锁）和意向写锁（IX锁）。在对表记录添加S或X锁之前，会先对表添加IS或IX锁。
- 按性能分：
- 乐观锁：假设并发操作时不会发生冲突，只在提交事务时检查数据是否被其他事务修改过。常用于读多写少的场景。
- 悲观锁：假设并发操作时会发生冲突，因此在操作期间持有锁来避免冲突。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。
此外，还有一些特定的锁类型，如：
- 间隙锁（Gap Lock）：锁定一个范围的键，但不包括这些键的实际值。
- 记录锁：锁定单条索引记录。
- 临建锁：与临时表相关的锁。
需要注意的是，不同的MySQL存储引擎（如MyISAM和InnoDB）支持的锁类型和实现方式可能会有所不同。例如，MyISAM存储引擎主要使用表级锁，而InnoDB存储引擎则支持行级锁和表级锁。
2. MySQL中有哪些不同的表类型？
MySQL支持多种不同的表类型，也称为存储引擎。这些存储引擎决定了数据在磁盘上如何存储以及数据如何被检索。以下是一些主要的MySQL表类型：
-
InnoDB：
- 支持事务安全（ACID兼容）。
- 支持行级锁定和外键。
- 数据存储在表空间，可能有一个或多个文件（共享表空间或独立表空间）。
- 主键索引采用聚集索引。
-
MyISAM：
- 非事务安全。
- 使用表级锁定。
- 存储三个文件：
.frm（表结构）、.MYD（数据文件）、.MYI（索引文件）。 - 支持全文索引。
-
Memory（HEAP）：
- 数据存储在RAM中，重启后数据会丢失。
- 表访问速度非常快，但只适合用于较小的数据集。
- 非事务安全。
-
CSV：
- 数据以CSV格式存储，纯文本文件，可以用文本编辑器查看。
- 没有索引，因此查询速度可能较慢。
-
ARCHIVE：
- 用于存储和检索大量归档数据。
- 使用zlib压缩，只支持INSERT和SELECT操作。
- 支持行级锁定。
-
Merge：
- 允许MySQL DBA或开发者将一系列MyISAM表组合成一个Merge表，并在查询时作为一个表来引用它们。
-
Federated：
- 一个特殊的存储引擎，允许一个MySQL数据库服务器访问另一个MySQL服务器上的表。
-
NDB Cluster：
- 用于MySQL Cluster分布式数据库系统。
-
Blackhole：
- 任何写入它的数据都会“消失”，而读取操作将返回一个空集。它可以用于复制数据到一个或多个目的地，而不实际存储数据。
此外，还有一些其他的存储引擎，但上述是最常用和重要的几种。你可以通过运行SHOW ENGINES;命令来查看MySQL服务器支持的所有存储引擎及其状态。注意，不同的MySQL版本可能支持不同的存储引擎集合。
3. MyISAM和InnoDB的区别
MyISAM和InnoDB是MySQL中两种常用的存储引擎，它们之间存在一些显著的区别，主要包括以下几个方面：
-
事务支持：
- InnoDB支持事务，具有ACID（原子性、一致性、隔离性、持久性）属性，确保数据的完整性和一致性。
- MyISAM不支持事务，每次查询都是原子的，但整体不支持事务的四个特性。
-
锁定机制：
- InnoDB支持行级锁定（Row-Level Locking），这是一种高度并发的锁定机制，可以减少锁争用问题，提高并发性能。
- MyISAM只支持表级锁定（Table-Level Locking），在并发写入时可能导致性能下降。
-
外键约束：
- InnoDB支持外键约束，这是关系数据库的重要功能之一，可以确保数据之间的参照完整性。
- MyISAM不支持外键约束，无法强制保证数据之间的参照完整性。
-
存储结构：
- InnoDB的表数据和主键索引是存储在一起的，被称为聚集索引（聚簇索引）。
- MyISAM的表数据和索引是分开存储的，是非聚集索引（非聚簇索引）。
-
崩溃恢复：
- InnoDB具有自动崩溃恢复功能，当数据库崩溃后，可以自动恢复数据，保护数据免受损坏。
- MyISAM不具备自动崩溃恢复功能，在数据库崩溃后可能需要手动修复数据。
-
数据压缩：
- InnoDB支持数据压缩，可以减少磁盘空间占用和IO操作，提高存储效率。
- MyISAM不直接支持数据压缩，但可以通过其他方式实现（如使用第三方工具）。
-
索引特性：
- MyISAM支持全文索引，可以支持复杂的文本查询。
- InnoDB和MyISAM都支持B+树数据结构的索引，但具体实现和性能上可能有所差异。
-
文件存储：
- MyISAM在磁盘存储上有三个文件：.frm用于存储表的定义，.MYD用于存放数据，.MYI用于存放表索引。
- InnoDB的表结构文件与MyISAM相同，为.frm文件，但数据和索引的存储方式根据配置可以是共享表空间存储或多表空间存储。
综上所述，MyISAM和InnoDB在事务支持、锁定机制、外键约束、存储结构、崩溃恢复、数据压缩、索引特性和文件存储等方面存在显著的差异。在选择使用哪种存储引擎时，需要根据具体的应用场景和需求进行权衡和选择。
4. InnoDB支持的四种事务隔离级别
InnoDB支持的四种事务隔离级别分别是：
-
READ UNCOMMITTED（读取未提交）：
- 这是最低的隔离级别。在这个级别下，一个事务可以读取另一个尚未提交的事务的数据。这可能导致脏读（Dirty Read）、不可重复读（Nonrepeatable Read）和幻读（Phantom Read）。
- 由于可以读取到其他事务未提交的数据，因此该级别也被称为“脏读”。
-
READ COMMITTED（读取已提交）：
- 大多数数据库系统的默认隔离级别不是READ COMMITTED，但InnoDB支持这个级别。
- 在此级别下，一个事务只能看到已经提交事务所做的更改。这可以防止脏读，但仍然可能出现不可重复读和幻读。
- 在Read Committed级别下，对于Update和Delete语句，InnoDB只会持有对应的更新或删除行的锁。对于未符合条件的记录，在WHERE条件计算时，会移除对应的行级锁。
-
REPEATABLE READ（可重复读）：
- 这是MySQL InnoDB存储引擎的默认隔离级别。
- 在此级别下，对同一字段的多次读取结果都是一致的。这解决了脏读和不可重复读的问题，但仍然存在幻读的可能性。
- InnoDB通过多版本并发控制（MVCC）和间隙锁（Gap Lock）来解决幻读问题。
-
SERIALIZABLE（可串行化）：
- 这是最高的隔离级别。
- 在此级别下，所有的事务依次逐个执行，这样事务之间就不可能产生干扰。但这也意味着事务并发执行的效率非常低下。
- 在SERIALIZABLE隔离级别下，InnoDB使用共享锁和排他锁来实现事务的串行化执行。
每个隔离级别都有其优缺点，选择合适的隔离级别需要根据具体的应用场景和需求进行权衡。例如，对于需要高并发性能的系统，可能会选择较低的隔离级别；而对于需要确保数据一致性的系统，可能会选择较高的隔离级别。
5. CHAR和VARCHAR的区别
CHAR和VARCHAR在MySQL中都是用于存储字符串的数据类型，但它们之间存在一些重要的区别：
-
存储方式：
- CHAR是固定长度的字符类型。无论实际存储的字符数是多少，它都会使用定义的长度来存储数据。如果插入的字符串长度小于定义的长度，CHAR会用空格填充到指定长度。
- VARCHAR是可变长度的字符类型。它根据实际存储的字符数动态分配存储空间，不会浪费多余的空间。
-
存储空间：
- CHAR会按照定义的长度分配存储空间，即使实际存储的字符数较少。这可能会导致空间浪费。
- VARCHAR则根据实际存储的字符数分配存储空间，更加灵活高效。
-
尾随空格：
- 由于CHAR在存储时会用空格填充到指定长度，所以在检索CHAR字段的值时可能需要删除尾随空格。
- VARCHAR没有这个问题，因为它不会填充尾随空格。
-
长度限制：
- CHAR的长度范围是1到255个字符。
- VARCHAR的标准长度范围也是1到255个字符，但在某些数据库系统中（如MySQL），VARCHAR的最大长度可以达到65535个字节（注意这里指的是字节，而不是字符，具体字符数取决于使用的字符集）。
-
访问效率：
- 由于CHAR是固定长度的，它在某些情况下可能会比VARCHAR稍微快一些，因为数据库可以更快地定位到数据的存储位置。但是，这种差异通常在实际应用中并不显著。
-
适用场景：
- CHAR适合存储长度固定且变化不大的数据，如手机号码、身份证号码等。
- VARCHAR适合存储长度可变的数据，如用户名、地址等。
总结来说，CHAR和VARCHAR各有优缺点，在选择使用时需要根据具体的应用场景和需求进行权衡。如果数据长度固定且变化不大，可以选择CHAR；如果数据长度可变，建议选择VARCHAR。
6. 主键和候选键的区别
主键和候选键在关系型数据库中扮演着重要的角色，它们之间的主要区别如下：
-
定义与唯一性：
- 主键（Primary Key）：是表中的一个特殊列或列组合，用于唯一标识表中的每一行数据。每个表只能有一个主键。主键的值在表中必须是唯一的，且不允许为空。
- 候选键（Candidate Key）：是能够唯一标识表中每一行数据的列或列组合，但不一定被选为主键。一个表可以有多个候选键，每个候选键也必须具有唯一性。候选键可以包含空值或重复值，但在实际作为主键使用时，必须满足非空和唯一性的要求。
-
选择性与使用：
- 主键是必选的，也就是说，每个记录必须具有主键值。主键用于唯一地标识表中的每个记录，确保每个记录具有唯一的标识符。
- 候选键可以被选择性地用作主键，但前提是满足唯一性和非空的要求。候选键除了可能被用作主键外，还可能被用作其他目的，如参照外键或用于数据的逻辑关联。
-
复合键：
- 主键可以由单个列或多个列组合而成，但通常建议使用最少的列来定义主键，以提高性能和维护的方便性。
- 当候选键由多个列组成时，称为复合候选键（Composite Candidate Key）。复合候选键要求组合键的值在全表中都是唯一的，但单独的每一列可能不满足唯一性。
-
稳定性与永久性：
- 主键的值在数据的生命周期中应该是稳定的，不会随着时间或其他因素的改变而变化。这确保了主键的唯一性和可靠性，避免了数据的混乱和冲突。
- 候选键同样具有这种稳定性要求，但在实际作为主键使用之前，可能需要进行额外的验证和确认。
总之，主键和候选键在关系型数据库中都具有唯一标识表中数据的作用，但它们在定义、选择性与使用、复合键以及稳定性等方面存在明显的区别。
7. myisamchk的用途
myisamchk是MySQL数据库中的一个命令行工具，专门用于对MyISAM存储引擎的表进行检查、修复和优化。以下是myisamchk的主要用途：
- 修复损坏的MyISAM表：当MyISAM表因为某种原因（如硬件故障、操作错误等）损坏时，myisamchk可以尝试修复表，使其恢复正常状态。
- 优化表：myisamchk可以重建索引，从而提高查询性能和表的整体性能。
- 检查表的完整性：myisamchk可以对表进行检查，查找并报告表中的错误和问题，但不会自动修复这些问题。
- 压缩表：myisamchk可以将MyISAM表中的空间碎片整理，从而减少表的磁盘占用空间。
- 还原被恶意修改的表：如果MyISAM表被攻击者恶意修改，例如通过对数据进行篡改或删除行，myisamchk可以尝试还原这些表。
需要注意的是，myisamchk只适用于MyISAM存储引擎，不适用于其他存储引擎（如InnoDB）。此外，在使用myisamchk时，需要确保MySQL服务器没有运行，因为myisamchk直接操作MyISAM表文件。
此外，myisamchk还提供了多种检查选项，如–information、-i（打印所检查表的统计信息）、–fast、-F（只检查没有正确关闭的表）等，以满足不同的需求。
总的来说，myisamchk是管理和维护MySQL中MyISAM表的重要工具。
8. MyISAM Static和MyISAM Dynamic的区别
MyISAM Static和MyISAM Dynamic是MySQL中MyISAM存储引擎的两种不同格式，它们在数据文件的存储和管理方面存在一些显著的区别。
-
固定大小与动态大小：
- MyISAM Static：在这种格式下，数据文件中的每一列都有预先定义好的固定长度。这意味着在创建表时，必须为每个列指定大小，并为每个列分配一个固定大小的空间来保存数据。这种格式适用于具有固定数据模式且不需要高度动态更改操作的应用程序。
- MyISAM Dynamic：与Static格式相反，Dynamic格式支持动态变化大小和结构的数据文件。这意味着数据列的长度可以根据实际存储的数据大小进行动态调整。这种格式更适合对表进行修改操作，特别是当表中含有像VARCHAR、TEXT或BLOB等可变长度字段时。
-
适用场景：
- MyISAM Static：由于数据文件的固定大小特性，Static格式通常用于只读或很少修改的大型静态表。它在受损情况下更容易恢复，因为数据记录的提取和恢复工作相比其他类型要容易。
- MyISAM Dynamic：Dynamic格式由于其动态变化大小的能力，更适用于需要频繁修改数据的场景。它允许根据实际需求动态分配存储空间，从而更有效地利用磁盘资源。
-
性能特点：
- MyISAM Static：由于数据文件固定大小且结构固定，Static格式支持非常快速的顺序扫描，并且可以非常快速地回答由简单索引匹配生成的查询。
- MyISAM Dynamic：虽然Dynamic格式在动态分配存储空间方面具有优势，但由于其需要管理动态变化的数据结构，因此在某些情况下可能会比Static格式稍微慢一些。然而，这种性能差异通常在实际应用中并不显著。
总的来说，选择MyISAM Static还是MyISAM Dynamic取决于具体的应用场景和需求。如果数据模式固定且不需要频繁修改，那么Static格式可能是一个更好的选择；而如果数据需要频繁修改且含有可变长度字段，那么Dynamic格式可能更合适。
9. TIMESTAMP列的行为
在MySQL中，TIMESTAMP列的行为主要包括以下几个方面：
-
默认值与自动更新：
- 当一个新记录被插入到表中时，如果没有明确为TIMESTAMP列指定值，它会自动设置为当前的日期和时间。
- 如果一个已存在的记录的TIMESTAMP列被更新（其他列的值发生变化），TIMESTAMP列的值也会自动更新为当前的日期和时间。这可以通过在创建表时为TIMESTAMP列指定DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性来实现。
-
时区转换：
- TIMESTAMP列的值在存储时会从客户端的时区转换为UTC（世界标准时间）。当从表中检索TIMESTAMP列的值时，它会从UTC转换回客户端的时区。这种时区转换特性使得TIMESTAMP列在处理跨时区数据时特别有用。
-
存储范围与大小：
- TIMESTAMP列可以存储从’1970-01-01 00:00:01’ UTC到’2038-01-19 03:14:07’ UTC之间的日期和时间值。注意，这个范围受限于UNIX时间戳的表示范围。
- 在MySQL 5.6.4及更高版本中，TIMESTAMP列可以指定小数秒精度，例如TIMESTAMP(6)可以存储到秒后6位小数的时间戳。
-
NULL值处理：
- 在MySQL 5.6.6之前，TIMESTAMP列如果没有明确声明NULL属性，默认为NOT NULL。如果尝试将TIMESTAMP列的值设置为NULL，它会自动被设置为当前的日期和时间。
- 从MySQL 5.6.6开始，引入了explicit_defaults_for_timestamp系统变量。当这个变量被设置为ON时，TIMESTAMP列的默认行为与其他数据类型一致，即允许NULL值，除非明确声明为NOT NULL。
请注意，TIMESTAMP列的这些行为可能会受到MySQL版本和配置的影响。因此，在使用TIMESTAMP列时，最好查阅相关文档或手册以了解特定版本的详细行为。
10. AUTO_INCREMENT达到最大值会发生什么？
当MySQL中的AUTO_INCREMENT列达到其最大值时，会发生以下情况：
- 停止递增：AUTO_INCREMENT列将停止自动递增，因为已经达到了其数据类型所允许的最大值。
- 插入错误：任何进一步的插入操作都将产生错误，因为AUTO_INCREMENT列无法为新的记录分配一个唯一的、未使用的标识符（即主键值）。这个错误通常是因为密钥已被使用或超出范围。
- 数据完整性：由于无法为新记录生成唯一的主键值，数据库将无法保证数据的完整性。这可能导致应用程序出错或数据不一致。
为了避免这种情况，你可以考虑以下几个解决方案：
- 使用更大的数据类型：例如，如果你的AUTO_INCREMENT列当前使用INT数据类型（其最大值为2147483647），你可以考虑将其更改为BIGINT数据类型（其最大值为9223372036854775807），以支持更大的范围。
- 重置AUTO_INCREMENT值：如果你确信表中不会有太多的记录，或者你可以安全地重用已删除的记录的主键值，你可以考虑重置AUTO_INCREMENT列的起始值。这可以通过ALTER TABLE语句来实现。但请注意，在重置AUTO_INCREMENT值之前，确保该值未被表中的任何现有记录使用。
- 检查并优化数据模型：考虑是否需要如此多的记录。也许你可以通过优化数据模型、删除不必要的数据或归档旧数据来减少AUTO_INCREMENT列的使用量。
总之，当AUTO_INCREMENT列达到最大值时，你需要采取适当的措施来确保数据库的正常运行和数据的完整性。
标签：存储,MySql,面试题,常用,InnoDB,MyISAM,MySQL,数据,主键 From： https://blog.csdn.net/qq_15363475/article/details/139721367