一、背景
今年五月上上线了一个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步
相关推荐
查询sql优化查询 sql查询优化 查询优化sql查询优化 查询优化
VB编程源代码 71一次SQL查询中获得多个查询数值VB编程源代码 71一次SQL查询中获得多个查询数值VB编程源代码 71一次SQL查询中获得多个查询数值VB编程源代码 71一次SQL查询中获得多个查询数值VB编程源代码 71一次SQL...
sql查询优化 提高MySQL数据库查询效率的几个技巧
sql查询优化总结
ORACLE中SQL查询优化技术.pdf ORACLE中SQL查询优化技术.pdf
SQL查询优化,SQL查询优化常用技巧以及注意事项
SQL优化 SQL优化软件 SQL优化工具 很好用的工具,可以分析优化TSQL语句,oracle数据库语句优化工具
sql查询优化规则,左右连接,执行计划使用,以提高查询效率
海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化 SQL海量数据 优化...
优化sql查询速度优化
sql优化sql优化sql优化sql优化
使用性能监视器、SQL Trace以及动态管理视图和函数建立性能基线 理解一般系统中发生瓶颈的...SQL Server 2008查询性能优化》不是理论书籍,它的目的是帮助你避免数据库出现性能低下的状况,它还能帮助你保住你的工作。
SQL 2000管理实录
ORACLE SQL性能优化系列 ORACLE SQL性能优化系列 ORACLE SQL性能优化系列
SQL查询优化,进行适当的优化,运行速度有了明显地提高
SQL查询优化[定义].pdf
详细介绍慢查询日志及示例演示,MySQL查询优化器介绍及特定SQL的查询优化等 1.获取有性能问题的SQL的三种方法 2.慢查询日志概述 3.慢查询日志实例 4.实时获取性能问题SQL 5.SQL的解析预处理及生成的执行计划 6.如何...
SqlServer2008查询性能优化
SQL查询优化 SQL查询优化