mysql index索引功能有什么用，怎么用

doggie · 2026年01月06日07:38

本文由简悦 SimpRead 转码，原文地址 www.cnblogs.com

一、索引是什么？为什么这么重要？

索引就像字典的目录

想象一下，你要在一本 1000 页的字典里找 “程序员” 这个词，你会怎么做？

没有目录：从第 1 页开始一页一页翻，可能要翻 500 页才能找到
有目录：直接翻到目录，找到 “程” 字开头的词在第 300 页，瞬间就找到了

数据库索引就是这样的 “目录”，它能帮我们快速定位数据的位置。

索引的神奇效果

场景	无索引	有索引	性能提升
100 万条数据查询	扫描 100 万行	扫描 3-4 行	提升 25 万倍 +
用户登录验证	50ms	1ms	提升 50 倍
订单查询	200ms	5ms	提升 40 倍

真实的例子

-- 没有索引的查询（慢得要命）
SELECT * FROM users WHERE email = 'john@example.com';
-- 执行时间：1.2秒（扫描了50万行数据）

-- 给email字段添加索引后
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'john@example.com';
-- 执行时间：0.01秒（直接定位到1行数据）

看到了吗？同样的查询，性能差了 120 倍！

二、索引的底层原理：B + 树的魔法

什么是 B + 树？

不要被这个名字吓到，B + 树其实很好理解。想象一下一个倒置的大树：

B + 树的查找过程

让我们用一个简单例子来理解：

-- 假设我们要查找 id = 75 的用户
SELECT * FROM users WHERE id = 75;

查找步骤：

第 1 步：从根节点开始，75 在 50-100 之间，走中间分支
第 2 步：到达叶子节点，找到 id=75 的数据位置
第 3 步：根据位置直接获取完整的用户数据

整个过程只需要 3 次磁盘 IO，而全表扫描可能需要几万次！

为什么 B + 树这么快？

特点	优势	实际效果
多路平衡	树的高度很低	减少磁盘访问次数
叶子节点连接	支持范围查询	ORDER BY、分页查询快
只在叶子存数据	内部节点小	更多索引数据放入内存

三、MySQL 索引的类型详解

1. 主键索引（Primary Key）

主键索引是最特殊的索引，它就像身份证号码一样：

-- 创建主键索引
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 自动创建主键索引
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 主键查询超级快
SELECT * FROM users WHERE id = 12345;  -- 毫秒级响应

主键索引的特点：

唯一且不能为空
一个表只能有一个主键
查询性能最好
数据按主键顺序存储

2. 唯一索引（Unique Index）

-- 给邮箱添加唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 插入重复邮箱会报错
INSERT INTO users(name, email) VALUES('张三', 'test@qq.com');  -- 成功
INSERT INTO users(name, email) VALUES('李四', 'test@qq.com');  -- 失败，邮箱重复

3. 普通索引（Normal Index）

最常用的索引类型：

-- 给姓名添加普通索引
CREATE INDEX idx_name ON users(name);

-- 快速查找用户
SELECT * FROM users WHERE name = '张三';

4. 复合索引（Composite Index）

多个字段组合的索引，功能更强大：

-- 创建复合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 这些查询都能用到索引
SELECT * FROM users WHERE name = '张三';                          -- ✓ 能用到
SELECT * FROM users WHERE name = '张三' AND age = 25;             -- ✓ 能用到  
SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';  -- ✓ 能用到
SELECT * FROM users WHERE age = 25;                               -- ✗ 用不到
SELECT * FROM users WHERE city = '北京';                          -- ✗ 用不到

复合索引的使用规则（最左前缀原则）：

-- 索引：(name, age, city)
-- 可以理解为创建了三个索引：
-- 1. (name)
-- 2. (name, age) 
-- 3. (name, age, city)

四、索引设计的黄金法则

法则 1：为 WHERE 条件添加索引

-- 经常这样查询
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE status = 'paid';
SELECT * FROM orders WHERE create_time > '2024-01-01';

-- 就应该创建这些索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_create_time ON orders(create_time);

法则 2：为 ORDER BY 字段添加索引

-- 经常按创建时间排序
SELECT * FROM articles ORDER BY create_time DESC LIMIT 10;

-- 创建索引让排序飞快
CREATE INDEX idx_create_time ON articles(create_time);

法则 3：复合索引的顺序很关键

-- 如果经常这样查询
SELECT * FROM users WHERE city = '北京' AND age > 25 ORDER BY create_time;

-- 索引字段顺序应该是：过滤性强的字段在前
CREATE INDEX idx_city_age_create_time ON users(city, age, create_time);

法则 4：覆盖索引让查询更快

-- 如果只需要这几个字段
SELECT id, name, email FROM users WHERE age = 25;

-- 创建覆盖索引，连回表都省了
CREATE INDEX idx_age_name_email ON users(age, name, email);

五、实战案例：订单系统优化

场景描述

假设我们有一个订单表：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_no VARCHAR(50) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled'),
    total_amount DECIMAL(10,2),
    create_time DATETIME,
    update_time DATETIME
);

常见查询场景及优化

场景 1：用户查看自己的订单

-- 原始查询（慢）
SELECT * FROM orders WHERE user_id = 12345 ORDER BY create_time DESC;

-- 优化方案
CREATE INDEX idx_user_id_create_time ON orders(user_id, create_time);

优化效果：

优化前：扫描 50 万行数据，耗时 800ms
优化后：直接定位用户订单，耗时 5ms

场景 2：订单状态查询

