mysql 面试题

📘 MySQL 面试题（200 道）

一、MySQL 基础（40 题）

  1. MySQL 常见的存储引擎有哪些？区别是什么？
  2. InnoDB 和 MyISAM 的区别？
  3. MySQL 中事务的四大特性 (ACID) 是什么？
  4. 什么是脏读、不可重复读、幻读？
  5. MySQL 支持哪些事务隔离级别？
  6. 默认的隔离级别是什么？
  7. char 与 varchar 的区别？
  8. varchar(100) 和 varchar(200) 有什么性能差异？
  9. decimal 和 float/double 的区别？
  10. datetime 和 timestamp 的区别？
  11. 主键、唯一键和索引的区别？
  12. 索引的分类有哪些？
  13. 什么是聚簇索引和非聚簇索引？
  14. 主键索引和唯一索引有什么不同？
  15. 联合索引的最左前缀原则是什么？
  16. 什么是覆盖索引？
  17. 索引下推 (Index Condition Pushdown, ICP) 是什么？
  18. SQL 优化时如何选择合适的索引？
  19. explain 中的 key、rows、extra 分别表示什么？
  20. explain 的 type 有哪些类型？（ALL, index, range, ref, eq_ref, const 等）
  21. SQL 执行顺序？
  22. where、having、on 的区别？
  23. inner join、left join、right join、full join 区别？
  24. union 和 union all 区别？
  25. exists 和 in 的区别？
  26. delete、truncate、drop 区别？
  27. order by 和 group by 的区别？
  28. limit 在大数据量时如何优化？
  29. select count(*) 和 count(1) 的区别？
  30. null 和 ‘’ 的区别？

31. MySQL 的锁有哪些？

第一类是全局锁。它作用于整个数据库实例。最典型的命令是 FLUSH TABLES WITH READ LOCK (FTWRL)。执行后，整个库都会变成只读状态，所有数据更新语句（DML）和结构变更语句（DDL）都会被阻塞。它的应用场景比较特定，比如在做全库逻辑备份时，为了保证备份期间数据的一致性，可以加上全局锁。但因为它影响范围太大，对业务冲击很严重，所以现在更推荐使用 mysqldump 的 --single-transaction 参数，通过开启一个一致性快照来避免加全局锁。
第二类是表级锁。它作用于整张表。我们可以通过 LOCK TABLES tbl_name READ/WRITE 命令手动加表锁。像 MyISAM 这样的老式存储引擎，默认就使用表锁。InnoDB 虽然主打行锁，但在执行一些 DDL 操作，比如 ALTER TABLE 时，也会涉及到表级锁。表锁的优点是实现简单，开销小，不会死锁；但缺点也很明显，就是粒度太粗，并发能力差。例如，一个线程在写这张表时，其他所有线程都无法读或写这张表，很容易成为性能瓶颈。
第三类，也是最重要的一类，是行级锁。这是 InnoDB 引擎的核心竞争力之一。它只锁定需要操作的特定数据行，极大地提高了并发访问的效率。行级锁又可以根据其模式分为两种：
共享锁 (S Lock / 读锁)：当一个事务需要读取一行数据并希望防止其他事务修改它时，可以使用 SELECT ... LOCK IN SHARE MODE 来加共享锁。多个事务可以同时对同一行加共享锁（读-读兼容），但如果另一个事务想加排他锁（写），就会被阻塞。
排他锁 (X Lock / 写锁)：当一个事务要修改数据时，会自动加上排他锁。我们也可以用 SELECT ... FOR UPDATE 显式地为读取的数据加上排他锁。排他锁是独占的，一旦某个事务获取了某行的排他锁，其他任何事务都不能再对该行加任何类型的锁（读-写、写-写互斥）。
此外，InnoDB 为了实现更高的隔离级别（如可重复读），还引入了更复杂的锁算法，比如间隙锁 (Gap Lock) 和 临键锁 (Next-Key Lock)。它们不仅能锁住记录本身，还能锁住记录之间的“空隙”，从而有效防止幻读现象的发生。例如，在执行 UPDATE 或 DELETE 语句时，如果 WHERE 条件命中了索引，InnoDB 就会在相关的索引记录上自动加上相应的行级锁（通常是临键锁）。而 INSERT 操作则可能会触发一种特殊的锁——插入意向锁，也是一种间隙锁，用于协调不同事务的插入操作。”
关于如何加锁，总结一下：
全局锁：通过 FLUSH TABLES WITH READ LOCK 手动添加。
表级锁：通过 LOCK TABLES 命令手动添加，或者由某些 DDL 语句隐式触发。
行级锁：
共享锁：显式使用 SELECT ... LOCK IN SHARE MODE。
排他锁：显式使用 SELECT ... FOR UPDATE，或者由 INSERT, UPDATE, DELETE 等 DML 语句在执行时自动添加。
特别需要注意的是，行级锁是基于索引实现的。如果 SQL 语句没有使用到索引，InnoDB 可能会对聚簇索引的所有记录进行扫描并加锁，这实际上会导致锁住整张表，造成严重的性能问题，这就是所谓的‘锁表’现象，我们在开发中必须避免。”
“总的来说，理解这些锁机制，特别是 InnoDB 的行级锁，对于写出高性能、高并发的 SQL 语句至关重要。我们在实践中应该尽量利用索引，减少锁的范围，并避免长事务，以降低死锁的风险。”

