SQL/Mysql (12) 썸네일형 리스트형 업무에 바로쓰는 SQL튜닝_정리 1. 조인알고리즘용어 중첩루프조인(NL조인) 드라이빙 테이블 데이터 1건당 드리븐 테이블 반복검색 - 최종 양쪽 데이터 출력 블록 중첩 루프 종인(BNL) 조인버퍼 찰때까지 적재한후 드리븐 테이블을 접근 배치키 엑세스조인 조인버퍼 + 랜덤버퍼 -> 드리븐 테이블 접근 해시조인 각 테이블의 데이터를 내부적 해시값을 만들어 조인 수행 결과는 조인버퍼에 저장되어 조인열의 인덱스 요구 X 2. 기본 튜닝용어 오브젝트 스캔 : 오브젝트 - 테이블 스캔 - 인덱스 스캔 TABLE FULL SCAN INDEX RANGE SCAN INDEX FULL SCAN INDEX UNIQUE SCAN - 기본키나 고유인덱스로 테이블 접근하는 방식 (가장 효율적) INDEX LOOSE SCAN - 인덱스의 필요한 부분만 골라서 스.. mysql 사용자 정의 변수(a.k.a.사용자함수) 총정리 [출처:Real Mysql] mysql의 변수 - 시스템 변수 - 글로벌 변수 - 사용자 변수 - 세션변수 1. 사용자 정의 변수 "@"로 시작 커넥션정의된 사용자변수는 다른커넥션과 공유하지 못하고 해당 커넥션에서만 사용 별도의 타입을 지정하는것이 아니라 저장하는 값에 의해 타입이 정해진다(= 스크립트언어와 동일) set@var := 'my first user variable'; set@var1 = 'my first', @var2 = 'user variable'; select @var as var1, concat(@var1, ' ', @var2) as var2; set @rownum = 0; -- set으로 rownum을 정의하고 초기값 0 할당 select (@rownum:= @rownum +1) as rownum, emp_no,.. Mysql- insert절 용법 총정리(Real Mysql) 1. insert auto_increment - 테이블 컬럼에 부여하는 옵션형태로 한 테이블에 serial increment number 컬럼의 기능을 제공한다. 1-1. insert와 auto_increment - 사용방법: CTA구문에 혹은 create table 구문으로 테이블을 생성 후, insert into 테이블명 (컬럼명_1) values ( 'value명'); or insert into 테이블명(컬럼명1, 컬럼명2) values ('value명1','values명2'); -- auto increment값의 현재값을 저장하려면 insert into 테이블명(null or 0) values ('value명1','values명2'); 하면된다. - 특성: 강제저장한값이 auto_increment.. MYSQL all about Select절 (REAL MY SQL 정리) ◈ select 더보기 - mysql 의 limit은 where 조건으로 사용하지않고 모든 처리결과의 레코드건수를 제한하는 형태로 사용한다. - where, order by , group by가 인덱스를 사용하려면 컬럼값을 변형하지않고 그대로사용해야 한다. - where 절에 or조건이있다면 인덱스 머지, 등의 방법을 구사해야한다. - group by 절의 컬럼순서가 인덱스 컬럼 순서와 같으면 group by 절은 인덱스를 탈 수 있다. - order by 절의 모든 컬럼이 asc, desc일때만 인덱스를 탈 수 있다. - where + order by 절이 같은 컬럼이 연속 포함될 경우 같은 인덱스를 탈 수 있다. - where절에 일치하는 레코드건수 많지않을 때 where절만 인덱스를 이용하는 튜닝방.. MYSQL 연산자/like연산자/between연산자/in연산자 (REAL MY SQL 정리) 1. 연산자 1-1. 동등비교 (양쪽모두 null-> true반환, 한쪽만 null -> false반환), = 1-2. 부정비교(, !=, !(false->0, true->1) 1-3. and(&&)와 or(||) 연산자 set sql_mode='pipes_as_concat'; --||을 oracle연산자와 동일하게 변경 select 'abc'||'def' as concated_string; -- 결과값 abcdef 1-4. 나누기(/, div)와 나머지(mod, %) 연산자 1-5. regexp연산자 ex> 'x','y','z'문자로 시작하는 검증 표현식 select 'abc' regexp '^[x-z]'; - 시작 문자패턴 검증식: ^ - 끝 문자패턴 검증식: $ - 문자그룹표시 [] - 이중 하나만.. MYSQL 내장함수 / 문자열처리/타입변환/처리대기/벤치마크/암호화/aggregation function/주석 (REAL MY SQL 정리) ◈ mysql 내장함수 1. null값 비교 및 대체 (ifnull, isnull) 2. 현재시각 조회(now, sysdate), sleep(2) 16진수 - unhex: 16진수 -> 2진 - 암호화 및 해시함수(md5, sha) ◈ 처리대기(sleep) - ex> sleep(2) -- 처리대기 2초의 간격을 준다 ◈ 벤치마크(bench mark) - benchmark(반복해서 수행할 횟수, 반복해서 실행할 표현식= 반드시 스칼라값 반환하는 표현식이어야한다.) - select expr X 10번 vs select benchmark(10,expr) : 후자는 한번의 요청으로 expr이 실행되는것이므로 이미 할당받은 메모리 자원까지 공유되므로 전자에 비해 1/10밖에 자워소모가 되지 않는다. 사실 큰의미있.. MYSQL 리터럴 모음 (REAL MY SQL 정리) MYSQL 리터럴 모음 ◈ 리터럴 표기법 CASE1) 문자열 SELECT * FROM DEPARTMENTS WHERE DEPT_NO = 'D001'; SELECT * FROM DEPARTMENTS WHERE DEPT_NO = "D001"; CASE2)문자열 SELECT * FROM DEPARTMENTS WHERE DEPT_NO = 'D''001'; --결과값 나옴 SELECT * FROM DEPARTMENTS WHERE DEPT_NO = 'D"001'; --결과값 나옴 SELECT * FROM DEPARTMENTS WHERE DEPT_NO = "D'001"; -- 결과값 나옴 SELECT * FROM DEPARTMENTS WHERE DEPT_NO = "D"001"; -- 결과값 안나옴 CASE3) 숫자 .. MYSQL 매뉴얼(Document)읽는 방법 (REAL MY SQL 정리) Mysql 매뉴얼 Document의 sql문법 표기 읽는 방법 더보기 (dev.mysql.com/doc/refman/8.0/en/tutorial.html) MySQL :: MySQL 8.0 Reference Manual :: 3 Tutorial dev.mysql.com ▶ Mysql reference manual 8.0 이상 version ◈ MYSQL DOCUMENT보는 방법 - 대문자: 키워드 - 이탤릭체: 사용자작성 토근 (테이블명 or 컬럼명등의 표현식) - 대괄호([]): 해당키워드나 표현식이 optional - 파이프(|): 앞 or 뒤의 표현식중 하나만 선택할 수 있음을 의미 - 중괄호({}): 괄호내의 아이템 중 반드시 하나를 사용해야 하는 경우 - ...: 앞의 명시 키워드나 표현식의 조.. MYSQL 쿼리 작성 및 최적화 관련 (REAL MY SQL 정리) 07. 쿼리작성 및 최적화 애플리케이션 코드와달리 sql은 작동방식이나 원리에 따라 2배이상의 성능개선을 할 수 잇으므로 sql의 작동원리와 유용한 쿼리패턴을 체크해보자 1. 쿼리와 연관된 시스템설정 1.1 sql모드 - STRICT_ALL_TABLES: 일반적으로 MYSQL에서는 컬럼값이 지정된 길이보다 길어도 에러가 발생하지 않지만 SQL_MODE 시스템변수에 "STRICT_ALL_TABLES"를 설정하면 에러를 발생시키고 쿼리가 중지된다. - STRICT_TRANS_TABLES: 컬럼 비호환값의 저장시 MYSQL 서버는 비슷한값을 바꿔서 저장하려고하는데 "STRICT_TRANS_TABLES"설정하면 MYSQL 서버의 강제전환을 막는다. - TRADITIONAL: 앞의 두예약어보다 빡센 방법으로 SQ.. Mysql 실행계획 오라클처럼 보는 방법: 명령어 설정 explain format = tree -> MYSQL의 실행계획을 오라클 처럼 볼 수 있다. 기타 MYSQL의 특징 - mysql 8.0.18ver 부터 hash join가능(documents 참조) - EUCKR CHARACTER SET 설정 show collation like '%euckr%'; collation euckr_bin -> euckr euck_korean_ci -> euckr + 계속 보완할 예정 이전 1 2 다음
