另辟蹊径的一则PostgreSQL数据库SQL优化案例

一 问题现象

早上,收到项目组诉求,业务系统的某个模块从昨天下午3:30左右出现有数据插入失败的情况。希望能从数据库端或者服务器上看看有没有异常现象,并给予支持。

登录到数据库控制台上,经过初步排查发现,看到下述现象:

 

从昨天上午10:29开始,系统频繁出现类似插入SQL,且每次执行耗时1秒。更为糟糕的是,该类型SQL一直持续到今天早上,不停的在执行。

二 分析问题

拎出其中一条SQL进行分析

INSERT
INTO t_ai_prd_history_effective_record
  (
    ID,
    create_time,
    cust_category,
    cust_id,
    cust_name,
    company_id,
    company_name,
    template_no,
    template_name,
    template_field_identifier,
    template_field_name,
    submit_value
  )
SELECT random_string (32),
  now(),
  $1,
  $2,
  $3,
  $4,
  $5,
  $6,
  $7,
  $8,
  $9,
  $10
WHERE NOT EXISTS
  (SELECT 1
  FROM t_ai_prd_history_effective_record r
  WHERE COALESCE (r.cust_category, '')           = COALESCE ($11, '')
  AND COALESCE (r.cust_id, '')                   = COALESCE ($12, '')
  AND COALESCE (r.company_id, '')                = COALESCE ($13, '')
  AND COALESCE (r.template_no, '')               = COALESCE ($14, '')
  AND COALESCE (r.template_field_identifier, '') = COALESCE ($15,'')
  AND COALESCE (r.submit_value, '')              = COALESCE ($16, '')
  )

对应的参数为:

params: $1                                   = 'MY001',
  $2                                             = 'b8168c7513014b0c9769f3d61574833d',
  $3                                             = 'WP MANAGEMENT SERVICES',
  $4                                             = '1BABS7HSS5UH01FE140A000085535171',
  $5                                             = 'KLINIK PERGIGIAN M DENTAL',
  $6                                             = 'MYBL0303',
  $7                                             = 'Expenses',
  $8                                             = 'InvoiceDate',
  $9                                             = 'InvoiceDate(发票日期)',
  $10                                            = '20170614',
  $11                                            = 'MY001',
  $12                                            = 'b8168c7513014b0c9769f3d61574833d',
  $13                                            = '1BABS7HSS5UH01FE140A000085535171',
  $14                                            = 'MYBL0303',
  $15                                            = 'InvoiceDate',
  $16                                            = '20170614'

显然,该SQL的目的是要向表t_ai_prd_history_effective_record进行有条件的插入数据,其条件是表中不存在

COALESCE (r.cust_category, '')           = COALESCE ('DZ001', '')
ai->   AND COALESCE (r.cust_id, '')                   = COALESCE ('b151ad4f86ab4ec5aee8c4cc377e9eb7', '')
ai->   AND COALESCE (r.company_id, '')                = COALESCE ('04cb580238dc49af8bfb46e00e959a1a', '')
ai->   AND COALESCE (r.template_no, '')               = COALESCE ('KJDZ0101', '')
ai->   AND COALESCE (r.template_field_identifier, '') = COALESCE ('ItemQuantity','')
ai->   AND COALESCE (r.submit_value, '')              = COALESCE ('10100$__$6080$__$$__$$__$', '');

的情况下。具体体现在SQL中的WHERE NOT EXISTS。

那么,问题就明了了,应该是出现在NOT EXISTS的子查询上。

接下来,单独看看这个子查询的执行计划:

 ai=> explain analyze SELECT 1
