MySQL性能优化与调优

数据库性能是系统整体性能的基石。一个优化良好的MySQL数据库可以轻松应对高并发场景，而配置不当的数据库则可能成为整个系统的瓶颈。今天，我们将深入探讨MySQL性能优化的各个方面，从查询优化到服务器配置，帮助你构建高性能的数据库系统。

1. 查询性能优化

EXPLAIN执行计划深度解读

EXPLAIN输出详解：

-- 基本EXPLAIN使用
EXPLAIN 
SELECT e.emp_name, d.dept_name, p.project_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN projects p ON e.emp_id = p.manager_id
WHERE e.salary > 5000
  AND d.location = '北京'
ORDER BY e.hire_date DESC
LIMIT 100;

-- EXPLAIN输出字段解析
CREATE TABLE explain_analysis (
    id INT PRIMARY KEY,
    select_type VARCHAR(50) COMMENT '查询类型',
    table_name VARCHAR(64) COMMENT '表名',
    partitions VARCHAR(255) COMMENT '匹配的分区',
    type VARCHAR(30) COMMENT '连接类型',
    possible_keys VARCHAR(255) COMMENT '可能使用的索引',
    key VARCHAR(255) COMMENT '实际使用的索引',
    key_len INT COMMENT '使用的索引长度',
    ref VARCHAR(255) COMMENT '与索引比较的列',
    rows INT COMMENT '估计要检查的行数',
    filtered DECIMAL(5,2) COMMENT '按条件过滤的行百分比',
    extra VARCHAR(255) COMMENT '额外信息'
);

执行计划类型深度分析：

-- 不同类型的执行计划示例

-- 1. system & const（最优）
EXPLAIN SELECT * FROM departments WHERE dept_id = 1;
-- type: const，通过主键或唯一索引查找

-- 2. eq_ref（优秀）
EXPLAIN SELECT * FROM employees e 
JOIN departments d ON e.dept_id = d.dept_id;
-- type: eq_ref，对于前表的每一行，后表只有一行匹配

-- 3. ref（良好）
EXPLAIN SELECT * FROM employees WHERE dept_id = 1;
-- type: ref，使用非唯一索引查找

-- 4. range（良好）
EXPLAIN SELECT * FROM employees 
WHERE hire_date BETWEEN '2020-01-01' AND '2023-01-01';
-- type: range，索引范围扫描

-- 5. index（中等）
EXPLAIN SELECT dept_id FROM employees;
-- type: index，全索引扫描

-- 6. ALL（最差）
EXPLAIN SELECT * FROM employees WHERE salary > 5000;
-- type: ALL，全表扫描（如果没有合适的索引）

EXPLAIN FORMAT=JSON深度分析：

-- 使用JSON格式获取更详细的执行计划
EXPLAIN FORMAT=JSON
SELECT 
    e.emp_name,
    d.dept_name,
    COUNT(p.project_id) as project_count
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN projects p ON e.emp_id = p.manager_id
WHERE e.hire_date >= '2020-01-01'
  AND d.budget > 100000
GROUP BY e.emp_id, e.emp_name, d.dept_name
HAVING project_count >= 2
ORDER BY e.salary DESC;

-- 解析JSON执行计划的关键信息
SELECT 
    JSON_EXTRACT(
        (EXPLAIN FORMAT=JSON 
         SELECT * FROM employees WHERE dept_id = 1),
        '$.query_block.cost_info.query_cost'
    ) as query_cost;

慢查询日志分析与优化

慢查询配置：

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

-- 动态配置慢查询（无需重启）
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1.0;  -- 1秒
SET GLOBAL min_examined_row_limit = 100;
SET GLOBAL log_queries_not_using_indexes = 1;

-- 永久配置（在my.cnf中）
/*
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 1
min_examined_row_limit = 100
log_slow_admin_statements = 1
*/

慢查询日志分析：

-- 使用pt-query-digest分析慢查询日志（外部工具）
-- pt-query-digest /var/log/mysql/slow.log > slow_report.txt

