当前位置：首页 > news >正文

mysql面试题合集-基础

news 来源：原创 2024/5/9 22:15:20

前言

工作很忙，本质还是自己比较懒惰，很久没更新博客了。近期打算面试，换个工作环境，那就先从面试题开始吧，后续也会逐渐更新自己在工作中的一些经验感悟。接下来切入主题，由于长期做前台开发工作，对mysql的使用较少，那就先从mysql开始吧。

mysql基础面试题合集

MySQL多表连接有哪些方式？

内连接（INNER JOIN）: 返回两个表中都有的记录。
左连接（LEFT JOIN 或 LEFT OUTER JOIN）: 返回左表中的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果为NULL。
右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）: 返回右表中的所有记录和左表中匹配的记录。如果左表中没有匹配的记录，则结果为NULL。
自连接（Self-Join）: 数据库表连接的一种，其中一个表被用作两个不同的表来对待，通常是用来处理层次结构或分类数据。例如，一个员工表可以自我连接以显示员工的直接上级。

请解释InnoDB和MyISAM存储引擎的区别。

事务支持：InnoDB支持事务，而MyISAM不支持。这意味着InnoDB可以确保数据的完整性和一致性，并支持回滚操作。
锁定机制：InnoDB使用行级锁定，而MyISAM使用表级锁定。行级锁定允许更高的并发访问，因为多个用户可以同时修改不同的行。
崩溃恢复：InnoDB有一个日志文件（redo log），可以在系统崩溃后恢复数据。而MyISAM没有这样的机制，如果数据库崩溃，可能会导致表损坏。
[说到这里补充介绍一下mysql中的log?]
{详见后文}
外键支持：只有InnoDB支持外键约束，这有助于维护数据的引用完整性。
表空间：InnoDB将数据和索引存储在一个表空间中，或者在多个文件（innodb_file_per_table）中。而MyISAM将数据和索引存储在独立的文件中。
全文搜索：MyISAM支持全文搜索，而InnoDB不支持。
索引类型：InnoDB支持聚集索引（主键索引），而MyISAM支持非聚集索引。
复制方式：InnoDB适用于主从复制的高可用性解决方案，因为它支持行级锁定和事务。
数据类型支持：两者基本相同，但InnoDB在某些数据类型上有更多的限制。
扩展性和灵活性：InnoDB更加扩展和灵活，支持更多的高级功能，如外键约束、行级锁定和事务处理。
默认的存储引擎：MySQL的默认存储引擎在早期版本中是MyISAM，但在后续版本中已经改为InnoDB。
总的来说，选择哪种存储引擎取决于具体的需求。如果需要事务支持、行级锁定、崩溃恢复或外键约束，InnoDB是更好的选择。如果不需要事务，并且更关心全文搜索和简单的数据完整性，MyISAM可能是一个更好的选择。

介绍一下mysql中的log？

错误日志（Error Log）：记录了启动、运行或停止MySQL服务器时出现的问题。包含服务器错误信息和潜在的服务器问题诊断。
查询日志（General Query Log）: 记录了已连接到MySQL服务器的客户端所发出的所有SQL查询。对于性能调优或监视非常有用，但可能会对性能产生影响，因为它记录了所有查询。
慢查询日志（Slow Query Log）:记录了执行时间超过long_query_time值的查询。对于优化性能或找出可能存在的性能问题非常有用。
二进制日志（Binary Log）:记录了对数据库执行的所有更改，主要用于复制和数据恢复。是主从复制中的重要组成部分，从服务器会使用这些日志来复制主服务器的更改。
中继日志（Relay Log）:在复制过程中，从服务器使用中继日志来保存从主服务器复制的二进制日志事件。
重做日志（Redo Log）:主要用于InnoDB存储引擎的崩溃恢复。这些日志包含已提交事务的记录，用于在系统崩溃后重新执行事务。
撤销日志（Undo Log）:与重做日志一起，用于InnoDB存储引擎的事务管理和崩溃恢复。它保存了数据修改之前的旧值，以便在必要时可以撤销更改。
事务日志（Transaction Log）:是数据库管理系统用来确认事务已提交的日志，主要与事务处理和恢复有关。