ai->   FROM t_ai_prd_history_effective_record r
ai->   WHERE COALESCE (r.cust_category, '')           = COALESCE ('DZ001', '')
ai->   AND COALESCE (r.cust_id, '')                   = COALESCE ('b151ad4f86ab4ec5aee8c4cc377e9eb7', '')
ai->   AND COALESCE (r.company_id, '')                = COALESCE ('04cb580238dc49af8bfb46e00e959a1a', '')
ai->   AND COALESCE (r.template_no, '')               = COALESCE ('KJDZ0101', '')
ai->   AND COALESCE (r.template_field_identifier, '') = COALESCE ('ItemQuantity','')
ai->   AND COALESCE (r.submit_value, '')              = COALESCE ('10100$__$6080$__$$__$$__$', '');
                                                                                                                                                                                                                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on t_ai_prd_history_effective_record r  (cost=0.00..203817.53 rows=1 width=0) (actual time=1266.116..1267.093 rows=1 loops=1)
   Filter: (((COALESCE(cust_category, ''::character varying))::text = 'DZ001'::text) AND ((COALESCE(cust_id, ''::character varying))::text = 'b151ad4f86ab4ec5aee8c4cc377e9eb7'::text) AND ((COALESCE(company_id, ''::character varying))::text = '04cb580238dc49af8bfb46e00e959a1a'::text) AND ((COALESCE(template_no, ''::character varying))::text = 'KJDZ0101'::text) AND ((COALESCE(template_field_identifier, ''::character varying))::text = 'ItemQuantity'::text) AND (COALESCE(submit_value, ''::text) = '10100$__$6080$__$$__$$__$'::text))
   Rows Removed by Filter: 3193300
 Planning time: 0.129 ms
 Execution time: 1267.133 ms
(5 rows)
ai=>

t_ai_prd_history_effective_record表数据量:

ai=> select count(*) from t_ai_prd_history_effective_record;
  count 
---------
 3193138
(1 row)
ai=>

意料之中,每次执行这个去重的不存在判断,都对这个拥有300多万条数据的t_ai_prd_history_effective_record表执行一次全表扫描,可想而知,执行效率肯定低下,每次耗时1秒多,且随着该表的数据不断增多,插入的速度将变得越来越慢。

这里,可以说是,每一条记录的插入,都将导致下一次插入变得更慢!

三 解决方案

找到了问题的症结,就要着手优化了。终极目标就是怎么可以绕过那个NOT exists的子查询?

经过和BPO部门林朝荣同学讨论,给出下述方案:

  1. 能不能对于t_ai_prd_history_effective_record表的子查询的那6个字段上建立一个唯一性索引,如果可以的话,那么我们就在插入的时候不再需要那个where条件判断去重了,而由这个唯一性索引的约束去做判断,如果插入重复数据的话,就违反约束,报错抛出异常。这样的话,看似解决了这个问题,但是,如果从业务上考虑的话,的确是存在过多的重复数据,如果报错的话,业务人员会在系统上频繁的收到报错信息,不是很友好。
  2. 既然这样的话,那问题就变的更简单了。直接粗暴的把SQL的where not exists的去重判断删掉,先一股脑儿的插入,不论是否重复,然后,写个批处理的SQL,放到晚上再统一把重复的记录删掉,问题不就解决了嘛。

给出一个删除PostgreSQL数据库表重复记录的示例:

ai=> create table t(id int primary key,name varchar(10),addr varchar(10));
CREATE TABLE
ai=> insert into t values(1,'onlyou.com','xiamen');
INSERT 0 1
ai=> insert into t values(2,'apple.com','usa');   
INSERT 0 1
ai=> insert into t values(3,'apple.com','usa');
INSERT 0 1
ai=> insert into t values(4,'google','usa');
INSERT 0 1
ai=> insert into t values(5,'google','usa');
INSERT 0 1
ai=> select * from t;
 id |    name    |  addr 
----+------------+--------
  1 | onlyou.com | xiamen
  2 | apple.com  | usa
  3 | apple.com  | usa
  4 | google     | usa
  5 | google     | usa
(5 rows)
ai=> select t1.id from t t1 where t1.ctid<>(select max(t2.ctid) from t t2 where t1.name=t2.name and t1.addr=t2.addr);
 id
