MySql 常用面试题 2024最新面试题（三）

21. NOW()和CURRENT_DATE()的区别

NOW()和CURRENT_DATE()函数在功能和使用上存在明显的区别，主要如下：

1. 功能描述：

   • NOW()函数返回当前的日期和时间，其格式为"YYYY-MM-DD HH:MM:SS"，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。
   • CURRENT_DATE()函数只返回当前的日期，其格式为"YYYY-MM-DD"，不包含时间信息。

2. 返回值：

   • NOW()函数的返回值包含小时、分钟和秒数，因此可以显示更详细的时间信息。
   • CURRENT_DATE()函数的返回值只包含日期，不显示时间信息。

3. 使用场景：

   • 如果需要同时获取当前的日期和时间，应该使用NOW()函数。
   • 如果只需要获取当前的日期，不关心时间信息，那么CURRENT_DATE()函数是更好的选择。

总结来说，NOW()和CURRENT_DATE()函数的主要区别在于它们返回的信息类型不同：NOW()返回日期和时间，而CURRENT_DATE()只返回日期。因此，在选择使用哪个函数时，应根据具体的需求和场景来决定。

22. 非标准字符串类型

在MySQL中，除了常见的字符串类型如CHAR和VARCHAR外，还有一些非标准的字符串类型，它们用于存储特定长度或格式的文本数据。以下是一些非标准字符串类型的概述：

1. TEXT:

   • 用于存储较长的字符串。TEXT类型可以存储最多65,535个字符。
   • 与CHAR和VARCHAR不同，TEXT类型的数据通常存储在表外部的一个单独的位置，并且可能需要额外的磁盘I/O操作来访问。

2. TINYTEXT:

   • 类似于TEXT，但用于存储较短的文本数据。TINYTEXT可以存储最多255个字符。

3. MEDIUMTEXT:

   • 用于存储中等长度的文本数据。MEDIUMTEXT可以存储最多16,777,215个字符。

4. LONGTEXT:

   • 用于存储非常长的文本数据。LONGTEXT可以存储最多4,294,967,295个字符。

5. ENUM:

   • ENUM类型用于存储预定义的枚举值列表中的一个值。在创建表时，必须指定允许的枚举值列表。
   • ENUM类型在内部以整数形式存储，但对外显示为指定的字符串值。

6. SET:

   • SET类型与ENUM类似，但允许存储预定义值列表中的零个或多个值。
   • SET类型也是以整数形式在内部存储，但每个SET列可以有多个位被设置，表示多个选定的值。

7. BINARY 和 VARBINARY:

   • 这些类型用于存储二进制字符串，而不是字符字符串。它们与CHAR和VARCHAR类似，但存储的是二进制数据而不是字符数据。

这些非标准字符串类型提供了更多的灵活性，允许用户根据数据的大小和用途选择合适的类型。但是，它们也可能需要更多的存储空间或更复杂的处理，因此在选择使用哪种类型时需要权衡各种因素。

23. 通用SQL函数示例

在SQL中，函数用于处理数据并返回结果。这些函数可以是内置的，也可以是用户定义的。以下是一些通用的SQL函数示例：

1. 字符串函数

   • CONCAT(str1, str2, ...): 连接两个或多个字符串。
   • LENGTH(str): 返回字符串的长度。
   • UPPER(str) / LOWER(str): 将字符串转换为大写/小写。
   • TRIM(str): 去除字符串两端的空格。
   • SUBSTRING(str, pos, len): 从字符串中提取子串。

SELECT CONCAT('Hello', ' ', 'World');  -- 结果: 'Hello World'
SELECT LENGTH('Hello World');  -- 结果: 11
SELECT UPPER('Hello');  -- 结果: 'HELLO'
SELECT LOWER('HELLO');  -- 结果: 'hello'
SELECT TRIM('   Hello World   ');  -- 结果: 'Hello World'
SELECT SUBSTRING('Hello World', 1, 5);  -- 结果: 'Hello'