什么是B+树索引？为什么选择B+树？什么是覆盖索引和索引下推？

B+树（B±tree）是一种自平衡的多路搜索树，它广泛应用于数据库和文件系统的索引。在B+树中，所有的值都存储在叶子节点上，而内部节点仅用于导航。这种结构使得B+树在插入、删除和查找操作中能够保持树的平衡，从而提高查询效率。
选择B+树作为索引的主要原因如下： 平衡性：B+树的设计使其在插入、删除等操作后能够保持相对平衡，从而在实际应用中提供稳定的查询性能。 磁盘友好：B+树的叶子节点之间的链接使其非常适合磁盘I/O操作。因为磁盘读写是块为单位的，所以B+树的结构能够更有效地利用磁盘I/O操作，提高查询效率。 范围查询效率：由于所有值都存储在叶子节点上，并且叶子节点之间有链接，因此B+树支持高效的区间查询。
覆盖索引（Covering Index）：当查询只需要访问索引中的信息，而不需要访问实际的数据行时，我们称这种索引为覆盖索引。覆盖索引可以显著提高某些查询的性能，因为它减少了数据表中的I/O操作。
索引下推（Index Condition Pushdown, ICP）：这是MySQL优化器的一个特性，允许在索引搜索过程中更早地进行过滤，从而提高查询性能。通过将某些条件直接下推到索引搜索阶段，而不是等到检索到数据行后再进行过滤，可以减少需要检查的数据行数。

整数类型有哪些，长度有什么影响？实数类型有哪些，各自的特点是什么？字符串类型有哪些，它们的特点是什么？

整数类型：
TINYINT: 1字节;
SMALLINT:存储大小: 2字节
MEDIUMINT:存储大小: 3字节
INT 或 INTEGER:存储大小: 4字节
BIGINT:存储大小: 8字节
实数类型：
FLOAT:存储空间为4字节。
DOUBLE 或 REAL:存储空间为8字节。
DECIMAL 或 NUMERIC:定点数，用于精确表示小数。可以指定总的数字数和小数点后的数字数。例如，DECIMAL(10,2)可以存储最大为99999999.99的值。由实际的数字值决定所需的存储空间。
字符串类型：
CHAR: 定长字符串。不管实际存储的字符串长度如何，都会占用固定数量的空间。例如，CHAR(10)总是占用10个字符的空间。
VARCHAR: 变长字符串。只占用实际字符串长度加上一个或两个额外字节的空间（用于存储长度信息）。例如，VARCHAR(10)可以存储最多10个字符的字符串，但只占用实际字符串长度+1或+2个字节的空间。
TINYTEXT: 可存储最多255个字符的字符串。
BINARY ：用于存储二进制数据。
TINYBLOB 和 BLOB: 用于存储BLOB（二进制大对象）数据
GEOMETRY: 用于存储地理空间数据。这是MySQL中用于地理空间数据的特殊数据类型。

请解释数据库三范式。

三范式（3NF）用于减少数据冗余和提高数据结构的合理性。
第一范式（1NF）: 数据表的每一列都是不可分割的最小单元。确保每列都有唯一的值，没有重复行。数据表的每一列都是原子的，不可再分。
第二范式（2NF）: 满足第一范式。所有非主键列都完全函数依赖于主键（全依赖）。如果某列不是完全依赖于主键，则该列可以与其他列合并。
第三范式（3NF）: 满足第二范式。 非主键列之间没有传递依赖，也就是说，非主键列必须直接依赖于主键，而不是间接地依赖于主键。 消除冗余数据，确保数据表中的信息只出现一次。

事务的概念是什么，它有哪些特性？

事务（Transaction）是数据库操作的基本单位，它是一系列相关数据库操作的集合。这些操作要么全部执行，要么全部不执行，确保数据的一致性和完整性。事务通常用于管理数据的并发访问和恢复。 事务具有四个特性，也被称为ACID特性，包括：
原子性（Atomicity）：事务被视为一个不可分割的工作单位，事务中的操作要么全部完成，要么全部不完成，不会结束在中间某个环节。事务的原子性确保动作要么全部完成，要么完全不起作用。
一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同的级别，包括读未提交、读已提交、可重复读和串行化。
持久性（Durability）：意味着事务一旦提交，其结果就是永久性的。接下来的操作或故障不应对其有任何影响。

