上海浦东新区网站,公司做的网站版权归谁所有,门户网站开发案例,wordpress如何重置1 单表查询 1.1 Prewhere替代where Prewhere和where语句的作用相同#xff0c;用来过滤数据。不同之处在于prewhere只支持 *MergeTree 族系列引擎的表#xff0c;首先会读取指定的列数据#xff0c;来判断数据过滤#xff0c;等待数据过滤之后再读取select 声明的列字段来补… 1 单表查询 1.1 Prewhere替代where Prewhere和where语句的作用相同用来过滤数据。不同之处在于prewhere只支持 *MergeTree 族系列引擎的表首先会读取指定的列数据来判断数据过滤等待数据过滤之后再读取select 声明的列字段来补全其余属性。 当查询列明显多于筛选列时使用Prewhere可十倍提升查询性能Prewhere会自动优化执行过滤阶段的数据读取方式降低io操作。 在某些场合下prewhere语句比where语句处理的数据量更少性能更高。 #关闭where自动转prewhere(默认情况下 where条件会自动优化成prewhere)
set optimize_move_to_prewhere0;
# 使用where
select WatchID, JavaEnable, Title, GoodEvent, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID, CounterClass, OS, UserAgent, URL, Referer, URLDomain, RefererDomain, Refresh, IsRobot, RefererCategories, URLCategories, URLRegions, RefererRegions, ResolutionWidth, ResolutionHeight, ResolutionDepth, FlashMajor, FlashMinor, FlashMinor2
from datasets.hits_v1 where UserID3198390223272470366;# 使用prewhere关键字
select WatchID, JavaEnable, Title, GoodEvent, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID, CounterClass, OS, UserAgent, URL, Referer, URLDomain, RefererDomain, Refresh, IsRobot, RefererCategories, URLCategories, URLRegions, RefererRegions, ResolutionWidth, ResolutionHeight, ResolutionDepth, FlashMajor, FlashMinor, FlashMinor2
from datasets.hits_v1 prewhere UserID3198390223272470366;默认情况我们肯定不会关闭where自动优化成prewhere但是某些场景即使开启优化也不会自动转换成prewhere需要手动指定prewhere 使用常量表达式使用默认值为alias类型的字段包含了arrayJOINglobalInglobalNotIn或者indexHint的查询select查询的列字段和where的谓词相同使用了主键字段 1.2 数据采样 通过采样运算可极大提升数据分析的性能 SELECT Title,count(*) AS PageViews
FROM hits_v1
SAMPLE 0.1 #代表采样10%的数据,也可以是具体的条数
WHERE CounterID 57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000采样修饰符只有在MergeTree engine表中才有效且在创建表时需要指定采样策略。 1.3 列裁剪与分区裁剪 数据量太大时应避免使用select * 操作查询的性能会与查询的字段大小和数量成线性表换字段越少消耗的io资源越少性能就会越高。 反例
select * from datasets.hits_v1;
正例
select WatchID, JavaEnable, Title, GoodEvent, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID
from datasets.hits_v1;分区裁剪就是只读取需要的分区在过滤条件中指定。 select WatchID, JavaEnable, Title, GoodEvent, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID
from datasets.hits_v1
where EventDate2014-03-23;1.4 orderby 结合 where、limit 千万以上数据集进行order by查询时需要搭配where条件和limit语句一起使用。 #正例
SELECT UserID,Age
FROM hits_v1
WHERE CounterID57
ORDER BY Age DESC LIMIT 1000#反例
SELECT UserID,Age
FROM hits_v1
ORDER BY Age DESC1.5 避免构建虚拟列 如非必须不要在结果集上构建虚拟列虚拟列非常消耗资源浪费性能可以考虑在前端进行处理或者在表中构造实际字段进行额外存储。 反例
SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;
正例拿到Income和Age后考虑在前端进行处理或者在表中构造实际字段进行额外存储
SELECT Income,Age FROM datasets.hits_v1;1.6 uniqCombined替代distinct 性能可提升10倍以上uniqCombined底层采用类似HyperLogLog算法实现能接收2%左右的数据误差可直接使用这种去重方式提升查询性能。Count(distinct )会使用uniqExact精确去重。 不建议在千万级不同数据上执行distinct去重查询改为近似去重uniqCombined 反例
select count(distinct rand()) from hits_v1;
正例
SELECT uniqCombined(rand()) from datasets.hits_v11.7 使用物化视图 参考第6章。 1.8 其他注意事项 1查询熔断 为了避免因个别慢查询引起的服务雪崩的问题除了可以为单个查询设置超时以外还可以配置周期熔断在一个查询周期内如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。 2关闭虚拟内存 物理内存和虚拟内存的数据交换会导致查询变慢资源允许的情况下关闭虚拟内存。 3配置join_use_nulls 为每一个账户添加 join_use_nulls 配置左表中的一条记录在右表中不存在右表的相应字段会返回该字段相应数据类型的默认值而不是标准SQL中的Null值。 4批量写入时先排序 批量写入数据时必须控制每个批次的数据中涉及到的分区的数量在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多会导致ClickHouse无法及时对新导入的数据进行合并从而影响查询性能。 5关注CPU cpu一般在50%左右会出现查询波动达到70%会出现大范围的查询超时cpu是最关键的指标要非常关注。 2 多表关联 2.1 准备表和数据 #创建小表
CREATE TABLE visits_v2
ENGINE CollapsingMergeTree(Sign)
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity 8192
as select * from visits_v1 limit 10000;#创建join结果表避免控制台疯狂打印数据
CREATE TABLE hits_v2
ENGINE MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity 8192
as select * from hits_v1 where 10;2.2 用 IN 代替 JOIN 当多表联查时查询的数据仅从其中一张表出时可考虑用 IN 操作而不是JOIN insert into hits_v2
select a.* from hits_v1 a where a. CounterID in (select CounterID from visits_v1);#反例使用join
insert into table hits_v2
select a.* from hits_v1 a left join visits_v1 b on a. CounterIDb. CounterID;2.3 大小表JOIN 多表join时要满足小表在右的原则右表关联时被加载到内存中与左表进行比较ClickHouse中无论是Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在所以右表必须是小表。 1小表在右 insert into table hits_v2 select a.* from hits_v1 a left join visits_v2 b on a. CounterIDb. CounterID; 2大表在右 insert into table hits_v2 select a.* from visits_v2 b left join hits_v1 a on a. CounterIDb. CounterID; 2.4 注意谓词下推版本差异 ClickHouse在join查询时不会主动发起谓词下推的操作需要每个子查询提前完成过滤操作需要注意的是是否执行谓词下推对性能影响差别很大新版本中已经不存在此问题但是需要注意谓词的位置的不同依然有性能的差异 Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a. CounterIDb. CounterID
having a.EventDate 2014-03-17;Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a. CounterIDb. CounterID
having b.StartDate 2014-03-17;insert into hits_v2
select a.* from hits_v1 a left join visits_v2 b on a. CounterIDb. CounterID
where a.EventDate 2014-03-17;insert into hits_v2
select a.* from (select * from hits_v1 where EventDate 2014-03-17
) a left join visits_v2 b on a. CounterIDb. CounterID;2.5 分布式表使用GLOBAL 两张分布式表上的IN和JOIN之前必须加上GLOBAL关键字右表只会在接收查询请求的那个节点查询一次并将其分发到其他节点上。如果不加GLOBAL关键字的话每个节点都会单独发起一次对右表的查询而右表又是分布式表就导致右表一共会被查询N²次N是该分布式表的分片数量这就是查询放大会带来很大开销。 2.6 使用字典表 将一些需要关联分析的业务创建成字典表进行join操作前提是字典表不宜太大因为字典表会常驻内存 2.7 提前过滤 通过增加逻辑过滤可以减少数据扫描达到提高执行速度及降低内存消耗的目的
