Gorm 관련글은 처음 쓴다.
우선 지난달에 이어 투표/설문조사 관련해서 이제 성능개선을 하고 있는 와중 데이터 삽입의 로직이 약간 변화되어 벌크업설트를 해야 할 일이 생겼다. 그래서 뭐가 어떻게 다른지 확인해 보기 간단한 예제를 준비했다.
type Workout struct {
Id uint `gorm:"column:id;type:int;primaryKey"`
Title string `gorm:"column:title;type:varchar(20)"`
Raps uint `gorm:"column:raps;type:int"`
}요런 테이블을 생성해주고
이렇게 데이터를 미리 생성해서 넣어주었다.
Gorm에서 Upsert를 해서 일반적으로는 insert를, pk 의 중복이 생길시 특정 저 raps 만 업데이트하고자 한다면 아래와 같이 작성하면 된다.
Upsert [성능개선이 필요했던 부분]
func (w *Workout) Upsert(db *gorm.DB) error {
return db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "id"}},
DoUpdates: clause.Assignments(map[string]interface{}{
"raps": gorm.Expr("raps + ?", w.Raps),
}),
}).Create(w).Error
}테스트를 돌려보면
func TestWorkoutUpsert(t *testing.T) {
db := table.GetDB().Db
for i := 1; i <= 3; i++ {
w := &table.Workout{
Id: i,
Raps: i * i * 3,
}
if err := w.Upsert(db); err != nil {
t.Error(err)
}
}
2023/09/07 21:41:19 /Users/guiwoopark/Desktop/personal/study/db_design/table/workout.go:28
[10.822ms] [rows:2] INSERT INTO `workout` (`title`,`raps`,`id`) VALUES ('',3,1) ON DUPLICATE KEY UPDATE `raps`=raps + 3
2023/09/07 21:41:19 /Users/guiwoopark/Desktop/personal/study/db_design/table/workout.go:28
[4.083ms] [rows:2] INSERT INTO `workout` (`title`,`raps`,`id`) VALUES ('',12,2) ON DUPLICATE KEY UPDATE `raps`=raps + 12
2023/09/07 21:41:19 /Users/guiwoopark/Desktop/personal/study/db_design/table/workout.go:28
[4.204ms] [rows:2] INSERT INTO `workout` (`title`,`raps`,`id`) VALUES ('',27,3) ON DUPLICATE KEY UPDATE `raps`=raps + 27이런식의 쿼리가 생성된다.
for loop로 돌다 보니 확실히 커넥션을 가져오는 횟수가 늘어난다 이를 방지하기 위해 벌크 어설트를 한다면 중복된 키값에 대해 동작하는 방식을 단일되게 설정해줘야 한다.
[문제점]
커넥션을 효율적으로 사용하기위해 한번의 커넥션과 벌크 인설트가 필요하다. gorm 에서 제공하는 create 를 사용했더니
list 의 업데이트 하고자 했던 부분이 하나의 값으로 밖에 업데이트를 할수 없는 상황이였다.
func (w *Workout) Upsert(db *gorm.DB) error {
return db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "id"}},
DoUpdates: clause.Assignments(map[string]interface{}{
"raps": gorm.Expr("raps + ?", "어떤 값을 집어넣어줘야 할까 ..."),
}),
}).Create(w).Error
}즉 리스트 별로 각자 다른 값이 있고 업데이트를 하고 싶지만 모두 동일한 값이 아니면 넣어줄수 없다...
제공 안 해주면 어떻게 하나, 직접 작성해야지...
이를 해결하기위해 아래와 같은 방식으로 작성하였다.
BatchUpsert[변경한 부분]
func (w *Workout) BatchUpsert(db *gorm.DB, data []Workout) error {
var (
value []string
valueArgs []interface{}
)
for _, v := range data {
value = append(value, ("(?,?,?)"))
valueArgs = append(valueArgs, v.Id)
valueArgs = append(valueArgs, v.Title)
valueArgs = append(valueArgs, v.Raps)
}
prep := "insert into workout(id,title,raps) values %s on duplicate key update raps = raps+values(raps)"
sql := fmt.Sprintf(prep, strings.Join(value, ","))
if err := db.Exec(sql, valueArgs...).Error; err != nil {
db.Rollback()
return err
}
return nil
}단순 sql 문을 실제로 작성해 주는 부분이다. gorm value와 같은 도움을 받아 value 같을 매칭 시켜준다.
sql을 실제로 찍어보면 이런 식으로 들어간다.
insert into workout(id,title,raps) values (?,?,?), (?,?,?), (?,?,?) on duplicate key update raps = raps+values(raps)
func TestWorkoutBatchUpsert(t *testing.T) {
db := table.GetDB().Db
list := make([]table.Workout, 0, 3)
for i := 1; i <= 3; i++ {
w := table.Workout{
Id: i,
Raps: i * i * 3,
}
list = append(list, w)
}
var workout table.Workout
if err := workout.BatchUpsert(db, list); err != nil {
t.Error(err)
}
}
insert into workout(id,title,raps) values (1,'',3),(2,'',12),(3,'',27) on duplicate key update raps = raps+values(raps)이 결과 sql 의 ? 부분들은 생성된 value들이 매칭되어 들어가 sql 쿼리가 만들어진다.
BatchUpdate[대안]
업설트가 아닌 위와 같이 업데이트 만 필요한 상황이라면? 업데이트 만하는 게 더 좋다고 본다.
왜냐하면 on duplicate update는 먼저 insert 이후 업데이트를 시도하기 때문이다.
func (w *Workout) BatchUpdate(db *gorm.DB, data []Workout) error {
var (
caseSql []string
whereSql []string
caseArgs []interface{}
whereArgs []interface{}
)
for _, v := range data {
caseSql = append(caseSql, "when ? then raps + ?")
caseArgs = append(caseArgs, v.Id, v.Raps)
whereArgs = append(whereArgs, v.Id)
whereSql = append(whereSql, "?")
}
prep := "update workout set raps = case id %s end where id in (%s)"
sql := fmt.Sprintf(prep, strings.Join(caseSql, " "), strings.Join(whereSql, ","))
caseArgs = append(caseArgs, whereArgs...)
if err := db.Exec(sql, caseArgs...).Error; err != nil {
return err
}
return nil
}when case 문을 활용해서 작성했다. 확실히 Upsert 보다 가독성이 많이 떨어진다.
func TestWorkoutBatchUpdate(t *testing.T) {
db := table.GetDB().Db
list := make([]table.Workout, 0, 3)
for i := 1; i <= 3; i++ {
w := table.Workout{
Id: i,
Raps: i * i * 3,
}
list = append(list, w)
}
var workout table.Workout
err := workout.BatchUpdate(db, list)
if err != nil {
t.Error(err)
}
}
update workout set raps = case id when 1 then raps + 3 when 2 then raps + 12 when 3 then raps + 27 end where id in (1,2,3)원하는 방식대로 쿼리가 나간다.
1000 개 비교
그렇다면 업설트와 업데이트의 차이는 어는 정도 있는지 궁금해졌다.
[32.416ms] insert into workout(id,title,raps) values
[6.349ms] [rows:3] update workout set raps =
뒤에 내용은 길어서 삭제했다. 대충 여러번 돌려본 결과 약 3배 정도 ms 차이가 발생한다.
다시 말해 update or insert의 기능이 아닌 단순 업데이트만 필요하다면? 배치 업데이트를 사용하자.
회사에서 성능개선으로 고친부분 으로는 이 쿼리가 실행되는 시점은 절대 pk 값이 없을수가 없다. 로직 자체를 변경했다.
따라서 위의 단순 비교로만 본다면 해당 부분에서 약 3배의 성능 이점을 얻은거로 판단된다.
SQL INJECTION [왜 ?]
작성하면서 ? 부분이 왜 필요한가에 대해 확인해 봤다. SQL Injection이라는 어택을 방어하기 위해서 필요한 부분이다.
sql injection 이란 ? 사용자가 임의의 sql 문을 집어넣어서 프로그램 실행에 방해 혹은 버그를 주는 공격을 말한다.
사용자가 주는 값을 그대로 받지않고 ? 부분을 사용해 스트링 값이 아닌 공간을 할당하는 변수가 포함되어 있어 sql 문을 실행하기 전 원하는 값으로 대체해준다.
예를 들어
// 1번케이스
sql := fmt.Sprintf("select name from user where id = %s","사용자의 입력값")
// select name from user where id = (select id from item where id =123 )
// 2번 케이스
sql := fmt.Sprintf("select name from user where id = ?")
db.Exec(sql,사용자의입력값)
// select name from user where id = 'select id from item where id =123'위와 아래는 엄청난 차이가 있다. 아래에서는 사용자의 입력값을 말그대로 숫자 혹은 스트링으로 만 넣어주는 반면 위에 처럼 한번 생성하게 되면 만약 사용자가 select id from table where id =123과 같은 값을 넣었을 때 해당 부분이 서브쿼리로 실행되고
2번째 sql 은 'select id from table where id =123' 이렇게 스트링 자체로 실행 된다.
테이블 타입, 메서드 : https://github.com/Guiwoo/go_study/blob/master/db_design/table/workout.go
테스트 코드 : https://github.com/Guiwoo/go_study/blob/master/db_design/test/workout_test.go
출처 - https://zhiruchen.github.io/2017/08/31/bulk-insert-bulk-query-with-gorm/
bulk insert, bulk query with gorm · zbbbbbblog
zhiruchen.github.io
'Go > Gorm 삽질기' 카테고리의 다른 글
| MYSQL - 유휴커넥션에 대해서 (0) | 2024.09.10 |
|---|