ClickHouse 助力大数据电商数据分析

ClickHouse 助力大数据电商数据分析

关键词：ClickHouse, 大数据分析, 电商数据分析, 列式存储, 实时OLAP, 数据仓库, 高并发查询

摘要：在电商行业，“数据”是决策的灵魂——从实时监控双11销售额到分析用户购买路径，从优化商品推荐到预警库存风险，都离不开对海量数据的高效处理。但当数据量达到”每天10亿订单”“千万用户行为日志”级别时，传统数据库往往力不从心。本文将以”给小学生讲故事”的方式，带你认识”数据库界的闪电侠”ClickHouse：它如何用”列式存储”这个”魔法衣柜”装下海量数据？如何靠”MergeTree引擎”这个”智能整理员”让查询快如闪电？又如何在电商场景中从”数据大山”里挖出真金白银？我们将从核心概念、原理到实战代码，一步步揭开ClickHouse助力电商数据分析的秘密，让你明白为什么它能成为阿里、京东、拼多多等电商巨头的”数据大脑”。

背景介绍

目的和范围

在电商世界里，数据就像空气一样无处不在：用户点击了哪个商品、加购了几件、下单时用了什么优惠券、物流到了哪个城市……这些数据每时每刻都在产生，一天可能就有”亿”级别的记录。如果把这些数据比作”积木”，传统数据库就像用手一块一块搭积木，搭到10层就累得不行；而ClickHouse则像开了”积木工厂”，能轻松搭出1000层的高塔，还能随时告诉你”第500层用了多少红色积木”。

本文的目的，就是带你搞懂：

ClickHouse到底是什么”神奇工具”？它为什么特别适合电商的”大数据分析”？如何用它解决电商中的实际问题（比如实时算销售额、分析用户行为）？

范围会聚焦在”电商数据分析场景”，不涉及ClickHouse的底层源码实现（那是给数据库工程师看的”说明书”），而是讲清楚”怎么用”和”为什么好用”。

预期读者

无论你是：

电商公司的数据分析师（每天要算”今天卖了多少钱”）；大数据开发工程师（负责搭数据平台）；产品经理（想知道”用户为什么不买我的商品”）；甚至是刚入门的技术小白（想了解”大数据怎么玩”）——

只要你对”如何从海量电商数据中快速挖到有用信息”感兴趣，这篇文章就适合你。我们会尽量避开复杂术语，用”生活比喻”代替”技术黑话”。

文档结构概述

本文就像一本”ClickHouse电商探险指南”，共分7个章节：

背景介绍：为啥电商需要ClickHouse？（现在读的就是这部分）核心概念与联系：用”魔法衣柜””智能整理员”等比喻，讲透ClickHouse的核心能力；核心原理与操作步骤：揭秘ClickHouse”跑得快”的底层逻辑，比如列式存储怎么存数据、MergeTree怎么整理数据；数学模型与公式：用简单数学解释”为什么ClickHouse查询这么快”（别怕，小学生也能看懂）；项目实战：手把手教你用ClickHouse建电商数据模型、写分析SQL、看实时报表；实际应用场景：阿里、京东怎么用ClickHouse解决真实问题？总结与思考题：回顾重点，留下”动手试一试”的小挑战。

术语表

核心术语定义

ClickHouse：一种”列式存储数据库”，专为”大数据量下的快速查询分析”设计，就像”数据库界的短跑冠军”，特别擅长处理”一次写入、多次查询”的场景（电商数据大多是这样）。OLAP：全称”联机分析处理”（Online Analytical Processing），简单说就是”对大量数据做复杂分析”，比如”统计过去30天每个省份的用户购买转化率”。列式存储：存储数据的一种方式，按”列”存数据（比如把所有”订单金额”放一起，所有”用户ID”放一起），而不是传统的按”行”存（一行存一个订单的所有信息）。实时分析：数据产生后”马上就能分析出结果”，比如双11时，消费者刚下单，系统就能实时更新”总销售额”数字。数据仓库：存储大量历史数据的”超级数据库”，就像电商的”数据图书馆”，里面存着几年的订单、用户、商品数据，供分析师”借阅”分析。宽表：一种包含很多字段的表，比如”用户订单宽表”可能同时有用户ID、商品ID、下单时间、支付方式、优惠券金额等上百个字段，方便分析师”一次查全信息”。物化视图：ClickHouse的”预计算神器”，就像”提前做好的数学公式答案”，把常用的复杂查询结果”存起来”，下次查直接取，不用重新算。

