DAY.05

-- //////////////////////////////////
-- 5.1.3 필요 이상으로 많은 정보를 가져오는 나쁜 SQL 문
SELECT COUNT(S.emp_no) AS cnt
FROM (
	SELECT E.emp_no, DM.dept_no
	FROM (
		SELECT * FROM employees
		 WHERE gender = 'M'
		   AND emp_no > 300000
	) E
	LEFT JOIN dept_manager DM
	ON DM.emp_no = E.emp_no
) S;

-- I_성별 
EXPLAIN 
SELECT COUNT(1) 
FROM employees
 WHERE emp_no > 300000
   AND gender = 'M';
   
-- primary    
SELECT COUNT(1) 
FROM employees FORCE INDEX (PRIMARY)
 WHERE gender = 'M'
   AND emp_no > 300000;   
   

-- 179_973
SELECT COUNT(1) FROM employees WHERE gender = 'M';

-- 100_000
SELECT COUNT(1) FROM employees WHERE emp_no > 300000;


-- 5.1.4 대량의 데이터를 가져와 조인하는 나쁜 SQL 문;



-- 24
SELECT COUNT(1) FROM dept_manager;

-- 331,603
SELECT COUNT(1) FROM dept_emp;

SELECT DISTINCT dept_no FROM dept_manager;

-- 책 result
 
SELECT DE.dept_no
FROM ( SELECT DISTINCT dept_no
       FROM dept_emp
) DE
WHERE EXISTS (SELECT 1 FROM dept_manager 
              WHERE dept_no = DE.dept_no)
ORDER BY DE.dept_no;            

-- 책 original
 
SELECT straight_join  DISTINCT DE.dept_no
FROM dept_manager DM
INNER JOIN dept_emp DE
ON DE.dept_no = DM.dept_no
ORDER BY DE.dept_no;


SELECT DE.dept_no
FROM dept_emp DE
WHERE DE.dept_no IN (SELECT distinct dept_no FROM dept_manager);



-- 5.2.1 인덱스 없이 작은 규모의 데이터를 조회하는 나쁜 SQL문
EXPLAIN 
SELECT *
FROM employees 
WHERE last_name = 'Wielonsky'
AND first_name = 'Georgi';


SELECT COUNT(DISTINCT first_name) FROM employees ;
SELECT COUNT(DISTINCT last_name) FROM employees ;

-- 인덱스 생성
CREATE INDEX I_lastname_firstname 
ON employees (last_name, first_name);
-- 인덱스 삭제
DROP INDEX I_lastname_firstname ON employees;


SHOW INDEX FROM employees;


-- 5.2.2 인덱스를 하나만 사용하는 나쁜 SQL문
  