事务隔离级别？

读未提交（Read Uncommitted）：事务可以读取尚未提交的其他事务的数据。这是最低的隔离级别，允许脏读，不可重复读和幻象读（PhantomRead）。
读已提交（Read Committed）：一个事务只能看到其他已经提交事务对数据的修改。允许不可重复读和幻象读（PhantomRead）出现。
可重复读（Repeatable Read）：在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。这样就发生了在一个事务内两次读到的数据是一样的，因此称为是可重复读。
串行化（Serializable）：这是最高的隔离级别，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

脏读、幻读、不可重复读？

脏读（Dirty Read）：当一个事务读取了另一个未提交事务的数据时，可能会出现脏读。如果未提交事务回滚，那么这个已读取的数据就变成了“脏”数据。
不可重复读（Non-repeatable Read）：当一个事务在多次读取同一数据时，如果另一个事务修改了该数据并提交，那么前一个事务的两次读取结果不一致，导致不可重复读。
幻象读（Phantom Read）：当一个事务在执行过程中，另一个事务插入了满足某些条件的新数据，当第一个事务再次读取时，会发现新的“幻象”数据。

什么是SQL分类？

DDL（Data Definition Language）：数据定义语言。用于定义或修改数据库的结构。主要的DDL语句有CREATE、ALTER、DROP等，可以用来创建、修改或删除数据库、表、列等对象。
DML（Data Manipulation Language）：数据操作语言。用于添加、删除、修改和查询数据库中的数据。主要的DML语句有INSERT、UPDATE、DELETE、SELECT等。
DQL（Data Query Language）：数据查询语言。用于查询数据库中的数据，返回满足特定条件的数据记录。主要的DQL语句就是SELECT。
DCL（Data Control Language）：数据控制语言。用于定义数据库的访问权限和安全级别，以及创建和管理用户。主要的DCL语句有GRANT、REVOKE等。
TCL（Transaction Control Language）：事务控制语言。用于控制事务的提交和回滚，确保数据的完整性和一致性。主要的TCL语句有COMMIT、ROLLBACK等。

数据库查询优化有哪些方法？

数据库查询优化是提高数据库性能的关键步骤。以下是一些常见的数据库查询优化方法：
索引优化：合理使用索引可以大大提高查询速度。要避免全表扫描，尽量使用索引来检索数据。同时，要注意索引的选择性，避免冗余索引。
查询优化：简化查询语句，避免使用复杂的子查询和联接操作。在编写查询语句时，应尽量使用EXPLAIN关键字来分析查询的执行计划，以便找到潜在的性能问题。
数据库设计优化：合理设计数据库结构，包括表的结构、索引、关系等。遵循数据库设计范式，避免数据冗余，提高数据的一致性和完整性。
**分区：**对于大型数据库，可以考虑使用分区技术将数据分散到不同的物理存储设备上，以提高查询性能和管理效率。
缓存：利用缓存技术存储常用查询结果，减少对数据库的访问次数，提高系统响应速度。
硬件和配置优化：根据数据库的性能需求，合理配置硬件资源，如内存、CPU和存储设备。同时，调整数据库的配置参数，如缓冲区大小、连接数等，以优化性能。

如何对MySQL进行性能优化？

