起因

在自己写的一段代码中，使用到了Redis。代码的逻辑是这样子的，先取出数据，然后判断，再然后将该数据进行自减。代码如下：

1
2
3
4
5
6
7
8
9
10
11
12

public boolean isOK(String invitationCode, boolean isSub) {
       ValueOperations<String, Object> vo = redisTemplate.opsForValue();
       Object count = vo.get(key + invitationCode);
       if (count != null && Integer.parseInt(count.toString()) >= 1) {
           if (isSub) {
               vo.increment(key + invitationCode, -1);
           }
           return true;
       } else {
           return false;
       }
   }

分析

在提交代码的时候，总监复查代码时，提出这段代码可能在并发的时候出现问题：比如有两条线程，都进入到了if(sub){xxx}这段代码，这个时候，就造成了重复多次自减的问题。比如，如果该value是5，那应该减到0就不会再减了，但是这段代码在高并发情况下，可能会出现减到-1的情况。这虽然对于现有的业务影响不大，但是，如果是对于其他的一些比较敏感并且需要精确的数据时，就需要特别注意了。

关键是之前一直没有怎么意识到这段代码可能会造成的问题。之前一直认为，对于局部变量是不会出现多线程问题的，这倒是没有错，但关键是如果是从其他地方传过来而且在多处地方允许被使用的情况下，就需要注意多线程问题。

第一次看动画，看到也会莫名其妙地流泪，感触颇深，情到深处无言；爱到深处只能默默地在心里流泪。
人生太多悲天悯人的事，许多时候真的只是只可意会，无法言语。父母之爱无言，只有自己默默体会，默默地感受了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

麦太：我的小傻猪，长大了，懂事了。
麦太：你看....
麦兜：啊？你买了我叫你不要买的那些号码啊？
麦太：我只会买你选的号码
麦太：你的手呀，从小就又厚，又软，不是霉猪手
麦兜：妈妈！
傍白：她说....
麦太：全世界的人不信你，我也会信你；
麦太：全世界的人不爱你，我也会爱你；
麦太：我爱你爱到心肝里；
麦太：我信你信到脚趾头。
傍白：我说....
傍白：我还能说什么？我爱我妈妈
麦兜：妈妈！
面对命运，妈妈可以输的，已经通通输光；
面对命运，妈妈可以赢的，都赢了回来；
她把赢的都给了我，把输了的留给自己。
我没有离去
只是换了个地方 
活在爱我的人心里

PG对各表的统计信息：pg_stats

资源来源：PostgreSQL 9.3.1 中文文档 —— Chapter 47. 系统表 —— 47.68. pg_stats

成本因子

因为PostgreSQL是基于代价模型来选择最优的执行计划的，而成本因子则是计算代价模型的最重要参数。（代价=CPU代价+IO代价+数据传输[如网络]代价）

在PG9.4默认情况下的成本因子如下：（这些值可以在 postgresql.conf 文件里修改的）

1
2
3
4
5
6
7
8

# - Planner Cost Constants -
#seq_page_cost = 1.0                    # measured on an arbitrary scale。扫描一个数据块（一页）的成本（IO成本）
#random_page_cost = 4.0                 # same scale as above。随机获取一个数据块（一页）的成本（IO成本）
#cpu_tuple_cost = 0.01                  # same scale as above。获取一行数据的CPU成本
#cpu_index_tuple_cost = 0.005           # same scale as above。获取一个索引项的CPU成本
#cpu_operator_cost = 0.0025             # same scale as above。每个操作符的CPU成本
#effective_cache_size = 4GB             #评估操作系统缓存可能使用的内存大小。用于评估索引扫描的开销，大的值倾向使用索引，小的值倾向使用全表扫描。一般设置为“物理内存 - shared buffers - 内核和其他软件占用的内存”。

注意：SSD的随机读和顺序读差别不是太大，这时可以缩小 seq_page_cost 和 random_page_cost 之间的大小。使random_page_cost趋向于seq_page_cost。

关于 effective_cache_size 特别说明一下

资料来源

effective_cache_size用于在Linux操作系统上报告内核缓存的大小，我想强调一下它在postgresql.conf配置里的重要性。
effective_cache_size

不像其他内存那样是设置已经分配好的控制内存，effective_cache_size用于告诉优化器在内核里有多少cache（读缓存）。这对于决定代价高的索引扫描方式是非常重要的。优化器知道 shared_buffers 大小，但是不知道内核缓存大小，从而影响到代价非常高的磁盘访问。

