MYSQL 面试题

间隙锁 (Gap Lock) 详解

间隙锁（Gap Lock） 是数据库中的一种锁机制，主要用于解决幻读（Phantom Read）问题，特别是在 InnoDB 存储引擎的可重复读（REPEATABLE READ）隔离级别下。

🧠 基本概念

间隙锁锁定的是索引记录之间的“间隙”，而不是记录本身。它锁定的是一个范围（Range），不包括已有的记录本身。

举例说明：

如果某表中已有 id 值为 10、20、30 的记录，则间隙锁可能锁定以下区间：

  • (-∞, 10)
  • (10, 20)
  • (20, 30)
  • (30, +∞)

🔑 主要特点

  • 防止幻读：防止其他事务在锁定的间隙内插入新记录。
  • 范围锁定：锁定的是索引区间，而非具体记录。
  • 共享性质：多个事务可以同时持有相同的间隙锁（共享锁）。
  • 隔离级别依赖：仅在可重复读（REPEATABLE READ）隔离级别下启用，读已提交（READ COMMITTED）不使用间隙锁。

🧩 工作场景

间隙锁通常出现在以下类型的 SQL 操作中（前提是使用了索引）：

  • SELECT ... FOR UPDATE
  • SELECT ... LOCK IN SHARE MODE
  • UPDATE 或 DELETE 语句使用索引条件时

💡 示例

假设表 t 的 id 列为主键，已有记录：1, 5, 10。

-- 事务A
BEGIN;
SELECT * FROM t WHERE id BETWEEN 5 AND 10 FOR UPDATE;
-- 此时会锁定 (5,10) 间隙 和 (10,+∞) 间隙

-- 事务B 尝试插入
INSERT INTO t VALUES(7);   -- ❌ 被阻塞（在间隙 (5,10) 内）
INSERT INTO t VALUES(12);  -- ❌ 被阻塞（在间隙 (10,+∞) 内）

MySQL有哪些存储引擎？各自的特点是什么？

主要存储引擎：

  • InnoDB：支持事务、行级锁、外键，MySQL 5.5后默认引擎
  • MyISAM：不支持事务和行级锁，但查询速度快
  • MEMORY：数据存储在内存中，速度快但不持久
  • ARCHIVE：适合存储归档数据，压缩率高- CSV：数据以CSV格式存储
  • BLACKHOLE：接收数据但不存储，用于复制和日志

5. 什么是事务？MySQL如何支持事务？

事务是数据库操作的一个逻辑单元，包含一组操作，要么全部执行成功，要么全部失败回滚。

MySQL通过以下方式支持事务：

  • 使用支持事务的存储引擎如InnoDB

  • 提供START TRANSACTION/BEGIN、COMMIT、ROLLBACK语句

  • 实现ACID特性保证数据一致性

  • 提供事务隔离级别控制并发访问

ENUM类型的优缺点

优点：

  • 存储紧凑：ENUM值在内部存储为整数，只占用1-2字节

  • 可读性好：存储的是字符串值而非数字代码

  • 数据完整性：限制字段只能取预定义的值

  • 排序优化：按定义的顺序排序而非字母顺序

缺点：

  • 修改代价高：添加或删除值需要ALTER TABLE

  • 移植性差：不是所有数据库都支持ENUM

  • 排序依赖定义顺序：可能不符合预期

  • 有限的值数量：最多65,535个不同的枚举值

MySQL 5.7 的主要新特性

1. 原生 JSON 数据类型支持

  • 引入 JSON 类型字段，支持文档存储

  • 支持 JSON 函数：

    • JSON_EXTRACT(), JSON_SET(), JSON_CONTAINS(), JSON_ARRAYAGG() 等

  • 支持生成列（Generated Columns）基于 JSON 建索引

CREATE TABLE user_data (
  id INT,
  info JSON,
  INDEX idx_name ((JSON_UNQUOTE(info->'$.name')))
);

2. 性能提升

  • 查询优化器增强（更好地选择索引/执行计划）
  • 默认启用 innodb_file_per_table
  • 新增性能模式变量：如 innodb_buffer_pool_dump_at_shutdown
  • 更高效的分组与排序逻辑
  • DDL 支持在线操作更多场景（ALTER TABLE 不锁表）

3. 复制增强（Replication）

  • 引入 GTID（全局事务 ID）更强的主从复制管理
  • 多源复制（Multi-Source Replication）：一个从库支持多个主库数据同步
  • 半同步复制增强
  • 支持复制过滤规则更细化（按通配符库名/表名）

4. 安全性增强

  • 默认用户 root 安全性更强，强制密码
  • 密码过期策略支持
  • 更细粒度的权限控制（如对 SELECT 加密列的控制）
  • 支持 SSL 连接更加完善

5. GIS（地理信息系统）功能增强

  • 支持标准 OpenGIS 类型（POINT, POLYGON 等）
  • 空间索引增强，支持 InnoDB 引擎
  • 支持更多空间函数（如 ST_Contains(), ST_Distance()）

