ubuntu下如何使用mysql进行数据分析

Ubuntu 下使用 MySQL 进行数据分析的实操指南

一 环境准备与连接

• 安装与启动
  • 更新索引并安装：sudo apt update && sudo apt install mysql-server
  • 检查服务状态：sudo systemctl status mysql（如未运行：sudo systemctl start mysql）
• 安全初始化（首次安装建议执行）：sudo mysql_secure_installation（设置 root 密码、移除匿名用户等）
• 登录与基础操作
  • 登录：mysql -u root -p
  • 查看库/表：SHOW DATABASES;、USE db;、SHOW TABLES;
• 命令行快速查询
  • 非交互执行：mysql -h localhost -u root -p -e “SELECT COUNT(*) FROM your_table;”
• 远程连接
  • 连接串：mysql -h 主机 -u 用户名 -p -P 端口（如端口非 3306 需用 -P 指定）

二 数据导入导出与磁盘占用

• 导出数据库或表
  • 导出整个库：mysqldump -h 主机 -u 用户名 -p 数据库名 > 数据库名.sql
  • 仅导出结构：mysqldump -h 主机 -u 用户名 -p -d --add-drop-table 数据库名 > schema.sql
• 导入数据
  • 方式一（命令行）：mysql -u 用户名 -p 数据库名 < file.sql
  • 方式二（客户端）：登录后 USE 数据库; 再执行 SOURCE /path/file.sql;
• 查看磁盘占用
  • 文件系统层：sudo du -sh /var/lib/mysql（MySQL 默认数据目录）
  • 按数据库统计（MB）：
    • SELECT table_schema AS ‘Database’, SUM(data_length + index_length)/1024/1024 AS ‘Size (MB)’ FROM information_schema.tables GROUP BY table_schema;
  • 按表统计（MB）：
    • SELECT table_name AS ‘Table’, (data_length + index_length)/1024/1024 AS ‘Size (MB)’ FROM information_schema.tables WHERE table_schema = ‘你的数据库名’;

三 常用 SQL 分析范式

• 聚合统计
  • 总数/均值/极值：COUNT(*)、SUM(col)、AVG(col)、MAX(col)、MIN(col)
• 分组与筛选
  • 按类别计数：SELECT category, COUNT(*) FROM t GROUP BY category;
  • 条件聚合：SELECT AVG(col) FROM t WHERE cond GROUP BY …
• 占比与比率
  • 条件占比：*SELECT (COUNT(CASE WHEN cond THEN 1 END)/COUNT(*))100 AS pct FROM t;
• 多表关联
  • 关联分析：SELECT a.x, b.y, COUNT(*) FROM a JOIN b ON a.k=b.k GROUP BY a.x, b.y;
• 时间维度
  • 按月统计：SELECT DATE_FORMAT(created_at, ‘%Y-%m’) AS m, COUNT(*) FROM t GROUP BY m;
• 性能提示
  • 大数据量分析前，为过滤/分组/关联字段建立合适索引，可显著提升查询性能

四 与 Python 和可视化结合

• 使用 Python 拉取数据
  • 安装驱动：pip install pymysql pandas
  • 连接与读取：
    • import pymysql, pandas as pd
    • conn = pymysql.connect(host=‘localhost’, user=‘your_user’, password=‘your_pass’, db=‘your_db’, charset=‘utf8mb4’)
    • df = pd.read_sql_query(“SELECT * FROM your_table”, conn); conn.close()
• 分析与可视化
  • 描述统计：df.describe()
  • 可视化：matplotlib/seaborn/plotly 进行柱状图、折线图、分布图等展示

五 性能监控与优化

• 快速体检
  • MySQLTuner：sudo apt-get install mysqltuner，运行 mysqltuner 获取配置与性能建议
• 深度诊断与优化
  • Percona Toolkit：包含 pt-query-digest（慢查询分析）、pt-table-checksum（数据一致性校验）等
• 监控体系
  • Prometheus + Grafana + mysqld_exporter：采集 MySQL 指标并可视化展示（适合长期观测与容量规划）