在 Linux 上使用 MariaDB 进行数据分析的实用流程
一 环境准备与数据接入
- 安装与启动
- Ubuntu/Debian：sudo apt-get update && sudo apt-get install mariadb-server
- RHEL/CentOS：sudo yum install mariadb-server mariadb
- 启动与开机自启：sudo systemctl start mariadb && sudo systemctl enable mariadb
- 安全初始化
- 执行 sudo mysql_secure_installation，设置 root 密码、移除匿名用户、禁止远程 root 登录、删除测试库
- 建库建表与导入
- 建库：CREATE DATABASE IF NOT EXISTS analytics CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
- 导入 CSV/TSV：
- LOAD DATA INFILE ‘/data/sales.csv’
INTO TABLE sales
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
(sale_date, store_id, amount);
- 命令行直接执行查询并将结果导出：
- mysql -h localhost -u analyst -ppwd -D analytics -e “SELECT region, SUM(amount) AS rev FROM sales GROUP BY region;” -s -N > region_rev.tsv
- 说明：-e 直接执行 SQL，-s 精简输出，-N 去掉列头，便于后续用 awk/sort/uniq/jq 处理。
二 常用分析 SQL 范式
- 指标汇总与分组
- 月度营收与订单数：
- SELECT DATE_FORMAT(sale_date, ‘%Y-%m’) AS ym,
SUM(amount) AS revenue, COUNT(*) AS orders
FROM sales
GROUP BY ym ORDER BY ym;
- 时间窗口与环比
- 近 7 天日营收与环比：
- SELECT cur.day,
cur.rev,
ROUND((cur.rev - prev.rev)/prev.rev*100, 2) AS mom_pct
FROM (
SELECT DATE(sale_date) AS day, SUM(amount) AS rev
FROM sales
WHERE sale_date >= CURDATE() - INTERVAL 7 DAY
GROUP BY day
) cur
LEFT JOIN (
SELECT DATE(sale_date) AS day, SUM(amount) AS rev
FROM sales
WHERE sale_date >= CURDATE() - INTERVAL 14 DAY
AND sale_date < CURDATE() - INTERVAL 7 DAY
GROUP BY day
) prev
ON prev.day = DATE_SUB(cur.day, INTERVAL 7 DAY)
ORDER BY cur.day;
- 留存与漏斗（示例：按用户首购与次日回访）
- 首购日期与次日留存率：
- WITH first AS (
SELECT user_id, MIN(DATE(order_time)) AS first_buy
FROM orders GROUP BY user_id
),
retained AS (
SELECT f.user_id, f.first_buy
FROM first f
JOIN orders o
ON o.user_id = f.user_id
AND DATE(o.order_time) = DATE_ADD(f.first_buy, INTERVAL 1 DAY)
)
SELECT f.first_buy,
COUNT(DISTINCT f.user_id) AS new_users,
COUNT(DISTINCT r.user_id) AS retained_users,
ROUND(COUNT(DISTINCT r.user_id)/COUNT(DISTINCT f.user_id)*100, 2) AS retention_pct
FROM first f
LEFT JOIN retained r USING (user_id)
GROUP BY f.first_buy
ORDER BY f.first_buy;
- Top-N 与占比
- 各品类销售额 Top 5 及其占比：
- WITH cat AS (
SELECT category, SUM(amount) AS rev
FROM sales
GROUP BY category
)
SELECT category, rev,
ROUND(rev/SUM(rev) OVER ()*100, 2) AS pct_total
FROM cat
ORDER BY rev DESC
LIMIT 5;
三 性能与可观测性保障
- 执行计划与索引
- 使用 EXPLAIN 检查扫描方式、索引命中与成本；尽量使用覆盖索引，避免对索引列做函数计算；必要时建立复合索引以匹配多条件查询。
- 慢查询与实时排查
- 启用慢查询日志（slow_query_log、long_query_time），定期分析慢 SQL；在会话中结合 SHOW PROCESSLIST 或 SHOW STATUS LIKE ‘Threads_%’ 观察连接与线程负载，定位阻塞与长事务。
- 配置优化要点（示例）
- 在 /etc/mysql/mariadb.conf.d/50-server.cnf 或 /etc/my.cnf 中调整关键参数：
- innodb_buffer_pool_size：建议为物理内存的约 50%–75%
- innodb_log_file_size：如 256M，提升写入吞吐
- innodb_flush_log_at_trx_commit：设为 2 可提升性能（在一致性与性能间折中）
- max_connections：结合并发需求设置（如 500）
- tmp_table_size / max_heap_table_size：如 256M，避免磁盘临时表
- 修改后重启：sudo systemctl restart mariadb
- 监控与可视化
- 系统与服务：使用 systemctl status mariadb、mysqladmin status 做健康检查
- 深入观测：查询 performance_schema（如 threads、events_statements_current）获取语句与线程级指标
- 运维监控：部署 PMM、Zabbix、Nagios 等做指标与告警
- 终端监控：安装 mytop 实时查看 QPS、线程与 SQL 概览（Debian/Ubuntu：sudo apt install mytop）。
四 结果导出与自动化
- 命令行导出与二次处理
- TSV/CSV：mysql -e “SELECT …” db -s -N > out.tsv；后续用 awk/sort/uniq 或 pandas.read_csv 分析
- 直接生成 JSON：mysql -sN -e “SELECT JSON_OBJECT(‘k’,v) FROM t” | jq .
- 定时与调度
- 使用 cron 定时跑批并将结果写入数据仓或报表目录；将常用查询封装为视图（CREATE VIEW）以便复用与权限控制
- 变更与回滚
- 分析脚本与 ETL 过程纳入版本管理；DDL 变更前备份并在低峰期执行，变更后复核统计信息与性能基线
以上流程覆盖了从数据接入、SQL 分析、性能保障到自动化导出的关键环节。对于 10GB–100GB 级数据量，优先通过索引与配置优化获得数量级提升；更大规模建议引入列式引擎（如 ColumnStore）、分区/分片或外部 OLAP 系统协同分析。