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. 开启慢查询日志

先开启慢查询日志，找出哪些 SQL 慢：

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

超过1秒的查询就会被记录。

2. 用 Explain 分析

慢 SQL 拿到后，用 EXPLAIN 分析执行计划：

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

重点看：

• type：访问类型，ALL 是全表扫描，要优化
• key：实际用到的索引
• rows：扫描行数
• Extra：额外信息，Using filesort、Using temporary 要警惕

3. 加索引

最直接的优化就是加索引：

ALTER TABLE users ADD INDEX idx_name(name);

加索引原则：

• WHERE、JOIN、ORDER BY 字段优先
• 区分度高的字段效果好
• 联合索引遵循最左前缀

4. 避免 SELECT *

只查需要的字段：

-- 不好
SELECT * FROM users WHERE id = 1;

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

5. 深分页优化

LIMIT 100000, 10 很慢，因为要扫描10万行：

-- 不好
SELECT * FROM users LIMIT 100000, 10;

-- 好，用子查询
SELECT * FROM users 
WHERE id >= (SELECT id FROM users LIMIT 100000, 1)
LIMIT 10;

6. 大表拆小

单表数据量超过千万行，性能会下降。可以：

• 按时间分表
• 按用户ID分库
• 归档历史数据

7. 总结

慢查询优化的核心思路：先找慢 SQL，再分析执行计划，然后加索引或改写 SQL。优化完记得对比前后效果，确保真的变快了。

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 以 % 开头
• 联合索引不满足最左前缀

总结

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