1. 避免使用 select * 进行查询

避免使用 select *，很多时候我们写 SQL 语句的时候为了方便使用 select * 查出表中所有数据。
而在实际业务场景中，可能我们真正使用的只有其中一两列，查了很多数据，但是不用，白白浪费了数据库资源，例如内存或者 cpu。
此外多查出来的数据通过网络 IO 传输过程中，也会大大增加数据的传输时间。还有最重要的是 select * 不会走覆盖索引，会出现大量的回表操作，从而导致查询 SQL 性能低。

反例 (使用了 select * )

select * from user where id=1;

正例(只查询需要用到的列)

select name,age from user where id=1;

2. 用 union all 代替 union

SQL 语句使用 union 关键字后，可以获取排重后的数据，而如果使用 union all 关键字则可以获取所有数据，包含重复的数据。

反例

(select * from user where id=1;)
union
(select * from user where id=2;)

排重的过程需要遍历、排序和比较，它不仅耗时，还消耗 CPU 资源。
所以能用 union all 的时候尽量不用 union 。

正例

(select * from user where id=1;)
union all
(select * from user where id=2;)

除非一些特殊场景，比如 union all 之后，果集中出现了重复数据，而业务场景中是不允许产生重复数据，这时可以使用 union 。

3. 小表驱动大表

也就是说用小表数据集驱动大表的数据集
假如有 order 和 user 这两张表，其中 order 表有 1 万多条数据，而 user 表只有 1 百条数据。
这时如果想查询一下，所有有效用户下过的订单列表.

可以使用 in 关键字实现

select * from order
where user_id in (select id from user where status=1)

也可以使用 exists 关键字实现

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

这个需求用 in 关键字实现更合适，因为如果 SQL 语句中包含了 in 关键字，则他会优先执行 in 里面的子查询语句，然后再执行 in 外面的语句。如果 in 里面的数据量很少，作为条件查询速度更快。
而如果 SQL 语句中包含 exists 关键字，它会优先执行 exists 左边的语句(主查询语句)，然后把它作为条件，去跟右边的语句匹配，匹配成功，则可以查询出数据；匹配失败，数据被过滤掉。
在假设的业务中 order 表是大表，user 表是小表。如果 order 表在左边，in 关键字的性能会更好。

总结:
in 适用于左边大表，右边小表(in 左大右小)
exists 适用于左边小表，右边大表(exists 左小右大)

4. 批量操作

一批数据经过处理后，需要插入数据
反例

for(Order order:list){
orderMappper.insert(order);
}

insert into order(id,code,user_id)
values(123,'001',100);

这种操作需要多次请求数据库才能完成这批数据插入
多次请求数据库会消耗大量性能

正例

orderMappper.insertBatch(list);

提供一个批量插入数据的方法

insert into order(id,code,user_id)
values(123,'001',100),(124,'002',100),(125,'003',101);

这样只需要远程请求一次数据库，SQL 性能会得到提升，数据库量越大提升越多。（不建议一次批量操作太多数据，数据太多数据库也会响应很慢，建议每批控制在 500 以内）

5. 多用 limit

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

反例

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

List<Order> list = orderMappper.getOrderList();
Order order =list.get(0);

根据用户 id 查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单集合，然后在代码中获取第一个元素的数据, 即首单数据，就能获取首单时间。
这样虽然在功能上没有问题，但效率不高。需要先查询出所有数据，有点浪费资源。

正例

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

使用 limit 1，只返回该用户下单时间最小的那一条数据即可。

进阶操作

在删除和修改数据时，为了防止误操作，导致删除或修改了不相干的数据也可以在 sql 语句最后加上 limit。

例如

update order set status=0,edit_time=now(3)
where id>=100 and id<200 limit 100;

这样即使误操作，比如 id 搞错，也不会对太多数据造成影响

6. in 中值太多

在批量查询接口时，我们通常会使用 in 关键字过滤出数据。
比如，想通过指定的一些 id，批量查询出用户信息。

select id,name from category
where id in (1,2,3...100000);

如果我们不做任何限制，该语句一次性会查询出很多数据，很容易导致接口超时。
可以在 SQL 中对数据用 limit 做限制。

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

不过我们更多是在业务代码里加限制

例如