----
  2
  4
(2 rows)
ai=> delete from t where id in(select t1.id from t t1 where t1.ctid<>(select max(t2.ctid) from t t2 where t1.name=t2.name and t1.addr=t2.addr));
DELETE 2
ai=> select * from t;                                                                                                                          
 id |    name    |  addr 
----+------------+--------
  1 | onlyou.com | xiamen
  3 | apple.com  | usa
  5 | google     | usa
(3 rows)
ai=>

四 小结

发现并找到慢SQL的问题所在,进行有针对性的优化改造,解决问题往往不一定难,深入思考一下多尝试。

以上,给出一个优化的思路和方向。

加密SSH连接安全吗? 如何使用?

各位网友,大家好,我是黄伟老师。

今天,我们一起来研究一下关于SSH加密连接的问题、如何使用SSH,以及有哪些注意事项?

第一,我们来说一说什么是SSH?

SSH,全称是Secure Shell,就是一种安全的shell,说白了就是通过SSH来传输的数据是经过加密了的,被认为是一种安全的数据传输方式。

这种经过加密后的安全是针对之前的telnet方式传输数据而言的,通过telnet协议来传输的数据,是传输的明文数据。也就意味着通过这种方式在网络上传输的数据,一旦被截获的话,则数据就被破解了。显然,人们为了安全的考虑,逐渐不再使用或者很少使用telnet方式来传输数据了。

于是,人们就想到,如果把要传输的数据,先经过加密,然后来传输,接收到经过加密之后的数据,再进行解密的话,是不是就相对安全一些呢?

比如,传输和接收数据的双方,事先约定一种加密算法,对要传输的数据进行✖️2018,然后➖620的算术运算。那么,发送方对要发送的数据进行先✖️2018,然后➖620的加密运算,得到密文,然后对密文进行发送。接收方拿到密文数据之后,进行先➕620,然后➗2018的逆运算,就得到原文数据。很显然这种加解密也不足够牢靠,一旦传输的密文被窃取,加密算法被破解的话,数据就被破译了。

这种加密解密用同一套秘钥的算法,被称之为对称加密算法。显然,对称加密算法也不够安全。

于是,人们进一步思考有没有一种安全的加密算法呢?

有。非对称加密,也叫公开秘钥加密的算法应运而生。这种加密算法需要两个密钥:公钥和私钥。其中一个用于加密,另一个用作解密。公钥和私钥均可以用于加解密。使用任何一个密钥对明文进行加密之后的密文,都需要且只能由另外一个密钥才能成功解密,得到明文。由于加密和解密的过程需要两个不同的密钥,所以称之为非对称加密。

说白了,其基本原理就是,用私钥加密的密文必须由公钥才能解密,反之,用公钥加密的密文也只能由私钥才可以解密。

相较于对称加密而言,即使拿到了密文和用于加密的那个密钥,也无法反算出用于解密的密钥,自然也就无法破解,拿不到明文。因此,可以用于公开的那个密钥称之为公钥,可对外发布,不能用于公开的密钥称为私钥,必须严格保管。记住了,任何情况下,即使打死也不能把私钥提供给任何人。一会儿,我们来实际演示一下,如果公开私钥的话有什么危害性?

第二,我们来具体说一说SSH的非对称加密算法。

SSH常用的两种加密方式分别是RSA、DSA算法。

RSA是由名为Ronald Rivest、Adi Shamir、和Leonard Adleman的三位MIT的密码学家于1977年提出的,这种算法的命名是这三位计算机科学家的姓氏首字符拼在一起组成的。这种算法的安全性是建立在基于大整数的因数分解(两个素数的乘积)极其困难的基础上的。为什么这个事儿这么难呢?大家有兴趣可以查看我前面写的一篇文章:

再向前一步或许结果大不同

法国业余数学家费马,曾提出一个猜想,对于非负整数n而言:

