数据库►PostgreSQL

自行测试的1亿条数据中PostgreSQL性能

插入一亿条数据

1
2
3
4
5
test=# insert into tbl_time1 select generate_series(1,100000000),clock_timestamp(),now();
INSERT 0 100000000
Time: 525833.218 ms
约:8.7分钟

COUNT，没有索引，1亿条数据。

1
2
3
4
5
6
7
8
9
test=# select count(1) from tbl_time1;
   count   
-----------
 100000000
(1 row)
Time: 3070658.058 ms
约:51.2分钟

添加主键索引耗时

1
2
3
4
5
test=# alter table tbl_time1 add primary key (id);
ALTER TABLE
Time: 981276.804 ms
约：16.4分钟

再 explain 一下看看

1
2
3
4
5
6
test=# explain select count(id) from tbl_time1;
                                 QUERY PLAN                                 
-----------------------------------------------------------------------------
 Aggregate  (cost=7770150.00..7770150.01 rows=1 width=4)
   ->  Seq Scan on tbl_time1  (cost=0.00..7520150.00 rows=100000000 width=4)
(2 rows)

虽然 id 上有索引，但是依然是使用顺序扫描。

COUNT，有索引(主键），1亿条数据，还要注意有没有 where id > 0 的条件的差别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
这个有 where id > 0
test=#  select count(id) from tbl_time1 where id > 0;
   count   
-----------
 100000000
(1 row)
Time: 244243.112 ms
约：4.071分钟
test=# explain select count(id) from tbl_time1 where id > 0;
                                          QUERY PLAN                                          
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=7644075.89..7644075.90 rows=1 width=4)
   ->  Bitmap Heap Scan on tbl_time1  (cost=623925.90..7560742.56 rows=33333333 width=4)
         Recheck Cond: (id > 0)
         ->  Bitmap Index Scan on tbl_time1_pkey  (cost=0.00..615592.57 rows=33333333 width=0)
               Index Cond: (id > 0)
(5 rows)
 
Time: 0.767 ms
这个无 where id > 0
test=#  select count(id) from tbl_time1;
   count   
-----------
 100000000
(1 row)
Time: 548650.606 ms
约：9.144分钟
test=# explain select count(id) from tbl_time1;
                                 QUERY PLAN                                 
-----------------------------------------------------------------------------
 Aggregate  (cost=7770150.00..7770150.01 rows=1 width=4)
   ->  Seq Scan on tbl_time1  (cost=0.00..7520150.00 rows=100000000 width=4)
(2 rows)
 
Time: 1.253 ms

COUNT 结论：（9.3.5的版本，默认配置）

无论你的数据有没有索引，COUNT都只会进行全表扫描。（条件是没有where， 或有where，但经过查询计划估算代价时，还是决定使用顺序全表扫描）。比如在上面的表，id 有索引。大概原因是说：因为MVCC的影响。

select count(id) from tbl_time1 where id > 0;
经过PG的查询优化器估算时，它最后还是决定使用 Seq Scan 扫描。

1
2
3
4
5
6
7
8
9
test=# explain select count(id) from tbl_time2 where id > 0;
                               QUERY PLAN                               
-------------------------------------------------------------------------
 Aggregate  (cost=21370.00..21370.01 rows=1 width=4)
   ->  Seq Scan on tbl_time2  (cost=0.00..18870.00 rows=1000000 width=4)
         Filter: (id > 0)
(3 rows)
 
Time: 0.872 ms
Postgres中通过需要扫描来计数count(*)的成本比较高. 没有别的办法来来对行数计数并返回结果除了扫描全部数据.

通过修改配置文件调优，时间从 ：51.2分钟—>9.144分钟—>4.071分钟—>1.456分钟

1
2
3
4
5
6
7
8
9
10
11
12
13
14
enable_bitmapscan = off
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
enable_seqscan = off
#enable_sort = on
enable_tidscan = off
实测在如此配置的情况下，indexonlyscan优先!
所谓的 IndexOnlyScan，表示只在索引取数据，不用再定位物理位置后再取数据。性能最快。

通过以上的配置，重启下服务器。再执行查询计划时可以看到：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
test=# explain select count(id) from tbl_time1 where id > 0;
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=118803211.23..118803211.24 rows=1 width=4)
   ->  Index Only Scan using tbl_time1_pkey on tbl_time1  (cost=0.57..118719877.89 rows=33333333 width=4)
         Index Cond: (id > 0)
(3 rows)
Time: 16.033 ms
这时变成了：Index Only Scan 了。
不过耗时还是需要 
test=# select count(id) from tbl_time1 where id > 0;
   count  
-----------
100000000
(1 row)
 
Time: 87501.151 ms
约：1.456分钟

估算表大小

