MySQL连接查询用法

引言

业务数据分散在用户表、订单表、商品表中，要一次性取出用户姓名加订单金额，就要用JOIN把多张表按关联字段拼起来。分清内连接和外连接，是写对多表查询的关键。

内连接 INNER JOIN

只保留两表匹配上的行：

SELECT u.name, o.amount, o.order_no
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

没有订单的用户不会出现，没有对应用户的订单也不会出现。

左连接 LEFT JOIN

以左表为主，左表全部保留，右表匹配不上补NULL：

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

每个用户都列出来，没下单的用户amount显示NULL，常用于统计哪些用户没购买。

右连接 RIGHT JOIN

以右表为主，和左连接方向相反，实际项目多数统一用LEFT JOIN，可读性更好。

多表连接示例

SELECT u.name, p.title, o.amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.status = 1
ORDER BY o.create_time DESC;

注意事项

• 关联字段类型要一致，避免隐式转换导致索引失效；
• ON写连接条件，WHERE过滤结果，别把连接条件误放WHERE导致LEFT JOIN变内连接；
• 大表JOIN先确认关联字段有索引；
• 多表查询用表别名，字段加别名前缀避免歧义；
• 不建议无关联条件的多表笛卡尔积。

总结

INNER JOIN取交集，LEFT JOIN保留左表全部。写多表查询先确定以哪张表为主，连接条件放ON，过滤条件放WHERE，关联字段建索引，避免隐式转换和笛卡尔积。

MySQL慢查询定位

引言

网站访问缓慢很多根源是执行时间过长的SQL。开启MySQL慢查询日志，可以捕获执行超时SQL，找到需要优化的语句，是数据库性能排查的核心手段。

慢查询基础配置

慢查询日志会记录执行时长超过指定阈值的SQL语句。

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

-- 设置阈值，单位秒，超过1秒就记录
SET GLOBAL long_query_time =1;

常用分析工具

1. mysqldumpslow：mysql自带工具，统计慢日志

   mysqldumpslow -s t /var/lib/mysql/slow.log

2. pt-query-digest：percona工具，分析效果更强，适合大量日志。

优化排查流程

1. 开启慢查询，收集慢SQL；
2. 使用explain查看执行计划，判断是否缺少索引；
3. 优化where条件，添加合适索引，改写SQL逻辑；
4. 优化完成后，持续观察慢日志确认效果。

示例

EXPLAIN SELECT * FROM article WHERE title like '%PHP%';

看type字段，ALL代表全表扫描，是需要优化的信号。

总结

慢查询日志用来抓取低效SQL，但是开启会少量增加服务器IO压力，生产环境可以按需开启。定位到慢SQL后优先检查索引，再调整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 协助处理

前言

网站突然变慢，不知道哪条SQL拖后腿。开慢查询日志，把跑得慢的SQL都抓出来优化。

一、什么是慢查询日志

慢查询日志（slow query log）记录执行时间超过阈值的SQL语句。

作用： 找到慢SQL，优化它们。

二、查看是否开启

SHOW VARIABLES LIKE 'slow_query_log';

OFF就是没开。

三、开启慢查询日志

方法1：临时开启（重启失效）

-- 开启
SET GLOBAL slow_query_log = ON;

-- 设置阈值（超过2秒算慢）
SET GLOBAL long_query_time = 2;

方法2：永久开启

改my.cnf：

[mysqld]
# 开启慢查询
slow_query_log = 1

# 慢查询阈值（秒）
long_query_time = 2

# 慢查询日志文件位置
slow_query_log_file = /var/log/mysql/slow.log

# 没走索引的SQL也记下来（可选）
log_queries_not_using_indexes = 1

重启MySQL：

systemctl restart mysqld

四、查看慢查询日志

直接看文件

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

用mysqldumpslow分析

MySQL自带的分析工具：

# 按查询时间排序，取前10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

常用参数：

示例：

# 按次数最多的排
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log

# 只看select语句
mysqldumpslow -g "select" /var/log/mysql/slow.log

五、用pt-query-digest分析

更专业的分析工具（需要安装）：