内核缓存大小改变比较频繁，所以，正常地运行一段时间的系统负载，然后使用该内存值去设置 effective_cache_size。这个值不必是非常完美的，仅仅只是粗略地估计还有多少内核内存，相当于是shared buffers的二级缓存。

explain 输出及详解

explain 语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

test=# \h explain;
Command:     EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
 
where option can be one of:
 
    ANALYZE [ boolean ] 是否真正执行
    VERBOSE [ boolean ] 显示详细信息
    COSTS [ boolean ] 显示代价信息
    BUFFERS [ boolean ] 显示缓存信息
    TIMING [ boolean ] 显示时间信息
    FORMAT { TEXT | XML | JSON | YAML } 输出格式，默认为 text

图解：

注意

看执行计划，是从最底层开始往后看的。即先从 节点1，再到节点2，最后到节点3。
而且，上一级节点的成本，是包含了下一级的成本的。比如：节点2的启动成本和结束成本是已经包含了节点1的启动成本和结束成本的，由此可以得出一个结论：就是上一级节点的启动成本和结束成本永远不会比下一级的小。

再次强调一下，每个节点的估算的总启动或结束成本只是平均每次loops的平均成本，所以最后的总成本还要乘以loops次数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

test=# explain(analyze, timing, verbose, buffers,costs) select max(sum) from ( select count(*) as sum from tgroup group by point) as t;
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=235363.15..235363.16 rows=1 width=8) (actual time=4898.900..4898.900 rows=1 loops=1)
   Output: max((count(*)))
   Buffers: shared hit=12770 read=49578
   ->  HashAggregate  (cost=235363.04..235363.09 rows=5 width=4) (actual time=4898.890..4898.891 rows=5 loops=1)
         Output: count(*), tgroup.point
         Group Key: tgroup.point
         Buffers: shared hit=12770 read=49578
         ->  Seq Scan on public.tgroup  (cost=0.00..177691.36 rows=11534336 width=4) (actual time=0.045..1643.984 rows=11534336 loops=1)
               Output: tgroup.id, tgroup.age, tgroup.point
               Buffers: shared hit=12770 read=49578
 Planning time: 0.170 ms
 Execution time: 4898.982 ms
(12 rows)
Time: 4899.758 ms
test=#

估算成本的计算公式

全表扫描时计算：

公式：total cost = relpages * seq_page_cost + reltuples * cpu_tuple_cost。

1
2
3
4
5
6

test=# select relpages, reltuples from pg_class where relname = 'tgroup';
-[ RECORD 1 ]----------
relpages  | 62348
reltuples | 1.15343e+07
Time: 0.751 ms

1
2
3
4
5

test=# show seq_page_cost ;
-[ RECORD 1 ]-+--
seq_page_cost | 1
Time: 28.848 ms

1
2
3
4
5

test=# show cpu_tuple_cost;
-[ RECORD 1 ]--+-----
cpu_tuple_cost | 0.01
Time: 0.460 ms

如上节点1执行计划，是全表扫描：
177691.36 = 62348 * 1 + 1.15343e+07 * 0.01

1
2
3
4
5
6

test=# select 62348 * 1 + 1.15343e+07 * 0.01;
-[ RECORD 1 ]-------
?column? | 177691.00
Time: 39.815 ms
test=#

这个与结果相符。

全表顺序扫描并过滤，代价公式为:

Cost = seq_scan_cost*relpages + cpu_tuple_cost*reltuples + cpu_operator_cost*reltuples

扫描的方式

1
2
3
4
5
6
7
8
9
10
11

enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on #索引扫描
enable_indexonlyscan = on #只读索引扫描
enable_material = on #物化视图
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on

虽然我们不能强制指定PostgreSQL按我们写的SQL来执行（无视优化器），但是可以通过改变某些的查询方式代价从而影响PostgreSQL的查询优化器的选择。
这时，我们可以从上面的扫描方式中将其修改为 off（其实不是强制不能以某种方式扫描，将其设置为 off时，只是将该项的扫描代价提高到非常大的值，从而让PostgreSQL尽可能避免使用该方式来进行扫描，但不是绝对的，如果其他的方式比off的代价更大，那PostgreSQL还是会选择代价最小的来执行的），这就为我们提供了非常好的控制我们SQL的扫描方式。

昨天晚上，和同事讨论了一个SQL的问题，是如何选择根据某字段分组，然后取出MAX COUNT(XX) 值的数据出来。例如数据是：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

