新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT > 数据库 > 百科

SQL优化案例一则

时间:2019-05-14 09:39:25  来源:  作者:

这是学习笔记的第 1978 篇文章

今天优化了几个SQL问题,拿出来两个做下总结和分享。

第一条SQL如下,内容做了删减。

SELECT p.*, m.uid, m.username, m.groupid, ....m.email, m.gender, m.showemail, m.invisible

FROM cdb_posts p

LEFT JOIN cdb_members m ON m.uid=p.authorid

LEFT JOIN cdb_memberfields mf ON mf.uid=m.uid

WHERE p.tid='xxxxx' AND p.invisible='0' ORDER BY first DESC,dateline DESC LIMIT 13250, 50

这条语句的执行效率根据监控,平均时间在9秒,但是在测试的时候,时间执行时间远远大于9秒,我们就暂且按照9秒来估算时间成本吧。

cdb_posts表的数据有3000多万,另外两个表cdb_members,cdb_memberfields的数据量也不小,量级在七百万。

其中索引分布在如下的字段中:

  • 索引字段:cdb_posts.authorid,tid 数据量:3000多万
  • 索引字段:cdb_members.uid 数据量:700多万
  • 索引字段:cdb_memberfields.uid 数据量:3000多万

对于这样一个SQL,按照目前的执行情况,基于LEFT JOIN,肯定是有一个表要“全量”了。

所以整个SQL的关注目标先在于where子句:

p.tid='xxxxx' AND p.invisible='0'

根据测试,这个数据量也相对小一些:

>>SELECT count(*)

-> FROM cdb_posts p

-> LEFT JOIN discuz.cdb_members m ON m.uid=p.authorid

-> WHERE p.tid='6297759' AND p.invisible='0' ;

+----------+

| count(*) |

+----------+

| 29625 |

+----------+

1 row in set (7.27 sec)

所以我们后续的测试会以这个数据作为基础,执行计划如下:

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: p

type: ref

possible_keys: displayorder,idx_tid_fir_authorid,idx_invisible

key: displayorder

key_len: 4

ref: const,const

rows: 59148

Extra: Using where; Using filesort

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: m

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: test.p.authorid

rows: 1

Extra:

*************************** 3. row ***************************

id: 1

select_type: SIMPLE

table: mf

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: test.m.uid

rows: 1

Extra:

3 rows in set (0.00 sec)

从执行计划来看,瓶颈点就在于第1部分了,整个SQL的执行路径类似于下面的形式:

SQL优化案例一则

 

对于这个部分的评估,主要是做了索引的评估,发现改进力度很有限,所以我的注意力放在了逻辑部分,其中cdb_posts是最全面的信息,后续的信息都是以它a来说,既然优化器看不到这个边界,我们可以间接告诉它。

即把cdb_posts缩小为一个派生表:

select * from cdb_posts where tid='xxxx' AND invisible='0' LIMIT 11625, 50

这样的话数据量是绝对可控,而且符合逻辑的。

改造后的语句如下:

SELECT SQL_NO_CACHE p.*, m.uid, m.username, 。。。m.email, m.gender, m.showemail, m.invisible。。。

FROM (

select * from cdb_posts where tid='xxxx' AND invisible='0' LIMIT 11625, 50

)p

LEFT JOIN cdb_members m ON m.uid=p.authorid

LEFT JOIN cdb_memberfields mf ON mf.uid=m.uid

ORDER BY dateline DESC,first asc;

改造后,执行时间为0.14秒,相比之前的方式快了许多。



