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;
查看键缓存配置
SHOW VARIABLES LIKE 'key%';
查看键缓存运行状态
SHOW GLOBAL STATUS LIKE 'Key%';
解决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;
-- 3. 预热缓存
LOAD INDEX INTO CACHE user;
完全禁用键缓存（若无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"
数据获取方法
- 实时监控命令
SHOW GLOBAL STATUS WHERE VARIABLE_NAME IN ('Questions', 'Com_%', 'Queries', 'Slow_queries');
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 = 6410241024（不支持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%';
SHOW GLOBAL STATUS LIKE 'Sort%';
1.‘Handler_read_rnd_next’：全表扫描的次数（对应Scan）。
2.‘Sort_scan’：全表扫描排序的次数（对应Sort scan）。
3.‘Sort_range’：范围扫描排序的次数（对应Sort range）。
4.‘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。
#慢查询日志
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%';
彻底禁用查询缓存（推荐）
-- 立即生效
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';
结果
5.表锁报表和表信息报表
表缓存配置优化
SET GLOBAL table_open_cache = 250;
表定义缓存优化
-- 查看当前表定义缓存状态
SHOW GLOBAL STATUS LIKE 'Open_table_definitions';
-- 优化配置（设置为表缓存的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'; -- 当前连接数
SHOW GLOBAL STATUS LIKE 'Threads_running'; -- 当前运行线程数
SHOW GLOBAL STATUS LIKE 'Created_tmp%'; -- 临时表和文件创建情况
紧急连接优化 (立即执行)
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';
SHOW GLOBAL STATUS LIKE 'Connection_errors%'
配置调整回顾
连接相关：
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;
-- 调整缓冲池实例数（减少锁竞争）
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 锁监控方法
- 实时锁状态查看
-- 当前锁等待信息
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;
空记录
- 历史锁竞争分析
-- 锁等待历史统计
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
9.InnoDB其他报表
性能优化策略
- 读优化（关键重点）
-- 识别高频读取表
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