-- 使用MySQL自身分析慢查询
CREATE TABLE slow_log_analysis (
    query_time DECIMAL(10,6),
    lock_time DECIMAL(10,6),
    rows_sent INT,
    rows_examined INT,
    db VARCHAR(512),
    query TEXT,
    timestamp TIMESTAMP
);

-- 将慢查询日志导入表中分析
LOAD DATA INFILE '/var/log/mysql/slow.log'
INTO TABLE slow_log_analysis
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

-- 分析慢查询模式
SELECT 
    LEFT(query, 100) as query_sample,
    COUNT(*) as query_count,
    AVG(query_time) as avg_time,
    AVG(rows_examined) as avg_rows_examined,
    AVG(rows_sent) as avg_rows_sent
FROM slow_log_analysis
GROUP BY LEFT(query, 100)
ORDER BY avg_time DESC
LIMIT 10;

常见慢查询模式及优化：

-- 1. 全表扫描优化
-- 慢查询
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

-- 优化后
SELECT * FROM employees 
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';
-- 添加索引：ALTER TABLE employees ADD INDEX idx_hire_date (hire_date);

-- 2. OR条件优化
-- 慢查询
SELECT * FROM employees 
WHERE dept_id = 1 OR dept_id = 2 OR salary > 10000;

-- 优化后
SELECT * FROM employees WHERE dept_id IN (1, 2)
UNION
SELECT * FROM employees WHERE salary > 10000;

-- 3. 分页优化
-- 慢查询（偏移量大时）
SELECT * FROM employees ORDER BY emp_id LIMIT 10000, 20;

-- 优化后
SELECT * FROM employees 
WHERE emp_id > 10000 
ORDER BY emp_id 
LIMIT 20;

-- 4. LIKE优化
-- 慢查询
SELECT * FROM employees WHERE emp_name LIKE '%张%';

-- 优化方案
-- 使用全文索引或添加反转索引
ALTER TABLE employees ADD FULLTEXT idx_name_ft (emp_name);
SELECT * FROM employees 
WHERE MATCH(emp_name) AGAINST('张' IN BOOLEAN MODE);

查询重写技巧与模式

查询重写模式：

-- 1. 使用EXISTS替代IN（当子查询数据量大时）
-- 原始查询
SELECT * FROM employees 
WHERE dept_id IN (SELECT dept_id FROM departments WHERE budget > 100000);

-- 优化后
SELECT e.* FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.dept_id = e.dept_id AND d.budget > 100000
);

-- 2. 使用JOIN替代子查询
-- 原始查询
SELECT emp_name, 
       (SELECT dept_name FROM departments WHERE dept_id = employees.dept_id) as dept_name
FROM employees;

-- 优化后
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- 3. 避免SELECT *
-- 原始查询
SELECT * FROM employees WHERE dept_id = 1;

-- 优化后
SELECT emp_id, emp_name, email, salary 
FROM employees WHERE dept_id = 1;

-- 4. 使用批处理替代循环
-- 不好的做法（在应用程序中循环）
-- FOR each id IN ids: 
--   UPDATE employees SET status = 1 WHERE emp_id = id

-- 好的做法
UPDATE employees SET status = 1 WHERE emp_id IN (1, 2, 3, 4, 5);

高级查询重写：

-- 5. 使用派生表优化复杂查询
-- 原始复杂查询
SELECT 
    e.emp_name,
    d.dept_name,
    (SELECT COUNT(*) FROM projects p WHERE p.manager_id = e.emp_id) as project_count
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);

-- 优化后使用派生表
WITH dept_stats AS (
    SELECT 
        dept_id,
        AVG(salary) as avg_salary
    FROM employees
    GROUP BY dept_id
),
emp_projects AS (
    SELECT 
        manager_id,
        COUNT(*) as project_count
    FROM projects
    GROUP BY manager_id
)
SELECT 
    e.emp_name,
    d.dept_name,
    COALESCE(ep.project_count, 0) as project_count
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN dept_stats ds ON e.dept_id = ds.dept_id
LEFT JOIN emp_projects ep ON e.emp_id = ep.manager_id
WHERE e.salary > ds.avg_salary;

-- 6. 条件顺序优化
-- 原始查询（选择性差的条件在前）
SELECT * FROM employees 
WHERE status = 1  -- 可能80%的数据status=1
  AND hire_date > '2023-01-01'  -- 只有5%的数据满足
  AND dept_id = 2;  -- 只有10%的数据满足

