目录

crud boy

记录下日常搬砖过程中的踩坑和学到的骚操作,随缘更新。

MySQL

  • mysql中字符串类型索引查询时必须加引号,不然不会使用索引。原因是不支持函数索引,不加引号会使用了cast函数做隐式类型转换。(这里一般是int到string的cast
  • 有时候写子查询嵌套非常多,可以使用with as优化可读性,装逼点这玩意叫公用表表达式common table expression,CTE
    1
    2
    
    with t1 as (select * from xxx)
    select * from t1; -- 必须紧跟着就使用,不用就销毁了
    

On Conflict

遇到的场景是去优化有大量的数据需要upsert的场景:数据不存在就插入,数据存在就更新。接手时的实现是每次都做两次网络io,先判断是否存在,然后做更新操作。为了节省大量的io时间,我开始寻求更好的解决方案。

刚开始的思路是找类似redis pipline的方式:构造一个pipline,往里面赛语句,然后exec,才会发生网络io,redis服务器会按顺序执行所有语句。这和在mysql里用拼接多条语句很类似,发现只能实现批量插入,因为upsert还有一个判断逻辑。

最终开始求助我司内部大佬:得出的结论是使用Conflict.

https://gorm.io/docs/create.html#Upsert-On-Conflict

1
2
3
-- MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET "name"="excluded"."name"; SQL > Server
-- INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age"; PostgreSQL
INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `name`=VALUES(name),`age=VALUES(age);

原理是利用唯一键的冲突,在发生冲突时做更新操作。

  • 利用冲突做大批量upsert操作
    • 拼sql,尝试一次插入多列,定义冲突时更新的字段
    • 表中必须存在唯一键(primary key 或 unique key)才能使用
1
2
3
4
INSERT INTO `users` (id, name)
VALUES  (1, "jinzhu1"),
        (2, "jinzhu2")
ON DUPLICATE KEY UPDATE `name` = VALUES(name)
真・金柱大佬亲传(

https://blog-1256556944.file.myqcloud.com/public/jinzhu-taught-me.jpg

ES

  • es创建mapping踩坑,text会把索引字段分词,搜索用match而不能用term,keyword不会进行分词

TotalHits != Hits

es查询结果,searchResult.TotalHits()len(searchResult.Hits.Hits)不一致,原因

下面的代码panic了,以为TotalHits > 0就肯定Hits里有数据,结果数组长度是0,访问越界。

https://blog-1256556944.file.myqcloud.com/public/es_hits_code.png

TotalHits是本次查询命中了多少记录,Hits里是实际返回的数据,由于分片的查询队列满了,所以没有查询到任何数据。 searchResult.Shards.Failures里有报错信息:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
rejected execution of 
org.elasticsearch.transport.TcpTransport$RequestHandler@xxx 
on EsThreadPoolExecutor
[
    search, queue capacity = 1000, 
    org.elasticsearch.common.util.concurrent.EsThreadPoolExecutor@xxx
    [
        Running, pool size = 13, active threads = 13, 
        queued tasks = 1000, completed tasks = 919466345
    ]
]

Hive

  • hive里也可以使用with as,注意区分和临时表的区别,临时表储存在磁盘的临时区中,只有数据库连接断开,或者drop掉才会消失

创建变量

在mysql创建和使用变量可以这样:

1
2
set @id = 1;
select * from xxx where id = @id;

在hive里可以这样:

1
2
set date = 'xxx';
select * from xxx where date = ${hiveconf:date}