Backend 개발/SQL 성능 개선 (15) 썸네일형 리스트형 HAVING 문이 사용된 SQL문 튜닝 방법 GROUP BY와 HAVING문이 사용된 SQL은 어떻게 튜닝할까? 예제로 바로 확인해보자 (필자는 MariaDB 이용)-- 테이블 생성CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(100),age INT,department VARCHAR(100),salary INT,created_at TIMESTAMP DEFAULT current_timestamp);-- 가짜 데이터 100만개 생성INSERT INTO users (NAME,age, department, salary, created_at)WITH recursive cte (n) AS ( SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE .. WHERE 문에 인덱스 vs ORDER BY 문에 인덱스 WHERE문과 ORDER BY문 중, 어디에 인덱스를 거는 것이 좋을까?정답은 없다! 실행 계획과 SQL 실행 시간을 보면서 어떻게 인덱스를 거는 게 나은지 찾아가는 게 정확하다.예제-- 테이블 생성CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(100),department VARCHAR(100),salary INT,created_at TIMESTAMP DEFAULT current_timestamp);-- 가짜 데이터 100만개 생성INSERT INTO users(NAME, department, salary, created_at)WITH recursive cte (n) AS ( SELECT 1 AS n UNION ALL SELE.. ORDER BY 문이 사용된 쿼리 튜닝 방법 ORDER BYORDER BY 문은 시간이 오래 걸리는 작업이라서 최대한 피해주는 것이 좋음다른 작업에 비해 부담스러운 작업이고 성능에 안좋은 영향을 끼치는 요소 중 하나→ 인덱스를 걸어두면 미리 정렬해둔 표이므로 ORDER BY 작업 생략 가능예제-- 테이블 생성CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(100),department VARCHAR(100),salary INT,created_at TIMESTAMP DEFAULT current_timestamp);-- 가짜 데이터 100만개 생성INSERT INTO users(NAME, department, salary, created_at)WITH recursive cte (n).. 인덱스가 동작하지 않는 경우 특정 컬럼에 인덱스를 설정했는데 성능이 개선되지 않거나 실행 계획 확인 시, 인덱스가 동작하지 않는다면 다음과 같은 원인을 추측해볼 수 있다.1. 넓은 범위의 데이터를 조회할 때옵티마이저가 넓은 범위의 데이터를 조회할 때 인덱스를 활용하는 것이 비효율적이라고 판단할 때 인덱스가 동작하지 않고 실행 계획 결과 type: ALL 이 나온다.넓은 범위의 데이터를 인덱스를 거쳐서 각 원래 테이블의 데이터를 일일이 하나씩 찾아내는 것보다, 바로 원래 테이블에 접근해서 모든 데이터를 통째로 가져와서 정렬하는 것이 효율적이라고 판단한 것. 실제 성능상으로도 풀 테이블 스캔을 통해 가져오는 것이 효율적이다.2. 인덱스를 설정한 컬럼을 가공할 때인덱스를 건 컬럼을 가공하면 MySQL과 같은 일부 RDBMS에서는 해당 인.. WHERE 문이 사용된 SQL 튜닝하기 ② 어떤 컬럼에 인덱스를 걸어야 할까?WHERE 문이 사용된 SQL 튜닝하기 에서 WHERE 문에 사용된 컬럼에 인덱스를 걸면 좋다고 했다 하지만 자주 사용되고 WHERE 절이 사용된 쿼리를 튜닝할 때 어떤 컬럼에 인덱스를 거는 것이 효율적일지 판단해야 한다. 이를 위해서 먼저 하나씩 테스트를 해보며 실행 계획을 확인해 볼 수 있다.MariaDB 예제-- 테이블 생성CREATE TABLE users( id INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(100),department VARCHAR(100),created_at TIMESTAMP DEFAULT current_timestamp);-- 가짜 데이터 100만개 생성INSERT INTO users(NAME, departm.. WHERE 문이 사용된 SQL 튜닝하기 한번에 너무 많은 데이터를 조회하는 SQL문 튜닝 방법 에서 언급했듯이 WHERE나 LIMIT을 사용하면 속도를 좀더 개선할 가능성이 높아진다고 했다. 하지만 WHERE절이 사용된 컬럼에 인덱스를 사용하면 속도가 더 빨라질 수 있다. MariaDB로 다음의 간단한 예제를 확인해본다.-- 테이블 생성CREATE TABLE users( id INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(100),department VARCHAR(100),created_at TIMESTAMP DEFAULT current_timestamp -- 생일);-- 100만개 데이터 생성INSERT INTO users(NAME, department, created_at)WITH RECURSIVE cte.. 한번에 너무 많은 데이터를 조회하는 SQL문 튜닝 방법 백엔드 조회 API 성능 개선하기 ① 에서도 언급했지만 실무에서는 DB의 쓰기 작업보다 읽기 작업, 즉 SELECT 할 때 성능 이슈가 많이 생긴다.쿼리 튜닝의 핵심은 가져오는 데이터의 개수를 줄이는 것이다. 따라서 실무에서 조회 성능이 낮을 때는 한번에 너무 많은 데이터를 조회하는 것이 아닌지 확인이 필요하다.→ limit, rownum, where 등을 이용해서 한번에 조회하는 데이터의 수를 줄이는 방법을 고려하여 성능 개선을 도모할 수 있다.LIMIT을 이용한 간단한 예제limit을 이용하여 조회 시간을 비교하는 간단한 예제를 확인해보자-- 테이블 생성CREATE TABLE users( id INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(100), age int);.. 실행 계획 컬럼, type 의미 MySQL 실행 계획 컬럼의 의미 에서 실행 계획을 조회하면 여러가지 컬럼에 대한 값을 얻을 수 있다고 했다. 여기서는 type 컬럼의 종류와 의미를 알아본다. (필는 MariaDB를 사용)ALLALL은 풀 테이블 스캔(Full Table Sacn)을 의미하며 풀 테이블 스캔은 인덱스를 활용하지 않고 테이블을 처음부터 끝까지 전부 다 뒤져서 데이터를 찾는 방식이기 때문에 비효율적 → 결과에 ALL이 나오면 성능 개선 해야겠다는 생각을 해야 함EXPLAIN SELECT * FROM [테이블명] ORDER by [컬럼] LIMIT 10;index풀 인덱스 스캔(Full Index Scan)이란 인덱스 테이블을 처음부터 끝까지 다 뒤져서 데이터를 찾는 방식이다.인덱스의 테이블은 실제 테이블보다 크기가 작기 때.. 이전 1 2 다음