-- 优化后（选择性好的条件在前）
SELECT * FROM employees 
WHERE dept_id = 2  -- 先过滤掉90%的数据
  AND hire_date > '2023-01-01'  -- 在剩下的10%中再过滤
  AND status = 1;  -- 最后过滤

分页查询优化方案

传统分页的问题：

-- 问题：偏移量大时性能差
SELECT * FROM employees 
ORDER BY emp_id 
LIMIT 100000, 20;  -- 需要扫描100000+20行

-- 使用索引覆盖优化
SELECT emp_id, emp_name, email  -- 只选择需要的列
FROM employees 
ORDER BY emp_id 
LIMIT 100000, 20;

-- 添加覆盖索引
ALTER TABLE employees ADD INDEX idx_cover (emp_id, emp_name, email);

高效分页方案：

-- 方案1：游标分页（推荐）
-- 第一页
SELECT * FROM employees 
ORDER BY emp_id 
LIMIT 20;

-- 获取最后一条记录的emp_id: 比如是20
-- 第二页
SELECT * FROM employees 
WHERE emp_id > 20  -- 使用游标
ORDER BY emp_id 
LIMIT 20;

-- 方案2：使用子查询（MySQL 8.0+）
SELECT * FROM employees 
WHERE emp_id IN (
    SELECT emp_id FROM employees 
    ORDER BY emp_id 
    LIMIT 100000, 20
)
ORDER BY emp_id;

-- 方案3：延迟关联
SELECT e.* 
FROM employees e
JOIN (
    SELECT emp_id 
    FROM employees 
    ORDER BY emp_id 
    LIMIT 100000, 20
) tmp ON e.emp_id = tmp.emp_id;

-- 方案4：业务分页（按时间范围）
SELECT * FROM employees 
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY emp_id 
LIMIT 20;

复杂分页场景：

-- 多条件排序分页
CREATE INDEX idx_dept_hire_salary ON employees (dept_id, hire_date, salary);

-- 游标分页实现
-- 第一页
SELECT * FROM employees 
WHERE dept_id = 1
ORDER BY hire_date DESC, salary DESC, emp_id
LIMIT 20;

-- 假设最后一条记录：hire_date='2023-05-20', salary=8000, emp_id=150
-- 第二页
SELECT * FROM employees 
WHERE dept_id = 1
  AND (
      hire_date < '2023-05-20' 
      OR (hire_date = '2023-05-20' AND salary < 8000)
      OR (hire_date = '2023-05-20' AND salary = 8000 AND emp_id > 150)
  )
ORDER BY hire_date DESC, salary DESC, emp_id
LIMIT 20;

大数据量查询处理策略

分批处理策略：

-- 大量数据更新分批处理
DELIMITER //

CREATE PROCEDURE BatchUpdateEmployees()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE current_id INT DEFAULT 0;
    DECLARE max_id INT;
    
    -- 获取最大ID
    SELECT MAX(emp_id) INTO max_id FROM employees;
    
    WHILE current_id < max_id DO
        -- 分批更新
        UPDATE employees 
        SET last_updated = NOW()
        WHERE emp_id > current_id 
          AND emp_id <= current_id + batch_size;
        
        -- 记录处理进度
        INSERT INTO batch_process_log (process_name, processed_id, processed_at)
        VALUES ('employee_update', current_id + batch_size, NOW());
        
        SET current_id = current_id + batch_size;
        
        -- 短暂休眠，减少对系统的影响
        DO SLEEP(0.1);
    END WHILE;
    
END //

DELIMITER ;

数据归档策略：

-- 历史数据归档
CREATE TABLE employees_archive LIKE employees;

-- 归档过程
DELIMITER //

CREATE PROCEDURE ArchiveOldEmployees(IN cutoff_date DATE)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 归档数据
    INSERT INTO employees_archive 
    SELECT * FROM employees 
    WHERE hire_date < cutoff_date;
    
    -- 删除已归档数据
    DELETE FROM employees 
    WHERE hire_date < cutoff_date;
    
    COMMIT;
    
