>

SQL决断语句用法和多表查询,PostgreSql品质测试

- 编辑:www.bifa688.com -

SQL决断语句用法和多表查询,PostgreSql品质测试

mysql 各类实际常用疑难sql整理

壹 按天、月做总计,供给每日的升势变化。
sql查询到每天(每月)的值为在此以前全数值的和
福寿齐天如下: 通过op_time与a.op_time比较完毕

select DAY(op_time),(select sum(price) from t_margin_profit where day(op_time)<=day(a.op_time) 
and year(op_time) = '2016' and month(op_time) = '1') 
from
    t_margin_profit a where year(op_time) = '2016' and month(op_time) = '1' 
    GROUP BY DAY(op_time)

②待续…

各类实际常用疑难sql整理 一按天、月做总计,须要每日的长势变化。 sql查询到天天(每月)的值为事先全体值的和 完毕如下: 通过...

一.格式化时间sql语句

# PostgreSql质量测试

本例中作者随便做了两张表,和骨子里不是很合乎,只是想表明sql语句的写法。

## 1. 环境

例壹表格式如下:

  • 版本:9.4.9
  • 系统:OS X 10.11.5
  • CPU:Core i5 2.7G
  • 内存:16G
  • 硬盘:256G SSD

88bifa必发唯一官网 1

## 2. 测试情形

必要:查询出本表,但须要使time字段的时间格式为yyyy-MM-dd,举例:20一三-0八-一三

### 二.一 测试表结构

sql写法:

```sql
/*
Navicat Premium Data Transfer

SELECT u.id,u.userId,u.timeType,DATE_FORMAT(time,'%Y-%m-%d') AS time,secondId FROM `user` u

Source Server : postgresql
Source Server Type : PostgreSQL
Source Server Version : 90409
Source Host : localhost
Source Database : postgres
Source Schema : public

运作结果:

Target Server Type : PostgreSQL
Target Server Version : 90409
File Encoding : utf-8

88bifa必发唯一官网 2

Date: 09/10/2016 16:59:58 PM
*/

二.多表查询(三表查询)


例2三表结构如下:

-- Table structure for bi_object

88bifa必发唯一官网 3


88bifa必发唯一官网 4

DROP TABLE IF EXISTS "public"."bi_object";
CREATE TABLE "public"."bi_object" (
"id" int8 NOT NULL DEFAULT nextval('id_seq'::regclass),
"features" jsonb,
"creator" varchar(64) COLLATE "default",
"type" int2,
"is_deleted" bool,
"gmt_create" timestamp(6) NULL,
"gmt_modify" timestamp(6) NULL
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."bi_object" OWNER TO "postgres";

88bifa必发唯一官网 5

COMMENT ON COLUMN "public"."bi_object"."id" IS '对象ID,主键';
COMMENT ON COLUMN "public"."bi_object"."features" IS '对象特征集结,json结构';
COMMENT ON COLUMN "public"."bi_object"."type" IS '对象类型';
COMMENT ON COLUMN "public"."bi_object"."is_deleted" IS '是还是不是已去除';
COMMENT ON COLUMN "public"."bi_object"."gmt_create" IS '创造时间';

急需:查询出主表,须求在主表中显示username和secondname


sql写法:

-- Primary key structure for table bi_object

SELECT u.id,n.`name`,u.timeType,u.time,s.`name` FROM `user` u,`name` n,`second` s


WHERE u.userId=n.id AND u.secondId=s.id

ALTER TABLE "public"."bi_object" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;
```

运作结果:

### 二.二 测试数据

88bifa必发唯一官网 6

数据量:500万条;

3.when then料定语句

样例数据:
```sql
insert into "public"."Object" ( "gmt_modify", "id", "gmt_create", "type", "features", "creator", "is_deleted") values ( null, '1', '2016-09-10 15:35:32', '1', '{"age": 18, "sex": 1, "city": "Hangzhou", "height": 180, "userId": 1, "weight": 70, "country": "China", "userName": "zhenmo"}', 'zhenmo', 'f') RETURNING *;
```

