MySQLTuner 和 Explain Analyze：新手必备的 MySQL 优化工具

在数据库的世界里，优化性能是一项至关重要的任务。就像汽车需要定期保养一样，数据库也需要持续地进行调优以保持最佳状态。而 MySQLTuner 和 Explain Analyze 就是两款帮助你轻松维护 MySQL 数据库的利器，尤其是对于新手来说，更是必不可少的工具。

1. MySQLTuner 和 Explain Analyze 的异同

MySQLTuner：总体诊断与建议

MySQLTuner 是一款广受好评的 MySQL 优化工具，它可以全面检查你的数据库，识别潜在的性能瓶颈并提供改进建议。它的优势在于：

1. 全面检查：它会检查数百个指标，包括日志文件、存储引擎、安全设置和性能参数。

2. 简洁报告：它会生成一份易于理解的报告，突出显示问题并提供优化建议。

3. 自动优化：它还可以自动应用一些优化，比如调整缓冲池大小或禁用未使用的索引。

Explain Analyze：查询性能分析

Explain Analyze 是 MySQL 8 中引入的一个强大的工具，它可以分析查询的执行计划，并提供有关其实际执行情况的信息。它的特点包括：

1. 查询优化：它可以识别慢查询并帮助你找到优化它们的方法。

2. 实际执行信息：它会显示查询的实际执行计划，包括使用的索引、行数和时间成本。

3. 细粒度分析：它可以逐行分析查询，让你深入了解其执行过程。

2. 新手更适合哪一个？

对于新手来说，MySQLTuner 是一个更容易上手的选择。它会自动检查你的数据库并给出全面的报告，即使你对 MySQL 优化一窍不通，也可以理解和应用它的建议。

而 Explain Analyze 则更适合对 MySQL 查询优化有一定了解的中级用户。它需要你手动分析查询计划，并根据洞察力进行优化。

3. MySQLTuner 的使用步骤

安装

将以下脚本下载到你的 MySQL 服务器上：

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

运行

使用以下命令运行 MySQLTuner：

./mysqltuner.pl --socket /var/lib/mysql/mysql.sock

它会提示你输入 MySQL 管理员密码。输入后，它将生成一份详细的报告。

4. Explain Analyze 的使用步骤

启用

在你的 MySQL 配置文件中（通常是 /etc/mysql/my.cnf）中，添加以下行：

optimizer_trace="enabled=on"

分析查询

使用以下语法对查询执行 Explain Analyze：

EXPLAIN ANALYZE 你的查询

它会在查询结果中包含一个名为 "execution\_plan" 的额外的列，其中包含查询执行的详细信息。

5. 实例演示

MySQLTuner 报告示例

1.------------------------------------------

MySQLTuner 1.7.4 - Major Hayden

1.------------------------------------------

MySQLTuner is designed to be used on the mysql command line. Not on mysql.sock

Please enter your MySQL administrative login: root

Please enter your MySQL administrative password:

Server version: 5.5.54-MariaDB-31-linux-x86_64

Server uptime: 1 hours 15 minutes 2 seconds

1.----------------------------------------------------

Recommendations are advisory only, and individually tuned for your system and your application(s)

Performance can vary between different OS/architectures. Value/Variable | Default/Actual | 10% / 30% Rule

1.----------------------------------------------------

General recommendations:

Control warning is active!

The maximum number of connections (max_connections) is set excessively high. Connections are a scarce resource; limit them!

max_connections | 151 | 25 / 93

General recommendations:

Control warning is active!

Maximum possible memory usage: 4.9G (244.13% of installed RAM).

Either reduce memory overcommit, or install more RAM into this host to avoid swapping.

Global / Global limit | 2G / 2G

General recommendations:

The slow query log is disabled.

Having it enabled with a reasonable value will help detecting potentially problematic queries and their execution times.

slow_query_log | 0 / 0

Binary log section:

The binary log is disabled. Replication is not possible.

Binary logging is recommended for replication and point-in-time recovery.

Log is created upon server start with default settings, it may require tuning for write heavy workloads.

Binary logging | no / NO

Variables to adjust:

query_cache_limit | 0 / 2M

query_cache_size | 0 / 128M

transaction_isolation | REPEATABLE-READ / READ-COMMITTED

Key cache section:

Key cache miss rate: 0.00% (4005 hits / 2 misses)

Key cache ratio: 0.82

Key cache in/out count is very low.

Hint: Try to increase innodb_buffer_pool_size and/or size of the data files. Value/Variable | Default/Actual | 10% / 30% Rule

1.----------------------------------------------------

Short summary:

Key cache usage ~82%

73.3% of queries served from key cache

Key cache hit rate: 99.98%

Key cache miss rate: 0.00%

Cache inspection size: 436M

Global / Global limit | 1G / 1G

MyISAM / XtraDB / InnoDB memory usage | 15.0M / 15.0M

InnoDB buffer pool | 128M / 128M

MyISAM / XtraDB memory usage | 15.0M / 15.0M

Full table scans are active! Full table scans always hurt performance!

You should consider adding an index on the used columns.

There are performance schema tables that are disabled:

threads

file_instances

file_summary_by_instance

file_summary_by_event_name

partitions

partition_instances

partition_summary

table_io_waits

table_lock_waits

There are no Maatkit recommendations yet. Maatkit recommendations:

There are no mysqltuner cache recommendations yet. MySQLTuner cache recommendations:

Hidden / obscure performance recommendations:

server_id = 1

log_slow_extra

Timeout longer than 180 seconds. Increase Query Timeout or reduce transactions executing over the limit. thread_connect_timeout='600', thread_read_timeout='600', thread_write_timeout='600'

Timeout longer than 180 seconds. Increase Query Timeout or reduce transactions executing over the limit. innodb_lock_wait_timeout=600

Security recommendations:

/home/.bash_history contains meaningful usernames

/home/.bash_history contains root and MySQL passwords

/home/.bash_history contains uncommented root password line

/home/.bashrc contains uncommented root password line

MySQL root password is both empty and not set in a config file.

root can connect from external hosts with no password

mysql dataset is accessible from localhost without a password

mysql dataset is accessible from external hosts without a password

No warning for severity scaling (policies=Warning, errors=Unsafe-Ignore)

Performance schema section:

Some performance schema tables are idle.

Consider disabling them or reducing their differential stats logging.

Config file section:

Config file does not include server_id. Warned about prior to MySQL 8. server_id=1

Variables to adjust:

long_query_time | 10 / 180

OK

Explain Analyze 执行计划示例

对于查询 SELECT FROM users WHERE name LIKE '%John%' 的 Explain Analyze 执行计划：

execution_plan

SELECT FROM users WHERE name LIKE '%John%'

└─Table scan on users

└─Filter: index_name_of_name LIKE '%John%'

rows: 5

filtered: 100%

cost: 100

这个计划表明，查询正在对 users 表进行全表扫描，并使用索引 index_name_of_name 过滤行。它扫描了 5 行，过滤了 100% 的行，查询成本为 100。

互动内容

1. 你更喜欢哪款优化工具？为什么？

2. 你在使用 MySQL 优化工具时遇到过哪些

3. 你有哪些 MySQL 优化技巧可以分享？