test=# select * from tgroup;
id | age | point
----+-----+-------
  1 |   1 |    11
  2 |   1 |    32
  3 |   2 |    32
  4 |   2 |    13
  5 |   2 |    33
  6 |   2 |    38
  7 |   3 |    38
  8 |   2 |    38
  9 |   2 |    38
 10 |   2 |    38
 11 |   2 |    38
(11 rows)
 
test=#

现在要选择出根据 POINT 分组里包含个数最大的值。大概意思是：MAX(COUNT(*)) FROM tgroup GROUP BY POINT;
所以，一开始，我们的SQL语句是（失败）：

1

select MAX(COUNT(*)) FROM tgroup group by point;

然而，我们得出的错误是：

1

aggregate function calls cannot be nested

原来，聚集函数是不能嵌套调用的。

然后又想到能不能使用子查询来完成。SQL如下（成功） ：

1

select max(sum) from ( select count(*) as sum from tgroup group by point) as t;

再来一条，不使用子查询，而是使用ORDER BY 结合 LIMIT 来完成，SQL语句如下（成功):

1

select count(*) as sum from tgroup group by point order by sum desc limit 1;

最后使用PG里的CTE表达式最容易理解（成功）：

1

with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;

那他们的性能是何呢？测试了一千一百五十多万的数据。每条SQL性能如何？

1
2
3
4
5
6
7

test=# select count(*) from tgroup;
  count  
----------
11534336
(1 row)
 
test=#

使用子查询的性能（POINT没有索引）

1
2
3
4
5
6
7

test=# select max(sum) from ( select count(*) as sum from tgroup group by point) as t;
   max  
---------
6291456
(1 row)
 
Time: 3055.716 ms

ORDER BY 结合 LIMIT（POINT没有索引）

1
2
3
4
5
6
7
8

test=# select count(*) as sum from tgroup group by point order by sum desc limit 1;
   sum  
---------
6291456
(1 row)
 
Time: 3047.152 ms
test=#

使用CTE表达式（POINT没有索引）

1
2
3
4
5
6
7

test=#  with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;
   max  
---------
6291456
(1 row)
 
Time: 25675.005 ms

后面为POINT添加索引，速度只有CTE表达式的加快了一倍（添加索引其实也不太科学，POINT的数据分布不均匀，重复的数据比较多，因为是通过 insert into select 的方式来生成大量数据的，只是想看一下添加索引后的效果）:

1
2
3
4
5
6
7
8

test=#  with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;
   max  
---------
6291456
(1 row)
 
Time: 11735.775 ms
test=#

其他两种方式，并没有什么变化。看执行计划，其他两种依然是使用Seq Scan的方式，而添加了索引后，CTE的方式使用了 CTE Scan + IndexOnlyScan的方式。

1
2
3
4
5
6
7
8
9
10
11

test=# explain  with cte as (select count(*) over (partition by point) from tgroup) select max(count) from cte;
                                                QUERY PLAN                                               
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=955503.54..955503.55 rows=1 width=8)
   CTE cte
     ->  WindowAgg  (cost=0.43..695980.98 rows=11534336 width=4)
           ->  Index Only Scan using tgroup_point on tgroup  (cost=0.43..522965.94 rows=11534336 width=4)
   ->  CTE Scan on cte  (cost=0.00..230686.72 rows=11534336 width=8)
(5 rows)
 
Time: 0.909 ms

总结

