• 1
  • 2
  • 3
  • 4
  • 5
mssql数据库问题 首 页  »  帮助中心  »  数据库  »  mssql数据库问题
2016年应掌握的十个Postgres技巧
发布日期:2016-4-29 21:4:22

  Postgres作为一款开源的对象—关系数据库,一直得到许多开发者喜爱。最近,Postgres正式发布了9.5版本,该版本进行了大量的修复与功能改进。本文将分享10个Postgres使用技巧,希望能让开发者能更加灵活与高效地使用这个数据库。

  下面是译文:

  很多人会选择去阅读一些新书或者一些新技术来充实自己。下面将推荐一些Postgres技巧与技能给大家,这些技巧能够帮助你更加灵活方便地使用Postgres。若你觉得这些技巧会对你产生帮助,你可以选择订阅 Postgres weekly,本周都回发布一些Postgres最新的资讯与技术干货。

  一.CTEs——Common Table Expressions

  CTE允许你做一些很棒的事情,如递归查询,即便是用在一些最简单的语句操作上,CET都会有很出色的表现。CTE可以认为是在单个SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似,具体表现在不存储为对象,且只在查询期间有效。CTE与派生表的不同之处在于,它可以自引用,还可以在同一查询中引用多次。这样开发者就可以更容易地创建可读查询。

  开发者在创建SQL语句的时候,很多时候会有很多行,有的时候甚至超过上百行,而通过使用4-5个CETs后,语句会缩短很多,这样就很容易提高语句的可读性,尤其是对于新人来说。

  二.安装一个.psqlrc

  若安装了bashrc、vimrc等文件,那为什么不对Postgres做些同样的操作呢?下面这些设置都非常棒,你不妨试试,如一下步骤:

  1.通过默认\x auto来获得更好的格式;

  2.使用\pset null ¤,让null更形象化;

  3.默认设置\timing on来显示SQL执行时间;

  4.自定义提示\set PROMPT1'%[3[33;1m%]%x%[3[0m%]%[3[1m%]%/%[3[0m%]%R%# ';

  5.根据名称来保存你常用的运行语句。

  下面是笔者的psqlrc设置,如下所示:

  \set QUIET 1

  \pset null '¤'

  -- Customize prompts

  \set PROMPT1 '%[3[1m%][%/] # '

  \set PROMPT2 '... # '

  -- Show how long each query takes to execute

  \timing

  -- Use best available output format

  \x auto

  \set VERBOSITY verbose

  \set HISTFILE ~/.psql_history- :DBNAME

  \set HISTCONTROL ignoredups

  \set COMP_KEYWORD_CASE upper

  \unset QUIET

  三. 通过pg_stat_statements来查看需要进行索引的地方

  pg_stat_statements可能是开发者提高数据库性能最有价值的工具。一旦启用(还有extension pg_stat_statements),它便会自动记录数据库的所有查询记录以及它们所花费的时间。这样,你就很容易优化查询语句,提高性能。如以下语句:

  SELECT

  (total_time / 1000 / 60) as total_minutes,

  (total_time/calls) as average_time,

  query

  FROM pg_stat_statements

  ORDER BY 1 DESC

  LIMIT 100;

  当然,会因为这样付出一些性能代价,但是对比其所带来的性能提升简直微乎其微。在这篇文章里可以读到更多关于Postgres性能方面的东西。

  四.ETL有点慢,用FDWs

  若有大量的微服务或不同的应用程序,那么很可能需要很多不同的数据库来支持它们。默认情况是创建一些数据仓库并通过ETL连接,但是这样做有时候太重度了。在这个时候,你只需将数据库一次性集中在一起,或者在少数情况下,外部数据封装器可以允许你跨多个数据库查询,比如Postgres到Postgres,或者是Postgres 到Mongo或Redis数据库之类。

  五. array与array_agg

  在开发应用程序的时候,很少会完全不用arrays,而在数据库中同样如此。Arrays可以看作是Postgres里的另一个数据类型,并且拥有一些杀手级应用,比如博文标签这些。

  但即使你不把arrays当做数据类型使用,也常常需要像一个array那样汇总一些数据,中间用逗号隔开。类似下面这样,你可以轻松汇总用户清单,如下所示:

  SELECT

  users.email,

  array_to_string(array_agg(projects.name), ',')) as projects

  FROM

  projects,

  tasks,

  users

  WHERE projects.id = tasks.project_id

  AND tasks.due_at > tasks.completed_at

  AND tasks.due_at > now()

  AND users.id = projects.user_id

  GROUP BY

  users.email

  六.慎重使用materialized views

  你可能不熟悉materialized views(物化视图),materialized views是包括一个查询结果的数据库对像。因此,它是一些查询或“view”的一个物化的或基本的快照版本。在最开始的物化版本中,会在Postgres建立一个常请求,但是整体是不可用的。那是因为当你锁定事务的时候,有可能会阻碍一些其它读取和活动。

  现在已经好很多,但是仍然缺乏一些开箱即用的工具来进行刷新。这也就意味着你必须安装一些调度任务或者cron作业来定期刷新物化视图。若你目前正在开发一些报告或者BI应用程序,那么你还是需要使用物化视图的。它们的可用性正在不断提升,所以,Postgres已经知道如何自动化刷新它们。

  七.窗口函数

  可能窗口函数(Windows fuction)仍然是SQL中较复杂且很难理解的东西。总而言之,它们会让你排序一个查询结果,然后进行一行到玲一行的计算,若没有SQL PL,这些东西会很难做。不过,你可以做一些非常简单的操作,比如排名,基于某些值对结果进行排序;复杂些的,比如计算环比增长数据。

  八.针对数据透视表的一个更简单方法

  在Postgres中,Table_func通常是作为计算一个数据透视表的引用方式。然而不幸地是,这个使用起来相当困难的,更为基础的用法是与原始SQL一起使用。在Postgres 9.5中已经进行了改进,用起来会方便很多。但是在此之前,你汇总每个条件的结果不是false就是true,最后合计为更简单的推理,如一下代码:

  select date,

  sum(case when type = 'OSX' then val end) as osx,

  sum(case when type = 'Windows' then val end) as windows,

  sum(case when type = 'Linux' then val end) as linux

  from daily_visits_per_os

  group by date

  order by date

  limit 4;

  大家可以前往Dimitri Fontaine的博客查看具体示例。

  九.PostGIS

  PostGIS可以说是所有GIS数据库中最好的一个了。实际上,开发者获得的所有Postgres标准会使它更加强大——一个最好的例子是来自Postgres近年来的GiST索引,它给PostGIS提供了极大的性能提升。 若你现在正在做一些与地理空间数据有关的事情,并且需要一些比earth_distance扩展更好用的工具,那么PostGIS就是你最佳选择。

  十.JSONB

  从Postgres 9.2开始,Postgres的每个版本中都有JSON的身影,在每个新版本功能都有所提升,并正在逐步完善成一个更加完美的库。在最新发布的9.5版本中,JSONB在psql中的输出也更具可读性。

  原文地址:http://www.craigkerstiens.com/2015/12/29/my-postgres-top-10-for-2016/

  以后会恒信关于mssql的相关信息,关注mssql的朋友敬请期待。