使用适当的数据类型：在定义表结构时，应尽量选择合适的数据类型，避免使用过大的数据类型，如使用INT代替BIGINT。
优化查询语句：使用EXPLAIN关键字分析查询语句的执行计划，避免全表扫描。同时，减少不必要的JOIN操作，优化子查询。
调整MySQL配置：根据服务器的硬件配置，调整MySQL的配置参数，如缓冲区大小、连接数等。例如，可以增加innodb_buffer_pool_size的大小以提高InnoDB存储引擎的性能。
定期维护和优化：定期进行数据库的维护和优化工作，如优化表（OPTIMIZE TABLE）、修复表（REPAIR TABLE）等操作。
使用适当的存储引擎：MySQL支持多种存储引擎，如InnoDB和MyISAM等。根据实际需求选择合适的存储引擎，例如，InnoDB支持事务处理和行级锁定，适用于需要高并发写入的场景。
读写分离：通过将读操作和写操作分散到不同的服务器上，可以减轻主服务器的负载，提高系统的整体性能。
使用缓存插件：例如Memcached或Redis等缓存系统可以缓存MySQL的查询结果，减少对数据库的访问次数。
考虑使用分区：对于大型表，可以考虑使用分区技术将数据分散到不同的物理存储设备上，提高查询性能和管理效率。
使用复制和分片：通过设置MySQL的主从复制和分片架构，可以实现数据的高可用性和扩展性，提高系统的整体性能和可靠性。

如何处理MySQL中的大数据量？

上文的基础上，采纳以下：
使用压缩技术：对数据库中的数据进行压缩可以减少磁盘空间的使用和网络传输的数据量，从而提高性能。MySQL提供了压缩功能，可以在创建表时使用COMPRESSED选项进行压缩。
考虑使用分布式数据库架构：分布式数据库将数据分散到多个节点上，每个节点负责一部分数据的存储和处理。通过扩展数据库的规模，可以提高系统的整体性能和可靠性。
数据归档和清理：定期清理和归档历史数据可以减小数据库的大小，提高查询性能。可以使用MySQL的归档功能或定期执行数据清理脚本进行数据归档和清理。

MySQL中有哪些常见的安全问题，如何避免这些问题？

MySQL中常见的安全问题包括：
未限制远程访问：默认情况下，MySQL允许任何IP地址的远程连接，这可能暴露数据库于潜在的攻击。应限制远程访问，只允许必要的IP地址或网络连接。
权限分配不当：赋予用户过多的权限或不恰当的权限可能会导致数据泄露或被篡改。应根据最小权限原则为用户分配权限。
未及时更新和打补丁：未及时更新和打补丁可能导致已知的安全漏洞被利用。应定期检查并应用MySQL的安全更新和补丁。
SQL注入：通过注入恶意的SQL代码，攻击者可以操纵数据库查询。应使用参数化查询或预编译语句来避免SQL注入攻击。
未加密存储的数据：存储在数据库中的敏感数据（如密码、个人信息等）未加密，可能导致数据泄露。应使用加密算法对敏感数据进行加密存储。
未限制错误信息显示：MySQL错误信息的显示可能会暴露数据库的敏感信息，如数据库结构、版本号等。应配置MySQL以隐藏敏感信息或自定义错误信息的显示。
不安全的配置设置：错误的配置设置可能会降低数据库的安全性。应仔细检查并配置MySQL的安全相关设置，如my.cnf或my.ini配置文件中的参数。
为了避免这些安全问题，建议采取以下措施：
【配置SSL/TLS加密通信通道，以保护数据传输的安全性。
限制远程访问，只允许必要的IP地址或网络连接。
根据最小权限原则为用户分配权限，避免赋予用户过多的权限或不恰当的权限。
定期检查并应用MySQL的安全更新和补丁，以修复已知的安全漏洞。
使用参数化查询或预编译语句来避免SQL注入攻击。
对敏感数据进行加密存储，使用加密算法对数据进行加密处理。
隐藏MySQL错误信息中的敏感信息，或自定义错误信息的显示。
仔细检查并配置MySQL的安全相关设置，确保配置参数的安全性。

你如何解决MySQL中的常见问题，例如慢查询、死锁等？