例三表结构如下:

### 二.3 插入质量测试

88bifa必发唯一官网 7

只有主键索引的景况下,插入500万条记下测试结果

须求:查询上表,若timeType字段值为2时,显示每月的第二天,若timeType字段值为4时,突显每月的第5日

执行sql:
```sql
insert into bi_object(creator, features, gmt_create, is_deleted, type)
select 'zhenmo', row_to_json(bi_user)::jsonb, now(), false, 1 from bi_user
```

sql写法:

实践结果:
```
Affected rows : 5000000, Time: 51.24sec
```

SELECT u.id,u.userId,(CASE WHEN u.timeType=二 THEN '每月第壹天' WHEN u.timeType=四 THEN '每月第陆日' END) AS timeType ,u.time,u.secondId FROM `user` u

平均:97580条/秒

运维结果:

除了主键,单个B-Tree索引的情事下,插入500万条记下测试

88bifa必发唯一官网 8

执行sql:同上

                                                                                                     手艺分享:www.kaige123.com

推行结果:
```
Affected rows : 5000000, Time: 194.78sec
```

目录对于插入数据的**天性影响非常大**,带有索引后插入速度慢了近四倍。

除了主键,单个GIN索引的状态下,插入500万条记下测试

执行sql:同上

实施结果:
```
Affected rows : 5000000, Time: 100.40sec
```

**测试了四次,时间稳定在50秒多或多或少**

### 二.4 查询品质测试

#### 贰.肆.一 无索引查询

执行sql:
```sql
explain analyze select id, features from bi_object where features->>'name' = 'user_4000000';
```

实施结果:
```
Seq Scan on bi_object (cost=0.00..177028.79 rows=25000 width=113) (actual time=1234.339..1520.186 rows=1 loops=1)
Filter: ((features ->> 'name'::text) = 'user_4000000'::text)
Rows Removed by Filter: 4999999
Planning time: 0.044 ms
Execution time: 1520.204 ms
```

**测试了反复,时间稳固在壹.伍秒左右**

#### 二.四.二 创设索引

  • 创建B-Tree索引

执行sql:
```sql
create index idx_bi_object_features_name on bi_object using btree ((features ->> 'name'));
```
实践结果:
```
OK, Time: 183.59sec
```

看得出创立索引**耗费时间比较相当短**

创设jsonb字段单个key的GIN索引

执行sql:
```sql
create index idx_bi_object_features_name on bi_object using gin ((features -> 'name'));

```

执行结果:
```
OK, Time: 26.82sec
```

创制jsonb字段GIN索引,索引class为暗中认可的jsonb_ops

执行sql:
```sql
create index idx_bi_object_features on bi_object using gin (features);
```

进行结果:
```
OK, Time: 85.74sec
```

创造jsonb字段GIN索引,索引class为暗中同意的jsonb_ops

执行sql:
```sql
create index idx_bi_object_features on bi_object using gin (features jsonb_path_ops);
```

执行结果:
```
OK, Time: 74.71sec
```

#### 2.肆.3 使用索引查询

使用B-Tree索引(为features.name单独成立目录)查询

执行sql:
```sql
explain analyze select id, features from bi_object where features->>'name' = 'user_4000000';
```

实施结果:
```
Bitmap Heap Scan on bi_object (cost=582.20..58832.89 rows=25002 width=113) (actual time=0.045..0.045 rows=1 loops=1)
Recheck Cond: ((features ->> 'name'::text) = 'user_4000000'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_bi_object_features_name (cost=0.00..575.95 rows=25002 width=0) (actual time=0.041..0.041 rows=1 loops=1)
Index Cond: ((features ->> 'name'::text) = 'user_4000000'::text)
Planning time: 0.055 ms
Execution time: 0.061 ms
```

