使用golang插入mysql性能提升经验

刺骨的言语ヽ痛彻心扉 2022-06-02 04:42 192阅读 0赞

## 前言 ##

golang可以轻易制造高并发，在某些场景很合适，比如爬虫的时候可以爬的更加高效。但是对应某些场景，如文件读写，数据库访问等IO为瓶颈的场合，就没有什么优势了。

## 前提基础 ##

**1、golang数据库访问**  
在golang中数据库访问使用”database/sql”包提供的接口，不同的数据库，比如pg、mysql只需要提供对应的驱动就可以了。注意”database/sql”包提供的接口只针对关系型数据库，nosql如redis和mongodb都是直接使用对应的客户端包，不实现”database/sql”包提供的接口。关于”database/sql”包，这里不做讲述，后续在基础回顾上巩固下。总体上就是提供了连接、事务处理、还有就是打开的时候注意打开的时候并没有连接，而是产生一个池，每次有交互的时候才产生一个连接（事务交互除外）。

**2、数据库插入优化基础**  
1）插入无索引表会比插入有索引的表快，毕竟建立索引总是要增加一些额外操作  
2）插入小表比插入大表快，业务一般插入速度是以条数计算，大表一条记录比较大，需要IO的时间比较长。  
3）多个连接一起插入会比单连接快，因为mysql不是单线程。  
4）日志缓存增大可以加快插入速度，因为减少了IO访问次数。  
5）一次插入多条数据可以加快插入速度。

## 实践经验 ##

ps：以小表做实验，都用一个环境，比较差异。  
表结构：

create table lamp( id bigint not null primary key, state char(1), collecttime timestamp);

**1、无任何优化，一条条插入，且使用同一个链接**  
代码片段：

fmt.Println(time.Now().Unix())
        _, err = db.Prepare("INSERT INTO lamp (id, state, collecttime)VALUES(?,'0', '20180103002930')")
        if err != nil {
            fmt.Println(err)
            return
        }
        for i := 0; i < 100000; i++ {
            _, err := db.Exec(execstring + data)
            if err != nil {
                fmt.Println(err)
                return
            }
        }
        fmt.Println(time.Now().Unix())

结果：

1514911765
    1514912248

使用了483s 平均100000/500 大概是**200次每秒**。

2、单连接，使用事务。

fmt.Println(time.Now().Unix())
        insert, err = db.Prepare("INSERT INTO lamp (id, state, collecttime)VALUES(?,'0', '20180103002930')")
        if err != nil {
            fmt.Println(err)
            return
        }
        begin, err := db.Begin()
        if err != nil {
            fmt.Println(err)
            return
        }
        for i := 0; i < 100000; i++ {
            _, err := begin.Stmt(insert).Exec(i)
            if err != nil {
                fmt.Println(err)
                return
            }
        }
        err = begin.Commit()
        if err != nil {
            fmt.Println(err)
            return
        }
        fmt.Println(time.Now().Unix())

**运行结果**

1514910923
    1514911049

使用了129s 平均100000/125， 约为**800次每秒**

**3、批量插入，每1W条执行一次插入操作。，注意max\_allowed\_packet要设置的足够大**

fmt.Println(time.Now().Unix())
        for i := 0; i < 1000; i++ {
            for j := i * 10000; j < i*10000+10000; j++ {
                if j < i*10000+9999 {
                    id := strconv.Itoa(j)
                    onedata := "(" + id + ", '0', '20180103002930'), "
                    data = data + onedata
                } else {
                    id := strconv.Itoa(j)
                    onedata := "(" + id + ",'0', '20180103002930')"
                    data = data + onedata
                }
            }       
            _, err := db.Exec(execstring + data)
            if err != nil {
                fmt.Println(err)
                return
            }
        }
        fmt.Println(time.Now().Unix())

结果：

1514969811
    1514970318

使用了507s 平均10000000/500， 约为**2W次每秒**

**4、并发插入，使用100个协程插入**

fmt.Println(time.Now().Unix())
        intertnumber := 0
        for i := 0; i < 10; i++ {
            value := i
            go func() {
                execstring := "INSERT INTO lamp (id, state, collecttime)VALUES"
                for k := value; k < 1000; k = k + 10 {
                    data := " "
                    for j := k * 10000; j < k*10000+10000; j++ {
                        if j < k*10000+9999 {
                            id := strconv.Itoa(j)
                            onedata := "(" + id + ", '0', '20180103002930'), "
                            data = data + onedata
                        } else {
                            id := strconv.Itoa(j)
                            onedata := "(" + id + ",'0', '20180103002930')"
                            data = data + onedata
                        }
    
                    }
                   //fmt.Println(execstring + data)
                    _, err := db.Exec(execstring + data)
                    if err != nil {
                        fmt.Println(err)
                        return
                    }
                    intertnumber = intertnumber + 10000
                }
            }()
        }
        for intertnumber < 9999999 {
            time.Sleep(1 * time.Second)
        }
        fmt.Println(time.Now().Unix())

运行结果：

1514974432
    1514974796

使用了363s 平均10000000/500， 约为**2.7W次每秒**

**4、1千W条数据，开1000个协程做插入操作，每次插入1W条数据。mysql最大连接数设置为2048**  
运行结果：

mysql宕机，CPU,MEM使用过高，IO使用并不高。

## 总结： ##

从程序层面上看：  
1、使用事务会比较快一些。  
2、多连接插入会快很多，当读写成为瓶颈的时候，效果就不太明显。  
3、一次插入多条数据也会快很多。  
4、**高并发大量插入请求，mysql服务的应对措施是宕机，而不是拒绝请求。**（这个跟笔者代码也有一定关系，因为100个协程前面都是再抢CPU构造插入请求，几乎都是同时向mysql请求），**mysql在高并发场景，如果承受不住会宕机，这点在设计上需要注意。**