可以采用以下方法：
慢查询问题：
**开启慢查询日志：**首先，确保MySQL的慢查询日志已经开启。慢查询日志记录了执行时间超过指定阈值的查询。
EXPLAIN分析：使用EXPLAIN关键字分析慢查询语句的执行计划。这可以帮助您了解查询是如何执行的，并找出可能的性能瓶颈。
优化查询语句： 减少全表扫描，确保查询使用了适当的索引。避免在查询中使用复杂的子查询或联接操作。优化数据表结构，例如使用合适的数据类型和规范化。优化数据库配置：调整MySQL的配置参数，例如缓冲区大小、连接数等，以适应您的查询负载和硬件资源。
使用索引优化工具：可以考虑使用索引优化工具（如pt-index-usage）来分析查询模式并建议添加或修改索引。
定期维护：定期运行数据库维护任务，如优化表（OPTIMIZE TABLE）和修复表（REPAIR TABLE），以保持数据和索引的完整性。
死锁问题：
识别死锁：MySQL的错误日志中通常会记录死锁信息。也可以使用SHOW ENGINE INNODB STATUS命令来查看死锁的详细信息。
避免死锁：尽量减少事务中的锁定资源数量和时间。按照相同的顺序访问数据库对象，以减少锁争用。使用较低的事务隔离级别（如READ COMMITTED）可以减少死锁的可能性。
**检测和预防：**使用监控工具来定期检查数据库的健康状况和潜在的死锁情况。考虑使用死锁检测算法来自动检测和解决死锁情况。
重试策略：对于可能遭遇死锁的业务场景，实现一个重试策略来处理因死锁导致的失败操作。
分析死锁原因并采取措施：深入分析死锁的原因，检查是否有代码逻辑问题、索引不足或事务设计不当等问题，然后采取相应的优化措施。

你如何对MySQL数据库进行维护和监控？

备份数据库：定期备份MySQL数据库是至关重要的。可以使用mysqldump等工具进行备份，并确保备份文件存储在安全的位置。
监控性能指标：使用监控工具来收集和跟踪数据库的性能指标，如查询响应时间、连接数、磁盘I/O等。常见的监控工具包括MySQL Enterprise Monitor、Percona Monitoring and Management (PMM) 和Zabbix等。
优化查询性能：定期审查和分析慢查询日志，找出执行缓慢的查询并优化它们。使用EXPLAIN命令来理解查询的执行计划，并确保查询使用了适当的索引。
调整配置参数：根据数据库的工作负载和硬件资源，调整MySQL的配置参数，如缓冲区大小、连接数等。可以参考MySQL的性能优化指南来调整配置。
维护数据库表：定期运行OPTIMIZE TABLE命令来重新组织表的物理存储，提高查询性能。对于InnoDB存储引擎，可以使用ANALYZE TABLE命令更新表的统计信息。
检查和修复数据完整性：定期检查数据库的完整性，确保数据的一致性和正确性。可以使用CHECK TABLE命令来检查表的完整性，使用REPAIR TABLE命令修复损坏的表。
监视错误日志：关注MySQL的错误日志，以便及时发现潜在的问题或异常情况。
硬件和存储管理：确保数据库服务器拥有足够的硬件资源，如RAM、CPU和存储空间。监控硬件的性能指标，如磁盘I/O、CPU使用率等，以便及时发现瓶颈或问题。

在设计和优化数据库时，你通常会考虑哪些因素？

业务需求：理解业务需求和用例，确保数据库设计能够支持当前和未来的业务需求。识别关键业务实体和它们之间的关系，以构建合适的数据模型。
数据模型：选择合适的数据模型（如关系型、非关系型、图形、列式存储等），根据数据的性质和使用模式。定义数据表结构、字段、主键、外键等，确保数据的完整性和一致性。
性能： 考虑查询性能，为常用查询路径创建索引，但要避免过度索引导致性能下降。优化查询语句，减少不必要的JOIN操作、子查询和复杂的计算。评估数据库的I/O性能，包括磁盘读写速度、网络带宽等，并进行适当的调整。
可扩展性：设计数据库架构以支持水平扩展（增加更多服务器）或垂直扩展（增强单个服务器的能力）。使用分区、分片等技术来分散数据和负载，提高扩展性。
安全性：确保数据库实例的安全性，使用强密码、访问控制、网络隔离等手段。加密敏感数据，如用户密码、个人身份信息（PII）等。定期审计和监控数据库访问，检测潜在的安全威胁。
备份与恢复：设计并实施可靠的备份策略，包括全库备份和增量备份。定期测试备份恢复流程，确保在紧急情况下能够快速恢复数据
维护性： 简化数据库结构，避免过度复杂的设计，以便于未来的维护。编写清晰的文档，包括数据字典、ER图、存储过程逻辑等。监控数据库的健康状况，包括性能指标、错误日志、资源利用率等。
成本：评估硬件、软件、许可和维护的成本，确保数据库解决方案符合预算要求。
兼容性：确保数据库软件与应用程序开发语言、框架和工具兼容。考虑数据库迁移和升级路径，以减少未来技术变更的影响。
规范化与反规范化：根据需要进行数据库规范化，以减少数据冗余和提高数据一致性。在必要时考虑反规范化，以提高查询性能，但要权衡可能增加的数据冗余和维护复杂性。

