MySQL性能优化实战：我是如何把查询速度提升10倍的

# MySQL性能优化实战：我是如何把查询速度提升10倍的

## 引言
最近在做一个数据分析项目，单表数据量达到了500万条，一个统计查询竟然需要15秒才能返回结果，用户体验极差。经过一周的优化，最终把查询速度降到了1.2秒，提升了10倍还多。今天把整个优化过程分享给大家，都是实战中总结出来的干货，看完就能用到自己的项目里。

## 优化前的情况
### 业务场景
我们有一个订单统计表，结构如下：
```sql
CREATE TABLE `order_stat` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL COMMENT '用户ID',
  `order_id` varchar(64) NOT NULL COMMENT '订单号',
  `amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `status` tinyint NOT NULL COMMENT '订单状态：1=待支付，2=已支付，3=已取消',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
数据量：500万条，每天新增约5万条。

### 慢查询SQL
需要统计某个用户在某个时间段的订单总金额和订单数量：
```sql
SELECT 
  COUNT(*) as order_count,
  SUM(amount) as total_amount
FROM order_stat
WHERE user_id = 12345 
  AND create_time BETWEEN '2026-01-01 00:00:00' AND '2026-02-01 00:00:00'
  AND status = 2;
```
执行时间：15.3秒，完全无法接受。

## 优化步骤
### 第一步：加索引（最基础也最有效）
首先想到的肯定是加索引，但是怎么加是有讲究的。很多人会给每个条件都单独加索引，但实际上联合索引效果更好。

#### 错误的做法：
```sql
-- 单独加三个索引，效果很差
ALTER TABLE order_stat ADD INDEX idx_user_id(user_id);
ALTER TABLE order_stat ADD INDEX idx_create_time(create_time);
ALTER TABLE order_stat ADD INDEX idx_status(status);
```
这种情况下，MySQL一般只会用到`idx_user_id`索引，然后回表筛选其他条件，速度提升有限。

#### 正确的做法：联合索引
根据最左匹配原则，我们创建联合索引：
```sql
-- 联合索引：user_id + status + create_time
ALTER TABLE order_stat ADD INDEX idx_user_status_time(user_id, status, create_time);
```
**为什么这么建？**
1. 第一个字段是`user_id`：过滤性最高，先快速定位到某个用户的所有订单
2. 第二个字段是`status`：过滤掉非支付状态的订单
3. 第三个字段是`create_time`：最后按时间范围过滤

建完索引后，执行时间从15.3秒降到了3.7秒，提升了4倍，但是还不够快。

### 第二步：覆盖索引，避免回表
刚才的查询虽然用到了联合索引，但是还需要回表查询`amount`字段，因为索引里没有这个字段。如果我们把`amount`也加到索引里，就可以实现覆盖索引，不需要回表了。

```sql
-- 创建覆盖索引，包含需要查询的所有字段
ALTER TABLE order_stat ADD INDEX idx_user_status_time_amount(user_id, status, create_time, amount);

-- 删除旧的联合索引
ALTER TABLE order_stat DROP INDEX idx_user_status_time;
```
现在这个索引包含了查询需要的所有字段：`user_id`、`status`、`create_time`、`amount`，查询的时候直接从索引里就能拿到所有数据，不需要回表。

优化后执行时间：1.8秒，又快了一倍。

### 第三步：优化SQL写法
很多人写SQL的时候不注意细节，一些小的改动就能带来很大的性能提升。我们的SQL其实还有优化空间。

#### 优化前：
```sql
SELECT 
  COUNT(*) as order_count,
  SUM(amount) as total_amount
FROM order_stat
WHERE user_id = 12345 
  AND create_time BETWEEN '2026-01-01 00:00:00' AND '2026-02-01 00:00:00'
  AND status = 2;
```

