伴随着国产数据库的不断崛起，熊大、熊二以及光头强，成立了一家名为“StrongBear"的IT服务公司，其中有一项业务就是为甲方提供数据库技术支持服务。甲方在自己的云服务中大量使用了PostgreSQL数据库。最近，出现了一个奇怪的问题， 业务中某一张表，按某些条件查询特别慢(非常非常的慢)。于是光头强派熊二前去调研，看看能不能帮助解决。
背景
熊二也不多说什么，直接就前去甲方公司，见到了当初跟自己合作的开发人员小玲子。小玲子把那张表的情况说了下，大概是这样的：
有一张初始表，有大量的数据入库，运行了较长的一段时间。但是由于tenantid使用的是uuid，进行Hash分区以后效果不好。数据大量的集中在少数几个分区里头，并且按时间删除记录，效果非常差。这也是小玲子她当初没有做充分调研的结果。
create table mylog(id varchar(36) not null primary key, tenantid bpchar(36) not null, created timestamp, component varchar(64), content text);
create index idx_created_mylog on mylog(created);
create index idx_tenantid_mylog on mylog(tenantid);
-- tenantid 就是租户的id
后来小玲子联系了熊二，让他帮忙进行在线调整一下，使用新的分区方式，从created字段入手，按时间范围分区。这样调整以后，新的表单独运行，效果还真的不错。因为是云服务，整个服务不能停。熊二，稍思考片刻，采用了如下思路：
1、定义一张新表：
create table mylog_v2(id varchar(36) not null primary key, tenantid varchar(36) not null, created timestamp, component varchar(64), content text);
create index idx_created_mylogv2 on mylog_v2(created);
create index idx_tenantid_mylogv2 on mylog_v2(tenantid);
-- 定义好基于created的若干分区
熊二习惯性的为tenantid用上了varchar，而不是bpchar(36)。当时还嘀咕了下，怎么会用这种内置类型。
2、修改业务代码：
新的数据，都往表mylog_v2及其分区中插入。这种日志类型的表，基本上就是插入和查询，以及删除。
为了处理查询，熊二创建了一个视图：
create view v_mylog as select * from mylog union select * from mylog_v2;
这样，只需要把原来查询里的表名换成v_mylog就可以了。删除操作，也基本上是这样，只需要把表名换一下就OK了。
然后，表中只存活一个月的数据。也就是说，部署上去以后，希望一个月以后，原表mylog就没有数据了。那个时候，再把所有引用表mylog的地方去掉，就完成了整个替换，同时又保证了业务不间断的运行。
可是，一些查询怎么就特别特别的慢呢？
小玲子告诉了熊二一些现象：
insert into mylog select md5(random()::varchar), md5(random()::varchar), clock_timestamp(), 'component', 'content' from generate_series(1, 1000);
INSERT 0 1000
insert into mylog_v2 select md5(random()::varchar), md5(random()::varchar), clock_timestamp(), 'component', 'content' from generate_series(1, 1000);
INSERT 0 1000
先直接模拟往两表插入以上这些数据。然后看看查询的效果：
mydb=# explain analyze select * from v_mylog where tenantid = 'd247082937a073543dd314c0302e092c' order by created limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
---
Limit (cost=154.17..154.18 rows=5 width=308) (actual time=4.360..4.364 rows=1 loops=1)
-> Sort (cost=154.17..154.19 rows=10 width=308) (actual time=4.358..4.361 rows=1 loops=1)
Sort Key: v_mylog.created
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on v_mylog (cost=109.00..154.00 rows=10 width=308) (actual time=3.269..4.354 rows=1 loops=1)
Filter: (v_mylog.tenantid = 'd247082937a073543dd314c0302e092c'::bpchar)
Rows Removed by Filter: 1999
-> HashAggregate (cost=109.00..129.00 rows=2000 width=308) (actual time=3.031..3.872 rows=2000 loops=1)
Group Key: mylog.id, mylog.tenantid, mylog.created, mylog.component, mylog.content
Batches: 1 Memory Usage: 633kB
-> Append (cost=0.00..84.00 rows=2000 width=308) (actual time=0.008..0.893 rows=2000 loops=1)
-> Seq Scan on mylog (cost=0.00..27.00 rows=1000 width=96) (actual time=0.007..0.172 rows=1000 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..37.00 rows=1000 width=91) (actual time=0.016..0.508 rows=1000 loops=
1)
-> Seq Scan on mylog_v2 (cost=0.00..27.00 rows=1000 width=92) (actual time=0.012..0.209 rows=1000 loops=1)
Planning Time: 0.144 ms
Execution Time: 4.431 ms
(16 rows)
居然，建在tenantid上的索引都没有用上。两个1000行的表，都执行了全表扫描。难怪生产环境中，遇到了这种查询，奇慢无比。生产环境中的数据量都是1000万行以上。
熊二看了看过去的表定义，不由得冒出一身冷汗。他已经预感到，是自己当时不经意的用了varchar(36)，与原表略有不同的字段类型定义，导致后来的视图查询，该索引始终用不上。甚至连created字段上的索引也没法用上。
只能先在模拟环境中试探一下：
-- 强改, 因为视图有依赖，不让改
mydb=# alter table mylog_v2 alter tenantid type bpchar(36);
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view v_mylog depends on column "tenantid"
把心一横，重建视图....
mydb=# drop view v_mylog;
DROP VIEW
mydb=# alter table mylog_v2 alter tenantid type bpchar(36);
ALTER TABLE
mydb=# create view v_mylog as select * from mylog union select * from mylog_v2;
CREATE VIEW
mydb=# explain analyze select * from v_mylog where tenantid = 'd247082937a073543dd314c0302e092c' order by created limit 5;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
-------
Limit (cost=24.90..24.91 rows=5 width=424) (actual time=0.378..0.381 rows=1 loops=1)
-> Sort (cost=24.90..24.91 rows=6 width=424) (actual time=0.377..0.379 rows=1 loops=1)
Sort Key: mylog.created
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=24.70..24.76 rows=6 width=424) (actual time=0.370..0.372 rows=1 loops=1)
Group Key: mylog.id, mylog.tenantid, mylog.created, mylog.component, mylog.content
Batches: 1 Memory Usage: 32kB
-> Append (cost=0.28..24.62 rows=6 width=424) (actual time=0.119..0.361 rows=1 loops=1)
-> Index Scan using idx_tenantid_mylog on mylog (cost=0.28..8.29 rows=1 width=96) (actual time=0.119..0.120 rows=1 loop
s=1)
Index Cond: (tenantid = 'd247082937a073543dd314c0302e092c'::bpchar)
-> Bitmap Heap Scan on mylog_v2 (cost=4.31..16.24 rows=5 width=207) (actual time=0.238..0.239 rows=0 loops=1)
Recheck Cond: (tenantid = 'd247082937a073543dd314c0302e092c'::bpchar)
-> Bitmap Index Scan on idx_tenantid_mylogv2 (cost=0.00..4.31 rows=5 width=0) (actual time=0.236..0.236 rows=0 lo
ops=1)
Index Cond: (tenantid = 'd247082937a073543dd314c0302e092c'::bpchar)
Planning Time: 0.490 ms
Execution Time: 1.248 ms
(16 rows)
这下索引扫描也确实用上了。扫描的行数大为减少。
可是因为这中间涉及到视图删除以及表结构变更，对业务是有影响的。
机灵的熊二发现，原表mylog在生产环境中，实际上已经没有数据了。因为一个月的时限早就过去了。这样的话，他只需要把v_mylog重新定义一下，定义到表mylog_v2上边就搞定了。
mydb=# drop view v_mylog;
DROP VIEW
mydb=# create view v_mylog as select * from mylog_v2;
CREATE VIEW
mydb=# explain analyze select * from v_mylog where tenantid = 'd247082937a073543dd314c0302e092c' order by created limit 5;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=16.30..16.31 rows=5 width=207) (actual time=0.022..0.023 rows=0 loops=1)
-> Sort (cost=16.30..16.31 rows=5 width=207) (actual time=0.020..0.021 rows=0 loops=1)
Sort Key: mylog_v2.created
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on mylog_v2 (cost=4.31..16.24 rows=5 width=207) (actual time=0.014..0.014 rows=0 loops=1)
Recheck Cond: (tenantid = 'd247082937a073543dd314c0302e092c'::bpchar)
-> Bitmap Index Scan on idx_tenantid_mylogv2 (cost=0.00..4.31 rows=5 width=0) (actual time=0.011..0.012 rows=0 loops=1)
Index Cond: (tenantid = 'd247082937a073543dd314c0302e092c'::bpchar)
Planning Time: 0.246 ms
Execution Time: 0.098 ms
(10 rows)
这样一番操作以后，连业务代码都不用作任何改动。弄完以后，熊二终于松了一口气。
经此次事件，熊二对于表中字段的定义再也不掉以轻心了。两张表的union，如果某字段类型不一致，尽管兼容，即算原表建了索引，最后的union，仍然用不上这个索引 。回来之后，光头强让熊二做一个总结。
小结：
在PostgreSQL中，有一个函数：pg_typeof，非常有用：
mydb=# select pg_typeof('abc');
pg_typeof
-----------
unknown
(1 row)
-- 意味着这种常量，可以让char(), varchar()上的索引起作用
mydb=# select pg_typeof('abc' || 'ddd');
pg_typeof
-----------
text
(1 row)
-- 只能作用于varchar/text类型上的索引
mydb=# select pg_typeof(('abc' || 'ddd') ::bpchar);
pg_typeof
-----------
character
(1 row)
mydb=# select pg_typeof(('abc' || 'ddd') ::char);
pg_typeof
-----------
character
(1 row)
作了转换以后，可以让定长类型char()/bpchar()上的索引起作用。
再加到上边的表定义：
mydb=# select pg_typeof(tenantid) from v_mylog limit 1;
pg_typeof
-----------
character
(1 row)
这样导致的结果就是原表：mylog_v2上的索引用不上。它相当于作了一个转换。
我是【Sean】, 欢迎大家长按关注并加星公众号：数据库杂记。
原文作者：数据库杂记；如有侵权，请联系管理员删除。
最新文章
- PostgreSQL Internals之路 Part-II 第10章 WAL日志
- 从Oracle过渡到PostgreSQL: 理解模式的概念
- PostgreSQL是Oracle的一个可行的替代方案——原因如下
- PostgreSQL Internals之路Part II: Buffer Cache和WAL第9章 Buffer Cache
- 天纵奇才 - "林诗栋"
- 没有PostgreSQL样例数据库,不用担心, 帮你搞定！
- 推荐几本数据库技术图书
- 由浅入深高可用：HAProxy + PostgreSQL配置逐步详解(二)
- PostgreSQL Internals之路 Part-I 第7,8章 冻结、重建表和索引
- 乒坛最新消息引发巨震 - 世界第一王楚钦惨败林诗栋，王曼昱0-4遭横扫，还有孙颖莎4-2稳胜张本美和