mysql可重复读隔离级别下是否解决了幻读问题？

是的，MySQL的"可重复读"（REPEATABLE READ）隔离级别确实解决了"幻读"问题。
在数据库事务处理中，"幻读"是指在一个事务处理过程中，一个SQL语句查询出来的数据行数在多次执行时不一样，因为其他事务插入了新的数据行或者删除了某些数据行。
在"可重复读"隔离级别下，MySQL使用多版本并发控制（MVCC）来确保事务在开始时看到的数据快照在整个事务期间保持一致。这样，即使其他事务修改了数据，当前事务也不会受到影响，从而避免了幻读问题。

解释一下当前读和快照读？

当前读和快照读是数据库操作中的两种读取方式，它们在处理并发事务和数据一致性方面有所不同。
**当前读（Current Read）**是指在事务执行期间，读取已经提交但未被当前事务更新的数据。当前读能确保获取到最新的数据，因此在需要实时数据可见性和高一致性的应用场景中比较适用，如金融产品和库存系统。但需要注意，当前读可能导致脏数据出现，因为在读取数据的同时，其他事务可能已对该数据进行了修改。
快照读（Snapshot Read）则是指在事务执行期间，读取已经提交且符合当前事务隔离级别的数据的快照，而非实时数据。与当前读不同，快照读不会看到其他事务已经提交的更改，它提供的是某个时间点的数据快照。这种读取方式通常用于需要查看历史数据或生成报表的应用场景。在快照读中，事务读取过程中，只会读取版本号小于或等于自己事务版本号的数据行，以保证数据一致性。快照读有助于减少并发问题，但性能方面可能存在问题，因为在实现上需要进行版本控制，增加了一定的额外开销。
总结来说，当前读和快照读各有特点。当前读提供最新数据，适用于需要实时数据和高一致性的场景，但可能出现脏数据。快照读提供稳定数据，适用于需要历史数据和较高一致性的场景，但性能方面可能存在问题。**根据实际需求和应用场景选择合适的读取方式是关键。

解释一下间隙锁？

间隙锁（Gap Lock）是数据库中用于锁定索引范围的一种锁。它是InnoDB存储引擎在可重复读（REPEATABLE READ）隔离级别下为了解决幻读问题时引入的锁机制。
间隙锁的主要目的是防止其他事务在给定范围内插入新的数据，保证范围内数据的一致性和避免幻读现象。幻读指的是在一个事务中，两次相同的查询返回了不同数量的行，这可能是因为其他事务在此期间插入了新的数据。通过间隙锁，数据库确保在范围查询期间，其他事务无法在查询范围内插入新的数据行，从而保证了查询结果的一致性。
间隙锁锁定的是一个范围，而不仅仅是单个数据行或索引项。当执行范围查询（如SELECT … FOR UPDATE）或使用范围条件进行更新/删除操作时，如果命中索引条件，InnoDB会对满足条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，也会加锁，这个锁就是间隙锁。
需要注意的是，间隙锁可能会对并发性能产生一些影响，因为它会锁定索引范围而不是具体的数据行。因此，在设计数据库时需要权衡使用间隙锁对并发性能的影响以及数据的一致性需求。
此外，间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，也造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。因此，在使用间隙锁时需要注意其可能带来的性能问题，并根据实际情况进行合理的优化和调整。