pt-query-digest /var/log/mysql/slow.log

输出更详细的统计报告。

六、常用参数说明

七、优化慢SQL

找到慢SQL后怎么优化：

1. 加索引

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

看type是不是ALL，是ALL说明没走索引。

2. 避免select *

只查需要的字段：

-- 别这样
SELECT * FROM users;

-- 这样
SELECT id, name FROM users;

3. 避免大表全表扫描

加where条件，加limit。

4. 优化分页

大表分页越查越慢，用游标分页。

常见坑

坑1：long_query_time设太小

设成0.1秒，日志文件爆炸，全是慢SQL。

建议： 生产环境设1-2秒。

坑2：日志文件越来越大

慢查询日志一直写，磁盘满了。

解决： 用logrotate切割日志。

坑3：开启慢查询影响性能

开慢查询本身有性能损耗。

解决： 排查完就关掉，或者用低采样率。

坑4：long_query_time不生效

改了参数，但还是记录所有SQL。

原因： 连接没断开，用的还是旧参数。

解决： 重新连接MySQL。

坑5：log_output=TABLE占满系统表

把慢查询写到mysql.slow_log表，表越来越大。

解决： 定期清理：

TRUNCATE TABLE mysql.slow_log;

总结

开启三步：

1. SET GLOBAL slow_query_log = ON
2. SET GLOBAL long_query_time = 2
3. 看日志：mysqldumpslow -s t -t 10 slow.log

记住： 慢查询日志是优化数据库的第一步，先找到慢SQL再优化。

遇到问题加QQ23979811 协助处理

前言

SQL查得慢，不知道为什么慢。用EXPLAIN看MySQL怎么执行这条SQL，就能找到问题在哪。

一、EXPLAIN怎么用

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

输出很多列，重点看这几个：id、select_type、table、type、key、rows、Extra。

二、重点列详解

1. type（访问类型）

最重要的一列，看SQL查得快不快。

从好到差：

目标： 至少达到range，最好是ref以上。

如果type是ALL，就是全表扫描，必须优化。

2. key（实际用到的索引）

key: NULL

说明没用索引。

key: idx_name

说明用到了idx_name索引。

3. rows（扫描行数）

MySQL预估要扫描多少行。

rows越大，越慢。

4. Extra（额外信息）

常见的：

• Using where：WHERE过滤
• Using index：覆盖索引，不用回表，好
• Using filesort：需要额外排序，不好
• Using temporary：用了临时表，不好

三、常见问题

问题1：type=ALL，全表扫描

原因： 没加索引，或者索引失效。

解决： 给WHERE条件的字段加索引。

问题2：key=NULL，没用索引

原因：

1. 没建索引
2. 索引失效了（函数、类型转换、前导模糊查询等）

解决： 建正确的索引，避免索引失效写法。

问题3：Using filesort

原因： ORDER BY的字段没走索引。

解决： 给ORDER BY的字段加索引。

问题4：Using temporary

原因： GROUP BY或DISTINCT用了临时表。

解决： 给GROUP BY的字段加索引。

四、实战案例

慢SQL

SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC LIMIT 10;

EXPLAIN结果：

问题：

1. type=ALL，全表扫描
2. key=NULL，没用索引
3. rows=100000，扫了10万行
4. Using filesort，还要额外排序

优化

加联合索引：

ALTER TABLE orders ADD INDEX idx_userid_createtime (user_id, create_time);

再EXPLAIN：

优化效果：

1. type变成ref
2. 用到了索引
3. rows从10万变成10
4. 没有filesort了

五、常用优化技巧

1. 避免SELECT *

只查需要的列，能用上覆盖索引。

2. WHERE条件加索引

经常查询的字段加索引。

3. 联合索引最左前缀

联合索引 (a,b,c)，查询条件要从最左列开始。

4. 避免索引失效

• 不要对索引字段用函数
• 不要隐式类型转换
• 不要前导模糊查询 like '%xxx'

总结

EXPLAIN看这三点：

1. type：是不是全表扫描
2. key：有没有用到索引
3. rows：扫描了多少行

记住： 慢SQL先EXPLAIN，找到问题再优化。

