数据库优化 Interview MySQL

如何优化mysql慢日志？介绍下mysql explain参数

本文介绍了优化MySQL慢日志的步骤和策略，并详细解析了EXPLAIN语句的各个参数，帮助开发者分析查询性能瓶颈。

核心内容:

  • 开启并配置慢日志，使用long_query_time和log_queries_not_using_indexes参数定位低效查询
  • 通过EXPLAIN分析查询执行计划，重点关注type、key、rows和Extra字段
  • 合理添加索引、优化查询语句结构和表设计，避免全表扫描和临时表排序等性能问题

优化慢日志

一些优化 MySQL 慢日志的步骤和策略：

1. 确认开启慢日志并配置：

  • 启用慢查询日志： 确保 slow_query_log 参数设置为 ON。 
    1

    SET GLOBAL slow_query_log = 'ON';
  • 设置慢查询日志文件： 建议指定一个专门的日志文件，方便管理。 
    1

    SET GLOBAL slow_query_log_file = '/path/to/your/slow-query.log';
  • 调整 long_query_time： 根据实际需求，将 long_query_time 设置为一个合理的值。 建议在生产环境中设置为 1-2 秒。 
    1

    SET GLOBAL long_query_time = 1;
  • log_queries_not_using_indexes： 开启这个参数，记录没有使用索引的查询。 这是一个非常有用的参数，可以帮助你发现潜在的索引缺失问题。 
    1

    SET GLOBAL log_queries_not_using_indexes = 'ON';
  • min_examined_row_limit: 设置最小扫描行数。 如果一个查询扫描的行数少于这个值，即使执行时间超过 long_query_time 也不会被记录。 这可以减少日志的冗余，只关注那些扫描大量数据的慢查询。 
    1

    SET GLOBAL min_examined_row_limit = 100;  -- 例如，设置为 100
  • 刷新日志： 修改完参数后，刷新日志，使配置生效。 
    1

    FLUSH LOGS;
    • 持久化配置: 上述 SET GLOBAL 命令只是临时修改，服务器重启后会失效。 要永久修改，需要在 MySQL 的配置文件 (my.cnf 或 my.ini) 中进行设置： 
      1
2
3
4
5
6

      [mysqld]
slow_query_log = ON
slow_query_log_file = /path/to/your/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = ON
min_examined_row_limit = 100
      修改后，需要重启 MySQL 服务器。

2. 分析慢日志：

  • 使用 mysqldumpslow 工具： MySQL 自带的 mysqldumpslow 工具可以帮助你分析慢日志，它能按照查询时间、频率等进行排序，方便你找到最需要优化的 SQL 语句。 
    1
2

    mysqldumpslow -s t -t 10 /path/to/your/slow-query.log  # 按时间排序，显示前 10 条
mysqldumpslow -s c -t 10 /path/to/your/slow-query.log  # 按出现次数排序，显示前 10 条
  • 使用第三方工具： 有很多第三方工具可以更方便地分析慢日志，例如 pt-query-digest (Percona Toolkit)、MySQL Enterprise Monitor 等。 这些工具通常提供更友好的界面和更强大的分析功能。
  • 人工分析： 在慢日志文件中，仔细阅读每一条慢查询语句，理解其执行逻辑，找到潜在的性能瓶颈。

3. 优化慢 SQL 语句：

  • 使用 EXPLAIN 分析查询： 对于慢查询语句，首先使用 EXPLAIN 命令分析其执行计划，了解 MySQL 如何执行这条语句。 重点关注 type、key、rows 和 Extra 列，找出性能瓶颈。 
    1

    EXPLAIN SELECT ... FROM ... WHERE ...;
  • 添加索引： 如果 EXPLAIN 显示没有使用索引或者使用了不合适的索引，考虑添加合适的索引。 索引应该建立在经常用于查询的列上，例如 WHERE 子句、JOIN 条件、ORDER BY 子句等。 注意，不要过度索引，过多的索引会降低写入性能。
  • 优化查询语句：
    • 避免全表扫描： 尽量使用索引来避免全表扫描。
    • 优化 WHERE 子句： 避免在 WHERE 子句中使用函数或表达式，这会导致索引失效。
    • 优化 JOIN 查询： 确保 JOIN 的列上有索引，避免使用笛卡尔积。
    • 避免使用 SELECT *： 只选择需要的列，减少数据传输量。
    • 分页优化： 使用合适的技巧进行分页优化，例如使用书签或延迟关联。
    • 使用 LIMIT 限制结果集： 如果只需要部分数据，使用 LIMIT 限制结果集大小。
    • 批量操作： 对于大量的插入、更新或删除操作，尽量使用批量操作，减少与数据库的交互次数。
  • 重写 SQL 语句： 有时，即使添加了索引，查询性能仍然不佳，这时可能需要重写 SQL 语句，改变查询的逻辑，以获得更好的性能。
  • 优化表结构： 如果表结构设计不合理，也会影响查询性能。 例如，可以考虑使用合适的数据类型、进行表分区、垂直或水平拆分等。
  • 使用查询缓存： 如果查询结果不经常变化，可以使用 MySQL 的查询缓存来缓存查询结果，提高查询速度。 但要注意，查询缓存只对完全相同的查询有效，并且在表数据发生变化时会失效。 MySQL 8.0 以后已经移除了查询缓存。
  • 考虑使用存储过程或视图： 对于复杂的查询逻辑，可以考虑使用存储过程或视图来封装，简化 SQL 语句，提高可维护性。