看来没有 WHERE 或其他条件过滤数据而且数据量非常大的情况下，不适宜使用CTE表达式，因为它本质是一个一次性视图，生成一张这么大的视图，性能也快不到哪里去(可能使用物化视图会好点,不过没有测试过）。在大量数据情况下，还是使用普通的全表扫描比使用生成CTE再全表扫描来得快。这也应验了之前翻译篇文章的强调：CTE表达式的作用，真的不是为了加快查询速度，而仅仅是为了方便。冏 ~~。

为何Integer不能转换为String

原文

因为 String 和 Integer 不是在同一个对象阶层。

1
2
3
4

      Object
     /      \
    /        \
String     Integer

当你尝试强制转换时，仅仅会在同一个对象阶层转换。比如：

1
2
3
4
5
6
7

     Object
     /
    /
   A
  /
/
B

在这种情况，(A)objB 或者 (Object)objB 或者 (Object)objA 可以进行转换。

正如其他人已经提到，将integer转换成String可以使用以下方法：

基本类型的整型时使用：String.valueOf(integer)或者Integer.toString(integer) 来转换成String。
Integer对象型时使用：Integer.toString()转换成String

写代码过程中，居然遇到这种错误（在使用Spring 来操作 Redis 经常出现，但百思不得其姐。和同事讨论时才发现，原来Redis在底层永远是保存String的，即使在写代码的时候是写成 set(String, Object)。这时，如果get这个缓存出来，而且想要的是整型时，就会报java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.String错误。），出来工作差不多2年了，这种错误的本质原因，直到今天才明白。实在是太惭愧了。很可能是被所有对象可以以字符串的形式表示而导致的，以为所有的对象在使用 (String)obj时，会使用 obj.toString()，所以才造成这种认为理所当然的错觉。

今天和总监、同事又讨论起关于Session共享的解决方案问题，讨论到因为Tomcat自带的Session机制在集群时难以做到真正的集群。因为使用Tomcat自带的Session机制，难以做到在集群中节点共享，一般是通过Nginx反向代理使用Hash、固定IP等解决方案，并不能避免单节点崩溃时不能继续提供服务的问题。虽然这种可以解决压力的问题，但是当一直为某IP或通过Hash来分配服务的某台服务器挂了，则它负责服务的客户就都访问不了了（Session失效，只能重新调度分配到其他服务器，这时要重新生成会话）。讨论到的可用的解决方案是Cookie + Redis，然后又讨论了Cookie的安全性问题。然后同事问了下HttpOnly这个在浏览器里打勾的作用，然后自己按以前了解到的资料来回答了一下，大概是说：不能通过Javascript来修改带有HttpOnly属性的Cookie，只能通过服务器来修改。但是看到总监却可以通过JS来修改带有HttpOnly属性的Cookie，这让我产生了怀疑自己的正确性。

不过还好，事后向总监确认了一下，原来他是通过删除旧的带有HttpOnly属性的Cookie，然后才用JS添加一个同名同值没有HttpOnly属性来测试。所以，我之前说的大概是对的，但是不够系统，所以再次查了下资料来系统整理一下，与君分享。

下面两个属性都属于Cookie安全方面考虑的。这要视浏览器或服务端有没有支持。

Secure

Cookie的Secure属性，意味着保持Cookie通信只限于加密传输，指示浏览器仅仅在通过安全/加密连接才能使用该Cookie。如果一个Web服务器从一个非安全连接里设置了一个带有secure属性的Cookie，当Cookie被发送到客户端时，它仍然能通过中间人攻击来拦截。

HttpOnly

Cookie的HttpOnly属性，指示浏览器不要在除HTTP（和 HTTPS)请求之外暴露Cookie。一个有HttpOnly属性的Cookie，不能通过非HTTP方式来访问，例如通过调用JavaScript(例如，引用 document.cookie），因此，不可能通过跨域脚本（一种非常普通的攻击技术）来偷走这种Cookie。尤其是Facebook 和 Google 正在广泛地使用HttpOnly属性。

原文

版本策略

我们始终建议所有用户都运行最新可用的次版本号的发行版，无论你正在使用哪个主版本的PostgreSQL。

PostgreSQL主版本包含新特性并且大概一年发行一次。主版本是通过增加第一个或第二个版本数字来标识的，例如：9.1 到 9.2

主版本通常改变内部系统表和数据文件的格式。这些改变通常是非常复杂的，所以，我们不保证所有存储数据会向后兼容。主版本一般使用 备份/恢复 数据库或者使用 pg_upgrade模块来升级。

次版本是版本号的第三部分的数字。例如：9.2.3 到 9.2.4。PostgreSQL团队仅在修复bugs时才会增加次版本号。所有用户都尽可能应该升级到最新的次版本号。虽然升级总是会有风险，PostgreSQL次版本升级仅修复那些经常遇到、安全性以及数据崩溃的bugs，以减少升级的风险。社区认为，不升级比升级更危险。

升级次版本，不要求备份和恢复数据；仅仅只需停止数据库服务器，安装更新的二进制文件，然后重新启动服务器即可。对于某些发行版本，可能需要手工升级，所以，在升级之前，请必须阅读发行版的提示。

PostgreSQL发行版支持策略

PostgreSQL目的是对主版本号完整地支持5年。

当一个发行版不再受支持时，我们可能（这取决于我们提交者的决定）会继续为源代码进行打上关键的补丁。但项目不会产生正式发行或者二进制包，但是更新后的源代码可以从我们的代码控制系统里获取。

该策略会在尽最大努力的基础上实行。在极端情况下，它可能在计划的生命周期内就不再被支持了；例如，如果在给定的主版本上发现有一个严重的bug超出了代码稳定性或者要牺牲程序序兼容性来解决的。在这种情况下，该主要版本就要提前退休了。

注意事项

1
2

1. 如果原先的数据库安装了第三方的扩展，请在升级的新版本服务器上也先安装好这些第三方扩展(contrib)
2. 相应的表空间配置也要与原先的一致

升级步骤

1
2
3
4
5
6
7
8

1. 以PG管理员的身份运行以下命令来备份所有数据库信息（包括用户，角色，等）
    pg_dumpall > outfile
2. 源码安装PG，请参考安装步骤：
http://dreamer-yzy.github.io/2014/12/03/PostgreSQL%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0%EF%BC%88%E4%B8%80%EF%BC%89/
3. 恢复DB（记得将最新版本的DB服务器运行起来先），运行以下命令
    psql -f outfile postgres

打完收工。

原文地址

从不同的重大版本升级PostgreSQL（例如，从9.1升级到9.3），基本上有三种方法：

用 pg_dump 升级

如果可以的话，首先推荐的方法是使用新版本（9.3）进行二进制备份一个老（9.1）版本数据，然后在新版本创建的集群中恢复数据。

这种途径，通常，是比较慢的，但是也是最实用的。使它进行得更加快的方法之一是，使用并发。为了并行备份任务，你可以这样子做：

1

$ pg_dump --format=directory --jobs=4 --no-synchronized-snapshots --file=/path/to/mydump mydatabase

你可以为每个数据库进行这样子的备份，并调整 --jobs=4 参数值到任何值（测试从2到CPU核心数，并看看哪个更快）。当然，在备份期间，没人应该连接到数据库，任何的修改将会导致备份中断（因为非安全选项 --no-synchronized-snapshots）

之后，你可以使用pg_restore来恢复到新的实例中：

1
2

$ createdb <options> -T template0 mydatabase
$ pg_restore --exit-on-error --jobs=4 --dbname=mydatabase /path/to/mydump

之后, 建议在数据库上执行ANALYZE命令：

1

$ vacuumdb --analyze-only mydatabase

（如果你可以等待时间的话，仅仅运行 --analyze 来同时进行 VACUUM 数据库并更新可视数据字典）

使用 pg_upgrade 升级

另一种方法，是使用扩展包中的 pg_upgrade。它提供了一个非常快速的方法来升级PostgreSQL，就是使用 --link 方法。
使用之前，你必须备份整个数据目录，因为在 --link 模式下，如果出错，你可能丢失所有数据（包括新旧数据）。并且，请完整地阅读文档，特别是底部的提示（pg_upgrade有许多限制）

使用基于复制工具的触发器来升级

另一种升级版本的选项，是使用基于触发器的复制工具 。比如Slony, Bucardo和Londiste。

这个选项可能用于最少停机时间，但是也是最难操作的。

这样做的话，需要建立一个 master-slave，主库是你当前版本（9.1），从库是新版本（9.3）。之后，等待第一次同步（系统仍然在生产环境），之后你关闭所有连接到数据库（停机时间从这里开始），等待从库赶上，然后提升（从库）到主库，然后重定向所有客户/应用程序到新版本数据库。打完收工。

Slony文档提供了一步一步地教你使用Slony来升级PostgreSQL.

应该选择哪个？

Well, as always depends, resuming:
好了，这要取决于什么，总结一下：

dump+restore是最可靠的，但通常也是最慢的一种（尽管，并行性可以带来更好的结果）

pg_upgrade是对于比较少的停机时间来说是最好的选择之一（如果你能使用的话，看看它的限制）。它通常只需要花数分钟的时间，甚至对于大的数据库也是这样。

触发器复制，毫无疑问是最少停机时间（几乎为0）的做法，但是它真的好难实行，并且我仅仅建议专家（PostgreSQL和复制工具二者都非常熟悉的专家）

希望我可以帮到你。祝你好运。

上星期请教了条SQL为什么没有使用到索引的问题，引起了我对SQL执行顺序的疑惑，查了不少资料，收集到比较认可的答案如下，引用资料也在后面标识了。有什么不同的见解，还请大牛指出。

各部分的执行顺序

1
2
3
4
5
6
7
8
9
10
11

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP(LIMIT)

来源资料：
Stackoverflow whats-the-execute-order-of-the-different-parts-of-a-sql-select-statement

Stackoverflow order-of-execution-of-the-query

MSDN order-of-execution-of-sql-queries