遇到问题加QQ23979811 协助处理

前言

大表分页查询，第一页很快，第十页就慢，第一百页直接卡死。这是MySQL分页的经典问题，本文讲怎么优化。

为什么越往后越慢

普通分页：

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

MySQL 会先扫描前100010条，然后丢掉前100000条，只返回最后10条。

越往后翻，扫描的行越多，当然越慢。

优化方案一：子查询优化

先查出起始位置的id，再用id做条件：

SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 100000, 1)
LIMIT 10;

原理： 子查询走索引，只查id列，很快。然后外层用id直接定位，不用扫那么多行。

优化方案二：游标分页（推荐）

记住上一页最后一条的id，下次从这个id之后查：

-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 10;

-- 第二页（假设上一页最后id是100）
SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;

-- 第三页（假设上一页最后id是110）
SELECT * FROM orders WHERE id > 110 ORDER BY id LIMIT 10;

优点： 不管翻到第几页，都是直接定位，速度一样快。

缺点： 不能跳页，只能上一页下一页。

优化方案三：覆盖索引

只查需要的列，不要 SELECT *：

SELECT id, title, create_time FROM orders ORDER BY id LIMIT 100000, 10;

如果查询的列都在索引里，MySQL直接从索引返回数据，不用回表，快很多。

优化方案四：业务上限制深分页

根本解决： 不让用户翻到那么深。

1. 搜索结果只显示前100页
2. 用条件过滤，不要全表翻
3. 用搜索引擎（ES）代替深分页

常见坑

坑1：用了offset就一定慢

其实offset小的时候（比如前10页），差别不大。

结论： 数据量不大的时候，普通分页就行，不用过度优化。

坑2：游标分页不能用排序

游标分页只能按主键或唯一索引排序。

解决： 如果按其他字段排序，先查出对应的id列表，再用id定位。

坑3：子查询效率也不高

子查询在MySQL 5.6以前有优化器问题。

解决： MySQL 5.6以后子查询优化得很好了，放心用。

坑4：没加where条件

深分页没加where，全表扫描。

解决： 尽量加上时间范围、状态等过滤条件。

性能对比

总结

大表分页优化三板斧：

1. 小数据量：普通分页就行
2. 中等数据量：子查询优化
3. 大数据量：游标分页 + 搜索引擎

记住： 深分页不是MySQL擅长的事，数据量大了就上ES。

遇到问题加QQ23979811 协助处理

前言

网站越来越慢，不知道哪条 SQL 拖后腿。MySQL 自带慢查询日志功能，能把执行慢的 SQL 记录下来，方便优化。

什么是慢查询

就是执行时间超过某个阈值的 SQL 语句。默认阈值是 10 秒，太久了，一般改成 1 秒或者 2 秒。

临时开启（重启不丢）

登录 MySQL：

mysql -u root -p

开启慢查询：

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

-- 设置超过1秒就记录
SET GLOBAL long_query_time = 1;

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

永久开启（改配置文件）

改 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

慢查询日志长什么样

# Time: 2026-10-07T07:00:00.000000Z
# User@Host: root[root] @ localhost []  Id:  1234
# Query_time: 5.234567  Lock_time: 0.000123 Rows_sent: 1000  Rows_examined: 1000000
SET timestamp=1728270000;
SELECT * FROM users WHERE name LIKE '%张%';

重点看：

• Query_time：执行时间
• Rows_examined：扫描了多少行
• 最后一行就是慢 SQL 语句

分析慢查询日志

用 mysqldumpslow 工具：

# 找出最慢的10条SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 找出返回数据最多的10条
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

参数说明：

常见坑

坑1：没开启慢查询

不知道怎么开，或者开了忘了。

解决： 用 SHOW VARIABLES LIKE 'slow_query_log'; 确认一下。

坑2：阈值设太大

默认是 10 秒，10 秒以下的慢 SQL 都没记录到。

解决： 改成 1 秒，够用了。

坑3：日志太大占磁盘

慢查询日志一直写，磁盘满了。

解决： 配 logrotate 定期切割。

坑4：没记录没走索引的SQL

