如何在Ubuntu上使用MariaDB进行数据分析

在 Ubuntu 上使用 MariaDB 进行数据分析的实操指南

一 环境准备与安装

• 更新索引并安装 MariaDB：
  • 命令：sudo apt update && sudo apt install mariadb-server
  • 安装后建议执行安全初始化：sudo mysql_secure_installation（设置 root 密码、移除匿名用户、禁止远程 root 登录等）
• 启动与验证服务：
  • Ubuntu/原生系统：sudo systemctl start mariadb、sudo systemctl enable mariadb、sudo systemctl status mariadb
  • WSL 环境：使用 sudo service mysql start|restart|status
• 基本连接与测试：
  • 本地登录：mariadb -u root -p
  • 简单检查：SHOW DATABASES;、SELECT VERSION();
• 如需远程访问或改端口，编辑配置文件 /etc/mysql/mariadb.conf.d/50-server.cnf：
  • 远程：bind-address = 0.0.0.0
  • 端口：port = 3307（示例）
  • 修改后重启服务（WSL 用 sudo service mysql restart）

二 数据导入与导出

• 导入 SQL 备份
  • 命令：mysql -u user -p database_name < backup.sql
  • 或在 MariaDB 会话中：USE db; SOURCE /path/file.sql;
• 导出为 SQL 备份
  • 命令：mysqldump -u user -p database_name > backup.sql
  • 远程导出：mysqldump -h remote_IP -u user -p -P 3306 database_name > backup.sql
• 导入/导出 CSV（服务器端导出，便于 Excel 分析）
  • 导出：SELECT ... FROM table INTO OUTFILE '/tmp/table.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
  • 导入：LOAD DATA INFILE '/tmp/table.csv' INTO TABLE table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
  • 注意：需保证 MariaDB 对目标目录有写权限，且 secure_file_priv 配置允许该路径
• 使用 Docker 运行 MariaDB（便于隔离与可移植）
  • 示例：
    • docker run -d --name mymariadb -p 3307:3306 \ -e MYSQL_ROOT_PASSWORD=123456 \ -e MARIADB_USER=datalab -e MARIADB_PASSWORD=123456 \ -e MARIADB_DATABASE=testdb \ -v /home/datalab/mariadb_vol/mysql_data:/var/lib/mysql \ -v /home/datalab/mariadb_vol/mysql_conf:/etc/mysql/conf.d \ mariadb
  • 进入容器：docker exec -it mymariadb mariadb -u root -p
  • 数据卷挂载可实现数据与配置持久化，避免容器重建后丢失

三 数据分析常用 SQL 范式

• 基础查询与聚合
  • 选择字段：SELECT col1, col2 FROM t WHERE cond;
  • 分组聚合：SELECT category, COUNT(*), SUM(amount) FROM t GROUP BY category HAVING SUM(amount) > 1000;
  • 排序与分页：ORDER BY col DESC LIMIT 10 OFFSET 20;
• 时间维度分析
  • 月度汇总：SELECT DATE_FORMAT(event_time, '%Y-%m') AS ym, COUNT(*) AS cnt FROM t GROUP BY ym;
  • 近 30 天：SELECT * FROM t WHERE event_time >= CURDATE() - INTERVAL 30 DAY;
• 窗口函数进阶（MariaDB 10.2+ 支持）
  • 累计求和（Running Total）：
    • SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM sales;
  • 分组累计（按类别）：
    • SELECT date, category, amount, SUM(amount) OVER (PARTITION BY category ORDER BY date) AS cat_running FROM sales;
  • 滑动平均（7 天）：
    • SELECT date, amount, AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7 FROM sales;
  • 同比（YoY）：
    • SELECT year, month, revenue, LAG(revenue, 12) OVER (ORDER BY year, month) AS prev_year, ROUND((revenue - LAG(revenue, 12) OVER (ORDER BY year, month)) / LAG(revenue, 12) OVER (ORDER BY year, month), 4) AS yoy FROM sales;
• 实用技巧
  • 条件聚合：SUM(CASE WHEN region='North' THEN amount ELSE 0 END) AS north_sales
  • 排名：ROW_NUMBER()/RANK()/DENSE_RANK() OVER (PARTITION BY dept ORDER BY score DESC)
  • 缺失日期补全：结合数字序列表或日历表 LEFT JOIN 后再做窗口计算

四 与 Python 协同分析

• 安装驱动：pip install mariadb
• 连接与查询示例：
  • 连接：
    • conn = mariadb.connect(user='datalab', password='123456', host='localhost', port=3306, database='testdb')
    • cursor = conn.cursor()
  • 查询与取数：
    • cursor.execute("SELECT id, amount FROM sales WHERE sale_date >= CURDATE() - INTERVAL 7 DAY")
    • rows = cursor.fetchall()
  • 批量插入（提升吞吐）：
    • data = [(v1, v2), (v3, v4)]
    • cursor.executemany("INSERT INTO sales (col1, col2) VALUES (?, ?)", data)
    • conn.commit()
  • 关闭资源：
    • cursor.close(); conn.close()
• 典型用途：将 MariaDB 作为数据接口，用 pandas 做统计与可视化，或将聚合结果回写到分析库

五 性能与运维要点

• 引擎选择
  • 事务与一致性：InnoDB（默认，支持事务、行级锁、外键）
  • 分析型场景：ColumnStore（列式引擎，适合大数据量聚合/扫描）
• 配置优化（示例）
  • 缓冲池：innodb_buffer_pool_size（如系统内存的 50%–70%）
  • 排序与临时：sort_buffer_size、tmp_table_size、max_heap_table_size
  • 并发：max_connections、innodb_thread_concurrency
• 索引策略
  • 为高频过滤/关联/排序字段建立索引；避免在大字段上建索引
  • 组合索引遵循最左前缀；定期用 EXPLAIN 检查执行计划
• 统计信息
  • 执行 ANALYZE TABLE table_name; 更新索引统计，提升执行计划质量
• 资源与权限
  • 为分析账号按需授予 SELECT 权限；导出/导入使用受限账号与专用目录
• 版本建议
  • 充分利用窗口函数与新特性，建议使用 MariaDB 10.2+ 或更高版本