是一个质数,当时一直没有人证明出来,直到费马去世之后的67年,由欧拉给反证出来了,证明这个猜想是错误的。

那么,我们说真的是在他之前就没有一个人把n=5代入进去验证吗?

很可能不是,我猜想极有可能是欧拉把n=5代入进去之后,并且对f(5)=4294967297做出了因数分解641*6700417,进而得证的。

既然对于4294967297这个10位数的大数进行因数分解就这么难,那么,如果我们给出一个更大的正整数呢?比如,给出一个1024位的数,2048位的大数呢?岂不是更难,所以,这就是RSA被认为是一种很难破解的安全加密算法。当然了,随着计算机的计算能力越来越强越快,对大数进行因数分解也不是不可能的,RSA-155算法,512位的二进制的的大数在1999年已经被计算机计算出因数分解了,相隔十年以后,也就是2009年,计算机又成功因数分解出了RSA-768算法,768位二进制的的大数。

进而,人们又担心,是不是基于1024位二进制的RSA算法也不够安全呢?是的,所以,现在建议RSA算法推荐采用2048位或以上来加密,相对更安全。

我估计呀,随着Google研究的量子计算机一旦量产问世,这事儿就悬了。不但这事儿悬,估计用量子计算机去破解加密货币诸如bitcoin,EOS的难度,也就类似于你怎么去哄你女朋友开心的难度了….

好了,我们再回到课程主题,我们捎带说一下DSA加密算法。

DSA是Digital Signature Algorithm,用于数字签名和认证的加密方式。其基本原理是

这种算法的安全性是建立在基于整数的有限域离散对数难题的基础上的。其安全性跟RSA相比差不多。

第三,到了我们的动手实际操作环节。一起来动手实践操作,如何使用基于RSA算法的SSH连接?

具体操作部分的详细部分,请见下面的视频。

第四,配套视频

第五,小结

主要讲了SSH安全算法的由来、工作原理以及如何使用SSH连接?

如果你喜欢我的视频,请在微信公众号上关注我:黄伟DBCA


真诚的相信你每一次的帮忙转发和分享,都能给我带来新的读者。也希望我的文字和教程,能帮助到更多的朋友。谢谢收看,再见。

如何免费利用谷歌云平台来搭建学习环境?

各位网友,大家好,我是黄伟老师。

一 今天的课程内容是:

如何免费利用谷歌云平台来搭建学习环境?

二 学完之后,你将收获以下内容:

  1. 自己亲自动手构建一套属于你自己的免费Google云平台;
  2. 在你的云平台上构建满足你自己需求的学习环境,比如,几乎你需要的所有软件都能部署在上面;或者是工作环境,比如选用不同的OS,灵活部署各种应用系统,比方说,从OraclePostgreSQLMySQLSQLservermongodbRedisJavaPythonNGINXGit,大数据的TensorFlow、以及前端的nodeJavaScript等各种各样的相关程序和环境,等等;
  3. 你自己动手一步一步搭建的这套Google云平台将为你提供7*24的高质量服务,你随时随地都能利用它来学习,工作,编写程序,测试验证等。这套云平台不但免费,而且让你触手可及;
  4. 这套云平台环境让你告别虚拟机,不再因为由于本地主机内存、CPU资源或者磁盘空间不够而导致创建本地虚拟机捉襟见肘,或者无法在本地创建虚拟机的现象发生。同时,告别之前想在本地虚拟机环境验证某一程序功能时,需要先开启本地虚拟机的烦恼。取而代之的是,通过网络直接连到云端的服务器,立即开始使用,进行验证和使用。

三 本次课程的适合学习对象:

包括但不限于,互联网从业者,IT程序员,新技术爱好者,一贯的自己动手实践者。

四 免费利用谷歌云平台来搭建学习环境的两个前提条件:

  1. 确保你的电脑可以正常访问Google并注册一个Google账户:
    具体可以参考之前的文章,我们一起奔向互联网上那最遥远的地方:
  2. 一张可以支付外币的信用卡:

