原始记录一次性能优化过程

    起因是协同中有个界面，当数据量达到5w以上的时候，载入速度比较慢。

    当时想这个问题很奇怪啊，才5W数据，怎么会造成性能问题呢。经过抓取sql看执行时间，得到其中一个sql确实很慢，达到了12837.277 ms。这是不可忍耐的:

SELECT DISTINCT
	A .ID,A .Name,A .a,A .b,A .c,A .d,A .e,A .f,A .g,A .h,A .i,A .j,A .kid,A .l
FROM
	 A,
	 C
WHERE
A. STATE = 0
AND A.d = -6546434384760714573
AND A.ID = C.aId
AND (
	C.mId IN (-5162051908097979680, -4684993734047936633, -4188876460025819562, -2445024670931664111, -2127697530593523428, -1730833917365171641, 1640843220652215198, 3967511645675736983, 4078688053696283097, 4585915779813822190, 6053039406954661992, 8708984262145166406, 8775698007915447638, 9009299994434554372)
	OR A.kId = 4585915779813822190
)
ORDER BY
	A .top_Sequence DESC,
	A .issue_Time DESC

LIMIT 6

    其中A表的id是主键，在state ,d,kId 字段以及top_Sequence和issue_Time 字段都有索引；C表在MId和aId也有索引。C表数据量是A表的N倍(N>5)。

    在postgre中执行计划如下：

Limit  (cost=220071.59..220073.04 rows=6 width=79) (actual time=12806.229..12806.441 rows=6 loops=1)
  ->  Unique  (cost=220071.59..238499.62 rows=76317 width=79) (actual time=12806.228..12806.439 rows=6 loops=1)
        ->  Sort  (cost=220071.59..221300.13 rows=491414 width=79) (actual time=12806.226..12806.345 rows=161 loops=1)
              Sort Key: a.top_sequence, A .ID,A .Name,A .a,A .b,A .c,A .d,A .e,A .f,A .g,A .h,A .i,A .j,A .kid,A .l
              Sort Method: external merge  Disk: 107552kB
              ->  Hash Join  (cost=9117.45..129943.80 rows=491414 width=79) (actual time=131.803..2268.255 rows=1116474 loops=1)
                    Hash Cond: (c.aid = a.id)
                    Join Filter: ((c.mid = ANY ('{-5162051908097979680,-4684993734047936633,-4188876460025819562,-2445024670931664111,-2127697530593523428,-1730833917365171641,1640843220652215198,3967511645675736983,4078688053696283097,4585915779813822190,6053039406954661992,8708984262145166406,8775698007915447638,9009299994434554372}'::bigint[])) OR (a.kid = 4585915779813822190::bigint))
                    ->  Seq Scan on c  (cost=0.00..42803.06 rows=2205306 width=16) (actual time=0.036..422.028 rows=2205306 loops=1)
                    ->  Hash  (cost=7194.48..7194.48 rows=76317 width=79) (actual time=80.471..80.471 rows=76723 loops=1)
                          Buckets: 1024  Batches: 16 (originally 8)  Memory Usage: 1025kB
                          ->  Bitmap Heap Scan on a  (cost=1435.73..7194.48 rows=76317 width=79) (actual time=5.576..36.533 rows=76723 loops=1)
                                Recheck Cond: (A.d = (-6546434384760714573)::bigint)
                                Filter: (state = 0)
                                ->  Bitmap Index Scan on idx_d  (cost=0.00..1416.65 rows=76317 width=0) (actual time=5.350..5.350 rows=76723 loops=1)
                                      Index Cond: (A.d = (-6546434384760714573)::bigint)
Total runtime: 12837.277 ms

    可见在最上面的sort之前实际用时2268.255ms，在Filter之前用时422.028ms 这两项比较耗时。

    考察数据库中的实际数据后，看到a表中state的状态很少，而且state=0的数据大于总数据量的90%，还有A.d字段不同值也不多(测试时少于100)。这两个字段的索引基本没有起作用。先去掉试试。

    再次执行后，速度提升2000ms左右。总耗时约10000ms。

    再次看执行计划，可以看到基本都是sort在耗时。引起sort的是order by 和distinct 。但order by 不能去掉，因为界面要取排序后的数据展示。而distinct可以通过改写sql规避。改写后的sql：

SELECT 
	A .ID,A .Name,A .a,A .b,A .c,A .d,A .e,A .f,A .g,A .h,A .i,A .j,A .kid,A .l
