MySQL分页查询优化

引言

后台列表、文章分页是最常见的SQL场景。数据量小的时候LIMIT偏移量查询很快，数据到几十万、上百万行，深分页会明显变慢，甚至拖垮数据库。

为什么深分页慢

LIMIT 100000,20 会先扫描前100000行再丢弃，只返回20行，扫描行数越多IO开销越大。

优化方案一：子查询定位主键

先查出起始主键，再用主键关联取数据，避免回表扫描：

SELECT a.* FROM article a
INNER JOIN (
  SELECT id FROM article ORDER BY id LIMIT 100000,20
) b ON a.id=b.id;

子查询走覆盖索引，速度明显提升。

优化方案二：游标/记住上一页最大ID

SELECT * FROM article
WHERE id > 100000
ORDER BY id LIMIT 20;

每次只查比上一页最后一条ID大的记录，不走大偏移，适合无限滚动。

其他优化点

• 分页排序字段必须建索引，避免filesort；
• 只查询需要的字段，不用SELECT *；
• 禁止前端跳转到任意深页，后台限制最大页码；
• 超大表考虑分表、搜索引擎承接列表。

总结

深分页慢的根源是大偏移扫描。优先用主键游标翻页，配合覆盖索引；传统offset分页用子查询优化。列表数据量极大时，交给ES等搜索方案更合理。

MySQL索引设计原则

引言

MySQL索引相当于书籍目录，快速定位数据，避免全表扫描。不合理的索引不仅无法提升查询速度，还会降低写入性能。索引设计是数据库优化最基础、最高效的手段，建站、接口开发都需要掌握。

索引核心原理

InnoDB引擎索引基于B+树实现，主键索引叶子节点存储完整行数据；二级索引叶子节点存储主键值，查询会触发回表。索引字段越大，占用磁盘越多，内存缓存能存放的索引越少，查询越慢。

索引设计核心原则

1. 选择性优先：优先给区分度高的字段建索引，如手机号、ID；性别、状态这类只有少量枚举值字段不适合单独建索引。
2. 联合索引遵循最左匹配原则，字段顺序：等值查询在前，范围查询放后面。
3. 避免在索引字段做函数运算，where YEAR(create_time)=2026会直接失效索引。
4. 不要过度建索引，每次新增、更新、删除数据，所有索引都需要同步更新，拖累写入。

示例SQL

-- 联合索引示例，适合 where uid=100 and create_time>xxx
CREATE INDEX idx_uid_ct ON user(uid,create_time);

-- 查看sql是否命中索引
EXPLAIN SELECT * FROM user WHERE uid=100;

常见踩坑

• 字符串字段不加引号，隐式类型转换，索引失效；
• 模糊查询%关键词，前置百分号不走索引；
• 联合索引跳过左侧字段，无法触发索引。

总结

索引不是越多越好，按需创建。建索引前先用explain分析执行计划，优先优化高频查询SQL，控制索引数量，平衡查询与写入性能。

前言

明明加了索引，但查询还是慢？explain一看type是ALL，索引根本没生效！本文总结10种索引失效的场景，帮你避坑。

一、对索引字段用函数

失效写法

SELECT * FROM users WHERE LEFT(name, 3) = '张三';
SELECT * FROM users WHERE DATE(create_time) = '2024-01-01';

正确写法

SELECT * FROM users WHERE name LIKE '张三%';
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

原因： 对索引字段用函数，MySQL不知道索引值会变成什么，索引用不上。

二、索引字段用运算

失效写法

SELECT * FROM users WHERE age + 1 = 20;
SELECT * FROM orders WHERE price * 2 > 100;

正确写法

SELECT * FROM users WHERE age = 19;
SELECT * FROM orders WHERE price > 50;

原因： 运算后索引值变了，MySQL没法用索引。

三、最左前缀原则不满足

联合索引

INDEX idx_name_age_city(name, age, city)

失效写法

SELECT * FROM users WHERE age = 20; -- 跳过了name
SELECT * FROM users WHERE city = '北京'; -- 跳过了name和age

正确写法

SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 20;

原则： 联合索引要从最左边的字段开始用，不能跳过。

四、like以%开头

失效写法

SELECT * FROM users WHERE name LIKE '%张三%';
SELECT * FROM users WHERE name LIKE '%张三';

正确写法

SELECT * FROM users WHERE name LIKE '张三%';

原因： 以%开头，MySQL不知道从哪开始找，索引用不上。

五、类型不匹配

失效写法

-- phone是varchar类型
SELECT * FROM users WHERE phone = 13800138000; -- 传了数字

正确写法

SELECT * FROM users WHERE phone = '13800138000'; -- 传字符串

原因： 类型不匹配，MySQL会做隐式转换，索引用不上。

六、OR两边有没索引的字段

失效写法

-- name有索引，age没索引
SELECT * FROM users WHERE name = '张三' OR age = 20;

原因： OR只要有一边没索引，MySQL就干脆全表扫描。

解决

-- age也加索引
ALTER TABLE users ADD INDEX idx_age(age);

七、使用NOT、!=、<>

失效写法

SELECT * FROM users WHERE name != '张三';
SELECT * FROM users WHERE age NOT IN (18, 20);

原因： 不等于操作，MySQL觉得要查大部分数据，不如全表扫描。

解决

-- 改成范围查询
SELECT * FROM users WHERE name > '张三';

八、is null / is not null

这个要看情况，有的版本会失效，有的不会。

建议： 尽量让字段不要为null，设默认值。

九、查询条件用了函数或表达式

失效写法

SELECT * FROM users WHERE ABS(age) = 20;
SELECT * FROM users WHERE age / 2 = 10;

正确写法

SELECT * FROM users WHERE age = 20;

十、MySQL自己判断不走索引

