本文将深入分享我在MySQL数据库优化方面的实战经验，包括索引设计原则、EXPLAIN执行计划解读、慢查询优化、分库分表策略等。每个优化技巧都配有真实的SQL示例和性能对比数据。

1. 索引设计：从盲目添加到了解原理

初期错误的索引使用：

-- 错误示范：盲目添加索引
ALTER TABLE `order` ADD INDEX idx_user_id (`user_id`);
ALTER TABLE `order` ADD INDEX idx_status (`status`);
ALTER TABLE `order` ADD INDEX idx_create_time (`create_time`);

-- 实际查询语句
SELECT * FROM `order` 
WHERE user_id = 123 
  AND status = 'PAID' 
  AND create_time BETWEEN '2019-01-01' AND '2019-01-31';

​问题分析：​​ 多个单列索引，MySQL只能选择一个最优的，其他条件需要回表查询。

正确的复合索引设计：

-- 删除多余的单列索引，创建复合索引
ALTER TABLE `order` DROP INDEX idx_user_id, DROP INDEX idx_status, DROP INDEX idx_create_time;

-- 创建复合索引，字段顺序很重要！
ALTER TABLE `order` ADD INDEX idx_user_status_time (`user_id`, `status`, `create_time`);

复合索引设计原则：

  1. ​最左前缀原则​：索引从左到右匹配
  2. ​等值查询在前​：=、IN等条件放前面
  3. ​范围查询在后​：BETWEEN、>、<等放后面
  4. ​区分度高的在前​：选择性高的列放前面

2. EXPLAIN执行计划深度解读

实战分析：

EXPLAIN SELECT o.*, u.username 
FROM `order` o 
LEFT JOIN `user` u ON o.user_id = u.id
WHERE o.status = 'PAID' 
  AND o.amount > 100 
  AND o.create_time >= '2019-01-01'
ORDER BY o.create_time DESC 
LIMIT 20;

EXPLAIN结果解读：

+----+-------------+-------+------------+------+---------------+---------+---------+-------+--------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+--------+----------+---------------------------------------+
|  1 | SIMPLE      | o     | NULL       | ref  | idx_status    | idx_status | 1023    | const | 15680  |    33.33 | Using index condition; Using filesort |
|  1 | SIMPLE      | u     | NULL       | eq_ref| PRIMARY       | PRIMARY | 4       | o.user_id | 1     |   100.00 | NULL                                  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+--------+----------+---------------------------------------+

关键字段分析：

  • ​type​: ref（普通索引扫描）vs eq_ref（主键/唯一索引）
  • ​key​: 实际使用的索引
  • ​rows​: 预估扫描行数

  • ​Extra​:

    • Using filesort：需要额外排序（性能差）
    • Using temporary：使用临时表
    • Using index：覆盖索引，性能最佳

3. 慢查询优化实战案例

案例1：分页查询优化

优化前（深度分页性能差）：

SELECT * FROM `order` 
WHERE status = 'COMPLETED' 
ORDER BY id DESC 
LIMIT 10000, 20; -- 扫描10020行，返回20行

优化方案1：游标分页

-- 第一页
SELECT * FROM `order` 
WHERE status = 'COMPLETED' 
ORDER BY id DESC 
LIMIT 20;

-- 后续页面，记录上一页最后一条的ID
SELECT * FROM `order` 
WHERE status = 'COMPLETED' 
  AND id < ?last_id  -- 传入上一页最后一条的ID
ORDER BY id DESC 
LIMIT 20;

优化方案2：覆盖索引+延迟关联

-- 先通过覆盖索引获取主键
SELECT id FROM `order` 
WHERE status = 'COMPLETED' 
ORDER BY id DESC 
LIMIT 10000, 20;

-- 再通过主键关联查询详细数据
SELECT o.* FROM `order` o 
INNER JOIN (
    SELECT id FROM `order` 
    WHERE status = 'COMPLETED' 
    ORDER BY id DESC 
    LIMIT 10000, 20
) tmp ON o.id = tmp.id;

案例2：大数据量统计优化

优化前（全表扫描）：

SELECT COUNT(*) FROM `order` 
WHERE create_time BETWEEN '2019-01-01' AND '2019-01-31';

优化方案：近似统计+缓存

-- 1. 使用EXPLAIN获取近似行数（快速）
EXPLAIN SELECT COUNT(*) FROM `order` 
WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31';

-- 2. 精确统计但添加缓存
@Cacheable(value = "orderStats", key = "#startDate + '-' + #endDate")
public long getOrderCount(Date startDate, Date endDate) {
    return orderMapper.countByDateRange(startDate, endDate);
}

