使用MYSQL Report分析数据库性能（中）

顾翔

发布于 2025-08-11 11:19:05

15500

代码可运行

文章被收录于专栏：啄木鸟软件测试啄木鸟软件测试

运行总次数：0

代码可运行

1.索引报表

根据上一节的提示做如下设置。由于有MyISAM表，但使用率很低，可以减小键缓存大小以节省内存

SET GLOBAL key_buffer_size =4*1024*1024; --设置为4MB

永久设置my.ini(通过SHOW VARIABLES LIKE 'datadir';获得)

[mysqld]
key_buffer_size=4M

查看哪些表是MyISAM引擎：

SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE engine='MyISAM';

结果为空，所以考虑转换为InnoDB：

ALTER TABLE user ENGINE=InnoDB

由于MyISAM表是只不是读的，所以不设置

LOAD INDEX INTO CACHE user;

Table	Op	Msg_type	Msg_text
chatgptebusiness.user	preload_keys	note	The storage engine for the table doesn't support preload_keys

查看键缓存配置

SHOW VARIABLES LIKE 'key%';

Variable_name	Value（上篇的值）
key_buffer_size	4,194,304（16,777,216）
key_cache_age_threshold	300（1024）
key_cache_block_size	1024（300）
key_cache_division_limit	100（100）
key_cache_file_hash_size	512
key_cache_segments	0

查看键缓存运行状态

SHOW GLOBAL STATUS LIKE 'Key%';

Variable_name	Value
Key_blocks_not_flushed	0
Key_blocks_unused	3341
Key_blocks_used	0
Key_blocks_warm	0
Key_read_requests	0
Key_reads	0
Key_write_requests	0
Key_writes	0

解决1:键缓存完全失效

问题2:缓存利用率低但配置过大

-- 将键缓存从16MB降到4MB
SET GLOBAL key_buffer_size = 4 * 1024 * 1024;
-- 永久配置(my.cnf)
[mysqld]
key_buffer_size = 4M

键缓存优化策略

最佳配置计算

-- 计算理想键缓存大小
SELECT
CONCAT(ROUND(SUM(INDEX_LENGTH)/1024/1024, 2), ' MB') AS ideal_size
FROM information_schema.TABLES
WHERE ENGINE = 'MyISAM';

显示null

多键缓存配置(MariaDB专用)

-- 1. 创建专用缓存
SET GLOBAL mycache.key_buffer_size = 64 * 1024 * 1024;
-- 2. 分配表到缓存
CACHE INDEX chatgptebusiness.user IN mycache;

Table	Op	Msg_type	Msg_text
chatgptebusiness.user	assign_to_keycache	note	The storage engine for the table doesn't support assign_to_keycache

-- 3. 预热缓存
LOAD INDEX INTO CACHE user;

Table	Op	Msg_type	Msg_text
chatgptebusiness.user	preload_keys	note	The storage engine for the table doesn't support preload_keys

完全禁用键缓存（若无MyISAM表）：有MyISAM表

监控转变：