有时候SQL写法没问题，但MySQL优化器觉得走索引还不如全表扫描。

比如： 表数据很少，或者要查的数据占表的大部分。

解决： 用FORCE INDEX强制走索引

SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';

十一、怎么检查索引有没有生效

用explain

EXPLAIN SELECT * FROM users WHERE name = '张三';

看key字段：

• key有值：用了索引
• key是NULL：没用到索引

看type字段：

• type=ALL：全表扫描
• type=ref/range：用了索引

十二、常见坑

坑1：加了索引但SQL写法不对

解决： 按上面10种场景检查。

坑2：索引加太多

索引不是越多越好，写的时候要维护索引，会变慢。

建议： 单表索引不超过5个。

坑3：区分度低的字段不加索引

比如gender（男/女），加索引没用。

原则： 区分度高的字段加索引。

坑4：隐式类型转换

例子： varchar字段传了数字。

解决： 传参时类型要匹配。

十三、索引使用原则

1. 对WHERE条件的字段加索引
2. 对ORDER BY的字段加索引
3. 联合索引遵守最左前缀
4. 不要对索引字段用函数
5. 不要对索引字段做运算
6. like不要以%开头
7. 类型要匹配
8. 区分度高的字段加索引

总结

10种索引失效场景：

1. 对索引字段用函数
2. 索引字段用运算
3. 最左前缀原则不满足
4. like以%开头
5. 类型不匹配
6. OR两边有没索引的字段
7. 使用NOT、!=、<>
8. is null / is not null
9. 查询条件用了表达式
10. MySQL自己判断不走索引

记住：

• 用explain检查索引有没有生效
• key=NULL就是没用到索引
• type=ALL就是全表扫描
• 避开上面10种场景

遇到问题加QQ23979811 协助处理

前言

网站突然变慢，数据库CPU100%？多半是慢查询搞的鬼。本文讲怎么开启慢查询日志、用explain分析SQL执行计划，找到慢的原因。

一、开启慢查询日志

临时开启

-- 查看慢查询是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启慢查询
SET GLOBAL slow_query_log = ON;

-- 超过1秒就算慢查询
SET GLOBAL long_query_time = 1;

永久开启

编辑my.cnf：

vi /etc/my.cnf

加：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

重启MySQL：

systemctl restart mysqld

看慢查询日志

tail -f /var/log/mysql/slow.log

二、explain是什么

explain用来分析SQL是怎么执行的，看有没有用索引、扫了多少行。

用法

EXPLAIN SELECT * FROM users WHERE name = '张三';

三、explain字段解释

id

SELECT的标识符，越大越先执行。

select_type

查询类型：

• SIMPLE：简单查询
• PRIMARY：主查询
• SUBQUERY：子查询

table

查的哪张表。

type（最重要）

访问类型，从好到差：

重点看： 出现ALL就是全表扫描，要加索引。

possible_keys

可能用到的索引。

key

实际用到的索引。

key是NULL就是没用到索引。

rows

预估要扫描多少行。

越大越慢。

Extra

额外信息：

四、常见慢查询原因

原因1：没加索引

SELECT * FROM users WHERE name = '张三';

name字段没索引 → 全表扫描 → 慢。

解决： 加索引

ALTER TABLE users ADD INDEX idx_name(name);

原因2：索引失效

虽然加了索引，但SQL写法不对，索引用不上。

索引失效的情况：

1. 对索引字段用函数：

   SELECT * FROM users WHERE LEFT(name, 3) = '张三'; -- 索引失效

2. 索引字段用运算：

   SELECT * FROM users WHERE age + 1 = 20; -- 索引失效

3. 最左前缀原则不满足：

   -- 联合索引(a, b, c)
   SELECT * FROM t WHERE b = 1; -- 索引失效

4. like以%开头：

   SELECT * FROM users WHERE name LIKE '%张三%'; -- 索引失效

5. 类型不匹配：

   -- phone是varchar
   SELECT * FROM users WHERE phone = 13800138000; -- 索引失效

原因3：SELECT *

查了不需要的字段，占内存。

解决： 只查需要的字段

SELECT id, name FROM users WHERE ...

原因4：大表分页越查越慢

SELECT * FROM users ORDER BY id LIMIT 100000, 10;

慢的原因： 要先扫10万行再跳过。

解决：

SELECT u.* FROM users u
INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 100000, 10) t
ON u.id = t.id;

五、实战案例

慢SQL

SELECT * FROM orders WHERE user_id = 123 AND status = 1 ORDER BY create_time DESC LIMIT 10;

explain分析

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 1 ORDER BY create_time DESC LIMIT 10;

发现： type是ALL，key是NULL → 全表扫描。

加索引

ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time);

再explain： type变成ref，key是idx_user_status_time → 用上索引了。

六、优化原则

1. WHERE条件加索引
2. ORDER BY字段加索引
3. 联合索引遵守最左前缀
4. **不要SELECT ***
5. 避免在索引字段上用函数
6. 大分页用子查询优化

七、常见坑

坑1：加了索引但没用

原因： SQL写法导致索引失效。

排查： explain看key字段。

坑2：索引加太多

索引不是越多越好，写的时候要维护索引，会变慢。

建议： 单表索引不超过5个。

坑3：区分度低的字段不加索引

比如gender（男/女），加索引没用。

原则： 区分度高的字段加索引。

总结

记住：

1. 开启慢查询日志找慢SQL
2. 用explain分析SQL执行计划
3. type=ALL就是全表扫描，要加索引
4. key=NULL就是没用到索引
5. rows越大越慢
6. 索引失效要避免

最常用：

EXPLAIN SELECT * FROM users WHERE ...

遇到问题加QQ23979811 协助处理

MySQL 索引优化技巧

索引是提升查询性能的关键，但用不好反而拖慢速度。

1. 为什么要建索引

