MySQL 数据库性能分析的方法与命令
1. SHOW STATUS
- 命令:
SHOW GLOBAL STATUS;
- 结果:
返回 MySQL 服务器的各项状态信息,如连接数、查询数等。 - 示例:
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Threads_connected | 10 | | Questions | 1000 | | Uptime | 3600 | +--------------------------+-------+
2. SHOW VARIABLES
- 命令:
SHOW VARIABLES;
- 结果:
返回当前 MySQL 配置的所有变量及其值。 - 示例:
+--------------------------+---------------------+ | Variable_name | Value | +--------------------------+---------------------+ | max_connections | 151 | | innodb_buffer_pool_size | 134217728 | +--------------------------+---------------------+
3. EXPLAIN
- 命令:
EXPLAIN SELECT * FROM your_table WHERE condition;
- 结果:
返回 SQL 查询的执行计划,包括使用的索引、连接类型等。 - 示例:
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+ | 1 | SIMPLE | your_table | range | idx_col | idx_col | 4 | NULL | 100 | Using where | +----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
4. SHOW PROCESSLIST
- 命令:
SHOW PROCESSLIST;
- 结果:
返回当前正在执行的查询和连接信息。 - 示例:
+----+------+-----------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+-------+------------------+ | 1 | root | localhost | test | Query | 0 | NULL | SELECT * FROM ...| +----+------+-----------------+------+---------+------+-------+------------------+
5. SHOW ENGINE INNODB STATUS
- 命令:
SHOW ENGINE INNODB STATUS;
- 结果:
返回 InnoDB 存储引擎的详细状态信息,包括锁、事务等。 - 示例:
====================== ROW OPERATIONS ====================== ... LOCK WAIT ...
6. 慢查询日志
- 设置:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; -- 设置阈值为 2 秒
- 结果:
记录执行时间超过设定阈值的查询。 - 示例:
# Time: 2024-08-24T07:53:00.000000Z # User@Host: root[root] @ localhost [] Id: 1 # Query_time: 2.123456 Lock_time: 0.000000 Rows_sent: 10 Rows_examined: 1000 SELECT * FROM your_table WHERE condition;
7. SHOW TABLE STATUS
- 命令:
SHOW TABLE STATUS;
- 结果:
返回表的状态信息,如行数、数据大小等。 - 示例:
+------------+--------+---------+------------+-------+----------------+-------------+ | Name | Engine | Version | Row_format | Rows | Data_length | Index_length| +------------+--------+---------+------------+-------+----------------+-------------+ | your_table | InnoDB | 10 | Compact | 1000 | 8192 | 16384 | +------------+--------+---------+------------+-------+----------------+-------------+
8. Performance Schema
- 查询示例:
SELECT * FROM performance_schema.events_statements_summary_by_digest;
- 结果:
返回 SQL 语句的执行统计信息。 - 示例:
+-----------------------------------+---------+----------+----------+ | DIGEST | COUNT_STAR | SUM_TIMER_WAIT | AVG_TIMER_WAIT | +-----------------------------------+---------+----------+----------+ | 0x1234567890ABCDEF | 100 | 5000000 | 50000 | +-----------------------------------+---------+----------+----------+