-- 查询待支付订单
SELECT * FROM orders WHERE status = 'pending' AND create_time > '2024-01-01';

-- 优化方案
CREATE INDEX idx_status_create_time ON orders(status, create_time);

场景 3：订单号精确查找

-- 通过订单号查找
SELECT * FROM orders WHERE order_no = 'ORD20240101001';

-- 优化方案
CREATE UNIQUE INDEX idx_order_no ON orders(order_no);

优化前后对比

查询类型	优化前耗时	优化后耗时	提升倍数
用户订单查询	800ms	5ms	160 倍
状态筛选	1200ms	8ms	150 倍
订单号查找	600ms	2ms	300 倍

六、索引的注意事项：别踩这些坑

坑 1：不要给小表建索引

-- 错误示例：给只有100行数据的字典表建索引
CREATE TABLE dict_status (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);
-- 这个表数据量太小，建索引反而浪费空间

坑 2：不要在区分度低的字段建索引

-- 错误示例：性别字段只有男/女两个值
CREATE INDEX idx_gender ON users(gender);  -- 没意义，区分度太低

坑 3：索引不是越多越好

-- 错误示例：给每个字段都建索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_city ON users(city);
CREATE INDEX idx_phone ON users(phone);
CREATE INDEX idx_email ON users(email);
-- 太多索引会严重影响INSERT/UPDATE性能

坑 4：复合索引的字段顺序

-- 错误示例
CREATE INDEX idx_age_name ON users(age, name);
SELECT * FROM users WHERE name = '张三';  -- 用不到索引

-- 正确示例
CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users WHERE name = '张三';  -- 能用到索引

七、索引优化实战技巧

技巧 1：使用 EXPLAIN 分析查询

-- 分析查询是否使用了索引
EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

EXPLAIN 结果解读：

字段	说明	好的值	坏的值
type	访问类型	const, eq_ref, ref	ALL, index
key	使用的索引	有具体索引名	NULL
rows	扫描行数	越少越好	很大的数字
Extra	额外信息	Using index	Using filesort

技巧 2：监控慢查询

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录下来

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log_file';

技巧 3：定期分析表统计信息

-- 更新表的统计信息，让优化器做出更好的选择
ANALYZE TABLE orders;

技巧 4：合理使用前缀索引

-- 对于很长的字符串字段，使用前缀索引
CREATE INDEX idx_title_prefix ON articles(title(20));  -- 只索引前20个字符

八、高级索引特性

1. 函数索引（MySQL 8.0+）

-- 给计算字段创建索引
ALTER TABLE orders ADD INDEX idx_year ((YEAR(create_time)));

-- 这个查询能用到索引
SELECT * FROM orders WHERE YEAR(create_time) = 2024;

2. 降序索引（MySQL 8.0+）

-- 创建降序索引
CREATE INDEX idx_create_time_desc ON orders(create_time DESC);

-- 降序排序更快
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10;

3. 不可见索引

-- 创建不可见索引（用于测试）
CREATE INDEX idx_test ON orders(status) INVISIBLE;

-- 测试性能后再设为可见
ALTER INDEX idx_test VISIBLE;

九、索引维护：让索引保持最佳状态

定期检查索引使用情况

-- 查看索引使用统计
SELECT 
    schema_name,
    table_name,
    index_name,
    rows_selected,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE schema_name = 'your_database';

删除无用索引

-- 找出从未使用的索引
SELECT 
    t.table_schema,
    t.table_name,
    t.index_name
FROM information_schema.statistics t
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage p
    ON t.table_schema = p.object_schema
    AND t.table_name = p.object_name
    AND t.index_name = p.index_name
WHERE p.index_name IS NULL
    AND t.table_schema = 'your_database'
    AND t.index_name != 'PRIMARY';

重建碎片化的索引

-- 检查索引碎片化程度
SHOW TABLE STATUS WHERE name = 'orders';

-- 重建索引
ALTER TABLE orders ENGINE=InnoDB;

十、实际项目中的索引策略

电商系统索引设计

-- 商品表
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    category_id INT,
    name VARCHAR(200),
    price DECIMAL(10,2),
    stock INT,
    status TINYINT,
    create_time DATETIME,
  
    -- 核心索引
    INDEX idx_category_status_price (category_id, status, price),
    INDEX idx_name (name),
    INDEX idx_create_time (create_time)
);

-- 订单表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status TINYINT,
    total_amount DECIMAL(10,2),
    create_time DATETIME,
  
    -- 核心索引
    INDEX idx_user_id_create_time (user_id, create_time),
    INDEX idx_status_create_time (status, create_time)
);

社交系统索引设计

-- 用户关注表
CREATE TABLE user_follows (
    id BIGINT PRIMARY KEY,
    follower_id BIGINT,    -- 关注者
    following_id BIGINT,   -- 被关注者
    create_time DATETIME,
  
    -- 核心索引
    INDEX idx_follower_id (follower_id),        -- 查询我关注的人
    INDEX idx_following_id (following_id),      -- 查询关注我的人
    UNIQUE KEY uk_follow (follower_id, following_id)  -- 防止重复关注
);

十一、性能测试与优化案例

案例 1：用户登录优化

场景： 用户登录验证

-- 优化前的查询
SELECT id, password_hash FROM users WHERE email = 'user@example.com';

-- 性能测试结果
-- 数据量：100万用户
-- 查询时间：平均 850ms
-- 扫描行数：平均 50万行

优化方案：

-- 1. 创建邮箱唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 2. 创建覆盖索引（避免回表）
CREATE INDEX idx_email_password ON users(email, password_hash);