MySQL 中 6 种索引访问类型的完整方案

卡布奇诺海晨 2025-06-16

以下是建表、创建索引及模拟不同访问类型的详细操作步骤：

-- 创建测试表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,   -- 主键列
    username VARCHAR(50) NOT NULL,      -- 用户名
    email VARCHAR(100) NOT NULL,         -- 邮箱
    age INT NOT NULL,                    -- 年龄
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP  -- 创建时间
);
-- 插入测试数据
INSERT INTO users (username, email, age)
VALUES 
    ('john_doe', 'john@example.com', 28),
    ('jane_smith', 'jane@test.com', 32),
    ('bob_johnson', 'bob@mail.com', 45),
    ('alice_wonder', 'alice@domain.com', 23),
    ('john_doe', 'john2@site.com', 31);  -- 故意插入重复用户名
-- 添加额外索引
ALTER TABLE users ADD INDEX idx_username (username);        -- 普通索引
ALTER TABLE users ADD INDEX idx_age (age);                  -- 普通索引
ALTER TABLE users ADD UNIQUE INDEX uniq_email (email);      -- 唯一索引

模拟 6 种访问类型场景

1. ALL - 全表扫描

-- 场景：无索引条件查询
EXPLAIN SELECT * FROM users WHERE created_at > '2023-01-01';

结果特征：

type: ALL

key: NULL

rows: 5（全表行数）

2. INDEX - 全索引扫描

-- 场景：仅查询索引列（覆盖索引）
EXPLAIN SELECT username FROM users;

结果特征：

type: index

key: idx_username

Extra: Using index

3. RANGE - 索引范围扫描

-- 场景：使用范围查询
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;

结果特征：

type: range

key: idx_age

Extra: Using index condition

4. REF - 非唯一索引查找

-- 场景：使用普通索引等值查询（username 有重复值）
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

结果特征：

type: ref

key: idx_username

rows: 2（返回匹配行数）

5. EQ_REF - 唯一索引查找

-- 创建关联表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_email VARCHAR(100),
    amount DECIMAL(10,2)
);
-- 添加外键关联
ALTER TABLE orders ADD CONSTRAINT fk_user_email 
    FOREIGN KEY (user_email) REFERENCES users(email);
-- 模拟场景：多表关联（唯一索引）
EXPLAIN SELECT * 
FROM users
JOIN orders ON users.email = orders.user_email;

结果特征：

type: eq_ref

key: uniq_email

Extra: Using index

6. CONST - 主键/唯一索引单行查找

-- 场景：主键等值查询
EXPLAIN SELECT * FROM users WHERE id = 3;

结果特征：

type: const

key: PRIMARY

rows: 1

验证结果对照表

优化建议：

避免 ALL 扫描：为核心查询字段创建索引

利用覆盖索引：减少 SELECT *，只查询必要字段

范围查询优化：对范围列单独建索引，避免组合索引失效

EQ_REF 场景：确保关联字段有唯一索引

CONST 优先：高频查询尽量通过主键/唯一键访问

通过 EXPLAIN 分析后，可针对性优化索引策略：

-- 查看执行计划详情
EXPLAIN FORMAT=JSON SELECT ... 
-- 检查索引使用情况
SELECT * FROM sys.schema_index_statistics

mysql mysql执行计划 mysql创建索引索引 sql优化

文章转载自卡布奇诺海晨，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL 中 6 种索引访问类型的完整方案

评论