用于开通Google cloud platform服务时,Google验证这是一个正常人的请求,而不是机器人的操作。Google会先象征性的从你的信用卡里扣掉1美金,等验证成功之后,会返还那1美金到你的信用卡上;visa或master信用卡均可,前提是你的信用卡必须能支付外币,这里推荐招商银行的全币种外币信用卡,可以直接在线申请,且免年费。同时要求你的这张卡之前没有开通过Google cloud platform服务,否则当你使用同一张信用卡第二次购买GCP服务时,Google在验证的时候,会给予不通过。

五 配套视频

六 小结

主要介绍了如何免费利用Google云平台来构建一套自己的云学习环境,以及2个前提条件和相应的解决方案。

如果你喜欢我的视频,请在微信公众号上关注我:黄伟DBCA

真诚的邀请你可以帮我把这篇文章分享给更多的朋友,谢谢收看,再见。

欢迎订阅我的公众号:黄伟DBCA

2015年的时候,由于那时候我还在做Oracle数据库相关的工作,所以开通的个人公众号取名为『黄伟DBCA』,DBCA源于Oracle数据库的图形化建库工具,Database Create Assistant。

期间,断断续续的发布了一些文章。

的确,更多的订阅者和阅读者将成为我继续写下去的一个动力,当然,我更希望把源于我的一些工作经验、技能和思考,通过公众号传递给更多的人。

公众号内容涵盖我在工作中遇到的一些典型的生产案例、PostgreSQL数据库相关技术、Linux相关知识,以及我的读书笔记。所有文章均为原创。欢迎订阅。

 

再向前一步或许结果大不同

数学史上的一个小故事。

皮埃尔·德·费马Pierre de Fermat,1601年8月17日-1665年1月12日),法国律师,业余数学家,也被称为业余数学家之王。在数学上的成就很高。

有一次,他在看书的时候,提出了一个猜想,对于:

当n为非负整数时,f(n)一定是一个质数(除1和它自身以外,没有其它因数的数)。并且,他做出了下述验证:

接着就写下了他的那句名言:“我发现了一个美妙的证明,但由于书的空白太小而没有写下来”。

在当时没有计算器也没有计算机的情况下,当n=5时,2的32次方是一个不太好计算的数,再加上费马在数学界又很有名气,于是人们就理所应当的认为这个猜想是对的。

直到费马去世后的67年,即1732年,莱昂哈德·欧拉(Leonhard Euler,1707年4月15日-1783年9月18日)瑞士数学家和物理学家,证明了这个猜想是错误的。

那么,欧拉是怎么证明的呢?

欧拉只不过是把n=5代入了公式,并且计算出了f(5)=4294967297=641*6700417.也就是说,4294967297这个数除了1和它自身之外,还有641和6700417这两个因数。那么,它就不是一个质数。至此,反证了费马的这个猜想是错误的。

  • 我们总是习惯于听从别人的意见,尤其是盲从那些权威、专家、领袖的想法,而不去选择遵循自己内心的真实想法,或者自己根据事实逻辑做出的基本面判断的结果。何况,大多数时候,听取的只是所谓的专家权威,事实上他们可能根本就不是,也算不上。
  • 人是一种很容易被「忽悠」的高级动物。我们得知道且承认这个事实,然后尽可能的不被忽悠,或少被忽悠。
  • 工作、生活、学习中,我们肯定都遇到过类似的情况:这个问题我实在搞不定,这件事情我是真的没有太好的办法,那本书我怎么也读不下去了….事实上,我们只要稍微停顿一下,找准对的方向,然后如小卒一样,往前拱一下,事情或许真的会呈现出另外一番景象。

这个数学故事,我是从YouTube上看李永乐老师的教程才知道的。感兴趣的话,可以去关注一下。

直奔YouTube:我们一起奔向互联网上那最遥远的地方啊


欢迎订阅我的公众号