تصميم قاعدة بيانات السلاسل الزمنية والتحليلات

الخصائص: - حجم كتابة عالي (آلاف في الثانية) - استعلامات قائمة على الوقت (آخر ساعة، يوم، شهر) - غالباً إلحاق فقط (نادراً ما يتم تحديث البيانات القديمة) - البيانات تنمو باستمرار - التجميعات عبر فترات زمنية حالات الاستخدام الشائعة: - مراقبة أداء التطبيقات (APM) - بيانات مستشعرات إنترنت الأشياء - بيانات التداول المالي - تحليلات سلوك المستخدم - مقاييس النظام والسجلات - تحليلات حركة مرور الموقع

-- بيانات المقاييس (محسّنة للسلاسل الزمنية) CREATE TABLE metrics ( id BIGINT UNSIGNED AUTO_INCREMENT, metric_name VARCHAR(100) NOT NULL, metric_value DOUBLE NOT NULL, tags JSON COMMENT 'أبعاد إضافية: {host, region, etc}', recorded_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (recorded_at, id), INDEX idx_metric_time (metric_name, recorded_at), INDEX idx_recorded (recorded_at) ) ENGINE=InnoDB PARTITION BY RANGE (UNIX_TIMESTAMP(recorded_at)) ( PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')), PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01')), PARTITION p202403 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')), PARTITION p202404 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-01')), PARTITION p_future VALUES LESS THAN MAXVALUE ); -- تتبع مشاهدات الصفحة CREATE TABLE page_views ( id BIGINT UNSIGNED AUTO_INCREMENT, user_id BIGINT UNSIGNED NULL, session_id VARCHAR(100) NOT NULL, page_url VARCHAR(500) NOT NULL, referrer_url VARCHAR(500), user_agent TEXT, ip_address VARCHAR(45), country_code CHAR(2), device_type ENUM('desktop', 'mobile', 'tablet') NOT NULL, viewed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (viewed_at, id), INDEX idx_user_time (user_id, viewed_at), INDEX idx_session (session_id, viewed_at), INDEX idx_url_time (page_url(100), viewed_at) ) ENGINE=InnoDB PARTITION BY RANGE (TO_DAYS(viewed_at)) ( PARTITION p_week1 VALUES LESS THAN (TO_DAYS('2024-01-08')), PARTITION p_week2 VALUES LESS THAN (TO_DAYS('2024-01-15')), PARTITION p_week3 VALUES LESS THAN (TO_DAYS('2024-01-22')), PARTITION p_week4 VALUES LESS THAN (TO_DAYS('2024-01-29')), PARTITION p_future VALUES LESS THAN MAXVALUE );

-- الحصول على المقاييس لآخر ساعة SELECT metric_name, AVG(metric_value) AS avg_value, MAX(metric_value) AS max_value, MIN(metric_value) AS min_value, COUNT(*) AS data_points FROM metrics WHERE recorded_at >= NOW() - INTERVAL 1 HOUR GROUP BY metric_name; -- الحصول على مشاهدات الصفحة في الساعة لآخر 24 ساعة SELECT DATE_FORMAT(viewed_at, '%Y-%m-%d %H:00:00') AS hour, COUNT(*) AS views, COUNT(DISTINCT session_id) AS unique_sessions, COUNT(DISTINCT user_id) AS unique_users FROM page_views WHERE viewed_at >= NOW() - INTERVAL 24 HOUR GROUP BY hour ORDER BY hour; -- الحصول على أفضل الصفحات حسب المشاهدات SELECT page_url, COUNT(*) AS view_count, COUNT(DISTINCT user_id) AS unique_visitors FROM page_views WHERE viewed_at >= NOW() - INTERVAL 7 DAY GROUP BY page_url ORDER BY view_count DESC LIMIT 20; -- حساب المتوسط المتحرك (نوافذ 5 دقائق) SELECT DATE_FORMAT(recorded_at, '%Y-%m-%d %H:%i:00') AS time_bucket, metric_name, AVG(metric_value) AS avg_value FROM metrics WHERE recorded_at >= NOW() - INTERVAL 1 HOUR AND metric_name = 'cpu_usage' GROUP BY time_bucket, metric_name ORDER BY time_bucket;