有些 SQL 虽然快，但没走索引，数据量一大就慢了。

解决： 加上这个参数，没走索引的也记录：

log_queries_not_using_indexes = 1

总结

三步开启慢查询：

1. 开 slow_query_log
2. 设 long_query_time
3. 用 mysqldumpslow 分析

记住： 慢查询日志是优化数据库的第一步，先开了再说。

遇到问题加QQ23979811 协助处理

前言

做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的explain命令了。explain可以告诉你MySQL是怎么执行这条SQL的，走了哪个索引，扫了多少行，是不是全表扫描。搞懂了explain，你就能快速定位SQL慢的原因，然后针对性优化。

很多新手同学不知道explain怎么用，也不知道每一列是什么意思。这篇文章就把explain的知识点从头到尾讲清楚，看完之后你就能看懂执行计划了。

一、explain是什么

explain就是MySQL提供的一个分析工具。你在SELECT语句前面加上EXPLAIN，MySQL就不会真的执行这条SQL，而是告诉你它准备怎么执行这条SQL。

explain能告诉你什么

• 这条SQL有没有用到索引
• 扫了多少行数据
• 有没有全表扫描
• 用的是哪个索引
• 表连接的顺序是什么

二、怎么用explain

用法很简单，就在你的SELECT语句前面加上EXPLAIN就行：

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

执行完之后，MySQL会返回一行结果，每一列都代表不同的信息。

三、explain的各列详解

explain返回的结果有很多列，我们重点讲几个常用的。

1. id

这一列表示SELECT的序号。如果有多个SELECT（比如子查询），id越大的越先执行。

2. select_type

这一列表示查询的类型，常见的有：

• SIMPLE：简单查询，没有子查询或者UNION
• PRIMARY：最外层的查询
• SUBQUERY：子查询里的第一个SELECT
• DERIVED：派生表（FROM子句里的子查询）
• UNION：UNION里的第二个及以后的SELECT

3. table

这一列表示当前这一行是在查哪张表。

4. type

这一列非常重要，表示MySQL是怎么找到数据的，也就是访问类型。从好到差依次是：

• system：表里只有一行数据，最好的情况
• const：通过索引一次就找到了，比如WHERE id=1，id是主键
• eq_ref：联表查询的时候，用主键或者唯一索引关联，最多匹配一行
• ref：用普通索引关联，可能匹配多行
• range：索引范围扫描，比如WHERE id BETWEEN 1 AND 10
• index：扫描整个索引树，比全表扫描好一点
• ALL：全表扫描，最差的情况，一定要优化

重点关注： 如果type是ALL或者index，说明这条SQL性能很差，要赶紧优化。

5. possible_keys

这一列表示理论上可能用到的索引。如果是NULL，说明没有用到索引。

6. key

这一列表示实际用到的索引。如果是NULL，说明真的没用到索引。

重点关注： 如果possible_keys有值，但是key是NULL，说明MySQL没有选择用索引，可能是数据量太小，或者索引失效了。

7. key_len

这一列表示用了索引的长度。可以用来判断用了联合索引的前几个字段。

8. ref

这一列表示用了哪个常量或者列来和索引做比较。

9. rows

这一列表示MySQL估计要扫多少行才能找到需要的数据。这个值越大，说明要扫的数据越多，性能越差。

10. Extra

这一列非常重要，包含了很多额外的信息，常见的有：

• Using where：服务器在存储引擎返回数据之后，又做了一层WHERE过滤
• Using index：用了覆盖索引，不需要回表，性能很好
• Using temporary：用了临时表，一般是GROUP BY或者ORDER BY的时候没有用到索引
• Using filesort：用了文件排序，ORDER BY没有用到索引，性能差
• Using join buffer：联表的时候用了缓冲，说明联表的字段没有索引
• Impossible WHERE：WHERE条件永远是false，根本查不到数据

重点关注： 如果Extra里出现了Using temporary或者Using filesort，说明SQL性能有问题，要优化。

四、重点看哪几列

很多同学看explain结果的时候，这么多列不知道重点看什么。其实重点看这几个：

