技术数据库

MySQL性能优化与运维实战完全指南：从索引调优到分库分表的进阶教程

, , , ,

MySQL性能优化与运维实战完全指南：从索引调优到分库分表的进阶教程

MySQL作为最流行的开源关系型数据库，广泛应用于各类业务场景。随着数据量的增长和业务复杂度的提升，数据库性能优化成为系统架构中的关键环节。本文将从索引原理到架构设计，全面介绍MySQL性能优化的核心技术和最佳实践。

一、MySQL索引原理与优化

1.1 索引基础概念

索引是数据库优化查询性能的核心手段。理解索引的工作原理，是进行SQL优化的基础。

B+树索引结构：

1
2
3
4
5
         [20, 40, 60]
        /    |    \
 [5,10,15] [25,30,35] [45,50,55] [65,70,75]
 / |  \    /  |  \    /  |  \    /  |  \
5 10 15  25 30 35  45 50 55  65 70 75

索引类型对比：

索引类型 特点 适用场景
主键索引 唯一标识记录，聚簇索引 表的主键
唯一索引 列值唯一，可有空值 唯一约束的列
普通索引 无唯一约束 频繁查询的列
组合索引 多列联合索引 多条件查询
全文索引 支持全文搜索 文本搜索
空间索引 支持空间数据 GIS应用

1.2 索引设计原则

最左前缀原则：

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建组合索引
CREATE INDEX idx_name_age ON users(name, age, city);

-- 有效使用索引
SELECT * FROM users WHERE name = '张三';                    -- 使用第1列
SELECT * FROM users WHERE name = '张三' AND age = 20;       -- 使用第1、2列
SELECT * FROM users WHERE name = '张三' AND city = '北京';  -- 使用第1列

-- 无效使用索引
SELECT * FROM users WHERE age = 20;                       -- 缺少第1列
SELECT * FROM users WHERE city = '北京';                   -- 缺少第1列
SELECT * FROM users WHERE age = 20 AND city = '北京';      -- 缺少第1列

覆盖索引（Covering Index）：

1
2
3
4
5
6
-- 创建覆盖索引
CREATE INDEX idx_name_email ON users(name, email);

-- 查询使用覆盖索引，无需回表
SELECT name, email FROM users WHERE name = '张三';
-- Extra列显示：Using index

索引选择性：

1
2
3
4
5
6
7
8
-- 计算选择性
SELECT
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

-- 选择性越高，索引效果越好
-- 选择性接近1的列适合建索引
-- 选择性低的列（如性别）不适合建索引

1.3 索引优化实践

查看索引使用情况：

1
2
3
4
5
6
7
8
-- 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 详细执行计划（JSON格式）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = '张三';

-- 查看索引统计信息
SHOW INDEX FROM users;

EXPLAIN输出字段说明：

字段 说明 优化建议
id 查询标识 关注多表连接的顺序
select_type 查询类型 避免DEPENDENT SUBQUERY
table 表名 -
type 访问类型 目标：ref或range，避免ALL
possible_keys 可能使用的索引 -
key 实际使用的索引 关注是否使用索引
key_len 使用的索引长度 越长越好（覆盖更多列）
ref 索引比较的值 -
rows 扫描的行数 越少越好
Extra 额外信息 避免Using filesort、Using temporary

type字段优化目标：

1
2
3
4
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >
unique_subquery > index_subquery > range > index > ALL

目标：至少达到range级别，最好是ref或eq_ref

二、SQL查询优化30条法则

2.1 SELECT优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 1. 明确指定列，避免SELECT *
SELECT id, name, email FROM users;

-- 2. 使用LIMIT限制结果集
SELECT * FROM users LIMIT 10;

-- 3. 使用覆盖索引
SELECT name, email FROM users WHERE name = '张三';

-- 4. 避免在WHERE子句中对字段进行函数操作
-- 错误写法：
SELECT * FROM users WHERE YEAR(create_time) = 2024;
-- 正确写法：
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- 5. 避免在WHERE子句中对字段进行表达式操作
-- 错误写法：
SELECT * FROM users WHERE age * 2 = 40;
-- 正确写法：
SELECT * FROM users WHERE age = 20;

2.2 WHERE优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 6. 使用IN替代OR（少量值时）
SELECT * FROM users WHERE id IN (1, 2, 3);

-- 7. 使用EXISTS替代IN（子查询返回大量数据时）
-- 使用EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 8. 避免使用!=或<>操作符
-- 考虑使用范围查询或UNION代替

-- 9. 避免使用NULL判断
-- 错误写法：
SELECT * FROM users WHERE name IS NULL;
-- 优化方案：为列设置默认值

-- 10. 使用BETWEEN替代IN（范围查询）
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

2.3 JOIN优化

1
2
3
4
5
6
7
8
9
10
11
12
-- 11. 确保JOIN列有索引
-- 12. 小表驱动大表
-- 错误写法（大表驱动）：
SELECT * FROM big_table b JOIN small_table s ON b.id = s.id;
-- 正确写法（小表驱动）：
SELECT * FROM small_table s JOIN big_table b ON s.id = b.id;

