为什么这么少的代码需要11秒才能执行这几个数据库调用？

Question

I have this bit of code:

package tradebot

import (
    "log"
    "net/http"
    "strconv"

    "github.com/Philipp15b/go-steam/tradeoffer"
    "github.com/gorilla/mux"
)

func AcceptTrade(w http.ResponseWriter, r *http.Request) {

    vars := mux.Vars(r)
    var tradeId tradeoffer.TradeOfferId
    var sTradeId string
    if _, has := vars["TradeId"]; has {
        var err error
        sTradeId = vars["TradeId"]
        id, err := strconv.ParseUint(vars["TradeId"], 10, 64)
        if err != nil {
            http.Error(w, "Error accepting trade offer", 500)
        }
        tradeId = tradeoffer.TradeOfferId(id)
    }

    err := Trader.Accept(tradeId)
    if err != nil {
        log.Println(err.Error())
        http.Error(w, "Failed to accept trade try again after", 500)
    }
    rows, err := Db.Query("SELECT DepositedBy, Price FROM skinbank WHERE tradeofferid=? AND accepted=?", sTradeId, 0)
    if err != nil {
        log.Println(err)
        http.Error(w, "Your trade offer was accepted but there was an error on our side. Please open a ticket with the trade ID: "+sTradeId+" so we can address this issue. Thank you and sorry for the inconvenience", 500)
    }
    foundRows := false
    for rows.Next() {
        foundRows = true
        var price float64
        var depositedby string
        err = rows.Scan(&depositedby, &price)
        if err != nil {
            log.Println(err)
            http.Error(w, "Your trade offer was accepted but there was an error on our side. Please open a ticket with the trade ID: "+sTradeId+" so we can address this issue. Thank you and sorry for the inconvenience", 500)
        }
        if err != nil {
            log.Println(err)
            http.Error(w, "Your trade offer was accepted but there was an error on our side. Please open a ticket with the trade ID: "+sTradeId+" so we can address this issue. Thank you and sorry for the inconvenience", 500)
        }
        log.Println(depositedby, price)
        _, err := Db.Query("UPDATE accounts SET credits = credits + ? WHERE steamid=?", price, depositedby)
        if err != nil {
            log.Println(err)
            http.Error(w, "Your trade offer was accepted but there was an error on our side. Please open a ticket with the trade ID: "+sTradeId+" so we can address this issue. Thank you and sorry for the inconvenience", 500)
        }
    }

    _, err = Db.Query("UPDATE skinbank SET accepted=? WHERE tradeofferid=?", 1, sTradeId)
    if err != nil {
        log.Println(err)
        http.Error(w, "Your trade offer was accepted but there was an error on our side. Please open a ticket with the trade ID: "+sTradeId+" so we can address this issue. Thank you and sorry for the inconvenience", 500)
    }
    log.Println("Trade accepted: " + sTradeId)
    if foundRows {
        http.Redirect(w, r, "/profile", 303)
    } else {
        http.Error(w, "No trade with that id, either doesn't exist or already accepted.", 500)
    }
}

It is mostly just a few MySQL calls to the database to update / get a bit of information, I was wondering if anybody can tell me what I have done inefficiently here that it's causing this to take 11 seconds to run (with about 6 items to loop in the for rows.Next() call.

Answer 1

Specimen A:

rows, err := Db.Query("SELECT DepositedBy, Price FROM skinbank 
WHERE tradeofferid=? AND accepted=?", sTradeId, 0)

Specimen B: (inside for loop)

_, err := Db.Query("UPDATE accounts SET credits = credits + ? WHERE steamid=?", price, depositedby)

Specimen C:

_, err = Db.Query("UPDATE skinbank SET accepted=? WHERE tradeofferid=?", 1, sTradeId)

Put profiling timing statement around these. If you don't know how, ask. Show the table schemas if you need more help by issuing show create table skinbank and show create table credits. This will enable some clue on indexes. Tell us the rowcounts of the two tables.

If you have a ton of rows, it is possible without proper indexes that you are performing tablescans, not fast lookups, to get to the rows in the update where clause. Heck, in the select too. Right now you don't know the timings.

At the very least, have indexes on

skinbank(tradeofferid,accepted) -- a composite index, useful for Specimen A
credits(steamid) -- useful for Specimen B

As for specimen C, it should pick up the left-most of the composite index just suggested. So a separate index on just skinbank(tradeofferid) is overkill if you are going the composite route.