-- 첫번째 ref (cost=28.9 rows=11.1) (actual time=0.116..0.298
EXPLAIN ANALYZE 
SELECT *
FROM employees
WHERE hire_date = '1987-03-31'
AND first_name = 'matt';


-- 두번째 merge (cost=3.55 rows=1) (actual time=0.0642..0.179
EXPLAIN ANALYZE 
SELECT *
FROM employees
WHERE hire_date = '1987-03-31'
AND first_name = 'matt';

CREATE INDEX I_firstname ON employees (first_name);
DROP INDEX I_firstname ON employees;



-- 5.2.3 큰 규모의 데이터 변경으로 인덱스에 영향을 주는 나쁜 SQL문

SELECT @@autocommit; -- (세션)autocommit 상태 확인
SET autocommit = 0; -- autocommit off (끄기)
SET autocommit = 1; -- autocommit on (켜기)

-- 두번째
UPDATE emp_access_logs SET door = 'X' WHERE door = 'B';

UPDATE emp_access_logs SET door = 'B' WHERE door = 'X';

/*
I_출입문 존재: 5.453초, 5.282 초
I_출입문 삭제: 0.484초, 2.781 초 
*/

DROP INDEX I_출입문 ON emp_access_logs;
CREATE INDEX I_출입문 ON emp_access_logs(door);


-- 300,000 rows
SELECT COUNT(1) FROM emp_access_logs WHERE door = 'X';
SELECT COUNT(1) FROM emp_access_logs;


-- 5.2.4 비효율적인 인덱스를 사용하는 나쁜 SQL문


-- gender,last_name
EXPLAIN ANALYZE 
SELECT emp_no, first_name, last_name
FROM employees
WHERE gender = 'M'
AND last_name = 'Baba';



-- 기존 인덱스 삭제 후 변경 인덱스 추가
ALTER TABLE employees
DROP INDEX I_성별_성,
ADD INDEX I_lastname_gender (last_name, gender);

-- 변경 인덱스 삭제 후 기존 인덱스 추가
ALTER TABLE employees
DROP INDEX I_lastname_gender,
ADD INDEX I_성별_성 (gender, last_name);


-- last_name, gender
EXPLAIN ANALYZE
SELECT emp_no, first_name, last_name
FROM employees
WHERE last_name = 'Baba'
AND gender = 'M';


-- 5.3.1 잘못된 열 속성으로 비효율적으로 작성한 나쁜 SQL문
EXPLAIN 
SELECT dept_name, remark
FROM departments  
WHERE remark = 'active'
AND ASCII(SUBSTR(remark, 1, 1)) = 97   -- a
AND ASCII(SUBSTR(remark, 2, 1)) = 99;  -- c

SELECT dept_name, remark
FROM departments  
WHERE remark = 'active';

-- remark 컬럼의 collate general > bin
ALTER table departments
CHANGE COLUMN remark remark VARCHAR(40) NULL DEFAULT NULL
COLLATE 'utf8mb4_bin';

-- remark 컬럼의 collate bin > general
ALTER table departments
CHANGE COLUMN remark remark VARCHAR(40) NULL DEFAULT NULL
COLLATE 'utf8mb4_general_ci';


-- 5.3.2 대소문자가 섞인 데이터와 비교하는 나쁜 SQl문
-- 96 rows




-- 0.394

SELECT first_name, last_name, gender, birth_date
FROM employees
WHERE first_name = 'MARY'
AND hire_date >= '1990-01-01';

-- 0.115
EXPLAIN  
SELECT first_name, last_name, gender, birth_date
FROM employees
WHERE lower_first_name = 'mary'
AND hire_date >= '1990-01-01';


-- 1. 컬럼 하나 추가 lower_first_name
ALTER TABLE employees
ADD COLUMN lower_first_name VARCHAR(14) NOT NULL COLLATE 'utf8mb3_general_ci' AFTER first_name;

SELECT * FROM employees;

-- 2. first_name값을 모두 소문자로 변경하여 lower_first_name으로 값을 넣고싶다. 
UPDATE employees
SET lower_first_name = LOWER(first_name);

COMMIT;
SELECT * FROM employees;









EXPLAIN ANALYZE 
SELECT first_name, last_name, gender, birth_date
FROM employees FORCE INDEX (I_입사일자)
WHERE LOWER(first_name) = LOWER('MARY')
AND hire_date >= '1990-01-01';

-- 300,024 rows
SELECT COUNT(1) FROM employees;

-- 135,227 rows
SELECT COUNT(1) FROM employees where hire_date >= STR_TO_DATE('1990-01-01', '%Y-%m-%d');



-- 5.3.3 분산 없이 큰 규모의 데이터를 사용하는 나쁜 SQL문

-- 2,844,047 rows
SELECT COUNT(1) FROM salaries;

-- 255,785 rows, 파티션: 0.284ms, 노 파티션: 1.359ms

SELECT * from salaries;

SELECT COUNT(1) FROM salaries
WHERE from_date BETWEEN '2000-01-01' AND '2000-12-31';


SELECT YEAR(from_date) AS from_year, COUNT(1)
FROM salaries
GROUP BY from_year;


-- 전체 파티션이 제거
ALTER TABLE salaries  remove partitioning;

-- 파티셔닝 생성

ALTER TABLE salaries
PARTITION BY RANGE COLUMNS (from_date)
(
	PARTITION p85 VALUES LESS THAN ('1985-12-31'),
	PARTITION p86 VALUES LESS THAN ('1986-12-31'),
	PARTITION p87 VALUES LESS THAN ('1987-12-31'),	
	PARTITION p88 VALUES LESS THAN ('1988-12-31'),
	PARTITION p89 VALUES LESS THAN ('1989-12-31'),
	PARTITION p90 VALUES LESS THAN ('1990-12-31'),
	PARTITION p91 VALUES LESS THAN ('1991-12-31'),
	PARTITION p92 VALUES LESS THAN ('1992-12-31'),
	PARTITION p93 VALUES LESS THAN ('1993-12-31'),
	PARTITION p94 VALUES LESS THAN ('1994-12-31'),
	PARTITION p95 VALUES LESS THAN ('1995-12-31'),
	PARTITION p96 VALUES LESS THAN ('1996-12-31'),
	PARTITION p97 VALUES LESS THAN ('1997-12-31'),
	PARTITION p98 VALUES LESS THAN ('1998-12-31'),
	PARTITION p99 VALUES LESS THAN ('1999-12-31'),
	PARTITION p00 VALUES LESS THAN ('2000-12-31'),
	PARTITION p01 VALUES LESS THAN ('2001-12-31'),
	PARTITION p02 VALUES LESS THAN ('2002-12-31'),
	PARTITION p03 VALUES LESS THAN (maxvalue)
);

-- 파티션 스캔 비율
SELECT partition_name, table_rows, AVG_ROW_LENGTH, data_length / 1024 / 1024 AS data_size_mb
FROM information_schema.partitions
WHERE table_schema = 'tuning2'
AND TABLE_NAME = 'salaries'
AND partition_name IS NOT NULL 
ORDER BY partition_ordinal_position;