1. type：有没有全表扫描（ALL）
2. key：有没有用到索引
3. rows：扫了多少行
4. Extra：有没有Using filesort或者Using temporary

只要这几个都没问题，这条SQL的性能一般就不会太差。

五、举个例子

比如我们执行这条SQL：

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;

执行完之后看到：

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | orders | ref  | user_id       | user_id | 4       | const |  100 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

我们来分析一下：

• type是ref：说明用了普通索引，还不错
• possible_keys是user_id：理论上可以用user_id索引
• key是user_id：实际用了user_id索引
• rows是100：估计要扫100行
• Extra是Using where：说明返回之后又做了一层过滤

从这个结果来看，这条SQL用了索引，性能还可以。但是status字段没有用到索引，如果status过滤性好的话，可以考虑建一个(user_id, status)的联合索引。

六、explain的扩展用法

除了基本的explain，还有几个扩展的用法：

EXPLAIN PARTITIONS

看这条SQL会不会命中分区表：

EXPLAIN PARTITIONS SELECT * FROM orders WHERE ...;

EXPLAIN FORMAT=JSON

输出JSON格式的执行计划，信息更详细：

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE ...;

常见坑

坑1：看到type是const就以为没问题

很多同学看到type是const就以为这条SQL性能很好。但是如果rows很大，或者Extra里有Using filesort，性能还是可能有问题。不能只看type，要综合看。

坑2：以为possible_keys有值就一定会用索引

很多同学看到possible_keys有值，就以为这条SQL一定会用到索引。其实不是，possible_keys只是说理论上可能用到，实际用不用要看key列。如果key是NULL，说明MySQL最后没有用这个索引。

坑3：rows是精确值

很多同学以为rows列是精确的行数，其实不是。rows是MySQL估计出来的行数，不是精确值。但是大致能反映出扫描的数据量大小。

坑4：以为Using filesort就是用了文件排序

很多同学看到Extra里有Using filesort就以为是真的用磁盘文件排序。其实不是，Using filesort只是说明MySQL做了额外的排序操作，不一定是用磁盘，数据量小的时候也可能在内存里排。

坑5：只看单表的explain，不看联表的

很多同学优化SQL的时候，只看单表的执行计划，不看联表的。其实联表的时候，表的连接顺序对性能影响很大。explain会按表的连接顺序一行一行列出来，要按顺序看。

总结

MySQL explain执行计划的核心知识点：

1. explain的作用：看MySQL是怎么执行SQL的，不用真的执行
2. type列最重要：从好到差是system > const > eq_ref > ref > range > index > ALL，出现ALL就是全表扫描
3. key列：实际用到的索引，NULL就是没用到索引
4. rows列：估计要扫多少行，越大越慢
5. Extra列：重点关注Using filesort和Using temporary，出现这两个就要优化
6. 重点看type、key、rows、Extra这四列，其他的了解就行
7. rows是估计值，不是精确的，但是能反映大致的数据量

explain是分析SQL性能的必备工具，不管是面试还是实际开发，都是必须掌握的。以后遇到SQL慢，先explain一下，看看是哪里出了问题。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定遇到过这种情况：数据库里有一张几百万行甚至上千万行的大表，做分页查询的时候，第一页第二页查得很快，但是翻到第100页、第1000页的时候，查询就变得特别慢，甚至直接卡死。

这是为什么呢？怎么优化才能让大表分页查询不卡呢？这篇文章就详细讲讲MySQL大表分页越查越慢的原因，以及对应的优化方案。

为什么分页会越查越慢

先看一下最常见的分页查询写法：

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

这个SQL的意思是：从 orders 表里，跳过100000条数据，取后面10条。

很多同学以为这个查询很快就能出来，其实不是的。MySQL执行这个SQL的时候，会先扫描前100010条数据，然后把前面的100000条扔掉，只返回最后10条。

也就是说，你翻到第1000页的时候，MySQL要先扫描100000多条数据，然后再扔掉。翻的页数越深，MySQL要扫描和扔掉的数据就越多，所以查询就会越来越慢。

优化方案

方案1：子查询优化（延迟关联）