2. 数值函数

   • ROUND(number, decimals): 对数字进行四舍五入。
   • CEIL(number) / FLOOR(number): 返回大于或等于/小于或等于给定数字的最小整数。
   • ABS(number): 返回数字的绝对值。

SELECT ROUND(123.4567, 2);  -- 结果: 123.46
SELECT CEIL(123.4567);  -- 结果: 124
SELECT FLOOR(123.4567);  -- 结果: 123
SELECT ABS(-123);  -- 结果: 123

3. 日期和时间函数

   • NOW(): 返回当前日期和时间。
   • CURDATE(): 返回当前日期。
   • CURTIME(): 返回当前时间。
   • DATE_FORMAT(date, format): 按指定格式格式化日期。

SELECT NOW();  -- 返回当前日期和时间，例如: '2023-10-23 14:30:00'
SELECT CURDATE();  -- 返回当前日期，例如: '2023-10-23'
SELECT CURTIME();  -- 返回当前时间，例如: '14:30:00'
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');  -- 格式化日期和时间，结果类似于 '2023-10-23 14:30:00'

4. 聚合函数

   • COUNT(*): 计算表中的行数。
   • SUM(column): 计算某列的总和。
   • AVG(column): 计算某列的平均值。
   • MAX(column) / MIN(column): 返回某列的最大/最小值。

SELECT COUNT(*) FROM users;  -- 计算用户表中的行数
SELECT SUM(salary) FROM employees;  -- 计算员工薪水的总和
SELECT AVG(salary) FROM employees;  -- 计算员工薪水的平均值
SELECT MAX(salary), MIN(salary) FROM employees;  -- 返回员工薪水的最大值和最小值

5. 转换函数

   • CAST(value AS type) 或 CONVERT(value, type): 将值转换为指定的数据类型。

SELECT CAST('123' AS SIGNED);  -- 将字符串'123'转换为整数123
SELECT CONVERT('123.45', DECIMAL(5,2));  -- 将字符串'123.45'转换为DECIMAL类型，保留两位小数

6. 条件函数

   • CASE 语句: 根据条件返回不同的值。
   • IF(condition, value_if_true, value_if_false): 如果条件为真，则返回第二个参数的值，否则返回第三个参数的值。
   • COALESCE(value1, value2, ...): 返回参数列表中的第一个非NULL值。

SELECT 
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        ELSE 'C'
    END AS grade 
FROM students;

SELECT IF(score >= 60, 'Pass', 'Fail') AS result FROM students;

SELECT COALESCE(NULL, NULL, 'Third Value', 'Fourth Value');  -- 返回 'Third Value'

24. MySQL是否支持事务？

是的，MySQL支持事务。但需要注意的是，MySQL的事务支持能力取决于其存储引擎。在MySQL中，不同的存储引擎对事务的支持程度不同。

1. InnoDB：InnoDB是MySQL的默认存储引擎，并且它支持ACID事务、行级锁定和外键。因此，如果你需要事务支持，InnoDB是一个很好的选择。
2. MyISAM：与InnoDB不同，MyISAM不支持事务。它使用表级锁定，并且在崩溃恢复方面也不如InnoDB强大。
3. 其他存储引擎：除了InnoDB和MyISAM之外，MySQL还支持其他存储引擎，如Memory（或HEAP）、CSV、Federated、Archive等。这些存储引擎对事务的支持各不相同，但大多数都不如InnoDB全面。

在使用MySQL进行事务处理时，建议注意以下几点：

• 确保你的表使用的是支持事务的存储引擎（如InnoDB）。
• 在开始事务之前，使用START TRANSACTION语句或设置自动提交模式为关闭（SET autocommit=0）。
• 在事务中执行多个SQL语句，这些语句将作为一个整体提交或回滚。
• 如果需要，可以使用COMMIT语句提交事务，或使用ROLLBACK语句回滚事务。
• 在处理并发事务时，注意锁定机制以避免死锁和其他并发问题。