加多索引前查询耗费时间:1520.20四 ms,创制索引后查询耗费时间:0.06一ms,查询速度升高:24921倍

使用GIN索引(为features.name单独成立目录,索引class为私下认可的jsonb_ops)查询

执行sql:
```sql
explain analyze select * from bi_object where features->'name' ? 'user_4000000';
```

实践结果:
```
Bitmap Heap Scan on bi_object (cost=66.75..16463.02 rows=5000 width=139) (actual time=0.021..0.022 rows=1 loops=1)
Recheck Cond: ((features -> 'name'::text) ? 'user_4000000'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_bi_object_features_name (cost=0.00..65.50 rows=5000 width=0) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: ((features -> 'name'::text) ? 'user_4000000'::text)
Planning time: 0.062 ms
Execution time: 0.043 ms
```

相相比B-Tree索引,查询速度并不曾晋升广大,不过GIN索引占用的仓储空间却是B-Tree的三倍左右,这样看来使用GIN索引并不是三个好选用。

将全部jsonb字段建立GIN索引(索引class为默许的jsonb_ops)后查询

执行sql:
```sql
explain analyze select * from bi_object where features @> '{"name":"user_4000000"}';
```

施行结果:
```
Bitmap Heap Scan on bi_object (cost=86.75..16470.52 rows=5000 width=139) (actual time=0.100..0.100 rows=1 loops=1)
Recheck Cond: (features @> '{"name": "user_4000000"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_bi_object_features (cost=0.00..85.50 rows=5000 width=0) (actual time=0.091..0.091 rows=1 loops=1)
Index Cond: (features @> '{"name": "user_4000000"}'::jsonb)
Planning time: 0.064 ms
Execution time: 0.123 ms
```

<code>疑问:</code>
时而询问没有选用索引,以为很想获得,在单身为features.name建设构造的GIN索引时,可以应用索引查询,可是为features建设构造目录后,再度查询却不会动用索引,而官方文书档案却说能够支撑'?',操作很吸引:什么鬼??GIN的天性很意外。

执行sql:
```sql
explain analyze select * from bi_object where features->'name' ? 'user_4000000';
```

施行结果:
```
Seq Scan on bi_object (cost=0.00..177030.00 rows=5000 width=139) (actual time=100.820..2059.617 rows=1 loops=1)
Filter: ((features -> 'name'::text) ? 'user_4000000'::text)
Rows Removed by Filter: 4999999
Planning time: 0.051 ms
Execution time: 2059.635 ms
```

将整个jsonb字段创建GIN索引(索引class为jsonb_path_ops)后查询

执行sql:
```sql
explain analyze select * from bi_object where features @> '{"name":"user_4000000"}';
```

实施结果:
```
Bitmap Heap Scan on bi_object (cost=66.75..16450.52 rows=5000 width=139) (actual time=0.019..0.019 rows=1 loops=1)
Recheck Cond: (features @> '{"name": "user_4000000"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_bi_object_features (cost=0.00..65.50 rows=5000 width=0) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (features @> '{"name": "user_4000000"}'::jsonb)
Planning time: 0.065 ms
Execution time: 0.045 ms
```

看一看出来二和四一回实践的询问速度临近,四比二的查询速度有了显眼升高。二和四五次询问不一样在于两点,前者选用的是GIN索引class是jsonb_ops,后者是jsonb_path_ops。后者仅扶助'@>'操作,前者协助'? ?& ?| @>'等操作。遵照法定文书档案的说法jsonb_path_ops的习性要超越jsonb_ops,这点已经获得注解,并且前者占用的空中要小,以本次试验为例,jsonb_ops索引占用空间为68陆M,而jsonb_path_ops占用空间为4四6M。真实景况来看宁可捐躯点空间

jsonb类型的字段的有个别key单独创立B-Tree索引

注解:jsonb类型的字段features的age创建B-Tree索引,然后利用索引检索。