-- 关注 InnoDB 缓冲池
SHOW ENGINE INNODB STATUS;
"Type"	"Name"	"Status"
"InnoDB"	""	"
=====================================
2025-08-05 13:06:58 0x4738 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 37 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 740 srv_idle
srv_master_thread log flush and writes: 741
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 14
OS WAIT ARRAY INFO: signal count 12
RW-shared spins 13, rounds 367, OS waits 12
RW-excl spins 6, rounds 43, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 28.23 RW-shared, 7.17 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 3910271
Purge done for trx's n:o < 3909988 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283084118929552, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: native aio handle (insert buffer thread)
I/O thread 1 state: native aio handle (log thread)
I/O thread 2 state: native aio handle (read thread)
I/O thread 3 state: native aio handle (read thread)
I/O thread 4 state: native aio handle (read thread)
I/O thread 5 state: native aio handle (read thread)
I/O thread 6 state: native aio handle (write thread)
I/O thread 7 state: native aio handle (write thread)
I/O thread 8 state: native aio handle (write thread)
I/O thread 9 state: native aio handle (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1643 OS file reads, 227 OS file writes, 55 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 305, seg size 307, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 35265617625
Log flushed up to   35265617625
Pages flushed up to 35265617625
Last checkpoint at  35265617616
0 pending log flushes, 0 pending chkp writes
29 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4311744512
Dictionary memory allocated 741184
Buffer pool size   256864
Free buffers       255375
Database pages     1489
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1343, created 146, written 194
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1489, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   32108
Free buffers       31961
Database pages     147
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 143, created 4, written 22
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 147, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   32108
Free buffers       31976
Database pages     132
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 132, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 132, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   32108
Free buffers       31996
Database pages     112
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 112, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 112, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   32108
Free buffers       31839
Database pages     269
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 255, created 14, written 16
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 269, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   32108
Free buffers       31943
Database pages     165
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 165, created 0, written 3
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 165, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   32108
Free buffers       31909
Database pages     199
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 199, created 0, written 10
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 199, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   32108
Free buffers       31883
Database pages     225
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 161, created 64, written 72
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 225, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   32108
Free buffers       31868
Database pages     240
Old database pages 0
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 176, created 64, written 70
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 240, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=8244, Main thread ID=1424, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
"
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
"Variable_name"	"Value"
"Innodb_buffer_pool_dump_status"	""
"Innodb_buffer_pool_load_status"	"Buffer pool(s) load completed at 250804 11:04:31"
"Innodb_buffer_pool_resize_status"	""
"Innodb_buffer_pool_load_incomplete"	"OFF"
"Innodb_buffer_pool_pages_data"	"992"
"Innodb_buffer_pool_bytes_data"	"16252928"
"Innodb_buffer_pool_pages_dirty"	"0"
"Innodb_buffer_pool_bytes_dirty"	"0"
"Innodb_buffer_pool_pages_flushed"	"201"
"Innodb_buffer_pool_pages_free"	"0"
"Innodb_buffer_pool_pages_misc"	"11"
"Innodb_buffer_pool_pages_total"	"1003"
"Innodb_buffer_pool_read_ahead_rnd"	"0"
"Innodb_buffer_pool_read_ahead"	"0"
"Innodb_buffer_pool_read_ahead_evicted"	"91"
"Innodb_buffer_pool_read_requests"	"12023"
"Innodb_buffer_pool_reads"	"1515"
"Innodb_buffer_pool_wait_free"	"0"
"Innodb_buffer_pool_write_requests"	"963"

数据获取方法

1. 实时监控命令

SHOW GLOBAL STATUS WHERE VARIABLE_NAME IN ('Questions', 'Com_%', 'Queries', 'Slow_queries');

Variable_name	Value
Queries	24626
Questions	24626
Slow_queries	0

2.操作报表

生成类似报表的SQL查询

-- 查看所有Com_开头的状态变量
SHOW GLOBAL STATUS LIKE 'Com_%';
"Variable_name"	"Value"
"Com_admin_commands"	"6185"
"Com_alter_db"	"0"
"Com_alter_db_upgrade"	"0"
"Com_alter_event"	"0"
"Com_alter_function"	"0"
"Com_alter_procedure"	"0"
"Com_alter_server"	"0"
"Com_alter_sequence"	"0"
"Com_alter_table"	"1"
"Com_alter_tablespace"	"0"
"Com_alter_user"	"0"
"Com_analyze"	"0"
"Com_assign_to_keycache"	"1"
"Com_backup"	"0"
"Com_backup_lock"	"0"
"Com_begin"	"0"
"Com_binlog"	"0"
"Com_call_procedure"	"0"
"Com_change_db"	"14"
"Com_change_master"	"0"
"Com_check"	"0"
"Com_checksum"	"0"
"Com_commit"	"0"
"Com_compound_sql"	"0"
"Com_create_db"	"0"
"Com_create_event"	"0"
"Com_create_function"	"0"
"Com_create_index"	"0"
"Com_create_package"	"0"
"Com_create_package_body"	"0"
"Com_create_procedure"	"0"
"Com_create_role"	"0"
"Com_create_sequence"	"0"
"Com_create_server"	"0"
"Com_create_table"	"0"
"Com_create_temporary_table"	"0"
"Com_create_trigger"	"0"
"Com_create_udf"	"0"
"Com_create_user"	"0"
"Com_create_view"	"0"
"Com_dealloc_sql"	"0"
"Com_delete"	"0"
"Com_delete_multi"	"0"
"Com_do"	"0"
"Com_drop_db"	"0"
"Com_drop_event"	"0"
"Com_drop_function"	"0"
"Com_drop_index"	"0"
"Com_drop_procedure"	"0"
"Com_drop_package"	"0"
"Com_drop_package_body"	"0"
"Com_drop_role"	"0"
"Com_drop_server"	"0"
"Com_drop_sequence"	"0"
"Com_drop_table"	"0"
"Com_drop_temporary_table"	"0"
"Com_drop_trigger"	"0"
"Com_drop_user"	"0"
"Com_drop_view"	"0"
"Com_empty_query"	"0"
"Com_execute_immediate"	"0"
"Com_execute_sql"	"0"
"Com_flush"	"0"
"Com_get_diagnostics"	"0"
"Com_grant"	"0"
"Com_grant_role"	"0"
"Com_ha_close"	"0"
"Com_ha_open"	"0"
"Com_ha_read"	"0"
"Com_help"	"0"
"Com_insert"	"0"
"Com_insert_select"	"0"
"Com_install_plugin"	"0"
"Com_kill"	"0"
"Com_load"	"0"
"Com_lock_tables"	"0"
"Com_multi"	"0"
"Com_optimize"	"0"
"Com_preload_keys"	"3"
"Com_prepare_sql"	"0"
"Com_purge"	"0"
"Com_purge_before_date"	"0"
"Com_release_savepoint"	"0"
"Com_rename_table"	"0"
"Com_rename_user"	"0"
"Com_repair"	"0"
"Com_replace"	"0"
"Com_replace_select"	"0"
"Com_reset"	"0"
"Com_resignal"	"0"
"Com_revoke"	"0"
"Com_revoke_all"	"0"
"Com_revoke_role"	"0"
"Com_rollback"	"0"
"Com_rollback_to_savepoint"	"0"
"Com_savepoint"	"0"
"Com_select"	"18552"
"Com_set_option"	"18562"
"Com_show_authors"	"0"
"Com_show_binlog_events"	"0"
"Com_show_binlogs"	"0"
"Com_show_charsets"	"0"
"Com_show_collations"	"0"
"Com_show_contributors"	"0"
"Com_show_create_db"	"0"
"Com_show_create_event"	"0"
"Com_show_create_func"	"0"
"Com_show_create_package"	"0"
"Com_show_create_package_body"	"0"
"Com_show_create_proc"	"0"
"Com_show_create_table"	"0"
"Com_show_create_trigger"	"0"
"Com_show_create_user"	"0"
"Com_show_databases"	"0"
"Com_show_engine_logs"	"0"
"Com_show_engine_mutex"	"0"
"Com_show_engine_status"	"1"
"Com_show_errors"	"0"
"Com_show_events"	"0"
"Com_show_explain"	"0"
"Com_show_fields"	"0"
"Com_show_function_status"	"0"
"Com_show_generic"	"0"
"Com_show_grants"	"0"
"Com_show_keys"	"0"
"Com_show_master_status"	"0"
"Com_show_open_tables"	"0"
"Com_show_package_status"	"0"
"Com_show_package_body_status"	"0"
"Com_show_plugins"	"0"
"Com_show_privileges"	"0"
"Com_show_procedure_status"	"0"
"Com_show_processlist"	"0"
"Com_show_profile"	"0"
"Com_show_profiles"	"0"
"Com_show_relaylog_events"	"0"
"Com_show_slave_hosts"	"0"
"Com_show_slave_status"	"0"
"Com_show_status"	"4"
"Com_show_storage_engines"	"0"
"Com_show_table_status"	"0"
"Com_show_tables"	"0"
"Com_show_triggers"	"0"
"Com_show_variables"	"1"
"Com_show_warnings"	"0"
"Com_shutdown"	"0"
"Com_signal"	"0"
"Com_start_all_slaves"	"0"
"Com_start_slave"	"0"
"Com_stmt_close"	"0"
"Com_stmt_execute"	"0"
"Com_stmt_fetch"	"0"
"Com_stmt_prepare"	"0"
"Com_stmt_reprepare"	"0"
"Com_stmt_reset"	"0"
"Com_stmt_send_long_data"	"0"
"Com_stop_all_slaves"	"0"
"Com_stop_slave"	"0"
"Com_truncate"	"0"
"Com_uninstall_plugin"	"0"
"Com_unlock_tables"	"0"
"Com_update"	"0"
"Com_update_multi"	"0"
"Com_xa_commit"	"0"
"Com_xa_end"	"0"
"Com_xa_prepare"	"0"
"Com_xa_recover"	"0"
"Com_xa_rollback"	"0"
"Com_xa_start"	"0"
"Compression"	"OFF"
-- 或者使用查询（在MySQL 5.7及以上或MariaDB中）：
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME LIKE 'Com_%';

1146 - Table 'performance_schema.global_status' doesn't exist

报表中的’Com_’（总命令数）实际上是所有Com_变量的总和（注意：不包括’Com_quit’，因为’Com_quit’是退出命令，不属于查询命令）。

问题1:短连接风暴

启用连接池：

[mysqld]
thread_handling = pool-of-threads
thread_pool_size = 32
thread_pool_max_threads = 1000

调整超时参数：

SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;

问题2: 非业务命令泛滥

禁用不必要命令：

REVOKE SUPER ON *.* FROM 'root'@'localhost'; --（最好不用）
REVOKE SHOW DATABASES ON *.* FROM 'root'@'localhost';

缓存元数据查询：

SET GLOBAL information_schema_stats_expiry = 86400;

1193 - Unknown system var'iable 'information_schema_stats_expiry分析：information_schema_stats_expiry 是 MySQL 8.0.0 引入的变量，用于控制 INFORMATION_SCHEMA 统计信息的缓存时间。

审计命令来源

SELECT * FROM performance_schema.events_statements_history
WHERE SQL_TEXT LIKE '%set_option%'
ORDER BY TIMER_START DESC
LIMIT 10;

结果为空

问题3: SELECT主导数据操作

查询缓存优化：

SET GLOBAL query_cache_size = 64M;
SET GLOBAL query_cache_min_res_unit = 2048;

注意

SET GLOBAL query_cache_size = 64*1024*1024（不支持64M）

读写分离：

[mysqld]
read_only = ON
# 在从库上

热点查询优化：

-- 找出高频SELECT
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE 'SELECT%'
ORDER BY COUNT_STAR DESC
LIMIT 10;
"DIGEST_TEXT"	"COUNT_STAR"	"AVG_TIMER_WAIT"
"SELECT @@SESSION . `auto_increment_increment` AS `auto_increment_increment` , @@`character_set_client` AS `character_set_client` , @@`character_set_connection` AS `character_set_connection` , @@`character_set_results` AS `character_set_results` , @@`character_set_server` AS `character_set_server` , @@`collation_server` AS `collation_server` , @@`collation_connection` AS `collation_connection` , @@`init_connect` AS `init_connect` , @@`interactive_timeout` AS `interactive_timeout` , @@`license` AS `license` , @@`lower_case_table_names` AS `lower_case_table_names` , @@`max_allowed_packet` AS `max_allowed_packet` , @@`net_write_timeout` AS `net_write_timeout` , @@`performance_schema` AS `performance_schema` , @@`query_cache_size` AS `query_cache_size` , @@`query_cache_type` AS `query_cache_type` , @@`sql_mode` AS `sql_mode` , @@`system_time_zone` AS `system_time_zone` , @@`time_zone` AS `time_zone` , @@`tx_isolation` AS `transaction_isolation` , @@"	"42669"	"220900000"
"SELECT COUNT ( * ) AS `sum` FROM SYSTEM_USER WHERE `username` = ? AND PASSWORD = ? "	"42548"	"777900000"
"SELECT @@SESSION . `tx_isolation` "	"42140"	"91900000"
"SELECT `table_schema` , TABLE_NAME , ENGINE FROM `information_schema` . `tables` WHERE ENGINE = ? "	"1"	"145809900000"
"SELECT `CONCAT` ( `ROUND` ( SUM ( `INDEX_LENGTH` ) / ? / ?, ... ) , ? ) AS `ideal_size` FROM `information_schema` . `TABLES` WHERE ENGINE = ? "	"1"	"417820200000"
"SELECT `VARIABLE_NAME` , `VARIABLE_VALUE` FROM `performance_schema` . `global_status` WHERE `VARIABLE_NAME` LIKE ? "	"1"	"189100000"
"SELECT COUNT ( * ) FROM `information_schema` . `TABLES` WHERE `TABLE_SCHEMA` = ? UNION SELECT COUNT ( * ) FROM `information_schema` . `COLUMNS` WHERE `TABLE_SCHEMA` = ? UNION SELECT COUNT ( * ) FROM `information_schema` . `ROUTINES` WHERE `ROUTINE_SCHEMA` = ? "	"1"	"99159400000"
"SELECT `TABLE_SCHEMA` , TABLE_NAME , `TABLE_TYPE` FROM `information_schema` . `TABLES` WHERE `TABLE_SCHEMA` = ? ORDER BY `TABLE_SCHEMA` , `TABLE_TYPE` "	"1"	"53001700000"
"SELECT `TABLE_SCHEMA` , TABLE_NAME , COLUMN_NAME , `COLUMN_TYPE` FROM `information_schema` . `COLUMNS` WHERE `TABLE_SCHEMA` = ? ORDER BY `TABLE_SCHEMA` , TABLE_NAME "	"1"	"55605700000"
"SELECT DISTINCTROW `ROUTINE_SCHEMA` , `ROUTINE_NAME` , `PARAMS` . `PARAMETER` FROM `information_schema` . `ROUTINES` LEFT JOIN ( SELECT `SPECIFIC_SCHEMA` , `SPECIFIC_NAME` , GROUP_CONCAT ( `CONCAT` ( `DATA_TYPE` , ? , `PARAMETER_NAME` ) ORDER BY `ORDINAL_POSITION` SEPARATOR ? ) `PARAMETER` , `ROUTINE_TYPE` FROM `information_schema` . `PARAMETERS` GROUP BY `SPECIFIC_SCHEMA` , `SPECIFIC_NAME` , `ROUTINE_TYPE` ) `PARAMS` ON `ROUTINES` . `ROUTINE_SCHEMA` = `PARAMS` . `SPECIFIC_SCHEMA` AND `ROUTINES` . `ROUTINE_NAME` = `PARAMS` . `SPECIFIC_NAME` AND `ROUTINES` . `ROUTINE_TYPE` = `PARAMS` . `ROUTINE_TYPE` WHERE `ROUTINE_SCHEMA` = ? ORDER BY `ROUTINE_SCHEMA` "	"1"	"12105600000"

配置优化建议(my.cnf)

[mysqld]
# 连接优化
max_connections = 1000
thread_cache_size = 100
wait_timeout = 300
# 性能优化
query_cache_size = 64M
table_open_cache = 2048
tmp_table_size = 64M
# 监控配置
performance_schema = ON
slow_query_log = ON
long_query_time = 2

3.查询和排序报表

查看这些指标

SHOW GLOBAL STATUS LIKE 'Handler_read%';

Variable_name	Value
Handler_read_first	5
Handler_read_key	18
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	14
Handler_read_rnd_deleted	0
Handler_read_rnd_next	2215029819

SHOW GLOBAL STATUS LIKE 'Sort%';

Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	3
Sort_range	0
Sort_rows	14
Sort_scan	6

‘Handler_read_rnd_next’：全表扫描的次数（对应Scan）。
‘Sort_scan’：全表扫描排序的次数（对应Sort scan）。
‘Sort_range’：范围扫描排序的次数（对应Sort range）。
‘Sort_merge_passes’：排序合并通过次数（对应Sort mrg pass）。 慢查询日志分析

-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
 
-- 捕获1秒以上查询
SET GLOBAL log_queries_not_using_indexes = ON;
   
-- 临时增加缓冲池：
SET GLOBAL innodb_buffer_pool_size = 2*1024*1024*1024;
 
-- 2GB  
-- 索引优化验证：
EXPLAIN
SELECT * FROM `user` WHERE username='cindy' and password='123456'

注意

若版本 < 10.5：不支持 EXPLAIN ANALYZE，需升级或改用 EXPLAIN。

若版本 ≥ 10.5：支持 EXPLAIN ANALYZE。

id	select_type	table	possible_keys	type	key	key_len	"ref	rows	Extra
1	SIMPLE	user	ALL					1001	Using where

#慢查询日志
slow_query_log = 1
slow_query_log_file = C:\xampp\mysql\slow.log
long_query_time = 2
 # 记录超过2秒的查询

配置优化建议(my.cnf)

# 索引优化配置
innodb_flush_neighbors = 0
 
# 减少随机IO影响
innodb_adaptive_hash_index = OFF
 
# 高并发下禁用AHI
# 排序优化
sort_buffer_size = 4M
 
# 增大排序缓冲区
max_sort_length = 1024
 
# 减少排序内存占用
# 监控配置
performance_schema = ON
optimizer_trace = enabled=on

自动索引建议系统：

MariaDB 10.4+：默认启用INDEX_STATISTICS，但统计信息可能延迟更新。

4.查询缓存报表

彻底关闭缓存

SET GLOBAL query_cache_size = 0;

验证

SHOW VARIABLES LIKE 'query_cache%';

Variable_name	Value
query_cache_limit	1048576
query_cache_min_res_unit	2048
query_cache_size	0
query_cache_strip_comments	OFF
query_cache_type	OFF
query_cache_wlock_invalidate	OFF

彻底禁用查询缓存（推荐）

-- 立即生效
SET GLOBAL query_cache_size = 0;
SET GLOBAL query_cache_type = OFF;
-- 永久配置(my.cnf)
[mysqld]
query_cache_type = 0
query_cache_size = 0

InnoDB缓冲池优化

SELECT
ROUND(
(1 - (Variable_value / (SELECT Variable_value
FROM information_schema.global_status
WHERE Variable_name = 'Innodb_buffer_pool_read_requests'))
) * 100, 2) AS hit_rate
FROM information_schema.global_status
WHERE Variable_name = 'Innodb_buffer_pool_reads';

结果

hit_rate
99.99

5.表锁报表和表信息报表

表缓存配置优化

SET GLOBAL table_open_cache = 250;

表定义缓存优化

-- 查看当前表定义缓存状态
SHOW GLOBAL STATUS LIKE 'Open_table_definitions';

Variable_name	Value
Open_table_definitions	366

-- 优化配置（设置为表缓存的1.5-2倍）
SET GLOBAL table_definition_cache = 400;

调整表缓存大小：

SET GLOBAL table_open_cache = 250;
 -- 降低85%内存占用（当前打开161表 → 新缓存大小 ≈161 × 1.5 ≈ 250）

转换存储引擎

ALTER TABLE `user` ENGINE=InnoDB;

配置建议（my.cnf）

# 表缓存设置 (基于当前负载)
table_open_cache = 250
table_definition_cache = 400
# 表锁监控 (默认已开启)
performance_schema = ON

查看行锁状态

SHW ENGINE INNODB STATUS;
"Type"
 
"Name"
  
"Status"
"InnoDB"
 
""
    
"
=====================================
2025-08-04 18:35:33 0x6538 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 972 srv_idle
srv_master_thread log flush and writes: 973
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 143
OS WAIT ARRAY INFO: signal count 53
RW-shared spins 16, rounds 441, OS waits 14
RW-excl spins 7, rounds 43, OS waits 1
RW-sx spins 1, rounds 1, OS waits 0
Spin rounds per wait: 27.56 RW-shared, 6.14 RW-excl, 1.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 3910314
Purge done for trx's n:o < 3910313 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284302175940752, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: native aio handle (insert buffer thread)
I/O thread 1 state: native aio handle (log thread)
I/O thread 2 state: native aio handle (read thread)
I/O thread 3 state: native aio handle (read thread)
I/O thread 4 state: native aio handle (read thread)
I/O thread 5 state: native aio handle (read thread)
I/O thread 6 state: native aio handle (write thread)
I/O thread 7 state: native aio handle (write thread)
I/O thread 8 state: native aio handle (write thread)
I/O thread 9 state: native aio handle (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
511 OS file reads, 199 OS file writes, 34 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 305, seg size 307, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 35265783830
Log flushed up to
 
35265783830
Pages flushed up to 35265783830
Last checkpoint at
 
35265783821
0 pending log flushes, 0 pending chkp writes
24 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 285212672
Dictionary memory allocated 40064
Buffer pool size
 
16054
Free buffers
 
15443
Database pages
 
609
Old database pages 238
Modified db pages
 
0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 462, created 147, written 179
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 609, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1480, Main thread ID=37740, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 319579260
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 330960.56 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
"

6.链接报表与临时报表

监控与进一步分析

使用以下命令监控连接和临时表的状态：

SHOW GLOBAL STATUS LIKE 'Threads_connected'; -- 当前连接数

Variable_name	Value
Threads_connected	1

SHOW GLOBAL STATUS LIKE 'Threads_running'; -- 当前运行线程数

Variable_name	Value
Threads_running	6

SHOW GLOBAL STATUS LIKE 'Created_tmp%'; -- 临时表和文件创建情况

Variable_name	Value
Created_tmp_disk_tables	85
Created_tmp_files	4
Created_tmp_tables	506

紧急连接优化 (立即执行)

SET GLOBAL max_connections = 600;-- 启用线程池 (MariaDB 10.4+)
SET GLOBAL thread_handling = pool-of-threads;

1238 - Variable 'thread_handling' is a read only variable

用配置文件代替

thread_handling = pool-of-threads

SET GLOBAL thread_pool_size = 32;
-- 减少连接超时
SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;

配置优化

# 临时表优化
tmp_table_size = 64M
max_heap_table_size = 64M
#internal_tmp_disk_storage_engine = InnoDB
在MariaDB中。不可以这么设置，默认即为InnoDB
# 文件系统优化
tmpdir = "C:/xampp/tmp" #使用默认

7.线程报表

调查连接失败问题

查询监控连接失败情况

SHOW GLOBAL STATUS LIKE 'Aborted_connects';

Variable_name	Value
Aborted（流产的）_connects	1240

SHOW GLOBAL STATUS LIKE 'Connection_errors%'

Variable_name	Value
Connection_errors_accept	0
Connection_errors_internal	1240
Connection_errors_max_connections	1240
Connection_errors_peer_address	0
Connection_errors_select	0
Connection_errors_tcpwrap	0

配置调整回顾

连接相关：

max_connections=600
thread_cache_size=256
wait_timeout=60
interactive_timeout=60

临时表相关：

tmp_table_size=64M
max_heap_table_size=64M

以前有

thread_cache_size = 4，改为256

wait_timeout = 300 保持300

解决方案：

-- 1. 检查认证错误
SHOW GLOBAL STATUS LIKE 'Connection_errors%';
--同上
-- 2. 优化认证缓存
SET GLOBAL credentials_expired = OFF;

1193 - Unknown system variable 'credentials_expired'

SET GLOBAL disconnect_on_expired_password = OFF;
-- 3. 增加连接积压队列
SET GLOBAL back_log = 200;

238 - Variable 'back_log' is a read only variable

用配置文件代替

back_log = 200

配置优化 (my.cnf):

[mysqld]
# 认证优化
connect_timeout = 15
authentication_policy = ''
 
# 允许所有认证方法
# 连接缓冲
skip_name_resolve = ON
max_connect_errors = 1000000

注意MariaDB 10.4.32显示authentication_policy未知变量，改为

#设置默认认证插件
default_authentication_plugin = mysql_native_password
# 启用所有认证插件（需要先安装）
plugin_load_add = auth_socket
plugin_load_add = auth_ed25519
plugin_load_add = auth_gssapi

网络流量优化

-- 启用协议压缩
SET GLOBAL protocol_compression = ON;
-- 调整包大小
SET GLOBAL max_allowed_packet = 32*1024*1024;
SET GLOBAL net_buffer_length = 32*1024;

注意protocol_compression 是 MySQL 8.0.20+ 引入的变量，MariaDB 不支持此变量

InnoDB缓冲池精细调优 (推荐)

优化点：14.56%空闲页面利用

-- 查看缓冲池页分布
SELECT
PAGE_TYPE,
COUNT(*) AS pages,
ROUND(COUNT(*)*100/(SELECT COUNT(*)
FROM information_schema.INNODB_BUFFER_PAGE),2) AS pct
FROM information_schema.INNODB_BUFFER_PAGE
GROUP BY PAGE_TYPE;

PAGE_TYPE	pages	pct
FILE_SPACE_HEADER	5	0
IBUF_BITMAP	7	0
IBUF_INDEX	1	0
INDEX	131	0.05
INODE	5	0
SYSTEM	258	0.1
TRX_SYSTEM	1	0
UNDO_LOG	110	0.04
UNKNOWN	256346	99.8

-- 调整缓冲池实例数（减少锁竞争）
SET GLOBAL innodb_buffer_pool_instances = 8;

1238 - Variable 'innodb_buffer_pool_instances' is a read only variable

用配置文件代替

innodb_buffer_pool_instances

配置优化:

# 缓冲池优化
innodb_buffer_pool_size = 16M
innodb_buffer_pool_chunk_size = 1M
innodb_lru_scan_depth = 256
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

注意：前面有配置

innodb_buffer_pool_size = 4G #默认256K。保持4G

最终优化建议

立即执行：

# 增加连接积压缓冲
mysql -e "SET GLOBAL back_log = 200;"

1238 - Variable 'back_log' is a read only variable

用配置文件代替

back_log=200

# 启用协议压缩
mysql -e "SET GLOBAL protocol_compression = ON;"
# 1193 - Unknown system variable 'protocol_compression'

24小时内完成：

# 更新my.cnf配置
connect_timeout=15
skip_name_resolve=ON
protocol_compression=ON
innodb_buffer_pool_instances=8
#不支持protocol_compression

8.InnoDB锁报表

获取InnoDB锁信息的方法：

1.查看当前锁等待：

SHOW ENGINE INNODB STATUS; # 在输出的TRANSACTIONS部分查看
------------TRANSACTIONS------------
Trx id counter 3910271
Purge done for trx's n:o < 3909988 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283084118929552, not started
0 lock struct(s), heap size 1128, 0 row lock(s)

2.查看锁等待的详细信息：

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

为空

3.查看当前所有锁：

SELECT * FROM information_schema.INNODB_LOCKS;

为空

4.查看InnoDB事务：

SELECT * FROM information_schema.INNODB_TRX;

为空

InnoDB 锁监控方法

1. 实时锁状态查看

-- 当前锁等待信息
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

为空

-- 所有活跃锁
SELECT * FROM information_schema.INNODB_LOCKS;

为空

-- 事务与锁关联
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

为空

预防性配置优化

[mysqld]
# 事务隔离级别 (推荐RC)
transaction_isolation = READ-COMMITTED
# 锁超时设置
innodb_lock_wait_timeout = 30 #默认50 改为30
# 死锁检测优化
innodb_deadlock_detect = ON
innodb_print_all_deadlocks = ON
# 行锁设置
innodb_row_lock_timeout = 10

unknown variable 'innodb_row_lock_timeout=10'

改为

innodb_lock_wait_timeout = 10

Deepseek回答有误

高级锁监控技术

1.实时锁可视化

- 锁等待链分析
SELECT
CONCAT('thread ', r.trx_mysql_thread_id,
' waiting for lock held by thread ', b.trx_mysql_thread_id) AS lock_wait_chain
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

空记录

2. 历史锁竞争分析

-- 锁等待历史统计
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT_READ,
COUNT_WRITE,
COUNT_TIMER_WAIT/1000000000 AS total_wait_sec
FROM performance_schema.table_lock_waits_summary_by_table;

1054 - Unknown column 'COUNT_TIMER_WAIT' in 'field list'

COUNT_TIMER_WAIT 改为SUM_TIMER_WAIT

OBJECT_SCHEMA	OBJECT_NAME	COUNT_READ	total_wait_sec
mysql	plugin	0	0
mysql	servers	0	0
mysql	db	0	0
mysql	proxies_priv	0	0
mysql	roles_mapping	0	0
mysql	global_priv	0	0
mysql	time_zone_leap_second	0	0
mysql	time_zone_name	0	0
mysql	time_zone	0	0
mysql	time_zone_transition_type	0	0
mysql	time_zone_transition	0	0
mysql	tables_priv	0	0
mysql	columns_priv	0	0
mysql	procs_priv	0	0
mysql	func	0	0
mysql	event	0	0
mysql	gtid_slave_pos	0	0
chatgptebusiness	user	26705	26.5247
mysql	table_stats	0	0
mysql	column_stats	0	0
mysql	index_stats	0	0
performance_schema	table_lock_waits_summary_by_table	0	0

9.InnoDB其他报表

性能优化策略

1. 读优化（关键重点）

-- 识别高频读取表
SELECT
TABLE_SCHEMA,
TABLE_NAME,
ROWS_READ
FROM information_schema.TABLE_STATISTICS
ORDER BY ROWS_READ DESC
LIMIT 5;

空记录

2.缓冲池优化

-- 增加缓冲池实例（减少锁竞争）
SET GLOBAL innodb_buffer_pool_instances = 8;

1238 - Variable 'innodb_buffer_pool_instances' is a read only variable

my.ini添加

innodb_buffer_pool_instances=8

-- 分析TOP读取查询
SELECT DIGEST_TEXT, ROWS_READ_EXAMINED, COUNT_STAR
FROM performance_schema.events_statements_summary_by_digest
ORDER BY ROWS_READ_EXAMINED DESC
LIMIT 10;

1054 - Unknown column 'ROWS_READ_EXAMINED' in 'field list'

# 读优化配置
innodb_read_ahead_threshold = 64
 
# 提高预读效率
innodb_random_read_ahead = ON
 
# 启用随机预读
read_buffer_size = 4M
 
# 增大读取缓冲区
# 缓冲池管理
innodb_buffer_pool_size = 16M
innodb_buffer_pool_dump_now = ON
 
# 立即转储缓冲池状态
innodb_buffer_pool_load_now = ON
 
# 启动时立即加载
# 性能监控
innodb_monitor_enable = all

前面设置innodb_buffer_pool_size = 4G，保持4G

前面设置read_buffer_size=256K 改为4M

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-08-05，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库