END //

DELIMITER ;

2. 索引优化实战

索引创建策略与原则

索引选择策略：

-- 索引创建决策流程
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION,
    NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'your_table';

-- 计算索引选择性
SELECT 
    COUNT(DISTINCT dept_id) as distinct_values,
    COUNT(*) as total_rows,
    ROUND(COUNT(DISTINCT dept_id) / COUNT(*) * 100, 2) as selectivity_percent
FROM employees;

-- 选择性建议：
-- > 20%：适合创建索引
-- 5%-20%：根据查询频率决定
-- < 5%：通常不适合单独创建索引

复合索引设计：

-- 好的复合索引设计
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date DATETIME NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered') NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    -- 其他字段...
    
    -- 复合索引设计
    INDEX idx_customer_date (customer_id, order_date),           -- 客户订单查询
    INDEX idx_date_status (order_date, status),                  -- 按状态查询订单
    INDEX idx_status_date (status, order_date),                  -- 状态+日期查询
    INDEX idx_customer_status_date (customer_id, status, order_date) -- 覆盖多种查询
);

-- 索引使用分析
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 123 
  AND order_date >= '2023-01-01'
  AND status = 'shipped';
-- 可能使用 idx_customer_date 或 idx_customer_status_date

索引失效场景分析

常见索引失效场景：

-- 1. 对索引列使用函数
-- 索引失效
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;
SELECT * FROM employees WHERE UPPER(emp_name) = 'ZHANGSAN';

-- 优化后
SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';
SELECT * FROM employees WHERE emp_name = 'zhangsan';  -- 应用层处理大小写

-- 2. 隐式类型转换
-- 索引失效（如果phone是varchar类型）
SELECT * FROM users WHERE phone = 13800138000;

-- 优化后
SELECT * FROM users WHERE phone = '13800138000';

-- 3. OR条件使用不当
-- 索引可能失效
SELECT * FROM employees 
WHERE dept_id = 1 OR emp_name LIKE '张%';

-- 优化后
SELECT * FROM employees WHERE dept_id = 1
UNION
SELECT * FROM employees WHERE emp_name LIKE '张%';

-- 4. 使用NOT、!=、<> 
-- 索引通常失效
SELECT * FROM employees WHERE dept_id != 1;

-- 优化方案：考虑是否真的需要排除，或者使用范围查询
SELECT * FROM employees WHERE dept_id > 1 OR dept_id < 1;

-- 5. LIKE以通配符开头
-- 索引失效
SELECT * FROM employees WHERE emp_name LIKE '%张%';

-- 优化方案：使用全文索引或反转存储

复合索引失效场景：

-- 创建测试索引
CREATE INDEX idx_dept_hire_salary ON employees (dept_id, hire_date, salary);

-- 1. 不满足最左前缀原则
-- 索引失效
SELECT * FROM employees WHERE hire_date > '2023-01-01';
-- 只能使用hire_date的索引，不能使用复合索引

-- 2. 跳过中间列
-- 部分使用索引（只用到dept_id）
SELECT * FROM employees 
WHERE dept_id = 1 AND salary > 5000;

-- 3. 范围查询后的列无法使用索引
-- 只使用dept_id和hire_date进行索引查找，salary使用索引过滤
SELECT * FROM employees 
WHERE dept_id = 1 
  AND hire_date > '2023-01-01'
  AND salary > 5000;

-- 4. 索引列顺序影响
-- 好的顺序：等值查询列在前，范围查询列在后
CREATE INDEX idx_good_order ON employees (dept_id, salary, hire_date);

前缀索引与函数索引

前缀索引：

-- 文本列的前缀索引
-- 计算合适的前缀长度
SELECT 
    COUNT(DISTINCT LEFT(emp_name, 5)) as distinct_5,
    COUNT(DISTINCT LEFT(emp_name, 10)) as distinct_10,
    COUNT(DISTINCT emp_name) as distinct_full,
    COUNT(*) as total_rows
FROM employees;

-- 创建前缀索引
CREATE INDEX idx_emp_name_prefix ON employees (emp_name(10));

