当前位置：首页 > news >正文

SQL调优讨论

news 来源：原创 2025/5/21 6:31:03

说明：狭义的SQL调优，指对慢SQL（一般是Select语句，或包含Select的语句）优化，在不改变查询结果的情况下提高SQL执行效率。广义上的SQL调优，指对某个慢查询优化，通过一些类操作提高查询效率，不至于接口超时。

本文讨论广义上SQL调优的几个方面；

SQL语句

查询的核心是SQL，一个查询可能包含了一个或者多个SQL。SQL调优的第一步，是将慢查询执行的SQL取出来，这个SQL可能是运维同事通过监控捕捉到的，反馈给了开发。也有可能是通过慢SQL日志发现的，MySQL设置慢SQL日志可参考下面这篇文章：

如何开启MySQL的慢查询日志

拿到SQL后，如果是MySQL，可通过explain查看这个语句的执行计划；

# explain查看执行计划
explain select * from user;

其中type列，表示了此次查询的类型，如下：

在这里插入图片描述

效率排序如下

system > const > eq_ref > ref > range > index > all

阿里巴巴代码规范，要求如下：

在这里插入图片描述

以上是SQL语句方面

数据库设计

索引失效

发现了慢SQL，查看执行计划后，发现执行效率低，就要着手来优化。需要分析以下几点：

关联查询，关联的字段，是否建立了索引？类型是否一致？不一致会造成隐式的类型转换，导致索引失效；
查询是否使用了or、like、内置函数，导致了索引失效？
索引是否遵循了最左匹配原则，即联合索引，查询条件（并非书写顺序，参考文章）是否与索引顺序一致？
查询的字段是否加了索引，是否造成了回表？即查询的字段没有索引，使其借助了主键索引

建立索引

在表设计建立索引时，除了需考虑以上情况，还需考虑：

查询频繁使用的字段，where、order by 、join的字段建立索引；
组合索引，应该把散列度高的值放在前面；
过长的字段（加密的摘要字段），使用前缀索引；
区分度低（性别）的字段，不建议创建索引；
频繁更新的字段，不建议创建索引；
不建议用无序的值（身份证号、UUID）作为索引；

建立前缀索引，参考下面这篇文章：

怎么给数据库某个字段建立一个前缀索引

除此之外，在做表设计的时候，还应该考虑到查询便利和兼容性。博主在工作中遇到了一个场景，项目原来使用的是postgres数据库，后来客户需要能支持MySQL，遂将项目中的SQL改造成MySQL，改造过程中发现有许多查询与postgres数据库的数据类型高度绑定，脱离了postgres数据库，只能用复杂的，勉强能执行的MySQL语句实现，其查询效率可想而知。

举一个例子，做RBAC（基于角色的权限验证框架，参考：搭建一个基于角色的权限验证框架）设计的几张表，用户表拥有多个角色，角色拥有多个权限，可以设计以下五张表：

用户表；
角色表；
权限表；
用户角色表；
角色权限表；

但在项目中只建了前三张表，用户对应的角色，角色对应的权限，都作为前者的一个字段存储了。因为postgres有字符串数组这个类型，也有相关的函数支持，所以很方便，但MySQL没有这样的类型，改造的时候就很麻烦，写复杂了查询效率低，不写复杂又达不到业务需求。

分区

另外，如果某张表数据量非常大，有千万级的记录，需考虑建立分区提高查询效率。分区表，可通过对某字段的值或范围划分数据，后续查询，加上分区字段，相当于自带了一个索引。MySQL建立分区表，可参考下面两篇文章：

MySQL分区表（一）
MySQL分区表（二）

代码层面

缓存

如果SQL效率确实低，数据库表能加的索引也加了，还没效率。考虑是不是能从代码层面入手，看能不能加缓存，把一些基本不变的数据（如账户信息）在登录时或项目启动时，加载到缓存里。可参考下面这篇文章：

Redis缓存预热

既然加了缓存，也就需要考虑维护，在修改、删除的地方，需要同步删除缓存，查询的时候再加入到缓存。而缓存Key的命名也需要规范，可参考如下：

如果存储的是数据库中查询到的数据：数据库名称:表名:主键名:主键值，如

db_user:i_user:id:1 {"":"","":""}

如果存储的是临时性的业务数据：模块名称:业务名称:唯一标识，如：

SSO:USERLOGIN:UUID 123456

拆开SQL

还可以考虑是否能将SQL拆开，拆成几个小SQL，避免写一个大而全的SQL。博主之前遇到一个大项目，有个查询的权限控制竟然是把符合条件记录都查出来，再根据返回数据的某个字段，和当前账户的ID比较，在代码里去剔除掉，而不是在SQL里做条件控制。

我以为是某个弱智程序员写出来的，后面发现是我经验不足，这样反而比把条件加在SQL里查询效率高。小丑竟是我自己。

硬件方面

最后是硬件方面，需要考虑数据库部署的结构（是否有集群、有没有读写分离）和硬件性能（CPU、内存）。

读写分离，是指将对数据库的读操作和写操作分开，分散数据库压力。除了数据库结构上要部署，代码也需要支持，可参考下面这几篇文章：

MySQL主从结构搭建
MySQL主从的应用
使用Canal实现MySQL主从同步

硬件性能就不用说了，部署数据库的服务器最好是一台服务器，即便是部署主从，也应该是主节点一台服务器，从节点一台服务器，不要和其他服务混在一起，硬件配置可根据自己的业务需要配置，越高越高，当然也需要考虑经济效益。

总结

本文从SQL语句、数据库设计、代码和硬件方面讨论了SQL调优，一家之言，希望能对大家有启发