新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  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、字符串拼接SELECT CONCAT('My','S','QL') 2、字符串比较SELECT STRCMP('abc','abd'),STRCMP('abc','abc'),S...【详细内容】
2020-03-19   SQL  点击:(3)  评论:(0)  加入收藏
这个是公司技术基础建设的一部分,在以后我会逐步推出更多的技术相关文章,感觉有用的请关注。这个文章是为了规范公司的数据库使用的,如果公司有原来使用的规范可以做下对比。这...【详细内容】
2020-03-18   SQL  点击:(8)  评论:(0)  加入收藏
今天搭建了一个asp+mssql数据库的环境用于安全测试学习1.判断注入点及获取网站信息:sqlmap -u http://192.168.1.30/1/onews.asp?id=8 web server operating system: Windows...【详细内容】
2020-03-18   SQL  点击:(5)  评论:(0)  加入收藏
概述很长一段时间,MySQL 执行 连接 的唯一算法是 嵌套循环算法 ( nested loop algorithm) 的变体 ,但是 嵌套循环算法 在某些场景下非常低效,也是 MySQL 一直被诟病的一个问...【详细内容】
2020-03-18   SQL  点击:(2)  评论:(0)  加入收藏
聚集索引和二级索引每张使用 InnoDB 作为存储引擎的表都有一个特殊的索引称为聚集索引,它保存着每一行的数据,通常,聚集索引就是主键索引。为了得到更高效的查询、插入以及其他...【详细内容】
2020-03-16   SQL  点击:(4)  评论:(0)  加入收藏
行锁的三种算法InnoDB存储引擎有3种行锁的算法,其分别是: Record Lock:单个行记录上的范围 Gap Lock:间隙锁,锁定一个范围,但不包含记录本身 Next-Key Lock:Gap Lock + Record Loc...【详细内容】
2020-03-16   SQL  点击:(4)  评论:(0)  加入收藏
《深入理解Java虚拟机》但要想真的深入理解虚拟机一问肯定远远不够的,但是本文中分三部分对JVM有深入的解析。第1章 走近Java第2章 Java内存区域与内存溢出异常第3章 垃圾收...【详细内容】
2020-03-16   SQL  点击:(6)  评论:(0)  加入收藏
前言随着系统的运行,数据量变得越来越大,单纯的将数据存储在mysql中,已然不能满足查询要求了,此时我们引入Redis作为查询的缓存层,将业务中的热数据保存到Redis,扩展传统关系型数...【详细内容】
2020-03-16   SQL  点击:(8)  评论:(0)  加入收藏
导论笔者的毕业设计是一个与大数据相关的项目,其中遇到了一个将百万级别数据存储到MySQL 的问题,本来是打算使用Spark SQL 的DataFrame 自带的API来直接将数据存储到MySQL 中,...【详细内容】
2020-03-16   SQL  点击:(2)  评论:(0)  加入收藏
本文介绍如何利用python来对MySQL数据库进行操作,本文将主要从以下几个方面展开介绍:1.数据库介绍2.MySQL数据库安装和设置3.Python操作MySQL在Python3.X上安装MySQL驱动创建...【详细内容】
2020-03-15   SQL  点击:(11)  评论:(0)  加入收藏
window7下的mysql的数据;C:\ProgramData\MySQL\MySQL Server 5.1\data如果是mysql同版本的数据库,直接备份这个文件夹的数据即可。如果是不同版本,可以导出sql脚本后再重新导...【详细内容】
2020-03-15   SQL  点击:(8)  评论:(0)  加入收藏
NULL是SQL常见的关键字之一,表示“空,无”的意思。它在SQL中是一种独特的存在,今天来汇总一下与它相关的知识点,看看这些你都知道吗?先贴一下我们的原始数据,是一个只有1列的表,表...【详细内容】
2020-03-15   SQL  点击:(5)  评论:(0)  加入收藏
本文主要针对的是关系型数据数据库 MySql。先简单梳理下 Mysql 的基本概念,然后分创建时和查询时这两个阶段的优化展开。1 基本概念简述1.1 逻辑架构 第一层:客户端通过连接...【详细内容】
2020-03-13   SQL  点击:(10)  评论:(0)  加入收藏
一,首先还是登录到我们的服务器服务器如何登录我在上一节已经讲过了,大家只需要去看我上一节课程即可。 然后通过下面命令行,检测服务器上是否安装过mysqlrpm -qa|grep mysql如...【详细内容】
2020-03-13   SQL  点击:(2)  评论:(0)  加入收藏
需要搞清楚pt-query工具Anemometer基于pt-query-digest将MySQL慢查询可视化percona-toolkit工具的安装安装目的:pt-query-digest是percona-toolkit里面一个工具,其作用就是分...【详细内容】
2020-03-13   SQL  点击:(2)  评论:(0)  加入收藏
MySQL编码过程MySQL出现乱码的原因有很多,一般与character_set参数有关。我们先来看看有哪些参数:SHOW VARIABLES LIKE "character%";Variable_name Value character_set_clie...【详细内容】
2020-03-12   SQL  点击:(7)  评论:(0)  加入收藏
在满足对某张表中数据的增、删、改的情况下,自动触发的功能称之为触发器。视图就是通过查询得到一张虚拟表,然后将标结果保存下来,下次直接使用即可。索引在MySQL中也叫做“键...【详细内容】
2020-03-12   SQL  点击:(7)  评论:(0)  加入收藏
Django 默认使用 SQLite3 数据库,而在稍中大型的项目中大多使用 MySQL,因此我们安装 MySQL 进行后续的开发。步骤 1 MySQL 安装文件地址为 https://pan.baidu.com/s/1eSiy6Fw,...【详细内容】
2020-03-10   SQL  点击:(8)  评论:(0)  加入收藏
MySQL 里有个东西叫 MRR,全称「Multi-Range Read Optimization」。简单说,MRR 通过: 把「随机磁盘读」,转化为「顺序磁盘读」从而: 提高了磁盘读取的性能至于: 为什么要把随机读转...【详细内容】
2020-03-10   SQL  点击:(12)  评论:(0)  加入收藏
一、MHA原理1、简介:MHA(Master High Availability)目前在MySQL高可用方面是一个相对成熟的解决方案,它由日本DeNA公司youshimaton(现就职于Facebook公司)开发,是一套优秀的作为MyS...【详细内容】
2020-03-10   SQL  点击:(7)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条