본문 바로가기

SQL 튜닝24

[SQL 튜닝] 분산 없이 큰 규모의 데이터를 사용하는 나쁜 SQL 문 분산 없이 큰 규모의 데이터를 사용하는 나쁜 SQL 문 현황 분석 | 튜닝 전 실행 계획 | 급여 테이블에서 시작일자가 2000년 1월 1일부터 2000년 12월 31일에 해당하는 데이터를 모두 집계하는 쿼리 즉, 2000년도의 급여 데이터 건수를 조회 EXPLAIN SELECT COUNT(1) FROM 급여 WHERE 시작일자 BETWEEN STR_TO_DATE('2000-01-01', '%Y-%m-%d') AND STR_TO_DATE('2000-12-31', '%Y-%m-%d'); - SQL문 결과 : 총 1건의 결과(255,785) 출력, 338ms 소요 - 급여 테이블의 I_사용여부 인덱스(key 항목 : I_사용여부)를 활용해서 커버링 인덱스로 수행 - 테이블 접근 없이 인덱스만으로 원하는 데이.. 2023. 8. 14.
[SQL 튜닝] 대소문자가 섞인 데이터와 비교하는 나쁜 SQL 문 대소문자가 섞인 데이터와 비교하는 나쁜 SQL 문 현황 분석 | 튜닝 전 실행 계획 | 사원 테이블에서 입사일자가 1990년 이후이고, 이름이 MARY인 사원정보를 조회하는 쿼리 이때 MARY와 1990-01-01은 입력된 변수 값이므로 매번 입력되는 영문의 대소문자는 고정되지 않음 EXPLAIN SELECT 이름, 성, 성별, 생년월일 FROM 사원 WHERE LOWER(이름) = LOWER('MARY') AND 입사일자 >= STR_TO_DATE('1990-01-01', '%Y-%m-%d'); - SQL문 결과 : 총 96건의 결과 출력, 88.6ms 소요 - 테이블 풀 스캔(type 항목 : ALL)으로 수행 - 이름 열은 기본적으로 LOWER() 함수가 가공, 이름 열로 만들어진 인덱스가 존재하더.. 2023. 8. 9.
[SQL 튜닝] 잘못된 열 속성으로 비효율적으로 작성한 나쁜 SQL 문 잘못된 열 속성으로 비효율적으로 작성한 나쁜 SQL 문 현황 분석 | 튜닝 전 실행 계획 | 부서 테이블의 비고 열값이 소문자 'active' 일 때의 데이터를 조회하는 쿼리 SUBSTR(비고,1,1)과 SUBSTR(비고,2,1)라는 조건절로 비고 열의 데이터에서 첫 번째 문자와 두번째 문자를 각각 추출한 뒤 ASCII 코드값을 비교(아스키 코드 값 : 97 = a, 99 = c 에 해당) EXPLAIN SELECT 부서명, 비고 FROM 부서 WHERE 비고 = 'active' AND ASCII(SUBSTR(비고,1,1)) = 97 AND ASCII(SUBSTR(비고,2,1)) = 99; - SQL문 결과 : 총 4건의 결과(active인 값들) 출력, 0.4ms 소요 (소요 시간이 매우 짧음) 튜닝 .. 2023. 8. 8.
[SQL 튜닝] 비효율적인 인덱스를 사용하는 나쁜 SQL 문 비효율적인 인덱스를 사용하는 나쁜 SQL 문 현황 분석 | 튜닝 전 실행 계획 | 사원 테이블에서 성별이 M(남성)이고 성이 Baba인 정보 조회하는 쿼리 EXPLAIN SELECT 사원번호, 이름, 성 FROM 사원 WHERE 성별 = 'M' AND 성 ='Baba'; - SQL문 결과 : 총 135건의 결과 출력, 5.7ms 소요 (소요 시간이 매우 짧음) - I_성별_성 인덱스를 활용하여 사원 테이블에 접근 - 성별과 성 열에 고정된 값으로 조건절(ref 항목 : const,const)을 작성하여, 스토리지 엔진에서 인덱스 스캔으로 원하는 데이터 가져와서 출력 튜닝 수행 - 조건절에 작성된 열 현황 확인 - 성 열 : 1,637건, 성별 열 : 2건 SELECT COUNT(distinct 성) 성_.. 2023. 8. 4.
[SQL 튜닝] 큰 규모의 데이터 변경으로 인덱스에 영향을 주는 나쁜 SQL 문 큰 규모의 데이터 변경으로 인덱스에 영향을 주는 나쁜 SQL 문 현황 분석 | 튜닝 전 수행 결과 | 사원출입기록 테이블의 출입문 열에 'B'로 저장된 데이터를 'X'로 변경하는 쿼리 MYSQL에서 DML(data manipulation language) 문을 수행할 때 커밋은 기본적으로 자동 저장이 됨 튜닝 전 SQL 문은 UPDATE 문이므로 별도의 설정을 하지 않으면 바로 자동 저장됨 따라서 반복되는 UPDATE 문을 수행할 때 자동 저장되는 커밋 설정을 본인이 접속한 세션에 한해서만 자동 저장되지 않도록 변경 SELECT @@autocommit; /* 1 */ SET autocommit=0; SELECT @@autocommit /* 0 */ - autocommit이 1이면 자동 커밋, 0이면 자동.. 2023. 8. 3.
[SQL 튜닝] 인덱스를 하나만 사용하는 나쁜 SQL 문 인덱스를 하나만 사용하는 나쁜 SQL 문 현황 분석 | 튜닝 전 실행 계획 | 이름이 Matt이거나 입사일자가 1987년 3월 31일인 사원정보를 조회하는 쿼리 EXPLAIN SELECT * FROM 사원 WHERE 이름 = 'Matt' OR 입사일자 = '1987-03-31'; - SQL문 결과 : 총 343건의 결과 출력, 102ms 소요 (소요 시간이 매우 짧음) - 튜닝 여부를 판단할 때 짧은 소요시간만을 기준으로 삼지 않음 → 더 나은 쿼리로 변경할 수 있음 - 사원 테이블 : 테이블 풀스캔(type항목 : ALL)로 처리 - 스토리지 엔진으로 모든 데이터를 가져온 뒤 MYSQL 엔진에서 2개의 조건절을 활용하여 데이터를 필터링 튜닝 수행 - 우선, 조건절에 해당하는 데이터 분포 확인 /* 30.. 2023. 8. 2.

티스토리툴바