🚴‍♂️ MyCycly 数据库全景手册：从“骑行记录”到“数据大屏”一次搞定！

专为 骑行爱好者 & 俱乐部 打造的轻量级数据库方案
支持 单车/用户/路线/活动/成绩 全链路，
今天 10 分钟，带你拆完 ER 图 → 建表 SQL → 索引 → 统计视图 → Python 连接 → 数据大屏🔧

1️⃣ 业务场景：先讲故事再建模📖

• 用户注册 → 录入单车 → 创建/加入俱乐部
• 发起活动（路线+时间）→ 成员报名 → 手机记录 .gpx 上传
• 系统自动算：距离/爬升/均速/卡路里 → 排行榜 → 月度勋章

一句话：“骑、晒、比、赞” 全都要落库🚀

---

2️⃣ ER 图：5 大核心实体（MySQL 8.0）

用户 1───n 单车
  |
  n
活动 1───n 成绩记录
  |
路线 1───n 活动

---

3️⃣ 建表 SQL：复制直接跑✅

CREATE DATABASE mycycly CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 用户
CREATE TABLE user (
    id            BIGINT PRIMARY KEY AUTO_INCREMENT,
    username      VARCHAR(30) NOT NULL UNIQUE,
    email         VARCHAR(50) NOT NULL UNIQUE,
    pwd_hash      CHAR(60)    NOT NULL,
    gender        ENUM('M','F','O'),
    birth_date    DATE,
    weight_kg     DECIMAL(4,1),
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 单车
CREATE TABLE bike (
    id            BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id       BIGINT NOT NULL,
    brand         VARCHAR(40),
    model         VARCHAR(40),
    weight_kg     DECIMAL(4,1),
    is_default    TINYINT(1) DEFAULT 0,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

-- 路线
CREATE TABLE route (
    id            BIGINT PRIMARY KEY AUTO_INCREMENT,
    name          VARCHAR(100) NOT NULL,
    distance_m    INT NOT NULL,
    elevation_m   INT NOT NULL,
    gpx_file      VARCHAR(255),
    created_by    BIGINT NOT NULL,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES user(id)
);

-- 活动
CREATE TABLE activity (
    id            BIGINT PRIMARY KEY AUTO_INCREMENT,
    route_id      BIGINT NOT NULL,
    title         VARCHAR(120) NOT NULL,
    start_time    DATETIME NOT NULL,
    join_end      DATETIME,
    created_by    BIGINT NOT NULL,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (route_id) REFERENCES route(id),
    FOREIGN KEY (created_by) REFERENCES user(id)
);

-- 成绩记录（核心事实表）
CREATE TABLE record (
    id            BIGINT PRIMARY KEY AUTO_INCREMENT,
    activity_id   BIGINT NOT NULL,
    user_id       BIGINT NOT NULL,
    bike_id       BIGINT,
    distance_m    INT NOT NULL,
    duration_s    INT NOT NULL,
    elevation_m   INT DEFAULT 0,
    avg_speed_kmh DECIMAL(4,1),
    calories      INT,
    gpx_file      VARCHAR(255),
    recorded_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (activity_id) REFERENCES activity(id),
    FOREIGN KEY (user_id)     REFERENCES user(id),
    FOREIGN KEY (bike_id)     REFERENCES bike(id)
);

---

4️⃣ 索引 & 分区：百万记录不卡顿🚀

-- 高频查询列
CREATE INDEX idx_record_user_date  ON record(user_id, recorded_at);
CREATE INDEX idx_record_act        ON record(activity_id);
CREATE INDEX idx_activity_start    ON activity(start_time);

-- 按月分区（MySQL 8.0 RANGE COLUMNS）
ALTER TABLE record
PARTITION BY RANGE COLUMNS (recorded_at) (
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
    ...
);

---

5️⃣ 统计视图：排行榜 & 仪表盘📊

-- 用户累计里程/爬升
CREATE VIEW v_user_total AS
SELECT u.id, u.username,
       SUM(r.distance_m)/1000   AS total_km,
       SUM(r.elevation_m)       AS total_ele,
       COUNT(DISTINCT r.id)     AS ride_cnt
FROM user u
JOIN record r ON u.id = r.user_id
GROUP BY u.id;

-- 活动实时均速排行榜
CREATE VIEW v_activity_leader AS
SELECT a.id, a.title, u.username,
       r.distance_m/1000 AS km,
       r.duration_s/3600 AS hrs,
       (r.distance_m/1000)/(r.duration_s/3600) AS avg_speed_kmh
FROM record r
JOIN activity a ON r.activity_id = a.id
JOIN user u     ON r.user_id     = u.id
WHERE a.id = ?   -- 传入参数
ORDER BY avg_speed_kmh DESC;

---

6️⃣ Python 连接：pymysql + 连接池🔌

import pymysql
from dbutils.pooled_db import PooledDB

pool = PooledDB(pymysql, maxconnections=20,
                host='127.0.0.1', user='mycycly', passwd='***',
                database='mycycly', charset='utf8mb4')

def get_leaderboard(activity_id):
    with pool.connection() as conn:
        with conn.cursor(pymysql.cursors.DictCursor) as cur:
            cur.execute("""SELECT username, km, avg_speed_kmh
                             FROM v_activity_leader
                            WHERE id = %s""", (activity_id,))
            return cur.fetchall()

---

7️⃣ 数据大屏：Streamlit 5 行📺

import streamlit as st, pandas as pd

st.title("MyCycly 实时大屏")
df = pd.DataFrame(get_leaderboard(sel_id))
st.dataframe(df)
st.bar_chart(df.set_index('username')['avg_speed_kmh'])

streamlit run dashboard.py → 浏览器自动打开

---

8️⃣ 备份 & 恢复：定时 crontab🕐

# 每天 2 点全量备份
0 2 * * * mysqldump -umycycly -p*** mycycly \
        | gzip > /backup/mycycly_$(date +\%F).sql.gz

恢复：gunzip < mycycly_2024-06-01.sql.gz | mysql -umycycly -p*** mycycly

---

9️⃣ 安全 & 规范：生产级 checklist🔒

1. 密码 → 8 位随机 + 定期轮换
2. SQL 注入 → 全部 %s 占位符（见上代码）
3. 连接池 → 避免每请求新建 TCP
4. 索引覆盖 → EXPLAIN 查看 type=range/ref
5. GDPR 合规 → 用户随时导出/删除个人 .gpx

---

🔟 扩展路线：从 MySQL → 数据湖☁️

阶段	技术	说明
初期	MySQL + 索引	万级用户够用
中期	MySQL → TiDB / PolarDB	水平扩展
海量	MySQL + Canal → Kafka → ClickHouse	实时 OLAP 大屏
全球	MongoDB Atlas / DynamoDB	多活 Region

---

🏁 一句话口诀（背它！）

“用户-单车-路线-活动-记录五张表，索引分区防卡顿；
**视图出排行榜，Python 连接池 + Streamlit 出大屏，备份安全要上心！”**🚴‍♂️🔚