6. 信息与性能监控增强

  • 全新的 Performance Schema 监控系统更丰富，低开销
  • 支持监控线程、语句、锁、内存、文件 IO 等细节
  • 增强 SHOW PROFILE, INFORMATION_SCHEMA 视图

7. 错误日志与诊断增强

  • 支持错误日志插件（错误输出格式更标准）
  • 引入 sys 库（MySQL 内置性能诊断库）
SELECT * FROM sys.user_summary;

8. InnoDB 引擎改进

  • 支持全文索引（Fulltext Index）
  • 支持在线创建/删除索引
  • 启用 InnoDB 空间自动回收（innodb_page_cleaners）

9. 增强的默认设置

  • 默认启用 STRICT MODE
  • 默认使用 utf8mb4 字符集（兼容 Emoji）
  • 默认开启 innodb_file_per_table

10. 系统表重构

  • mysql.user 表结构变化，权限字段更细化
  • 增强安全性和可管理性

1. 如何诊断和解决 MySQL 连接问题？

诊断方法：

  • 查看错误信息（如 Can't connect to MySQL server）
  • 使用 telnet / nc 确认端口开放：telnet 127.0.0.1 3306
  • 检查 MySQL 服务是否启动：systemctl status mysqld
  • 查看 MySQL 错误日志：/var/log/mysqld.log
  • 检查防火墙/网络配置

解决方法：

  • 确认 my.cnf 中 bind-address 设置正确（如 0.0.0.0）
  • 用户权限设置正确（如 user@'%'）
  • 增加最大连接数：max_connections
  • 使用 netstat 检查端口占用

2. 如何处理表损坏问题？

检查方法：

  • 错误信息如：Table is marked as crashed
  • 使用命令：CHECK TABLE tbl_name

解决方法：

  • REPAIR TABLE tbl_name（MyISAM）
  • 删除 .MYI/.MYD 文件后恢复（如有备份）
  • InnoDB 表建议使用 ibd 恢复工具或从备份恢复

3. 如何恢复误删除的数据？

恢复方法：

  • binlog 恢复：

    • 使用 mysqlbinlog 工具恢复
    • 示例：mysqlbinlog --start-datetime="..." binlog.000001 > restore.sql

  • 备份恢复：

    • 全量 + 增量备份
    • 使用 Percona XtraBackup 或 mysqldump

  • 延迟从库：

    • 设置 replica_delay 回滚误操作

4. 如何解决主从复制不一致问题？

原因：

  • 非确定性函数（如 UUID(), NOW()）
  • 手动变更从库数据
  • binlog 格式为 statement

解决方法：

  • 使用 ROW 格式 binlog：binlog_format=ROW
  • 开启 read_only 防止手动操作从库
  • 使用 pt-table-checksum 检查差异
  • 使用 pt-table-sync 进行修复
  • 查看 SHOW SLAVE STATUS\G 获取错误信息

5. 如何处理死锁问题？

诊断：

  • 查看死锁日志：SHOW ENGINE INNODB STATUS

解决方法：

  • 减少事务范围，及时提交
  • 保证一致的加锁顺序
  • 拆分大事务，避免批量更新锁冲突

6. 如何诊断 MySQL 内存泄漏？

诊断工具：

  • valgrind 分析内存分配
  • 查看内存占用：ps aux | grep mysqld
  • 使用 Performance Schema 查询 memory usage

解决方法：

  • 避免使用 debug 版本
  • 定期重启
  • 升级修复已知漏洞的版本

7. 如何解决 Too many connections 错误？

原因：

  • 活跃连接超出 max_connections

解决方法：

  • 提升 max_connections
  • SHOW PROCESSLIST 排查连接来源
  • 使用连接池，设置超时 wait_timeout
  • 设置 max_user_connections 限制用户连接数

8. 如何分析并解决长事务问题？

诊断：

  • SELECT * FROM information_schema.innodb_trx;

影响：

  • 占用锁和 undo log，阻塞 GC

解决方法：

  • 缩小事务粒度
  • 强制中断：KILL <thread_id>
  • 拆分批量事务

9. 如何应对 MySQL 的 OOM（内存溢出）问题？

诊断：

  • 查看系统日志或使用 dmesg

原因：

  • 参数设置不当如 innodb_buffer_pool_size
  • 查询过大或连接过多

解决方法：

  • 优化配置，限制查询
  • 限制连接数
  • 使用性能监控工具识别热点

10. 如何制定 MySQL 的灾难恢复计划？

要点：

  • 备份机制：mysqldump、XtraBackup
  • 备份策略：定期、异地、多级校验
  • 恢复演练：定期测试恢复能力
  • 高可用架构：MHA、Orchestrator
  • 延迟从库：防误删
  • 标准文档：定义 SOP，提升恢复效率
posted @ 2025-05-11 09:22  不报异常的空指针  阅读(18)  评论(0)    收藏  举报