前言
前段时间压力山大,为了提高程序的性能和稳定性,需要对基于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特征
- 表数据量大(随时间推移)
- 关联表多(关联表超过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
- 代码中的插件:你在执行功能的时候,通过控制台可以直接看到 SQL 的执行时间
- 数据库工具:Oracle 的工具 PL/SQL Developer,MySQL 的工具 Navicat。你直接把 sql 语句放在工具中跑,你能直观的看到查询的结果以及执行的时间。
- 利用show processlist,查看执行计划,找出哪些sql的执行慢,然后对症下药
sql调优
- 页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
- 建组合索引的时候,区分度最高的在最左边
- 不要使用count(列名)或count(常量)来替代count(*)
- 不要使用select * ,防止全表扫描
- order by / group by 字段包括在索引当中减少排序,效率会更高。
- 大查询分页,不要一次返回太多数据
- 不做join,改为在数据库中做冗余
- 尽量不在事务内做读操作或者其他远程访问操作,事务里面只做写,尽量保障事务短
- 如果慢sql是因为业务设计原因,考虑是否通过业务改造避免,比如原来要查30天的数据,现在查7天也能满足需求
- 将数据库的查询转成更高效的K-V或者内存缓存起来,有模糊查询的,走搜索引擎
- 进行sql语句:看下表是否where、order的字段未加索引或者join出来太多行
- 进行分库分表,将大表拆小
总结
慢sql的产生不单单是sql代码和程序本身的问题,更多需要从架构、业务、服务器、网络等多方面去排查和分析具体原因 。
技术交流