一、行式存储和列式存储的优点缺点（从应用上）

1. . 行式存储（如 MySQL 的 InnoDB）

存储特点：整行数据连续存储（一行中的所有列紧挨着存放）。

优点：适合 “按行查询 / 修改” 场景

示例 1：查询单条完整记录

sq

-- 需求：查询ID=100的用户的所有信息（姓名、年龄、地址、电话等）
SELECT * FROM users WHERE id = 100;

• 行式存储优势：只需一次 IO 操作就能读取整行数据（所有列连续存储），效率极高（毫秒级）。
• 列式存储劣势：需要读取多个列的存储块并拼接，IO 次数多，效率低。

示例 2：更新单行数据

-- 需求：修改用户的手机号和地址
UPDATE users SET phone = '138xxxx', address = '北京市' WHERE id = 100;

• 行式存储优势：行级锁只需锁定一行，修改后一次性写入整行，操作简单高效。
• 列式存储劣势：需修改两个列的存储块，可能涉及多个锁和 IO 操作，效率低且容易引发冲突。

2. 列式存储（如 ClickHouse）

存储特点：同一列的数据连续存储（不同行的同一列紧挨着存放）。

优点：适合 “按列聚合分析” 场景

示例 3：对单列进行聚合计算

-- 需求：统计所有用户的平均年龄（只需用到age列）
SELECT AVG(age) FROM users;

• 列式存储优势：只需读取age列的存储块，无需读取姓名、地址等无关列，IO 量极小（例如 10 亿行数据，仅需读取几十 MB 的age列数据）。
• 行式存储劣势：需扫描全表的所有列，IO 量是列式存储的数倍甚至数十倍（例如每行有 10 列，IO 量增加 10 倍）。

示例 4：多列分组统计sql

-- 需求：按地区统计用户平均年龄和总消费金额（仅需region、age、amount列）
SELECT region, AVG(age), SUM(amount) FROM users GROUP BY region;

• 列式存储优势：仅需读取region、age、amount三列，且列数据连续存储，可高效利用 CPU 缓存进行计算，适合并行处理。
• 行式存储劣势：需读取所有列的数据，再过滤出需要的列，大量时间浪费在无用数据的 IO 和解析上，大表查询可能耗时分钟级。

3. 各自的缺点总结

存储类型	典型缺点（对比场景）	举例说明
行式存储	1. 分析场景中 IO 浪费严重 2. 聚合计算效率低	统计 10 亿行数据的平均值时，需读取所有列数据
列式存储	1. 单行查询 / 修改效率低 2. 不适合事务性操作	查询单用户完整信息时，需拼接多个列存储块

总结

• 行式存储：擅长处理 “整行读写” 的事务性操作（OLTP），如用户注册、订单修改。
• 列式存储：擅长处理 “多行列聚合” 的分析性操作（OLAP），如销售报表、用户行为分析。

实际业务中，两者常配合使用：用行式数据库存储业务数据，定期同步到列式数据库进行分析。

二、DQL语言上差异

(一) 基础查询部分

通用部分：

select 列名1、列名 
from 表名
【where 条件】
【gourp by】
【having 聚合条件】
【order by 列名 asc/desc】
【limit 数量】

(二) 聚合函数和去重

(一) MYSQL

• 查看人员表里有多少不重复的人名

• 查看在运行的计划的数量

(二) clickhouse

• 查看人员里有多少不重复的人名

select countDistinct name) from users

• 查看在联盟id为75的计划的数量

select countIf(id,unoin_id=75) from ads

(三) 时间函数处理

(一) MYSQL

依赖 DATE_FORMAT、UNIX_TIMESTAMP 等基础函数

• 将'2024-01-01 01:01:01' 转换成日期格式（yyyy-mm-dd)

select date_format('2024-01-01 01:01:01',"%Y-%m-%d")

• 每月结算毛利汇总（这里stat_month 是202301格式）

select
left(stat_month,4) as '年',
right(stat_month,2) as 月,
concat(left(stat_month,4),'-',right(stat_month,2)) '年月' ,
market_commission
from dm_market_commission_summary_no_invoice_monthly_stat;

• 获取当月的第一天

select date_format(current_date(),"%Y-%m-01")；
select concat(left(current_date(),7),'-01')；

(二) clickhouse

• 获取当月的第一天

select toStartOfMonth(toDateTime('2020-01-02 11:11:11'));

• 获取当月/当天/当周/当年的第一天

SELECT formatDateTime(dateTrunc('week', now()), '%Y-%m-%d %H:%i:%S');

