sql优化小技巧【常见面试题】

（1）.避免使用“ select *  ”：

有时我们就是为了方便而直接使用' * '，如下图：

但是当实际业务场景中只查询一两列的时候，使用' * '查了很多数据但是不用，不仅白白浪费数据库资源，甚至内存或CPU资源。并且多查出来的数据，通过网络IO传输的过程中也大大浪费时间，所以建议最好使用需要查询的列名即可，如下图：

（2）.用union all代替union：

union all执行效率高，union执行效率低，因为union先进行union all把两个集合合并，之后对合并后的数据进行排序，去掉重复元素，去重过程需要遍历、排序和比较，更耗时、耗CPU资源

所以在实际业务当中没有具体要求到需要去重时，尽量使用union all效率更高些

（3）.小表驱动大表：

顾名思义，用小表的数据集驱动大表数据集。

假设大表oracle有10000条数据，小表user中有100条数据，需求：查询有效用户下过的订单信息，可以使用in或exists实现需求。此场景用in会更加合适些。

分析：in的特点：当sql语句中有in时先执行子查询语句，后执行外面语句；exists的特点：先执行exists左边的语句（即主查询语句）后作为条件与右边语句匹配，符合得出不符过滤。在此业务中，将大表放于左边用in关键字性能会更高。总结：in适用于左边大表，右边小表（条件用小表）；exists适用于左边小表，右边大表

如下图：

（4）.批量操作：

反例如下：

用for多次插入数据，多次请求数据库，众所周知每次请求数据库会消耗性能。这时就需要提供一个批量插入数据的方法

只需远程请求一次数据库，sql性能提升，数据量越多提升的性能越大。同时不建议一次批量操作太多数据，数据太多，响应也很会很慢，建议每批数据控制在500以内即可

（5）.多用limit：

例题：查询某个用户下第一个订单的首单时间。下面这个先查询用户id，时间，后又在代码中获取第一个数据，功能上没有问题但是需要先查出所有数据，就有点浪费资源了

优化：使用limit1，只返回该用户下单时间最小的那一个即可

此外在执行删除数据或修改时也可以在代码后加上limit，这样哪怕误操作，比如id写错了，也不会对太多数据造成影响

（6）.in中值太多：

在批量查询接口中，通常使用in关键字过滤数据，但是如下图中值过多很容易导致接口超时

这时可以在sql结尾加上limit做限制

但更建议在业务代码中加限制，如下图：

但是还是建议不要用于值太多的场景，返回的数据量太大，网络传输也是会消耗性能的

（7）.增量查询：

如下图所示，如果直接获取数据然后同步过去，当数据量庞大时，查询性能会非常差

优化如下图所示：

解释：按id和时间升序，每次之同步一批数据，这一批数据只有100条数据，每次完成之后，保存最大的id和时间给下一批使用，通过这种增量查询的方式，提高每次单次批量的效率

（8）.高效的分页：

有时候列表页查询数据时为避免一次性返回过多的数据而影响接口性能，一般会做分页处理，如下图；

但是当数据量大时，如下情况只需要1000000后面的20条数据，这样是非常浪费资源的

优化如下图所示：先找到上次分页最大的id，然后利用id上的索引查询。注意：该方案只适用于id连续且有序的

也可以使用between分页。注意between要在唯一索引上分页，否则会出现每页大小不一致的问题

（9）.用连接查询代替子查询：

当查询两张表时，可以使用子查询，优点是简单结构化，如果涉及表为数不多i的情况下，缺点是查询时数据库需要创建临时表，完成后又要删除这些临时表，有一定的额外消耗

这时可以改成连接查询，可以使性能更高，如下图所示：

（10）.join里的表不宜过多：

反例如下图所示：太多数据库在选择索引时会非常复杂，很容易选错索引，所以应该控制表数量

不过还是要根据实际业务需求来定，不能一概而论，尽量越少越好

（11）.join时要注意：

当我们在查询两张表时，通常会使用inner join连接如下图，因为数据库在inner join中会自动选择两张表中的小表去驱动大表，所以性能上不会有太大问题

但是当使用left join就要注意了，使用left join时数据库会默认用left join左边的表去驱动它右边的表，当左边的表数据量很大时就会影响性能，所以特别注意在用left join时左边尽量用小表右边用大表，如果能inner join的地方尽量少用left join

（12）.控制索引的数量：

众所周知，索引能提高查询的性能，但索引的数量并非越多越好。当表中新增数据时，需要同时为它创建索引，而索引是需要更多的存储空间的，并且还会有一定的性能消耗，所以一般建议在5个以内。当然如果索引太多的话，需要考虑数据库并发量不高，数据量也不多的情况下，即便超过5 个也可以。

对于一些高并发的系统如何优化索引数量：能够建联合索引就不建单个索引，可以适当删除多个单个索引

（13）.选择合理的字段类型：

char表示固定字符串类型，该类型会浪费一定存储空间；而varchar是根据实际情况字符串定的，相比较后者会更好一些，而手机号码、身份证号都有固定长度这时候选择char就可以。对于查询来说，在一个相对较小的字段内搜索效率明显要高一些，我们在选择字段时应该遵循能用数字类型就尽量不要用字符串类型，因为字符串的处理往往比数字慢一些，尽可能使用小的类型

（14）.提升group by的效率：

group by的功能：去重和分组

反例如下图所示：先根据id分组后再过滤用户id大于等于200的，分组是耗性能的操作

而应该先缩小数据量再进行分组反而会更好一些

（15）.索引优化：

检查sql有没有走索引，使用explain命令查看数据的执行计划，如下图所示：

第一次写博客，有哪里不对的请大佬在评论区指点~