相关概念解释

行式存储 vs 列式存储：行式存储像”日记本”，一页写一天的所有事（日期、天气、做了什么）；列式存储像”分类账本”，专门有一页记”每天的天气”，另一页记”每天做了什么”。查”过去30天有多少晴天”，列式存储直接翻”天气页”数就行，比翻整本日记快得多。OLAP vs OLTP：OLTP是”联机事务处理”（比如用户下单、支付），像”便利店收银台”，要快且不能出错；OLAP是”分析处理”，像”便利店月底算账”，要算清楚”哪种商品卖得最好”，可以慢一点但要算得全。ClickHouse专攻OLAP，收银台的事交给MySQL就行。

缩略词列表

OLAP：联机分析处理（Online Analytical Processing）OLTP：联机事务处理（Online Transaction Processing）SQL：结构化查询语言（Structured Query Language，操作数据库的”英语”）GB/TB/PB：数据量单位，1GB=1024MB，1TB=1024GB，1PB=1024TB（电商数据经常按TB/PB算）MV：物化视图（Materialized View）CK：ClickHouse的简称（圈内人常这么叫）

核心概念与联系

故事引入：双11的”数据堵车”难题

想象你是某电商公司的”数据指挥官”，双11当天：

0点刚过，1分钟内有1000万用户下单，产生2000万条订单数据；老板在直播间问：“现在总销售额多少？哪个省份卖得最好？”你打开传统数据库的查询界面，输入”统计各省销售额”，结果屏幕转了5分钟圈圈，显示”查询超时”——数据太多，算不过来！

这时，隔壁技术总监走过来说：“试试ClickHouse吧，上次我们用它查1亿行数据，0.1秒就出结果了。”

你半信半疑地切换到ClickHouse，同样的查询，按下回车，屏幕瞬间跳出结果：“总销售额58亿，广东省第一，占比18%！”

老板在直播间笑着说：“我们的系统就是快！”

为什么ClickHouse这么快？这就要从它的”三大法宝”说起：列式存储、MergeTree引擎、分布式架构。接下来，我们一个个拆开看。

核心概念解释（像给小学生讲故事一样）

核心概念一：列式存储——数据界的”分类衣柜”

生活例子：
假设你有一个衣柜，里面放着T恤、裤子、外套。

行式存储的衣柜：把”一件T恤+一条裤子+一件外套”打包成一套，叠在一起放。想找”所有蓝色的T恤”，你得把每套衣服都拆开翻一遍，累死了！列式存储的衣柜：把所有T恤放一个抽屉，所有裤子放一个抽屉，所有外套放一个抽屉。想找”蓝色T恤”？直接打开T恤抽屉，一眼就能挑出来，快多了！

ClickHouse的列式存储：
在电商订单表中，有”订单ID、用户ID、商品ID、下单时间、订单金额”等字段。

行式存储：一行存一个订单的所有字段（订单1的ID+用户ID+金额……订单2的ID+用户ID+金额……）。列式存储：把所有订单ID放一起（列1），所有用户ID放一起（列2），所有金额放一起（列3）……

为什么快？
电商分析中，我们很少需要”一个订单的所有信息”，更多是”统计所有订单的总金额”（只需要”订单金额”这一列）、“每个用户的下单次数”（只需要”用户ID”这一列）。列式存储可以”只读取需要的列”，不用加载整行数据，就像找T恤不用翻裤子抽屉，自然快得多。

核心概念二：MergeTree引擎——数据界的”智能整理员”

生活例子：
你是个”剁手党”，每天收快递，快递盒堆在客厅（这叫”数据写入”）。如果不管它，客厅会乱成垃圾场，想找一个月前买的耳机盒（这叫”查询旧数据”），得翻遍所有盒子。

这时你请了个”智能整理员”（MergeTree引擎），它会：

按规则分类：每天的快递盒单独放一个箱子（按”日期”分区），比如11月1日的放红箱子，11月2日的放蓝箱子……排序整理：每个箱子里的快递盒按”大小”排序（按”订单金额”排序），大盒子放前面，小盒子放后面；定期合并：每周日把这周的7个小箱子合并成1个大箱子（合并小分区为大分区），并扔掉重复的盒子（去重）。

