MySQL中索引的常见问题与性能优化详解_Mysql

什么是索引

想象一下，你要在100万人的花名册里面找到"张三"：

没有索引：从头到尾逐页翻找，可能要找50万次
有索引：相当于有目录，直接查目录，2-3次就找到了

索引的本质：索引是一种数据结构，帮助数据库快速定位数据，避免全表扫描。

-- 创建用户表的正确方式
create table users (
    id int auto_increment primary key,
    username varchar(50) not null,
    email varchar(100) not null,
    age int,
    created_at timestamp default current_timestamp,
    index idx_username (username)  -- 为username字段创建索引
);

这里为username字段创建了普通索引，当按用户名查询时会使用这个索引，所以查询速度会提升。

-- 查看索引使用情况
explain select * from users where username = '张三';

使用explain可以查看mysql如何执行查询，是否使用了索引。

explain结果关键字段解读：

字段	说明	理想值
type	查询类型	const, eq_ref, ref
key	实际使用的索引	显示索引名称
key_len	使用的索引长度	越长越好
rows	预估扫描行数	越少越好
extra	额外信息	using index

type字段详细说明：

const：通过主键或唯一索引查询，最多返回一行
eq_ref：联表查询时使用主键或唯一索引
ref：使用普通索引查询
range：使用索引进行范围查询
index：全索引扫描
all：全表扫描（需要优化）

索引的底层原理

mysql索引主要使用b+树结构，就像一本多层目录的书：

根节点：最顶层目录
中间节点：章节目录
叶子节点：具体的页码

为什么用b+树？

平衡：左右子树高度差不超过1，查询稳定
有序：数据按顺序存储，范围查询效率高
扇出高：每个节点可以存储很多指针，减少io次数

常见问题

问题1：索引越多越好吗

-- 错误示范：盲目创建索引
create table products (
    id int primary key,
    name varchar(100),
    category varchar(50),
    price decimal(10,2),
    status tinyint,
    -- 问题：每个索引都要维护，写操作变慢
    index idx_name (name),           -- 可能很少按name单独查询
    index idx_category (category),   -- 可能很少按category单独查询
    index idx_price (price),         -- 可能很少按price单独查询
    index idx_status (status),       -- 状态只有几个值，索引效果差
    index idx_name_category (name, category)  -- 与单列索引重复
);

这里创建了5个索引，但很多可能用不上，反而影响性能

每个索引的代价：

占用磁盘空间：每个索引都是一个b+树
降低写性能：insert/update/delete需要更新所有索引
增加优化器负担：需要评估多个索引的选择

正确做法：按实际查询需求创建

create table products (
    id int primary key,
    name varchar(100),
    category varchar(50),
    price decimal(10,2),
    status tinyint default 1,
    -- 根据业务查询模式创建索引
    index idx_category_status_price (category, status, price),  -- 联合索引
    index idx_name (name)  -- 只有经常单独按name查询才需要
);

使用联合索引覆盖多个查询条件，比多个单列索引更高效

问题2：在低选择性字段上创建索引

-- 选择性太低的索引
create table employees (
    id int primary key,
    name varchar(100),
    gender enum('男','女'),        -- 只有2个可能值
    status tinyint default 1,      -- 1激活, 0禁用, 2删除
    department varchar(50),
    index idx_gender (gender),
    index idx_status (status) 
);

gender和status字段值重复度高，创建索引效果很差

假设表有10000条数据：

gender索引：2个不同值
status索引：3个不同值
查询时可能返回大量数据，索引效果差

正确做法：选择高区分度字段

create table employees (
    id int primary key,
    name varchar(100),
    gender enum('男','女'),
    status tinyint default 1,
    department varchar(50),
    employee_code varchar(20) unique,  -- 员工编号，唯一性高
    email varchar(100),                -- 邮箱，区分度高
    
    -- 创建有价值的索引
    unique index uk_employee_code (employee_code),
    index idx_email (email),
    index idx_department_gender (department, gender) -- 联合索引选择性较好
);

employee_code和email字段值几乎不重复，索引效果很好

问题3：索引列参与计算或函数

-- 创建测试表
create table orders (
    id int primary key,
    order_date date,                -- 日期字段，有索引
    amount decimal(10,2),           -- 金额字段，有索引
    customer_name varchar(100),     -- 客户名，有索引
    index idx_order_date (order_date),
    index idx_amount (amount),
    index idx_customer_name (customer_name)
);

错误的查询方式：索引列参与计算

select * from orders where year(order_date) = 2024;        -- 索引失效！
select * from orders where amount * 1.1 > 1000;           -- 索引失效！
select * from orders where upper(customer_name) = 'john'; -- 索引失效！
select * from orders where order_date + interval 1 day > '2024-01-01'; -- 索引失效！

