面试题：MySQL主库变更与角色交换中的性能优化与故障恢复

数据库状态分析：
- 使用 SHOW STATUS 命令获取当前数据库的状态信息，包括查询执行次数、缓存命中率等，了解数据库当前负载情况。
- 查看 SHOW VARIABLES 相关配置，确保如 innodb_buffer_pool_size、query_cache_type 等关键参数设置合理。
查询分析：
- 使用 EXPLAIN 关键字分析主库上执行频率较高的 SQL 查询，检查索引使用情况，确保查询有合适的索引支持，避免全表扫描。
- 利用 slow_query_log 开启慢查询日志，记录执行时间较长的查询，提前优化这些潜在的性能瓶颈查询。
硬件资源评估：
- 检查服务器的 CPU、内存、磁盘 I/O 和网络带宽等资源使用情况。确保在主库变更和角色交换期间，硬件资源能够满足系统需求，如有必要，可提前进行硬件升级或资源调整。
复制延迟评估：
- 在主从复制环境下，使用 SHOW STATUS LIKE 'Seconds_Behind_Master' 查看从库复制延迟情况。如果延迟较高，分析原因并解决，避免在角色交换后影响业务性能。
模拟测试：
- 在测试环境中模拟主库变更和角色交换操作，监控系统性能指标，如响应时间、吞吐量等。根据测试结果提前优化数据库配置、查询语句或应用程序逻辑。

实时监控：
- 利用 SHOW STATUS、SHOW ENGINE INNODB STATUS 等命令实时监控数据库运行状态，包括事务处理、锁争用等情况。
- 使用操作系统工具（如 top、iostat、netstat）实时监控服务器硬件资源使用情况。
性能问题应对：
- 查询性能下降：如果发现查询性能突然下降，迅速使用 EXPLAIN 再次分析查询，确认是否由于索引失效或其他原因导致。若为索引问题，根据需要重建或添加索引。
- 锁争用：通过 SHOW ENGINE INNODB STATUS 查看锁争用情况，对于长时间持有锁的事务，根据业务情况决定是否回滚该事务以释放锁资源。
- 硬件资源瓶颈：如果 CPU 使用率过高，可考虑调整数据库配置参数，减少不必要的查询计算；若磁盘 I/O 瓶颈，可优化数据库存储结构，如采用固态硬盘或调整数据文件存储位置。
故障应对：
- 主库故障：立即启动从库提升为主库的应急流程，确保业务能尽快恢复读写操作。同时，对故障主库进行诊断，检查硬件、软件错误日志，分析故障原因。
- 网络故障：检查网络连接设备（如路由器、交换机），确认网络故障范围。如果是数据库服务器网络接口问题，尝试重启网络服务或更换网络接口；若为网络链路问题，及时联系网络管理员修复。

性能验证：
- 重新运行性能测试用例，对比操作前后的性能指标，确保系统性能恢复到正常水平或符合预期优化目标。
- 再次分析关键查询的执行计划，确认索引使用和查询优化效果持续有效。
数据一致性检查：
- 使用数据库自带的工具（如 innodb_checksums）或编写脚本来验证数据的一致性，确保在主库变更和角色交换过程中数据没有丢失或损坏。
- 对比主从库的数据记录数量、关键数据字段值等，发现不一致及时排查原因并修复。
故障复盘：
- 对于操作过程中出现的性能问题和故障，进行详细的复盘，分析根本原因，总结经验教训，更新应急预案和操作手册，避免类似问题在未来的操作中再次出现。
性能持续优化：
- 根据操作后的性能数据和业务发展需求，持续对数据库进行性能优化，如进一步调整配置参数、优化查询语句、添加合适索引等。

星途面试题库