下次你想找”11月5日买的耳机盒（中等大小）”，整理员会直接打开11月5日的蓝箱子，在”中等大小”区域快速找到，不用翻遍整个客厅！

ClickHouse的MergeTree引擎：
MergeTree是ClickHouse最核心的存储引擎（就像汽车的发动机），它对写入的数据做三件事：

分区（Partitioning）：按时间（如”下单日期”）把数据分成多个”数据块”（类似按日期分箱子），查询时只扫描目标日期的分区（比如查”11月11日的订单”，只看11月11日的分区）；排序（Ordering）：每个分区内的数据按指定列排序（比如按”订单金额”排序），并建立稀疏索引（类似目录），快速定位数据位置；合并（Merging）：后台自动把小数据块合并成大数据块（减少文件数量），并支持去重、预计算（类似合并快递盒），提升查询效率。

核心概念三：分布式架构——数据界的”分工小队”

生活例子：
学校要办”六一儿童节活动”，需要准备1000个气球。如果让1个老师吹，可能吹到天黑都吹不完；但如果让10个老师分工，每人吹100个，1小时就能搞定——这就是”分布式”的思想：把大任务拆成小任务，多个人一起干。

ClickHouse的分布式架构：
当电商数据量达到”100TB”时，单台服务器存不下也处理不过来。ClickHouse可以搭建”分布式集群”，就像10个老师组成的小队：

数据分片（Sharding）：把数据分成多个”分片”（Shard），每个分片存在不同的服务器上。比如按”用户ID取模”分片，用户ID=1、11、21……放服务器1，用户ID=2、12、22……放服务器2……并行查询：查询时，每个服务器只处理自己分片的数据（比如查”所有用户的下单次数”，服务器1算自己分片的用户，服务器2算自己的），最后汇总结果。就像10个老师吹完气球后，把气球汇总到一起。

这样一来，100TB的数据分给10台服务器，每台只需处理10TB，速度自然快10倍！

核心概念四：物化视图——数据界的”错题本”

生活例子：
你是小学生，数学老师每天布置100道计算题（比如”1+2=？““3×4=？”）。如果每次考试前都重新算一遍所有题，太浪费时间了。聪明的做法是准备一个”错题本”，把常考的难题答案记下来，考试时直接看错题本——这就是”物化视图”。

ClickHouse的物化视图：
电商分析师经常需要查”每天各省的销售额”，这个查询要扫描”订单表”的”下单日期”“省份”“金额”三列，计算量很大。物化视图就像”错题本”：

预计算：提前把”每天各省销售额”的结果算出来，存成一张表（物化视图）；实时更新：当新订单数据写入时，ClickHouse自动更新物化视图（就像你每天把新的难题答案记到错题本）；快速查询：下次查”每天各省销售额”，直接查物化视图，不用扫描原始订单表，速度提升10倍以上！

核心概念之间的关系（用小学生能理解的比喻）

列式存储 × MergeTree引擎：“分类衣柜”配”整理员”

列式存储是”按类别分抽屉”的衣柜，MergeTree引擎是”负责整理抽屉”的智能整理员。

整理员会给每个抽屉贴标签（分区），比如”2023年11月订单金额抽屉”；抽屉里的衣服按大小排序（排序键），并放一个”目录本”（稀疏索引），告诉你”红色T恤在第5页”；定期把小抽屉合并成大抽屉（合并过程），避免抽屉太多不好找。

没有整理员的衣柜（只有列式存储，没有MergeTree），虽然按类别分了抽屉，但抽屉里的衣服乱糟糟，找东西还是慢；没有分类衣柜的整理员（只有MergeTree，没有列式存储），整理员得面对一堆混合的衣服，整理起来也费劲。两者搭配，才能让”找衣服”（查询数据）又快又准。

分布式架构 × 列式存储：“分工小队”用”分类工具箱”

分布式架构是”10个分工干活的老师”，列式存储是每个老师手里的”分类工具箱”（按类别放工具的箱子）。

每个老师负责一部分任务（分片数据），比如老师1处理”用户ID结尾是1的订单”；老师用分类工具箱（列式存储）快速找到需要的工具（数据列），比如算销售额只拿”金额”工具，不算用户ID；10个老师同时用分类工具箱干活，比1个老师用”混合工具箱”（行式存储）快10倍以上。

物化视图 × MergeTree引擎：”错题本”由”整理员”维护

物化视图是”记录难题答案的错题本”，MergeTree引擎是”帮你更新错题本的学习委员”。