-- 前缀索引的使用限制
-- 不能用于ORDER BY和GROUP BY完整列
-- 不能用于覆盖索引

-- 查看索引统计信息
ANALYZE TABLE employees;
SHOW INDEX FROM employees;

函数索引（MySQL 8.0+）：

-- 创建函数索引
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200),
    product_data JSON,
    created_at DATESTAMP
);

-- 函数索引示例
CREATE INDEX idx_product_name_upper 
ON products ((UPPER(product_name)));

CREATE INDEX idx_product_price 
ON products ((CAST(JSON_EXTRACT(product_data, '$.price') AS DECIMAL(10,2))));

CREATE INDEX idx_created_date 
ON products ((DATE(created_at)));

-- 使用函数索引查询
SELECT * FROM products 
WHERE UPPER(product_name) = UPPER('iPhone 14');

SELECT * FROM products 
WHERE CAST(JSON_EXTRACT(product_data, '$.price') AS DECIMAL(10,2)) > 1000;

SELECT * FROM products 
WHERE DATE(created_at) = '2023-01-01';

索引维护与重建策略

索引监控：

-- 监控索引使用情况
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY COUNT_FETCH DESC;

-- 查找未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_FETCH = 0
  AND COUNT_INSERT = 0
  AND COUNT_UPDATE = 0
  AND COUNT_DELETE = 0;

索引维护操作：

-- 索引重建
ALTER TABLE employees ENGINE=InnoDB;  -- 重建表，包括所有索引
ALTER TABLE employees DROP INDEX idx_old, ADD INDEX idx_new (columns);
OPTIMIZE TABLE employees;  -- 重建表，整理碎片

-- 在线索引操作（MySQL 5.6+）
ALTER TABLE employees 
ADD INDEX idx_new_column (new_column),
ALGORITHM=INPLACE, 
LOCK=NONE;

-- 索引统计信息更新
ANALYZE TABLE employees;  -- 更新统计信息

-- 监控索引大小
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(SUM(INDEX_LENGTH) / 1024 / 1024, 2) as index_size_mb
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'your_database'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY index_size_mb DESC;

全文索引与空间索引应用

全文索引：

-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT idx_content_ft (title, content);

-- 全文索引查询
SELECT 
    article_id,
    title,
    MATCH(title, content) AGAINST('数据库 优化') as relevance_score
FROM articles
WHERE MATCH(title, content) AGAINST('+数据库 +优化' IN BOOLEAN MODE)
ORDER BY relevance_score DESC;

-- 全文索引配置
-- 查看最小词长
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
SHOW VARIABLES LIKE 'innodb_ft_max_token_size';

-- 停用词配置
SHOW VARIABLES LIKE 'innodb_ft_enable_stopword';
SHOW VARIABLES LIKE 'innodb_ft_server_stopword_table';

-- 重建全文索引
ALTER TABLE articles DROP INDEX idx_content_ft;
ALTER TABLE articles ADD FULLTEXT idx_content_ft (title, content);

空间索引：

-- 创建空间数据表
CREATE TABLE locations (
    location_id INT PRIMARY KEY AUTO_INCREMENT,
    location_name VARCHAR(100),
    coordinates POINT NOT NULL,
    area POLYGON,
    SPATIAL INDEX idx_coordinates (coordinates),
    SPATIAL INDEX idx_area (area)
);

-- 空间索引查询
SELECT 
    location_name,
    ST_AsText(coordinates) as coords
FROM locations
WHERE ST_Contains(
    ST_GeomFromText('POLYGON((116.3 39.8, 116.5 39.8, 116.5 40.0, 116.3 40.0, 116.3 39.8))'),
    coordinates
);

-- 距离查询优化
SELECT 
    l1.location_name as place1,
    l2.location_name as place2,
    ST_Distance_Sphere(l1.coordinates, l2.coordinates) as distance
FROM locations l1
JOIN locations l2 ON l1.location_id < l2.location_id
WHERE ST_Distance_Sphere(l1.coordinates, l2.coordinates) < 5000;  -- 5公里内

3. 服务器参数调优

内存参数优化

InnoDB缓冲池优化：

-- 查看当前缓冲池配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
SHOW VARIABLES LIKE 'innodb_buffer_pool_chunk_size';

