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

MySQL 10.4.32-MariaDB uptime 0 1:41:2 Wed Jul 30 15:17:44 2025

_ Key _________________________________________________
Buffer used		0 of  16.00M	%Used:	0.00
Current		2.92M		%Usage:18.28
Write hit				0.00%
Read hit				0.00%

Buffer used：0 of 16.00M (%Used: 0.00) 

Current：2.92M (%Usage: 18.28)

Write hit：0.00%

Read hit：0.00%

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

[mysqld]
key_buffer_size = 4M

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

ALTER TABLE table_name ENGINE=InnoDB;

LOAD INDEX INTO CACHE table_name;

SHOW VARIABLES LIKE 'key%';

SHOW GLOBAL STATUS LIKE 'Key%';

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

-- 将键缓存从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';

-- 1. 创建专用缓存
SET GLOBAL mycache.key_buffer_size = 64 * 1024 * 1024;
-- 2. 分配表到缓存
CACHE INDEX db1.table1, db2.table2 IN mycache;
-- 3. 预热缓存
LOAD INDEX INTO CACHE table1, table2;

SELECT
  'Key Buffer' AS metric,
  CONCAT(
    FORMAT(@@GLOBAL.key_buffer_size/1024/1024,2), 'M'
  ) AS config_size,
  CONCAT(
    (SELECT VARIABLE_VALUE 
     FROM information_schema.GLOBAL_STATUS 
     WHERE VARIABLE_NAME = 'Key_blocks_used') * 
    @@GLOBAL.key_cache_block_size / 1024 / 1024, 'M'
  ) AS current_usage,
  CONCAT(
    (SELECT ROUND(
      (SELECT VARIABLE_VALUE 
       FROM information_schema.GLOBAL_STATUS 
       WHERE VARIABLE_NAME = 'Key_blocks_used') * 
      @@GLOBAL.key_cache_block_size / 
      @@GLOBAL.key_buffer_size * 100, 2)
    ), '%'
  ) AS pct_usage
UNION ALL
SELECT
  'Hit Rate',
  CONCAT(
    ROUND(
      (1 - Key_reads / Key_read_requests) * 100, 2), '%'
  ) AS read_hit,
  CONCAT(
    ROUND(
      (1 - Key_writes / Key_write_requests) * 100, 2), '%'
  ) AS write_hit
FROM (
  SELECT
    MAX(IF(VARIABLE_NAME='Key_read_requests', VARIABLE_VALUE,0)) AS Key_read_requests,
    MAX(IF(VARIABLE_NAME='Key_reads', VARIABLE_VALUE,0)) AS Key_reads,
    MAX(IF(VARIABLE_NAME='Key_write_requests', VARIABLE_VALUE,0)) AS Key_write_requests,
    MAX(IF(VARIABLE_NAME='Key_writes', VARIABLE_VALUE,0)) AS Key_writes
  FROM information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME IN ('Key_read_requests','Key_reads','Key_write_requests','Key_writes')
) AS cache_stats;

ALTER TABLE myisam_table ENGINE=InnoDB; 

[mysqld]
key_buffer_size = 0
skip-secure-auth

-- 关注 InnoDB 缓冲池
SHOW ENGINE INNODB STATUS;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

__ Questions ___________________________________________________________
Total		3.89M		640.2/s
Com_		2.25M		370.0/s	%Total:  57.80
DMS		1.64M		270.3/s	42.22
-Unknown	548.48k	90.2/s		14.09
COM_QUIT	547.78k	90.1/s		14.07
Slow 10 s	0		0/s		0.00%	DMS:	0.00	Log:
DMS		1.64M		270.3/s	42.22
  SELECT	1.64M		270.1/s	42.19		99.93
  INSERT	1.00k		0.2/s		0.03		0.06
  DELETE	66		0.0/s		0.00		0.00
  UPDATE	0		0/s		0.00		0.00
  REPLACE	0		0/s		0.00		0.00
Com_		2.25M		370.0/s	57.80
  set_option	1.67M		274.6/s	42.89
  admin_comma	547.03k	90.0/s		14.05
  show_tables	13.57k		2.2/s		0.35

