우리집앞마당

GROUP BY로 그룹핑한 로우들의 합계를 구해주는 기능이있다. ROLLUP인데 사용법은 GROUP BY 절 뒤에 WITH ROLLUP을 사용해주면 된다. SELECT col1, col2 FROM table GROUP BY col1 WITH ROLLUP; 이때 ROLLUP을 사용했음에도 합계가 구해지지않고 마지막 로우의 값이 출력되는 경우가 있다. ROLLUP으로 값을 구하기위해선 SELECT절에 집계함수가 사용되어야하는데 그렇지 않을 경우 나타나는 현상(?) 이다. 오늘의 교훈 : ROLLUP을 사용할땐 집계함수와 같이 쓰자.

쿼리 수행시간을 측정하고자 여러번 측정하면서 비교시 쿼리가 캐싱되어 정확한 측정이 힘든 경우가 존재한다. 이런 경우에 쿼리 캐싱을 방지하는 힌트를 주면 된다. SELECT SQL_NO_CACHE * FROM table_name; SELECT /*! SQL_NO_CACHE */ * FROM table_name;

1번 포스팅에 이어서 작성한다. 5. possible_keys옵티마이저는 어떤 인덱스를 사용할지 후보들을 정해놓고 그중에 하나를 사용하는데 possible_keys는 후보에서 떨어진 인덱스 키들을 보여준다. 강제로 인덱스를 태우고자할때는 유용할 수도 있을것 같으나 일단 수립된 계획을 확인할때는 상대적으로 비중이 떨어지는 항목이다. 6. keypossible_keys가 탈락한 키들의 집합이라면 key는 뽑힌 인덱스키를 보여준다. index_merge 처럼 다중 인덱스를 활용한 경우가 아니라면 값은 항상 1개를 표현한다. 7. key_len사용된 키의 컬럼크기를 나타낸다. character set에 따라 같은 컬럼이라도 다르게 표현될 수 있으며, null 유무에 따라서도 크기가 달라질 수 있다. 8. ref..

쿼리 앞에 EXPLAIN 키워드를 붙이면 실행 계획을 확인 할 수 있다. EXPLAIN SELECT * FROM test; 실행 계획은 일반 조회 쿼리 처럼 테이블 형태로 출력되는데 간단하게 그 내용을 정리해본다. 1. idSELECT 쿼리에 부여되는 아이디. 한 SELECT 쿼리 내에 복수개의 SELECT 문이 서브쿼리(Sub Query) 형태로 들어갈 수 있으므로 그런 경우에 id 값이 증가되어 여러행의 실행계획이 나타나게 된다. SELECT 문이 1개만 존재하는 JOIN(조인) 형태의 쿼리는 복수의 id가 부여되지 않는다. 2. select_type-SIMPLE명칭 그대로 심플한 쿼리이다. 서브쿼리같은 복잡함이 없다. -PRIMARY서브쿼리가 존재할때 가장 바깥 쿼리를 뜻한다. 복수 행의 실행계획에..

요즘 통계쪽 쿼리를 짜게되면서 DB를 공부하고있다. 보통 쿼리를 짜게되면 실행계획을 확인 한 후 쿼리를 튜닝하여 효율성 높은 쿼리를 짜게되는데 이번에 포스팅할 내용은 그 실행계획을 빠르게 수집하는 법이다. 쿼리에 2개 이상 테이블의 join이 들어갈 경우 어떤 테이블을 기준으로 조인을 거는지에 따라 수행시간이 엄청나게 차이가 날 수 있는데 이때 최적화된 조인을 위한 알고리즘은 2가지가 존재한다. 1. Exhaustive Search(철저한 탐색) 모든 경우의 수에 해당하는 비용을 계산한 후 최적의 조합을 찾는다. 확실하게 최적의 조합을 찾을 순 있겠으나 모든 경우의 수를 구하는 만큼 효율적이라고 보기 힘들다. 2. Greedy Search(탐욕스러운 탐색) optimizer_search_depth 변수에..

어려운 쿼리들은 아닌데... 헷갈려서 쓸때마다 검색을 하게됐던 쿼리들을 모으고자 한다. MySQL 버전확인 SELECT version(); 테이블이 존재하면 삭제, 존재하지않으면 경고만 발생(테이블이 없다고해서 에러를 발생시키지 않음) DROP TABLE IF EXISTS table_name; 기본키가 중복되는 데이터가 존재하면 뒷부분 update 실행, 존재하지않을경우 앞부분 insert 실행 INSERT INTO table_name(col1, col2) VALUES('a', 'b') ON DUPLICATE KEY UPDATE col1 = 'a'; 테이블명칭 조회 SHOW TABLES LIKE '%table_name%'; 테이블 컬럼 명 변경 ALTER TABLE table_name CHANGE bef..

SHOW GLOBAL VARIABLES LIKE 'lock_wait_timeout'; DB가 락이 걸렸을때 타임아웃을 확인하는 쿼리. 기본설정은 31536000초다. 말도 안되는 시간이니 당연히 필요에 따라 변경을 해주는게 좋을것이다. 변경법은 SET SESSION lock_wait_timeout=900;

특정 기본키가 없으면 insert를, 있으면 update를 쳐야하는 경우가 종종 존재한다. 이때마다 애플리케이션단에서 단순하게는 if문을 사용하여 처리했었는데 mysql에 이미 이런 구문이 있는걸 알게되었다. insert문에 on duplicate key update 옵션을 사용하거나 replace 문을 사용하면 된다. INSERT INTO test(col1, col2) VALUES('a', 'b') ON DUPLICATE KEY UPDATE col1 = 'a'; REPLACE test SET col1 = 'a', col2 = 'b'; 둘다 처음 보는 문법이라하더라도 기존에 SQL을 어느정도 사용했던 사람이라면 직관적으로 알 수 있을거라고 본다(혹시 모르겠으면 댓글을...). 비슷한 용도기때문에 뭘 써야..

에러내용 : is not allowed to connect to this MYSQL server로컬에서는 접속이 가능한데 다른 컴퓨터에서 접근할때 나는 에러. 권한이 없어서 발생하는 것이다. 해결법 : grant all privileges on *.* to root@'권한을 부여할 IP' identified by 'password' with grant option; 에러내용 : the last packet send successfully to the server was 0 milliseconds ago.해결법(우분투 기준) : /etc/mysql 경로에 있는 my.cnf 실행 vi my.cnf 쭉쭉 내리다보면 bind-address 라는 항목이 있다. 해당 항목이 127.0.0.1로 되어있다면 해당 항목..