Meluu_의 코딩 공부 일지

공간 데이터 인덱스 추가

행정동 데이터의 geom을 이용해 ST_Contains로 특정 POINT가 포함되어있는지 확인하였다. 그러다 문뜩 이게 인덱스를 타는지 궁금하여 실행계획을 봤더니..explain select idFROM region_dongWHERE ST_Contains( geom, ST_SRID(POINT(126.9780, 37.5665), 4326)); type ALL 즉, 풀스캔을 때려버렸다.왜인지 궁금해서 찾아보았다. 1. SRID 불일치이는 SELECT ST_SRID(geom)로 확인해본 결과 4326으로 잘 저장되어있다. 2. 공간 인덱스 미생성-> 이거다.. 공간데이터라서 mysql 단에서 자동으로 인덱스를 생성해주는줄 알았다. 아래 쿼리를 날려서 공간데이터 인덱스를 생성해주었다. 이로써 R-Tre..

기존 데이터베이스(스키마)의 이름을 잘 못 지어서 변경하는 방법에 대해서 정리하려 한다. mysqldump를 이용해서 모든 데이터를 백업한 뒤새로운 스키마(데이터베이스)를 생성하고 백업한 데이터를 덤프한다. -- 데이터 백업 (덤프)mysqldump -u [사용자명] -p [기존_데이터베이스명] > backup.sql-- 변경할 이름의 새 DB 생성CREATE DATABASE [새 DB 명]-- OR mysql -u [사용자명] -p -e "CREATE DATABASE [새 DB 명]"-- 백업한 덤프 데이터 복원mysql -u root -p [새 DB 명] 데이터 백업과 복원에 관한 공부도 할 수 있어서 좋은 경험을 했다.

외래키 꼭 필요한가?프로젝트를 하면서 '외래키 설정이 꼭 필요한가?' 라는 의문을 갖게 되었다.물론 내 프로젝트처럼 소규모는 데이터 정합성과 무결성을 위해서 사용해도 성능상 큰 문제는 없다. 그럼에도 불편한 점이 존재한다. 초반 프로젝트를 막 설계하고 적용한 후 변경사항이 없을 수가 없고, 이로 인해 테이블을 변경하게 되는데부모 테이블을 삭제하려니 FK 조건때문에 막히고, 테스트 시 현재 테스트에서 관심사가 아닌 연관관계가 설정된 테이블의 데이터도 함께 넣어줘야하므로 생각보다 번거롭다고 느꼈다. 특정 부모를 참조하는 자식데이터들의 대량 insert 발생 (ex 댓글) 데이터무결성 (Integrity) : “데이터 자체가 논리적으로 올바른가?” — 잘못된 값이 들어오지 않도록 보호하는 제약..

최근 당근의 지역기반 데이터 조회에 대한 궁금증이 생겨서 어떻게 공간데이터를 처리하는 지 알아보기위해먼저 공간 데이터에 대한 학습이 필요하여 정리하게 되었다. 공간 데이터 타입mysql 서버는 OpenGIS에서 제시하는 표준을 준수OpenGIS에서 제공하는 WKT(Well Known Text), WKB(Well Known Binary)를 이용해공간 데이터를 관리할 수 있게 지원 OpenGIS는 지리 공간 정보 기술의 개방형 표준이다. Open Geospatial Consortium(OGC)에서 개발한 지리 공간 데이터 및 서비스에 대한 일련의 기술 사양을 의미한다. WKT : 공간데이터를 글자로 적음, EX) POINT(1,1) 데이터 타입 GEOMETRY는 슈퍼 타입으로 셋 중 아무거나 다 받을 수..

내용이 많아 따로 글을 나눠서 작성한다. Extra 칼럼쿼리 실행 계획에서 성능에 관련된 중요한 내용이 자주 표시됨 const row not foundconst 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1개도 없다면 표시Deleting all rows 테이블의 모든 레코드를 삭제하는 기능을 제공하는 경우 (Where 없이 모두 삭제)8.0부터 삭제되고, where 없이 삭제를 권장하지 않음, Truncate Table 사용distinct중복제거, 중복된 값들은 읽지 않고 무시함 FirstMach(table)IN(subquery) 형태의 세미 조인 을 EXISTS(subquery) 형태로 튜닝한 것과 비슷하게 실행되며, 서브 쿼리를 조인으로 처리하며, 일치하는 레코드 1건만 찾으면 더..