加分项（如果面试官追问或你想展示深度）

提到意向锁：“另外，为了高效地管理行锁和表锁的关系，InnoDB 还引入了意向锁（Intention Lock），比如意向共享锁(IS)和意向排他锁(IX)。它是一种表级锁，用来表明一个事务接下来要在表中的某些行上加 S 锁或 X 锁。这样，当另一个事务想对整张表加锁时，只需要检查表上的意向锁，而不需要遍历所有行的锁状态，大大提高了效率。”
对比隔离级别：“不同的事务隔离级别会影响锁的行为。比如在 READ COMMITTED 级别下，通常不会使用间隙锁，而在 REPEATABLE READ 级别下，InnoDB 会使用临键锁来防止幻读。”
实战经验：“在我们之前的项目中，就遇到过因为一个查询没有走索引，导致 UPDATE 语句锁住了整个大表，引发大量超时的问题。后来通过优化 SQL 和添加合适的索引解决了这个问题。”
32. 表级锁和行级锁的区别？
33. InnoDB 的行锁实现机制？
34. 什么是间隙锁？
35. 什么是死锁？如何排查？
36. show processlist 命令作用？
37. information_schema 数据库的用途？
38. performance_schema 数据库的用途？
39. mysql 和 mysqld 的区别？
40. mysql 的配置文件 my.cnf 常见配置项有哪些？

41. 你如何优化一条慢 SQL？

优化方向 方法
索引优化 建立合适的索引（单列、联合索引、覆盖索引）；删除无用索引。
查询优化 减少返回字段（SELECT * → 指定列）；减少不必要的排序与子查询。
条件优化 避免在 WHERE 中使用函数、隐式类型转换；用索引列做过滤条件。
表设计优化 拆分大表、冷热数据分离；归档历史数据。
分页优化 LIMIT offset, size 优化为“基于上次 ID 继续查”（索引翻页）。
锁优化 事务尽快提交；减少范围更新；避免热点行。
缓存优化 使用 Redis 缓存热点查询结果。
统计优化 手动刷新表统计信息：ANALYZE TABLE your_table;。

42. 什么是慢查询日志？如何开启？

临时开启

-- 开启慢查询日志 ANALYZE
SET GLOBAL slow_query_log = 1;

-- 设置慢查询阈值（单位：秒）
SET GLOBAL long_query_time = 1;

-- 查看日志存放路径
SHOW VARIABLES LIKE 'slow_query_log_file';

永久开启 my.cnf

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

查看是否开启

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
  1. 如何定位 SQL 性能瓶颈？
  2. explain 中 possible_keys 和 key 的区别？

45. explain 的extra 字段

