`
yvfish
  • 浏览: 263781 次
  • 性别: Icon_minigender_1
  • 来自: 合肥
社区版块
存档分类
最新评论

一次SQL查询优化实录

 
阅读更多

一、背景

今年五月上上线了一个Web系统,面向公众开放使用,随着系统用户量增长,系统性能下降的问题十分明显,因此着手进行瓶颈点排查。

二、Tomcat优化

按照日常方法优化Tomcat7,启用线程池、GZIP等机制(详情请Google之),系统性能略有改善。考虑通常磁盘IO通常才是系统关键瓶颈点,因此着手优化数据库。

三、数据库优化

当前系统使用MySQL5.1数据库,当前的数据库中5个关键表中数据平均各约10万左右,2个基本数据表,3个关联关系表。

安装30天免费试用版的MONYog5.5监测数据库状态和SQL,将执行次数较多查询1秒以上的语句导出,逐个分析。

 

1.增加字段索引

找出查询条件和关联时常用的字段,为其增加索引后,有80%以上的语句性能得到巨大提升,查询效率达到50毫秒以下。

需要注意的一点是,关系表中通常使用两个字段构建主键,应当分别为每个字段创建单独的索引。

2.优化复杂关联语句

MySQL5.x早期版本的子查询的性能非常差,特别是嵌套子查询,因此在程序中对查询语句进行拆分,分为两到三次查询,然后使用程序进行结果集合并。

例如,本系统中的一个需求:在某页面要求显示选中机构下的下级机构名称列表,每个机构名称后同时需要显示其下级机构和人员的数量。由于系统中对用户进行了人员查看分级控制,因此每个用户进行时看到的数据并不致,不适合采用数据维护时计算的方式(就是在添加、修改、删除机构或人员时,在相应的字段上更新累计数量)。

版本1的语句为:

SELECT g.group_id,group_name
,((SELECT COUNT(*) FROM tbl_contacts_group AS tcg,tbl_contacts AS c WHERE tcg.group_id=g.group_id AND c.cont_id=tcg.cont_id AND is_display=1 AND cont_level>='A')
+(SELECT COUNT(*) FROM tbl_group_relation WHERE parent_id=g.group_id)) AS group_childs_num 
FROM tbl_group AS g ,tbl_group_relation AS r
WHERE g.group_id=r.group_id AND g.ec_id='5515118546' AND r.parent_id='42b8df1e-415f-45ed-bedc-6147df42bc85' ORDER BY row_id ;

 用时约5-10秒,当有高并发出现时可能更高

版本2调整为两条语句

SELECT g.group_id,group_name
,(SELECT COUNT(*) FROM tbl_group_relation WHERE parent_id=g.group_id) AS group_childs_num FROM tbl_group_relation AS r,tbl_group AS g 
WHERE g.group_id=r.group_id  AND r.parent_id='42b8df1e-415f-45ed-bedc-6147df42bc85' ORDER BY row_id;


SELECT tcg.group_id,COUNT(0) 
FROM tbl_contacts_group AS tcg
JOIN tbl_contacts AS c
WHERE is_display=1 AND c.cont_id=tcg.cont_id AND tcg.group_id IN (SELECT group_id FROM tbl_group_relation AS tgr WHERE tgr.parent_id='42b8df1e-415f-45ed-bedc-6147df42bc85') AND  cont_level>='A'
GROUP BY tcg.group_id  

 第1条语句约20ms,第二条约500ms,使用程序合并的时间可以忽略

 

版本3中将第二条语句使用关联查询替代子查询,时间缩短为30ms

SELECT cg.group_id,COUNT(0) AS contact_count FROM tbl_contacts_group AS cg,tbl_contacts AS c,tbl_group_relation AS gr 
WHERE c.cont_id=cg.cont_id AND cg.group_id=gr.group_id AND gr.parent_id='42b8df1e-415f-45ed-bedc-6147df42bc85' AND is_display=1 AND  cont_level>='A' GROUP BY cg.group_id 

 

四、总结

数据库优化的最佳效果

1.常用字段增加索引

2.拆分复杂语句

4.优化数据库参数,适当增加相关参数的缓存空间

5.升级数据库版本(在mysql5.6上,版本1的语句执行时间在2秒以内)

优化的方法

1.监控分析(MONYog是个好工具)获取执行频繁用时较长的语句

2.调优

3.重复第1步

 

分享到:
评论

相关推荐

    MYSQL更新优化实录

    3. 优化更新策略:如果数据量巨大,可以考虑分批更新,每次处理一部分数据,而不是一次性处理所有数据。这样可以减少锁表的时间,提高并发性。 4. 利用存储过程:创建存储过程来封装整个更新过程,减少网络通信和...

    Java项目开发全程实录-源代码.zip

    首先,Java是一种广泛应用于企业级应用开发的强大编程语言,以其“一次编写,到处运行”的特性而闻名。学习Java项目开发不仅需要掌握语言基础,如类、对象、接口、异常处理等,还要熟悉其核心库,如集合框架、多线程...

    JAVA项目开发全程实录

    它的“一次编写,到处运行”(Write Once, Run Anywhere, WORA)理念,使得开发者可以轻松地在不同操作系统上部署应用程序。学习Java,首先要掌握其语法基础,包括数据类型、变量、运算符、控制结构(如if语句、for...

    php项目开发全程实录的光盘的 第三版源代码

    对于初学者,这是一次宝贵的实践经验;对于有经验的开发者,这是一个学习新技巧和提升技能的好机会。记得在学习过程中,不仅要理解代码的运作方式,还要思考为什么要这样设计,以及如何在自己的项目中应用这些知识。

    《C#项目开发实录》09_在线考试系统

    《C#项目开发实录》09_在线考试系统是一个基于C#编程语言构建的教育技术项目,旨在提供一个高效、便捷的在线测试平台...同时,对于想要从事教育信息化工作的人员,这是一次宝贵的实践经验,有助于提升他们的专业技能。

    PHP项目开发全程实录- 图书管理系统(php源码)精心收集

    数据库方面,可能使用了MySQL存储书籍、用户等信息,使用SQL语句进行查询和操作。 3. 物流配送信息网: 物流配送信息网涉及到订单管理、配送路线规划、货物追踪等功能。在实现上,PHP可能被用来处理用户请求,与...

    MySQL的集群配置的基本命令使用及一次操作过程实录

    1. SQL节点(SQL node,对应于MySQLd):负责执行SQL查询,并与数据节点交互,处理自身数据以及查询中心的数据。 2. 数据节点(Data node,ndbd):存储集群共享的数据,这些数据主要存在于内存中,以提高读写速度。...

    SharePoint 开发实录:3,SSO不同解决方案

    Single Sign-On(SSO)是一种身份验证机制,允许用户在一次登录后访问多个相互关联的应用系统,无需为每个系统单独进行身份验证。在SharePoint环境中,SSO可以帮助用户简化登录流程,提高工作效率,同时减少密码管理...

    互联网数字营销广告数据管理平台应用.pdf

    在方案选型与对比中,AdMaster选择了构建一个混合异构的大数据平台,该平台结合了SQL和NoSQL数据库以适应不同数据类型的需求,并利用实时数据库来处理即时数据流。这种架构允许快速响应市场变化,降低了框架切换的...

    《面向对象Java程序设计实验》教学大纲.docx

    数据库系统的基本概念、数据模型、关系数据库及其标准语言SQL、数据库安全性和完整性的概念和方法、关系规范化理论、数据库设计方法和步骤,数据库恢复和并发控制等事务管理基础知识,关系查询处理和查询优化等。...

Global site tag (gtag.js) - Google Analytics