-- 13. 避免JOIN过多表（建议不超过3个）
-- 14. 使用STRAIGHT_JOIN强制连接顺序
SELECT STRAIGHT_JOIN * FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
JOIN table3 t3 ON t2.id = t3.id;

2.4 ORDER BY和GROUP BY优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 15. 使用索引列进行排序
-- 确保ORDER BY的列有索引
SELECT * FROM users ORDER BY create_time DESC;

-- 16. 避免多列排序方向不一致
-- 错误写法（无法使用索引）：
SELECT * FROM users ORDER BY age ASC, score DESC;
-- 正确写法：
SELECT * FROM users ORDER BY age DESC, score DESC;

-- 17. GROUP BY优化
-- 错误写法：
SELECT category, COUNT(*) FROM products GROUP BY category;
-- 优化方案：创建(category)索引

-- 18. 使用松散索引扫描
SELECT DISTINCT category FROM products;

2.5 子查询优化

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 19. 将子查询转换为JOIN
-- 原始写法：
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- 优化写法：
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id;

-- 20. 使用派生表优化
SELECT * FROM (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
) t WHERE t.order_count > 10;

2.6 分页优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 21. 避免深分页
-- 低效写法：
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;

-- 优化方案1：使用覆盖索引+子查询
SELECT * FROM users
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 10;

-- 优化方案2：使用上一页最大ID
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 优化方案3：使用JOIN
SELECT u.* FROM users u
JOIN (SELECT id FROM users ORDER BY id LIMIT 1000000, 10) tmp
ON u.id = tmp.id;

2.7 其他优化技巧

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- 22. 使用UNION ALL替代UNION（允许重复数据时）
SELECT name FROM users WHERE age < 18
UNION ALL
SELECT name FROM users WHERE age > 60;

-- 23. 使用INSERT批量插入
INSERT INTO users (name, email) VALUES
    ('张三', 'zhangsan@example.com'),
    ('李四', 'lisi@example.com'),
    ('王五', 'wangwu@example.com');

-- 24. 使用REPLACE或INSERT ON DUPLICATE KEY UPDATE
INSERT INTO user_stats (user_id, visit_count)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE visit_count = visit_count + 1;

-- 25. 使用DELAYED插入（对实时性要求不高的场景）
INSERT DELAYED INTO logs (message) VALUES ('log message');

-- 26. 合理使用索引提示
SELECT * FROM users USE INDEX (idx_name) WHERE name = '张三';

-- 27. 使用FORCE INDEX强制使用索引
SELECT * FROM users FORCE INDEX (idx_name) WHERE name = '张三';

-- 28. 定期分析表
ANALYZE TABLE users;

-- 29. 优化表空间
OPTIMIZE TABLE users;