在索引列上使用函数或计算，mysql无法使用索引，会导致全表扫描

正确的查询方式

select * from orders 
where order_date >= '2024-01-01' and order_date < '2025-01-01';  -- 使用索引

select * from orders where amount > 1000 / 1.1;                  -- 使用索引

select * from orders where customer_name = 'john';               -- 使用索引

select * from orders where order_date > '2024-01-01' - interval 1 day;  -- 使用索引

保持索引列干净，把计算移到等号右边，mysql就能使用索引

问题4：最左前缀原则

什么是联合索引？ 联合索引也叫复合索引，是在多个列上创建的索引。

-- 创建联合索引
create table sales (
    id int primary key,
    region varchar(50),      -- 地区
    city varchar(50),        -- 城市
    sale_date date,          -- 销售日期
    amount decimal(10,2),
    index idx_region_city_date (region, city, sale_date)  -- 联合索引
);

这个联合索引按region→city→sale_date的顺序组织数据

为什么使用联合索引？ 1.减少索引数量：一个联合索引替代多个单列索引 2.覆盖更多查询：支持多种查询条件组合 3.避免回表：如果查询字段都在索引中，不需要访问数据行 4.排序优化：天然支持按索引顺序排序

联合索引的性价比体现在：

存储成本：1个联合索引 < 3个单列索引
查询性能：联合索引可以一次性满足复杂查询
维护成本：只需要维护1个索引结构

-- 能充分利用联合索引的查询
select * from sales where region = '北京';  -- 使用索引
select * from sales where region = '北京' and city = '朝阳区'; -- 使用索引
select * from sales where region = '北京' and city = '朝阳区' and sale_date = '2024-01-01'; -- 使用索引
select * from sales where region = '北京' order by city;  -- 使用索引

这些查询都能充分利用联合索引，因为条件从最左列开始

-- 不能使用或不能充分利用联合索引的查询
select * from sales where city = '朝阳区'; -- 无法使用索引
select * from sales where sale_date = '2024-01-01'; -- 无法使用索引
select * from sales where region = '北京' and sale_date = '2024-01-01';  -- 只能使用region部分
select * from sales where city = '朝阳区' and sale_date = '2024-01-01';  -- 无法使用索引

缺少最左列region，索引无法使用或只能部分使用

最佳实践

实践1：选择合适的索引类型

1.主键索引（聚集索引）- 最重要的索引

create table users (
    id int auto_increment primary key,  -- 自动创建聚集索引
    name varchar(50)
);

主键索引决定数据物理存储顺序，一个表只能有一个

2. 唯一索引 - 保证数据唯一性

create table users (
    id int primary key,
    email varchar(100) unique,      -- 唯一索引
    phone varchar(20) unique        -- 唯一索引
);

唯一索引既保证数据唯一，又提供查询加速

3. 联合索引 - 多条件查询的最佳选择

create table orders (
    id int primary key,
    user_id int,
    status tinyint,
    created_at datetime,
    index idx_user_status_created (user_id, status, created_at)
);

联合索引的顺序很重要，应该把最常用的等值查询条件放在前面

4. 前缀索引 - 处理长文本字段

create table articles (
    id int primary key,
    title varchar(500),
    content text,
    index idx_title_prefix (title(50))  -- 只索引前50个字符
);

对于长文本字段，可以只索引前n个字符，平衡性能与存储

实践2：覆盖索引

什么是覆盖索引？

当查询的所有字段都包含在索引中时，mysql只需要访问索引而不需要回表查询数据行。

-- 创建测试表
create table user_activities (
    id int primary key,
    user_id int,
    activity_type varchar(50),
    activity_time datetime,
    description text,  -- 大文本字段
    index idx_user_activity_time (user_id, activity_type, activity_time)
);

-- 需要回表查询的例子
select * from user_activities 
where user_id = 123 and activity_type = 'login';

执行过程：

1.在索引idx_user_activity_time中找到匹配记录

2.获取对应的主键id

3.通过主键id到数据行中读取所有字段（包括大文本description）

4.返回结果

-- 覆盖索引的例子
select user_id, activity_type, activity_time 
from user_activities 
where user_id = 123 and activity_type = 'login';

执行过程：

1.在索引idx_user_activity_time中找到匹配记录

2.直接返回索引中的字段值（user_id, activity_type, activity_time都在索引中）

3.不需要访问数据行

覆盖索引的优势：

性能提升：避免回表操作，减少io
减少内存使用：不需要加载整行数据
查询更快：特别是在有text/blob字段的表上

实践3：索引维护和监控

1. 查看索引使用情况

select 
    table_name,
    index_name,
    seq_in_index,
    column_name