Extra 值 含义 / 作用 说明 示例 SQL
Using index 索引覆盖查询 查询只用索引即可，不回表访问数据行（覆盖索引） SELECT id, name FROM user WHERE id = 10;（id 和 name 都在索引中）
Using where 过滤条件 MySQL 在访问表/索引之后，对结果进行额外的 WHERE 过滤 SELECT * FROM user WHERE age > 20;（索引无法完全覆盖 age 条件）
Using temporary 使用临时表 查询需要创建临时表，例如排序（ORDER BY）或分组（GROUP BY） SELECT dept, COUNT(*) FROM employee GROUP BY dept;
Using filesort 使用文件排序 MySQL 没有通过索引排序，而是额外做排序（可能在内存或磁盘上） SELECT * FROM employee ORDER BY salary DESC;（salary 没有索引）
Using index condition 索引条件下推（ICP） InnoDB 在索引扫描阶段完成部分 WHERE 条件，提高效率 SELECT * FROM orders WHERE user_id = 10 AND amount > 100;（user_id 用索引，amount 在 ICP 下过滤）
Using join buffer 使用连接缓存 在某些联接类型（如 Block Nested Loop Join）下，需要临时缓冲行 SELECT * FROM a JOIN b ON a.id = b.a_id;（b.a_id 没有索引）
Impossible WHERE 条件恒假 WHERE 条件无法满足，查询直接返回空结果 SELECT * FROM user WHERE 1 = 0;
Distinct 去重 查询需要去重，例如 SELECT DISTINCT SELECT DISTINCT dept FROM employee;
Not exists / Not exists subquery 子查询优化信息 对应子查询的优化策略信息 SELECT * FROM user u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
Range checked for each record (key map: …) 范围检查 MySQL 对每行检查索引范围，通常在多列索引或子查询场景出现 SELECT * FROM orders WHERE user_id = 10 AND order_date BETWEEN '2025-10-01' AND '2025-10-31';
Using MRR 多范围读取优化（Multi-Range Read） 用于 InnoDB 批量按主键范围读取，减少随机 I/O SELECT * FROM orders WHERE order_id IN (101, 203, 305, 407);
Using join buffer (Block Nested Loop) Block Nested Loop Join 对应非索引联接，MySQL 使用 join buffer 提高效率 SELECT * FROM a JOIN b ON a.col1 = b.col2;（b.col2 没有索引）
Using temporary; Using filesort 同时使用临时表和文件排序 常见于复杂 GROUP BY + ORDER BY 查询 SELECT dept, COUNT(*) FROM employee GROUP BY dept ORDER BY COUNT(*) DESC;
  1. limit 分页优化思路？
  2. 索引失效的常见场景？
  3. like ‘%abc’ 能走索引吗？如何优化？
  4. 前缀索引是什么？
  5. 索引选择性是什么？
  6. 索引覆盖率过低会怎样？
  7. 单表 1000w 行，如何快速 count？
  8. distinct 和 group by 的区别？
  9. order by 多列时索引如何生效？
  10. group by 和 having 性能优化技巧？
  11. join 优化方法有哪些？
  12. hash join 和 nested loop join 区别？
  13. 临时表和派生表的区别？
  14. union 查询优化方法？
  15. exists 和 in 哪个性能更好？
  16. 什么时候适合用子查询，什么时候适合用 join？
  17. update 大量数据如何避免锁表？

64. delete 大量数据如何优化？

你的回答已经抓住了 大规模 DELETE 的核心痛点，尤其是对 Buffer Pool 冲击、I/O 消耗、间隙锁、磁盘碎片 的分析，非常到位 👍。

我帮你稍微整理成 面试更易读的结构化版本，更清晰、逻辑分明：

64. DELETE 大量数据如何优化？

  1. Buffer Pool 冲击

    • DELETE 会读取并锁定涉及的页。
    • 删除几千万行时，大量脏页和淘汰页会挤占 Buffer Pool，降低正常查询的缓存命中率。

  2. I/O 消耗高

    • DELETE 需要写入大量 UNDO/REDO 日志，并读取数据页。
    • 瞬时 I/O 高峰会导致正常读写操作变慢。

  3. 间隙锁阻塞其他操作

    • 在非唯一索引范围条件下，REPEATABLE READ 会触发 Next-Key Lock（记录 + 间隙锁）。
    • 不仅删除行被锁定，索引间隙也被锁定，阻止其他事务在这些范围内 INSERT，影响并发。

  4. 磁盘存储碎片

    • 大量 DELETE 会留下空洞，导致数据页不连续，影响存储和查询性能。
  1. 批量插入如何优化？
  2. insert ignore 和 replace into 的区别？
  3. insert on duplicate key update 的作用？
  4. select for update 的锁机制？
  5. nowait 和 skip locked 的作用？
  6. MySQL 索引 B+Tree 为什么比 B-Tree 更适合？

B+Tree 相比 B-Tree 的优势主要包括：

  1. 内部节点只存 key → 树更矮，IO 次数少；
  2. 叶子节点链表 → 范围查询、顺序扫描快；
  3. 支持聚簇索引，叶子节点存整行数据；
  4. 二级索引小 → 回表高效；
  5. 支持事务锁粒度优化（Gap Lock/Next-Key Lock）；
  6. 页密度高 → 磁盘访问友好；
  7. 树矮 + 顺序页 → 大数据量下查询仍然高效。

