十三、性能监控与调优

13.1 状态变量

-- 查看所有状态变量
SHOW STATUS;

-- 查看关键性能指标
SHOW STATUS LIKE 'Threads_connected';      -- 当前连接数
SHOW STATUS LIKE 'Max_used_connections';   -- 历史最大连接数
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';  -- 从磁盘读取次数
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';  -- 逻辑读取请求
SHOW STATUS LIKE 'Slow_queries';           -- 慢查询数量
SHOW STATUS LIKE 'Questions';              -- 查询总数

-- 查看变量
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

13.2 慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 超过2秒的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

13.3 Performance Schema

-- 查看 Performance Schema 是否开启
SHOW VARIABLES LIKE 'performance_schema';

-- 查看语句执行统计
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- 查看等待事件
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- 查看文件IO
SELECT * FROM performance_schema.file_summary_by_instance
ORDER BY SUM_NUMBER_OF_BYTES_READ DESC LIMIT 10;

13.4 系统调优

# my.cnf 配置优化

[mysqld]
# 连接设置
max_connections = 1000
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800

# 缓冲区设置
innodb_buffer_pool_size = 4G  # 设置为物理内存的 70-80%
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
query_cache_size = 0  # MySQL 8.0 移除查询缓存
tmp_table_size = 64M
max_heap_table_size = 64M

# 排序设置
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M

# InnoDB 设置
innodb_flush_log_at_trx_commit = 1  # 最安全
innodb_file_per_table = ON
innodb_open_files = 4000
innodb_io_capacity = 2000

# 日志设置
log_error = /var/log/mysql/error.log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 字符集设置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

十四、高可用与集群

14.1 主从复制

-- 主服务器配置
-- my.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydb
binlog_format = ROW

-- 创建复制用户
CREATE USER 'replication'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

-- 查看主服务器状态
SHOW MASTER STATUS;

-- 从服务器配置
-- my.cnf
[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
read_only = ON

-- 配置从服务器
CHANGE MASTER TO
    MASTER_HOST = 'master_host',
    MASTER_USER = 'replication',
    MASTER_PASSWORD = 'repl_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 123;

-- 启动复制
START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G

-- 停止复制
STOP SLAVE;

-- 重置复制
RESET SLAVE ALL;

14.2 主主复制

-- 服务器 A
-- my.cnf
server-id = 1
log_bin = mysql-bin
auto_increment_increment = 2
auto_increment_offset = 1

-- 服务器 B
-- my.cnf
server-id = 2
log_bin = mysql-bin
auto_increment_increment = 2
auto_increment_offset = 2

-- 互相配置为主从
-- 在 A 上
CHANGE MASTER TO MASTER_HOST = 'B_HOST', ...;
START SLAVE;

-- 在 B 上
CHANGE MASTER TO MASTER_HOST = 'A_HOST', ...;
START SLAVE;

14.3 读写分离

-- 使用 MySQL Router
-- 配置读写分离
router> configure
router> add master-replicaset
router> bootstrap

-- 应用层实现（以 PHP 为例）
$db = [
    'write' => ['host' => 'master_host'],
    'read' => ['host' => 'slave1_host', 'host' => 'slave2_host']
];

function query($sql) {
    if (strpos($sql, 'SELECT') === 0) {
        // 读操作
        $connection = getRandomReadConnection();
    } else {
        // 写操作
        $connection = getWriteConnection();
    }
    return $connection->query($sql);
}

14.4 分区表

-- 范围分区
CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 列表分区
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    region VARCHAR(20)
)
PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('北京', '天津', '河北'),
    PARTITION p_south VALUES IN ('广东', '广西', '海南'),
    PARTITION p_other VALUES IN ('其他')
);

-- 哈希分区
CREATE TABLE logs (
    id INT,
    log_time DATETIME,
    message TEXT
)
PARTITION BY HASH (id) PARTITIONS 8;

-- 键分区
CREATE TABLE sessions (
    session_id VARCHAR(32),
    user_id INT,
    data TEXT
)
PARTITION BY KEY(session_id) PARTITIONS 16;

-- 管理分区
ALTER TABLE orders ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));
ALTER TABLE orders DROP PARTITION p2022;
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (...);

十五、安全最佳实践

15.1 SQL 注入防护

-- 使用预编译语句（推荐）
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ? AND password = ?';
SET @username = 'admin';
SET @password = 'p@ssw0rd';
EXECUTE stmt USING @username, @password;
DEALLOCATE PREPARE stmt;

-- 使用存储过程（参数化）
CREATE PROCEDURE GetUser(IN username VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE username = username;
END;

-- 转义输入（应用层）
$username = mysqli_real_escape_string($conn, $_POST['username']);
$query = "SELECT * FROM users WHERE username = '$username'";

15.2 数据加密

-- 传输加密（SSL/TLS）
-- 配置 SSL
[mysqld]
ssl-ca = /etc/mysql/ca-cert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem

-- 强制使用 SSL
GRANT ALL ON *.* TO 'user'@'%' REQUIRE SSL;

-- 静态数据加密（MySQL 8.0）
[mysqld]
early-plugin-load = keyring_file.so
keyring_file_data = /var/lib/mysql-keyring/keyring

-- 字段加密
CREATE TABLE users (
    id INT PRIMARY KEY,
    credit_card VARBINARY(255)
);

INSERT INTO users (id, credit_card) 
VALUES (1, AES_ENCRYPT('1234567890123456', 'encryption_key'));

SELECT AES_DECRYPT(credit_card, 'encryption_key') FROM users WHERE id = 1;

15.3 审计日志

-- 安装审计插件（MySQL Enterprise Audit）
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- 配置审计
[mysqld]
audit_log_policy = ALL
audit_log_format = JSON
audit_log_file = /var/log/mysql/audit.log

-- 使用 init-connect 记录登录信息
CREATE TABLE audit.logins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    login_time DATETIME,
    connection_id INT,
    ip_address VARCHAR(50)
);

SET GLOBAL init_connect = 'INSERT INTO audit.logins (username, login_time, connection_id, ip_address) VALUES (USER(), NOW(), CONNECTION_ID(), SUBSTRING_INDEX(USER(), "@", -1));';

十六、常见问题排查

16.1 连接问题

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';
-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看所有连接
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 杀死进程
KILL process_id;

-- 查看连接超时设置
SHOW VARIABLES LIKE '%timeout%';

16.2 性能问题排查

-- 查看当前正在执行的查询
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep';

-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS\G

-- 查看表大小
SELECT 
    table_schema,
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
WHERE table_schema = 'mydb'
ORDER BY size_mb DESC;

16.3 数据恢复

-- 使用 binlog 恢复误删数据
mysqlbinlog --start-position=100 --stop-position=500 mysql-bin.000001 | mysql -u root -p

-- InnoDB 表损坏修复
-- 设置 innodb_force_recovery = 1-6
SET GLOBAL innodb_force_recovery = 1;
-- 导出数据
mysqldump mydb broken_table > broken_table.sql
-- 删除并重建表
DROP TABLE broken_table;
-- 恢复数据
mysql mydb < broken_table.sql
-- 关闭强制恢复
SET GLOBAL innodb_force_recovery = 0;

MySQL 是一个功能强大、不断演进的数据库系统。本文从基础概念到高级特性，从日常操作到性能优化，系统地梳理了 MySQL 的核心知识点。掌握这些内容，你不仅能高效地使用 MySQL，更能深入理解其内部原理，应对各种复杂的生产环境挑战。
来源：
https://app-adtysnu98v0h.appmiaoda.com