总之，虽然MySQL支持事务，但具体的事务处理能力取决于所使用的存储引擎。如果你需要事务支持，建议选择支持事务的存储引擎（如InnoDB）。

25. 记录货币用什么字段类型？

在MySQL中记录货币金额时，通常推荐使用DECIMAL或NUMERIC字段类型。这两种类型被MySQL实现为同样的类型，并且它们被设计用于存储那些准确精度极其重要的值，例如与金钱有关的数据。

以下是为什么DECIMAL或NUMERIC类型更适合用于记录货币的几个原因：

1. 精确性：DECIMAL和NUMERIC类型可以指定总共的位数和小数点后的位数，从而确保货币金额的精确表示。这与FLOAT和DOUBLE类型不同，后者是以二进制形式存储的，可能会引入舍入误差。
2. 可控性：通过指定精度和小数位数，你可以控制货币金额的表示方式，确保它符合你的业务需求。例如，你可以指定DECIMAL(10,2)来存储最多9位整数和2位小数的货币金额。
3. 避免精度损失：使用FLOAT或DOUBLE类型进行货币计算可能会导致精度损失，特别是在涉及大量计算或多次舍入的情况下。而DECIMAL和NUMERIC类型则不会出现这种问题。
4. 广泛支持：大多数数据库管理系统都支持DECIMAL或NUMERIC类型，并且它们的行为在不同系统之间是一致的。这使得在不同系统之间迁移数据或进行集成变得更加容易。

因此，在MySQL中记录货币金额时，建议使用DECIMAL或NUMERIC类型来确保数据的精确性和可控性。

26. 与权限相关的表

在MySQL中，与权限相关的表主要存储在mysql数据库中，这些表用于控制用户对数据库和表的访问权限。以下是几个主要的与权限相关的表：

1. user表：

   • 这是MySQL中最重要的权限表之一。它记录了允许连接到MySQL服务器的账号信息。表中的每个用户账号都有一行记录，包含了用户名、主机名、密码（尽管密码字段在较新版本的MySQL中已被更安全的认证方式如authentication_string替代）等信息。
   • 在user表中启用的所有权限都是全局级的，即这些权限适用于所有数据库。例如，如果一个用户在user表中被授予了DELETE权限，则该用户可以删除MySQL服务器上所有数据库中的任何记录。

2. db表：

   • 这个表列出了可以访问不同数据库的用户清单。表中的每条记录都由数据库名称、用户身份验证信息和相关的数据访问权限代码组成。通过db表，可以为特定用户指定对特定数据库的访问权限。

3. tables_priv表：

   • 该表用于对单个表进行权限设置。它记录了用户对特定表的访问权限，包括SELECT、INSERT、UPDATE、DELETE等操作权限。通过tables_priv表，可以实现对表中数据的细粒度控制。

4. columns_priv表：

   • 这个表用于对单个数据列进行权限设置。它允许管理员为用户指定对表中特定列的访问权限。例如，可以为用户设置只读取某个表的某一列的权限。

5. procs_priv表（或proc_priv表）：

   • 该表用于对存储过程和存储函数进行权限设置。它记录了用户对特定存储过程或存储函数的执行权限。通过procs_priv表，可以控制哪些用户可以执行哪些存储过程或函数。

请注意，对权限表的直接修改应谨慎进行，因为不正确的权限设置可能导致安全漏洞或数据损坏。通常建议使用GRANT和REVOKE语句来修改用户的权限，这些语句会自动更新相关的权限表。同时，为了确保权限更改生效，可能需要执行FLUSH PRIVILEGES命令来重新加载权限表。

27. 列的字符串类型

在MySQL中，列的字符串类型多种多样，每种类型都有其特定的用途和限制。以下是一些常用的字符串类型：

