sql优化

sql优化（mysql）

1. 避免使用select *

2. 用union all代替union

   union的使用(并集) 类似distinct union all（字段不去重）

   #应用场景，旧表联合新表的时候
   SELECT number,name from student
   UNION all
   SELECT number,name from student
   #这个是单表
   SELECT number,name from student
   

3. 小表驱动大表

   in（小表）

   因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。

   SELECT
   	*
   FROM
   	student
   WHERE
   	id IN (SELECT id FROM student2)
   

   exists

   #查询兴趣爱好为跳舞的同学姓名及住址：
   select st.name,st.address from student st 
   where EXISTS 
   (select * from student_info sf where 
   sf.student_no = st.student_no
   and sf.hobbies = '跳舞');
   
   

4. 批量操作

   orderMapper.insertBatch(list):
   

5. 多用limit

   有时候，我们需要查询某些数据中的第一条，比如：查询某个用户下的第一个订单，想看看他第一次的首单时间。

   select id, create_date 
   from order_Demo 
   where user_id=123 
   order by create_date asc 
   limit 1;
   

6. in中值太多

   对于批量查询接口，我们通常会使用in关键字过滤出数据。比如：想通过指定的一些id，批量查询出用户信息。会接口超时，可以限制条数抛异常

   select id,name from category
   where id in (1,2,3...100)
   limit 500;
   
   

7. 增量查询

8. 高效的分页

   在mysql中分页一般用的limit关键字：

   select id,name,age 
   from user limit 10,20;
   

   如果表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。

   比如现在分页参数变成了：

   select id,name,age 
   from user limit 1000000,20;
   

   mysql会查到1000020条数据，然后丢弃前面的1000000条，只查后面的20条数据，这个是非常浪费资源的。

   那么，这种海量数据该怎么分页呢？

   优化sql：

   select id,name,age 
   from user where id > 1000000 limit 20;
   

​ 先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的。

还能使用between优化分页。

select id,name,age 
from user where id between 1000000 and 1000020;

需要注意的是between要在唯一索引上分页，不然会出现每页大小不一致的问题。

9. 用连接查询代替子查询

   #子查询
   select * from order
   where user_id in (select id from user where status=1)
   

   #连接查询
   select o.* from order o
   inner join user u on o.user_id = u.id
   where u.status=1
   
   

10. join的表不宜过多

    根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个。

11. join时要注意

    区别：左（满足左边的，右边可能为空） 、右（满足右面，左边可能为空）、内（都满足的）

    • left join：求两个表的交集外加左表剩下的数据。

    • inner join：求两个表交集的数据。

    使用inner join的示例如下：

    select o.id,o.code,u.name 
    from order o 
    inner join user u on o.user_id = u.id
    where u.status=1;
    

    如果两张表使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。

    使用left join的示例如下：

    select o.id,o.code,u.name 
    from order o 
    left join user u on o.user_id = u.id
    where u.status=1;
    

    如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。

    要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join

12. 控制索引的数量

    阿里巴巴的开发者手册中规定，单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个

13. 选择合理的字段类型

    字节数的严格规定，可以节省存储空间

    遵循的原则：

    • 能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
    • 尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
    • 长度固定的字符串字段，用char类型。
    • 长度可变的字符串字段，用varchar类型。
    • 金额字段用decimal，避免精度丢失问题。

14. 提升group by的效率

    我们有很多业务场景需要使用group by关键字，它主要的功能是去重和分组。

    通常它会跟having一起配合使用，表示分组后再根据一定的条件过滤数据。

    #正常代码
    select user_id,user_name from orderDemo
    group by user_id
    having user_id <= 200;
    

    这种写法性能不好，它先把所有的订单根据用户id分组之后，再去过滤用户id大于等于200的用户。

    分组是一个相对耗时的操作，为什么我们不先缩小数据的范围之后，再分组呢？

    #优化代码     先缩小数据的范围之后，再分组  代码顺序问题
    select user_id,user_name from orderDemo
    where user_id <= 200
    group by user_id
    
    

15. 索引优化

    https://www.bilibili.com/video/BV1ko4y1N7x6?p=3&spm_id_from=pageDriver&vd_source=5cf290400cf3eef06b9d6f5b4834a615

    sql优化当中，有一个非常重要的内容就是：索引优化。

    很多时候sql语句，走了索引，和没有走索引，执行效率差别很大。所以索引优化被作为sql优化的首选。

    索引优化的第一步是：检查sql语句有没有走索引。

    那么，如何查看sql走了索引没？

    可以使用explain命令，查看mysql的执行计划。

    explain select * from `order` where code='002';
    
    

    

---

必要时可以使用force index来强制查询sql走某个索引。

​