-- 计算合适的缓冲池大小
SELECT 
    @@innodb_buffer_pool_size / 1024 / 1024 / 1024 as current_buffer_pool_gb,
    @@innodb_buffer_pool_instances as buffer_pool_instances;

-- 缓冲池使用监控
SHOW ENGINE INNODB STATUS\G
-- 查看 BUFFER POOL AND MEMORY 部分

-- 在线调整缓冲池（MySQL 5.7+）
SET GLOBAL innodb_buffer_pool_size = 8589934592;  -- 8GB

-- 监控缓冲池命中率
SELECT 
    (1 - (variable_value / (
        SELECT variable_value 
        FROM information_schema.global_status 
        WHERE variable_name = 'innodb_buffer_pool_read_requests'
    ))) * 100 as buffer_pool_hit_rate
FROM information_schema.global_status 
WHERE variable_name = 'innodb_buffer_pool_reads';

内存参数配置建议：

# my.cnf 内存配置示例
[mysqld]
# 缓冲池配置（建议为系统内存的70-80%）
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M

# 日志缓冲区
innodb_log_buffer_size = 256M

# 排序缓冲区
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
join_buffer_size = 2M

# 临时表
tmp_table_size = 64M
max_heap_table_size = 64M

# 连接内存
thread_cache_size = 100
table_open_cache = 4000
table_definition_cache = 2000

I/O参数调优

InnoDB I/O优化：

-- 查看I/O相关配置
SHOW VARIABLES LIKE 'innodb_io_capacity%';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'innodb_flush_method';
SHOW VARIABLES LIKE 'innodb_read_io_threads';
SHOW VARIABLES LIKE 'innodb_write_io_threads';

-- I/O性能监控
SHOW STATUS LIKE 'innodb%io%';

I/O参数配置建议：

# my.cnf I/O配置示例
[mysqld]
# I/O容量（根据存储设备性能调整）
# SSD: 2000-10000, HDD: 200-500
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# 日志刷盘策略
# 1: 最高安全性（每次提交刷盘）
# 2: 折中（每秒刷盘）
# 0: 最高性能（每秒刷盘，崩溃可能丢失1秒数据）
innodb_flush_log_at_trx_commit = 1

# 刷盘方法（Linux推荐O_DIRECT）
innodb_flush_method = O_DIRECT

# I/O线程数
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 预读设置
innodb_random_read_ahead = ON

# 双写缓冲（SSD可考虑关闭）
innodb_doublewrite = ON

连接数与会话管理

连接配置优化：

-- 查看连接相关配置
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'max_user_connections';
SHOW VARIABLES LIKE 'thread_cache_size';
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

-- 监控连接状态
SHOW STATUS LIKE 'Threads_%';
SHOW PROCESSLIST;

-- 连接使用分析
SELECT 
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 100) as query_sample
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

连接优化配置：

# my.cnf 连接配置示例
[mysqld]
# 最大连接数
max_connections = 1000

# 用户最大连接数
max_user_connections = 500

# 线程缓存
thread_cache_size = 100

# 超时设置
wait_timeout = 600
interactive_timeout = 600

# 连接限制
max_connect_errors = 100000

# 反向解析（建议关闭提升性能）
skip_name_resolve = 1

复制参数配置优化

主从复制优化：

-- 查看复制配置
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'sync_binlog';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- 监控复制状态
SHOW SLAVE STATUS\G

-- 复制性能参数
SHOW VARIABLES LIKE 'slave_parallel_workers';
SHOW VARIABLES LIKE 'slave_parallel_type';

复制优化配置：

# my.cnf 复制配置示例
[mysqld]
# 二进制日志格式
binlog_format = ROW

# 二进制日志同步
sync_binlog = 1

# 从库并行复制
slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK

# 复制延迟控制
slave_preserve_commit_order = 1

# 二进制日志保留
expire_logs_days = 7
binlog_expire_logs_seconds = 604800

监控指标与调优依据

关键性能指标监控：

-- 创建性能监控表
CREATE TABLE performance_metrics (
    metric_id INT AUTO_INCREMENT PRIMARY KEY,
    metric_name VARCHAR(100) NOT NULL,
    metric_value DECIMAL(20,4),
    collected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT
);