学习委员（MergeTree）会定期检查新作业（新数据），如果有新的难题（符合物化视图定义的查询），就把答案算出来记到错题本（物化视图）；错题本本身也按”日期”分类（MergeTree的分区），方便你快速找到”最近一周的难题答案”；没有学习委员（MergeTree），错题本需要你手动更新（麻烦）；没有错题本（物化视图），每次考试都要重新算难题（慢）。

核心概念原理和架构的文本示意图（专业定义）

ClickHouse电商数据分析架构示意图：

┌─────────────────────────────────────────────────────────────────┐  
│                        数据采集层                               │  
│  (用户行为日志、订单系统、支付系统、物流系统) -> Kafka消息队列  │  
└───────────────────────────┬─────────────────────────────────────┘  
                            │  
┌───────────────────────────▼─────────────────────────────────────┐  
│                        数据存储层                               │  
│  ┌─────────────────────────────────────────────────────────┐    │  
│  │                ClickHouse分布式集群                     │    │  
│  │  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐     │    │  
│  │  │  分片1      │  │  分片2      │  │  分片3      │ ... │    │  
│  │  │ (MergeTree) │  │ (MergeTree) │  │ (MergeTree) │     │    │  
│  │  │ 列式存储    │  │ 列式存储    │  │ 列式存储    │     │    │  
│  │  └─────────────┘  └─────────────┘  └─────────────┘     │    │  
│  └─────────────────────────────────────────────────────────┘    │  
└───────────────────────────┬─────────────────────────────────────┘  
                            │  
┌───────────────────────────▼─────────────────────────────────────┐  
│                        数据计算层                               │  
│  ┌────────────────┐  ┌────────────────┐  ┌────────────────┐    │  
│  │ 物化视图(MV)   │  │ 分布式查询引擎 │  │ 并行执行计划   │    │  
│  │ (预计算结果)   │  │ (分片协同计算) │  │ (多线程加速)   │    │  
│  └────────────────┘  └────────────────┘  └────────────────┘    │  
└───────────────────────────┬─────────────────────────────────────┘  
                            │  
┌───────────────────────────▼─────────────────────────────────────┐  
│                        数据应用层                               │  
│  (实时报表、用户画像、商品推荐、库存预警) <- Grafana/Superset   │  
└─────────────────────────────────────────────────────────────────┘  
1234567891011121314151617181920212223242526272829

Mermaid 流程图：电商数据从产生到分析的全流程

核心算法原理 & 具体操作步骤

列式存储的底层实现：为什么”只读需要的列”这么快？

原理：
传统行式存储（如MySQL）按”行”存储数据，一行数据在磁盘上是连续的字节（比如一行订单数据占100字节，磁盘上连续存100字节）。当你查询”订单金额总和”时，需要把每一行的100字节都读入内存，再从中提取”金额”字段（比如第50-60字节）——90%的读取都是无用功！

列式存储则按”列”存储，所有”金额”字段在磁盘上连续存储（比如100万行订单的金额，在磁盘上是连续的400万字节，假设每个金额占4字节）。查询时，直接读取这400万字节，不用管其他列——读取的数据量减少90%，速度自然快9倍！

操作步骤（如何验证列式存储的优势）：
我们用ClickHouse和MySQL分别查询”1亿行订单表的金额总和”，对比耗时。

准备数据：

在MySQL中创建订单表（行式存储）：

CREATE TABLE mysql_orders (  
    id INT,  
    user_id INT,  
    amount DECIMAL(10,2),  
    create_time DATETIME  
);  
-- 插入1亿行随机数据（可通过Python脚本生成）  

sql
1234567

在ClickHouse中创建订单表（列式存储，MergeTree引擎）：

CREATE TABLE ck_orders (  
    id Int32,  
    user_id Int32,  
    amount Decimal(10,2),  
    create_time DateTime  
) ENGINE = MergeTree()  
PARTITION BY toYYYYMMDD(create_time)  -- 按日期分区  
ORDER BY id;  -- 按订单ID排序  
-- 插入和MySQL相同的1亿行数据  

sql
123456789

执行查询：

MySQL查询：

SELECT SUM(amount) FROM mysql_orders;  -- 耗时约30秒（假设）  

sql
1

ClickHouse查询：

SELECT SUM(amount) FROM ck_orders;  -- 耗时约0.5秒（假设）  

sql
1

