使用 EXPLAIN ANALYZE 优化查询速度
事情的经过是这样
写了一个消息分发的服务,群内有大概1200人,每个人发一条消息,数据库待分发的消息就增加 1200 条。如果短期内发消息的人特别多且特别频繁。然后每小时清理一次。
这里涉及到两个操作
- 从数据库里拿出消息最近的一部分消息。
- 发完消息之后,从数据库里更新这些消息。
然后发现,当数据库里的数据大于几十万条之后,消息发送极其缓慢。
1 和 2 的的查询都是在10s左右。
于是建立了两个索引之后,1 和 2 的查询速度都降低到了 10ms 以内。
由于瓶颈是在这里,所以问题也就迎刃而解了。
数据库速度分析
- 正常查询
SELECT * FROM t1 WHERE a=1 AND b=2 AND c=3 order by d,e
这个查询,当没有建立索引的时候,数据量一大,就会非常慢。
- 查询分析(EXPLAIN ANALYZE)
其实就是在正常查询的前边加上了 EXPLAIN ANALYZE
就能够获取数据库执行 sql
语句,所经历的过程,以及耗费的时间。
EXPLAIN ANALYZE SELECT * FROM t1 WHERE a=1 AND b=2 AND c=3 ORDER BY d,e;
就会得到如下结果
Limit (cost=6.82..6.83 rows=1 width=287) (actual time=0.040..0.041 rows=0 loops=1)
-> Sort (cost=6.82..6.83 rows=1 width=287) (actual time=0.039..0.040 rows=0 loops=1)
" Sort Key: d, e"
Sort Method: quicksort Memory: 25kB
Index Cond: (a = 1)
Filter: ((b = 2) AND (c = 3))
Planning Time: 0.695 ms
Execution Time: 0.065 ms
可以看到,这里其实只用到了 a (假设他是 pk),这一个字段的索引,数据量大了,就会非常非常慢。
所以,这里的解决方案也是讲 a/b/c/d/e 这五个字段全部进行索引
。这样的话,这条sql
语句的执行,就是毫秒级的了。
CREATE INDEX t1_a_b_c_d_e_idx ON t1(a,b,c,d,e);
执行这条语句,就会给 t1 这个表,添加 a/b/c/d/e
的索引,同时用 a/b/c/d/e
进行查询的时候,就会优先使用这个索引,从而达到毫秒级的查询速度。
最后,我们再用添加完索引之后的表,再进行 EXPLAIN ANALYZE
一下。
EXPLAIN ANALYZE SELECT * FROM t1 WHERE a=1 AND b=2 AND c=3 ORDER BY d,e;
Limit (cost=0.41..8.42 rows=1 width=308) (actual time=0.015..0.015 rows=0 loops=1)
-> Index Scan using t1_a_b_c_d_e_idx on t1 (cost=0.41..8.42 rows=1 width=308) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: ((a = 1) AND (b=2) AND (c=3))
Planning Time: 0.123 ms
Execution Time: 0.034 ms
可以看到,这个查询路径更短,而且一上来首先就直接使用了 t1_a_b_c_d_e_idx
这个索引来进行查询,后边的就是在索引之后的结果里操作的,这样非常快了。