-- 收集关键指标
INSERT INTO performance_metrics (metric_name, metric_value)
SELECT 'qps', VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME = 'Queries';

INSERT INTO performance_metrics (metric_name, metric_value)
SELECT 'tps', VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME = 'Com_commit';

-- 缓冲池命中率
INSERT INTO performance_metrics (metric_name, metric_value)
SELECT 'buffer_pool_hit_rate', 
    (1 - ( 
        SELECT VARIABLE_VALUE 
        FROM information_schema.GLOBAL_STATUS 
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    ) / (
        SELECT VARIABLE_VALUE 
        FROM information_schema.GLOBAL_STATUS 
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    )) * 100;

-- 连接使用率
INSERT INTO performance_metrics (metric_name, metric_value)
SELECT 'connection_usage_rate',
    ( 
        SELECT VARIABLE_VALUE 
        FROM information_schema.GLOBAL_STATUS 
        WHERE VARIABLE_NAME = 'Threads_connected'
    ) / (
        SELECT VARIABLE_VALUE 
        FROM information_schema.GLOBAL_VARIABLES 
        WHERE VARIABLE_NAME = 'max_connections'
    ) * 100;

性能调优检查清单：

-- 系统健康检查查询
SELECT 
    '连接数' as metric,
    CONCAT(
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected'),
        '/',
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'max_connections')
    ) as value
UNION ALL
SELECT 
    '缓冲池命中率',
    CONCAT(
        ROUND((1 - ( 
            SELECT VARIABLE_VALUE 
            FROM information_schema.GLOBAL_STATUS 
            WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
        ) / (
            SELECT VARIABLE_VALUE 
            FROM information_schema.GLOBAL_STATUS 
            WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
        )) * 100, 2),
        '%'
    )
UNION ALL
SELECT 
    '临时表磁盘使用率',
    CONCAT(
        ROUND((
            SELECT VARIABLE_VALUE 
            FROM information_schema.GLOBAL_STATUS 
            WHERE VARIABLE_NAME = 'Created_tmp_disk_tables'
        ) / NULLIF(
            SELECT VARIABLE_VALUE 
            FROM information_schema.GLOBAL_STATUS 
            WHERE VARIABLE_NAME = 'Created_tmp_tables'
        , 0) * 100, 2),
        '%'
    )
UNION ALL
SELECT 
    '慢查询比例',
    CONCAT(
        ROUND((
            SELECT VARIABLE_VALUE 
            FROM information_schema.GLOBAL_STATUS 
            WHERE VARIABLE_NAME = 'Slow_queries'
        ) / NULLIF(
            SELECT VARIABLE_VALUE 
            FROM information_schema.GLOBAL_STATUS 
            WHERE VARIABLE_NAME = 'Questions'
        , 0) * 100, 4),
        '%'
    );

总结

通过本篇的深入学习，我们掌握了MySQL性能优化的核心技能：

1. 查询优化：深入理解执行计划，掌握慢查询分析和查询重写技巧
2. 索引优化：合理设计索引，避免索引失效，掌握索引维护策略
3. 服务器调优：优化内存、I/O、连接等关键参数配置
4. 监控体系：建立完善的性能监控和报警机制

关键优化原则：

• 测量优先：基于实际监控数据进行优化
• 渐进优化：每次只调整一个参数，观察效果
• 平衡考虑：在性能、安全、可靠性之间找到平衡点
• 预防为主：建立常规维护和监控机制

性能优化层次：

1. SQL和索引优化：效果最明显，成本最低
2. 数据库配置优化：需要深入了解MySQL内部机制
3. 架构优化：读写分离、分库分表等
4. 硬件优化：SSD、更多内存、更好CPU

动手练习：

1. 分析你当前系统的慢查询，并实施优化
2. 检查索引使用情况，删除无用索引，添加必要索引
3. 根据服务器配置调整MySQL参数
4. 建立性能监控体系，定期收集关键指标
5. 实施定期的数据库维护操作

欢迎在评论区分享你的性能优化经验和遇到的问题！