71. 为什么不使用 Hash 索引？

MySQL 不使用 Hash 索引的原因主要有：

  1. 仅支持等值查询，不支持范围查询和排序；
  2. 哈希冲突导致性能不稳定；
  3. 无法维护数据顺序，不能支持 ORDER BY / GROUP BY；
  4. 难以支持事务、行锁和 MVCC；
  5. 空间利用率低，哈希表需要预留容量。

因此，MySQL InnoDB 默认选择 B+Tree 索引，兼顾范围查询、排序、高并发事务和存储效率。

  1. 聚簇索引的优缺点？
  2. 回表是什么意思？如何避免？
  3. 索引合并 (Index Merge) 是什么？
  4. 最左匹配原则的底层实现逻辑？
  5. explain partitions 列表示什么？
  6. subquery 在什么情况下会被优化为 semi-join？
  7. derived merge 优化是什么？
  8. materialized subquery 是什么？
  9. optimizer trace 的作用？

三、InnoDB 深入与事务（40 题）

  1. InnoDB 存储结构（表空间、段、区、页）？
  2. InnoDB 数据页大小？
  3. redo log 和 binlog 区别？
  4. redo log 的作用？
  5. undo log 的作用？
  6. 两阶段提交 (2PC) 在 MySQL 的应用？
  7. LSN (Log Sequence Number) 是什么？
  8. InnoDB checkpoint 机制？
  9. MVCC 的实现原理？
  10. MVCC 依赖 undo log 和 Read View 吗？
  11. 快照读和当前读的区别？
  12. select * from t where id=1 for update 会加什么锁？
  13. RC、RR、Serializable 区别？
  14. RR 是如何避免幻读的？
  15. 什么是 next-key lock？
  16. 事务隔离级别与锁的关系？
  17. autocommit=1 的事务执行流程？
  18. 如何避免死锁？
  19. show engine innodb status 的作用？
  20. purge 线程的作用？
  21. insert buffer 的作用？
  22. doublewrite buffer 的作用？
  23. adaptive hash index 的作用？
  24. change buffer 的作用？
  25. innodb_flush_log_at_trx_commit 参数意义？
  26. sync_binlog 参数意义？
  27. innodb_lock_wait_timeout 的作用？
  28. gap lock 和 next-key lock 的区别？
  29. phantom read 的本质？
  30. undo log 什么时候会被清理？
  31. innodb_buffer_pool 的作用？
  32. buffer pool 中的 LRU 算法是怎样的？
  33. free list 和 flush list 的区别？
  34. insert/update/delete 各会写哪些日志？
  35. 为什么 redo log 是顺序写？
  36. redo log group commit 的作用？
  37. InnoDB 支持表压缩吗？

118. InnoDB 大表如何分区？

InnoDB 大表分区常用策略包括：按时间 RANGE 分区、按用户或 ID HASH 分区、或组合子分区。
分区能提升查询效率、降低锁竞争、加快历史数据清理。
分区设计时要注意：分区列必须在主键/唯一索引中、分区数量不要过多、查询必须利用分区列，才能真正发挥性能优势。

119. 为什么 InnoDB 必须有主键？

InnoDB 表在物理存储上必须有一个“聚簇索引”（Clustered Index），而这个聚簇索引的键就是逻辑上的“主键”。

120. 如果没有定义主键会怎样？

在关系型数据库（如 MySQL、PostgreSQL、Oracle 等）中，如果没有显式定义主键（Primary Key），会产生一系列影响，具体取决于你使用的数据库系统。我们以最常用的 MySQL（InnoDB 存储引擎） 为例来详细说明：

  1. 如果没有定义主键，InnoDB 会如何处理？
    InnoDB 存储引擎必须有一个主键，即使你没有明确定义。

