数据库优化 - SQL优化

简介: 前面一篇文章从实例的角度进行数据库优化,通过配置一些参数让数据库性能达到最优。但是一些“不好”的SQL也会导致数据库查询变慢,影响业务流程。本文从SQL角度进行数据库优化,提升SQL运行效率。

判断问题SQL


判断SQL是否有问题时可以通过两个表象进行判断:


系统级别表象


  • CPU消耗严重


  • IO等待严重


  • 页面响应时间过长


  • 应用的日志出现超时等错误


可以使用sar命令,top命令查看当前系统状态。


也可以通过Prometheus、Grafana等监控工具观察系统状态。(感兴趣的可以翻看我之前的文章)


1.jpg


SQL语句表象


  • 冗长


  • 执行时间过长


  • 从全表扫描获取数据


  • 执行计划中的rows、cost很大


冗长的SQL都好理解,一段SQL太长阅读性肯定会差,而且出现问题的频率肯定会更高。更进一步判断SQL问题就得从执行计划入手,如下所示:


2.jpg


执行计划告诉我们本次查询走了全表扫描Type=ALL,rows很大(9950400)基本可以判断这是一段"有味道"的SQL。


获取问题SQL


不同数据库有不同的获取方法,以下为目前主流数据库的慢查询SQL获取工具


MySQL


  • 慢查询日志


  • 测试工具loadrunner


  • Percona公司的ptquery等工具


Oracle


  • AWR报告


  • 测试工具loadrunner等


  • 相关内部视图如v$、$session_wait等


  • GRID CONTROL监控工具


达梦数据库


  • AWR报告


  • 测试工具loadrunner等


  • 达梦性能监控工具(dem)


  • 相关内部视图如v$、$session_wait等


SQL编写技巧


SQL编写有以下几个通用的技巧:


• 合理使用索引


索引少了查询慢;索引多了占用空间大,执行增删改语句的时候需要动态维护索引,影响性能 选择率高(重复值少)且被where频繁引用需要建立B树索引;


一般join列需要建立索引;复杂文档类型查询采用全文索引效率更好;索引的建立要在查询和DML性能之间取得平衡;复合索引创建时要注意基于非前导列查询的情况


• 使用UNION ALL替代UNION


UNION ALL的执行效率比UNION高,UNION执行时需要排重;UNION需要对数据进行排序


• 避免select * 写法


执行SQL时优化器需要将 * 转成具体的列;每次查询都要回表,不能走覆盖索引。


• JOIN字段建议建立索引


一般JOIN字段都提前加上索引


• 避免复杂SQL语句


提升可阅读性;避免慢查询的概率;可以转换成多个短查询,用业务端处理


• 避免where 1=1写法


• 避免order by rand()类似写法


RAND()导致数据列被多次扫描


SQL优化


执行计划


完成SQL优化一定要先读执行计划,执行计划会告诉你哪些地方效率低,哪里可以需要优化。我们以MYSQL为例,看看执行计划是什么。(每个数据库的执行计划都不一样,需要自行了解)explain sql


3.png


image.png


接下来我们用一段实际优化案例来说明SQL优化的过程及优化技巧。


优化案例


表结构


CREATETABLE`a`(
`id`int(11) NOTNULLAUTO_INCREMENT,
`seller_id`bigint(20)                                       DEFAULTNULL,
`seller_name`varchar(100) CHARACTERSETutf8COLLATEutf8_binDEFAULTNULL,
`gmt_create`varchar(30)                                      DEFAULTNULL,
PRIMARYKEY (`id`)
);
CREATETABLE`b`(
`id`int(11) NOTNULLAUTO_INCREMENT,
`seller_name`varchar(100) DEFAULTNULL,
`user_id`varchar(50)  DEFAULTNULL,
`user_name`varchar(100) DEFAULTNULL,
`sales`bigint(20)   DEFAULTNULL,
`gmt_create`varchar(30)  DEFAULTNULL,
PRIMARYKEY (`id`)
);
CREATETABLE`c`(
`id`int(11) NOTNULLAUTO_INCREMENT,
`user_id`varchar(50)  DEFAULTNULL,
`order_id`varchar(100) DEFAULTNULL,
`state`bigint(20)   DEFAULTNULL,
`gmt_create`varchar(30)  DEFAULTNULL,
PRIMARYKEY (`id`)
);