结果对比：
ClickHouse快60倍！因为它只读取了”amount”这一列，而MySQL读取了所有4列。

MergeTree引擎的核心算法：分区、排序与合并

1. 分区（Partitioning）：数据的”抽屉标签”
MergeTree按”分区键”（如日期）把数据分成多个目录（分区），每个分区独立存储。查询时指定分区键（如
WHERE create_time = '2023-11-11'），ClickHouse会直接定位到对应分区，跳过其他分区。

例子：
创建按日期分区的订单表：

CREATE TABLE ck_orders (  
    id Int32,  
    user_id Int32,  
    amount Decimal(10,2),  
    create_time DateTime  
) ENGINE = MergeTree()  
PARTITION BY toYYYYMMDD(create_time)  -- 分区键：日期（如20231111）  
ORDER BY id;  

sql
12345678

插入数据后，磁盘上会生成类似
20231111_1_1_0的分区目录，查询”2023-11-11的订单”时，只需扫描这个目录，不用管其他日期的数据。

2. 排序（Ordering）：数据的”目录索引”
每个分区内的数据按”排序键”（如订单ID）排序，并建立”稀疏索引”（每隔8192行存一个索引项）。查询时，通过索引快速定位数据范围，不用全表扫描。

例子：
如果排序键是
id，稀疏索引会记录”第8192行id=10000，第16384行id=20000……“。当查询
WHERE id BETWEEN 15000 AND 25000时，ClickHouse通过索引知道数据在”16384行附近”，直接跳转到该位置扫描，而不是从第一行开始找。

3. 合并（Merging）：小文件变”大砖头”
ClickHouse写入数据时，会先创建小的”临时分区”（如每次写入生成一个分区）。后台线程定期把小分区合并成大分区（类似把零散的纸页装订成一本书），减少文件数量，提升查询效率。

合并规则：

同一分区键的小分区才会合并（比如都是20231111的分区）；合并时按排序键排序，并支持去重（通过
ENGINE = ReplacingMergeTree）、汇总（通过
ENGINE = SummingMergeTree）等操作。

例子：
用
SummingMergeTree引擎自动汇总销售额：

CREATE TABLE ck_daily_sales (  
    province String,  
    sale_date Date,  
    total_amount Decimal(10,2)  
) ENGINE = SummingMergeTree(total_amount)  -- 合并时自动累加total_amount  
PARTITION BY sale_date  
ORDER BY province;  

sql
1234567

插入两条”广东省2023-11-11″的销售数据：

INSERT INTO ck_daily_sales VALUES ('广东省', '2023-11-11', 10000);  
INSERT INTO ck_daily_sales VALUES ('广东省', '2023-11-11', 20000);  

sql
12

合并后，数据会自动变成一条：
('广东省', '2023-11-11', 30000)，查询时直接返回汇总结果，不用手动
SUM()。

分布式查询的执行流程：多服务器”分工合作”

原理：
ClickHouse分布式表由”本地表”和”分布式表”组成：

本地表：实际存储数据的表，每个分片一台服务器，存一部分数据；分布式表：虚拟表，不存数据，只负责把查询请求分发到各个分片的本地表，汇总结果后返回。

执行步骤：

用户查询分布式表：
SELECT SUM(amount) FROM distributed_orders;分布式表把查询转发给每个分片的本地表：“分片1，查你的orders表的SUM(amount)；分片2，查你的orders表的SUM(amount)……”每个分片计算本地结果（如分片1返回10亿，分片2返回15亿……）；分布式表汇总所有分片结果（10亿+15亿+……=总销售额），返回给用户。

操作步骤（搭建分布式集群）：

准备3台服务器（shard1、shard2、shard3），每台安装ClickHouse；在每台服务器创建本地表：

-- 在shard1、shard2、shard3上分别执行  
CREATE TABLE local_orders (  
    id Int32,  
    user_id Int32,  
    amount Decimal(10,2),  
    create_time DateTime  
) ENGINE = MergeTree()  
PARTITION BY toYYYYMMDD(create_time)  
ORDER BY id;  

sql
123456789

在任意一台服务器创建分布式表，关联所有分片的本地表：

CREATE TABLE distributed_orders AS local_orders  
ENGINE = Distributed(  
    'cluster_name',  -- 集群名称（在config.xml中配置）  
    'db_name',       -- 数据库名  
    'local_orders',  -- 本地表名  
    user_id % 3      -- 分片规则：按user_id取模3，分配到3个分片  
);  