4. 硬件和配置优化：

  • 增加服务器硬件资源： 如果服务器的 CPU、内存或磁盘 I/O 成为瓶颈，可以考虑升级硬件。
  • 调整 MySQL 配置参数： 根据服务器的硬件资源和 workload 特点，调整 MySQL 的配置参数，例如 innodb_buffer_pool_size、innodb_log_file_size、max_connections 等。

5. 定期维护：

  • 定期分析表： 使用 ANALYZE TABLE 语句更新表的统计信息，帮助 MySQL 优化器更好地选择执行计划。
  • 定期优化表： 使用 OPTIMIZE TABLE 语句整理表碎片，提高查询性能。
  • 定期清理慢日志： 定期清理过期的慢日志，释放磁盘空间。 可以使用 mysql 命令的 FLUSH LOGS` 命令来轮转日志文件，然后再手动删除旧的日志文件，或者使用 logrotate 工具来自动管理日志。

注意事项:

  • 在生产环境进行优化前，务必在测试环境进行充分的测试。
  • 备份数据后再进行任何可能导致数据丢失的操作。
  • 监控数据库的性能指标，例如 CPU 使用率、内存使用率、磁盘 I/O、查询响应时间等，以便及时发现和解决问题。
  • 优化是一个持续的过程，需要不断地分析、调整和测试。

总结:

优化 MySQL 慢日志是一个涉及多个方面的复杂任务。 通过认真分析慢日志，找出性能瓶颈，并采取相应的优化措施，可以显著提高数据库的查询效率。 记住，没有万能的解决方案，最佳的优化策略需要根据实际情况进行调整。

explain参数

EXPLAIN 语句是 MySQL 中一个非常有用的工具，用于分析查询语句的执行计划。它可以帮助你了解 MySQL 如何执行你的 SQL 查询，从而识别潜在的性能瓶颈并进行优化。下面详细介绍 EXPLAIN 语句返回的各个参数：

基本语法:

1

EXPLAIN SELECT ... FROM ... WHERE ...;

输出结果列及其含义：

EXPLAIN 语句会返回一个表格，每行代表查询执行计划中的一个操作步骤。每个步骤都对应一个参数，下面按重要性排序逐一介绍：

  1. id (重要)

    • 表示查询中执行 SELECT 语句或子查询的顺序。
    • id 值越大，执行优先级越高（先执行）。
    • 如果 id 相同，则从上到下依次执行。
    • 如果 id 为 NULL，通常表示这是一个 union 操作的结果。
    • 理解 id 对于理解查询的执行顺序至关重要。

  2. select_type (重要)

    • 表示 SELECT 语句的类型，也就是查询的复杂程度。 常见的类型包括：
    • SIMPLE: 简单查询，不包含子查询或 UNION。
    • PRIMARY: 最外层的 SELECT 查询。
    • SUBQUERY: SELECT 或 WHERE 子句中的子查询。
    • DERIVED: 在 FROM 子句中的子查询（派生表）。 MySQL需要创建一个临时表来保存子查询的结果。
    • UNION: UNION 语句中的第二个或之后的 SELECT 语句。
    • UNION RESULT: 从 UNION 的匿名临时表检索结果。
    • DEPENDENT SUBQUERY: 子查询依赖于外层查询的数据。 每次执行外层查询，都会重新执行子查询。 性能通常较差。
    • DEPENDENT UNION: UNION 中的第二个或后面的SELECT语句，取决于外层查询。
    • MATERIALIZED: 将子查询结果物化成临时表。
    • 注意： DEPENDENT SUBQUERY 通常是性能瓶颈的信号，应该尽量避免。

  3. table (重要)

    • 表示查询访问的表名。 如果查询是基于临时表或者派生表，这里显示的是临时表或派生表的名称，例如 <derived2> 表示由 id 为 2 的查询派生出的表。
    • 理解表名是理解查询如何访问数据的关键。

  4. partitions

    • 如果表是分区表，则显示查询将访问的分区。 对于非分区表，该值为 NULL。

  5. type (非常重要)

    • 表示 MySQL 如何查找表中的行。 这是一个衡量查询性能的重要指标，从最优到最差依次排列：

    • system: 表只有一行记录（理想情况），这是 const 类型的一个特例。

    • const: 使用主键或唯一索引的等值查询。 MySQL 可以快速定位到唯一的一行记录。

    • eq_ref: 在关联查询中，使用主键或唯一索引进行连接。 对于前表的每一行，后表都只有一行匹配。 效率很高。

    • ref: 使用非唯一索引或唯一索引的非唯一前缀进行查找。 可能会找到多行记录。

    • fulltext: 使用全文索引。

    • ref_or_null: 类似 ref，但 MySQL 需要额外搜索包含 NULL 值的行。

    • index_merge: 使用多个索引合并来查找行。

    • unique_subquery: 在 IN 子查询中使用唯一索引。

    • index_subquery: 在 IN 子查询中使用非唯一索引。

    • range: 使用索引范围扫描，例如 BETWEEN、>、<、IN 等。

    • index: 扫描整个索引树。 这通常比 ALL 好，因为索引通常比表小。

    • ALL: 全表扫描，性能最差，应该尽量避免。

    • 优化目标: 尽量达到 ref 或更好的类型。 避免 ALL (全表扫描)。

  6. possible_keys

    • MySQL 在查找数据时可能使用哪些索引。 注意，这里列出的索引并不意味着 MySQL 一定会使用它们。
    • 如果该列为 NULL，则表示没有可用的索引。

  7. key (重要)

    • MySQL 实际使用的索引。
    • 如果 MySQL 没有选择任何索引，则该值为 NULL。
    • 如果 possible_keys 有值，但 key 为 NULL，表示 MySQL 认为使用全表扫描比使用索引更有效。

  8. key_len

    • 表示 MySQL 实际使用的索引的长度（字节）。 通过这个值可以计算出具体使用了索引中的哪些列。
    • 计算方式：
    • 字符串：
      • char(n)： n 字节
      • varchar(n)： 如果小于 255 字节， n + 1 字节； 如果大于 255 字节，n + 2 字节
    • 数值类型：
      • TINYINT: 1
      • SMALLINT: 2
      • MEDIUMINT: 3
      • INT: 4
      • BIGINT: 8
    • 时间类型：
      • DATE: 3
      • DATETIME: 8
      • TIMESTAMP: 4
    • 如果索引字段可以为 NULL，则 key_len 需要加 1。

  9. ref

    • 显示哪些列或常量被用于查找索引列上的值。
    • 例如，const 表示使用了常量进行比较， table.column 表示使用了其他表的列进行比较。

  10. rows (非常重要)

    • MySQL 估计需要扫描的行数才能找到所需的结果。 这不是最终返回的行数，而是 MySQL 估计的为了找到结果需要扫描的行数。
    • rows 值越小，查询效率越高。 这是优化 SQL 的一个重要指标。

  11. filtered

    • 表示经过条件过滤后，返回的结果集的百分比。
    • 例如，如果 rows 为 1000，filtered 为 10%，则实际返回的行数为 1000 * 10% = 100 行。

  12. Extra (重要)

    • 包含一些额外的信息，这些信息可以帮助你更好地理解 MySQL 如何执行查询。 常见的取值包括：
      • Using index: 查询使用了覆盖索引，即查询所需的数据可以直接从索引中获得，而不需要回表查询。 性能很好。
      • Using where: MySQL 需要在存储引擎返回行后再进行过滤。 这意味着即使使用了索引，也需要在存储引擎层面进行额外的过滤。
      • Using temporary: MySQL 需要创建一个临时表来保存中间结果。 这通常发生在 GROUP BY 或 ORDER BY 语句中，需要优化。
      • Using filesort: MySQL 需要对结果进行外部排序，而不是使用索引排序。 性能较差，应该尽量避免。
      • Using join buffer (Block Nested Loop): 使用了连接缓冲区，通常发生在连接查询中，表示 MySQL 无法使用索引进行连接，而是将其中一个表的数据加载到缓冲区中进行连接。
      • Impossible WHERE: WHERE 子句中的条件永远为 false，导致没有记录满足条件。
      • Select tables optimized away: 查询优化器已经将查询优化到不需要访问任何表的地步。
      • Distinct: MySQL 发现第一个匹配行后，停止搜索同样的行。
      • Not exists: MySQL 可以对 LEFT JOIN 查询进行优化，一旦找到了匹配 LEFT JOIN 标准的所有行，就不再为前面的行的组合搜索更多的行。

优化建议:

  • 避免 ALL 类型: 优化查询，使其能够利用索引，避免全表扫描。
  • 减少 rows 值: 通过添加合适的索引、优化查询条件等方式，减少 MySQL 需要扫描的行数。
  • 注意 Extra 列: 关注 Using temporary、Using filesort 和 Using join buffer 等提示，这些通常是性能瓶颈所在，需要重点优化。
  • 合理使用索引: 添加索引可以加快查询速度，但过多的索引会降低写入性能，并占用额外的存储空间。 因此，需要根据实际情况，合理选择索引。
  • 优化查询语句: 避免使用复杂的查询语句，尽量将查询分解成多个简单的查询。
  • 定期分析表: 使用 ANALYZE TABLE 语句可以更新表的统计信息，帮助 MySQL 更好地选择执行计划。

示例:

1

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

通过分析 EXPLAIN 的输出结果，可以了解 MySQL 如何执行这个查询，是否使用了索引，扫描了多少行，以及是否存在性能瓶颈。 然后，可以根据分析结果，对查询进行优化，例如添加索引，优化查询条件等。