MySQL/MariaDB数据库性能优化完整教程

meta-description: MySQL与MariaDB数据库性能优化完整教程，详解索引优化、查询缓存和配置调优。

keywords: MySQL优化,MariaDB优化,数据库索引,查询缓存,my.cnf配置

# MySQL/MariaDB数据库性能优化完整教程

本文详细介绍MySQL和MariaDB数据库的性能优化方法，包括索引优化、查询缓存配置和参数调优。

## 为什么要优化数据库

| 瓶颈 | 影响 |

|------|------|

| 慢查询 | 页面加载慢 |

| 缺少索引 | 全表扫描 |

| 配置不当 | 内存浪费 |

| 连接数过多 | 服务崩溃 |

## 索引优化

### 检查缺失索引

-- 查找全表扫描的查询
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 AS avg_ms,
    ROWS_SENT,
    ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

### 创建合适索引

-- 单列索引
CREATE INDEX idx_post_status ON wp_posts(post_status);
CREATE INDEX idx_post_type ON wp_posts(post_type);

-- 复合索引（注意顺序）
CREATE INDEX idx_status_type_date ON wp_posts(post_status, post_type, post_date);

-- 前缀索引（大文本字段）
CREATE INDEX idx_post_title ON wp_posts(post_title(50));

### 索引使用原则

| 原则 | 说明 |

|------|------|

| 最左前缀 | 复合索引从左匹配 |

| 区分度高的列优先 | 性别列不适合索引 |

| 控制索引数量 | 过多影响写入性能 |

| 定期分析 | 使用ANALYZE TABLE |

## 查询缓存优化

### MySQL查询缓存配置

# /etc/mysql/my.cnf
[mysqld]
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M
query_cache_min_res_unit = 2k

### 监控查询缓存

-- 查看缓存状态
SHOW STATUS LIKE 'Qcache%';

-- 关键指标
/*
Qcache_hits: 缓存命中次数
Qcache_inserts: 缓存插入次数
Qcache_lowmem_prunes: 内存不足删除次数
*/

## InnoDB缓冲池优化

### 核心配置

# innodb_buffer_pool_size
# 规则：物理内存的70-80%
innodb_buffer_pool_size = 4G  # 8G内存服务器

# innodb_buffer_pool_instances
# 缓冲池实例数（每个至少1G）
innodb_buffer_pool_instances = 4

# innodb_log_file_size
# 日志文件大小（通常设置为缓冲池的25%）
innodb_log_file_size = 1G

### 验证配置

-- 查看缓冲池状态
SHOW ENGINE INNODB STATUS\G

-- 查看缓冲池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

## 慢查询分析

### 开启慢查询日志

# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  # 超过2秒记录
log_queries_not_using_indexes = 1

### 分析慢查询

# 使用mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 使用pt-query-digest（更准确）
pt-query-digest /var/log/mysql/slow.log

### EXPLAIN分析

-- 分析查询执行计划
EXPLAIN SELECT * FROM wp_posts 
WHERE post_status='publish' 
AND post_type='post' 
ORDER BY post_date DESC 
LIMIT 10;

/*
关键字段解读：
- type: 访问类型（const > eq_ref > ref > range > index > ALL）
- key: 实际使用的索引
- rows: 扫描行数
- Extra: 附加信息
*/

## WordPress数据库优化

### wp_options表清理

-- 清理自动草稿
DELETE FROM wp_posts WHERE post_status='auto-draft';

-- 清理文章修订版
DELETE FROM wp_posts WHERE post_type='revision';

-- 清理过期瞬态
DELETE FROM wp_options WHERE option_name LIKE '%_transient_timeout%' AND option_value < UNIX_TIMESTAMP();

-- 优化表
OPTIMIZE TABLE wp_options, wp_posts, wp_postmeta;

### 常用优化插件

| 插件 | 功能 |

|------|------|

| WP-Optimize | 数据库清理 |

| Advanced Database Cleaner | 深度清理 |

| Index WP MySQL For Speed | 索引优化 |

## 主从复制配置

### 主服务器配置

# my.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = wordpress

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 查看主状态
SHOW MASTER STATUS;

### 从服务器配置

# my.cnf
[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin
log_bin = /var/log/mysql/mysql-bin.log
read_only = 1

-- 配置复制
CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

-- 启动复制
START SLAVE;

## 完整优化配置示例

# /etc/mysql/my.cnf 完整优化示例
[mysqld]
# 基础设置
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql

# InnoDB设置
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# 连接设置
max_connections = 200
max_connect_errors = 10000

# 查询缓存
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

# 慢查询
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 表定义缓存
table_open_cache = 2000
table_definition_cache = 1400

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

数据库优化是提升WordPress性能的关键，合理配置可以让网站速度提升50%以上。