sql
1234567

向分布式表写入数据，数据会自动按分片规则分发到3台服务器；查询分布式表，享受3台服务器并行计算的速度！

数学模型和公式 & 详细讲解 & 举例说明

列式存储的IO效率提升模型

问题：为什么查询”单列总和”时，列式存储比行式存储快？

模型：
假设一张表有
N行，
C列，每个列的数据大小为
S字节（假设所有列大小相同）。

行式存储IO量：查询单列时，需要读取所有列的数据，总IO量 =
N × C × S（因为一行有C列，每列S字节，共N行）；列式存储IO量：只需读取目标列的数据，总IO量 =
N × S；

效率提升倍数 = 行式IO量 / 列式IO量 =
(N × C × S) / (N × S) = C。

结论：列式存储的IO效率提升倍数约等于表的列数
C！表的列数越多，列式存储优势越大。

举例：
电商订单宽表通常有100列（用户ID、商品ID、下单时间、支付方式、优惠券金额、地址、物流信息……），即
C=100。

行式存储IO量 =
1亿 × 100 × 4字节 = 400亿字节 = 40GB；列式存储IO量 =
1亿 × 4字节 = 4亿字节 = 0.4GB；效率提升倍数 = 40GB / 0.4GB = 100倍！

这就是为什么ClickHouse查宽表时特别快——列数越多，省的IO越多！

MergeTree的查询时间估算模型

问题：MergeTree的分区和排序如何减少查询时间？

模型：
假设表总数据量为
D（GB），查询条件命中
P个分区（
P ≤ 总分区数），每个分区内需要扫描的数据比例为
R（
0 < R ≤ 1，由排序和索引决定）。

查询时间
T ≈ (D × P / 总分区数 × R) / 磁盘读取速度。

结论：分区数越多（
P越小）、索引越精确（
R越小），查询时间越短。

举例：
某电商订单表总数据量
D=1000GB（1TB），按天分区（一年365个分区），磁盘读取速度为200MB/s。

场景1：查询”2023-11-11当天的订单金额总和”（
P=1个分区，
R=1，因为要扫描整个分区）；
数据量 =
1000GB × 1/365 × 1 ≈ 2.7GB；
查询时间 ≈
2.7GB / 200MB/s = 2700MB / 200MB/s = 13.5秒。

场景2：查询”2023-11-11金额>1000元的订单”（
P=1个分区，排序键是
amount，通过索引定位到
R=0.1的数据）；
数据量 =
1000GB × 1/365 × 0.1 ≈ 0.27GB；
查询时间 ≈
0.27GB / 200MB/s = 270MB / 200MB/s = 1.35秒。

场景3：如果不分区（
P=365个分区，即全表扫描），查询”2023-11-11的订单”：
数据量 =
1000GB × 365/365 × 1 = 1000GB；
查询时间 ≈
1000GB / 200MB/s = 1000000MB / 200MB/s = 5000秒 ≈ 1.4小时！

可见，分区和排序能把查询时间从”1.4小时”压缩到”1秒级”，这就是MergeTree的魔力！

物化视图的加速比模型

问题：物化视图能把查询加速多少？

模型：
假设原始查询需要扫描
D字节数据，耗时
T秒；物化视图存储的预计算结果为
d字节（
d << D）。

加速比 =
T / (d / 磁盘读取速度) ≈ T × (磁盘读取速度) / d。

结论：物化视图存储的结果越小（
d越小），加速比越高。

举例：
查询”过去30天各省销售额”，原始订单表
D=100GB，耗时
T=100秒；物化视图只存”日期+省份+销售额”，
d=0.1GB。

加速比 =
100秒 × 200MB/s / 0.1GB = 100 × 200MB/s / 100MB = 200倍！

即原始查询100秒，物化视图查询只需0.5秒，加速200倍！

项目实战：代码实际案例和详细解释说明

开发环境搭建

1. 安装ClickHouse（单节点，适合测试）
在Linux服务器（推荐Ubuntu 20.04+）上执行：

# 添加ClickHouse官方仓库  
sudo apt-get install -y apt-transport-https ca-certificates dirmngr  
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D75  
echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list  

# 安装ClickHouse  
sudo apt-get update  
sudo apt-get install -y clickhouse-server clickhouse-client  

# 启动服务  
sudo systemctl start clickhouse-server  

