MySQL慢SQL优化方案汇总

⛰️个人主页:     蒾酒

🔥系列专栏：《mysql经验总结》

🌊山高路远，行路漫漫，终有归途

目录

写在前面

优化思路

避免查询不必要的列

分页优化

索引优化

JOIN优化

排序优化

UNION 优化

写在最后

写在前面

本文介绍了MySQL常见的优化慢sql的手段，坚持看完相信对你有帮助。

同时欢迎订阅MySQL系列专栏，持续分享MySQL的使用经验。

优化思路

慢sql的优化无非是从两个方向着手

• SQL语句本身的优化
• 据库设计的优化

  下面进行渐进式的分享一些常见优化手段

  避免查询不必要的列

  查询应该精准的查出需要的列，对于select * 的写法要避免，因为所有字段查出来不仅sql查询执行慢，若是直接返回给前端，大量的数据也会影响网络传输效率。

  分页优化

  对于数据量特别大，这时分页会比较深，查询扫描的数据量会比较大效率自然低，我们就需要进行分页优化

  假设我们有一个包含大量订单记录的订单表，其中每个订单都有一个唯一的不包含业务逻辑的主键，并且我们想要查询最近一个月的订单并按照订单id从小到大进行分页显示某页。

  假设出现深分页的sql如下：

  select * from orders where order_date >= date_sub(now(), interval 1 month)
   order by id limit 100000, 10;

  执行此SQL时需要先扫描到100000行，然后再去取10行，但是随着扫描的记录数越多，SQL的性能就会越差，因为扫描的记录越多，MySQL需要扫描越多的数据来定位到具体的多少行，这样耗费大量的 IO 成本和时间成本。

  对于解决该深分页问题通常有两种方法

  1.延迟关联

  先通过 where 条件提取出主键，在将该表与原数据表关联，通过主键 id 提取数据行，而不是通过原来的二级索引提取数据行

  优化后sql:

  select o.*
  from (
      select id
      from orders
      where order_date >= date_sub(now(), interval 1 month)
      order by id
      limit 100000, 10
  ) as sub
  join orders as o on sub.id = o.id;
  

  优化后SQL中的子查询只取主键id，可避免通过二级索引中的主键去回表查询，这样性能会快一些。

  2.id偏移量

  偏移量就是找到 limit 第一个参数对应的主键值，根据这个主键值再去过滤并 limit，这种方法又称为基于游标的分页。基于游标的分页的前提是需要保证主键或排序列的连续性、唯一性。

  优化后sql:

  select *
  from orders
  where id >= (select id from orders order by id limit 100000, 1)
  order by id
  limit 10;

  这种方法相对于原来直接使用偏移量和限制结果数量的方式，可以在大数据集上提供更稳定和一致的性能，因为它不需要扫描和跳过大量的行。

  索引优化

  通过合理的设计和使用索引，能够有效优化sql性能，这也是我们使用最多的手段。

  下面介绍一下如何进行索引优化：

  使用覆盖索引

  InnoDB使用二级索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引，还有一个简单的理解查询列都是索引列。

  示例：

  select product_name, price
  from products
  where category_id = 1;
  

  create index idx_category_id on products (category_id, product_name, price);
  

  避免使用or查询

  在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的MySQL版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题，不过建议大家在实际使用中还是规范写法，能不用就少用。

  避免使用 != 或者 操作符

  SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引。解决方法:通过把不等于操作符改成 or，可以使用索引，避免全表扫描

  id 'aaa'

  id >'aaa'or id