MySQL 8.0 索引优化实战：千万级数据下的性能提升

标签： mysql database optimization index performance
作者： @dba_master
发布时间： 2026-06-12

问题背景

在我们的用户行为分析系统中，单表数据量达到 3000 万+，查询性能急剧下降。本文记录优化过程，从 8 秒查询优化到 50 毫秒。

表结构

CREATE TABLE user_behavior (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    action VARCHAR(50) NOT NULL,
    page_url VARCHAR(500) NOT NULL,
    session_id VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_created (user_id, created_at),
    INDEX idx_action_created (action, created_at)
) ENGINE=InnoDB;

优化前的问题

慢查询示例

-- 查询某用户最近 30 天的行为
SELECT * FROM user_behavior 
WHERE user_id = 12345 
  AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY created_at DESC
LIMIT 100;
-- 耗时：8.2 秒

EXPLAIN 分析

EXPLAIN SELECT * FROM user_behavior 
WHERE user_id = 12345 
  AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY created_at DESC
LIMIT 100;

结果：

type: ALL（全表扫描！）
rows: 30,000,000
Extra: Using where; Using filesort

优化过程

第一步：分析索引使用情况

-- 查看索引统计
SHOW INDEX FROM user_behavior;

-- 查看索引选择性
SELECT 
    COUNT(DISTINCT user_id) / COUNT(*) as user_selectivity,
    COUNT(DISTINCT action) / COUNT(*) as action_selectivity,
    COUNT(DISTINCT DATE(created_at)) / COUNT(*) as date_selectivity
FROM user_behavior;

第二步：优化索引设计

问题分析：

单独的 user_id 索引不够，需要覆盖查询条件
ORDER BY created_at 导致 filesort
SELECT * 导致回表查询

优化方案：

-- 1. 创建覆盖索引（Covering Index）
ALTER TABLE user_behavior 
DROP INDEX idx_user_created,
ADD INDEX idx_user_created_page (user_id, created_at, page_url, action);

-- 2. 或者使用索引下推（Index Condition Pushdown）
ALTER TABLE user_behavior 
ADD INDEX idx_user_created_action (user_id, created_at, action);

第三步：优化查询语句

-- 优化前：SELECT * 导致回表
SELECT * FROM user_behavior 
WHERE user_id = 12345 
  AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY created_at DESC
LIMIT 100;

-- 优化后：只查询需要的字段
SELECT id, user_id, action, page_url, created_at 
FROM user_behavior 
WHERE user_id = 12345 
  AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY created_at DESC
LIMIT 100;
-- 耗时：50ms

第四步：EXPLAIN 验证

EXPLAIN SELECT id, user_id, action, page_url, created_at 
FROM user_behavior 
WHERE user_id = 12345 
  AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY created_at DESC
LIMIT 100;

优化后结果：

type: ref
key: idx_user_created_action
rows: 1,250
Extra: Using where; Using index（覆盖索引！）

进阶优化：分区表

对于时间序列数据，可以考虑分区：

-- 按月份分区
CREATE TABLE user_behavior_partitioned (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    action VARCHAR(50) NOT NULL,
    page_url VARCHAR(500) NOT NULL,
    session_id VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at),
    INDEX idx_user_created (user_id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
    PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

性能对比

优化阶段	查询时间	扫描行数	备注
优化前	8.2s	30,000,000	全表扫描
添加索引	1.5s	1,250	避免全表扫描
覆盖索引	50ms	1,250	无需回表
分区表	15ms	300	仅扫描当月分区

索引设计原则

1. 最左前缀原则

-- 索引 (user_id, created_at, action)
WHERE user_id = 1;                    -- ✅ 使用索引
WHERE user_id = 1 AND created_at = '2024-01-01';  -- ✅ 使用索引
WHERE created_at = '2024-01-01';      -- ❌ 不使用索引（缺少最左列）
WHERE user_id = 1 AND action = 'click';  -- ✅ 使用索引（跳列）

2. 覆盖索引

-- 索引包含查询所需的所有字段
SELECT user_id, created_at, action FROM user_behavior
WHERE user_id = 1;
-- 如果索引是 (user_id, created_at, action)，则无需回表

3. 避免冗余索引

-- 已有索引 (user_id, created_at)
-- 不需要单独的 (user_id) 索引
-- 但可能需要 (created_at) 索引用于时间范围查询

监控与维护

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 启用 Performance Schema
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%stage/sql%';

-- 查看索引使用统计
SELECT 
    OBJECT_SCHEMA, 
    OBJECT_NAME, 
    INDEX_NAME, 
    COUNT_FETCH, 
    COUNT_INSERT, 
    COUNT_UPDATE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database';