72 lines
3.4 KiB
SQL
72 lines
3.4 KiB
SQL
-- crypto_binance_data表索引优化脚本
|
|
-- 针对500万+数据量的查询性能优化
|
|
|
|
-- 1. 主要查询索引 - 覆盖最常见的查询模式
|
|
-- 支持: WHERE symbol = ? AND bar = ? ORDER BY timestamp
|
|
CREATE INDEX idx_symbol_bar_timestamp ON crypto_binance_data (symbol, bar, timestamp);
|
|
|
|
-- 2. 时间范围查询索引 - 优化时间范围查询
|
|
-- 支持: WHERE symbol = ? AND bar = ? AND timestamp BETWEEN ? AND ?
|
|
CREATE INDEX idx_symbol_bar_timestamp_range ON crypto_binance_data (symbol, bar, timestamp);
|
|
|
|
-- 3. 按symbol分组查询索引 - 优化GROUP BY symbol查询
|
|
-- 支持: GROUP BY symbol, bar
|
|
CREATE INDEX idx_symbol_bar ON crypto_binance_data (symbol, bar);
|
|
|
|
-- 4. 时间戳排序索引 - 优化ORDER BY timestamp查询
|
|
-- 支持: ORDER BY timestamp DESC/ASC
|
|
CREATE INDEX idx_timestamp ON crypto_binance_data (timestamp);
|
|
|
|
-- 5. 技术指标查询索引 - 优化技术指标相关查询
|
|
-- 支持: WHERE symbol = ? AND bar = ? AND macd_signal = ?
|
|
CREATE INDEX idx_symbol_bar_macd_signal ON crypto_binance_data (symbol, bar, macd_signal);
|
|
|
|
-- 支持: WHERE symbol = ? AND bar = ? AND kdj_signal = ?
|
|
CREATE INDEX idx_symbol_bar_kdj_signal ON crypto_binance_data (symbol, bar, kdj_signal);
|
|
|
|
-- 支持: WHERE symbol = ? AND bar = ? AND rsi_signal = ?
|
|
CREATE INDEX idx_symbol_bar_rsi_signal ON crypto_binance_data (symbol, bar, rsi_signal);
|
|
|
|
-- 支持: WHERE symbol = ? AND bar = ? AND boll_signal = ?
|
|
CREATE INDEX idx_symbol_bar_boll_signal ON crypto_binance_data (symbol, bar, boll_signal);
|
|
|
|
-- 支持: WHERE symbol = ? AND bar = ? AND ma_cross = ?
|
|
CREATE INDEX idx_symbol_bar_ma_cross ON crypto_binance_data (symbol, bar, ma_cross);
|
|
|
|
-- 6. 复合技术指标查询索引 - 优化多条件技术指标查询
|
|
-- 支持: WHERE symbol = ? AND bar = ? AND macd_signal = ? AND kdj_signal = ?
|
|
CREATE INDEX idx_symbol_bar_macd_kdj ON crypto_binance_data (symbol, bar, macd_signal, kdj_signal);
|
|
|
|
-- 7. 日期时间查询索引 - 优化按日期时间查询
|
|
-- 支持: WHERE date_time >= ? AND date_time <= ?
|
|
CREATE INDEX idx_date_time ON crypto_binance_data (date_time);
|
|
|
|
-- 8. 交易对时间索引 - 优化特定交易对的时间查询
|
|
-- 支持: WHERE symbol = ? ORDER BY timestamp
|
|
CREATE INDEX idx_symbol_timestamp ON crypto_binance_data (symbol, timestamp);
|
|
|
|
-- 9. 周期时间索引 - 优化特定周期的时间查询
|
|
-- 支持: WHERE bar = ? ORDER BY timestamp
|
|
CREATE INDEX idx_bar_timestamp ON crypto_binance_data (bar, timestamp);
|
|
|
|
-- 10. 统计查询优化索引 - 优化COUNT, AVG等聚合查询
|
|
-- 支持: SELECT COUNT(*) FROM crypto_binance_data WHERE symbol = ? AND bar = ?
|
|
CREATE INDEX idx_symbol_bar_volume ON crypto_binance_data (symbol, bar, volume);
|
|
|
|
-- 11. 价格相关查询索引 - 优化价格相关查询
|
|
-- 支持: WHERE symbol = ? AND bar = ? AND close > ? AND close < ?
|
|
CREATE INDEX idx_symbol_bar_close ON crypto_binance_data (symbol, bar, close);
|
|
|
|
-- 12. 成交量相关查询索引 - 优化成交量相关查询
|
|
-- 支持: WHERE symbol = ? AND bar = ? AND volume > ?
|
|
CREATE INDEX idx_symbol_bar_volume_high ON crypto_binance_data (symbol, bar, volume);
|
|
|
|
-- 删除可能存在的重复或低效索引
|
|
-- 注意：在实际执行前请先检查现有索引，避免删除正在使用的索引
|
|
|
|
-- 查看当前索引状态
|
|
-- SHOW INDEX FROM crypto_binance_data;
|
|
|
|
-- 分析查询性能
|
|
-- EXPLAIN SELECT * FROM crypto_binance_data WHERE symbol = 'BTC-USDT' AND bar = '5m' ORDER BY timestamp DESC LIMIT 100;
|