-- 3. 分时段统计，避免大范围扫描
SELECT COUNT(*) FROM `order` 
WHERE create_time >= '2019-01-01' AND create_time < '2019-01-02'
UNION ALL
SELECT COUNT(*) FROM `order` 
WHERE create_time >= '2019-01-02' AND create_time < '2019-01-03';
-- ... 以此类推

4. 连接查询优化

N+1查询问题：

// 错误示范：循环中查询数据库
List<Order> orders = orderMapper.selectByStatus("PAID");
for (Order order : orders) {
    // 每次循环都查询数据库
    User user = userMapper.selectById(order.getUserId());
    order.setUser(user);
}

优化方案1：JOIN查询

SELECT o.*, u.username, u.email 
FROM `order` o 
LEFT JOIN `user` u ON o.user_id = u.id
WHERE o.status = 'PAID';

优化方案2：MyBatis结果映射

<resultMap id="OrderWithUserMap" type="Order" autoMapping="true">
    <id property="id" column="id"/>
    <association property="user" javaType="User" autoMapping="true">
        <id property="id" column="user_id"/>
        <result property="username" column="username"/>
        <result property="email" column="email"/>
    </association>
</resultMap>

<select id="selectOrdersWithUser" resultMap="OrderWithUserMap">
    SELECT o.*, u.username, u.email
    FROM `order` o 
    LEFT JOIN `user` u ON o.user_id = u.id
    WHERE o.status = 'PAID'
</select>

5. 数据库设计优化

反范式设计提升查询性能：

-- 订单表增加冗余字段，避免频繁JOIN
ALTER TABLE `order` 
ADD COLUMN `user_name` VARCHAR(100) COMMENT '用户名（冗余字段）',
ADD COLUMN `product_name` VARCHAR(200) COMMENT '商品名（冗余字段）';

-- 更新时同步冗余字段
UPDATE `order` o 
LEFT JOIN `user` u ON o.user_id = u.id
SET o.user_name = u.username
WHERE o.user_name IS NULL;

垂直分表：
-- 将大字段分离到单独表
CREATE TABLE order_detail (
id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
remark TEXT COMMENT '订单备注',
invoice_info JSON COMMENT '发票信息',
extended_data JSON COMMENT '扩展数据',
INDEX idx_order_id (order_id)
);

-- 主表只保留常用字段
CREATE TABLE order (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
status VARCHAR(20),
amount DECIMAL(10,2),
create_time DATETIME
);

6. 读写分离和分库分表

动态数据源配置：

@Configuration
public class DataSourceConfig {
    
    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DruidDataSourceBuilder.create().build();
    }
    
    @Bean
    @ConfigurationProperties("spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DruidDataSourceBuilder.create().build();
    }
    
    @Bean
    public DataSource routingDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource());
        targetDataSources.put("slave", slaveDataSource());
        
        RoutingDataSource routingDataSource = new RoutingDataSource();
        routingDataSource.setDefaultTargetDataSource(masterDataSource());
        routingDataSource.setTargetDataSources(targetDataSources);
        return routingDataSource;
    }
}

// 自定义路由逻辑
public class RoutingDataSource extends AbstractRoutingDataSource {
    
    @Override
    protected Object determineCurrentLookupKey() {
        // 读操作路由到从库，写操作路由到主库
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly() ? "slave" : "master";
    }
}

// 使用注解控制读写分离
@Service
public class OrderService {
    
    @Transactional(readOnly = true)  // 读操作，路由到从库
    public Order getOrderById(Long id) {
        return orderMapper.selectById(id);
    }
    
    @Transactional  // 写操作，路由到主库
    public void updateOrder(Order order) {
        orderMapper.update(order);
    }
}

7. 性能监控和调优

慢查询日志分析：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;  -- 2秒以上视为慢查询
SET GLOBAL log_queries_not_using_indexes = 1;

-- 分析慢查询日志
pt-query-digest /var/lib/mysql/slow.log

-- 实时查看正在执行的慢查询
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' AND TIME > 10;

索引使用情况监控：

-- 查看索引使用情况
SELECT OBJECT_NAME, INDEX_NAME, COUNT_READ, COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database';

-- 查找冗余索引
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

8. 最佳实践总结

  1. ​索引不是越多越好​，维护索引有成本
  2. ​理解B+树原理​，才能设计好索引
  3. ​避免SELECT​*，只查询需要的字段
  4. ​大数据量考虑分页优化​，避免深度分页
  5. 读写分离缓解主库压力
  6. ​定期分析慢查询​，持续优化
  7. ​监控数据库性能​，预防问题发生

总结：

数据库优化是一个持续的过程，需要结合业务特点和数据增长趋势。两年的经验让我明白，​优化不是简单的添加索引，而是要理解数据访问模式、业务场景和数据库原理的综合平衡。​​ 从EXPLAIN执行计划到分库分表策略，每个优化决策都应该有数据支撑和业务价值。

© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