如何写好SQL（常见优化）

最新推荐文章于 2024-07-08 21:32:26 发布

weixin_34149796

最新推荐文章于 2024-07-08 21:32:26 发布

阅读量1.1k

点赞数

文章标签：数据库 python 运维

原文链接：https://my.oschina.net/mengyuankan/blog/1838288

版权

2019独角兽企业重金招聘Python工程师标准>>>

在每个项目中都会用到数据库，而操作数据库是通过 SQL 语句来操作的，如何写出好的SQL，需要经验的积累和多多动手实践，在开发的过程中，我们很可能对我们书写的SQL没有更高的要求，仅仅只是停留在满足业务需要的层面上，要求就是能用就好；这样以后可能会出现问题，如性能问题等。因为在本地开发的过程中，数据量没有那么大，仅仅是一些测试数据；而到了后期上线测试的时候，数据量一上来，就可能会暴露出问题，之后运维的兄弟在来优化 SQL；而我们在开发编写SQL的时候，也应该多想想怎么写，怎么写好，第一是能用，第二是好用，在满足需要的基础上在多考虑考虑，如该使用 in 还是该使用 join，还是 exists等；下面是一些常见的优化方法，自己在开发中也会尽量按照执行：

查询的方式

1. 一个复杂的查询还是多个简单的查询

MYSQL内部每秒能够扫描内存中上百万的数据，相比之下，MySQL响应数据给客户端的时候要慢得多，在其他条件相同的情况下，使用尽可能少的查询会更好些，这样会减少数据库的操作，如一次能够查到数据，则不应该查询多次返回。但是有时候，将一个大的复杂的查询分解为多个小的查询也是有必要的。

2. 切分查询

有时候需要将一个大的的查询拆分为多个小的查询，每个小查询的功能都一样，只完成一小部分，每次只返回一小部分的查询结果。

比如在定时删除过期数据，例如需要在每个月的最后一天12点定时删除一些过期的数据，而这个数据大概有10W左右，如果使用一个大的语句一次性完成的话，则可能需要一次锁住很多数据，耗尽系统资源，阻塞很多小的查询，但如果这个操作分为几次操作，每次只删除1W，循环10次等。

3. 分解关联查询

可以对复杂关联查询进行分解，对每一个表进行一次单查询，然后将结果在应用程序中进行关联，这样做的优点有：

a) 让缓存的效率更高，方便应用程序从缓存中查询单表的查询结果

b) 将查询分解后，执行单个查询可以减少锁的竞争

c) 可以减少冗余数据的查询，在应用层做关联，有些数据只需在数据库中查询一次，而在数据库中做关联，则需要查询多次。

优化特定类型的查询

count()查询

Counnt()有两个作用，一是查询某个列的数量，列为null, 则不做统计

另一个是统计结果集的行数，当确认括号内的列不可能为null的时候，实际上就是在统计行数，如 count(id)，count(*)，在使用count(col)来统计行数的时候，如count(id)，在MySQL内部会将这个表达式优化为 count(*) 执行。所以在统计行数的时候，直接使用 count(*) 即可，简单粗暴，性能也会更好。

简单优化

查询 id > 100 的数量，可以看到查询到的数量为 180w 多，耗时 2s 805ms

可以使用 count(*) 全表非常快的特性，转换思路，先查询总记录数，然后在查询 id <= 100 的记录数，再相减即可，如下，查询的数量还是 180w 多，时间只消耗20ms。

使用 explain 来查看该SQL的执行计划如下：

第一种方式：可以看到行数为 1835981 使用 where条件和索引。

第二种方式：可以看到行数为5，使用where条件和索引，还有 Select tables optimized away，表示mysql已经优化过。

使用近似值

有些业务场景不需要很精确的count值，则可以使用近似值代替，因为执行 explain 不需要正真的去执行查询，所有成本很低。可以使用 explain 中的 rows 行

优化关联查询

a) 确保 on 或者 using 子句中的列有索引。

b) 确保 group by 和 order by 中的表达式只涉及到一个表中的列。这样 MySQL才有可能使用索引了优化这个过程。

优化子查询

尽量使用关联查询来代替子查询

MySQL的子查询实现很不好，最常见的一类情况是 where 条件中包含 in 子句，

因为MySQL对 in() 列表中的选项有专门的优化策略，in() 列表查询速度很快，一般会认为会先执行子查询返回的数据，在执行外层查询，执行顺序如下：

但是MySQL不会这样做，它会将相关的外层表压到子查询中，它认为这样可以更高效率的查找到需要的数据行，它会上述SQL改下为如下方式：

可以使用关联查询来改写上面的SQL :

优化 group by

1. group by 可以使用索引来优化；当无法使用索引的时候，group by 会使用临时表和文件排序两种方式来做分组

2. 如果需要对关联查询做分组，并且按照表中的某个列进行分组，则通常会采用查找表的标识列进行分组的效率比其他列更高。

3. 当查询使用 group by 的时候，如果没有指定 order by 来显示指定排序，则结果会自动按照分组的字段进行排序，如果不关心结果的顺序，而这种默认的排序又会导致了需要文件排序，则可以使用 order by null 来让 MySQL 不再进行文件排序，也可以直接在 group by 中使用 asc 或 desc 来排序。

优化 limit 分页

在做分页操作的时候，通常会使用 limit 加偏移量的方式来实现，如果有索引的话，效率也会很不错，如果没有索引的话，MySQL则需要做大量的文件排序操作。

1. 如果偏移量很大，如 limit 1000, 20，则会查询出 10020条数据，然后只返回20条数据，前面的10000条数据将被丢弃，针对这种情况，要么在页面中限制分页的数量，要么在偏移量搞大点。

2. 在分页查询的时候，尽可能的使用索引覆盖扫描，而不是查询所有列，然后在根据需要做一次关联查询操作再返回所需要的列。

获取需要访问的记录后再根据关联列回原表查询所需的所有列。

3. 将 limit 转换为已知位置的查询，通过范围来查询结果，该方式需要实现计算边界值，极大提供查询效率

4. 使用 sql_calc_found_rows 来统计分页的总数

在 limit 语句中加上 sql_calc_found_rows 就可以获得去掉limit以后满足条件的行数，因此可以作为分页的总数；加上该条件后，不管是否需要， MySQL都会扫描所有满足条件的行，然后再抛弃掉不需要的行，而不是在满足limit的行数后就终止扫描，因此该条件的代价可能会很高。

5. 更好的设计

可以将分页的具体页数换为“下一页”按钮，假设每页显示20条记录，则在每次查询的时候，都使用 limit 查询21 条数据，然后只显示20 条，如果第21条数据存在，则表示还有更多的数据，就会显示“下一页”按钮，否则就无需显示。

优化 union 查询

MySQL总是通过创建并填充临时表的方式来执行 union 查询，因此很多优化策略在 union 中无法很好的使用，需要将 where, limit, order by 等子句下推到各个查询中，以便优化器可以充分利用这些条件进行优化，

它会把第一个子查询和第二个子查询查询到的所有结果放入到临时表中，之后再从临时表中取前10条数据。

尽量使用 union all 代替 union，union 查询结果是没有重复的记录，而union all 查询结果可能有重复的记录；除非确定查询的结果必须要无序的，否则应该使用 union all，如果使用 union，MySQL会给临时表添加 distinct 选项，这会导致对整个临时表做唯一性检查，效率低下。