前言

前段时间压力山大，为了提高程序的性能和稳定性，需要对基于springcloud的微服务平台进行性能压测。由于时间的紧迫性，我们只对关键的几十个api接口以及网关进行了压测，对于压测的结果，我发现一个有意思的地方：大部分api接口性能不达标源于大sql查询慢，而且是随着时间的推移，数据越多查询越慢。

什么是大sql

看几个我们项目真实的例子

1、获取新闻列表api接口

<select id="queryList" resultMap="BaseResultMap">
 select t.*,ac.*,u.user_truename as username,f.content as flowContent ,f.flow_type as flowType ,d.dept_id as
 deptid, d.org_name as prodeptName, c.name as categoryname ,pc.name as pcategoryname from cms_article t
 left join cms_template_flow f on t.template_flow_id=f.template_flow_id
 left join sys_user u on t.create_user_id =u.user_id
 left join sys_dept d on d.dept_id =t.prodeptid
 left join cms_category c on t.category_id=c.id
 left join cms_category pc on pc.id=c.parent_id
 left join ms_article_count ac on ac.article_id=t.id
 <where>
 ...
 </where>
 
</select>

为了获取新闻列表结果，需要文章表（cms_article）左关联文章模板表（cms_template_flow），用户表（sys_user），机构表（sys_dept），文章频道分类表（cms_category）、文章评论点赞统计表（ms_article_count）共7张表，其中文章表、评论点赞统计表随着时间的推移，数据量以千万级甚至亿级来计算，显然上面的大sql性能肯定不符合要求。

2、新闻详情api接口

<select id="queryObject" resultMap="BaseResultMap">
 select t.*,u.user_truename as username,c.name as categoryname,f.content as flowContent,f.flow_type as flowType ,
 c.dept_id as cdeptid ,d.dept_id as deptid, d.org_name as deptname,d1.org_name as prodeptName,pc.name as pcategoryname,pc.id as pcategoryid,
 sdt.ITEM_NAME as sfromname ,sd.DICT_NAME as ffromname
 from `cms_article` t
 left join sys_user u on t.create_user_id =u.user_id
 left join sys_dict_item sdt on t.sfrom = sdt.ITEM_NO
 left join sys_dict sd on t.ffrom = sd.DICT_NO
 left join sys_dept d on d.dept_id =t.deptid
 left join sys_dept d1 on d1.dept_id =t.prodeptid
 left join cms_category c on t.category_id =c.id
 left join cms_category pc on pc.id=c.parent_id
 left join cms_template_flow f on t.template_flow_id=f.template_flow_id
 where
 t.id = #{id}
</select>

新闻详情接口需要文章表（cms_article）左关联字典表（sys_dict、sys_dict_item）、用户表（sys_user），机构表（sys_dept），文章频道分类表（cms_category），文章模板表（cms_template_flow）同样存在数据量越大sql查询越慢的问题。

大sql特征

1. 表数据量大（随时间推移）
2. 关联表多（关联表超过4个）
3. 表设计没做冗余
4. 早期赶进度，业务关联增多，后期改造成本高

如何改进

早期数据库数据量小时，用户感知不到页面查询慢。但是随着时间的推移，数据量逐渐增多，数据库sql查询慢的问题会变得非常突出，这是我们不愿意看到的，所以我们在实际的项目中做了4种切实可行的sql以及程序优化。

一、表字段冗余设计

例如文章表需要关联用户表的userid字段，那我们可以把userid字段添加到文章表，这样减少与用户表关联查询。

二、利用redis做二级缓存查询

对于无需分页的数据，我们可以利用redis的set/get方式缓存数据，部分数据可以设置过期时间。

redis设置过期时间

redisClient.setex(key, 10 * 60, jsonValue); 

redis获取数据

String key = "pcm:news:getRelatedNews:" + getUserId() + ":" + getPath().replace(",", "")+":" +aid+":"+wid;
String jsonValue = redisClient.get(key);

三、合理创建表字段索引

建议一个表创建索引的数量在4个左右，合理在查询频繁的字段上建立索引可以大大提高sql的查询性能。

四、利用搜索引擎：elasticsearch、mongodb

对于查询数据量大，又需要实时查询的数据，可以elasticsearch和mongodb做搜索引擎，它们天生适合大数据查询。

其他解决思路

诊断sql

1. 代码中的插件：你在执行功能的时候，通过控制台可以直接看到 SQL 的执行时间
2. 数据库工具：Oracle 的工具 PL/SQL Developer，MySQL 的工具 Navicat。你直接把 sql 语句放在工具中跑，你能直观的看到查询的结果以及执行的时间。
3. 利用show processlist，查看执行计划，找出哪些sql的执行慢，然后对症下药

sql调优

1. 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
2. 建组合索引的时候，区分度最高的在最左边
3. 不要使用count(列名)或count(常量)来替代count(*)
4. 不要使用select * ，防止全表扫描
5. order by / group by 字段包括在索引当中减少排序，效率会更高。
6. 大查询分页，不要一次返回太多数据
7. 不做join，改为在数据库中做冗余
8. 尽量不在事务内做读操作或者其他远程访问操作，事务里面只做写，尽量保障事务短
9. 如果慢sql是因为业务设计原因，考虑是否通过业务改造避免，比如原来要查30天的数据，现在查7天也能满足需求
10. 将数据库的查询转成更高效的K-V或者内存缓存起来，有模糊查询的，走搜索引擎
11. 进行sql语句：看下表是否where、order的字段未加索引或者join出来太多行
12. 进行分库分表，将大表拆小

总结

慢sql的产生不单单是sql代码和程序本身的问题，更多需要从架构、业务、服务器、网络等多方面去排查和分析具体原因 。

最后

如果觉得本文对您有帮助的话，记得关注、转发哦，我会为大家持续提供原创干货。需要资料，请关注、转发，私信“资料”面试+微服务+springboot资料免费赠送。