#### 优化后：
```sql
SELECT 
  COUNT(id) as order_count,  -- 用COUNT(id)代替COUNT(*)，InnoDB下更快
  SUM(amount) as total_amount
FROM order_stat
WHERE user_id = 12345 
  AND status = 2  -- 把等值条件放在前面，范围条件放在最后
  AND create_time >= '2026-01-01' 
  AND create_time < '2026-02-01';  -- 用>=和<代替BETWEEN，避免边界问题
```
**优化点说明：**
1. `COUNT(id)`比`COUNT(*)`快：因为`id`是主键，InnoDB直接统计主键数量，不需要扫描其他字段
2. 条件顺序调整：等值条件在前，范围条件在后，更符合索引的匹配规则
3. 用`>=`和`<`代替`BETWEEN`：避免BETWEEN包含两端边界的问题，尤其是时间字段

优化后执行时间：1.2秒，又提升了30%。

### 第四步：分库分表（终极方案）
如果数据量继续增长到千万甚至亿级，索引优化也会遇到瓶颈，这时候就需要分库分表了。我们的方案是按`user_id`哈希分表，分成16张表：`order_stat_0`到`order_stat_15`。

#### 分表规则：
```python
# 按用户ID哈希取模，决定数据存在哪张表
table_index = user_id % 16
table_name = f"order_stat_{table_index}"
```

分表后，单表数据量降到了30万左右，相同的查询只需要0.3秒，提升了4倍。不过分表会增加代码复杂度，不到万不得已不建议用。

## 其他优化技巧
### 1. 开启慢查询日志
```ini
# my.cnf配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  # 超过2秒的查询都记录下来
```
定期分析慢查询日志，找到需要优化的SQL。

### 2. 调整InnoDB缓存配置
```ini
# 建议设置为物理内存的50%-70%
innodb_buffer_pool_size = 4G
# 日志文件大小，建议256M-1G
innodb_log_file_size = 256M
# 刷新日志的时机，性能优先设为2，数据安全优先设为1
innodb_flush_log_at_trx_commit = 1
```

### 3. 避免SELECT *
只查询需要的字段，不需要的字段不要查，减少数据传输和内存占用。

### 4. 批量操作代替循环操作
```sql
-- 好的做法：批量插入
INSERT INTO table (col1, col2) VALUES (val1, val2), (val3, val4), ...;

-- 不好的做法：循环单条插入
INSERT INTO table (col1, col2) VALUES (val1, val2);
INSERT INTO table (col1, col2) VALUES (val3, val4);
```

### 5. 避免在WHERE条件里对字段做运算
```sql
-- 好的做法：字段 = 值
SELECT * FROM table WHERE create_time >= '2026-01-01';

-- 不好的做法：对字段使用函数，会导致索引失效
SELECT * FROM table WHERE DATE(create_time) = '2026-01-01';
```

## 优化效果总结
| 优化阶段 | 执行时间 | 提升倍数 |
|---------|---------|---------|
| 优化前 | 15.3秒 | 1x |
| 加联合索引 | 3.7秒 | 4x |
| 加覆盖索引 | 1.8秒 | 8.5x |
| 优化SQL写法 | 1.2秒 | 12.75x |
| 分库分表（可选） | 0.3秒 | 51x |

## 常见误区
1. **索引越多越好**：索引会加快查询速度，但会减慢写入速度，一张表的索引建议不超过5个
2. **联合索引顺序无所谓**：联合索引要遵循最左匹配原则，过滤性高的字段放前面
3. **COUNT(*)一定慢**：InnoDB下COUNT(*)和COUNT(1)差不多，COUNT(主键)最快
4. **分库分表是银弹**：分表会带来很多复杂度，数据量没到千万级别用

## 总结
MySQL优化其实没有那么难，大多数慢查询都可以通过加合适的索引解决，少数需要优化SQL写法，只有极端情况才需要分库分表。记住优化的顺序：**先优化索引，再优化SQL，最后考虑架构调整**。