SELECT reltuples FROM pg_class WHERE relname = 'tb_name';

并发方式建立索引

当Postgres建立你的索引的时候， 和其他数据库一样, 在建立索引的时候是会锁表的. 对于小数据量来说没什么关系， 但是通常可能是我们对一个大数据量的表加索引, 这意味着要获得性能改进应用必须收到停机一段时间. 至少那一张表会受影响. Postgres有能力在创建索引的时候不锁表, 通过使用 CREATE INDEX CONCURRENTLY , 例如:

CREATE INDEX CONCURRENTLY idx_salary ON employees(last_name, salary);

当你的索引比你聪明的时候

在所有索引没有被Postgres使用的情况, 大多数情况下你应该相信Postgres, 例如当你查询的结果占所有数据的大部分时候, 它可能不使用索引，因为只扫描全表一次最简单,而不是使用索引做额外的查找.
数据库►PostgreSQL

《数据库查询优化器的艺术》第一章学习笔记

数据库管理系统

数据定义
数据操纵
数据库的运行管理
数据库的建立和维护等

查询优化的目标

使查询优化引擎生成一个执行策略的过程，尽量使查询的总开销（IO，CPU，网络传输等）达到最小。

查询优化技术（广义）

查询重用技术
查询重写规则
查询算法优化技术
并行查询优化技术
分布式查询优化技术
其他方面（如框架结构）的优化技术

查询优化技术（狭义）

查询重写规则
查询算法优化

代数优化（逻辑优化）

主要依据关系代数的等价变换做一些逻辑变换。查询重写规则属于逻辑优化。

非代数优化（物理优化）

主要根据数据读取，表连接方式，表连接顺序，排序等技术对查询进行优化。查询算法优化，属于物理优化。运用了基于代价估算的多表连接算法求解最小花费的技术。

数据库调优

目标是使数据库有更高的吞吐量以及更短的响应时间。它是全局的。而查询优化技术是SQL层面的，局部的优化。

查询重用

指尽可能利用先前的执行结果，以达到节约查询计算全过程的时间并减少资源消耗的目的。
（1）查询结果的重用
在缓冲区分配一块缓冲块，存放该SQL语句文本和最后的结果集，当遇到同样的SQL输入时，可直接把结果返回。它节约了查询计划生成时间和查询执行过程的时间，减少了查询执行全过程的资源消耗。

（2）查询计划的重用
缓存一条查询语句的执行计划及其相应语法树结构。查询计划的重用技术减少了查询计划生成的时间和资源消耗

查询重写规则

它是一种等价转换。即对于任何相关模式的任意状态都会产生相同的结果。目标：
（1）将查询转换为等价的，效率更高的形式。
（2）尽量将查询重写为等价，简单且不受表顺序限制的形式，为物理查询优化阶段提供更多的选择。如：视图的重写，子查询的合并转换等。
重写的主要依据是关系代数。它是查询重写规则的理论支持。
它有3个角度，第4个是物理优化。
（1）语法级
（2）代数级
（3）语义级
（4）物理级：即基于代价估算模型，比较得出代价最小的，是从连接路径中选择代价最小的路径的过程。

主要思路：
1）将过程性查询转换为描述性的查询，如视图重写
2）将复杂的查询（嵌套子查询，外连接，嵌套连接）尽可能转换为多表连接查询
3）将效率低的谓词转换为等价的效率高的谓词（如等价谓词重写）
4）利用等式和不等式的性质，简化 WHERE， HAVING 和 ON 条件

核心是：等价转换，只有等价才能转换。

查询算法优化

查询优化即求解给定查询语句的高效执行计划的过程。

单表结点获取数据的方式有：
（1）直接通过IO获取
（2）通过索引获取数据
（3）通过索引定位数据的位置再经IO到数据块中获取数据
这是从物理存储到内存解析成逻辑字段的过程。

查询计划的策略：
（1）基于规则优化
（2）基于代价优化。
PG和MySQL采取了基于规则和代价估算的查询优化策略。

多表连接优化算法

SYSTEM-R算法
启发式搜索算法 
贪婪算法 
动态规划算法
遗传算法

并行查询优化

单机：找到查询的一个具有最小执行花费的执行计划
并行：寻找具有最小响应时间的查询执行计划

并行查询：
（1）操作内并行：将同一操作，如单表扫描操作，两表连接操作，排序操作等分解成多个独立的子操作，由不同的CPU同时执行。
（2）操作间并行：一条SQL查询语句可以分解成多个子操作，由多个CPU执行。

分布式查询优化

查询策略优化（主要是数据传输策略）和局部处理优化（传统的单结点数据库的查询优化技术）是查询优化的重点。

主要目标：
减少传输的次数和数据量

分布式代价估算模型
总代价 = IO代价 + CPU代价 + 通信代价