Mysql 高频面试题

一、介绍一下MySQL事务的四大特性ACID？

ACID分别代表：

1. 原子性（Atomicity）：事务是一个不可分割的工作单位，其中的操作要么全部执行成功提交，要么全部不执行回滚。即使在执行过程中遇到错误或系统崩溃，也能保证数据库不会处于部分更新的状态。

2. 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。一致性状态指的是数据库中的数据满足所有预定义的约束和规则，比如数据类型、检查约束、外键约束等。任何违反这些规则的操作将导致整个事务被撤销。

3. 隔离性（Isolation）：事务的执行不受其他事务干扰，即并发执行的多个事务之间相互隔离。通过不同的隔离级别可以控制事务之间的可见性，避免如脏读、不可重复读和幻读等问题。MySQL默认的隔离级别是可重复读（Repeatable Read）。

4. 持久性（Durability）：一旦一个事务被提交，它对数据库所做的更改就是永久性的，即便系统发生故障也不会丢失。这是通过数据库的日志和恢复机制来实现的。

二、索引创建要注意什么？

1. 选择合适的列：高选择性、常用在WHERE、JOIN、ORDER BY中的列。
2. 避免过多索引：权衡读写性能，减少冗余。
3. 复合索引的设计：列顺序的重要性，最左匹配原则。
4. 索引类型的选择：B-Tree、哈希、全文等适用场景。
5. 索引维护：定期重建、监控使用情况。
6. 特殊注意事项：前缀索引、NULL值处理、覆盖索引。
7. 避免过度索引：监控和删除无用索引。

1. 选择合适的列创建索引

• 高选择性列：优先为取值唯一性高的列（如主键、唯一键）创建索引。例如，用户ID的索引效果远好于性别字段。
• 常用查询条件列：为频繁出现在 WHERE、JOIN、ORDER BY 和 GROUP BY 子句中的列创建索引。
• 避免低选择性列：如性别（男/女）、布尔值等字段，索引收益有限，可能导致全表扫描更高效。
• 覆盖索引：如果查询所需列均包含在索引中（覆盖索引），可避免回表操作，直接通过索引获取数据。

2. 避免过多索引

• 权衡读写性能：索引能加速查询，但会增加 INSERT、UPDATE、DELETE 的开销。频繁更新的列（如状态字段）需谨慎创建索引。
• 删除冗余索引：例如，已存在复合索引 (A, B, C) 时，无需单独为 (A) 或 (A, B) 创建索引。
• 监控索引使用率：通过工具（如 EXPLAIN、pg_stat_user_indexes）分析索引是否被实际使用，及时清理无用索引。

3. 合理设计复合索引

• 列顺序：将选择性高、最常用于过滤的列放在前面。例如，用户按 age 和 city 查询时，若 city 选择性更高，应建索引 (city, age)。
• 最左匹配原则：查询条件需包含复合索引的最左列才能生效。例如，索引 (A, B, C) 可支持 A=1、A=1 AND B=2，但不支持 B=2。
• 覆盖索引优化：复合索引包含查询所需所有列时，可避免回表操作。例如，查询 SELECT name FROM users WHERE age > 30，可建索引 (age, name)。

4. 索引类型选择

• B-Tree 索引：默认类型，适用于范围查询（如 >, <, BETWEEN）和精确匹配。
• 哈希索引：仅支持等值查询（如 =），适用于内存表（如 MySQL 的 Memory 引擎），但不支持范围查询。
• 全文索引：用于文本搜索（如 MATCH ... AGAINST），适合大文本字段（如文章内容）。
• 空间索引：针对地理空间数据（如 POINT 类型），适用于地图查询。
• 前缀索引：对长字符串（如 VARCHAR(255)）可指定前缀长度（如 col(10)），节省空间，但需权衡选择性。

5. 索引维护与优化

• 定期重建索引：频繁更新的表可能出现索引碎片，需定期重建（如 OPTIMIZE TABLE 或 REINDEX）以保持效率。
• 监控索引碎片：通过 SHOW TABLE STATUS（MySQL）或 pg_repack（PostgreSQL）检查碎片率，及时清理。
• 批量操作时禁用索引：在导入大量数据时，可先删除索引，导入完成后再重建，减少写操作开销。

6. 特殊注意事项

• NULL 值处理：大部分数据库（如 MySQL）的索引不包含 NULL 值，若查询条件包含 IS NULL，可能无法使用索引。
• 避免频繁更新的列：频繁修改的列（如计数器）会导致索引频繁更新，降低性能。
• 分区表索引：对分区表需考虑分区键与索引列的关系，合理设计局部索引或全局索引。
• 锁与并发：创建索引时可能对表加锁（如 PostgreSQL 的 CREATE INDEX），需在低峰期操作以避免阻塞。

7. 数据库差异与限制

• 数据类型限制：某些数据类型（如 TEXT、BLOB）不能直接作为索引键，但可作为包含列（如 MySQL 的前缀索引）。
• 存储引擎支持：例如，哈希索引仅在 MySQL 的 Memory 引擎中可用，InnoDB 默认使用 B-Tree。
• 特殊索引类型：Oracle 支持逆序索引、基于函数的索引；PostgreSQL 支持 GiST/GIN 索引（用于 JSON/全文搜索）。

8. 实践建议

1. 测试与验证：使用 EXPLAIN 分析查询计划，确认索引是否生效。
2. 小表无需索引：记录数较少的表（如配置表）全表扫描可能比索引更快。
3. 业务场景适配：根据读写比例、数据分布、查询模式调整索引策略，避免教条主义。
4. 文档与监控：记录索引设计逻辑，定期审查索引使用情况，结合监控工具（如 Prometheus + Grafana）优化。

三、如何进行查询优化？

1、索引优化：创建高选择性索引、避免冗余索引、定期重建索引；

2、SQL语句优化：避免全表扫描、减少子查询、优化分页逻辑；

3、数据库设计：使用分区表、归档冷数据、合理范式化/反范式化；

4、缓存优化：应用层缓存高频数据、设置合理的缓存过期时间；

5、执行计划分析：使用 EXPLAIN 分析查询，优化索引和查询条件；

6、数据库配置：调整内存、连接池、锁机制等参数；

 四、简单介绍一下redo log 和 undo log ?

• redo log是物理日志，用于崩溃恢复和持久性，确保已提交事务的修改不丢失。
• undo log是逻辑日志，用于事务回滚和MVCC，保证原子性和一致性。
• 两者共同协作，支撑MySQL的ACID特性，是数据库可靠性和高性能的关键机制。