如果你的项目里也有慢查询问题，按照这个步骤去优化，大概率能得到不错的提升。有任何问题欢迎在评论区留言，我会一一解答。

AI摘要

文章摘要：本文讲述了作者如何将一个涉及500万条记录的订单统计表查询速度提升到1.2秒，从最初的15.3秒大幅优化。文章首先介绍了原始的慢查询SQL及其在InnoDB存储引擎中的执行时间，然后详细阐述了通过加联合索引和创建覆盖索引来减少回表操作，以及通过优化SQL写法来提高查询效率。最后，作者总结了优化过程中的一些常见误区，并强调了优化MySQL性能时应遵循的顺序：先优化索引，再优化SQL，最后考虑架构调整。

正在总结中…

# MySQL性能优化实战：我是如何把查询速度提升10倍的

## 优化前的情况
### 业务场景
我们有一个订单统计表，结构如下：
```sql
CREATE TABLE `order_stat` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned NOT NULL COMMENT '用户ID',
`order_id` varchar(64) NOT NULL COMMENT '订单号',
`amount` decimal(10,2) NOT NULL COMMENT '订单金额',
`status` tinyint NOT NULL COMMENT '订单状态：1=待支付，2=已支付，3=已取消',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
数据量：500万条，每天新增约5万条。

### 慢查询SQL
需要统计某个用户在某个时间段的订单总金额和订单数量：
```sql
SELECT
COUNT(*) as order_count,
SUM(amount) as total_amount
FROM order_stat
WHERE user_id = 12345
AND create_time BETWEEN '2026-01-01 00:00:00' AND '2026-02-01 00:00:00'
AND status = 2;
```
执行时间：15.3秒，完全无法接受。

建完索引后，执行时间从15.3秒降到了3.7秒，提升了4倍，但是还不够快。

```sql
-- 创建覆盖索引，包含需要查询的所有字段
ALTER TABLE order_stat ADD INDEX idx_user_status_time_amount(user_id, status, create_time, amount);

优化后执行时间：1.8秒，又快了一倍。

### 第三步：优化SQL写法
很多人写SQL的时候不注意细节，一些小的改动就能带来很大的性能提升。我们的SQL其实还有优化空间。

#### 优化前：
```sql
SELECT
COUNT(*) as order_count,
SUM(amount) as total_amount
FROM order_stat
WHERE user_id = 12345
AND create_time BETWEEN '2026-01-01 00:00:00' AND '2026-02-01 00:00:00'
AND status = 2;
```

#### 优化后：
```sql
SELECT
COUNT(id) as order_count, -- 用COUNT(id)代替COUNT(*)，InnoDB下更快
SUM(amount) as total_amount
FROM order_stat
WHERE user_id = 12345
AND status = 2 -- 把等值条件放在前面，范围条件放在最后
AND create_time >= '2026-01-01'
AND create_time < '2026-02-01'; -- 用>=和<代替BETWEEN，避免边界问题
```
**优化点说明：**
1. `COUNT(id)`比`COUNT(*)`快：因为`id`是主键，InnoDB直接统计主键数量，不需要扫描其他字段
2. 条件顺序调整：等值条件在前，范围条件在后，更符合索引的匹配规则
3. 用`>=`和`<`代替`BETWEEN`：避免BETWEEN包含两端边界的问题，尤其是时间字段

优化后执行时间：1.2秒，又提升了30%。

#### 分表规则：
```python
# 按用户ID哈希取模，决定数据存在哪张表
table_index = user_id % 16
table_name = f"order_stat_{table_index}"
```

分表后，单表数据量降到了30万左右，相同的查询只需要0.3秒，提升了4倍。不过分表会增加代码复杂度，不到万不得已不建议用。

## 其他优化技巧
### 1. 开启慢查询日志
```ini
# my.cnf配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 # 超过2秒的查询都记录下来
```
定期分析慢查询日志，找到需要优化的SQL。