执行sql:

step一:创设目录
```sql
create index idx_bi_object_features_age on bi_object using btree (((features ->> 'age')::integer));
```
step2:查询,注意创设目录时钦赐的连串是integer那么查询也急需将数据转变到integer,其余的int类型如int二是不会使用索引查询的,那一点不友善,提出pg能够协作全数int类型
```sql
explain analyze select * from bi_object where (features->>'age')::integer = 50;
```

实施结果:
```
Bitmap Heap Scan on bi_object (cost=470.19..58840.83 rows=25000 width=139) (actual time=39.967..251.822 rows=50174 loops=1)
Recheck Cond: (((features ->> 'age'::text))::integer = 50)
Heap Blocks: exact=39786
-> Bitmap Index Scan on idx_bi_object_features_age (cost=0.00..463.94 rows=25000 width=0) (actual time=17.719..17.719 rows=50174 loops=1)
Index Cond: (((features ->> 'age'::text))::integer = 50)
Planning time: 0.082 ms
Execution time: 255.228 ms
```

就算是利用了目录,查询速度依旧不完美,看来<code>jsonb字段建构的btree索引效能相较于经常字段的btree来说功效很差</code>,慢了大半4200多倍。

#### 二.四.5 删除测试

删除500万条记下测试

执行sql:
```sql
delete from bi_object
```

施行结果:
```
Affected rows : 5000000, Time: 14.12sec
```

#### 贰.四.陆 表占用磁盘大小

表bi_object记录数四千000,创设单个字段B-Tree索引后合计占用磁盘1055M,在那之中索引258M,索引大概占有用百分之二10伍的半空中。将索引类型改为GIN索引后,磁盘空间占用增大到137陆M,索引占579M。详见下表:

索引 类型 大小(M) 备注
Object_pkey B-Tree 107
idx_bi_object_features_name B-Tree 151
idx_bi_object_features_name GIN 472 索引class为暗许的jsonb_ops
idx_bi_object_features GIN 686 索引class为暗中认可的jsonb_ops
idx_bi_object_features GIN 446 索引class为jsonb_path_ops

GIN索引占用的上空是B-Tree的三倍多。

## 3. 总结

### 三.一 大家的施用境况

大家须求为一类对象动态总括其所持有的特点,具体到一张表中的话,就是2个主键对应多少个列,可是列的个数是不定的,会时有的时候增加。同有时间了为了方便和别的关系表做涉嫌查询,保障高速的询问响应时间,大家要求将其积存在关系型数据库中。由于列的数量动态变化,频繁修改表字段自然不可取,所以想到将其积存到JSON字段中。

### 3.二 能够动用的能源

作者们的应用情形依托于Ali云数加情况,数加的rds只提供了mysql5.6本子(伍.七本子的mysql才支撑json类型)和postgresql 九.四版,postgresql协理json和jsonb类型(后者存储空间小,匡助部分索引操作)。所以我们决定动用postgresql作为数据存款和储蓄工具。

### 三.三 频仍的查询

实质上频仍查询的景况是,供给采用jsonb字段中的有个别key来组成做典型查询,比如:features->'age' >= 20 and features->'age' < 30等

### 叁.四 结论及难题

组合地点的测试,postgresql基本满足了作者们的行使场景须求。在写入速度、轻松询问响应时间、存储动态数据等方面都得以满意。

测试下来开掘的贰个私人商品房的标题就是jsonb类型字段的gin索引的不扶助按限定检索,须求做全表扫描。postgresql的jsonb字段营造gin索引后,只扶助标准查询,不能够做限定检索,那样会招致查询非常快。本地质衡量试下来500万条记下检索时间是二秒多,未来纵然数据量增大,那么很有一点都不小也许引致查询超时。

 

本文由88bifa必发唯一官网发布,转载请注明来源:SQL决断语句用法和多表查询,PostgreSql品质测试