数据库优化 - SQL优化-阿里云开发者社区

开发者社区> 飘渺Jam> 正文

数据库优化 - 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优化的过程及优化技巧。


优化案例


表结构



CREATE TABLE `a`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_id`   bigint(20)                                       DEFAULT NULL,
    `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    `gmt_create`  varchar(30)                                      DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `b`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_name` varchar(100) DEFAULT NULL,
    `user_id`     varchar(50)  DEFAULT NULL,
    `user_name`   varchar(100) DEFAULT NULL,
    `sales`       bigint(20)   DEFAULT NULL,
    `gmt_create`  varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `c`
(
    `id`         int(11) NOT NULLAUTO_INCREMENT,
    `user_id`    varchar(50)  DEFAULT NULL,
    `order_id`   varchar(100) DEFAULT NULL,
    `state`      bigint(20)   DEFAULT NULL,
    `gmt_create` varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);


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



select a.seller_id,
       a.seller_name,
       b.user_name,
       c.state
from a,
     b,
     c
where a.seller_name = b.seller_name
  and b.user_id = c.user_id
  and c.user_id = 17
  and a.gmt_create
    BETWEEN DATE_ADD(NOW(), INTERVAL  600 MINUTE)
    AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.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



alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `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改写等操作


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


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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Vite2 + Vue3 + TypeScript + Pinia 搭建一套企业级的开发脚手架【值得收藏】
Vite2 + Vue3 + TypeScript + Pinia 搭建一套企业级的开发脚手架【值得收藏】
3 0
【Typescript入门手册】类型进阶
【Typescript入门手册】类型进阶
12 0
写业务代码有成长机会吗
一乐兄弟写过一篇[做业务系统如何成长为架构师],深有同感,包接口的例子可以往下说说。各家都在搞开放API ,好不好用只有用过才知道。比如加签验签,有没有不同语言的client demo可以参考,参数说明是否清晰等。
13 0
多点生活的分布式服务框架DSF(下)
多点生活的分布式服务框架DSF(下)
10 0
面试题:说一说es6新增方法
面试题:说一说es6新增方法
22 0
祝贺!我的同事李飞飞当选ACM Fellow、IEEE Fellow
因在数据库查询处理和优化以及云数据库系统方面所做出的卓越贡献而入选
18 0
数据库中间件 MyCAT源码分析 —— PreparedStatement 重新入门
数据库中间件 MyCAT源码分析 —— PreparedStatement 重新入门
13 0
求解质量熵
摘要:软件系统的稳定性,主要决定于整体的系统架构设计,然而也不可忽略编程的细节,正所谓“千里之堤,溃于蚁穴”,一旦考虑不周,看似无关紧要的代码片段可能会带来整体软件系统的崩溃。我将和大家聊一聊软件质量稳定性之殇,分多篇刊发。
7 0
软件质量稳定性之殇
摘要:软件系统的稳定性,主要决定于整体的系统架构设计,然而也不可忽略编程的细节,正所谓“千里之堤,溃于蚁穴”,一旦考虑不周,看似无关紧要的代码片段可能会带来整体软件系统的崩溃。我将和大家聊一聊软件质量稳定性之殇,分多篇刊发。
8 0
什么是前端开发?什么是后端开发?
什么是前端开发?什么是后端开发?
20 0
+关注
飘渺Jam
飘渺Jam,CSDN博客专家, 一名写代码的架构师,做架构的程序员,可以通过 jianzh5 与我联系,咱们一起聊技术!
108
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载