public List<Category>getCategory(List<Long>ids){
    if(CollectionUtils.isEmpty(ids)){
        return null;
    if(ids.size（）>500){
        throw new BusinessException("一次最多允许查询              500条记录")
    return mapper.getCategoryList(ids);

还有一个方案就是，如果 ids 超过 500 条记录，可以分批用多线程去查询数据，每批 500 条记录，最后把查到的数据汇总到一起返回。只适合临时方案，不适合 ids 太多的场景（ids 太多返回数据量大，网络传输消耗性能）

7. 增量查询

当我们需要通过远程接口查询数据，然后同步到另外一个数据库

反例

select * from user;

直接获取所有数据，然后返回。这样虽然方便，但数据多起来的话查询性能会非常差。

正例

select * from user
where id>#{lastId} and create_time >= #{lastCreateTime}
limit 100;

按 id 和时间升序，每次只同步 100 条数据，每次同步完成后保存 100 条数据中最大的 id 和时间，给同步下批数据的时候使用，这就是增量查询，能够提升单次查询的效率。

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 条数据，很浪费资源。

解决方法
1.先找到上次分页最大 id，然后利用 id 上的索引查询（id 要连续且有序）

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

2.使用 between 优化分页（between 要在唯一索引上分页，不然会出现每页大小不一致问题）

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

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

在 MySQL 中，要在两张及以上的表中查询出数据的话，一般有两种实现方式: 子查询和连接查询

子查询

select * from order
where user_id in(select id from user where status=1);

子查询可以通过 in 关键字实现
一个查询语句的条件落在另一个 select 语句的查询结果中
先运行嵌套在最内层的语句，再运行外层语句。
适合表数量不多的情况。
缺点是执行子查询时，需要创建临时表，查询完毕后需要再删除这些临时表，会有额外的性能消耗。可以改成连接查询

连接查询

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

这样优化之后，性能可能会更高

10. join 表不宜过多

根据阿里巴巴开发者手册规定，join 表的数量不应该超过 3 个。
如果 join 太多，MySQL 在选择索引的时候会非常复杂，容易选错索引，在没有命中的时候 Nested-Loop Join 就是分别从两个表读一行数据进行两两对比，复杂度是 n^2。应该尽量控制 join 表的数量。

select a.name,b.name,c.name,a.d_name from a
inner join b on a.id = b.a_id
inner join c on c.b_id=b.id;

如果业务场景中需要查询出另外几张表中的数据，可以在 a、b、c 表中冗余专门的字段。比如在表 a 中冗余 d_name 字段，保存需要查询出的数据。我之前也见到过有些 ERP 系统，并发量不大但业务比较复杂，需要 join 十几张表才能查询出数据，所以 join 表的数量需要根据系统实际情况决定，尽量越少越好。

11. join 的注意事项

在涉及到多张表联合查询的时候，一般会使用 join 关键字，而 join 使用最多的是 left join 和 inner 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. 控制索引的数量

索引能够显著提升查询 SQL 性能，但索引并非越多越好。因为表中新增数据时，需要同时为它创建索引，而索引需要额外的存储空间，还有一定的性能消耗，阿里巴巴开发者手册规定，单表索引数量应该尽量控制在 5 个以内，并且单个索引中的字段数不超过 5 个。
MySQL 使用 B+树的结构来保存索引，在 insert、update 和 delete 操作时，需要更新 B+树索引。如果索引过多，会消耗更多性能。
如果表中索引太多已经超过 5 个，怎么解决？
1.系统并发量不高且表中数据量也不多的情况下，超过 5 个也可以，但不要超过太多。
2.对于一些高并发的系统，就需要遵守单表索引数量不超过 5 的限制。能够建联合索引就不要建单个索引，可以删除无用的单个索引，将部分查询功能迁移到其他类型的数据库中，比如 ES。在业务表中只需要建几个关键索引即可。

13. 选择合理的字段类型

char: 固定字符串类型，存储空间是固定的，有时候会浪费存储空间。
varchar: 变长字符串类型，存储空间会根据实际数据长度调整，不会浪费存储空间。

长度固定的字段使用 char 类型，比如手机号、邮编等。
长度不固定的字段使用 varchar 类型，比如企业名称、商品名称等。

我们在选择字段类型时，能用数据类型就不用字符串（字符处理往往比数字要慢），尽可能使用小的类型。比如用 bit 存布尔值、用 tinyint 存枚举值等。
长度固定的字符串字段，用 char 类型，长度可变的字符串字段用 varchar 类型。
金额字段用 decimal，避免精度丢失。

14. 提升 group by 的效率

group by: 主要功能去重和分组，通常会和 having 一起配合使用，表示分组后再根据条件筛选数据。

反例（实际开发中遇到的问题）

select user_id,user_name from order
group by user_id
having user_id <=200;

它先把所有数据根据用户 id 分组之后，再去筛选用户 id 大于 200 的用户，分组是一个相对耗时的操作，我们应该先缩小数据范围之后再分组。

正例

select user_id,user_name from order
where user_id <=200
group by user_id;

SQL 语句在做一些耗时操作之前，尽可能缩小数据范围，这样能提升 SQL 的整体性能。

15. 索引优化

检查 SQL 语句有没有走索引

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

SQL 索引失效常见的原因

1. 不满足最左前缀原则
2. 范围索引列没有放最后
3. 使用了 select *
4. 索引列上有计算
5. 索引列上使用了函数
6. 字符串类型没加引号
7. 用 is null 和 is not null 没注意字段是否允许为空
8. like 查询左边有%
9. 使用 or 关键字