# 连接客户端（默认无密码）  
clickhouse-client  

bash

1234567891011121314

2. 安装可视化工具（DBeaver）
DBeaver是免费的数据库客户端，支持ClickHouse。下载地址：https://dbeaver.io/
安装后，新建ClickHouse连接：

主机：localhost（或服务器IP）端口：9000（默认TCP端口）用户名：default（默认）密码：空（默认）

源代码详细实现和代码解读

步骤1：设计电商数据模型

我们需要分析”用户下单后，哪些数据对分析有用”，设计3张核心表：

1. 商品表（products）：存商品基本信息

CREATE TABLE products (  
    product_id Int32,               -- 商品ID（主键）  
    product_name String,            -- 商品名称  
    category String,                -- 商品分类（如"手机""服装"）  
    price Decimal(10,2),            -- 商品单价  
    brand String,                   -- 品牌（如"苹果""小米"）  
    create_time DateTime            -- 上架时间  
) ENGINE = MergeTree()  
ORDER BY product_id;  -- 按商品ID排序  

sql
123456789

2. 用户表（users）：存用户基本信息

CREATE TABLE users (  
    user_id Int32,                  -- 用户ID（主键）  
    username String,                -- 用户名  
    gender String,                  -- 性别（男/女/未知）  
    age Int8,                       -- 年龄  
    province String,                -- 省份（如"广东省""浙江省"）  
    register_time DateTime          -- 注册时间  
) ENGINE = MergeTree()  
ORDER BY user_id;  -- 按用户ID排序  

sql
123456789

3. 订单表（orders）：存订单明细（核心表，数据量最大）

CREATE TABLE orders (  
    order_id Int64,                 -- 订单ID（主键）  
    user_id Int32,                  -- 用户ID（关联users表）  
    product_id Int32,               -- 商品ID（关联products表）  
    amount Decimal(10,2),           -- 订单金额（单价×数量）  
    quantity Int32,                 -- 购买数量  
    pay_time DateTime,              -- 支付时间  
    pay_type String,                -- 支付方式（支付宝/微信/银联）  
    coupon_amount Decimal(10,2),    -- 优惠券金额  
    province String                 -- 用户所在省份（冗余字段，加速查询）  
) ENGINE = MergeTree()  
PARTITION BY toYYYYMMDD(pay_time)  -- 按支付日期分区（核心优化！）  
ORDER BY (product_id, pay_time);   -- 按商品ID+支付时间排序（方便查某商品的销售趋势）  

sql

12345678910111213

为什么订单表要冗余”province”字段？
因为分析时经常需要”按省份统计销售额”，如果不冗余，需要关联users表（
JOIN users ON orders.user_id = users.user_id），而关联操作很耗时。冗余后，直接用orders表的province字段查询，速度提升10倍！

步骤2：生成测试数据

用Python脚本生成1000万条订单数据、10万用户数据、1万商品数据：

import random  
import datetime  
from clickhouse_driver import Client  

# 连接ClickHouse  
client = Client(host='localhost', database='default')  

# 生成商品数据（1万条）  
products = []  
categories = ['手机', '电脑', '服装', '食品', '家电']  
brands = ['苹果', '小米', '华为', '耐克', '美的']  
for product_id in range(1, 10001):  
    product_name = f'商品{product_id}'  
    category = random.choice(categories)  
    price = round(random.uniform(10, 5000), 2)  
    brand = random.choice(brands)  
    create_time = datetime.datetime(2023, 1, 1) + datetime.timedelta(days=random.randint(0, 300))  
    products.append((product_id, product_name, category, price, brand, create_time))  

# 插入商品表  
client.execute('INSERT INTO products VALUES', products)  

# 生成用户数据（10万条）  
users = []  
provinces = ['广东省', '浙江省', '江苏省', '山东省', '河南省']  
for user_id in range(1, 100001):  
    username = f'用户{user_id}'  
    gender = random.choice(['男', '女', '未知'])  
    age = random.randint(18, 60)  
    province = random.choice(provinces)  
    register_time = datetime.datetime(2023, 1, 1) + datetime.timedelta(days=random.randint(0, 300))  
    users.append((user_id, username, gender, age, province, register_time))  

# 插入用户表  
client.execute('INSERT INTO users VALUES', users)  

