[mySQL/mariaDB] PK 없는 테이블 중복 데이터 삭제

데이터를 조인하여 조회하던 와중에 성능에 이슈가 생겨 확인을 해보니 PK가 설정이 안되어 있는 테이블이 어러개 발견되었다. PK가 설정되어 있지 않다보니 index도 생기지 않아 조회 성능이 매우 떨어졌던 것이다. 

 

따라서 PK 없는 테이블에서 중복된 데이터를 삭제하는 방법에 대해 포스팅 해보겠다.

- 해결방법

- ROW_NUMBER() OVER을 이용

 

SELECT 
    ROW_NUMBER() OVER(PARTITION BY EVENT_TIME ORDER BY EVENT_TIME) AS ROW_NUM 
    , EVENT_TIME 
FROM testTable

 

- EVENT_TIME으로 파티션을 생성해 주고 ROW_NUMBER() OVER을 이용해 중복된 EVENT_TIME일 경우 2이상의 값으로 넣어준다.

 

 

- 제 위의 쿼리를 이용해 ROW_NUM=1을 제외한 나머지 행을 삭제해 주면 된다.

- 조인을 이용해 위의 testTable에 ROW_NUM 컬럼을 생성해 준다. 이후 중복된 것을 삭제해 주면 된다.

 

DELETE MAIN
FROM testTable AS MAIN
JOIN  
	(SELECT 
		ROW_NUMBER() OVER(PARTITION BY EVENT_TIME ORDER BY EVENT_TIME) AS ROW_NUM 
		,EVENT_TIME 
	FROM testTable 
	) AS A
ON MAIN.EVENT_TIME = A.EVENT_TIME 
	
WHERE ROW_NUM >= 2