三张表关联,查询当前用户在当前时间前后10个小时的订单情况,并根据订单创建时间升序排列,具体SQL如下


selecta.seller_id,
a.seller_name,
b.user_name,
c.statefroma,
b,
cwherea.seller_name=b.seller_nameandb.user_id=c.user_idandc.user_id=17anda.gmt_createBETWEENDATE_ADD(NOW(), INTERVAL600MINUTE)
ANDDATE_ADD(NOW(), INTERVAL600MINUTE)
orderbya.gmt_create;


查看数据量  


4.png


原执行时间


5.png


原执行计划


6.png


初步优化思路


SQL中 where条件字段类型要跟表结构一致,表中 user_id 为varchar(50)类型,实际SQL用的int类型,存在隐式转换,也未添加索引。将b和c表 user_id 字段改成int类型。


因存在b表和c表关联,将b和c表 user_id创建索引


因存在a表和b表关联,将a和b表 seller_name字段创建索引


利用复合索引消除临时表和排序


初步优化SQL


altertablebmodify`user_id`int(10) DEFAULTNULL;
altertablecmodify`user_id`int(10) DEFAULTNULL;
altertablecaddindex`idx_user_id`(`user_id`);
altertablebaddindex`idx_user_id_sell_name`(`user_id`,`seller_name`);
altertableaaddindex`idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);


查看优化后执行时间


7.png


查看优化后执行计划


8.png


查看warnings信息


9.png


继续优化alter table a modify "gmt_create" datetime DEFAULT NULL;


查看执行时间


10.png


查看执行计划


11.png


总结


查看执行计划 explain


如果有告警信息,查看告警信息 show warnings;


查看SQL涉及的表结构和索引信息


根据执行计划,思考可能的优化点


按照可能的优化点执行表结构变更、增加索引、SQL改写等操作


查看优化后的执行时间和执行计划


如果优化效果不明显,重复第四步操作

目录
相关文章
|
5天前
|
SQL 存储 关系型数据库
sql数据库的相关概念与底层介绍
sql数据库的相关概念与底层介绍
28 0
|
2天前
|
存储 缓存 NoSQL
《优化数据库性能的关键技巧》
在当今信息爆炸的时代,数据库扮演着至关重要的角色。本文将分享一些关键的技巧,帮助开发人员优化数据库性能,提升系统的响应速度和稳定性。
|
4天前
|
SQL 数据库 索引
sql深度优化
sql深度优化
11 1
|
5天前
|
SQL 存储 关系型数据库
MySQL索引原理以及SQL优化
MySQL索引原理以及SQL优化
38 0
|
15天前
|
监控 关系型数据库 MySQL
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
57 0
|
17天前
|
SQL 存储 关系型数据库
【MySQL】七种SQL优化方式 你知道几条
【MySQL】七种SQL优化方式 你知道几条
28 0
|
17天前
|
缓存 NoSQL PHP
百度搜索:蓝易云【如何使用PHP进行数据库索引优化?】
通过以上方法,你可以使用PHP进行数据库索引优化,提高数据库查询性能和整体应用性能。同时,定期维护数据库和优化查询语句也是保持数据库高性能的关键。
37 11
|
19天前
|
机器学习/深度学习 算法 数据库
深入浅出:利用Python与机器学习优化数据库性能
本文介绍了一种创新的方法,结合Python编程语言和机器学习技术,来优化数据库性能。传统的数据库性能优化方法往往依赖于数据库管理员(DBA)的经验和直觉,而本文所提出的方法通过自动化的方式,利用机器学习模型对数据库查询进行分析和优化,从而实现更高效、更智能的数据库性能管理。本文首先介绍了使用Python进行数据库操作的基础知识,然后详细阐述了如何应用机器学习算法来预测和改善数据库查询性能,最后通过一个实际案例展示了该方法的有效性。本文旨在为数据库管理员、开发者以及对数据库性能优化感兴趣的读者提供一种全新的视角和工具。
|
20天前
|
存储 大数据 数据库
深入理解数据库索引优化技术
本文将深入探讨数据库索引优化技术,包括索引的基本概念、常见的索引类型以及如何选择和创建适当的索引。通过了解索引的原理和优化策略,读者可以提高数据库查询性能并优化数据访问效率。
|
21天前
|
SQL 人工智能 运维
数据库基础入门 — SQL排序与分页
数据库基础入门 — SQL排序与分页
16 0