1. CHAR：

   • CHAR用于存储固定长度的字符序列。例如，CHAR(10)可以存储最多10个字符的字符串。如果存储的字符串长度小于定义的长度，MySQL会使用空格填充到指定长度。
   • CHAR类型适合存储长度固定的数据，如身份证号码、电话号码等。

2. VARCHAR：

   • VARCHAR用于存储可变长度的字符序列。例如，VARCHAR(10)可以存储最多10个字符的字符串，但如果实际数据只有5个字符，那么只有5个字符的空间会被使用。
   • VARCHAR类型适合存储长度不固定的数据，如姓名、地址等。VARCHAR类型比CHAR类型更节省空间。

3. TINYTEXT：

   • TINYTEXT用于存储最大长度为255个字符的字符串。

4. TEXT：

   • TEXT用于存储最大长度为65,535个字符的字符串。

5. MEDIUMTEXT：

   • MEDIUMTEXT用于存储最大长度为16,777,215个字符的字符串。

6. LONGTEXT：

   • LONGTEXT用于存储最大长度为4,294,967,295个字符的字符串。

7. ENUM：

   • ENUM是一个字符串对象，其值来自表创建时定义的允许值的列表。例如，ENUM(‘male’, ‘female’)只能存储’male’或’female’这两个值。

8. SET：

   • SET也是一个字符串对象，但和ENUM不同的是，SET列可包含零个或多个值，这些值来自表创建时定义的允许值的列表。例如，SET(‘red’, ‘green’, ‘blue’)可以存储如’red, green’这样的值。

在选择使用哪种字符串类型时，应根据数据的实际需求和特性来决定。例如，如果数据的长度是固定的，使用CHAR类型可能更为合适；如果数据的长度是可变的，使用VARCHAR类型可能更为合适。同时，如果需要存储大量的文本数据，可以使用TEXT、MEDIUMTEXT或LONGTEXT类型。

28. 如何优化高并发写入的MySQL数据库？

优化高并发写入的MySQL数据库是一个复杂的过程，涉及多个层面的策略和技术。以下是一些建议来优化高并发写入的MySQL数据库：

1. 选择合适的存储引擎：

   • 对于高并发写入场景，推荐使用InnoDB存储引擎，因为它支持行级锁定和MVCC（多版本并发控制），能够提供更好的性能和并发控制。

2. 使用合理的索引：

   • 索引可以加快查询速度，但在高并发写入场景下，过多的索引可能会影响写入性能。因此，需要仔细选择哪些列需要索引，并考虑使用覆盖索引来提高性能。
   • 避免创建不必要的索引，定期审查和优化现有索引。

3. 分离读写操作：

   • 通过数据库主从复制或使用分布式数据库架构，将读操作和写操作分离。将读操作分发到多个从节点上，减轻主节点的负载，提高并发处理能力。

4. 批量插入和更新：

   • 在高并发写入场景下，单条插入和更新操作的性能会较低。将多个操作合并成一个批量操作，可以减少磁盘I/O和网络开销，提高写入性能。

5. 优化表设计：

   • 避免使用过大的数据类型，例如使用MEDIUMINT代替INT。
   • 选择适当的主键，自增列通常是一个不错的选择。
   • 考虑使用分区技术来将数据分散到多个表中，减轻单个表的负载。

6. 调整事务隔离级别：

   • 根据实际需求选择适当的事务隔离级别。较低的事务隔离级别可以提高并发写入性能，但可能会导致数据不一致。

7. 使用缓存技术：

   • 使用内存数据库或分布式缓存来减轻数据库写入的压力。缓存预热和缓存穿透策略可以提高缓存的效率。

8. 使用消息队列：

   • 将写入操作转化为消息发送，然后由消费者异步处理。这可以降低写入请求对系统的影响，提高系统的吞吐量和可用性。