대부분 DBMS는 많은 데이터를 안전하게 저장 및 관리 사용자가 원하는 데이터 빠르게 조회가 주 목적 이를 달성하기 위해 옵티마이저가 사용자의 쿼리를 최적으로 처리될 수 있게 하는 쿼리의 실행 계획을 수립할 수 있어야함 EXPLAIN 명령으로 옵티마이저가 수립한 실행 계획 확인 MYSQL 5.7버전까지 테이블과 인덱스에 대한 개괄적인 정보를 가지고 실행계획을 수립함 8.0부터 히스토그램 도입 히스토그램 : 인덱스되지 않은 칼럼들, 인덱스된 칼럼들의 데이터 분포도 통계 정보는 단순히 인덱스된 칼럼의 유니크한 값의 개수 정도만 가져 옵티마이저가 최적의 실행계획을 수립하기에는 많이 부족 실제 인덱스의 일부 페이지를 랜덤으로 가져와 참조하는 방식 사용 8.0부터 칼럼 데이터 분포도를 참조 가능한 히스토그램 정보..

흔히 DB에서 동시성 제어를 위해 사용하는 락에 대해서 다시 공부할 필요가 있어정리하고 이해한다. 락은 말 그대로 잠금이다. 동시성 제어를 위해 사용되며 3가지 종류가 있다.비관적 락(Pessimistic Lock)낙관적 락(Optimistic Lock)분산 락(Distrubuted Lock) 비관적 락DB나 멀티 스레드 환경에서 여러 사용자가 동시에 동일 레코드에 접근할 때 충돌을 방지하기 위해미리 락을 거는 방식으로, 사실상 의미그대로 잠금을 거는 것이다. 동시 접근이 많을 경우, 충돌이 많이 일어날 경우 사용한다. 비관적 락 종류Shared Lock(공유 락, S-Lock)데이터 읽기 시 사용 하는 락여러 트랜잭션이 동시 공유락 가능 (동시 읽기 허용)배타 락이 걸리면 동시 접근 불가Exclusi..

3가지 정렬 방식인덱스 사용조인의 드라이빙 테이블만 정렬임시 테이블 사용 후 정렬 3가지가 실제로 어떻게 적용되는지 궁금해서 직접 구현해봤다. CREATE TABLE tb_test1 ( col1 int NOT NULL, col2 int DEFAULT NULL, PRIMARY KEY (col1), INDEX ix_col1_col2 (col1,col2), INDEX ix_col2 (col2)) ENGINE=InnoDB CREATE TABLE tb_test2 ( col1 int DEFAULT NULL, col2 int DEFAULT NULL, INDEX ix_col1_col2 (col1,col2)) ENGINE=InnoDB303p 의 퀴리문을보고 테이블을 만들었다. 해당 쿼리로 테스트 한다. ..

쿼리 실행 절차1. 사용자로부터 요청된 SQL 문장을 잘 게쪼개서 MYSQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다. 2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을 지 선택 3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴 1단계가 SQL 파서 모듈이 처리하는 SQL 파싱SQL 문법적 체크 옵티마이저는 비용 기반 최적화와 규칙 기반 최적화가 있으며현재는 비용 기반 최적화를 사용 기본 데이터 처리풀 테이블 스캔과 풀인덱스 스캔 풀 스캔은 특정 테이블의 연속된 데이터 페이지 읽으면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작리드 어헤드 : 어떤 영역의 데이터가..

1. 디스크의 읽기 방식디스크 같은 기계식 장치의 성능은 상당히 제한적로 발전데이터베이스의 성능 튜닝 == 디스크 I/O를 어떻게 줄일지 관건 HDD vs SSDCPU, 메모리 같은 주요 장치는 대부분 전자식 장치HDD는 기계식 장치이기에 데이터베이스 서버에서 항상 디스크 장치가 병목 발생 전자식 저장 매체인 SSD가 많이 출시되고 있으며 HDD와 같은 인터페이스 지원, 내장 디스크나 DAS, SAN에 그대로 사용 가능한마디로 DB서버로 사용하기 최적이며 HDD보다 훨씬 빠르다. (랜덤 I/O, 순차I/O) 둘다 1.2 랜덤 I/O 와 순차 I/O랜덤 I/O : HDD의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동 시킨 다음 데이터를 읽는 것순차 I/O : 디스크의..