• 没有索引要全表扫描
• 数据量大时查询很慢
• 索引相当于书的目录
• 合适的索引能提升百倍速度

2. 索引类型

主键索引：

• 主键自动创建
• 唯一且非空

普通索引：

• 最基本的索引
• 加速查询

唯一索引：

• 值不能重复
• 比如手机号

联合索引：

• 多个字段组成
• 遵循最左前缀原则

3. 建索引原则

1）最频繁查询的字段建索引

-- 经常按name查
CREATE INDEX idx_name ON users(name);

2）区分度高的字段建索引

• 性别这种区分度低的不要建
• 手机号、身份证这种适合

3）联合索引注意顺序

-- 联合索引 (a, b, c)
-- 最左前缀：
-- a 能用
-- a,b 能用
-- a,b,c 能用
-- 单独b,c 用不了

4. 索引失效情况

1）where 里对索引字段用函数

-- 失效
SELECT * FROM users WHERE DATE(create_time) = '2026-01-01';

-- 有效
SELECT * FROM users WHERE create_time >= '2026-01-01' AND create_time < '2026-01-02';

2）where 里对索引字段做运算

-- 失效
SELECT * FROM users WHERE age + 1 = 20;

-- 有效
SELECT * FROM users WHERE age = 19;

3）like 以 % 开头

-- 失效
SELECT * FROM users WHERE name LIKE '%张';

-- 有效
SELECT * FROM users WHERE name LIKE '张%';

4）隐式类型转换

-- phone是字符串
-- 失效（数字转字符串）
SELECT * FROM users WHERE phone = 13800138000;

-- 有效
SELECT * FROM users WHERE phone = '13800138000';

5. EXPLAIN 查看执行计划

EXPLAIN SELECT * FROM users WHERE name = '张三';

重点看：

• type： 访问类型，至少要 range
• key： 实际用到的索引
• rows： 扫描行数
• Extra： 额外信息

6. 索引不要建太多

• 每个索引都占空间
• 插入更新时要维护索引
• 不是越多越好
• 一张表索引建议不超过5个

7. 覆盖索引

避免回表：

-- 索引包含查询的所有字段
SELECT name, age FROM users WHERE name = '张三';

-- 索引 (name, age) 就是覆盖索引
-- 不用回表查主键

Extra 显示 Using index 就是覆盖索引。

8. 慢查询定位

-- 开启慢查询
slow_query_log = 1
long_query_time = 1  -- 超过1秒记录

-- 查看慢查询日志
-- 找到慢SQL，加索引优化

9. 常见优化场景

1）分页查询优化

-- 慢（深分页）
SELECT * FROM orders LIMIT 100000, 10;

-- 快（子查询）
SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders LIMIT 100000, 1)
LIMIT 10;

2）JOIN 字段建索引

• JOIN 的两边都要建索引
• 类型要一致

3）ORDER BY 用索引

• 排序字段建索引
• 避免 filesort

10. 索引维护

查看索引：

SHOW INDEX FROM users;

删除多余索引：

DROP INDEX idx_name ON users;

分析表：

ANALYZE TABLE users;

总结

MySQL 索引优化要点：

• 区分度高的字段建索引
• 联合索引遵循最左前缀
• 避免索引失效（函数、运算、like%）
• 用 EXPLAIN 分析执行计划
• 索引不要建太多
• 覆盖索引避免回表
• 慢查询日志找问题SQL
• 深分页要优化
• JOIN 两边都建索引
• 定期维护索引

索引优化是MySQL性能优化的核心，一定要多实践。

MySQL 索引优化技巧

索引是数据库性能的关键，用对了查询快很多。

1. 索引类型

• 主键索引： 主键自带，唯一且非空
• 普通索引： 最基本的索引
• 唯一索引： 字段值不能重复
• 联合索引： 多个字段组成的索引
• 全文索引： 全文搜索用

2. 创建索引