FROM
	A
WHERE
	A. STATE = 0
AND A.d = -6546434384760714573
AND A.ID in(select C.aId from C  where  C.aId = A.ID 
	     AND (
                   C.mId IN (-5162051908097979680, -4684993734047936633, -4188876460025819562, -2445024670931664111, -2127697530593523428, -1730833917365171641, 1640843220652215198, 3967511645675736983, 4078688053696283097, 4585915779813822190, 6053039406954661992, 8708984262145166406, 8775698007915447638, 9009299994434554372)
			OR A.kId = 4585915779813822190
		)
	)
ORDER BY
	A .top_Sequence DESC,
	A .issue_Time DESC

LIMIT 6

执行计划如下：

Limit  (cost=2664060.25..2664060.26 rows=6 width=79) (actual time=1066.189..1066.190 rows=6 loops=1)
  ->  Sort  (cost=2664060.25..2664155.65 rows=38159 width=79) (actual time=1066.187..1066.187 rows=6 loops=1)
        Sort Key: a.top_sequence, a.issue_time
        Sort Method: top-N heapsort  Memory: 17kB
        ->  Index Scan using idx_bbsart_fh on a  (cost=0.00..2663376.26 rows=38159 width=79) (actual time=7.383..987.514 rows=76703 loops=1)
              Index Cond: ((A.d = (-6546434384760714573)::bigint) AND (state = 0))
              Filter: (SubPlan 1)
              SubPlan 1
                -> Index Scan using Idx_aid on c (cost=0.00..69.16 rows=3 width=8) (actual time=0.011..0.011 rows=1 loops=76723)
                      Index Cond: (c.aid = a.id)
                      Filter: ((mid = ANY ('{-5162051908097979680,-4684993734047936633,-4188876460025819562,-2445024670931664111,-2127697530593523428,-1730833917365171641,1640843220652215198,3967511645675736983,4078688053696283097,4585915779813822190,6053039406954661992,8708984262145166406,8775698007915447638,9009299994434554372}'::bigint[])) OR (a.issue_user_id = 4585915779813822190::bigint))
Total runtime: 1066.264 ms

    总用时为1066.264ms。没有了external merge 这个最大的耗用。这时增加对order by中两个字段的组合索引也没太大效果。

    也许觉得这样的速度也算可以了，毕竟还有个order by在哪儿摆着，不可能太快的吧。

    错！还可以更加快！

    因为后面几个in都是前台传的数据，不能更改为其他的。而且，看计划主要性能消耗是在7W多数据的loop项，而且执行计划中的“Sort Method: top-N heapsort  Memory: 17kB”是耗性能的大户。但是每次loop只消耗0.011ms，因此如何使这个loop次数减少是关键。因为我们只需要前6项，其他数据可以不需要loop出的。

    想起最开始的时候把state和d 两个单独字段的索引去掉了，是否可以利用下这两个字段呢，因此又加上了，不怎么见效果，反而慢了。

    再看order by，这两字段都是有索引的，但是没走，岂不是浪费，因为查出的数据量太大吗？NO，因为利用不充分！

    建立组合索引(state, d, top_sequence, issue_time)。在看执行计划，其他没变，但是loop成6次了(其他不贴了)：

  (cost=0.00..69.16 rows=3 width=8) (actual time=0.006..0.006 rows=1 loops=6)

  Total runtime: 0.105 ms

    神一样的速度啊。这才对嘛！

    大多数程序员在写hql的时候都是实现功能就好了，而这种limit操作使得这种不当的操作更加的隐蔽(大点的数据量或者数据量不太大但数据分配不均匀才能发现问题)。而这里的order by更加剧了问题的凸显。这是好事，否则到后期再发现恐怕造成的后果更加严重。

    造成最初查询很慢的原因是结果没用上应该用的索引，直接对order by 两个字段建组合索引没效果的原因是没使结果集变小。最终优化结果只需0.1ms是因为：首先是走了索引，其次是结果集变小了，因为有了索引，仅需扫描满足条件的6行就可以得到结果(实际扫描行数要比6多一些，看表中数据分布而定)。

    总结就是：尽量让查询使用索引，多表连接用尽量INNER JOIN，避免使用DISTINCT，灵活使用相关子查询(上面的最优查询如果没有重复的记录也可以用inner join代替)，尽量少做重复性的查询工作。

    END