-- 30. 使用分区表（大数据量场景）
CREATE TABLE logs (
    id INT AUTO_INCREMENT,
    log_time DATETIME,
    message TEXT,
    PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

三、MySQL主从复制架构

3.1 主从复制原理

复制流程：

1
2
3
4
5
6
7
8
9
10
11
Master                           Slave
  |                                |
  |  1. 写操作                     |
  |------------------------------->|
  |                                |
  |  2. Binlog                     |
  |  3. Binlog Dump Thread  ------>|
  |                                |  4. I/O Thread
  |                                |  5. Relay Log
  |                                |  6. SQL Thread
  |                                |  7. 重放SQL

3.2 主从配置

主服务器配置（my.cnf）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[mysqld]
server-id = 1
log-bin = mysql-bin
log-bin-index = mysql-bin.index
expire_logs_days = 7
max_binlog_size = 500M

# 需要复制的数据库
binlog-do-db = dbname
# 忽略的数据库
binlog-ignore-db = mysql

# 行级复制（推荐）
binlog_format = ROW

从服务器配置（my.cnf）：

1
2
3
4
5
6
7
8
9
10
[mysqld]
server-id = 2
relay-log = relay-bin
relay-log-index = relay-bin.index

# 只读（可选）
read_only = 1

# 复制错误跳过（谨慎使用）
slave-skip-errors = 1062

3.3 主从配置步骤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 1. 主服务器创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 2. 主服务器锁定表
FLUSH TABLES WITH READ LOCK;

-- 3. 查看主服务器状态
SHOW MASTER STATUS;
-- +------------------+----------+--------------+------------------+
-- | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
-- +------------------+----------+--------------+------------------+
-- | mysql-bin.000001 |     154  | dbname       | mysql            |
-- +------------------+----------+--------------+------------------+

-- 4. 导出主服务器数据
-- mysqldump -u root -p --all-databases --master-data > master.sql

-- 5. 解锁主服务器表
UNLOCK TABLES;

-- 6. 从服务器导入数据
-- mysql -u root -p < master.sql

-- 7. 从服务器配置复制
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

-- 8. 启动从服务器复制
START SLAVE;

-- 9. 查看从服务器状态
SHOW SLAVE STATUS\G;
-- 确认：Slave_IO_Running: Yes
--       Slave_SQL_Running: Yes

3.4 MySQL 5.7并行复制

1
2
3
4
5
6
7
# 从服务器配置
[mysqld]
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON

四、分库分表策略

4.1 垂直拆分

按业务模块拆分：

原数据库 拆分后
电商库(ecommerce) 用户库(user_db)
商品库(product_db)
订单库(order_db)
库存库(stock_db)

优点：

  • 业务清晰，职责单一
  • 便于维护和管理
  • 降低单库复杂度

缺点：

  • 分布式事务问题
  • 跨库JOIN复杂

4.2 水平拆分

按数据范围拆分：

1
2
3
user_db_0: user_id 0 - 9999999
user_db_1: user_id 10000000 - 19999999
user_db_2: user_id 20000000 - 29999999

按Hash取模拆分：

1
2
3
4
5
6
7
// 分片算法
dbIndex = userId % dbCount;
tableIndex = userId % tableCount;

// 示例：userId=12345678, dbCount=4, tableCount=8
dbIndex = 12345678 % 4 = 2;      // 使用user_db_2
tableIndex = 12345678 % 8 = 6;   // 使用user_6表

一致性Hash：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
// 一致性Hash算法，减少扩容时的数据迁移
TreeMap<Long, String> nodes = new TreeMap<>();

// 添加节点
for (String node : serverNodes) {
    for (int i = 0; i < 150; i++) {  // 虚拟节点
        long hash = hash(node + i);
        nodes.put(hash, node);
    }
}

// 获取节点
public String getNode(String key) {
    long hash = hash(key);
    SortedMap<Long, String> tailMap = nodes.tailMap(hash);
    if (tailMap.isEmpty()) {
        return nodes.get(nodes.firstKey());
    }
    return tailMap.get(tailMap.firstKey());
}

4.3 分库分表示例

用户表分片：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建4个库，每个库8张表
-- user_db_0 ~ user_db_3
-- user_0 ~ user_7

-- 分片规则：user_id % 32
-- db_index = (user_id % 32) / 8
-- table_index = user_id % 8

-- user_0表结构（所有表相同）
CREATE TABLE user_0 (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username)
);

五、MySQL监控与诊断

5.1 性能监控指标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 查看QPS（每秒查询数）
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW GLOBAL STATUS LIKE 'Uptime';
-- QPS = Queries / Uptime

-- 查看TPS（每秒事务数）
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';
-- TPS = (Com_commit + Com_rollback) / Uptime

-- 查看连接数
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_running';

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

5.2 慢查询分析

1
2
3
4
5
6
7
8
9
10
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';

-- 查看慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- 使用mysqldumpslow分析
-- mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

5.3 性能诊断工具

pt-query-digest：

1
2
3
4
5
# 分析慢查询日志
pt-query-digest /var/lib/mysql/slow.log

# 分析binlog
pt-query-digest --type=binlog mysql-bin.000001

pt-kill：

1
2
3
# 杀死执行超过60秒的查询
pt-kill --host=localhost --user=root --password=xxx \
    --match-command=Query --busy-time=60 --kill --print

六、MySQL参数优化

6.1 InnoDB缓冲池配置

1
2
3
4
5
6
7
8
9
[mysqld]
# 缓冲池大小（推荐物理内存的50%-75%）
innodb_buffer_pool_size = 4G

# 缓冲池实例数（提高并发性能）
innodb_buffer_pool_instances = 4

# 预读策略
innodb_read_ahead_threshold = 56

6.2 日志配置

1
2
3
4
5
6
7
8
9
10
11
[mysqld]
# 重做日志大小（影响写入性能）
innodb_log_file_size = 1G
innodb_log_files_in_group = 3

# 日志刷新策略
innodb_flush_log_at_trx_commit = 2
# 0: 每秒刷盘，1: 每次提交刷盘，2: 每次提交写OS缓存

# 脏页刷新策略
innodb_flush_method = O_DIRECT

6.3 连接配置

1
2
3
4
5
6
7
8
9
10
[mysqld]
# 最大连接数
max_connections = 1000

# 连接缓存
thread_cache_size = 100

# 等待连接超时
wait_timeout = 600
interactive_timeout = 600

七、总结

MySQL性能优化是一个系统工程，需要从多个维度进行考虑：

  1. 索引优化：合理设计索引，避免全表扫描
  2. SQL优化：编写高效的SQL语句，使用EXPLAIN分析
  3. 架构优化：采用主从复制、分库分表提升扩展性
  4. 参数优化：根据硬件配置调整MySQL参数
  5. 监控告警：建立完善的监控体系，及时发现性能问题

在实际生产环境中，建议：

  • 定期进行慢查询分析
  • 建立性能基线，对比历史数据
  • 重要变更先在测试环境验证
  • 做好数据备份和恢复演练

通过持续的优化和监控，可以确保MySQL数据库在高并发、大数据量场景下保持稳定的性能表现。