Go mysql 篇

GORM

安装

go get -u -v github.com/go-sql-driver/mysql
go get -u -v github.com/jinzhu/gorm

sql中出现`deleted_at` IS NULL

因为我的deleted_at是int型,不是timestamp所以只要要这个条件,就查询不出来数据

struct

type DBCoin struct {
    Id        int32  `json:"id" gorm:"column:id;primary_key;auto_increment"`
    Platform  string `json:"platform" gorm:"column:platform"`
    Source    string `json:"source" gorm:"column:source"`
    Target    string `json:"target" gorm:"column:target"`
    Symbol    string `json:"symbol" gorm:"column:symbol"`
    CreatedAt int32  `json:"created_at" gorm:"column:created_at"`
    UpdatedAt int32  `json:"updated_at" gorm:"column:updated_at"`
    DeletedAt int32  `json:"deleted_at" gorm:"column:deleted_at;type:int(10)"`
}

sql

var coins []DBCoin

# SELECT * FROM `coin`  WHERE `coin`.`deleted_at` IS NULL AND ((platform = 'okex') AND (deleted_at = '0'))
err = mysqlDb.Table("coin").
        Where("platform = ?", "okex").
        Where("deleted_at = ?", 0).
        Find(&coins).Error
# 修改之后
# SELECT * FROM `coin`  WHERE (platform = 'okex') AND (deleted_at = '0')
err = mysqlDb.Unscoped().Table("coin").
        Where("platform = ?", "okex").
        Where("deleted_at = ?", 0).
        Find(&coins).Error

文档

连接数据库

"github.com/jinzhu/gorm"
//这个是mysql的驱动 一定能要引入 否则会出现连接书库失败
_ "github.com/jinzhu/gorm/dialects/mysql"

db, err := gorm.Open("mysql", viper.GetString("database.mysql.host"))
if err != nil {
    fmt.Println(err)
    panic("--- 数据库连接失败")
}
defer db.Close()
fmt.Println(db)
database配置文件
database:
  mysql:
    host: root:123456@(10.211.55.33:3306)/go_test?charset=utf8&parseTime=True&loc=Local

(Error 1054: Unknown column 'created_at' in 'field list')

这是因为gorm和laravel差不多会自动维护 created_atupdated_at,所以我们取消这两个字段就行,只需要 注释 gorm.Model 即可

type GoTest struct {
    //gorm.Model
    Key   int
    Value int
}
fmt.Printf("%+v\n", db.First(&settings).Value)

ab测试有无连接池

不使用连接池

ab -T 'application/json' -p ab_settings_data -c 4 -n 10000 'http://127.0.0.1:9991/v1/other/settings'

➜  scripts bash ab_settings.sh
This is ApacheBench, Version 2.3 <$Revision: 1807734 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests

Server Software:
Server Hostname:        127.0.0.1
Server Port:            9991

Document Path:          /v1/other/settings
Document Length:        4348 bytes

Concurrency Level:      4
Time taken for tests:   9.267 seconds
Complete requests:      10000
Failed requests:        0
Total transferred:      44650000 bytes
Total body sent:        1510000
HTML transferred:       43480000 bytes
Requests per second:    1079.05 [#/sec] (mean)
Time per request:       3.707 [ms] (mean)
Time per request:       0.927 [ms] (mean, across all concurrent requests)
Transfer rate:          4705.06 [Kbytes/sec] received
                        159.12 kb/s sent
                        4864.17 kb/s total

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       2
Processing:     1    4   1.2      3      19
Waiting:        0    3   1.1      3      15
Total:          1    4   1.2      3      19

Percentage of the requests served within a certain time (ms)
  50%      3
  66%      4
  75%      4
  80%      4
  90%      5
  95%      6
  98%      7
  99%      8
 100%     19 (longest request)

使用连接池

This is ApacheBench, Version 2.3 <$Revision: 1807734 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 10000 requests
Completed 20000 requests
Completed 30000 requests
Completed 40000 requests
Completed 50000 requests
Completed 60000 requests
Completed 70000 requests
Completed 80000 requests
Completed 90000 requests
Completed 100000 requests
Finished 100000 requests

Server Software:
Server Hostname:        127.0.0.1
Server Port:            9991

Document Path:          /v1/other/settings
Document Length:        4348 bytes

Concurrency Level:      6
Time taken for tests:   35.519 seconds
Complete requests:      100000
Failed requests:        0
Total transferred:      446500000 bytes
Total body sent:        15100000
HTML transferred:       434800000 bytes
Requests per second:    2815.39 [#/sec] (mean)
Time per request:       2.131 [ms] (mean)
Time per request:       0.355 [ms] (mean, across all concurrent requests)
Transfer rate:          12276.09 [Kbytes/sec] received
                        415.16 kb/s sent
                        12691.25 kb/s total

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       7
Processing:     0    2   1.1      2      30
Waiting:        0    2   1.0      2      30
Total:          0    2   1.1      2      30

Percentage of the requests served within a certain time (ms)
  50%      2
  66%      2
  75%      3
  80%      3
  90%      3
  95%      4
  98%      5
  99%      6
 100%     30 (longest request)

使用连接池+nginx

这里慢我估计是因为nginx要输出access.log的缘故,其实本身nginx的qps是很高的能到1W+吧

This is ApacheBench, Version 2.3 <$Revision: 1807734 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 10000 requests
Completed 20000 requests
Completed 30000 requests
Completed 40000 requests
Completed 50000 requests
Completed 60000 requests
Completed 70000 requests
Completed 80000 requests
Completed 90000 requests
Completed 100000 requests
Finished 100000 requests

Server Software:        openresty
Server Hostname:        127.0.0.1
Server Port:            9999

Document Path:          /v1/other/settings
Document Length:        4348 bytes

Concurrency Level:      6
Time taken for tests:   72.868 seconds
Complete requests:      100000
Failed requests:        0
Total transferred:      452600000 bytes
Total body sent:        15100000
HTML transferred:       434800000 bytes
Requests per second:    1372.35 [#/sec] (mean)
Time per request:       4.372 [ms] (mean)
Time per request:       0.729 [ms] (mean, across all concurrent requests)
Transfer rate:          6065.69 [Kbytes/sec] received
                        202.37 kb/s sent
                        6268.06 kb/s total

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       7
Processing:     1    4   3.3      4     359
Waiting:        0    4   3.1      3     358
Total:          1    4   3.3      4     359

Percentage of the requests served within a certain time (ms)
  50%      4
  66%      5
  75%      5
  80%      6
  90%      7
  95%      8
  98%     10
  99%     11
 100%    359 (longest request)

gorm 错误

Column 'created_at' specified twice data

检查你的模型定义是否column 是否重复了


# 我这里就重复了
CreatedAt int32   `json:"created_at" gorm:"column:created_at"`
UpdatedAt int32   `json:"created_at" gorm:"column:created_at"`
``

参考文章