网站首页 > 资源文章 正文
前言
前段时间压力山大,为了提高程序的性能和稳定性,需要对基于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代码和程序本身的问题,更多需要从架构、业务、服务器、网络等多方面去排查和分析具体原因 。
最后
如果觉得本文对您有帮助的话,记得关注、转发哦,我会为大家持续提供原创干货。需要资料,请关注、转发,私信“资料”面试+微服务+springboot资料免费赠送。
猜你喜欢
- 2024-09-09 Oracle 查询命令合集:从新手到专家的指南
- 2024-09-09 数据迁移分享(数据迁移要注意什么)
- 2024-09-09 Oracle监听常见问题及解决办法(oracle19c监听)
- 2024-09-09 数据库工程师的岗位职责与任职要求有哪些?
- 2024-09-09 Oracle学习笔记(oracle入门教程)
- 2024-09-09 不用发送比特币,安华让你的数据库免受勒索
- 2024-09-09 有什么办法可以查看Oracle SQL执行计划?这里告诉你五个方法!
- 2024-09-09 系统部署-(三)数据库ORACLE数据还原
- 2024-09-09 Oracle性能调优——查看执行计划(oracle 执行计划查看)
- 2024-09-09 企业权限管理系统——项目需求分析&环境搭建
你 发表评论:
欢迎- 最近发表
- 标签列表
-
- 电脑显示器花屏 (79)
- 403 forbidden (65)
- linux怎么查看系统版本 (54)
- 补码运算 (63)
- 缓存服务器 (61)
- 定时重启 (59)
- plsql developer (73)
- 对话框打开时命令无法执行 (61)
- excel数据透视表 (72)
- oracle认证 (56)
- 网页不能复制 (84)
- photoshop外挂滤镜 (58)
- 网页无法复制粘贴 (55)
- vmware workstation 7 1 3 (78)
- jdk 64位下载 (65)
- phpstudy 2013 (66)
- 卡通形象生成 (55)
- psd模板免费下载 (67)
- shift (58)
- localhost打不开 (58)
- 检测代理服务器设置 (55)
- frequency (66)
- indesign教程 (55)
- 运行命令大全 (61)
- ping exe (64)
本文暂时没有评论,来添加一个吧(●'◡'●)