深海游弋的鱼 – 默默的点滴

null值在in/not in的陷阱

背景

在某个项目统计数据时，发现以外丢掉了部分数据，仔细查找，是null值在in/not in出现了判断问题。

问题

在统计数据时，需要去除某些值，使用了not in逻辑。

select null not in ('1', '2')

这行代码，要完成的逻辑是，null 不属于 '1' 或者 '2'。

想当然认为应该返回 true，实际上返回了null，null值映射为false。

最终统计丢失了这一部分null值。

继续测试

继续测试了各种null使用in/not in的例子

select null in ('1', '2', null);

select '3' not in ('1', '2', null);

select null not in ('1', '2');

都出现了与想当然不符合的结果

该问题的详细原理可以参考这篇文章 http://www.itpub.net/thread-1325582-1-1.html

小结

null值不应该参与in/not in逻辑判断。

GreenPlum-查看表占用磁盘大小

背景

最近使用GreenPlum时，因为大量实验，遗留了很多或临时或正式的表，存储捉襟见肘。准备清理表时，第一件事就是查看表占用磁盘大小，从网上找了一些方法，比较靠谱的是这两篇

https://www.cnblogs.com/gobird/archive/2012/04/11/2442846.html

https://my.oschina.net/u/347414/blog/544187

使用

查询库占用磁盘大小

select pg_size_pretty(pg_database_size('MyDatabase'));

查询表占用磁盘大小

普通表

select pg_size_pretty(pg_relation_size('relation_name')) ;

分区表

使用上面的语句去查分区表，会得到0bytes，就使用上文中创建的函数

-- Function: calc_partition_table(character varying, character varying)

-- DROP FUNCTION calc_partition_table(character varying, character varying);

CREATE OR REPLACE FUNCTION calc_partition_table(v_schemaname character varying, v_tablename character varying)
  RETURNS bigint AS
$BODY$
DECLARE
    v_calc BIGINT := 0;
    v_total BIGINT := 0;
    v_tbname VARCHAR(200);
    cur_tbname cursor for select schemaname||'.'||partitiontablename as tb from pg_partitions
   where schemaname=v_schemaname and tablename=v_tablename;
