SQL查询优化与索引设计：手把手教你解决慢查询问题

怎么确认？用EXPLAIN看执行计划！比如你写了SELECT * FROM user WHERE age > 30;，跑一下EXPLAIN：
– 如果type是ALL，说明全表扫描，没用到索引；
– 如果key是NULL，肯定没走索引；
– 如果rows数值很大（比如10万+），说明扫描了太多数据。

举个真实例子：某电商系统的用户表有100万条数据，没建age索引时，查age>30的用户要2.3秒；建了索引后，只需要0.01秒——差距是230倍！

索引设计的3个黄金原则：不瞎建，建对才有用

索引不是越多越好（多了会拖慢插入/更新速度），得遵循“有用、高效、不冗余”的原则。

原则1：选择性高的字段优先建索引

选择性是指“字段不同值的数量/总记录数”——比如身份证号的选择性是1（每个值唯一），性别字段的选择性是0.5（只有男/女）。选择性越高，索引区分度越好，查得越快。

例子：给user表的id_card（身份证号）建索引有用，给gender（性别）建索引没用——因为查“性别=男”会扫50万条数据，和全表扫描差不多。

原则2：联合索引遵循“最左匹配”

联合索引是多个字段的组合（比如(age, name)），查询时必须从左到右匹配字段，否则索引失效。

正确用法：
– 查age>30（用左1字段）；
– 查age=30 AND name LIKE '张%'（用左1+左2字段）；
错误用法：
– 查name LIKE '张%'（跳过左1字段，索引失效）。

原则3：覆盖索引优先，减少回表

覆盖索引是指“查询的所有字段都在索引里”——比如查SELECT id, name FROM user WHERE age>30，如果建了(age, name, id)的联合索引，不用再回表查主键索引，直接从索引拿数据，速度翻倍。

对比：
– 没覆盖索引：查age>30→用age索引→回表查id和name→2次扫描；
– 有覆盖索引：查age>30→直接从(age, name, id)索引拿数据→1次扫描。

查询优化的4个实战技巧：写SQL时就避开慢坑

技巧1：拒绝SELECT *，只查需要的字段

SELECT *会把所有字段查出来，要么全表扫描，要么回表多次。改成查具体字段，刚好凑成覆盖索引，速度快很多。

例子：
– 慢：SELECT * FROM order WHERE create_time > '2025-01-01'（要回表查10个字段）；
– 快：SELECT order_id, user_id FROM order WHERE create_time > '2025-01-01'（建(create_time, order_id, user_id)索引，直接覆盖）。

技巧2：用JOIN代替子查询

子查询会生成临时表，JOIN是直接关联表，效率更高。

例子：
– 慢：SELECT * FROM order WHERE user_id IN (SELECT id FROM user WHERE age>30)（子查询生成临时表）；
– 快：SELECT o.* FROM order o JOIN user u ON o.user_id = u.id WHERE u.age>30（直接关联，用到user表的age索引）。

技巧3：别在索引字段上做运算

给索引字段用函数（比如DATE(create_time)）、加减乘除（比如age+1=31），会让索引失效——数据库无法直接用索引查，只能全表扫描。

错误→正确：
– 错：WHERE DATE(create_time) = '2025-08-24'（函数操作索引字段）；
– 对：WHERE create_time BETWEEN '2025-08-24 00:00:00' AND '2025-08-24 23:59:59'（用范围查询，用到create_time索引）。

技巧4：批量操作代替循环单条

插入/更新1000条数据，用INSERT INTO ... VALUES (a1,b1),(a2,b2)...比循环INSERT快10倍——因为减少了数据库连接次数和日志写入次数。

例子：
– 慢：循环1000次INSERT INTO user(name) VALUES ('张三')；
– 快：INSERT INTO user(name) VALUES ('张三'),('李四'),...（1次执行，1000条数据）。

避坑：90%的人会踩的索引失效雷区

雷区1：模糊查询以%开头

比如WHERE name LIKE '%张三'——索引是按“张→张三→张三丰”排序的，%开头意味着“不管前面是什么”，数据库无法用索引，只能全表扫描。改成LIKE '张三%'（固定前缀），就能用到索引。

雷区2：隐式类型转换

比如user表的id是INT类型，你写WHERE id='123'（字符串），数据库会隐式转成INT，但可能导致索引失效。直接写WHERE id=123（数字），稳用索引。

雷区3：用OR连接未索引的字段

比如WHERE age>30 OR gender='男'——如果gender没建索引，数据库会放弃age索引，直接全表扫描。要么给gender建索引，要么拆成两个查询用UNION。

雷区4：联合索引跳过左字段

比如建了(a,b,c)的联合索引，查WHERE b=2 AND c=3——跳过左1字段a，索引失效。必须包含左1字段（比如WHERE a=1 AND b=2）。

雷区5：索引字段用函数

比如WHERE UPPER(name) = 'ZHANGSAN'（把name转大写）——函数会破坏索引的排序，导致索引失效。改成WHERE name = 'zhangsan'（存数据时转小写，查的时候用小写）。

用EXPLAIN验证：你的优化有没有用？

优化后要不要看效果？用EXPLAIN跑一遍执行计划，看3个关键字段：
– type：从差到好是ALL（全表）→range（范围）→ref（索引查找）→eq_ref（唯一索引）；
– key：显示用到的索引（NULL就是没用到）；
– rows：扫描的行数（越少越好）。

例子对比：
– 优化前：EXPLAIN SELECT * FROM user WHERE age>30→type=ALL（全表）、rows=100000（扫10万条）；
– 优化后：EXPLAIN SELECT id,name FROM user WHERE age>30→type=range（范围）、key=idx_user_age_name（用了联合索引）、rows=1000（扫1000条）。

附：常用索引类型对比表

最后：优化是“迭代”，不是“一步到位”

没有完美的索引，只有适合业务的索引。比如初期用户少，age索引够用；用户到100万时，得加联合索引；业务变了，要定期删除无用索引（比如gender索引）。

小建议：每周跑一次slow_query_log（慢查询日志），找出top10的慢查询，用上面的方法优化——坚持1个月，数据库性能至少提升50%！