新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  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   点击:()  评论:()
声明:本站部分内容来自互联网,如有任何版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
概述操作系统及MySQL数据库的实时性能状态数据尤为重要,特别是在有性能抖动的时候,这些实时的性能数据可以快速帮助你定位系统或MySQL数据库的性能瓶颈,那么有哪些重要的实时性...【详细内容】
2019-05-14 09:39:25   点击:(3)  评论:(0)  加入收藏
在程序设计当中,我们很多场景下都会用 group by 关键字。比如在分页读取数据时,为了避免重复扫描记录,这就是必须要使用 group by 了。比如我们使用如下 DDL 创建表:CREATE TAB...【详细内容】
2019-05-14 09:39:25   点击:(8)  评论:(0)  加入收藏
方法一cmd 到mysql bin目录下用如下命令:mysqldump --opt -h192.168.0.156 -uusername -ppassword --skip-lock-tables databasename>database.sql把ip改成localhost就可以的...【详细内容】
2019-05-14 09:39:25   点击:(5)  评论:(0)  加入收藏
概述Mysql binlog日志有三种格式,分别为Statement,MiXED,以及ROW!这三种格式之间有什么区别呢?下面先介绍下各自的优缺点。ROW日志中会记录成每一行数据被修改的形式,然后在slav...【详细内容】
2019-05-14 09:39:25   点击:(6)  评论:(0)  加入收藏
这是学习笔记的第 1978 篇文章今天优化了几个SQL问题,拿出来两个做下总结和分享。第一条SQL如下,内容做了删减。SELECT p.*, m.uid, m.username, m.groupid, ....m.email, m.g...【详细内容】
2019-05-14 09:39:25   点击:(6)  评论:(0)  加入收藏
MySQL中没有Rank排名函数当我们需要查询排名时,只能使用MySQL数据库中的基本查询语句来查询普通排名。尽管如此,可不要小瞧基础而简单的查询语句,我们可以利用其来达到Rank函数...【详细内容】
2019-05-14 09:39:25   点击:(5)  评论:(0)  加入收藏
查看表是否被锁:直接在mysql命令行执行:show engine innodb statusG。查看造成死锁的sql语句,分析索引情况,然后优化sql.然后show processlist,查看造成死锁占用时间长的sql语...【详细内容】
2019-05-14 09:39:25   点击:(7)  评论:(0)  加入收藏
分片(类似分库)分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题。Shard这个词...【详细内容】
2019-05-14 09:39:25   点击:(7)  评论:(0)  加入收藏
mysql高并发的解决方法有:优化SQL语句,优化数据库字段,加缓存,分区表,读写分离以及垂直拆分,解耦模块,水平切分等。高并发大多的瓶颈在后台,在存储mysql的正常的优化方案如下:(1)代码中...【详细内容】
2019-05-14 09:39:25   点击:(11)  评论:(0)  加入收藏
数据库的索引就像一本书的目录一样,它可以快速定位你所需要的信息。下面来详细说一下MySQL的索引结构。常见索引类型Hash 索引Hash索引的底层实现是由Hash表来实现的,非常适合...【详细内容】
2019-05-14 09:39:25   点击:(3)  评论:(0)  加入收藏
推荐资讯
相关文章
栏目更新
栏目热门
'); })();