【MySQL】常见SQL优化小技巧

一、前言

    MySQL的优化是分为性能优化和SQL语句优化的，二者是不一样的概念。所以在各位看官在观看本博客之前我就先强调这一点。然后呢这篇博客主要就是以SQL优化为主，因为SQL的优化我相信每个小伙伴都会在写sql语句的时候接触，也是我们印象比较深的。这里博主就介绍一些常见的SQL语句的优化，相信不管在实际项目开发中还是被面试官谈到SQL的优化，小伙伴们都能轻松应对。

二、SQL常用小技巧

1、select子句中避免使用‘*’

原因：有时候我们在查询数据的时候，使用不到一张表的全部信息时，使Select * 查询表中所有列的数据，此时会白白浪费数据库的资源或者内存资源、CUP的资源，而且多查出来的 数据，在网络IO传输的过程中会添加传输的时间，最重要的是select * 不会走覆盖索引，会有大量的回表操作，会导致sql的新能 低下哎（至于覆盖索引和回表操作，在博主之前的文章有讲到，有需要的小伙伴可以去看一看）

2、用union all代替 union

原因：使用sql中使用union关键字查询得到的是可以排除重复的数据，而使用union all关键字，可以获取所有关键字，包含重复的数据。虽然union获得的数据是没有重复的，但是在去重的过程会有遍历，比较等操作，是更耗时和消耗CUP资源的。（除了业务 场景需要不重复的数据外，我们尽量用union all 代替union）

3、用小表驱动大表

原因：在大表的查询中使用小表的查询结果，假如有个order表（10000条数据）和user表（100条数据），现在要查询所有有效的用户下过的订单列表，此时我们可以使用in或exists两个关键字实现这个需求。

使用In关键字

因为我们用in做关键字的时候sql会先执行in里面的子查询语句，再执行in外面的执行语句：select *  from order  where in (在user表中查询过滤信息 )。此时user表数更少查询速度更快，效率更高。

使用exists关键字

如果使用exists关键字，则会先执行exists左边的语句（即主查询语句），然后把它作为条件，去跟右边的语句匹配，如果匹配上则可以查出数据，如果匹配不上，数据就别过滤掉了。

总结

In适用于左边大表，右边小表；Exists适用于左边小表，右边大表。其核心思想都是用小表驱动大表。

4、批量操作

原因：如果对于数据插入的时候，我们一条一条的去执行 插入语句，可想而知需要不断的请求mysql的连接，这样会消耗更多的性能。此时我们就最好提供一个批量操作的方法，通过只请求一次数据库就完成批量操作，这样sql的性能就会提高，数据量越多提升越大。（注：批量操作的时候每批数据尽量控制在500以内，多余500就分批次处理，因为数据量太多，sql性能也会受 影响）

5、多用limit

原因：有时候我们需要查询某一条数据，此时使用limit 1。此时会大大提升我们查询的性能。此外在删除和修改数据的时候也可以使用limit减少我们的失误操作而造成更多的数据受影响。

6、增量查询

原因：有时候我们会通过远程接口查询数据，然后同步到另外一个数据库。如果直接获取所有的数据，然后同步过去，但是数据很多查询性能会非常大。此时我们可以按id和时间 升序，每次只同步一批数据，当数据同步 完成后，保存这批数据的最大id和时间，给同步下一批数据的时候用，通过这种增量 查询到方式能够提升单次查询的效率。

7、高效的分页

原因：我们有时候列表页在查询数据的时候，为了防止一次性返回过多的数据影响接口的性能，我们一般会对查询接口做分页处理，在数据库中分页一般采用limit关键字，但是当基数比较多的时候，用limit性能就受影响，此时可以用between优化分页，但between要在唯一索引上分页性能才好，不然会出现每页大小不一致的情况。

例如：select * from admin order by admin_id limit 100000,10（查了100010条数据，却只要10条数据，浪费很多不用的资源）

优化为：select * from admin where admin_id between 100000 and 100010 order by admin_id。

8、Join表的数量不宜太多

原因：如果Join表的数据太多那么数据库在选择索引的时候会非常复杂，很容易搞错索引，而且如果没有命中，时间复杂度可能是O(n^2)。

9、控制索引的数量

原因：索引能够明显的提升查询SQL的性能，但是索引增多的时候，其所需的储存空间也会增多，而且还会有一定的性能消耗。

10、选择合理的字段类型

原因：不同的字段类型对应的属性不同，合理原则可以大大提升SQL的性能。如：char表示固定字符串类型，varchar表示可变字符串类型，bit存布尔值，tinyint存枚举，decimal用于金额字段防止字段的丢失等等。

11、Group By的优化

原因：在使用group by实现分组操作时，我们可以先通过where语句在group之前进行一些数据的过滤，先缩小范围。当然也可以在分组时通过索引来优化性能。

12、主键的优化

①满足业务需求的情况下，尽量降低主键的长度。

 ②插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT自增主键。 尽量选择顺序插入，因为乱序容易出现页分裂和页合并现象，影响性能。

 ③尽量不要使用UUID做主键或者是其他自然主键，如身份证号。 （这种设置的主键为乱序，所以在插入时可能会出现 页分裂，并且主键长度相对较长，在检索时会耗费 大量的磁盘空间）

⑤业务操作时，避免对主键的修改。 （主键作为唯一的标识，若修改主键，还需要修改索引结构，代价大） 

13、将where中用的比较频繁的字段建立索引

原因：因为索引的添加可以大大提升我们查询的效率，如果又是经常使用的字段，此时为其建立索引能很好优化SQL的性能。

14、尽量避免在列上用函数或者运算 ，这样会导致索引失效

例如：select * from admin where year(admin_time)>2014

优化为： select * from admin where admin_time> '2014-01-01′

15、使用索引扫描,联合索引中的列从左往右,命中越多越好

原因：在博主索引篇介绍这个联合索引的优势，需要的小伙伴自取哦。

三、总结

    这篇文章大多数的内容是参考了b站up主【苏三说技术】的关于sql优化的讲解视频，大家有兴趣的可以去观看。博主这里做个记录，方便后期的学习与回顾。