-- 查看所有Com_开头的状态变量
SHOW GLOBAL STATUS LIKE 'Com_%';
-- 或者使用查询（在MySQL 5.7及以上或MariaDB中）：
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME LIKE 'Com_%';

# 第一次采样
mysql -e "SHOW GLOBAL STATUS" > status1.txt
# 等待60秒
sleep 60
# 第二次采样
mysql -e "SHOW GLOBAL STATUS" > status2.txt
# 解析并计算差值
# 这里只演示Questions和Com_变量
total1=$(grep -w Questions status1.txt | awk '{print $2}')
total2=$(grep -w Questions status2.txt | awk '{print $2}')
diff_total=$((total2-total1))
# 计算Com_变量（所有Com_开头的变量，除了Com_quit等）
# 注意：需要排除某些变量，具体根据需求
# 这里我们只计算几个示例变量
com_select1=$(grep -w Com_select status1.txt | awk '{print $2}')
com_select2=$(grep -w Com_select status2.txt | awk '{print $2}')
diff_com_select=$((com_select2-com_select1))
# 然后可以计算占比等

set_option: 1.67M (42.89%)
admin_comma: 547.03k (14.05%)
show_tables: 13.57k (0.35%)

/* 核心性能指标 */
SHOW GLOBAL STATUS WHERE 
  VARIABLE_NAME IN ('Questions', 'Com_%', 'Queries', 'Slow_queries');
/* 连接统计 */
SHOW GLOBAL STATUS LIKE 'Conn%';
SHOW GLOBAL STATUS LIKE 'Aborted_%';
/* 命令详情 */
SELECT * FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/%';

# Percona Toolkit (生成完整报表)
pt-mysql-summary --user=monitor --password=xxx
# mysqldumpslow (慢查询分析)
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

 SELECT
'Total Queries' AS metric,
  VARIABLE_VALUE AS total,
  ROUND(VARIABLE_VALUE / @@GLOBAL.Uptime, 1) AS per_sec
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Questions'
UNION ALL
SELECT 
  'DMS (Data Manipulation)',
  SUM(COUNT_STAR),
  ROUND(SUM(COUNT_STAR) / @@GLOBAL.Uptime, 1)
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME IN (
  'statement/sql/insert',
  'statement/sql/update',
  'statement/sql/delete',
  'statement/sql/select',
  'statement/sql/replace'
)
UNION ALL
SELECT
  'Non-DMS Commands',
  (SELECT VARIABLE_VALUE 
   FROM performance_schema.global_status 
   WHERE VARIABLE_NAME = 'Questions') - 
  (SELECT SUM(COUNT_STAR) 
   FROM performance_schema.events_statements_summary_global_by_event_name
   WHERE EVENT_NAME IN ('statement/sql/insert','statement/sql/update','statement/sql/delete','statement/sql/select','statement/sql/replace')),
  ROUND(
    ((SELECT VARIABLE_VALUE 
      FROM performance_schema.global_status 
      WHERE VARIABLE_NAME = 'Questions') - 
     (SELECT SUM(COUNT_STAR) 
      FROM performance_schema.events_statements_summary_global_by_event_name
      WHERE EVENT_NAME IN ('statement/sql/insert','statement/sql/update','statement/sql/delete','statement/sql/select','statement/sql/replace')))
    / @@GLOBAL.Uptime, 1
  );

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

// Node.js示例
const pool = mysql.createPool({
  connectionLimit: 100,
  host: 'db',
  user: 'user',
  password: 'pass',
  waitForConnections: true,
  queueLimit: 0
});

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

set_option: 1.67M/s (42.89%)
admin_comma: 547.03k/s (14.05%)
show_tables: 13.57k/s (0.35%)

REVOKE SUPER ON *.* FROM 'app_user'@'%';
REVOKE SHOW DATABASES ON *.* FROM 'app_user'@'%';

SET GLOBAL information_schema_stats_expiry = 86400;

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

SELECT: 1.64M/s (99.93%)
INSERT: 1.00k/s (0.06%)
DELETE: 66/s (0.00%)

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

