概述：小弟的程序可以通過輸入數(shù)據(jù)庫類型、數(shù)據(jù)庫連接字符串、執(zhí)行 sql 或者含有 sql 的文件等，將數(shù)據(jù)庫中的 sql 查詢出來并寫入 excel
性能：目前導(dǎo)出 25W 條數(shù)據(jù)，每行數(shù)據(jù) 5 個(gè)字段，數(shù)據(jù)庫類型 oracle，驅(qū)動(dòng) goracle，excel 生成庫使用 excelize，用時(shí) 1 分 33 秒；同環(huán)境使用 python3.6，數(shù)據(jù)庫驅(qū)動(dòng) cx_Oracle，excel 生成庫使用 pyexcelerate，同樣的查詢語句，用時(shí) 51s （是的…沒干過 python/(ㄒoㄒ)/~~）
程序描述：為了提升執(zhí)行效率，小弟使用了 goruntine，一個(gè)線程專門執(zhí)行 sql 并將結(jié)果生成[]interface{}并裝入通道，另外一個(gè)線程不斷的從通道中取出[]interface{}并寫入 excel
個(gè)人感覺可能存在的問題點(diǎn)：
1、golang 的數(shù)據(jù)庫查詢方式只能一條一條生成，同時(shí)，機(jī)制用到了反射，而不像 python 可以通過 fetchmany 一次性獲取大量數(shù)據(jù)，不知道此處是否會(huì)有性能差距
2、當(dāng)字段類型是 date 類型時(shí)，當(dāng)字段為空時(shí)，如果不做 isZero 判斷，輸出到 excel 的日期零值很異常（值為-5XXXXX，顯示為##########）。所以每取出一條[]interface{}，都需要挨個(gè)判斷類型是不是日期，如果是日期的話，是不是零值，此處可能會(huì)影響效率。而 python 沒有這個(gè)問題

請(qǐng)各位 golang 大大給提點(diǎn)優(yōu)化意見吧，謝謝大家
代碼如下：

package main

import (
    _ "gopkg.in/goracle.v2"
    _ "github.com/asifjalil/cli"
    "github.com/jmoiron/sqlx"
    "flag"
    "fmt"
    "github.com/axgle/mahonia"
    "strings"
    "os"
    "strconv"
    "io/ioutil"
    "time"
    "github.com/360EntSecGroup-Skylar/excelize"
    "runtime"
)

func DataGetter(db *sqlx.DB,query string,rowChan chan <- []interface{},columnChan chan <- []string){
    defer db.Close()
    row,err := db.Queryx(query)
    if err != nil{
        panic(err)
    }
    defer row.Close()
    columns,err := row.Columns()
    columnChan <- columns
    close(columnChan)
    if err !=nil {
        panic(fmt.Sprint("failed to add sheet:%s",err.Error()))
    }
    for row.Next(){
        r,err := row.SliceScan()
        if err !=nil{
            panic("db row query failed")
        }
        rowChan <- r
    }
    close(rowChan)
}

func ExcelWriter(sheetHead string,fileName string,rowChan <- chan[]interface{},columnChan <- chan[]string){
    cnt := 2
    sheetcnt := 1
    var r []interface{}
    columns := <- columnChan
    hasNext := true
    excel := excelize.NewFile()
    excel.NewSheet(sheetHead)
    excel.SetSheetRow(sheetHead,"A1",columns)
    //excel.SetSheetRow(sheetHead,"A"+strconv.Itoa(cnt),columns)
    for hasNext{
        r,hasNext = <- rowChan
        for a := 0;a<len(columns);a++{
            t,ok := r[a].(time.Time)
            if ok{
                if t.IsZero(){
                    excel.SetCellValue(sheetHead,excelize.ToAlphaString(a)+strconv.Itoa(cnt),"")
                }else{
                    excel.SetCellValue(sheetHead,excelize.ToAlphaString(a)+strconv.Itoa(cnt),t)
                }
            }else{
                excel.SetCellValue(sheetHead,excelize.ToAlphaString(a)+strconv.Itoa(cnt),r[a])
            }
        }
        cnt = cnt + 1
        if cnt >= 100000{
            excel.NewSheet(sheetHead+strconv.Itoa(sheetcnt))
            sheetHead = sheetHead+strconv.Itoa(sheetcnt)
            excel.SetSheetRow(sheetHead,"A1",columns)
            cnt = 2
            sheetcnt = sheetcnt + 1
        }
    }
    excel.SaveAs(fileName+".xlsx")
}



