MySQL面试深度解析：核心概念与优化策略

"这是一份关于MySQL面试题目的文档，包含20道常见的MySQL面试问题及答案，涉及复制原理、存储引擎对比、数据类型、事务与日志、日志格式、性能调优、备份策略、数据库监控、一致性校验、表情支持、数据字典维护等多个方面，适合准备MySQL面试的求职者参考学习。" 1. MySQL复制原理及流程： MySQL复制主要涉及三个线程：I/O线程、SQL线程和 Binlog Dump线程。I/O线程负责从主库拉取二进制日志（binlog），SQL线程则负责应用这些日志到从库，Binlog Dump线程则将主库的binlog发送给从库的I/O线程。 2. MyISAM与InnoDB的区别： - 存储结构：MyISAM使用非聚簇索引，InnoDB使用聚簇索引。 - 事务支持：MyISAM不支持事务，InnoDB支持事务。 - 表锁/行锁：MyISAM主要使用表级锁定，InnoDB支持行级锁定，提高并发性。 - 引擎特性：InnoDB有外键约束、行级锁定、MVCC（多版本并发控制）等特性。 - 空间占用：InnoDB通常比MyISAM占用更多空间，因为需要存储事务日志和行级锁定信息。 3. VARCHAR与CHAR的区别： - VARCHAR节省空间，只存储实际需要的字符数，而CHAR固定长度，无论内容多寡，都会占用指定长度的空间。 - VARCHAR(50)的50表示最多可存储50个字符，对于UTF-8编码，每个字符可能占用1-3字节。 4. InnoDB的事务与日志实现： - InnoDB使用redo log（重做日志）和undo log（回滚日志）来实现事务的持久化和回滚。 - 事务的隔离级别包括读未提交（READ UNCOMMITTED）、读已提交（READ COMMITTED）、可重复读（REPEATABLE READ）和串行化（SERIALIZABLE）。 - 事务通过日志来实现，如当事务提交时，其修改会先写入redo log，确保即使在系统崩溃时也能恢复。 5. MySQL binlog日志格式： - 三种格式：STATEMENT、ROW和MIXED，STATEMENT记录改变SQL语句，ROW记录具体行变化，MIXED是两者的混合。 - ROW格式在复制中能更好地保持数据一致性，但占用空间较大。 - 根据业务需求和性能考虑选择合适的日志格式。 6. MySQL CPU飙升处理： - 分析CPU高的原因，如查询复杂度、索引缺失、内存不足等。 - 调整SQL语句、优化索引、增加硬件资源或调整参数配置。 7. SQL优化： - EXPLAIN输出可以帮助理解查询执行计划，分析表扫描方式、索引使用等。 - PROFILE提供查询的执行步骤和时间，用于定位性能瓶颈。 8. 备份与恢复： - mysqldump基于逻辑备份，恢复速度快但占用网络资源。 - xtrabackup进行物理备份，恢复效率高，尤其适合大数据量环境。 9. 大INSERT语句与复制点信息： - 使用`--max-allowed-packet`参数可设置单条INSERT语句的最大长度。 - 复制点信息可在备份命令中加入`--master-data`选项。 10. 快速重启500台DB： 可通过自动化工具批量重启，确保操作一致性和顺序。 11. InnoDB读写参数优化： - 读取参数如`innodb_buffer_pool_size`调整缓存池大小。 - 写入参数如`innodb_flush_log_at_trx_commit`控制日志刷盘策略。 - IO参数如`innodb_io_capacity`设置I/O能力。 - 缓存参数如`query_cache_size`管理查询缓存。 12. 数据库监控与慢日志： - 使用监控工具（如Percona Monitoring Plugins）收集性能指标。 - 查询慢日志（slow query log）找出性能问题。 13. 主从一致性校验： - 工具如pt-table-checksum和pt-table-sync可用于检测和修复主从不一致。 14. Emoji表情支持： - 需要使用支持Unicode的字符集，如utf8mb4。 15. 数据字典维护： - 定期清理无用表和索引，保持数据字典的整洁。 16. 开发规范执行： - 建立SQL编码规范，使用代码审查和自动化测试确保合规。 17. 大字段处理： - 根据业务需求决定是否拆分字段，或者使用JSON列存储复杂数据。 以上是部分MySQL面试题目的详细解答，涵盖了数据库设计、性能优化、复制、备份等多个核心领域。