[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;

[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

__ SELECT and Sort _______________________________________
Scan577.62k	95.0/s %SELECT:35.16
Range00/s	0.00
Full join	00/s	0.00
Range check	00/s	0.00
Full rng join	00/s	0.00
Sort scan 	00/s
Sort range	2980.0/s
Sort mrg pass 	00/s

Scan577.62k	95.0/s		%SELECT:	35.16

SHOW GLOBAL STATUS LIKE 'Handler_read%';
SHOW GLOBAL STATUS LIKE 'Sort%';

-- 查看全表扫描事件
SELECT * FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME = 'statement/sql/select' AND SUM_NO_INDEX_USED > 0;

[mysqld]
# 增加排序缓冲区大小（根据实际内存调整）
sort_buffer_size = 2M
# 增加连接排序缓冲区大小（如果连接查询中有排序）
join_buffer_size = 1M

SHOW GLOBAL STATUS LIKE 'Handler_read%';
SHOW GLOBAL STATUS LIKE 'Sort%';

-- 查看扫描类型分布
SELECT 
  EVENT_NAME,
  COUNT_STAR AS total_ops,
  SUM_TIMER_WAIT AS total_time
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/select%'
  AND SUM_NO_INDEX_USED > 0;  -- 使用全表扫描的查询
-- 查看排序操作
SELECT 
  EVENT_NAME,
  COUNT_STAR,
  SUM_SORT_MERGE_PASSES,
  SUM_SORT_SCAN,
  SUM_SORT_RANGE
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE SUM_SORT_ROWS > 0;

-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 捕获1秒以上查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查找未使用索引的TOP10查询
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT 
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE 'SELECT%'
  AND SUM_NO_INDEX_USED > 0
ORDER BY COUNT_STAR DESC
LIMIT 10;

SET GLOBAL innodb_buffer_pool_size = 2*1024*1024*1024;  -- 2GB

-- 在关键查询中添加索引提示
SELECT /*+ INDEX(table_name idx_column) */ ...

-- 查找全表扫描最多的表
SELECT 
  OBJECT_SCHEMA,
  OBJECT_NAME,
  COUNT_READ AS full_scans
FROM performance_schema.table_io_waits_summary_by_table
WHERE COUNT_READ_WITHOUT_INDEX > 0
ORDER BY COUNT_READ_WITHOUT_INDEX DESC
LIMIT 5;

-- 复合索引示例（覆盖查询）
CREATE INDEX idx_covering ON orders (customer_id, order_date)
INCLUDE (total_amount, status);
-- 函数索引（优化WHERE条件）
CREATE INDEX idx_email_domain ON users (SUBSTRING_INDEX(email, '@', -1));

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

-- 反例（全表扫描）
SELECT * FROM products WHERE UPPER(name) = 'LAPTOP';
-- 优化后
SELECT * FROM products WHERE name = 'Laptop' COLLATE utf8mb4_general_ci;

-- 反例（OFFSET导致全表扫描）
SELECT * FROM logs ORDER BY id LIMIT 10000, 20;
-- 优化后（keyset分页）
SELECT * FROM logs 
WHERE id > 10000  -- 上次最后ID
ORDER BY id LIMIT 20;

-- 反例（获取不必要列）
SELECT * FROM users WHERE country = 'US';
-- 优化后
SELECT user_id, name FROM users WHERE country = 'US';

[mysqld]
# 索引优化配置
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

-- 每分钟扫描率监控
SELECT 
  NOW() AS timestamp,
  VARIABLE_VALUE AS current_scans
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Handler_read_rnd_next'
INTO OUTFILE '/tmp/scan_rates.csv'
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

-- 使用内置索引建议
SELECT * FROM sys.schema_index_statistics
WHERE query_count > 1000 
  AND rows_selected / rows_full_scanned > 0.5;

__ Query Cache __________________________________________
Memory usage	16.80k of	1.00M	%Used:	1.64
Block Fragmnt 	100.00%
Hits00/s
Inserts10.0/s
Insrt:Prune	1:10/s
Hit:Insert	0.00:1

SET GLOBAL query_cache_size = 0;-- 彻底关闭

RESET QUERY CACHE;

query_cache_type = OFF
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

-- 增大缓存并调整参数
SET GLOBAL query_cache_size = 64*1024*1024;  -- 64MB
SET GLOBAL query_cache_limit = 4*1024*1024;   -- 4MB/结果集
SET GLOBAL query_cache_min_res_unit = 2048;   -- 减少碎片
-- 重置缓存状态
FLUSH QUERY CACHE;
RESET QUERY CACHE;

# Python示例（Redis缓存）
import redis
import mysql.connector
r = redis.Redis()
def query(sql):
    if r.exists(sql):
        return r.get(sql)
    else:
        result = db.execute(sql)
        r.setex(sql, 3600, result)  # 缓存1小时
        return result

-- 启用查询缓存
INSERT INTO mysql_query_rules (active, cache_ttl, destination_hostgroup, apply)
VALUES (1, 60000, 0, 1);
-- 设置缓存策略
SET mysql-query_cache_size=256000000;

-- 确认缓存已禁用
SHOW VARIABLES LIKE 'query_cache%';
-- 性能对比指标
SELECT 
  'Before' AS state,
  (SELECT VARIABLE_VALUE 
   FROM performance_schema.global_status 
   WHERE VARIABLE_NAME = 'Qcache_hits') AS hits,
  (SELECT VARIABLE_VALUE 
   FROM performance_schema.global_status 
   WHERE VARIABLE_NAME = 'Com_select') AS selects
UNION ALL
SELECT 
  'After',
  0,
  (SELECT VARIABLE_VALUE 
   FROM performance_schema.global_status 
   WHERE VARIABLE_NAME = 'Com_select');

[mysqld]
# 分配80%可用内存
innodb_buffer_pool_size = 4G
# 监控命中率
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';

-- 识别高频查询
SELECT DIGEST_TEXT, COUNT_STAR
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC
LIMIT 10;

# 使用Percona Toolkit
pt-mysql-summary --user=dba --password=xxx
pt-query-digest /var/log/mysql/slow.log

__ Table Locks ________________________________________________
Waited			0		0/s	%Total: 0.00
Immediate		257		0.0/s
__ Tables ______________________________________________________
Open			161 of 2000	%Cache: 8.05
Opened			168		0.0/s

-- 计算理想缓存大小（当前打开表的150%）
SELECT CONCAT(
  ROUND(MAX_OPEN_TABLES * 1.5), 
  ' (当前配置: ', 
  @@GLOBAL.table_open_cache, 
  ')'
) AS recommended_size
FROM (
  SELECT MAX(VARIABLE_VALUE) AS MAX_OPEN_TABLES 
  FROM performance_schema.global_status 
  WHERE VARIABLE_NAME = 'Open_tables'
) AS t;
-- 动态调整（示例：从2000降到250）
SET GLOBAL table_open_cache = 250;

-- 查看当前表定义缓存状态
SHOW GLOBAL STATUS LIKE 'Open_table_definitions';
-- 优化配置（设置为表缓存的1.5-2倍）
SET GLOBAL table_definition_cache = 400;

-- 定期刷新（低峰期执行）
FLUSH TABLES;
-- 监控缓存未命中率
SELECT ROUND(
  (SELECT VARIABLE_VALUE 
   FROM performance_schema.global_status 
   WHERE VARIABLE_NAME = 'Opened_tables') /
  (SELECT VARIABLE_VALUE 
   FROM performance_schema.global_status 
   WHERE VARIABLE_NAME = 'Open_tables') * 100, 2
) AS cache_miss_rate;

[mysqld]
# 表缓存优化
table_open_cache = 250
table_definition_cache = 400
table_open_cache_instances = 16  # 减少锁竞争
# 表锁监控
performance_schema = ON
performance-schema-instrument = 'wait/lock/table/%=ON'
# InnoDB引擎转换（减少表锁）
default_storage_engine = InnoDB

ALTER TABLE large_table 
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- 查看当前表锁定状态
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM sys.innodb_lock_waits;
-- 实时监控元数据锁
SELECT * FROM performance_schema.metadata_locks;

-- 创建自动化维护事件
CREATE EVENT table_cache_maintenance
ON SCHEDULE EVERY 1 HOUR
DO BEGIN
  FLUSH TABLES;
  ANALYZE TABLE important_table;
END;

SELECT
  'Table Locks' AS category,
  CONCAT(
    (SELECT VARIABLE_VALUE 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Table_locks_waited'),
    ' (',
    ROUND(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Table_locks_waited') / 
      GREATEST((SELECT VARIABLE_VALUE 
                FROM performance_schema.global_status 
                WHERE VARIABLE_NAME = 'Uptime'), 1),
      2),
    '/s)'
  ) AS waited,
  CONCAT(
    (SELECT VARIABLE_VALUE 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Table_locks_immediate'),
    ' (',
    ROUND(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Table_locks_immediate') / 
      GREATEST((SELECT VARIABLE_VALUE 
                FROM performance_schema.global_status 
                WHERE VARIABLE_NAME = 'Uptime'), 1),
      2),
    '/s)'
  ) AS immediate
UNION ALL
SELECT
  'Table Cache',
  CONCAT(
    (SELECT VARIABLE_VALUE 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Open_tables'),
    '/',
    @@GLOBAL.table_open_cache,
    ' (',
    ROUND(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Open_tables') / 
      @@GLOBAL.table_open_cache * 100, 2),
    '%)'
  ),
  CONCAT(
    (SELECT VARIABLE_VALUE 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Opened_tables'),
    ' (',
    ROUND(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Opened_tables') / 
      GREATEST((SELECT VARIABLE_VALUE 
                FROM performance_schema.global_status 
                WHERE VARIABLE_NAME = 'Uptime'), 1),
      2),
    '/s)'
  );

SET GLOBAL table_open_cache = 250;  -- 降低85%内存占用

-- 将MyISAM表转为InnoDB
ALTER TABLE myisam_table ENGINE=InnoDB;

# 安装Percona监控工具
sudo percona-release setup ps80
sudo apt install percona-toolkit
# 生成表缓存报告
pt-mysql-summary --table-cache

-- 建议设置为当前打开表的 150% 左右
SET GLOBAL table_open_cache = 250;
（当前打开161表 → 新缓存大小 ≈ 161 × 1.5 ≈ 250）

-- 计算未命中率（>10% 需优化）
SELECT ROUND(@@GLOBAL.Opened_tables / @@GLOBAL.Open_tables * 100, 2) AS miss_rate;

FLUSH TABLES; -- 不影响业务时执行

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

ALTER TABLE your_table ENGINE = InnoDB; -- 转换表引擎
SHW ENGINE INNODB STATUS; -- 查看行锁状态

__ Connections _______________________________________________
Max used		501 of  500	%Max: 100.20
Total			547.95k	90.1/s
__ Created Temp ________________________________________________
Disk table		5.99k		1.0/s
Table			30.91k		5.1/s		Size:  16.0M
File			4		0.0/s

SET GLOBAL max_connections = 600;
SET GLOBAL wait_timeout = 60; -- 非交互连接超时时间（秒）
SET GLOBAL interactive_timeout = 60; -- 交互连接超时时间（秒）

SET GLOBAL tmp_table_size = 32 * 1024 * 1024; -- 32MB
SET GLOBAL max_heap_table_size = 32 * 1024 * 1024; -- 32MB

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;
SET GLOBAL thread_pool_size = 32;
-- 减少连接超时
SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;

[mysqld]
# 连接优化
max_connections = 600
thread_handling = pool-of-threads
thread_pool_size = 32
wait_timeout = 60
interactive_timeout = 60
# 连接缓冲
skip_name_resolve = ON

-- 增大内存临时表
SET GLOBAL tmp_table_size = 64 * 1024 * 1024;  -- 64MB
SET GLOBAL max_heap_table_size = 64 * 1024 * 1024;
-- 识别使用磁盘临时表的查询
SELECT * FROM sys.statements_with_temp_tables
WHERE disk_tmp_tables > 0
ORDER BY disk_tmp_tables DESC
LIMIT 5;

[mysqld]
# 临时表优化
tmp_table_size = 64M
max_heap_table_size = 64M
internal_tmp_disk_storage_engine = InnoDB
# 文件系统优化
tmpdir = /dev/shm  # 使用内存文件系统

// Java (HikariCP)
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(100);
config.setConnectionTimeout(30000);

INSERT INTO mysql_servers (hostname, port) VALUES ('db-host', 3306);
INSERT INTO mysql_users (username, password) VALUES ('appuser', 'password');
UPDATE global_variables SET variable_value=100 
WHERE variable_name='mysql-default_connection_max_age_ms';
LOAD MYSQL VARIABLES TO RUNTIME;

SELECT 1 FROM dual;  -- 每次借出连接前执行

-- 典型触发场景
SELECT user_id, COUNT(*) 
FROM orders 
GROUP BY user_id 
ORDER BY COUNT(*) DESC;  -- 需要排序和聚合

CREATE INDEX idx_user_orders ON orders(user_id);

-- 原查询
SELECT a.*, b.name 
FROM orders a
JOIN users b ON a.user_id = b.id;
-- 优化后 (减少连接列)
SELECT a.order_id, a.amount, b.name 
FROM orders a
JOIN users b ON a.user_id = b.id;

-- 连接使用率
SELECT 
  MAX_USED_CONNECTIONS AS max_used,
  VARIABLE_VALUE AS max_conn,
  ROUND(MAX_USED_CONNECTIONS / VARIABLE_VALUE * 100, 2) AS pct_used
FROM 
  (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS 
   WHERE VARIABLE_NAME = 'Max_used_connections') AS max_used,
  (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES 
   WHERE VARIABLE_NAME = 'max_connections') AS max_conn;
-- 临时表比率
SELECT 
  ROUND(SUM_CREATED_TMP_DISK_TABLES / SUM_CREATED_TMP_TABLES * 100, 2) AS disk_tmp_ratio
FROM (
  SELECT 
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS 
     WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') AS SUM_CREATED_TMP_DISK_TABLES,
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS 
     WHERE VARIABLE_NAME = 'Created_tmp_tables') AS SUM_CREATED_TMP_TABLES
) AS tmp_stats;

__ Threads _____________________________________________
Running10 of		31
Cached0 of		256	%Hit: 99.97
Created1630.0/s
Slow00/s
__ Aborted _________________________________
Clients3470.1/s
Connects17.97k		3.0/s
__ Bytes _____________________________________
Sent885.62M	145.6k/s
Received847.43M	139.4k/s
InnoDB缓存池报表
__ InnoDB Buffer Pool _____________________________
Usage13.39M of	15.67M	%Used:	85.44
Read hit	100.00%
Pages
  Free146		%Total:  14.56
  Data85084.75 %Drty:   0.00
  Misc70.70
  Latched0.00
Reads68.08M		11.2k/s
  From file		1.50k		0.2/s	0.00
  Ahead Rnd		00/s
  Ahead Sq					0/s
Writes9.09k		1.5/s
Flushes6070.1/s
Wait Free		00/s

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

-- 1. 检查认证错误
SHOW GLOBAL STATUS LIKE 'Connection_errors%';
-- 2. 优化认证缓存
SET GLOBAL credentials_expired = OFF;
SET GLOBAL disconnect_on_expired_password = OFF;
-- 3. 增加连接积压队列
SET GLOBAL back_log = 200;

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

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

# Python示例：启用MySQL压缩
import mysql.connector
db = mysql.connector.connect(
  host="localhost",
  user="user",
  passwd="password",
  compress=True  # 关键参数
)

-- 查看缓冲池页分布
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;

[mysqld]
# 缓冲池优化
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

SELECT 
  'Connections' AS category,
  CONCAT(
    (SELECT VARIABLE_VALUE 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Aborted_connects'),
    ' (',
    ROUND(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Aborted_connects') / 
      GREATEST((SELECT VARIABLE_VALUE 
                FROM performance_schema.global_status 
                WHERE VARIABLE_NAME = 'Uptime'), 1),
      2),
    '/s)'
  ) AS aborted_connects,
  CONCAT(
    (SELECT VARIABLE_VALUE 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Aborted_clients'),
    ' (',
    ROUND(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Aborted_clients') / 
      GREATEST((SELECT VARIABLE_VALUE 
                FROM performance_schema.global_status 
                WHERE VARIABLE_NAME = 'Uptime'), 1),
      2),
    '/s)'
  ) AS aborted_clients
UNION ALL
SELECT
  'Network',
  CONCAT(
    FORMAT(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Bytes_sent') / 1024 / 1024, 1),
    'M (',
    ROUND(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Bytes_sent') / 
      @@GLOBAL.Uptime / 1024, 1),
    'k/s)'
  ) AS bytes_sent,
  CONCAT(
    FORMAT(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Bytes_received') / 1024 / 1024, 1),
    'M (',
    ROUND(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Bytes_received') / 
      @@GLOBAL.Uptime / 1024, 1),
    'k/s)'
  ) AS bytes_received
UNION ALL
SELECT
  'InnoDB Buffer',
  CONCAT(
    (SELECT ROUND(VARIABLE_VALUE/1024/1024,2) 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_bytes_data'),
    'M/',
    (SELECT ROUND(@@GLOBAL.innodb_buffer_pool_size/1024/1024,2)),
    'M (',
    ROUND(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Innodb_buffer_pool_bytes_data') / 
      @@GLOBAL.innodb_buffer_pool_size * 100, 2),
    '%)'
  ) AS usage,
  CONCAT(
    ROUND(
      (1 - (SELECT VARIABLE_VALUE 
            FROM performance_schema.global_status 
            WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') / 
       (SELECT VARIABLE_VALUE 
        FROM performance_schema.global_status 
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')) * 100, 2),
    '%'
  ) AS hit_rate

# 增加连接积压缓冲
mysql -e "SET GLOBAL back_log = 200;"
# 启用协议压缩
mysql -e "SET GLOBAL protocol_compression = ON;"

# 更新my.cnf配置
[mysqld]
connect_timeout=15
skip_name_resolve=ON
protocol_compression=ON
innodb_buffer_pool_instances=8

__ InnoDB Lock ________________________________________________
Waits			0			0/s
Current		0
Time acquiring
  Total		0 ms
  Average		0 ms
  Max			0 ms

SHOW ENGINE INNODB STATUS; # 在输出的TRANSACTIONS部分查看

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

SELECT * FROM information_schema.INNODB_LOCKS;

SELECT * FROM information_schema.INNODB_TRX;

-- 当前锁等待信息
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;

-- 启用锁监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'wait/lock%';
-- 查看锁等待统计
SELECT 
  EVENT_NAME,
  COUNT_STAR,
  SUM_TIMER_WAIT/1000000000 AS wait_time_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/lock%';

SHOW ENGINE INNODB STATUS \G

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

-- 添加覆盖索引减少锁范围
CREATE INDEX idx_order_status ON orders(status, customer_id) INCLUDE (amount);

-- 保持事务简短
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 快速提交
-- 避免长事务
SET SESSION max_execution_time = 5000;  -- 5秒超时

-- 锁等待链分析
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;

[mysqld]
innodb_status_output = ON
innodb_status_output_locks = ON

-- 创建测试表
CREATE TABLE lock_test (
  id INT PRIMARY KEY,
  value INT
) ENGINE=InnoDB;
-- 并发更新测试
START TRANSACTION;
UPDATE lock_test SET value = value + 1 WHERE id = 1;
-- 保持连接打开，模拟长事务

-- 锁等待率
SELECT 
  ROUND(SUM(SUM_TIMER_WAIT)/1000000000, 4) AS total_wait_sec,
  ROUND(SUM(SUM_TIMER_WAIT)/SUM(COUNT_STAR)/1000, 4) AS avg_wait_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/lock/table/sql/handler';

-- 反例（长事务）
START TRANSACTION;
SELECT * FROM large_table;  -- 耗时操作
UPDATE ... 
COMMIT;
-- 优化后
UPDATE ... (直接操作)

# 每天检查锁等待
mysql -e "SELECT COUNT(*) FROM information_schema.INNODB_LOCK_WAITS" > lock_monitor.log

# Python自动重试
for attempt in range(3):
    try:
        execute_transaction()
        break
    except mysql.connector.errors.DatabaseError as e:
        if 'Deadlock' in str(e):
            sleep(0.1 * (2 ** attempt))
        else:
            raise

__ InnoDB Data, Pages, Rows _____________________________
Data
  Read1.66k		0.3/s
  Writes1.67k		0.3/s
  Fsync1.09k		0.2/s
  Pending
    Reads0
    Writes0
    Fsync0
Pages
  Created1490.0/s
  Read1.50k		0.2/s
  Written6070.1/s
Rows
  Deleted40.0/s
  Inserted1.00k		0.2/s
  Read540.48M	88.9k/s
  Updated00/s

-- 识别高频读取表
SELECT 
  TABLE_SCHEMA,
  TABLE_NAME,
  ROWS_READ
FROM information_schema.TABLE_STATISTICS
ORDER BY ROWS_READ DESC
LIMIT 5;
-- 添加覆盖索引
CREATE INDEX idx_covering ON orders (customer_id, status) 
INCLUDE (order_date, total_amount);

-- 增加缓冲池实例（减少锁竞争）
SET GLOBAL innodb_buffer_pool_instances = 8;
-- 预热关键表
SELECT * FROM top_table FORCE INDEX(PRIMARY) 
WHERE id BETWEEN 1 AND 100000;

-- 分析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;
-- 启用查询重写
CREATE OR REPLACE VIEW hot_data AS
SELECT /*+ MAX_EXECUTION_TIME(100) */ *
FROM orders WHERE status = 'completed';

[mysqld]
# 读优化配置
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

-- 按日期范围分区
ALTER TABLE orders PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);
-- 查询特定分区
SELECT * FROM orders PARTITION (p2023);

-- 创建内存副本
CREATE TABLE orders_cache ENGINE=MEMORY 
SELECT * FROM orders WHERE status = 'completed';
-- 定时刷新
CREATE EVENT refresh_cache
ON SCHEDULE EVERY 5 MINUTE
DO BEGIN
  TRUNCATE orders_cache;
  INSERT INTO orders_cache SELECT * FROM orders WHERE status = 'completed';
END

-- 使用查询缓存（MariaDB 10.6+）
SET GLOBAL use_result_cache = ON;
SET GLOBAL result_cache_size = 64M;
-- 标记可缓存查询
SELECT SQL_CACHE * FROM products WHERE category = 'Electronics';

SELECT 
  'Row Operations' AS category,
  CONCAT(ROUND(SUM_ROWS_READ/1000000,2),'M (', 
         ROUND(SUM_ROWS_READ/@@GLOBAL.Uptime), '/s)') AS rows_read,
  CONCAT(SUM_ROWS_INSERTED, ' (', 
         ROUND(SUM_ROWS_INSERTED/@@GLOBAL.Uptime,1), '/s)') AS rows_inserted
FROM (
  SELECT 
    SUM(VARIABLE_VALUE) AS SUM_ROWS_READ 
  FROM performance_schema.global_status 
  WHERE VARIABLE_NAME IN ('Innodb_rows_read')
) AS r,
(
  SELECT 
    SUM(VARIABLE_VALUE) AS SUM_ROWS_INSERTED 
  FROM performance_schema.global_status 
  WHERE VARIABLE_NAME IN ('Innodb_rows_inserted')
) AS i
UNION ALL
SELECT
  'Page Operations',
  CONCAT(
    (SELECT VARIABLE_VALUE 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Innodb_pages_read'),
    ' (',
    ROUND(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Innodb_pages_read') / 
      @@GLOBAL.Uptime, 1),
    '/s)'
  ) AS pages_read,
  CONCAT(
    (SELECT VARIABLE_VALUE 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Innodb_pages_written'),
    ' (',
    ROUND(
      (SELECT VARIABLE_VALUE 
       FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'Innodb_pages_written') / 
      @@GLOBAL.Uptime, 1),
    '/s)'
  ) AS pages_written;

-- 添加高频查询索引
CREATE INDEX idx_customer_status ON orders(customer_id, status);
-- 增加缓冲池实例
SET GLOBAL innodb_buffer_pool_instances = 8;

-- 实现分区表
ALTER TABLE orders PARTITION BY RANGE (YEAR(order_date));
-- 部署内存缓存表
CREATE TABLE orders_cache ENGINE=MEMORY SELECT * FROM orders;

# 安装列式存储引擎
sudo apt install mariadb-plugin-columnstore
# 转换表引擎
ALTER TABLE orders ENGINE=ColumnStore;