필터링 된 단어와 필터링된 횟수를 DB에 업데이트하고, 횟수가 많은 것들을 통계성 데이터로 뽑고싶다
기획팀으로부터 온 요구사항이다.
public void insertAllFilteredWords(Map<String, Integer> map) {
String sql = """
INSERT INTO filter_words (word, count)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE
count = count + VALUES(count)
""";
List<Map.Entry<String, Integer>> entries = new ArrayList<>(map.entrySet());
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, entries.get(1).getKey());
ps.setInt(2, entries.get(1).getValue());
}
@Override
public int getBatchSize() {
return entries.size();
}
});
}우선 채팅에서 필터링된 단어들과 통계를 메모리에 저장해놓고
새벽에 스케쥴링을 돌려서 배치로 insert할 계획이다.
ON DUPLICATE KEY UPDATE가 뭐야?
이 쿼리를 처음 써봤는데
insert를 하돼, 만약에 엔티티가 이미 있다면 update를 하는 방식의 sql이라고 한다.
찾아보니 이런 내용이 있었다.
공식문서도 봐보자.
If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:
유니크 인덱스나, 프라이머리 키에서 중복관련 문제가 발생하면 update를 실행시킨다고 한다.
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE t1 SET c=c+1 WHERE a=1;If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.
만약에, a=1이나 b=2를 만족하는 레코드가 여러개 있다면, 그 중 하나만 업데이트된다. 어떤 데이터가 업데이트되는지 알 수 없는 것이다.
만약에 a랑 b각각 유니크 인덱스가 걸렸다고 해보자.
a= 1 // b= 5
a= 1 // b= 7
b =2 // a= 0
b =2 // a= 4이렇게 되면 모두 유니크 인덱스 때문에 중복 에러가 발생하고, 이중에 임의의 레코드 하나만 업데이트 된다는 문제를 말하는 것이다. 그러므로 유니크 인덱스가 여러개 걸려있으면 사용을 피하라는 것이다.
이점과 단점은?
참고자료에 따르면
ON DUPLICATE KEY UPDATE의 이점은
- 트랜잭션이 보장된다 :INSERT나 UPDATE되거나 // 그렇지 않으면 롤백되거나(INSERT와 UPDATE 둘 중 하나의 작업만 되지 않는다)
- 성능 최적화가 가능하다 : 기존에는 exist by를 통해서 db에 존재하는지 확인하고, insert나 update 쿼리르 날린다. ON DUPLICATE KEY UPDATE를 사용하면 I/O 횟수를 줄일 수 있다.
ON DUPLICATE KEY UPDATE의 단점은
- INSERT와 UPDATE중 어디서 에러가 발생했는지 추적이 어렵다