from information_schema.statistics 
where table_schema = 'your_database' 
and table_name = 'your_table';

查看表的索引结构和字段信息

2. 查找冗余索引

select 
    t.table_name,
    s.index_name,
    group_concat(s.column_name order by s.seq_in_index) as columns
from information_schema.statistics s
join information_schema.tables t on s.table_name = t.table_name 
where s.table_schema = 'your_database'
group by t.table_name, s.index_name
order by t.table_name, s.index_name;

找出可能重复或冗余的索引

3. 重建索引优化性能

optimize table your_table;
-- 或者
alter table your_table engine=innodb;

重建表可以消除索引碎片，提高性能

不同业务的不同策略

场景1：电商商品搜索优化

create table products (
    id int primary key,
    name varchar(200),
    category_id int,
    brand_id int,
    price decimal(10,2),
    status tinyint default 1,  -- 1上架 0下架
    stock_count int,
    created_at datetime,
    
    -- 针对电商常见查询模式设计索引
    index idx_category_status_price (category_id, status, price),
    index idx_brand_status (brand_id, status),
    index idx_name_category (name, category_id),
    index idx_created_status (created_at, status)
);

高频查询优化：

查询1：分类页面商品列表

select * from products 
where category_id = 5 and status = 1 
order by price desc limit 20;

索引使用：idx_category_status_price 说明：等值查询category_id和status，按price排序，完美匹配索引

查询2：品牌商品搜索

select * from products 
where brand_id = 10 and status = 1 
order by created_at desc limit 10;

索引使用：idx_brand_status 说明：等值查询brand_id和status，索引覆盖查询条件

查询3：商品搜索

select * from products 
where name like '手机%' and category_id = 5 and status = 1;

索引使用：idx_name_category 说明：前缀匹配name，等值查询category_id和status

场景2：社交平台消息系统

create table messages (
    id bigint primary key,
    from_user_id bigint,
    to_user_id bigint,
    content text,
    is_read tinyint default 0,
    created_at datetime,
    
    -- 针对消息查询模式优化
    index idx_to_user_created (to_user_id, created_at),
    index idx_from_user_created (from_user_id, created_at),
    index idx_conversation (least(from_user_id, to_user_id), greatest(from_user_id, to_user_id), created_at)
);

典型查询优化：

查询1：查看收件箱（最新消息在前）

select * from messages 
where to_user_id = 123 
order by created_at desc 
limit 20;

索引使用：idx_to_user_created 说明：等值查询to_user_id，按created_at排序，完美匹配索引

查询2：查看对话历史

select * from messages 
where least(from_user_id, to_user_id) = 123 
  and greatest(from_user_id, to_user_id) = 456 
order by created_at;

索引使用：idx_conversation 说明：使用函数索引优化对话查询，避免or条件

场景3：日志分析系统

create table access_logs (
    id bigint primary key,
    user_id int,
    action varchar(50),
    resource_path varchar(500),
    ip_address varchar(45),
    access_time datetime,
    response_time int,
    
    -- 日志分析查询优化
    index idx_access_time (access_time),
    index idx_user_action_time (user_id, action, access_time),
    index idx_action_response (action, response_time)
);

分析查询优化：

查询1：时间范围统计

select action, count(*) 
from access_logs 
where access_time between '2024-01-01' and '2024-01-31'
group by action;

索引使用：idx_access_time 说明：范围查询access_time，索引快速定位时间范围

查询2：用户行为分析

select user_id, action, count(*) 
from access_logs 
where user_id = 123 
  and access_time >= '2024-01-01'
group by user_id, action;

索引使用：idx_user_action_time 说明：等值查询user_id，范围查询access_time，索引覆盖查询条件

性能优化

索引设计检查清单：

只为高频查询条件创建索引
选择性 > 10% 的字段才考虑索引
复合索引遵循最左前缀原则
避免索引列参与计算或函数
考虑覆盖索引优化
定期监控索引使用情况
删除长时间未使用的索引

查询优化技巧：

使用explain分析执行计划
避免select *，只取需要的字段
大数据量表考虑分区
合理使用limit限制结果集
避免在where子句中使用not、!=、<>操作

总结

1.理解业务需求：索引设计要从实际查询模式出发 2.平衡读写性能：索引加速查询但降低写性能 3.精准设计：联合索引比多个单列索引更高效 4.关注选择性：高选择性字段更适合创建索引 5.持续优化：随着业务发展调整索引策略

"为你的查询设计索引，而不是为你的表设计索引"

以上就是mysql中索引的常见问题与性能优化详解的详细内容，更多关于mysql索引优化的资料请关注代码网其它相关文章！

MySQL中索引的常见问题与性能优化详解

2025年11月20日 • Mysql •我要评论