Golang GORM控制台输出慢SQL日志，寻求性能问题排查方案

控制台持续输出慢SQL日志，示例如下：

[206.975ms] [rows:0] INSERT INTO analytics (timeframe,rsi,ticker_symbol) VALUES ('15m',28,'KSMUSDT') ON DUPLICATE KEY UPDATE rsi=VALUES(rsi)

模型定义 #

type Ticker struct {
    Symbol             string      `json:"symbol" gorm:"primaryKey;autoIncrement:false"`
    PriceChange        string      `json:"priceChange"`
    PriceChangePercent string      `json:"priceChangePercent"`
    WeightedAvgPrice   string      `json:"weightedAvgPrice"`
    PrevClosePrice     string      `json:"prevClosePrice"`
    LastPrice          string      `json:"lastPrice"`
    LastQty            string      `json:"lastQty"`
    BidPrice           string      `json:"bidPrice"`
    BidQty             string      `json:"bidQty"`
    AskPrice           string      `json:"askPrice"`
    AskQty             string      `json:"askQty"`
    OpenPrice          string      `json:"openPrice"`
    HighPrice          string      `json:"highPrice"`
    LowPrice           string      `json:"lowPrice"`
    Volume             string      `json:"volume"`
    QuoteVolume        string      `json:"quoteVolume"`
    OpenTime           int64       `json:"openTime"`
    CloseTime          int64       `json:"closeTime"`
    FirstID            int64       `json:"firstId"`
    LastID             int64       `json:"lastId"`
    Count              int64       `json:"count"`
    Analytics          []Analytics `json:"analytics" gorm:"foreignKey:TickerSymbol"`
}

type Analytics struct {
    Timeframe    string `json:"timeframe" gorm:"primaryKey"`
    RSI          int    `json:"rsi"`
    TickerSymbol string `json:"tickersymbol" gorm:"size:191;primaryKey"`
}

数据操作代码 #

func UpdateTicker() {
    tickerList, err := exchange.GetPriceChangeStats("USDT")
    if err != nil {
        fmt.Print(err)
    }
    
    for _, t := range tickerList {
        //create model and populate from binance response
        ticker := models.Ticker{Symbol: t.Symbol, PriceChange: t.PriceChange, PriceChangePercent: t.PriceChangePercent, WeightedAvgPrice: t.WeightedAvgPrice, PrevClosePrice: t.PrevClosePrice, LastPrice: t.LastPrice, LastQty: t.LastQty, BidPrice: t.BidPrice, BidQty: t.BidQty, AskPrice: t.AskPrice, AskQty: t.AskQty, OpenPrice: t.OpenPrice, HighPrice: t.HighPrice, LowPrice: t.LowPrice, Volume: t.Volume, QuoteVolume: t.QuoteVolume, OpenTime: t.OpenTime, CloseTime: t.CloseTime, FirstID: t.FristID, LastID: t.LastID}
        //create or update
        models.DB.Clauses(clause.OnConflict{
            Columns:   []clause.Column{{Name: "symbol"}},
            DoUpdates: clause.AssignmentColumns([]string{"price_change", "price_change_percent", "weighted_avg_price", "prev_close_price", "last_price", "last_qty", "bid_price", "bid_qty", "ask_price", "ask_qty", "open_price", "high_price", "low_price", "volume", "quote_volume", "open_time", "close_time", "first_id", "last_id", "count"}),
        }).Create(&ticker)
    
        //analyze ticker technicals per timeframe provided
        go utils.AnalyzeTicker(&ticker, "15m")
        go utils.AnalyzeTicker(&ticker, "4h")
        go utils.AnalyzeTicker(&ticker, "1d")
    
    }
}


func AnalyzeTicker(ticker *models.Ticker, timeframe string) {
    
    candles, err := exchange.GetCandles(ticker.Symbol, timeframe)
    
    if err != nil {
        fmt.Println("func AnalyzeTicker Symbol: ", ticker.Symbol, err)
        return
    }
    timeSeries, err := exchange.ParseToTimeSeries(candles)
    
    if err != nil {
        fmt.Println("func AnalyzeTicker Symbol: ", ticker.Symbol, "Timeframe: ", timeframe, err)
        return
    }
    rsi := int(RSI(timeSeries).Float())
    
    analytics := models.Analytics{Timeframe: timeframe, TickerSymbol: ticker.Symbol, RSI: rsi}
    ticker.Analytics = append(ticker.Analytics, analytics)
    models.DB.Clauses(clause.OnConflict{
        Columns:   []clause.Column{{Name: "ticker_symbol"}, {Name: "timeframe"}},
        DoUpdates: clause.AssignmentColumns([]string{"rsi"}),
    }).Create(&analytics)
    fmt.Println("analyzing ", ticker.Symbol, " interval", timeframe, " RSI: ", rsi)

}

数据库表结构 #

Tickers表 #

CREATE TABLE `tickers` (
    `symbol` varchar(191) NOT NULL,
    `price_change` longtext,
    `price_change_percent` longtext,
    `weighted_avg_price` longtext,
    `prev_close_price` longtext,
    `last_price` longtext,
    `last_qty` longtext,
    `bid_price` longtext,
    `bid_qty` longtext,
    `ask_price` longtext,
    `ask_qty` longtext,
    `open_price` longtext,
    `high_price` longtext,
    `low_price` longtext,
    `volume` longtext,
    `quote_volume` longtext,
    `open_time` bigint DEFAULT NULL,
    `close_time` bigint DEFAULT NULL,
    `first_id` bigint DEFAULT NULL,
    `last_id` bigint DEFAULT NULL,
    `count` bigint DEFAULT NULL,
    PRIMARY KEY (`symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Analytics表 #

CREATE TABLE `analytics` (
    `timeframe` varchar(191) NOT NULL,
    `rsi` double DEFAULT NULL,
    `sma50` double DEFAULT NULL,
    `sma100` double DEFAULT NULL,
    `sma200` double DEFAULT NULL,
    `lower_b_band` double DEFAULT NULL,
    `upper_b_band` double DEFAULT NULL,
    `symbol` varchar(191) NOT NULL,
    PRIMARY KEY (`timeframe`,`symbol`),
    KEY `fk_tickers_analytics` (`symbol`),
    CONSTRAINT `fk_tickers_analytics` FOREIGN KEY (`symbol`) REFERENCES `tickers` (`symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

已做排查动作 #

• Ticker模型以Symbol为主键，每个交易对对应一条记录，关联多条Analytics数据
• 修改过主键字段名
• 对慢SQL执行EXPLAIN，未发现明显异常

希望找到慢SQL的原因并解决性能问题。

内容的提问来源于stack exchange，提问作者BitQueen