70 lines
4.0 KiB
SQL
70 lines
4.0 KiB
SQL
-- crypto_binance_huge_volume表核心索引优化脚本
|
|
-- 针对2000万+数据量的关键查询性能优化
|
|
-- 基于实际代码中的查询模式分析
|
|
|
|
-- 核心索引1: 主要查询模式索引
|
|
-- 覆盖: WHERE symbol = ? AND bar = ? AND window_size = ? ORDER BY timestamp
|
|
-- 这是代码中最常见的查询模式，用于获取特定交易对、周期、窗口大小的数据
|
|
CREATE INDEX idx_symbol_bar_window_size_timestamp ON crypto_binance_huge_volume (symbol, bar, window_size, timestamp);
|
|
|
|
-- 核心索引2: 时间范围查询索引
|
|
-- 覆盖: WHERE symbol = ? AND bar = ? AND window_size = ? AND timestamp BETWEEN ? AND ?
|
|
-- 覆盖: WHERE symbol = ? AND bar = ? AND window_size = ? AND timestamp >= ? AND timestamp <= ?
|
|
# CREATE INDEX idx_symbol_bar_window_size_timestamp_range ON crypto_binance_huge_volume (symbol, bar, window_size, timestamp);
|
|
|
|
-- 核心索引3: 巨量交易查询索引
|
|
-- 覆盖: WHERE huge_volume = 1
|
|
-- 覆盖: WHERE symbol = ? AND bar = ? AND window_size = ? AND huge_volume = 1
|
|
CREATE INDEX idx_huge_volume_symbol_bar_window_size ON crypto_binance_huge_volume (huge_volume, symbol, bar, window_size);
|
|
|
|
-- 核心索引4: 成交量比率排序索引
|
|
-- 覆盖: ORDER BY volume_ratio DESC
|
|
-- 覆盖: WHERE huge_volume = 1 ORDER BY volume_ratio DESC
|
|
CREATE INDEX idx_volume_ratio_desc ON crypto_binance_huge_volume (volume_ratio DESC);
|
|
|
|
-- 核心索引5: 价格分位数高点查询索引
|
|
-- 覆盖: WHERE close_80_high = 1, close_90_high = 1
|
|
-- 覆盖: WHERE symbol = ? AND bar = ? AND window_size = ? AND close_80_high = 1
|
|
CREATE INDEX idx_close_80_90_high ON crypto_binance_huge_volume (close_80_high, close_90_high, symbol, bar, window_size);
|
|
|
|
-- 核心索引6: 价格分位数低点查询索引
|
|
-- 覆盖: WHERE close_20_low = 1, close_10_low = 1
|
|
-- 覆盖: WHERE symbol = ? AND bar = ? AND window_size = ? AND close_20_low = 1
|
|
CREATE INDEX idx_close_20_10_low ON crypto_binance_huge_volume (close_20_low, close_10_low, symbol, bar, window_size);
|
|
|
|
-- 核心索引7: 最高价分位数查询索引
|
|
-- 覆盖: WHERE high_80_high = 1, high_90_high = 1, high_20_low = 1, high_10_low = 1
|
|
CREATE INDEX idx_high_percentiles ON crypto_binance_huge_volume (high_80_high, high_90_high, high_20_low, high_10_low, symbol, bar, window_size);
|
|
|
|
-- 核心索引8: 最低价分位数查询索引
|
|
-- 覆盖: WHERE low_80_high = 1, low_90_high = 1, low_20_low = 1, low_10_low = 1
|
|
CREATE INDEX idx_low_percentiles ON crypto_binance_huge_volume (low_80_high, low_90_high, low_20_low, low_10_low, symbol, bar, window_size);
|
|
|
|
-- 核心索引9: 复合量价尖峰查询索引
|
|
-- 覆盖: WHERE huge_volume = 1 AND (close_80_high = 1 OR close_20_low = 1)
|
|
-- 覆盖: WHERE huge_volume = 1 AND (close_90_high = 1 OR close_10_low = 1)
|
|
CREATE INDEX idx_huge_volume_price_spike ON crypto_binance_huge_volume (huge_volume, close_80_high, close_20_low, close_90_high, close_10_low, symbol, bar, window_size);
|
|
|
|
-- 核心索引10: 分组统计查询索引
|
|
-- 覆盖: GROUP BY symbol, bar, window_size
|
|
-- 覆盖: GROUP BY symbol, bar
|
|
CREATE INDEX idx_symbol_bar_window_size_group ON crypto_binance_huge_volume (symbol, bar, window_size);
|
|
|
|
-- 核心索引11: 时间戳排序索引
|
|
-- 覆盖: ORDER BY timestamp DESC/ASC
|
|
-- 优化: 查询最新数据 ORDER BY timestamp DESC LIMIT 1
|
|
CREATE INDEX idx_timestamp_desc ON crypto_binance_huge_volume (timestamp DESC);
|
|
|
|
-- 核心索引12: 日期时间查询索引
|
|
-- 覆盖: WHERE date_time >= ? AND date_time <= ?
|
|
-- 覆盖: ORDER BY date_time
|
|
CREATE INDEX idx_date_time ON crypto_binance_huge_volume (date_time);
|
|
|
|
-- 执行前检查现有索引
|
|
-- SHOW INDEX FROM crypto_binance_huge_volume;
|
|
|
|
-- 执行后验证索引效果
|
|
-- EXPLAIN SELECT * FROM crypto_binance_huge_volume WHERE symbol = 'BTC-USDT' AND bar = '5m' AND window_size = 50 ORDER BY timestamp DESC LIMIT 100;
|
|
-- EXPLAIN SELECT * FROM crypto_binance_huge_volume WHERE huge_volume = 1 AND close_80_high = 1 ORDER BY volume_ratio DESC LIMIT 10;
|
|
-- EXPLAIN SELECT COUNT(*) FROM crypto_binance_huge_volume WHERE symbol = 'BTC-USDT' AND bar = '5m' AND window_size = 50;
|