9. 异步处理：

   • 使用异步处理技术，如异步I/O、线程池等，来提高系统的并发处理能力。将写入操作放入线程池中执行，避免阻塞主线程。

10. 监控和分析：

    • 使用性能监控工具来分析数据库的写入性能瓶颈，并根据分析结果进行相应的优化。

请注意，每个数据库和应用场景都有其独特的需求和挑战，因此需要根据实际情况来定制优化策略。在实施任何优化措施之前，建议先在测试环境中进行验证和评估。

29. 锁的优化策略

在MySQL中，锁是用于管理并发访问数据库资源的重要机制。在高并发场景下，锁的争用和死锁等问题可能导致性能下降甚至系统崩溃。因此，对锁进行优化是提高数据库性能的关键之一。以下是一些常见的锁优化策略：

1. 选择合适的隔离级别：

   • 根据应用的实际需求选择合适的隔离级别。较低的事务隔离级别（如READ COMMITTED）通常具有更高的并发性能，但可能牺牲数据的一致性。较高的事务隔离级别（如SERIALIZABLE）则提供了更强的数据一致性保证，但并发性能较低。

2. 减少锁定的数据量：

   • 通过垂直拆分和水平拆分等方式将大表分解为多个小表，减少单个事务需要锁定的数据量。这有助于降低锁争用的可能性。
   • 使用索引来优化查询语句，减少全表扫描的范围，从而降低锁冲突的风险。

3. 合理使用事务：

   • 将多个更新语句放在一个事务中执行，减少事务的提交和回滚开销。但注意事务的大小，过大的事务可能增加锁冲突的可能性。
   • 尽量避免在事务中执行长时间的查询操作，以减少锁的持有时间。

4. 使用一致性非锁定读：

   • MySQL提供了多种一致性非锁定读的方式，如读已提交（READ COMMITTED）和可重复读（REPEATABLE READ）。这些方式可以在不加锁的情况下读取数据，降低锁争用的可能性。
   • 使用SELECT ... LOCK IN SHARE MODE等语句来实现共享锁，允许多个事务同时读取同一份数据。

5. 读写分离：

   • 将读操作和写操作分离到不同的数据库服务器上执行。主服务器负责写操作，从服务器负责读操作。这样可以减轻主服务器的负载，提高并发处理能力。

6. 调整锁等待超时时间：

   • 设置合理的锁等待超时时间，避免长时间等待锁导致的事务阻塞和死锁问题。当事务等待锁的时间超过一定阈值时，自动终止等待并回滚事务。

7. 监控和分析：

   • 使用性能监控工具来监控和分析锁的使用情况，找出性能瓶颈并进行相应的优化。例如，可以查看哪些查询导致了大量的锁争用，然后针对这些查询进行优化。

请注意，不同的应用场景和需求可能需要不同的锁优化策略。在实施任何优化措施之前，建议先进行充分的测试和分析，以确保优化效果符合预期并不会对现有系统造成负面影响。

30. 索引的底层实现原理

索引在MySQL中的底层实现原理主要依赖于特定的数据结构，最常见的是**B-Tree（平衡多路查找树）**或其变种B+Tree。以下是关于B+Tree索引实现原理的简要概述：

1. B+Tree数据结构：

   • B+Tree是一种自平衡的树，能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。与普通的二叉查找树不同，B+Tree的节点可以拥有多于两个的子节点，称为度（degree）。
   • 一个m阶的B+Tree满足以下条件：
     • 每个节点至多可以拥有m棵子树。
     • 根节点至少有两棵子树（除非树仅包含一个节点）。
     • 非根非叶节点至少有⌈m/2⌉棵子树（⌈⌉表示向上取整）。
     • 所有叶子节点位于同一层，并且不带信息（可以看作是外部节点或查询失败的节点，实际上这些节点也存在，但是节点不存储信息）。

2. 索引结构：

   • 在B+Tree中，非叶子节点不保存关键字信息，只用于索引，所有数据（关键字）都出现在叶子节点中。
   • 叶子节点之间通过指针相连，从而方便范围查找。