-- التجميعات الساعية CREATE TABLE metrics_hourly ( metric_name VARCHAR(100) NOT NULL, hour_start TIMESTAMP NOT NULL, avg_value DOUBLE NOT NULL, min_value DOUBLE NOT NULL, max_value DOUBLE NOT NULL, sum_value DOUBLE NOT NULL, count_value BIGINT UNSIGNED NOT NULL, PRIMARY KEY (metric_name, hour_start), INDEX idx_hour (hour_start) ) ENGINE=InnoDB; -- التجميعات اليومية CREATE TABLE metrics_daily ( metric_name VARCHAR(100) NOT NULL, date DATE NOT NULL, avg_value DOUBLE NOT NULL, min_value DOUBLE NOT NULL, max_value DOUBLE NOT NULL, sum_value DOUBLE NOT NULL, count_value BIGINT UNSIGNED NOT NULL, PRIMARY KEY (metric_name, date), INDEX idx_date (date) ) ENGINE=InnoDB; -- ملء التجميعات الساعية (تشغيل كل ساعة) INSERT INTO metrics_hourly SELECT metric_name, DATE_FORMAT(recorded_at, '%Y-%m-%d %H:00:00') AS hour_start, AVG(metric_value) AS avg_value, MIN(metric_value) AS min_value, MAX(metric_value) AS max_value, SUM(metric_value) AS sum_value, COUNT(*) AS count_value FROM metrics WHERE recorded_at >= DATE_FORMAT(NOW() - INTERVAL 1 HOUR, '%Y-%m-%d %H:00:00') AND recorded_at < DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') GROUP BY metric_name, hour_start ON DUPLICATE KEY UPDATE avg_value = VALUES(avg_value), min_value = VALUES(min_value), max_value = VALUES(max_value), sum_value = VALUES(sum_value), count_value = VALUES(count_value);

-- جدول الحقائق: معاملات المبيعات CREATE TABLE fact_sales ( sale_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, date_key INT UNSIGNED NOT NULL, product_key INT UNSIGNED NOT NULL, customer_key INT UNSIGNED NOT NULL, store_key INT UNSIGNED NOT NULL, quantity INT UNSIGNED NOT NULL, unit_price DECIMAL(10, 2) NOT NULL, discount_amount DECIMAL(10, 2) DEFAULT 0, total_amount DECIMAL(10, 2) NOT NULL, profit_amount DECIMAL(10, 2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_date (date_key), INDEX idx_product (product_key), INDEX idx_customer (customer_key), INDEX idx_store (store_key) ) ENGINE=InnoDB; -- جدول البعد: التاريخ CREATE TABLE dim_date ( date_key INT UNSIGNED PRIMARY KEY, full_date DATE NOT NULL, year INT NOT NULL, quarter INT NOT NULL, month INT NOT NULL, week INT NOT NULL, day_of_month INT NOT NULL, day_of_week INT NOT NULL, day_name VARCHAR(20) NOT NULL, month_name VARCHAR(20) NOT NULL, is_weekend BOOLEAN NOT NULL, is_holiday BOOLEAN DEFAULT FALSE, UNIQUE KEY unique_date (full_date), INDEX idx_year_month (year, month) ) ENGINE=InnoDB;

-- أرشفة الأقسام القديمة ALTER TABLE metrics DROP PARTITION p202401; -- النقل إلى جدول الأرشيف قبل الإسقاط CREATE TABLE metrics_archive LIKE metrics; ALTER TABLE metrics_archive REMOVE PARTITIONING; INSERT INTO metrics_archive SELECT * FROM metrics PARTITION (p202401); -- ثم إسقاط القسم ALTER TABLE metrics DROP PARTITION p202401; -- التنظيف التلقائي (حذف البيانات الأقدم من 90 يوماً) DELETE FROM app_events WHERE created_at < NOW() - INTERVAL 90 DAY LIMIT 10000;