这是最常用的优化方案。思路就是：先用索引把需要的id查出来，然后再根据id去查完整的数据。

原来的慢查询：

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

优化后的写法：

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

这样优化的好处是：子查询里的 SELECT id FROM orders ORDER BY id LIMIT 100000, 10 只需要扫描索引就能完成，不需要回表查完整数据，速度会快很多。然后再根据查出来的10个id，去关联完整的表数据，只需要10次回表操作就行。

这种优化方式也叫"延迟关联"，是大表分页最常用的优化手段。

方案2：书签分页（记住上一页最后一条id）

如果你的业务场景允许（比如只允许"上一页"和"下一页"，不允许直接跳页），那用书签分页是最快的。

思路就是：记住上一页最后一条数据的id，下一页直接从这个id后面开始查。

第一页：

SELECT * FROM orders ORDER BY id LIMIT 10;

假设第一页最后一条数据的id是100。

第二页：

SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;

这样不管你翻到第几页，查询速度都是一样快的，因为每次都是从某个id开始往后查10条，MySQL不需要扫描前面的几十万条数据。

这种分页方式的缺点是不支持直接跳页，只能一页一页往后翻。很多信息流产品（比如朋友圈、抖音）都是用这种分页方式。

方案3：覆盖索引

如果你查询的字段刚好都在索引里，那MySQL就不需要回表查完整数据了，速度会快很多。

比如你只需要查 id, title, create_time 这几个字段，那你可以建一个联合索引：

CREATE INDEX idx_title_time ON orders(title, create_time);

然后查询的时候只查这几个字段：

SELECT id, title, create_time FROM orders ORDER BY create_time LIMIT 100000, 10;

这样MySQL直接从索引里就能拿到所有需要的数据，不需要回表，查询速度会快很多。

方案4：禁止跳页查询

如果你的业务场景允许，可以考虑禁止用户直接跳页。比如只允许用户看前100页，或者只允许"上一页"和"下一页"。

因为翻到第1000页、第10000页的用户其实很少，大部分用户都是看前几页。禁止跳页可以避免大量深分页查询拖慢数据库。

常见坑

坑1：用LIMIT offset的时候，offset越大越慢

很多同学不知道LIMIT offset的原理，以为MySQL直接就跳过了前面的offset条数据。其实不是的，MySQL是先把前面的offset条数据都查出来，然后再扔掉。所以offset越大，查询越慢。

坑2：优化了子查询，但是子查询里没有用到索引

如果你的ORDER BY字段没有建索引，那就算用了子查询优化，还是会很慢。一定要确保ORDER BY的字段有索引。

坑3：书签分页的时候，id不是连续的

书签分页的前提是你的id是连续的、递增的。如果你的业务场景里id可能会被删除，导致id不连续，那书签分页可能会漏数据。这时候需要根据实际情况调整分页策略。

坑4：用count(*)查总页数很慢

大表查总页数也很慢，比如 SELECT COUNT(*) FROM orders，几百万行的表可能要查好几秒。

这个优化方案是：

1. 如果业务不需要精确的总页数，可以用"大约XX万条"来代替
2. 或者单独建一张表存总条数，定期更新
3. 或者用explain估算一下大概的行数

总结

MySQL大表分页越查越慢的问题，核心原因就是LIMIT offset会先扫描offset条数据然后扔掉，offset越大越慢。

常用的优化方案：

1. 子查询优化（延迟关联）：先查id，再关联完整数据
2. 书签分页：记住上一页最后一条id，下一页从这个id开始查
3. 覆盖索引：只查索引里有的字段，避免回表
4. 禁止跳页：不允许直接翻到第1000页

根据你的业务场景选择合适的优化方案，大表分页查询慢的问题就能很好地解决了。

遇到问题加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 协助处理

前言

做网站开发的同学肯定遇到过这种情况：网站打开越来越慢，数据库CPU占用很高，但就是不知道哪条SQL语句拖慢了整个系统。这时候就需要用到MySQL的慢查询日志功能，把执行慢的SQL语句记录下来，然后逐个优化。