BEGIN
    OPEN cur_tbname;
    loop
        FETCH cur_tbname into v_tbname;
        if not found THEN
            exit;
        end if;
        EXECUTE 'select pg_relation_size('''||v_tbname||''')' into v_calc;
        v_total:=v_total+v_calc;        
    end loop;
    CLOSE cur_tbname;
    RETURN v_total;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION calc_partition_table(character varying, character varying) OWNER TO gpadmin;

-- Function: calc_partition_table(character varying, character varying)

-- DROP FUNCTION calc_partition_table(character varying, character varying);

CREATE OR REPLACE FUNCTION calc_partition_table(v_schemaname character varying, v_tablename character varying)

RETURNS bigint AS

$BODY$

DECLARE

v_calc BIGINT := 0;

v_total BIGINT := 0;

v_tbname VARCHAR(200);

cur_tbname cursor for select schemaname||'.'||partitiontablename as tb from pg_partitions

where schemaname=v_schemaname and tablename=v_tablename;

BEGIN

OPEN cur_tbname;

loop

FETCH cur_tbname into v_tbname;

if not found THEN

exit;

end if;

EXECUTE 'select pg_relation_size('''||v_tbname||''')' into v_calc;

v_total:=v_total+v_calc;

end loop;

CLOSE cur_tbname;

RETURN v_total;

end;

$BODY$

LANGUAGE plpgsql VOLATILE;

ALTER FUNCTION calc_partition_table(character varying, character varying) OWNER TO gpadmin;

其中 gpadmin是你的username

然后使用select查询，如

select calc_partition_table ('public', 'test_partition_table')

PostgreSQL 提供了多个系统管理函数来查看表，索引，表空间及数据库的大小

函数名	返回类型	描述
pg_column_size(any)	int	存储一个指定的数值需要的字节数（可能压缩过）
pg_database_size(oid)	bigint	指定OID的数据库使用的磁盘空间
pg_database_size(name)	bigint	指定名称的数据库使用的磁盘空间
pg_indexes_size(regclass)	bigint	关联指定表OID或表名的表索引的使用总磁盘空间
pg_relation_size(relation regclass, fork text)	bigint	指定OID或名的表或索引，通过指定fork('main', 'fsm' 或'vm')所使用的磁盘空间
pg_relation_size(relation regclass)	bigint	pg_relation_size(..., 'main')的缩写
pg_size_pretty(bigint)	text	Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units
pg_size_pretty(numeric)	text	把以字节计算的数值转换成一个人类易读的尺寸单位
pg_table_size(regclass)	bigint	指定表OID或表名的表使用的磁盘空间，除去索引（但是包含TOAST，自由空间映射和可视映射）
pg_tablespace_size(oid)	bigint	指定OID的表空间使用的磁盘空间
pg_tablespace_size(name)	bigint	指定名称的表空间使用的磁盘空间
pg_total_relation_size(regclass)	bigint	指定表OID或表名使用的总磁盘空间，包括所有索引和TOAST数据

mysql查询条件-不区分大小写

项目中遇到一个bug，查了一下问题在于mysql查询条件不区分大小写

比如 test表里面存储了两列数据

col0	col1	col2
tmp	1	1
TMP	2	2

查询语句 select * from test where col0 = 'tmp'

两行都能搜索出来。第一次拿到这一个结果，我的内心是这样的：WTF、EXM、你TMD是来逗我的吧

但事实摆在面前，不能不认怂。下一步究其根本，这个答案很靠谱

以下引自：https://blog.csdn.net/Veir_123/article/details/73730751

Mysql默认的字符检索策略：utf8_general_ci，表示不区分大小写；utf8_general_cs表示区分大小写，utf8_bin表示二进制比较，同样也区分大小写。（注意：在Mysql5.6.10版本中，不支持utf8_genral_cs！！！！）

创建表时，直接设置表的collate属性为utf8_general_cs或者utf8_bin；如果已经创建表，则直接修改字段的Collation属性为utf8_general_cs或者utf8_bin。

直接修改sql语句，在要查询的字段前面加上binary关键字即可。
-- 在每一个条件前加上binary关键字
select * from test where binary col0 = 'tmp';

-- 将参数以binary('')包围
select * from test where col0 = binary('tmp');

sql-按条件统计非重复值，count(distinct case when)使用

背景

项目中，遇到一个统计需求，从某张表中按照条件分别统计。刚开始想到用union all的写法，太臃肿，后来使用count(distinct case when)解决此问题

count

数据统计中，count出现最频繁

最简单的用法

select count(*) from table where ....

select count(distinct xx) from table where ...

但最简单的用法也有其深刻的地方，比如这里其实有3种写法，count(1)、count(*)、count(字段)，它们有什么区别呢？

count(1) 和 count(*)

count(1)和count(*)差别不大，使用count(*)时，sql会帮你自动优化，指定到最快的字段。所以推荐使用count(*)

count(*) 和 count(字段)

count(*)会统计所有行数，count(字段)不会统计null值

count(case when)

条件统计，即对某个table分条件统计，比如表test_db，有一个字段user_id(可能重复), gender(man、women)，需要统计man和women的人数

可以使用where分别统计

select count(distinct user_id) as man_cnt from test_db where gender = 'man'

select count(distinct user_id) as women_cnt from test_db where gender = 'women'

也可以使用按条件统计

select count(distinct case gender = 'man' then user_id end) as man_cnt

, count(distinct case gender = 'women' then user_id end) as woman_cnt

from test_db

sql中!=与的区别

背景

sql中表示不等于关系时，有两种写法!=、<>，抱着刨根问底的精神，看看这两个符号到底有什么不同

标准答案

https://stackoverflow.com/questions/18015422/what-is-difference-between-and-in-sql-server

搬运过来

这两个符号没有任何区别，你使用哪个都一样。

Microsoft的文档中，这两个符号的意义相同，都是 Not Equal To

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/not-equal-to-transact-sql-exclamation?view=sql-server-2017

但，<>是ANSI 99 SQL标准中的定义，!=不是。所以，不是所有的DB引擎都支持!=。

最终推荐使用<>。

mysql-使用联合索引

背景

项目使用mysql数据库，某张表数据量2000W左右，比较大，在组合查询时发生超时，需要优化。

优化

查询优化常用方法就是建立索引

查询语句样例：查询在20181001-20181010时间范围内A=a且B=b的数据

select * from table where A = 'a' and B = 'b' and Date > '20181001' and Date < '20181010'

按照查询建立了一个组合索引 idx_query(A, B, Date)，查询仍然报超时。然后开始研究索引原理，最后更改索引中列的顺序为idx_query(B,A,Date)解决。

---------------------------------------------------------------------------------------------

上面只是简单的项目背景，下面从原理细细解读索引

索引原理

首先，出问题以后再建立索引，是一种不好的习惯。排查、定位、解决问题会耗费很多开发时间和精力。

当然，不充分思考就给大多数列建立单列索引，给所有查询建立联合索引，是一种更坏的习惯。毕竟删除索引风险远高于添加索引。

使用索引需要成本：

空间成本：索引是占空间的，大表的联合索引占用空间不可忽略
时间成本：使用不对的索引会浪费时间
更新成本：增删改操作只要跟索引有关系，就需要更新索引

索引适合在返回大表中很小一部分数据时使用，很小当然越小越好，5%以下还可以，1%以下更好，如果表的数据量非常大，千万级别，返回行数要小到0.1%以下

从很大表中用联合条件查询出很小的数据，等价于count(distinct 联合条件) 的值很大，这个值被称为Cardinality，索引适合度=Cardinality/表记录数。

索引误区

多个单列索引(如idx_A, idx_B)不等同于联合索引(idx_A_B)

两个联合索引里的列顺序不同，这两个联合索引也不等价，适用场景不同

联合索引idx_A_B能够覆盖的场景

A = ？ and B = ？
A = ？ and B in (?, ?, ?)
A = ?
A in (?,?,?)

覆盖场景可以根据索引存储结构(B+Tree)推导出。

上面罗列的几种覆盖场景，效率由上至下越来越低，因为扫描行数越来越大

联合条件中，最左侧的索引列很重要，最好使用Cardinality最大的列。

总结

项目中遇到的问题，最终根据索引原理中最左侧索引列使用Cardinality最大列解决。

使用java获取当前是星期几

有个小需求，获取今天是周几，想我大java，获取一个星期几不是手到擒来。

第一想法，System.currentTimeMillis()，这是个时间戳，还得处理秒数，太麻烦。

第二想法，new Date() deprecated? 好吧。

第三想法，Calendar，简单写下代码，开始调试

Calendar calendar = Calendar.getInstance();

int weekDay = calendar.get(Calendar.DAY_OF_WEEK) - 1;

Calendar calendar = Calendar.getInstance();

int weekDay = calendar.get(Calendar.DAY_OF_WEEK) - 1;

weekDay = 3，wtf？今天是周二，怎么给了我一个3，只听说过从0、1计数，没听说过从2开始计数的啊！

继续查

国际上是以星期日为一周第一天的开始，Calendar中提供的DAY_OF_WEEK获取的一周也是以星期日作为一周的开始。

而中国的习惯是以星期一作为一周的开始，所以需要根据项目的实际需求来设置。

好吧，真是人生处处皆学问，写代码还能学知识。

怎么办呢？Calendar有个api叫setFirstDayOfWeek，原来从哪一天开始是可以设置的，java也是蛮人性化的么~

calendar.setFirstDayOfWeek(Calendar.MONDAY);

1	calendar.setFirstDayOfWeek(Calendar.MONDAY);

继续调试

weekDay = 3

这就有点意思了，这个api是用来看的么? 继续查

官方文档一大堆，概括为：

setFirstDayOfWeek的方法意思只对WEEK_OF_MONTH 与WEEK_OF_YEAR 有作用.

WEEK_OF_MONTH :当前Calendar日期对象是当前月的第几周.

WEEK_OF_YEAR :当前Calendar 日期对象是当前年的第几周.

好吧，老老实实写下比较丑陋的代码

int weekDay = calendar.get(Calendar.DAY_OF_WEEK) - 1;
if (weekDay == 0) {
    weekDay = 7;
}

int weekDay = calendar.get(Calendar.DAY_OF_WEEK) - 1;

if (weekDay == 0) {

weekDay = 7;

}

dependencies 与 dependencyManagement 的区别

顶层pom中可定义dependencyManagement和dependencies，它们的区别在于

dependencyManagement只是用来管理版本号，防止版本冲突，子pom依赖相应包时，会优先用dependencyManagement指定的版本号。

dependencies是实实在在的下载/依赖，子模块可以直接依赖到

Hollow导读

本文大部分内容来源于Hollow官方文档

开发过程中会遇到这样的数据：体量算不上“大数据”，数据在变化，幅度也不大。处理这类数据的时候，一般是把数据放到内存中（容器、json、xml、RDBMS），隔断时间更新一次。

这样处理有很多局限性

内存限制
更新频率不能过快，造成延迟
频繁的读写造成I/O、GC等

世界陷入水火，一般的套路现在主角就该出场了

Netflix推出了Hollow，它是数据的利剑，内存的盾牌，它将不JSON、不XML、少GC、高效率的解决问题，总而言之，他是人民的大救星，下面请一起走进科学，走进Hollow的内心世界。

Hollow致力于解决内存数据问题，处理的量级（将数据转为JSON）一般在GB级别，TB/PB就爱莫能助了。

快照-增量

生产的数据有两种类型，Snapshot、Delta，即全量、增量数据。大多数情况下，我们处理的是增量数据。

生产-消费者模式

一个生产者服务多个消费者，生产者生产快照和增量数据更新至BLOB（二进制大数据）文件，消费者在内存中使用数据，只读属性保证了消费的高效。生产文件到内存对开发者是透明的。

数据模型

数据模型基于一个POJO，又相当于数据库的一行。开发者只需要定义一个POJO，Hollow的API-Generator会生产这个POJO相应的消费文件。

它适用于只读数据、单个生产者、可能多个消费者的情形。Hollow实现持久化的唯一机制是利用BLOB存储，它只是一个文件存储，可能是S3、NFS、甚至一台FTP服务器。启动的时候，消费者们读取整个数据集的快照，并将数据集引导到内存中。通过增量的方法，可以保证内存中的数据集是最新的。对于每个消费者，内存中的数据备份是临时性的，如果消费者重新启动，需要从BLOB存储中重新加载快照。

实际上BLOB快照文件的格式很简单，它在很大程度上和在内存布局的结构相同，因此数据初始化主要是将BLOB的内容直接复制到内存中，这一步可以快速完成，确保了初始化的时间很短。

Hollow-Expected class sun.util.calendar.BaseCalendar$Date but object was class java.util.Date

ObjectMapper写入一个数据，包含一个java.util.Date成员，报Expected class sun.util.calendar.BaseCalendar$Date but object was class java.util.Date错误。

这是hollow v2.1.0以下的一个bug，详见：

https://github.com/Netflix/hollow/issues/13

dkoszewnik commented on 31 Jan

Thanks very much for the report! I verified the issue and released a fix in v2.1.1.

有趣的是，将hollow替换成2.1.1，还是报这个错误，替换成2.2.1就好了

这句I verified the issue and released a fix in v2.1.1.像极了研发的口头禅‘下个版本修复’，承包了我一上午的笑点。

2024 年 12 月
一	二	三	四	五	六	日
						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