-- 普通索引
CREATE INDEX idx_name ON users(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_phone ON users(phone);

-- 联合索引
CREATE INDEX idx_name_age ON users(name, age);

3. 索引失效情况

1）不要在索引字段上做运算：

-- 失效
SELECT * FROM users WHERE age + 1 = 20;

-- 生效
SELECT * FROM users WHERE age = 19;

2）like 左模糊：

-- 失效
SELECT * FROM users WHERE name LIKE '%张';

-- 生效
SELECT * FROM users WHERE name LIKE '张%';

3）联合索引不满足最左前缀：

-- 索引：(name, age)
-- 生效（从name开始）
SELECT * FROM users WHERE name = '张三' AND age = 20;

-- 失效（跳过name直接查age）
SELECT * FROM users WHERE age = 20;

4）用 or 连接：

• or 两边有一个没索引就全表扫描

4. 优化原则

• 频繁查询的字段加索引
• 区分度高的字段适合做索引
• 不要加太多索引（影响写入性能）
• 联合索引注意最左前缀
• 尽量扩展索引，不要新建索引

5. 查看索引

-- 查看表的索引
SHOW INDEX FROM users;

6. 常见问题

• 索引加了还是慢？检查是不是失效了
• 查询还是慢？用 EXPLAIN 分析
• 写入慢？索引太多了

总结

MySQL 索引优化要点：

• 常用查询字段加索引
• 注意索引失效情况
• 联合索引最左前缀
• 不要加太多索引
• EXPLAIN 分析执行计划

数据库性能优化，索引是第一步。

明明建了索引，查询还是很慢？十有八九是索引失效了。MySQL 索引失效是开发中最常见的坑，下面整理了最容易导致索引失效的场景，写SQL的时候一定要避开。

1. 对索引字段做函数操作

-- 索引失效
SELECT * FROM user WHERE DATE(create_time) = '2026-10-01';

-- 正确写法，走索引
SELECT * FROM user WHERE create_time >= '2026-10-01' AND create_time < '2026-10-02';

对索引字段用了 DATE()、YEAR()、SUBSTRING() 等函数，索引就失效了。

2. 隐式类型转换

-- 假设 phone 是 varchar 类型
-- 索引失效（数字和字符串比较，MySQL自动转换）
SELECT * FROM user WHERE phone = 13800138000;

-- 正确写法，走索引
SELECT * FROM user WHERE phone = '13800138000';

字符串类型的字段，查询条件一定要加引号。

3. 联合索引不满足最左匹配原则

-- 联合索引：(a, b, c)
-- 走索引
SELECT * FROM t WHERE a = 1 AND b = 2;

-- 不走索引（跳过了a）
SELECT * FROM t WHERE b = 2 AND c = 3;

-- 走部分索引（a走，b和c不走）
SELECT * FROM t WHERE a = 1 AND c = 3;

联合索引必须从最左边的列开始用，不能跳过。

4. 使用了 != 或 <>

-- 索引大概率失效
SELECT * FROM user WHERE status != 1;

-- 可以改成范围查询
SELECT * FROM user WHERE status < 1 OR status > 1;

不等于操作通常会导致全表扫描，因为要返回的结果太多了。

5. LIKE 以 % 开头

-- 索引失效
SELECT * FROM user WHERE name LIKE '%张';

-- 走索引
SELECT * FROM user WHERE name LIKE '张%';

模糊查询左边带 %，索引就用不上了。

6. OR 连接的条件有一个没索引

-- name 有索引，age 没索引 → 整个SQL不走索引
SELECT * FROM user WHERE name = '张三' OR age = 20;

OR 两边只要有一个字段没索引，整个查询就不走索引。

7. 用 explain 验证索引是否生效

EXPLAIN SELECT * FROM user WHERE name = '张三';

看 key 列：

• 显示索引名 → 走索引了
• 显示 NULL → 没走索引

看 type 列：

• ref、eq_ref → 正常
• ALL → 全表扫描，索引失效了

常见坑总结

1. 不要在索引字段上做运算：WHERE age + 1 = 20 → 改成 WHERE age = 19
2. 不要用 NOT IN / NOT EXISTS：大数据量下很慢
3. **不要用 SELECT ***：只查需要的字段，覆盖索引更快
4. 数据量小的表不要建索引：全表扫描可能比索引还快

遇到问题加QQ23979811 协助处理

MySQL 索引优化技巧

索引是数据库性能的关键，建对了快10倍，建错了反而更慢。这篇讲实战技巧。

1. 索引类型

普通索引： 最基本的索引，没有限制
唯一索引： 列值不能重复
主键索引： 特殊的唯一索引，一张表只能一个
联合索引： 多个字段组成的索引

2. 建索引原则

1）where 条件经常用的字段建索引

-- 常用查询
SELECT * FROM users WHERE email = 'xxx@qq.com';

-- 给 email 建索引
CREATE INDEX idx_email ON users(email);

2）联合索引最左前缀原则

-- 建联合索引 (name, age)
CREATE INDEX idx_name_age ON users(name, age);

-- 能用索引
WHERE name = '张三'
WHERE name = '张三' AND age = 25

-- 不能用索引（跳过了name）
WHERE age = 25

3）避免建太多索引

• 索引不是越多越好
• 每个索引都占空间，影响写入速度
• 一张表索引不要超过5个

3. 索引失效场景

1）不要在索引字段上做运算

-- 坏（索引失效）
SELECT * FROM users WHERE age + 1 = 26;

-- 好
SELECT * FROM users WHERE age = 25;

2）不要用函数

-- 坏
SELECT * FROM users WHERE DATE(create_time) = '2026-10-01';

-- 好
SELECT * FROM users WHERE create_time >= '2026-10-01' AND create_time < '2026-10-02';

3）like 前导模糊

-- 坏（用不到索引）
SELECT * FROM users WHERE name LIKE '%张%';

-- 好
SELECT * FROM users WHERE name LIKE '张%';

**4）避免 select ***

-- 坏（查所有字段，可能回表）
SELECT * FROM users WHERE name = '张三';

-- 好（覆盖索引）
SELECT id, name FROM users WHERE name = '张三';

4. 查看索引使用

-- 查看表索引
SHOW INDEX FROM users;

-- 用 explain 分析
EXPLAIN SELECT * FROM users WHERE name = '张三';

重点看：

• type：const/ref 好，ALL 全表扫描坏
• key：实际用到的索引
• rows：扫描行数

5. 总结

索引优化记住：

• where 字段建索引
• 联合索引最左前缀
• 别在索引字段上做运算
• 避免前导模糊查询
• 用 explain 检查

索引是数据库优化的第一步，做好了性能提升很大。

MySQL 性能优化实战

数据库慢是网站性能的大问题，这篇讲实战优化方法，从几个方面下手。

1. 慢查询排查

先找到慢SQL，再优化。

开启慢查询日志：

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log%';

-- 开启慢查询
SET GLOBAL slow_query_log = ON;

-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 1;

用 explain 分析：

EXPLAIN SELECT * FROM users WHERE name = '张三';

重点看：

• type：有没有用到索引（ref/const 好，ALL 全表扫描坏）
• key：实际用的索引
• rows：扫描行数

2. 索引优化

建索引原则：

• where 条件经常用的字段建索引
• 联合索引最左前缀原则
• 避免建太多索引，影响写入

查看索引：

SHOW INDEX FROM users;

建索引：

-- 普通索引
CREATE INDEX idx_name ON users(name);

-- 联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

3. SQL 优化

**不要 SELECT ***

-- 坏
SELECT * FROM users WHERE id = 1;

-- 好
SELECT id, name, email FROM users WHERE id = 1;

避免 LIKE 前导模糊：

-- 坏（用不到索引）
SELECT * FROM users WHERE name LIKE '%张%';

-- 好（能用索引）
SELECT * FROM users WHERE name LIKE '张%';

大表分页优化：

-- 坏（深度分页慢）
SELECT * FROM users LIMIT 100000, 10;

-- 好（子查询先定位）
SELECT * FROM users 
WHERE id >= (SELECT id FROM users LIMIT 100000, 1) 
LIMIT 10;

4. 表结构优化

• 用合适的数据类型（能用 int 就别用 bigint）
• 字段尽量 NOT NULL
• 大表分表分库

5. 配置优化

my.cnf 常用配置：

# 缓冲池大小（内存的 70%）
innodb_buffer_pool_size = 1G

# 连接数
max_connections = 500

# 慢查询日志
slow_query_log = 1
long_query_time = 1

6. 总结

MySQL 优化三步走：

1. 找慢SQL： 慢查询日志 + explain
2. 加索引： where 条件字段
3. 优化SQL： 不写 SELECT *，避免全表扫描

数据库优化不是一蹴而就，要持续监控和调整。

前言

做SQL优化的同学肯定听说过覆盖索引，很多文章里都说用覆盖索引能提升查询性能。但是很多新手同学搞不懂覆盖索引到底是什么，为什么能提升性能。

这篇文章就把覆盖索引的知识点从头到尾讲清楚：什么是覆盖索引？为什么能提升性能？怎么判断是不是用了覆盖索引？怎么利用覆盖索引优化查询？看完之后你就彻底搞懂了。

一、什么是覆盖索引

先说说回表是什么。

MySQL的InnoDB引擎，主键索引是聚簇索引，叶子节点直接存整行数据。普通索引的叶子节点存的是主键值。

如果你用普通索引查询，查到主键值之后，还要再去主键索引里查一遍，拿到完整的行数据。这个过程就叫回表。

那什么是覆盖索引呢？就是：你要查询的字段，在索引里就都有了，不用再回表去主键索引里查了。这就叫覆盖索引，或者叫索引覆盖。

简单来说：查询需要的所有数据，索引里都有，不用回表，就是覆盖索引。

二、为什么覆盖索引能提升性能

为什么覆盖索引能提升性能？因为少了回表这一步。

我们对比一下：

没有覆盖索引的情况

1. 先在普通索引里查，找到主键值
2. 再拿着主键值去主键索引里查，拿到完整的行数据

这样要查两棵B+树，查了两次，当然慢。

有覆盖索引的情况

1. 直接在索引里查到所有需要的数据，就完事了

这样只查了一棵B+树，少了回表这一步，当然快。

尤其是表很大的时候，回表要做随机IO，性能很差。如果不用回表，直接在索引里就能拿到数据，那性能提升就非常明显了。

三、怎么判断是不是覆盖索引

用explain看执行计划的时候，如果Extra列里显示 Using index，就说明用了覆盖索引，不用回表。

比如：

+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ref   | name          | name | 767     | const|  10  | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

看到Extra里的Using index了吗？这就说明用了覆盖索引。

四、怎么利用覆盖索引优化查询

知道了覆盖索引的好处，那怎么利用它来优化查询呢？

1. 查询的时候只查需要的字段，不要SELECT *

很多同学写SQL的时候喜欢写SELECT *，把所有字段都查出来。这样你要查的字段太多了，索引里不可能全都有，就必须回表。

如果你只查需要的几个字段，那就有可能把这几个字段都加到索引里，这样就能用到覆盖索引了。

比如：

-- 不好的写法
SELECT * FROM users WHERE name = '张三';

-- 好的写法
SELECT id, name, age FROM users WHERE name = '张三';

2. 建联合索引，把经常查询的字段都加进去

如果你经常根据name查询，并且经常查name和age，那就建一个(name, age)的联合索引。

这样当你查SELECT name, age FROM users WHERE name = '张三'的时候，索引里就有name和age了，不用回表，直接就能拿到数据。

3. 把常用的查询字段加到索引里

除了WHERE条件的字段，SELECT里的字段也可以加到索引里，这样就能实现覆盖索引。

比如你经常查SELECT id, name, age FROM users WHERE name = ?，那就建一个(name, age)的联合索引，因为id是主键，InnoDB的普通索引叶子节点本来就存主键值，所以索引里就有id、name、age三个字段了，正好覆盖你的查询。

五、覆盖索引的注意点

1. 不是所有查询都能用覆盖索引

只有你要查询的字段都在索引里的时候，才能用覆盖索引。如果有字段不在索引里，那就必须回表。

2. 覆盖索引也不是万能的

不要为了覆盖索引，把所有字段都加到索引里。索引太多了，会影响插入和更新的性能。要权衡一下，哪些查询是高频的，值得为它建覆盖索引。

3. 主键索引天然就是覆盖索引

如果你是用主键查询，那本来就是在主键索引里查，直接就能拿到所有数据，天然就是覆盖索引。

常见坑

坑1：以为只要用了索引就是覆盖索引

很多同学以为只要查询用到了索引，就是覆盖索引。其实不是，用到索引只是说用索引找到了主键值，如果你还要查其他字段，还是要回表。只有Extra里显示Using index，才是真的覆盖索引。

坑2：写SELECT *，用不了覆盖索引

很多同学写SQL的时候喜欢SELECT *，结果要查的字段太多了，索引里根本放不下，就用不了覆盖索引。记住：只查需要的字段，别查所有字段。

坑3：为了覆盖索引，索引建得太多

很多同学为了让某个查询用上覆盖索引，把那个查询的所有字段都加到索引里。结果一个表建了好几十个索引，插入更新的时候要维护这么多索引，性能很差。覆盖索引是好，但是不要滥用，要权衡。

坑4：不知道Using index就是覆盖索引

很多同学看explain的时候，看到Extra里的Using index不知道是什么意思。记住：Using index就是覆盖索引，不用回表，性能很好。

坑5：联合索引的顺序不对

很多同学建联合索引的时候，顺序建反了，结果用不了覆盖索引。记住：联合索引要满足最左前缀原则，WHERE条件里的字段要放在前面，SELECT的字段放在后面。

总结

MySQL覆盖索引的核心知识点：

1. 什么是覆盖索引：查询需要的所有数据，索引里都有，不用回表
2. 为什么能提升性能：少了回表这一步，不用查两棵B+树，减少IO
3. 怎么判断：explain的Extra列里显示Using index，就是覆盖索引
4. 怎么利用：
   • 不要写SELECT *，只查需要的字段
   • 建联合索引，把WHERE和SELECT的字段都加进去
5. 注意点：不要为了覆盖索引建太多索引，要权衡性能

记住：覆盖索引是SQL优化里很重要的一个手段，很多时候只要把SELECT *改成只查需要的字段，再建个合适的联合索引，性能就能提升好几倍。

遇到问题加QQ23979811 协助处理

前言

做网站开发或者运维的同学肯定都遇到过这种情况：网站越来越慢，数据库CPU占用越来越高，用户体验越来越差。这时候怎么办？很多同学一上来就说：加机器！加内存！其实这是最笨的方法。

MySQL性能优化是一个系统工程，要从多个方面下手：SQL、索引、表结构、配置参数、架构。很多时候，不用加机器，只要优化一下SQL和索引，性能就能提升好几倍。

这篇文章就把MySQL性能优化的完整思路整理出来，从定位问题到SQL优化、表结构优化、参数优化、架构优化，一步一步讲清楚。看完之后你就知道遇到性能问题应该从哪里下手了。

一、优化之前先定位问题

很多同学一上来就瞎优化，优化了半天也不知道有没有效果。正确的做法是：先定位问题，再针对性优化。

怎么定位慢SQL

1. 开启慢查询日志

MySQL的慢查询日志可以记录所有执行慢的SQL。先把慢查询日志开了，看看哪些SQL慢。

# my.cnf里配置
slow_query_log = 1
long_query_time = 1   # 超过1秒的SQL记录下来
slow_query_log_file = /var/log/mysql/slow.log

2. 用explain分析慢SQL

找到慢SQL之后，用explain看看它的执行计划，看看有没有走索引，是不是全表扫描。

3. 看数据库的状态

用下面的命令看看数据库当前的状态：

SHOW GLOBAL STATUS;

重点关注这几个指标：

• Slow_queries：慢查询的数量
• Threads_connected：当前连接数
• Innodb_row_reads：读了多少行数据
• Innodb_data_reads：读了多少数据

二、SQL和索引优化

这是最常用、也是性价比最高的优化方式。很多时候，加个索引，SQL性能就能提升几十倍。

1. 加合适的索引

哪些字段要加索引

• WHERE条件里经常用到的字段
• 表连接的字段
• ORDER BY、GROUP BY的字段
• 区分度高的字段

索引的注意点

• 不要加太多索引，索引也占空间，而且插入更新的时候要维护索引
• 联合索引要注意最左前缀原则
• 尽量不要在索引字段上做函数操作，不然索引会失效

2. 避免索引失效的情况

常见的索引失效场景：

• WHERE条件里对索引字段做函数操作
• WHERE条件里用了!=、<>、NOT IN
• LIKE以%开头
• 联合索引不满足最左前缀
• 字段类型不匹配（比如字符串字段用数字去查）

3. 优化SQL写法

不要用SELECT *

只查需要的字段，不要查所有字段。这样不仅减少数据传输，还能用到覆盖索引。

避免大分页

比如LIMIT 100000, 10，这种越往后越慢。优化方法：用上次的最大id来查。

避免在WHERE里做计算

比如WHERE age + 1 = 18，这样索引会失效。应该写成WHERE age = 17。

小表驱动大表

联表查询的时候，小表在前，大表在后，性能更好。

三、表结构优化

1. 选择合适的数据类型

• 能用数字类型就不要用字符串
• 能用短的就不要用长的，比如用TINYINT不要用INT
• 时间类型用DATETIME或者TIMESTAMP，不要用字符串存时间

2. 避免太多字段

一张表不要有太多字段，字段太多的话，数据页能放的行就少，IO次数就多。不常用的字段可以拆到另一张表里。

3. 适当加冗余字段

有些字段虽然可以联表查出来，但是如果经常用到，可以考虑加冗余字段，减少联表查询。

4. 大表做归档

如果表里有很多历史数据，但是查询的时候很少用到，可以把历史数据归档到另一张表里，主表只保留最近的数据。这样主表数据量小了，查询就快了。

四、配置参数优化

MySQL的默认配置很多都不是最优的，需要根据自己的服务器配置调一下。

1. innodb_buffer_pool_size

这个是InnoDB的缓冲池大小，是最重要的参数。一般设置成服务器内存的50%~70%。如果你的服务器内存是16G，那这个参数就设成8G~10G。

这个参数设大了，很多数据和索引都能在内存里，就不用读磁盘了，性能会好很多。

2. innodb_log_file_size

这个是redo log的大小，一般设成256M~1G。这个参数影响写性能，太小的话会频繁刷盘。

3. max_connections

最大连接数，根据你的业务量调整，一般设成500~1000就够了。不要设太大，不然每个连接都占内存，反而会拖慢性能。

4. query_cache

MySQL 8.0已经把查询缓存去掉了，因为查询缓存的命中率不高，而且维护成本很高。如果是老版本的MySQL，建议把查询缓存关了。

五、架构层面优化

如果SQL和索引都优化过了，还是不行，那就要从架构层面下手了。

1. 读写分离

主库写，从库读，把读的压力分散到多个从库上。大部分网站都是读多写少，读写分离效果很明显。

2. 加缓存

把热点数据放到Redis里，直接从Redis读，不用查数据库。这是提升性能最明显的方式。

3. 分库分表

单表数据量太大了，到了千万级甚至亿级，那就分库分表，把数据分散到多个表或者多个数据库里。

4. 用更好的硬件

如果前面的优化都做了，还是不够，那只能加硬件了。比如用SSD代替机械硬盘，数据库的性能瓶颈很多时候都是磁盘IO。SSD的IOPS比机械硬盘高几个数量级，提升非常明显。

常见坑

坑1：上来就加机器，不优化SQL

很多同学一遇到性能问题就说：加机器！加内存！其实很多时候都是SQL写的烂，没加索引，优化一下SQL性能就能提升好几倍。加机器是最后手段，不是第一选择。

坑2：索引加了很多，但是都没用到

很多同学觉得索引加的越多越好，结果加了一堆索引，真正查询的时候一个都没用到。加索引之前要先explain一下，看看是不是真的能用到。

坑3：以为加了索引就一定快

很多同学以为只要加了索引，查询就一定快。其实不是，如果索引字段的区分度很低，比如性别只有男和女，那加了索引也没用，优化器可能还是会选择全表扫描。

坑4：调参数瞎调

很多同学看了网上的优化文章，把一堆参数往自己服务器上套，结果把数据库搞出问题了。调参数一定要一点点调，调完观察效果，不要一下子改一堆参数。

坑5：不做监控

很多同学数据库出问题了才知道性能差，平时根本没监控。一定要做监控，比如慢查询数量、CPU使用率、连接数这些指标，提前发现问题。

总结

MySQL性能优化的思路和步骤：

1. 先定位问题：开慢查询日志，找到慢SQL
2. SQL和索引优化：加合适的索引，优化SQL写法，这是性价比最高的
3. 表结构优化：选合适的数据类型，大表归档，适当加冗余字段
4. 配置参数优化：重点调innodb_buffer_pool_size
5. 架构层面优化：读写分离、加缓存、分库分表
6. 最后才是加硬件：比如用SSD、加内存

记住：性能优化是一个系统工程，要从多个方面下手。不要指望一招就能解决所有问题，先从最简单、最容易见效的地方下手，比如加索引、优化SQL，然后再一步步深入。

遇到问题加QQ23979811 协助处理

前言

做数据库开发的同学都知道，索引是提升查询性能最有效的手段。很多时候一张几百万行的大表，加个合适的索引查询速度就能从几秒变成几毫秒。

但是很多同学不知道的是，就算你加了索引，如果SQL语句写得不对，索引也可能会失效，结果还是全表扫描，查询慢得要命。这篇文章就把MySQL索引失效的常见场景都整理出来，帮你避免踩坑。

常见索引失效场景

1. 对索引列使用函数或运算

这是最常见的索引失效场景。如果你在索引列上用了函数或者做了运算，MySQL就没法用到这个索引了。

失效的写法：

-- 对索引列使用函数
SELECT * FROM users WHERE DATE(create_time) = '2026-10-05';

-- 对索引列做运算
SELECT * FROM orders WHERE amount + 100 > 500;

正确的写法：

-- 改成范围查询
SELECT * FROM users 
WHERE create_time >= '2026-10-05 00:00:00' 
AND create_time < '2026-10-06 00:00:00';

-- 把运算移到右边
SELECT * FROM orders WHERE amount > 400;

2. 不满足最左前缀原则

如果你建的是联合索引（比如 index(a, b, c)），查询的时候必须从最左边的列开始用，否则索引就会失效。

联合索引：index(name, age, city)

失效的写法：

-- 没有用到最左列name
SELECT * FROM users WHERE age = 25;

-- 跳过了中间的列
SELECT * FROM users WHERE name = '张三' AND city = '北京';

正确的写法：

-- 从最左列开始
SELECT * FROM users WHERE name = '张三';

-- 按顺序用
SELECT * FROM users WHERE name = '张三' AND age = 25;

3. 使用like以%开头

如果你的查询条件是 like '%xxx' 或者 like '%xxx%'，那索引就会失效，因为MySQL不知道从哪里开始找。

失效的写法：

-- %在前面
SELECT * FROM users WHERE name LIKE '%张%';

-- %在前后都有
SELECT * FROM users WHERE name LIKE '%张';

正确的写法：

-- %在后面，这样可以用到索引
SELECT * FROM users WHERE name LIKE '张%';

如果一定要用前后模糊匹配的话，可以考虑全文索引或者ES搜索引擎。

4. 索引列使用 != 或 <>

如果查询条件用了 != 或者 <>，MySQL优化器会觉得大部分数据都要查，还不如直接全表扫描，所以索引就失效了。

失效的写法：

SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE status <> 1;

正确的写法：

-- 改成in或者or
SELECT * FROM users WHERE status IN (0, 2, 3);

5. 使用or连接条件

如果or两边的列不是都有索引，那整个查询就会全表扫描。

失效的写法：

-- name有索引，但是age没有索引
SELECT * FROM users WHERE name = '张三' OR age = 25;

正确的写法：

-- 用union all代替or
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE age = 25;

或者给age也加上索引，这样两边都能用索引。

6. 隐式类型转换

这是最坑的一个场景。如果你的索引列是字符串类型，但是查询的时候传了数字，MySQL会自动做类型转换，结果索引就失效了。

失效的写法：

-- 索引列phone是varchar类型，但是传了数字
SELECT * FROM users WHERE phone = 13800138000;

正确的写法：

-- 加引号，传字符串
SELECT * FROM users WHERE phone = '13800138000';

7. 使用not in、not exists

not in 和 not exists 也很容易导致索引失效。因为MySQL优化器对这类反查询的优化做得不太好。

失效的写法：

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

正确的写法：

-- 改成left join
SELECT u.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

8. 优化器认为全表扫描更快

还有一种情况是，虽然你写的SQL完全符合索引使用规则，但是MySQL优化器自己判断说"这张表数据太少了，全表扫描比走索引还快"，那它就会选择全表扫描。

这种情况是正常的，不用纠结。一般小表全表扫描确实更快。

如何检查索引是否生效

那怎么知道自己写的SQL有没有用到索引呢？很简单，用EXPLAIN关键字看执行计划就行。

EXPLAIN SELECT * FROM users WHERE name = '张三';

重点看这几个字段：

• type：如果是 ALL 就是全表扫描，说明索引失效了；如果是 ref、eq_ref、const 就说明用到索引了
• key：这里显示实际用到的索引名，如果是 NULL 说明没用到索引
• rows：扫描的行数，越大说明效率越低

常见坑

坑1：加了索引但是没生效，以为索引没用

很多同学加了索引之后发现查询还是慢，就觉得索引没用。其实大概率是你的SQL写法有问题，导致索引失效了。一定要先用EXPLAIN看一下执行计划，确认索引有没有被用到。

坑2：联合索引建了不用最左列

很多同学建了联合索引，但是查询的时候没用到最左边的列，结果索引白建了。一定要记住最左前缀原则。

坑3：隐式类型转换导致索引失效

这个最坑，因为写SQL的时候完全看不出来。比如手机号、身份证号这些字符串类型的字段，查询的时候一定要加引号，不然索引就失效了。

总结

MySQL索引失效的常见场景总结一下：

1. 对索引列使用函数或运算
2. 不满足最左前缀原则
3. like以%开头
4. 使用 != 或 <>
5. 使用or连接条件
6. 隐式类型转换
7. 使用not in、not exists
8. 表太小，优化器选择全表扫描

写SQL的时候一定要注意避开这些坑，写完之后用EXPLAIN检查一下索引有没有生效。这样你的数据库查询性能才能真正提上去。

遇到问题加QQ23979811 协助处理

MySQL 索引优化技巧

索引是数据库性能的关键，加对了查询快十倍，加错了反而更慢。

1. 索引类型

主键索引：主键自带索引，一张表只能一个。

普通索引：最基本的索引，加速查询。

CREATE INDEX idx_name ON user(name);

唯一索引：字段值不能重复。

CREATE UNIQUE INDEX idx_email ON user(email);

联合索引：多个字段组合的索引。

CREATE INDEX idx_name_age ON user(name, age);

2. 索引失效的情况

1）like 以 % 开头

-- 索引失效
SELECT * FROM user WHERE name LIKE '%张';

-- 索引生效
SELECT * FROM user WHERE name LIKE '张%';

2）对字段做函数运算

-- 索引失效
SELECT * FROM user WHERE LEFT(name,1) = '张';

-- 索引生效
SELECT * FROM user WHERE name LIKE '张%';

3）联合索引不满足最左前缀

联合索引 (name, age)：

-- 生效
SELECT * FROM user WHERE name = '张三';

-- 生效
SELECT * FROM user WHERE name = '张三' AND age = 25;

-- 失效
SELECT * FROM user WHERE age = 25;

4）隐式类型转换

-- phone 是 varchar
-- 索引失效
SELECT * FROM user WHERE phone = 13800001111;

-- 索引生效
SELECT * FROM user WHERE phone = '13800001111';

3. 查看索引使用情况

-- 查看执行计划
EXPLAIN SELECT * FROM user WHERE name = '张三';

-- 重点看 type 字段：
-- system > const > eq_ref > ref > range > index > ALL
-- 最差是 ALL（全表扫描）

4. 索引优化原则

• 为高频查询加索引：不要乱加
• 区分度高的字段优先：性别这种区分度低的不加
• 联合索引把最常用的放左边
• 索引不是越多越好：增删改会变慢
• 避免索引冗余：(a,b) 包含了 (a)

5. 慢查询排查

-- 开启慢查询
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

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

总结

MySQL 索引优化核心：

• 联合索引满足最左前缀
• like 不要以 % 开头
• 不要对字段做函数运算
• 用 EXPLAIN 看执行计划
• 慢查询找出来再优化

搞懂这些，数据库性能提升一大截。

MySQL 索引为什么能提速

索引是数据库性能优化的核心，搞懂原理才能用对。

1. 为什么慢查询慢

没有索引，MySQL 就得全表扫描，一行一行找。100万行数据就得扫100万次，当然慢。

有了索引，就像书有目录，直接定位到位置，查几百万行也很快。

2. B+ 树索引

MySQL InnoDB 默认用 B+ 树做索引。

• 树的高度低，一般3-4层
• 一次查询只需3-4次磁盘IO
• 叶子节点存数据，非叶子节点存索引

3. 聚簇索引 vs 二级索引

聚簇索引：数据和索引放在一起，按主键组织。一张表只有一个聚簇索引。

二级索引：索引和数据分开，叶子节点存主键值。查二级索引需要回表查主键。

4. 覆盖索引

如果查询的字段都在索引里，就不用回表了，叫覆盖索引。

-- name有索引
SELECT name FROM users WHERE name = '张三';
-- 覆盖索引，不用回表

5. 最左前缀

联合索引 (a, b, c)，必须从最左列开始用：

• WHERE a = 1 能用
• WHERE a = 1 AND b = 2 能用
• WHERE b = 2 用不了

6. 索引失效场景

• 索引字段做函数运算
• 隐式类型转换
• like 以 % 开头
• 联合索引不满足最左前缀

总结

索引是空间换时间。不是越多越好，太多索引会影响写入性能，占用额外存储空间。