高频SQL优化技巧9条

1.避免使用select *查所有字段

在实际业务场景中，可能我们真正需要使用的只有其中一两列。

但我们写sql语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据。多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。

还有一个最重要的问题是：select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。

2.小表驱动大表 （in里面的表尽量用小表）

小表驱动大表，即用小表的数据集驱动大表的数据集。

假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。

这时如果想查一下，所有有效的用户下过的订单列表。

可以使用in关键字实现：

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

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

3.避免in中值太多

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

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

如果我们不做任何限制，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。

如果id超过500条记录，可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回。

4.深度分页优化策略

•  有时为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理。

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

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

•  但是当数据多的时候，例如

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

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

方法一：用主键id或者唯一索引优化

即先找到上次分页的最大id，然后利用id上的索引来查询：

SELECT * FROM test_user WHERE id>1000000 LIMIT 100; # 0.047秒

 

使用此优化SQL相比于前面的查询速度已经快了11倍。除了使用主键ID，还可以运用唯一索引来快速定位特定数据，从而避免全表扫描。以下是相应的SQL优化代码，读取唯一键(pk)在1000至1019范围内的数据：

SELECT * FROM 表名称 WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

原因：索引扫描，速度会很快。

适用场景：如果数据查询出来是按照pk或者id进