Tags:SQL   点击:()  评论:()
声明:本站部分内容来自互联网,如有任何版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
零、用户管理:1、新建用户:>CREATE USER name IDENTIFIED BY 'ssapdrow';2、更改密码:>SET PASSWORD FOR name=PASSWORD('fdddfd');3、权限管理>SHOW GRANTS...【详细内容】
2019-07-18   SQL  点击:(3)  评论:(0)  加入收藏
先看结构体:以下代码块是用来连接数据库的通讯过程,要连接MYSQL,必须建立MYSQL实例,通过mysql_init初始化方能开始进行连接.typedef struct st_mysql {NET net; /* Communicati...【详细内容】
2019-07-18   SQL  点击:(4)  评论:(0)  加入收藏
概述sqlserver目前虽然基本上很少用了,不过很多老系统还是用的这个数据库,简单做个了解有备无患。有注意观察的朋友就可以发现sqlserver中总会有几个系统表,那么这几个系统表有...【详细内容】
2019-07-18   SQL  点击:(3)  评论:(0)  加入收藏
1、从概念上来讲,它们是不同的,truncate是DDL语句,会隐式提交,所以,不能回滚,不会触发触发器。delete是DML语句,这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应...【详细内容】
2019-07-17   SQL  点击:(1)  评论:(0)  加入收藏
概述在MySQL数据库中,直到5.7这个版本,开始引入JSON数据类型,在此之前如果想在表中保存JSON格式类型的数据,则需要依靠varchar或者text之类的数据类型,如果在低于5.7版本的数据库...【详细内容】
2019-07-17   SQL  点击:(2)  评论:(0)  加入收藏
概述今天主要分享一下mysql的数据库规范,仅供参考。从基础、命名、表设计、字段设计、索引设计、sql编写、行为规范几个方面做介绍。 基础规范 1、必须使用InnoDB存储引擎说...【详细内容】
2019-07-17   SQL  点击:(1)  评论:(0)  加入收藏
48作者 | Python语音识别来源 | 深度学习与python(ID:PythonDC)不管是机器学习、web开发或者爬虫,数据库都是绕不过去的。那么今天我们就来介绍Python如何Mysql数据库进行连...【详细内容】
2019-07-17   SQL  点击:(0)  评论:(0)  加入收藏
概述今天主要介绍一下mysql数据库一般修改InnoDB redo log事务日志文件大小的步骤,然后用一个实验来演示一下,下面一起来看看吧~基本步骤在MySQL 5.5版本里,如果想修改ib_logfi...【详细内容】
2019-07-16   SQL  点击:(3)  评论:(0)  加入收藏
前言假设现在我们要向mysql插入500万条数据,如何实现高效快速的插入进去?暂时不考虑数据的获取、网络I/O、以及是否跨机操作,本文将在本地进行数据的插入,单纯从mysql入手,把优化...【详细内容】
2019-07-16   SQL  点击:(10)  评论:(0)  加入收藏
概述有朋友问怎么去看连接mysql数据库有哪些用户及对应的IP地址等等信息,所以顺便整理了下这块内容,下面是涉及的一些命令。1、查看当前连接到数据库的用户和Host## 查看当前...【详细内容】
2019-07-15   SQL  点击:(1)  评论:(0)  加入收藏
概述闲来无事,看了下高性能mysql这本书,其中的一些MySQL高级特性写的还不错,在这里总结分享下。01分区表1、分区表限制 一张表最多1024个分区 分区表中无法使用外键约束2、分区...【详细内容】
2019-07-12   SQL  点击:(10)  评论:(0)  加入收藏
对于Mysql常用的SQL语句比如select、create、insert、update、delete、join、order by、group by等等相信大家都不陌生;但对于一些不是很常用却又十分实用的语句在要使用的...【详细内容】
2019-07-11   SQL  点击:(9)  评论:(0)  加入收藏
一、MySQL 优点:体积小、速度快、总体拥有成本低,开源;支持多种操作系统;是开源数据库,提供的接口支持多种语言连接操作 ;MySQL的核心程序采用完全的多线程编程。线程是轻量级的进...【详细内容】
2019-07-11   SQL  点击:(6)  评论:(0)  加入收藏
概述今天主要分享一款MySQL日志分析神器--mysqlsla,对于我们分析mysql数据库的三大日志还不错,这里介绍一下。什么是mysqlsla?Mysqlsla 是daniel-nichter 用perl 写的一个脚本,...【详细内容】
2019-07-11   SQL  点击:(4)  评论:(0)  加入收藏
1、统计函数(聚合函数)AVG() --求平均值COUNT() --统计数目MAX() --求最大值MIN() --求最小值SUM() --求和STDEV() --STDEV()函数返回表达式中所有数据的标准差STDEVP() --S...【详细内容】
2019-07-11   SQL  点击:(3)  评论:(0)  加入收藏
应用程序慢如牛,原因多多,可能是网络的原因、可能是系统架构的原因,还有可能是数据库的原因。那么如何提高数据库SQL语句执行速度呢?有人会说性能调优是数据库管理员(DBA)的事,然...【详细内容】
2019-07-11   SQL  点击:(6)  评论:(0)  加入收藏
SQL Server 系统数据库在安装软件时自动创建,用于协助系统共同完成对数据库的操作;也是数据库运行的基础; 1,master数据库是SQL Server 2012的核心数据库,如果损坏则数据库软件无...【详细内容】
2019-07-10   SQL  点击:(4)  评论:(0)  加入收藏
介绍获取MySQL分区表信息的几种方法。 1. show create table 表名可以查看创建分区表的create语句。 /*!...*/ 是一种特殊的注释,其他的数据库产品当然不会执行。mysql特殊处...【详细内容】
2019-07-09   SQL  点击:(6)  评论:(0)  加入收藏
时间存储时间存储是我们在MySQL中最常用的一种存储类型,MySQL为我们提供了timestamp和datetime两种数据类型,那么这两者有什么区别,又该如何进行选择呢timestamp和datetime的...【详细内容】
2019-07-08   SQL  点击:(19)  评论:(0)  加入收藏
概述前面已经介绍了Oracle如何去获取某用户下的所有表的行数,所以就不介绍了,今天主要分享的是怎么去获取mysql数据库下所有表的数据行数,主要介绍3个方法。1、估算某个数据库...【详细内容】
2019-07-08   SQL  点击:(6)  评论:(0)  加入收藏
推荐资讯
相关文章
栏目更新
栏目热门