func getConn(dbconn string,dbtype string)(db *sqlx.DB){
    if dbtype == "oracle"{
        driver := "goracle"
        return sqlx.MustOpen(driver,strings.Replace(dbconn,":","/",1))
    }else if dbtype == "db2"{
        driver := "cli"
        userPart := strings.Split(dbconn,"@")[0]
        username := strings.Split(userPart,":")[0]
        password := strings.Split(userPart,":")[1]
        dbPart := strings.Split(dbconn,"@")[0]
        dbname := strings.Split(dbPart,"/")[1]
        dbip := strings.Split(strings.Split(dbPart,"/")[0],":")[0]
        dbport := strings.Split(strings.Split(dbPart,"/")[0],":")[1]
        connString := fmt.Sprintf("Driver={IBM DB2 ODBC Driver};Hostname=%s;Port=%s;Protocol=TCPIP;Database=%s;CurrentSchema=%s;UID=%s;PWD=%s;",
            dbip,dbport,dbname,username,password)
        return sqlx.MustOpen(driver,connString)
    }else if dbtype == "postgres"{
        driver := "postgres"
        connString := "postgres://" + dbconn
        return sqlx.MustOpen(driver,connString)
    }else{
        fmt.Println("dbtype not matched!")
        os.Exit(-1)
        return
    }
}

func main() {
    //輸入?yún)?shù)解析
    dbconn := flag.String("d","",`Database connect string,use "user:password@ip:port/dbname" for db2 or "user:password@tnsname" for oracle`)
    dbtype := flag.String("t","","Database type:oracle db2 mysql mssql")
    filetype := flag.String("f","xlsx","exported file type:xlsx or txt default:xlsx")
    //xlsx require options
    sheetname := flag.String("h","Sheet1","sheet name: default Sheet1")
    //txt require options
    //charset := flag.String("c","utf-8","charset for exported text file:gbk utf-8 and so on")
    //separator := flag.String("s","/","separator: default:/")
    //sql options
    query := flag.String("q","","sql in one line")
    sqlfile := flag.String("l","","sqlfile")
    filename := flag.String("n",time.Now().Format("20060102150405"),"filename")
    flag.Parse()

    if *dbconn == "" || *dbtype == "" || *filetype == ""{
        flag.Usage()
        return
    }
    if *query == "" && *sqlfile == ""{
        flag.Usage()
        return
    }
    if *sqlfile != "" {
        sqlbyte,err := ioutil.ReadFile(*sqlfile)
        if err != nil{
            panic("read sqlfile failed!")
        }
        utf8 := mahonia.NewEncoder("utf-8")
        *query = utf8.ConvertString(string(sqlbyte))
    }
    runtime.GOMAXPROCS(2)
    if *filetype == "xlsx"{
        rowChan := make(chan []interface{},50000)
        columnsChan := make(chan []string)
        db := getConn(*dbconn,*dbtype)
        go DataGetter(db,*query,rowChan,columnsChan)
        ExcelWriter(*sheetname,*filename,rowChan,columnsChan)
        //}else if *filetype == "txt"{
        //    db := getConn(*dbconn,*dbtype)
        //    TextFileExporter(db,*charset,*separator,*filename,*query)
        //}else{
        flag.Usage()
        return
    }

}