MySql优化 自已总结

MySQL优化是数据库管理中至关重要的环节，它关系到系统性能和响应速度。下面将详细介绍MySQL自带的慢查询日志分析工具mysqldumpslow及其使用方法，以及如何使用EXPLAIN来分析SQL查询。 MySQL的慢查询日志（slow query log）是一个非常有用的工具，它记录了执行时间超过一定阈值的SQL语句。默认情况下，如果一个查询的执行时间超过10秒，它就会被记录到慢查询日志中。要在MySQL配置文件my.cnf中启用慢查询日志，只需添加以下行： ```ini log-slow-queries = [slow_query_log_filename] ``` 其中，`[slow_query_log_filename]`是你指定的日志文件路径。如果你希望将这个阈值降低到5秒，可以添加： ```ini long_query_time = 5 ``` mysqldumpslow是一个用于分析慢查询日志的工具。它提供了多种参数，例如： - `-v`, `-v+`: 详细输出 - `-d`, `-d+`: 调试模式 - `-s`: 排序依据，如`t`表示总时间，`l`表示锁定时间等 - `-r`: 倒序排序 - `-t`: 显示最高n个查询 - `-a`: 显示完整的数字和字符串，而不是简写 - `-n`: 抽象数字至少显示n位 - `-g`: 根据指定字符串筛选查询 - `-h`: 指定数据库服务器主机名 - `-i`: 服务器实例名称 - `-l`: 不减去锁定时间 使用这些参数，你可以根据具体需求定制报告，找出耗时最长的查询进行优化。 接下来，我们讨论MySQL的`EXPLAIN`命令。`EXPLAIN`用于查看MySQL如何执行SELECT语句，帮助识别潜在的性能问题。在你的查询前加上`EXPLAIN`，比如： ```sql EXPLAIN SELECT surname, first_name FROM a, b WHERE a.id = b.id; ``` 分析结果会展示一系列列，包括： - `id`: 查询中的唯一标识符，对于子查询会有递增的值。 - `select_type`: 查询类型，如SIMPLE（简单查询），SUBQUERY（子查询），DERIVED（派生表）等。 - `table`: 查询涉及的表。 - `type`: 访问类型，从最优到最差的顺序是：system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL。 - `possible_keys`: 可能使用到的索引。 - `key`: 实际使用的索引。 - `key_len`: 使用到的索引长度。 - `ref`: 使用哪个列或常量与键匹配。 - `rows`: 预计要扫描的行数。 - `Extra`: 其他信息，如Using where, Using index, Using filesort等。 `Extra`列中的信息非常重要，例如`Using where`表示WHERE条件用于索引，而`Using filesort`则表示数据必须进行额外的排序操作，这通常会导致性能下降。 通过分析`EXPLAIN`的结果，你可以发现是否正确利用了索引，是否存在全表扫描，以及是否需要调整索引策略以优化查询性能。例如，如果`type`是`ALL`，可能意味着没有使用索引，这时可以考虑创建适当的索引；如果看到`Using filesort`，可能需要重新组织查询以避免排序。 MySQL优化涉及多个方面，包括但不限于合理的索引设计、优化查询语句、调整系统参数和使用分析工具。慢查询日志和`EXPLAIN`是诊断和优化的重要工具，通过它们可以深入理解查询执行的过程，从而提升数据库的运行效率。