在 Debian 上使用 MariaDB 进行数据分析
一 环境准备与安装
- 更新系统并安装 MariaDB:
- sudo apt update && sudo apt install -y mariadb-server mariadb-client
- 启动并设置开机自启:
- sudo systemctl start mariadb
- sudo systemctl enable mariadb
- 运行安全初始化向导,设置 root 密码、移除匿名用户、禁止远程 root 登录等:
- sudo mysql_secure_installation
- 登录数据库验证:
- 可选:安装图形化管理工具 phpMyAdmin(便于导入数据与日常运维):
- sudo apt install -y phpmyadmin
二 数据导入与建模
- 创建示例库表(销售事实表):
- CREATE DATABASE IF NOT EXISTS sales_dw;
- USE sales_dw;
- CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100),
sale_amount DECIMAL(10,2),
sale_date DATE,
region VARCHAR(50)
);
- 导入数据方式:
- 从 .sql 备份恢复:mysql -u root -p sales_dw < /path/to/data.sql
- 命令行批量导入 CSV(示例,路径与列顺序需匹配):
- LOAD DATA INFILE ‘/path/to/sales.csv’
INTO TABLE sales
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS;
- 建模要点:
- 为分析高频字段(如 sale_date、region)建立索引,提升筛选与分组性能。
- 大表按时间做分区(如按月份/年份),便于快速裁剪历史数据并加速查询。
三 典型分析 SQL 示例
- 基础查询与筛选:
- 按地区筛选:SELECT * FROM sales WHERE region = ‘East’;
- 金额阈值:SELECT * FROM sales WHERE sale_amount > 1000;
- 聚合统计:
- 总销售额:SELECT SUM(sale_amount) AS total_sales FROM sales;
- 按产品平均销售额:SELECT product_name, AVG(sale_amount) AS avg_sale FROM sales GROUP BY product_name;
- 按月销售额趋势:
- SELECT DATE_FORMAT(sale_date, ‘%Y-%m’) AS month, SUM(sale_amount) AS monthly_sales
FROM sales
GROUP BY month
ORDER BY month;
- 多表关联与子查询:
- 关联产品维表(假设有 products 表:product_name, category):
- SELECT s.product_name, p.category, s.sale_amount
FROM sales s
JOIN products p ON s.product_name = p.product_name;
- 高于平均销售额的产品:
- SELECT product_name, sale_amount
FROM sales
WHERE sale_amount > (SELECT AVG(sale_amount) FROM sales);
四 性能优化与可维护性
- 索引与执行计划:
- 为筛选/分组字段建立索引:CREATE INDEX idx_sale_date ON sales(sale_date);
- 使用 EXPLAIN 检查是否走索引、避免全表扫描。
- 慢查询与性能模式:
- 启用慢查询日志(编辑 /etc/mysql/mariadb.conf.d/50-server.cnf):
- [mysqld]
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/log/mysql/slow-query.log
log_output = FILE
- 重启后分析:mysqldumpslow -t 5 -s at /var/log/mysql/slow-query.log
- 使用 performance_schema 定位最耗时语句:
- SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;
- InnoDB 缓冲池(示例将缓冲池设为系统内存的约 70%,请按实际调整):
- [mysqld]
innodb_buffer_pool_size = 1G # 示例值
- 表维护与统计:
- 定期执行 ANALYZE TABLE 更新统计信息;对高写入/删除表按需执行 OPTIMIZE TABLE 减少碎片。
五 可视化与扩展
- 图形化工具:
- 使用 MySQL Workbench、DBeaver、HeidiSQL 执行查询、导出结果与报表。
- 编程分析链路:
- 通过 Python 的 pymysql 或 mysql-connector-python 将 MariaDB 数据导入 Pandas,再用 Matplotlib/Seaborn 可视化;适合复杂统计与建模。
- Web 报表与可视化:
- 结合 LAMP/phpMyAdmin 导出 CSV,或在 PHP 页面中集成 Chart.js/Highcharts 生成交互式图表,用于业务看板与报表。