# 生成订单数据（1000万条，2023-11-01至2023-11-11）  
orders = []  
start_date = datetime.datetime(2023, 11, 1)  
for order_id in range(1, 10000001):  
    user_id = random.randint(1, 100000)  
    product_id = random.randint(1, 10000)  
    quantity = random.randint(1, 5)  
    # 从商品表查单价（实际场景中应提前缓存，这里简化）  
    price = client.execute(f"SELECT price FROM products WHERE product_id={product_id}")[0][0]  
    amount = round(price * quantity, 2)  
    pay_time = start_date + datetime.timedelta(days=random.randint(0, 10), hours=random.randint(0, 23), minutes=random.randint(0, 59))  
    pay_type = random.choice(['支付宝', '微信', '银联'])  
    coupon_amount = round(random.uniform(0, amount*0.3), 2)  
    # 从用户表查省份（实际场景中应提前缓存，这里简化）  
    province = client.execute(f"SELECT province FROM users WHERE user_id={user_id}")[0][0]  
    orders.append((order_id, user_id, product_id, amount, quantity, pay_time, pay_type, coupon_amount, province))  
    # 每10万条插入一次，避免内存溢出  
    if order_id % 100000 == 0:  
        client.execute('INSERT INTO orders VALUES', orders)  
        orders = []  

python
运行
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556

步骤3：编写电商分析SQL

1. 实时监控：双11当天销售额趋势（每小时）

SELECT  
    toStartOfHour(pay_time) AS hour,  -- 按小时聚合  
    SUM(amount) AS total_sales,      -- 销售额  
    SUM(coupon_amount) AS total_coupon,  -- 优惠券总金额  
    SUM(quantity) AS total_quantity  -- 总销量  
FROM orders  
WHERE pay_time >= '2023-11-11 00:00:00' AND pay_time < '2023-11-12 00:00:00'  
GROUP BY hour  
ORDER BY hour;  

sql
123456789

结果解读：会得到24行数据，每小时一行，显示”0点销售额5亿，1点销售额3亿……”，可画成折线图，看哪个时段是销售高峰。

2. 用户分析：各省份销售额排名（双11当天）

SELECT  
    province,  
    SUM(amount) AS sales,  
    ROUND(SUM(amount) / (SELECT SUM(amount) FROM orders WHERE pay_time >= '2023-11-11'), 4) AS sales_ratio  -- 占比  
FROM orders  
WHERE pay_time >= '2023-11-11 00:00:00' AND pay_time < '2023-11-12 00:00:00'  
GROUP BY province  
ORDER BY sales DESC  
LIMIT 10;  -- 取前10省份  

sql
123456789

结果解读：可能显示”广东省 12亿 18%，浙江省 8亿 12%……”，帮助电商调整区域营销策略。

3. 商品分析：哪个分类的商品最受欢迎（销量最高）

SELECT  
    p.category,  
    SUM(o.quantity) AS total_quantity,  
    SUM(o.amount) AS total_sales  
FROM orders o  
JOIN products p ON o.product_id = p.product_id  -- 关联商品表获取分类  
WHERE o.pay_time >= '2023-11-01' AND o.pay_time < '2023-11-12'  -- 双11活动期（11月1日-11日）  
GROUP BY p.category  
ORDER BY total_quantity DESC;  

sql
123456789

结果解读：可能显示”手机 50万件，服装 30万件……”，指导商家备货。

4. 创建物化视图：加速”每日各省销售额”查询

CREATE MATERIALIZED VIEW daily_province_sales  
ENGINE = SummingMergeTree()  -- 自动合并相同(日期,省份)的销售额  
PARTITION BY sale_date  
ORDER BY (sale_date, province)  
AS  
SELECT  
    toDate(pay_time) AS sale_date,  -- 日期  
    province,                       -- 省份  
    SUM(amount) AS total_sales,     -- 销售额（SummingMergeTree会自动累加）  
    SUM(quantity) AS total_quantity -- 销量（SummingMergeTree会自动累加）  
FROM orders  
GROUP BY sale_date, province;  

sql

123456789101112

使用物化视图查询：

-- 查询2023-11-11各省销售额，直接查物化视图  
SELECT province, total_sales  
FROM daily_province_sales  
WHERE sale_date = '2023-11-11'  
ORDER BY total_sales DESC;  

sql
12345

速度对比：直接查orders表可能需要5秒，查物化视图只需0.1秒！

代码解读与分析

1. 订单表设计的优化点：

按支付时间分区：双11的查询都