1. MySQL 8.0 中，为什么查询缓存被移除？
-
答案：
-
原因：查询缓存对频繁更新的表效果差，任何对该表的写操作都会清空所有相关缓存，导致缓存命中率低，反而增加开销。
-
替代方案：
- 使用应用层缓存（如 Redis）。
- 优化查询和索引，减少对缓存的依赖。
-
MySQL 8.0 改进：通过索引优化、并行查询等提升性能，弥补查询缓存缺失的影响。
-
2. InnoDB 的行锁和表锁分别在什么场景下使用？
-
答案：
- 行锁：高并发场景下更新或查询单行数据（如
UPDATE users SET score=100 WHERE id=1）。 - 表锁：低并发场景或批量操作（如
LOCK TABLES ... READ/WRITE）。 - 锁升级：当行锁数量过多时，InnoDB 可能升级为表锁（通过
innodb_lockescalation控制）。
- 行锁：高并发场景下更新或查询单行数据（如
3. MySQL 8.0 的窗口函数与用户自定义变量实现的滚动求和有何区别？
-
答案：
-
窗口函数：
- 语法简洁（如
SUM(column) OVER (PARTITION BY ...))，无需复杂子查询。 - 性能更优，基于 SQL 标准，代码可读性高。
- 语法简洁（如
-
自定义变量：
- 需手动维护变量，易出错（如
@sum := @sum + column）。 - 不支持复杂分区和排序，且在并行查询中可能失效。
- 需手动维护变量，易出错（如
-
4. 如何解决 UPDATE 语句执行慢的问题？
-
答案：
-
优化点：
- 添加索引（如
WHERE和JOIN条件字段）。 - 减少更新范围（如分批次更新）。
- 使用
LIMIT防止全表锁。
- 添加索引（如
-
工具：
EXPLAIN分析执行计划。pt-online-schema-change实现在线更新。
-
5. MySQL 8.0 中，SHOW ENGINE INNODB STATUS 的 TRANSACTIONS 部分能提供哪些关键信息？
-
答案：
- 事务状态：当前活跃事务、锁等待情况。
- 死锁信息：发生死锁时的事务 ID、锁资源及 SQL 语句。
- 事务日志：Redo Log 和 Undo Log 的状态，帮助排查事务阻塞。
6. 如何设计高可用的 MySQL 8.0 集群？
-
答案：
-
方案：
- Group Replication：MySQL 原生的多主复制集群，支持自动故障转移。
- ProxySQL + Keepalived：结合负载均衡和主从切换。
-
关键配置：
- 启用
GTID（全局事务标识符）确保数据一致性。 - 设置
super_read_only防止从库误写。
- 启用
-
7. 如何备份和恢复 MySQL 8.0 的数据？
-
答案：
-
逻辑备份：
mysqldump全量或增量备份（结合--single-transaction）。
-
物理备份：
xtrabackup热备份（无需锁表）。
-
恢复策略：
- 使用
binlog进行时间点恢复（POINT_IN_TIME）。 - 集群恢复需同步
GTID。
- 使用
-
8. MySQL 8.0 的 Caching SHA2 Password 认证插件有何优势？
-
答案：
- 安全性：支持 SHA-256 加密，比
mysql_native_password更安全。 - 兼容性：需确保客户端支持（如 MySQL 8.0+ 或配置
allowPublicKeyRetrieval=true）。 - 默认配置：MySQL 8.0 默认启用，可通过
ALTER USER改回旧插件。
- 安全性：支持 SHA-256 加密，比
9. 如何优化大表的 DELETE 操作？
-
答案：
-
分批删除：
DELETE FROM table WHERE id BETWEEN 1 AND 10000; -- 循环分批 -
索引优化：在
WHERE条件字段上建立索引。 -
锁控制：使用
ROW_COUNT或LIMIT避免长时间锁表。
-
10. MySQL 的 EXPLAIN 中 type=range 和 type=index 有何区别？
-
答案：
range：基于范围的索引扫描（如WHERE id > 100）。index：全索引扫描（未命中条件，需回表查询）。- 优化建议：确保查询条件能命中索引范围。
11. 如何实现 MySQL 的读写分离？
-
答案：
-
方案：
- ProxySQL：动态路由读写请求。
- MySQL Router：结合 Group Replication 的只读节点。
-
注意事项：
- 主从延迟可能导致读到旧数据。
- 使用
READ_ONLY模式强制从库只读。
-
12. MySQL 的 JSON_TABLE 函数如何将 JSON 转换为关系表？
-
答案：
SELECT * FROM JSON_TABLE('[{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}]','$[*]' COLUMNS(name VARCHAR(20) PATH '$.name',age INT PATH '$.age') ) AS jt;- 作用：将 JSON 数组转换为行和列，便于 SQL 查询。
13. 如何解决 SELECT FOR UPDATE 的死锁问题？
-
答案：
-
策略：
- 按固定顺序加锁（如先锁主键，再锁外键）。
- 减少事务持有锁的时间。
-
检测与处理：
- 使用
SHOW ENGINE INNODB STATUS定位死锁。 - 设置
innodb_lock_wait_timeout自动回滚。
- 使用
-
14. MySQL 的 PARTITION 如何优化大表查询？
-
答案：
-
分区策略：按范围（
RANGE）、哈希（HASH）或列表（LIST）分区。 -
优势：
- 减少扫描数据量（如
WHERE year=2023直接定位分区）。 - 分区独立维护（如单独备份或删除旧分区）。
- 减少扫描数据量（如
-
注意：分区键需与查询条件匹配。
-
15. 如何监控 MySQL 的慢查询？
-
答案：
-
配置：
slow_query_log = ON long_query_time = 1 log_output = FILE -
分析工具：
mysqldumpslow统计慢查询。pt-query-digest可视化分析。
-
16. MySQL 的 TRUNCATE 和 DELETE 的区别是什么？
-
答案：
-
TRUNCATE：- 重置表结构，速度快，不记录行级日志。
- 无法回滚，不触发触发器。
-
DELETE：- 行级删除，可回滚，记录日志。
- 需要满足
WHERE条件。
-
17. 如何实现 MySQL 的跨库分页查询？
-
答案：
-
优化方法：
- 覆盖索引：确保查询字段在索引中（避免回表）。
- 分页优化：使用
WHERE id > last_id LIMIT 10替代LIMIT 1000000。
-
工具：
SQL_CALC_FOUND_ROWS统计总行数（需谨慎使用）。
-
18. MySQL 的 FUNCTIONAL 索引如何加速 JSON 查询？
-
答案：
CREATE INDEX idx_json ON table (JSON_EXTRACT(json_col, '$.name'));- 作用：将 JSON 路径值存储为索引，加速
WHERE json_col->>'$.name' = 'Alice'的查询。
- 作用：将 JSON 路径值存储为索引，加速
19. 如何设计高并发场景下的计数器？
-
答案：
-
方案：
- 使用
AUTO_INCREMENT主键自增。 - 分布式场景用
Redis缓存计数，定期同步到 MySQL。
- 使用
-
避免锁竞争：
- 减少事务粒度，使用
NOWAIT或SKIP LOCKED。
- 减少事务粒度，使用
-
20. MySQL 8.0 的 GROUPING SETS 如何实现多维聚合？
-
答案：
SELECT category, SUM(sales), GROUPING(category) FROM sales GROUP BY GROUPING SETS ( (category), () );- 作用：生成多个分组结果（如按
category和总和同时返回）。
- 作用：生成多个分组结果（如按
总结：高频考点与技巧
- 核心知识点：锁机制、事务隔离、分区表、JSON 函数、高可用架构。
- 优化方向：索引设计、慢查询分析、分布式事务、集群配置。
- 陷阱规避：避免全表锁、合理使用
GROUP BY、注意VARCHAR的存储开销。
借助DBLens for MySQL数据库工具，文章涉及的SQL语句得以高效执行与管理。