如果表中没有定义主键，InnoDB 会按以下顺序自动选择一个“隐式主键”：

  1. 优先选择第一个 NOT NULL 的 UNIQUE 索引（唯一键），前提是它的所有列都非空。
  2. 如果没有这样的唯一索引，InnoDB 会自动创建一个隐藏的 6 字节的 ROW_ID（行 ID），作为聚簇索引（Clustered Index）。
    ❌ （1）性能下降
    隐式创建的 ROW_ID 只用于索引组织表，无法被 SQL 查询直接使用。
    如果你想通过主键查询某一行（如 SELECT * FROM t WHERE id = ?），但没有主键，就无法利用主键索引进行高效查找。
    所有二级索引（Secondary Index）最终都要回表到主键索引，使用隐藏 ROW_ID 会导致索引结构不够高效。
    ❌ （2）无法利用主键进行优化
    主键是唯一标识，常用于外键关联、JOIN、分页（LIMIT offset, size）、更新/删除特定行等。
    没有主键时，这些操作可能需要全表扫描或使用非唯一条件，效率低且容易出错。
    ❌ （3）数据去重和一致性困难
    主键保证了数据的唯一性。没有主键，可能出现重复数据。
    在主从复制、分布式系统或数据同步时，没有主键会导致难以识别和同步特定行。
    ❌ （4）不利于后期维护和扩展
    后期想加主键可能需要锁表、重建表，影响线上服务。
    ORM 框架（如 Hibernate、MyBatis Plus）通常依赖主键映射对象，缺少主键可能导致框架异常。
    ❌ （5）隐藏 ROW_ID 有潜在风险
    隐藏 ROW_ID 是全局递增的，可能导致争用（contention），尤其是在高并发插入场景下。
    多个表共用同一个 ROW_ID 计数器，可能影响性能。

四、运维与调优（40 题）

  1. MySQL 的主从复制原理？
  2. binlog 的格式有哪些？
  3. statement、row、mixed 格式的区别？
  4. 主从延迟的原因？
  5. 如何解决主从延迟？
  6. 主从复制能否并行？
  7. GTID 的作用？
  8. 半同步复制和异步复制区别？
  9. MySQL 高可用方案有哪些？
  10. MHA 原理？
  11. MySQL Group Replication 原理？
  12. ProxySQL 的作用？
  13. MyCat 或 ShardingSphere 的作用？
  14. MySQL 分库分表方案有哪些？
  15. 垂直拆分和水平拆分区别？
  16. 数据迁移常见方案？
  17. mysqldump 的原理和缺点？
  18. xtrabackup 的原理和优点？
  19. 在线 DDL 如何实现？
  20. pt-online-schema-change 的原理？
  21. alter table 的代价？
  22. 表分区的优缺点？
  23. range、list、hash、key 分区的区别？
  24. MySQL 8.0 新特性有哪些？
  25. MySQL 8.0 的 JSON 支持？
  26. MySQL 如何实现全文检索？
  27. MySQL 的字符集和排序规则？
  28. utf8mb4 和 utf8 的区别？
  29. collation utf8mb4_general_ci 和 utf8mb4_unicode_ci 区别？

150. 大表如何添加索引？

1. 核心策略：利用 Online DDL (Inplace/Instant)

从 MySQL 5.6 版本开始，InnoDB 引入了 Online DDL（在线数据定义语言）功能，这是在大表上添加索引的首选方法。

使用 ALGORITHM 和 LOCK 选项

在 ALTER TABLE 语句中，通过指定 ALGORITHM 和 LOCK 选项，可以控制操作的阻塞级别。

ALTER TABLE table_name
ADD INDEX idx_name (column_name),
ALGORITHM = INPLACE,
LOCK = NONE;
选项 值 含义 影响 推荐度
ALGORITHM INPLACE 推荐。 在线操作，允许并发 DML（增删改），只需要在操作开始和结束时短暂锁表。 性能影响小，但仍需复制增量日志。 高
COPY 不推荐。 离线操作，需要创建新表，复制数据，完全阻塞 DML。 性能差，耗时长。 低
INSTANT 最佳。 (MySQL 8.0+) 瞬间操作，只修改表的元数据，不涉及数据拷贝。 仅适用于某些操作（例如添加列或二级索引），对性能影响最小。 最高
LOCK NONE 推荐。 允许所有 DML 操作并发进行。 对业务影响最小。 高
SHARED 允许并发读取（SELECT），但阻塞并发写入（INSERT/UPDATE/DELETE）。 仅在特殊情况下使用。 中
EXCLUSIVE 完全阻塞所有读写操作。 禁止在大表 DDL 中使用。 低

总结： 尽量使用 ALGORITHM=INPLACE, LOCK=NONE (MySQL 5.6/5.7)，如果 MySQL 8.0+ 且条件允许，则使用 ALGORITHM=INSTANT。