(四) join操作

(一) mysql

MySQL：支持多种 JOIN 类型，写法灵活，适合小表关联

• 支持不等值关联

关于join的顺序：

1. INNER JOIN：无需刻意纠结顺序，优化器通常会自动选择 “小表驱动大表”，但需保证被驱动表的关联字段有索引。
2. LEFT JOIN / RIGHT JOIN：

• • 必须以左表 / 右表为驱动表，需确保驱动表经过过滤后尽可能小（通过 WHERE 条件），同时被驱动表的关联字段有索引。

1. 无索引场景：优先让小表作为驱动表，减少被驱动表的全表扫描次数。

(二) clickhouse

不支持等值关联

• 关联条件必须是相等条件（ON a.id = b.id），不支持非等值关联（如 a.value > b.value）。

ClickHouse 的 JOIN 执行采用 “散列表连接”（Hash Join）为主，流程如下：

1. 将右表（默认小表）加载到内存，构建哈希表（以关联字段为键）。
2. 流式扫描左表（大表），每行数据通过关联字段到哈希表中匹配，返回符合条件的结果。

因此，表的顺序对性能影响极大：

• 正确做法：大表 LEFT JOIN 小表（小表作为右表，被加载到内存）。
• 错误做法：小表 LEFT JOIN 大表（大表作为右表，无法全部加载到内存，会写入磁盘临时文件，性能暴跌）。

(五) 视图

一、核心本质差异

特性	MySQL 视图（View）	ClickHouse 物化视图（Materialized View）
数据存储	不存储实际数据，仅保存 SQL 查询逻辑（“虚拟表”）	存储查询结果的物理数据（“实体表”）
查询方式	每次查询视图时，动态执行底层 SQL 并返回结果	查询时直接读取预计算的物理数据
性能	与底层 SQL 性能一致（无优化）	极快（读取预存数据，避免重复计算）
更新机制	随底层表数据实时变化（无延迟）	依赖触发机制更新（非实时，有延迟）

MySQL 视图解决 “查询方便性” 问题，ClickHouse 物化视图解决 “查询性能” 问题。

(六) 分页

(一) MYSQL

SELECT 列名 FROM 表名 
[WHERE 条件] 
[ORDER BY 排序字段 [ASC|DESC]]
LIMIT [偏移量,] 行数;

• 首先获取最近2周的数据
• 展示7天之前的近7天的广告主佣金数据

SELECT date, sum(our_commission)
FROM dm_report_site drs
where date > date_add(current_date,interval -14 day)
group by date
ORDER BY date
limit 7 offset 1;

• 展示3天之前的近3天的广告主佣金数据

SELECT date, sum(our_commission)
FROM dm_report_site drs
where date > date_add(current_date,interval -14 day)
group by date
ORDER BY date
limit 3 offset 9;

(七) 分组总计

(一) MYSQL

• 查看2025年BD每个月的订单数，并查看每个BD的汇总金额 （with roollup）

select market_id,month(date),人员 ,sum(orders)
from dm_report_site drs inner join bi_duomaicps.view_org_bd market
on drs.market_id=market.dm_admin_id
where date>='2025-01-01'
group by market_id,month(date)
with rollup

(二) clickhouse

• 查看2025年BD每个月的订单数，并查看每个BD的汇总金额 （with cube）

SELECT month_int ,
       market_id,
       sum(orders)
FROM dm_report_site drs
WHERE month_int > 202501
GROUP BY month_int , market_id
WITH CUBE

• MySQL：

• • 不支持 “部分维度小计”，WITH ROLLUP 必须按所有分组字段生成完整层级。
  • 无法在合计中排除特定维度，灵活性低。

• ClickHouse：

• • 支持 ROLLUP(field1, (field2, field3)) 语法，可指定部分维度生成小计（如仅对 field1 做小计，忽略 field2 和 field3 的组合）。
  • 支持 GROUPING SETS 语法，精确指定需要的小计组合（如同时生成 (field1) 和 (field2) 的小计，但不生成 (field1, field2) 的明细）：

(八) 开窗函数

(一) MYSQL

• 查看每个BD的2025年9月每天 累计广告主销售额

with main as (
select market_id,date,sum(our_commission) as 'our_commission'
from dm_report_site drs
where date>='2025-09-10'
and market_id in(22766,22621)
group by 1,2
order by 1,2
)
select *,sum(our_commission) over(partition by market_id order by date) as 'agg'
from main

一、clickhouse

clickhouse基本和mysql一样，会多些内置函数