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

总结

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