3. 查找效率：

   • 由于B+Tree的特性，查找、序列访问、插入和删除操作都可以在对数时间内完成。
   • 例如，一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2)，即查找节点个数的渐进复杂度为O(logdN)。

4. MySQL中的B+Tree索引：

   • 在MySQL的InnoDB存储引擎中，主键索引采用聚集索引（Clustered Index）的方式实现，即表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点包含了完整的数据记录。
   • 非主键索引则采用非聚集索引（Secondary Index或Non-clustered Index）的方式实现，叶节点不包含行数据的全部信息，而是保存了行的主键值作为指向表中对应行的指针。

5. 优化考虑：

   • 在设计索引时，需要考虑到索引的选择性（即不重复的索引值与数据表记录总数的比值）、查询条件、写入性能等因素，以达到最佳的查询性能和数据更新性能平衡。
   • 过多的索引会导致写操作的性能下降，因为每次数据更新时都需要更新相关的索引。因此，需要根据实际情况选择合适的索引列和索引类型。

31. 索引无法使用的情况

MySQL中的索引在某些情况下可能无法被有效利用，导致查询性能下降。以下是一些常见的情况，其中索引可能无法正常工作：

1. 数据类型不匹配：如果查询中使用了索引列，但数据类型不匹配，MySQL将无法有效使用索引。例如，将字符串类型的列与数值类型进行比较，或将日期类型的列与文本进行比较。在设计表结构时，应确保索引列和查询的数据类型匹配。
2. 前缀索引使用不当：为了提高索引的效率和节省存储空间，有时会使用前缀索引。然而，如果使用前缀索引的长度过短，查询的结果可能会不准确。此外，如果使用前缀索引的列进行排序或分组操作，也会导致索引失效。
3. 在索引列上使用函数或表达式：在查询中对索引列进行数学运算（如除法、乘法等）或应用函数（如TO_CHAR(), UPPER(), LOWER()等），可能导致索引失效。因为索引存储的是原始数据值，而非经过计算或函数处理后的值。但请注意，以固定字符串开头的模糊查询（如’abc%'）有时可以利用索引前缀。
4. 索引列的顺序不正确：复合索引是指包含多个列的索引。如果查询的条件中的列的顺序与复合索引的列的顺序不一致，MySQL可能无法使用索引进行优化查询。例如，如果复合索引是(col1, col2)，但查询条件只涉及col2，那么索引可能不会被使用。
5. 全表扫描更高效：在某些情况下，DBMS发现全表扫描的效率更高，因此即使存在索引，也可能选择不使用索引。这通常发生在统计表中所有行的数量时（如使用SELECT COUNT(*) FROM table;），因为索引不能直接提供行计数。
6. LIKE语句以通配符%开头：当LIKE语句以通配符%开头时（如LIKE ‘%abc’），MySQL通常无法利用索引进行优化查询，因为它需要从第一个字符开始匹配。然而，如果通配符%不在开头（如LIKE ‘abc%’），有时可以利用索引前缀进行优化。
7. 使用OR连接的条件：在某些情况下，当使用OR连接多个条件时，如果OR前后的条件没有同时使用索引，MySQL可能无法有效利用索引。
8. 范围查询后的列：在复合索引中，如果查询条件包含范围查询（如BETWEEN、<、>等），那么范围条件右边的所有列索引都会失效。
9. IS NOT NULL条件：虽然IS NULL条件可以使用索引，但IS NOT NULL条件在某些情况下可能无法使用索引，具体取决于数据库的实现和查询优化器的决策。

请注意，以上情况并非绝对，具体是否使用索引还取决于MySQL查询优化器的决策和数据库的配置。为了获得最佳性能，建议仔细分析查询和表结构，并考虑使用EXPLAIN命令来查看查询的执行计划，以确定索引是否被有效利用。