这篇文章就详细讲讲如何开启MySQL慢查询日志，以及怎么用它来定位和优化慢SQL语句。

操作步骤

1. 先查看当前慢查询日志的状态

登录MySQL之后，先看看慢查询日志有没有开启：

SHOW VARIABLES LIKE '%slow_query_log%';

执行完之后会看到类似下面的输出：

+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | OFF                                           |
| slow_query_log_file | /var/lib/mysql/izuf6w7x1x2x3x4x5x6x-slow.log  |
+---------------------+-----------------------------------------------+

从这里可以看到，slow_query_log 是 OFF 状态，说明慢查询日志没有开启。

2. 查看当前慢查询时间阈值

慢查询的默认时间阈值是10秒，也就是说执行时间超过10秒的SQL才会被记录。这个阈值对生产环境来说太大了，一般我们会改成1秒或者2秒。

查看当前阈值：

SHOW VARIABLES LIKE 'long_query_time';

3. 临时开启慢查询日志（重启后失效）

如果只是想临时排查问题，可以直接在MySQL命令行里开启，不需要重启服务：

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

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

这样设置之后，执行时间超过1秒的SQL语句就会被记录到慢查询日志文件里了。

4. 永久开启慢查询日志（修改配置文件）

临时开启的方式在MySQL重启之后就会失效，如果想长期开启，需要修改MySQL的配置文件。

找到MySQL的配置文件，一般是 /etc/my.cnf 或者 /etc/mysql/my.cnf，在 [mysqld] 下面添加以下配置：

[mysqld]
# 开启慢查询日志
slow_query_log = 1
# 慢查询日志文件位置
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询阈值，单位秒
long_query_time = 1
# 没有用到索引的SQL也记录下来
log_queries_not_using_indexes = 1

修改完配置文件之后，重启MySQL服务：

systemctl restart mysqld

5. 查看慢查询日志内容

慢查询日志开启之后，执行慢的SQL语句就会被记录到日志文件里。我们可以直接查看日志文件：

cat /var/log/mysql/slow.log

不过慢查询日志的格式比较复杂，直接看日志文件不太直观。我们可以用 mysqldumpslow 工具来分析：

# 查看最慢的10条SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 查看访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

6. 使用EXPLAIN分析慢SQL

找到慢SQL之后，下一步就是分析这条SQL为什么慢。我们可以用 EXPLAIN 关键字来查看SQL的执行计划：

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

执行完之后会输出很多字段，我们重点关注这几个：

• type：访问类型，最好是 const、eq_ref，最差是 ALL（全表扫描）
• key：实际用到的索引，如果是 NULL 说明没用到索引
• rows：扫描的行数，这个值越大越慢
• Extra：额外信息，如果看到 Using filesort 或者 Using temporary 就说明需要优化了

常见坑

坑1：开启了慢查询但日志文件里什么都没有

很多同学开启了慢查询日志，但是去看日志文件却发现什么都没有。这通常是因为以下几个原因：

1. 慢查询时间阈值设置得太大了，你的SQL执行时间还没超过阈值
2. 日志文件的路径不对，你看的不是正确的日志文件
3. MySQL没有权限写入日志文件，需要检查文件权限

坑2：long_query_time设置了但不生效

有时候你设置了 long_query_time = 1，但是发现执行时间0.5秒的SQL也被记录下来了。这是因为当前已经建立的MySQL连接还是用的旧的阈值，需要重新连接MySQL才能生效。

坑3：慢查询日志文件太大占满磁盘

如果你的网站访问量很大，慢查询日志可能会增长得非常快，几天就把磁盘占满了。所以一定要配置日志切割，或者定期清理慢查询日志文件。

总结

MySQL慢查询日志是定位数据库性能问题的必备工具，核心就三步：

1. 开启慢查询日志，设置合理的时间阈值（一般1秒）
2. 用mysqldumpslow工具分析慢查询日志，找到最慢的SQL
3. 用EXPLAIN分析慢SQL的执行计划，针对性优化（加索引、改写SQL等）

掌握了慢查询日志的使用方法，网站数据库性能问题就能快速定位和解决了。

遇到问题加QQ23979811 协助处理