2. 辅助工具（针对旧版本或复杂情况）

如果您的 MySQL 版本不支持高效的 Online DDL，或者需要更细粒度的控制，可以考虑使用第三方工具：

  • pt-online-schema-change (Percona Toolkit):
    • 原理： 创建一个与原表结构相同的新表，然后在新表上添加索引。通过在原表上创建触发器，将操作期间的增量数据同步到新表。最后，原子性地替换新旧表。
    • 优点： 几乎无锁，对线上业务影响极小。
  • gh-ost (GitHub's Online Schema Migrations):
    • 原理： 类似 pt-osc，但使用 Binlog 进行增量同步，对原表的负载更低。
    • 优点： 性能更高，容错性更好，是目前社区推荐的 Online DDL 替代方案。
  1. 索引重建的方式？
  2. 如何查看表占用的空间大小？
  3. 如何清理碎片？
  4. truncate 和 drop table 后磁盘是否释放？
  5. analyze table 的作用？
    “ANALYZE TABLE 的作用是让 MySQL 重新分析并更新表的索引统计信息。
    优化器在生成执行计划时会依赖这些统计信息，比如索引的区分度、数据分布、行数估计等等。
    如果表里的数据变化比较大，比如大量的增删改操作后，这些统计信息可能会不准确，
    导致优化器选错索引或者走全表扫描，SQL 就会变慢。
    这时候执行 ANALYZE TABLE 就可以让优化器重新收集准确的索引信息，
    从而让执行计划恢复正常。”

156. optimize table 的作用？

OPTIMIZE TABLE 命令的主要作用是 整理表数据和索引的碎片，并回收由于删除或更新操作导致的 未使用空间，从而优化表的物理存储。

OPTIMIZE TABLE 的核心作用

1. 回收和释放碎片空间 (整理文件)

  • 当您对表进行大量的 DELETE、UPDATE 或 INSERT 操作时，数据文件和索引文件可能会产生 碎片（Fragmentation）和 空洞。
    • DELETE 操作只是将记录标记为已删除，并不会立即将磁盘空间归还给操作系统。
    • UPDATE 操作如果导致行长度增加，可能会将行移动到新的位置，在原位置留下碎片。
  • OPTIMIZE TABLE 通过 重建整个表 来消除这些碎片和空洞，将数据和索引紧密排列，从而减少文件大小，并将未使用的空间释放回文件系统（取决于具体的 InnoDB 配置）。

2. 提高 I/O 效率和查询性能

  • 由于碎片被消除，数据在磁盘上变得连续存储，这使得数据库在读取数据时能进行更高效的 顺序 I/O，减少了磁盘寻道时间。
  • 索引页和数据页的密度更高，能减少索引树的高度和查询所需的 I/O 次数，从而提高查询性能。

3. 更新统计信息

  • 执行 OPTIMIZE TABLE 会强制 MySQL 重新收集表的统计信息（如索引基数、行数分布等）。
  • 这些最新的统计信息对于 查询优化器（Query Optimizer） 至关重要，它能帮助优化器选择更准确、更高效的执行计划。

OPTIMIZE TABLE 对不同引擎的影响

存储引擎 OPTIMIZE TABLE 的底层操作 效果
InnoDB 执行 ALTER TABLE ... FORCE 或 ALTER TABLE ... ENGINE=InnoDB，即 原地重建表 (Online DDL)。 整理数据和索引碎片，回收空间，并更新统计信息。在支持 Online DDL 的版本中，这个过程对 DML 阻塞很小。
MyISAM 锁定表，重建表数据文件 (.MYD) 和索引文件 (.MYI)。 整理碎片，提高读取速度。会完全阻塞所有操作。

注意事项和替代方案

  1. 阻塞问题： 尽管 InnoDB 在新版本中可以使用 Online DDL (ALGORITHM=INPLACE) 来执行 OPTIMIZE TABLE，但操作开始和结束时仍然需要短暂的 元数据锁（Metadata Lock, MDL）。因此，应在业务低峰期执行。

  2. 空间要求： 执行 OPTIMIZE TABLE 时，数据库实例必须有 足够的临时磁盘空间 来容纳重建表所需的新数据副本。通常，所需的空间量与原表大小大致相当。

  3. 替代方案：

    • 对于 InnoDB，执行 ALTER TABLE table_name ENGINE=InnoDB; 或 ALTER TABLE table_name ROW_FORMAT=COMPACT; 等无实质性改变的 DDL 操作，也能触发表的重建和碎片整理，达到与 OPTIMIZE TABLE 类似的效果。
    • 在 MySQL 8.0 中，可以使用 ALTER INSTANT 来更快地更新统计信息。

  4. show global status 常用指标？

  5. QPS 和 TPS 如何计算？

159. innodb_row_lock_time 过高如何优化？

步骤 SQL 语句 目的/作用 分析重点
1. 快速定位锁等待 SELECT * FROM sys.innodb_lock_waits; 核心步骤。 快速、清晰地识别出 阻塞者 (blocking_pid) 和 等待者 (waiting_pid) 的 ID、以及他们正在执行的 SQL。 关注 blocking_pid 和 blocking_query。如果结果为空，说明锁竞争是瞬时的。
2. 确认所有活动事务 SELECT * FROM information_schema.innodb_trx; 查看所有当前运行的 InnoDB 事务，特别是那些长时间未提交的事务（无论是否在阻塞）。 关注 trx_mysql_thread_id、trx_started（事务开始时间）和 trx_state。
3. 定位会话细节 SELECT * FROM information_schema.PROCESSLIST WHERE ID = [ID]; 根据第 1 步或第 2 步得到的线程 ID，查看该会话的当前状态、连接信息和执行时间。 关注 HOST（定位应用服务器）、USER（定位应用服务）和 Command（如果是 Sleep，则意味着空闲持锁）。
  1. 如何限制单个用户连接数？
方法 示例语句 特点 是否影响现有权限 推荐度
ALTER USER ALTER USER 'user'@'host' WITH MAX_USER_CONNECTIONS N; ✅ 推荐方式，安全、直接；专门用于修改用户属性，不会影响现有权限 ❌ 否 ⭐⭐⭐⭐（推荐）
CREATE USER CREATE USER 'user'@'host' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS N; 适用于新建用户时一并设置连接限制 - ⭐⭐⭐
GRANT GRANT ALL PRIVILEGES ON *.* TO 'user'@'host' WITH MAX_USER_CONNECTIONS N; 功能强大但需谨慎，可能重置用户其他权限 ⚠️ 可能 ⭐（不推荐）

五、架构设计与实战（40 题）

  1. 如何设计一个高并发的订单表？
  2. 如何防止超卖？
  3. 乐观锁和悲观锁的区别？
  4. 如何用版本号实现乐观锁？
  5. 如何设计一个百万级用户的登录表？
  6. 如何存储用户密码比较安全？
  7. MySQL 支持分布式事务吗？
  8. XA 事务的实现原理？
  9. 分布式场景下如何保证数据一致性？
  10. CAP 定理和 BASE 理论？
  11. 如何用 binlog 实现消息队列？
  12. 如何利用 MySQL 实现幂等？
  13. MySQL 如何实现延迟队列？
  14. 大表 join 如何优化？
  15. 一张 1 亿行的表如何做分页查询？
  16. 如何设计一个高效的消息表？
  17. 如果表数据过亿，如何存储用户行为日志？
  18. 如何存储 JSON 数据？
  19. JSON 字段能建索引吗？
  20. 虚拟列 (Generated Column) 是什么？
  21. MySQL 支持函数索引吗？
  22. GIS（地理空间索引）如何实现？
  23. 如何设计一个秒杀系统的数据库架构？
  24. 如何避免热点更新？
  25. 如何存储海量图片/文件？
  26. 大数据量下的唯一约束如何优化？
  27. 主键设计是自增还是 UUID？
  28. 自增主键可能出现的问题？
  29. 雪花算法的主键优缺点？
  30. 高并发下如何避免死锁？
  31. 高并发下如何避免锁表？
  32. 高并发下如何保证写入性能？
  33. 高并发下如何保证读性能？
  34. 分布式 ID 的实现方式有哪些？
  35. MySQL 中冷热数据分离怎么做？
  36. MySQL 与 Redis 搭配的常见场景？
  37. MySQL + ElasticSearch 如何配合？
  38. 如果一张表数据量太大，怎么拆分？
  39. 你做过的 MySQL 性能优化实践？
  40. 谈谈你在项目中踩过的 MySQL 坑？

要不要我帮你把这 200 道题再整理成「简答题 + 参考答案」版本，这样可以直接当复习笔记用？

posted @ 2025-11-02 09:29  不报异常的空指针  阅读(10)  评论(0)    收藏  举报