面试题012-数据库-MySQL(日志+优化)

面试题012-数据库-MySQL(日志+优化)

题目自测

• 1. MySQL中常见的日志有哪些？
• 2. 慢查询日志有什么用？
• 3. binlog 主要记录了什么？
• 4. redo log 如何保证事务的持久性？
• 5. binlog 和 redolog 有什么区别？
• 6. 页修改之后为什么不直接刷盘呢？
• 7. 如何优化SQL查询？
• 8. 如何分析SQL性能？
• 9. 读写分离如何实现？
• 10. 为什么要分库分表？

题目答案

1. MySQL中常见的日志有哪些？

答：MySQL中的常见日志包括错误日志、查询日志、慢查询日志、二进制日志、事务日志（包括重做日志和回滚日志）、中继日志等。这些日志在数据库的运行、维护、优化和故障恢复等方面都发挥着重要作用。

• 错误日志(error log)：记录MySQL服务器启动和关闭的详细信息，以及在运行过程中遇到的错误。
• 查询日志(general query log)：记录所有客户端连接和断开连接的信息，以及每一条执行的SQL语句。
• 慢查询日志(slow query log)：记录所有执行时间超过特定时间阈值的查询（默认为 10 秒）。
• 二进制日志(binary log)：记录所有对数据库进行修改的SQL语句，用于数据恢复和主从复制。
• 事务日志(redo log 和 undo log)：redo log重做日志，undo log回滚日志。
• 中继日志(relay log)：在主从复制环境中，从服务器接收并处理主服务器发送的二进制日志事件。

2. 慢查询日志有什么用？

答：慢查询日志记录了所有执行时间超过阈值(long_query_time 默认为10s)的查询，通过分析这些日志找出可以优化的查询语句。

• 慢查询相关的配置(MySQL配置文件 my.cnf)

  # 用于启用慢查询日志。
  slow_query_log = 1
  # 用于指定慢查询日志文件的路径和名称
  slow_query_log_file = /path/to/your/slow_query.log
  # 置慢查询的时间阈值
  long_query_time = 2
  # 记录那些没有使用索引的查询
  log_queries_not_using_indexes = 1
  

3. binlog 主要记录了什么？

答：binlog主要记录了所有导致数据库中所有数据变更的操作。

• 记录的主要内容
  • SQL语句：所有执行的DDL语句(CREATE TABLE、ALTER TABLE、DROP TABLE)和DML语句(INSTER、UPDATE、DELETE)这些会改变数据变更等语句。
  • 事务信息：事务的开始和结束标记，以及事务的隔离级别和自动提交状态。

4. redo log 如何保证事务的持久性？

答：redo log 是物理日志，记录了某个数据页的修改，如某个页面某个偏移处修改了几个字节的内容以及被修改的具体内容是什么。每当执行一个事务就会产生这样的一条或者多条物理日志。

• InnoDB采用写前日志策略(WAL)，这意味着在对数据进行修改之前，首先将这些修改记录到redo log中。只有在redo log被成功写入后，才会对实际数据页进行修改。
• 在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。
• 当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

5. binlog 和 redo log 有什么区别？

答：binlog是二进制日志，redo log是重做日志。

• binlog是MySQL的服务层实现的日志，所有引擎都可以使用。redo log是InnoDB引擎特有的。
• binlog主要用于备份恢复、主从复制。redo log用于保证数据持久性，数据事务级别的数据恢复。
• binlog是追加写，不会覆盖以前的日志，保存的是全部的日子记录。redo log是循环写，空间大小固定，保存的是未被刷入磁盘的脏页日志。
• binlog是逻辑日志，主要记录的是数据库执行的所有DDL和DML语句。redo log是物理日志，主要记录的是某个页的修改。

6. 页修改之后为什么不直接刷盘呢？

答：不直接将数据页修改写入磁盘的主要原因是为了提高数据库性能、减少磁盘I/O操作，并通过使用缓冲池和Redo Log等机制确保数据的可靠性和一致性。通过将数据页的修改先写入内存，并在适当的时候批量刷新到磁盘，InnoDB可以在保证数据安全的前提下，显著提升数据库的运行效率。

7. 如何优化SQL查询？

答：优化SQL查询的方式有很多，如使合适索引，优化查询语句，分析执行计划等。

• 使用合适的索引：
  • 创建索引：在经常用于查询条件的列上创建索引可以显著加快查询速度。
  • 优化索引：确保索引是最新的，并且没有过多的重复或冗余索引。
  • 复合索引：如果查询条件经常涉及多个列，考虑创建包含这些列的复合索引。
• 优化查询语句：
  • 避免使SELECT*查询：明确列出需要查询的列，可以减少数据传输的量和处理时间。
  • 避免子查询：尽量使用JOIN代替子查询，JOIN的效率更高。
  • 优化JOIN操作：确保JOIN条件中有适当的索引，尽量减少JOIN的表数量。
  • 使用EXISTS代替IN：当处理大数据集时，EXISTS通常比IN更快。
  • 避免全表扫描：在WHERE子句中避免对列使用函数操作，使用通配符开头的LIKE语句，都会导致全表扫描。
• 使用EXPLAIN：
  • 使用EXPLAIN关键字分析查询计划，了解数据库如何执行查询，以找出潜在的性能瓶颈。

8. 如何分析SQL性能？

答：分析SQL性能的方法有多种，可以通过EXPLAIN分析查询计划、SHOW PROFILE分析执行过程、慢查询日志、以及一些第三方的分析工具。

• 使用EXPLAIN：执行计划显示了数据库如何解析、优化和执行SQL语句，包括访问哪些表、使用的索引、联接方法等。
• 使用SHOW PROFILE：该命令可以提供详细的查询执行统计信息，如时间花费在各个阶段的比例，以及执行每个阶段的次数。

9. 读写分离如何实现？

答：实现读写分离的基本思想是将数据库的读取请求分散到多个从属数据库服务器，而写操作仍然集中在主数据库服务器。

1. 部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。
2. 保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从复制。
3. 系统将写请求交给主数据库处理，读请求交给从数据库处理。

10. 什么是分库分表？

答：分库分表是一种数据库设计技术，用于解决单一数据库实例在数据量极大、并发访问极高时面临的性能瓶颈和可扩展性问题。通过将数据分散存储到多个数据库实例或多个表中，可以有效减轻单一数据库或表的负载，提高数据处理的效率和系统的整体性能。

• 分库：将数据库中的数据分散到不同的数据库上，分为垂直分库和水平分库。
  • 垂直分库：把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。
  • 水平分库：把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。
• 分表：对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。
  • 垂直分表：对数据表列的拆分，把一张列比较多的表拆分为多张表。
  • 水平分表：对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。

参考资料

• JavaGuide
• 牛客网-Java面试宝典
• ChatGPT
• MySQL官网
• 图解MySQL