MySql--sql语句优化

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 0 本文字数： 1.6k 阅读时长 ≈ 6 分钟

摘要

常见面试题

一般优化原则

  • 查询类型要与字段类型匹配，否则不会使用索引，这里注意日期类型可以使用字符串比较

  • like '%xx%'不会使用索引，like 'xx%'会使用索引

  • in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

  • where条件左侧避免使用函数，否则不会使用索引

分页查询优化

1
select * from employees limit 90000,25;

  • 1.根据自增且连续的主键排序的分页查询，要求主键自增且连续

1
select * from employees where id > 90000 limit 25;

  • 2.根据非主键字段排序的分页查询(这种方式更加灵活)

1
select * from employees e inner join (select id from employees order by name limit 90000,25) ed on e.id = ed.id;

深分页为什么慢？怎么改？

场景：employees 表，主键 id 自增，另有字段 name。线上 SQL：

1
SELECT * FROM employees ORDER BY id LIMIT 90000, 25;

翻到越后面越慢，偶发超时。

1. 为什么深分页会慢？

  • LIMIT offset, n 的含义是：按排序取出前 offset + n 行，丢掉前面 offset 行，只返回最后 n 行。

  • 上面这条即：沿主键顺序扫描并准备 90025 行，再丢掉前 90000 行，真正返回 25 行。offset 越大，扫描/回表的行数越多，IO 和 CPU 线性变差。

  • SELECT * 还会放大代价：即便排序能走主键，也要为这 90025 行准备完整行数据（或大量回表），而真正有用的只有最后 25 行。

  • 所以慢的不是「只要 25 条」，而是「为了只要 25 条，先白做了 offset 那么多功」。

2. 主键自增且连续时怎么改？

1
SELECT * FROM employees WHERE id > 90000 LIMIT 25;

  • 用「上一页最后一条的 id」做游标（seek/keyset pagination），把「跳过 90000 行」变成「从 id>上一页最大值 开始往后取 25 行」，扫描量与页码无关。

  • 前提：主键自增且连续（或业务上能接受用「上一页最大 id」当游标）。中间有大量删除导致空洞时，id > 90000 表示的是「id 大于该值」，不是「第 90001 行」，和 LIMIT 90000,25 的「跳过 90000 条记录」不完全等价。

  • 坑：接口要从「传 pageNo」改成「传 lastId / 上一页游标」；不能随意跳到第 N 页，只适合「上一页 / 下一页」或无限下滑。需要跳页时仍得用别的办法（或接受深 offset）。

3. 必须按非主键（如 name）排序时？

文中推荐先用覆盖索引只取主键，再回表拿整行：

1
2
3
4
SELECT * FROM employees e
INNER JOIN (
  SELECT id FROM employees ORDER BY name LIMIT 90000, 25
) ed ON e.id = ed.id;

  • 子查询只查 id，排序过程尽量落在索引上，避免对宽行做 filesort / 大结果集回表。

  • 外层用 25 个 id 回表取 *，回表次数从「约 offset+n」降到「约 n」。

  • 注意：子查询里的 LIMIT 90000,25 仍然要扫过 90025 个索引项，深分页的「跳过」成本还在，只是比 SELECT * ... LIMIT 90000,25 轻很多。极深页仍可考虑「记下上一页最后的 (name, id) 做联合游标」继续优化。

4. 追问：WHERE 左侧套函数、或 LIKE '%xx%'，和分页慢叠加时先改哪？

  • 优先改条件，让查询能走索引缩小范围，再谈分页写法。深分页优化建立在「有序扫描 / 索引定位」之上；条件本身就全表扫，改成 id > ? 也救不了。

  • WHERE YEAR(create_time) = 2024、WHERE UPPER(name) = 'XX'：函数加在列上，索引失效。改成范围或等值比较列本身，如 create_time >= '2024-01-01' AND create_time < '2025-01-01'；必须按函数查再用函数索引（见 MySql索引）。

  • LIKE '%xx%'：左模糊无法用 B+Tree 正常定位，往往全表扫；LIKE 'xx%' 才可能走索引。必须左右模糊时考虑 ES / 全文，不要指望普通索引 + 深分页硬扛。

  • 顺序建议：先 EXPLAIN 看 type/key → 改掉导致 ALL 的条件 → 再上主键游标或「先 id 后回表」的分页。

面试可背：LIMIT 大offset 会扫 offset+n 行再丢弃；主键连续用 WHERE id > lastId LIMIT n；非主键排序先查 id 再 join 回表；条件先能走索引，再优化分页。

order by 与 group by 优化

  • group by 与``order by` 很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则

  • order by 与 group by 字段尽量与 select 和 where 中的字段组合为联合索引，即覆盖索引，避免文件排序和回表

  • where 高于 having，能写在 where 中的限定条件就不要去 having 限定了

in和exsits优化，小表驱动大表

  • 1.当B表的数据集小于A表的数据集时，in优于exists

1
select * from A where id in (select id from B);

  • 2.当A表的数据集小于B表的数据集时，exists优于in

1
select * from A where exists (select 1 from B where B.id = A.id)

count(*)查询优化

  • count(*)≈count(1)>count(索引字段)>count(主键id)

多表关联语句优化

  • 不要超过3张表，关联表越多查询效率越低

  • mysql优化器一般会优先选择小表做驱动表，所以使用 inner join 时，排在前面的表并不一定就是驱动表。

  • 当使用 left join 时，左表是驱动表，右表是被驱动表，当使用 right join 时，右表时驱动表，左表是被驱动表， 当使用 join 时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。

  • 多表关联字段一定要创建索引

  • 在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。