• 원본 : https://employment.en-japan.com/engineerhub/entry/2017/09/05/110000

엔지니어로 근무하고 반드시 직면하는 고민. 그것은 "어떤 관계형 데이터베이스 (이하 RDB)를 선택하는 것이 최선인가?"입니다.
RDB마다 장점과 단점이 다릅니다. 따라서 자사 서비스에 일치하지 RDB를 선택해 버리면 그것이 병목이 개발 · 운용에 문제가 생기는 경우가 적지 않습니다.

그 중에서도 자주 비교 검토되는 것이 PostgreSQL과 MySQL. 모두 오픈 소스 RDB의 사실상의 표준이며, 높은 성능과 다양한 기능을 가지고 있습니다.

양자는 구체적으로 어떤 장단점이있는 것입니까? 그것을 철저 해부하기 위해, PostgreSQL 전문가 인 사와다: 마사히코 씨와 MySQL 전문가 인 다나카: 날개 씨의 대담을 실시. 각 기능마다 특징을 비교했습니다.

RDB에 대해 일본 최고 수준의 지식을 가진 2 명의 의견. 꼭 PostgreSQL과 MySQL을 선정 할 때 참고하세요!

사와다: 마사히코 (사와다: · 마사히코) @sawada\_masahiko (사진 왼쪽)  
2012 년 NTT 데이터에 입사. 이후 PostgreSQL에 관한 업무에 종사 주로 PostgreSQL의 본체 개발, 기술 지원 및 국내외 불문하고 다양한 컨퍼런스에서 강연을하고있다. 2016 년부터 NTT OSS 센터에 근무. PostgreSQL 커뮤니티는 Contributor으로 복제, VACUUM 분산 트랜잭션 기능의 개발 및 버그 수정을 통해 코어 개발에 기여.  
다나카: 날개 (타나카 날개) @ yoku0825 (사진 오른쪽)  
Linux 서버 운영자가 MySQL 기관을 거쳐 GMO 미디어에서 MySQL 전문 DBA로서 경력을 쌓는다. 주요 저서로 「MySQL 즉각적인 쿼리 튜닝」(임프레스 2016 년). MySQL 분야의 Oracle ACE에서 "MySQL 5.7 Community Contributor Award '를 수상한 핑크 두부.  

[비교 포인트 ①\] DDL 작업의 비 차단  
 [비교 포인트 ②\] DML 문 성능  
 [비교 포인트 ③\] 테이블 조인 (JOIN) 알고리즘  
 [비교 포인트 ④\] 트랜잭션 처리의 격리 수준  
 [비교 포인트 ⑤\] 저장 프로 시저, 트리거  
[비교 포인트 ⑥\] 복제 논리 형과 물리 형  
 [비교 포인트 ⑦\] 중 하나의 DB에만있는 편리한 기능  
 [비교 포인트 ⑧\] 데이터 형의 느슨 함, 유형 변환, 문자열 비교  
\[결론\] 어느 쪽을 어떤 서비스에 사용하나요?  

관련 기사

DDL: Data Define Language 의 약자로, 스키마/도메인/테이블/뷰/인덱스를 정의/변경/제거할 때 사용하는 언어이다.  
테이블을 생성하고, 테이블 내용을 변경하고, 테이블을 없애버리는 것.  
 흔히CREATE, ALTER, DROP을 떠올리면 된다.  

DML: Data Manipulation Language 의 약자로, Query(질의)를 통해서 저장된 데이터를 실질적으로 관리하는 데 사용한다.  
테이블 안의 데이터 하나하나를 추가하고 삭제하고 수정하는 것.  
 흔히INSERT, DELETE, UPDATE를 떠올리면 된다.  

DCL: Data Control Language 의 약자로, 보안/무결성/회복/병행 제어 등을 정의하는데 사용한다. 데이터 관리 목적.  
 흔히COMMIT, ROLLBACK, GRANT, REVOKE를 떠올리면 된다.  
 -COMMIT: Transaction의 변경 내용을 최종 반영한다고 재판 결정하는 것.  
 -ROLLBACK: Transaction의 변경 내용을 모두 취소하고 이전 상태로 되돌리는 것.  
(Transaction : Database에서 하나의 Logical Function을 수행하는 단위. 즉, 작업 하나의 단위. 하나의 Transaction은 COMMIT되거나 ROLLBACK되어야 한다. 하나의 Transaction은 정상적으로 종료되면 COMMIT을 비정상적으로 종료되면 ROLLBACK 수행. )

[비교 포인트 ①] DDL 작업의 비 차단

- 오늘은 잘 부탁합니다. 우선 DDL (데이터 정의 언어)에 대해 비교하고 싶습니다. MySQL에서 무엇입니까?

다나카: MySQL은 많은 DDL 작업을 논 블로킹 (트랜잭션 중에도 테이블에 블록이 들지 않는)에서 실행할 수 있다는 장점이 있습니다. 이 기능은 MySQL 버전 5.6에서 구현되었습니다. 또한 대상의 컬럼 만 검색 같은 ALTER TABLE (컬럼 이름을 변경하는 컬럼을 추가하는 등)의 경우 테이블을 처음부터 다시 빌드하지 않기 때문에 처리 속도가 빠르고, 전체 서버의 부하를 줄일 수 있다는 특징 도 있습니다.

- PostgreSQL는 ALTER TABLE 등의 DDL 작업은 논블로킹은없는 것일까요?

사와다: 그렇네요. 어떤 DDL 문을 발행되었는지에 따라 달라집니다 만, 예를 들어 열을 추가하는 등 테이블을 재 작성 작업은 테이블에 블록이 발생하게 참조 할 수 없게되어 버립니다.

-하지만 프로덕션 DB에 대해 ALTER TABLE을 걸고 싶은 경우도있을 거라고 생각합니다. 그 때 어떤 방법을 취해야하는 이유는 무엇입니까?

사와다: pg_repack는 유지 보수 용 외부 도구가 사용되는 경우가 많습니다. 그것을 사용하면 REINDEX 나 일부 ALTER TABLE 조작을 최소화 잠금에서 실행할 수 있습니다.pg_repack - PostgreSQL 데이터베이스의 테이블을 최소화 잠금으로 재구성합니다

- PostgreSQL을 보수 · 운용하고있는 분은 그 도구의 존재를 꼭 기억 싶네요.

[비교 포인트 ②] DML 문 성능

- 다음은 각종 DML (데이터 조작 언어)을 비교하고 싶다고 생각합니다. 먼저 SELECT 문입니다.

다나카: 간단한 SELECT이라면, MySQL도 PostgreSQL도 크게 다르지 않다고 생각합니다. 좋은 승부 게 아닐까요.

사와다: 그렇네요. SELECT는 크게 다르지 않습니다.

다나카: 그러나 대량 데이터의 정렬이 필요한 SELECT (ORDER BY를 한 후 테이블의 모든 데이터를 검색하는 등)은 MySQL이라고 늦어 져 버립니다. 왜냐하면 PostgreSQL과 비교하면 MySQL은 정렬 알고리즘이 그만큼 우수하기 때문입니다. MySQL은 대량 데이터를 정렬하는 것을 기본으로 유스 케이스로 상정하고 있지 않습니다.

- 어떤 조건의 SELECT이라면, MySQL은 강점을 발휘합니까?

다나카: MySQL은 신규 10 건이나 100 개의 데이터 (TOP n 레코드)를 취득하는, 예를 들어 Twitter와 같은 유스 케이스에 특화되어 있습니다. 그런 장면은 PostgreSQL보다 빠릅니다.

- 다른 DML 문은 어때? 예를 들어 UPDATE는.

다나카: UPDATE, MySQL 쪽이 성능이 우수하네요.

사와다: 저도 그렇게 생각합니다.

- 그 이유는 무엇입니까?

사와다: PostgreSQL을 추기 아키텍처라고해서 UPDATE 할 때 INSERT에 가까운 처리가 실행되고 있습니다. 무슨 말인가하면, 이전의 행 삭제 플래그의 종류를 세운 뒤, 변경된 데이터를 가진 새 행을 추가하고 있구요.

다나카: 한편 MySQL은, UPDATE 대상이되는 행의 값을 직접 덮어 쓸 수 있습니다. 문자 그대로 "업데이트"하고 있구요.

- 아키텍처이면 확실히 MySQL 쪽이 UPDATE 조작은 빨리 될 것 같네요. 그러면, DELETE에 관해서는 어떻습니까?

다나카: 과거 MySQL에 DELETE가 느리다는 단점이있었습니다. 이것은 데이터 삭제 후 보조 인덱스 (클러스터 인덱스를 제외한 모든 인덱스)를 동기화에 붙여 다시하고, 그 처리에 시간이 걸려 있었기 때문입니다.
하지만, 버전 5.5에서 상당히 해소되었습니다. 보조 인덱스의 비동기 체인지 버퍼 (보조 인덱스 항목의 변경을 버퍼링 해 두었다가 서버가 유휴 상태에있을 때 등에 변경 내용을 병합하는 방법)이 효과가있게 되었기 때문에 이전만큼 "DELETE가 느린 "그 말은하지 않습니다.

[비교 포인트 ③] 테이블 조인 (JOIN) 알고리즘

- 다음은 테이블 결합을 비교합니다. 자주 사용되는 테이블 조인 알고리즘은 "네 스티드 루프 조인 (Nested Loop Join)」 「해시 조인 (Hash Join)」 「정렬 병합 조인 (Sort Merge Join)」의 3 종류가 있습니다 만.

다나카: MySQL은 기본적으로 네 스티드 루프 조인 밖에 지원하지 않습니다. 왜냐하면, MySQL은 "복잡한 알고리즘은 가급적 지원하지 않는다 '는 설계 사상에 근거하고 있기 때문입니다.

- 왜 MySQL은 그러한 설계 사상이나요?

다나카: Web 어플리케이션에 사용되기 전에, MySQL은 원래 편입 계 시스템에서 사용 된 것에 기인하고 있습니다.
임베디드 매우 짧 디스크와 메모리에서 DB를 가동시킬 필요가 있고, 복잡한 알고리즘을 최대한 깎아 떨어 뜨리는 정책으로 설계되어 왔어요.

- 납득 이군요. 한 PostgreSQL은 어떻습니까?

사와다: PostgreSQL는 3 종류 모두 지원합니다.

- 각각의 결합 패턴은 어떤 유스 케이스에 향하고있는 것일까 요?

사와다: 결합 할 데이터 량이 많을 때는 해시 조인과 정렬 병합 조인을 사용하는 것이 좋다고 생각합니다. 데이터가 이미 정렬되어있는 경우에는 정렬 병합 조인이 더 좋고, 그렇지 않으면 해시 조인을 추천합니다.
네 스티드 루프 결합이 최선의 선택이 될 것은 결합 된 테이블 중 하나의 데이터 양이 적어 다른 많은 같은 때. 또는 내부 테이블 측이 인덱스 스캔을 사용할 경우군요. 이것은 MySQL에서도 마찬가지입니다.

f : id : blog-media : 20170901185246j : plain

[비교 포인트 ④] 트랜잭션 처리의 격리 수준
https://qiita.com/PruneMazui/items/4135fcf7621869726b4b

- 다음은 트랜잭션 처리에 대해 묻고 있습니다. PostgreSQL과 MySQL은 각각의 기본 트랜잭션 격리 수준 (트랜잭션 처리를 여러 번 실행 된 경우에 얼마나 많은 데이터 일관성 및 정확성에서 실행하는 방법을 정의하는 것)가 다르다는 이야기 수 있습니다.

다나카: 그렇습니다. MySQL은 기본이 REPEATABLE-READ가 있습니다. 이 방식이라면 읽기 대상 데이터가 도중에 다른 트랜잭션에서 변경되어 버릴 염려가 없습니다.
그러나 팬텀 리드 (동시에 실행되는 다른 트랜잭션이 추가 데이터가 도중에 보여 버리는 현상)이 발생할 수 있습니다. MySQL은 팬텀 리드를 피하기 위해, 넥스트 키 록하는 구조를 채용하고 있습니다.

- 그것은 무엇입니까?

다나카: 트랜잭션이 실행되고있는 동안 레코드가 증가하지 않도록 기본 키의 증가 대상 값까지 잠금을 건다는 것입니다. 이 방법을 통해 데이터의 안정성은 유지 될 것입니다 만, 동시에 이로 인해 의도하지 않은 잠금이 걸려, 고장의 원인이되어 버릴 수도 있습니다.
예를 들어, SELECT FOR UPDATE 등으로 WHERE 절에 "<(부등호)"를 사용하고 "ID가 10 이상"레코드를 검색했다고합니다. 그러면 10 개 이상의 키가 모두 잠겨 버립니다.
이렇게되어 버리면 새로운 기본 키를 생성하지 못하고, INSERT 할 수 없게되어 버립니다. 이 사양은 꽤 빠져 그런데이므로주의 해 두는 것이 좋습니다.
잠금 경합을 줄이기 위해 트랜잭션 격리 수준을 낮은 READ-COMMITTED (항상 커밋 된 최신 데이터를 읽는 형식)로 변경하여 운용하는 경우도 있습니다.

- PostgreSQL에서는 기본 트랜잭션 격리 수준은 무엇입니까?

사와다: READ-COMMITTED입니다. 이 방식의 경우, 팬텀 리드 및 비 반복 가능한 읽기를 (동일한 트랜잭션에서도 동일한 데이터를 읽어 들일 때마다 값이 변하는 현상)이 발생할 수 있기 때문에 운용에서는 그 점을 조심해야합니다.
또한 PostgreSQL에서는 트랜잭션 격리 수준을 REPEATABLE-READ로 변경하더라도 넥스트 키 잠금을 차지하지 않고 다른 방법으로 팬텀 리드를 막고 있습니다. 따라서 잠금 충돌을 방지 쉽다는 점은 MySQL보다 더 있을지도 모릅니다.

[비교 포인트 ⑤] 저장 프로 시저, 트리거
https://qiita.com/setsuna82001/items/e742338eb93e3a48ba46

- 저장 프로 시저에 대해서는 어떻습니까?

사와다: PostgreSQL은 SQL 이외에도 Python 등을 이용한 외부 프로 시저를 사용할 수있는 것은 장점이라고 생각합니다.

다나카: MySQL은 SQL만을군요. 또한 MySQL 단체에서는 저장 프로 시저의 단계 수행 할 수 없다는 단점이 있습니다.

- 트리거에 대해서는?

다나카: MySQL 5.6 이전 버전에서는 테이블 당 최대 6 개까지만 다중 트리거가 건 수 없다는 단점이있었습니다. 또한 BEFORE INSERT TRIGGER가 테이블 당 1 개 밖에 설치되고 않았기 때문에 상당히 제한이 있었어요.
현재는 트리거 개수의 제한은 없으며되어 있습니다. 그러나 MySQL 트리거는 FOR EACH ROW 밖에 없어서 FOR EACH STATEMENT이 없기 때문에, 그 점은 고려되어야합니다.

[비교 포인트 ⑥] 복제 논리 형과 물리 형

- 다음은 복제에 대해.

다나카: MySQL의 경우 복제는 논리 형 (SQL 문 자체를 복사) 또는 물리 형 (변경 후 행 이미지를 복사) 중 하나를 선택할 수있게되어 있습니다. 종래는 논리 형이 기본 설정이었던 것입니다 만, MySQL 5.7 이상에서 물리 형이 기본이되었습니다.

사와다: PostgreSQL는 물리 형만군요. 다만, 현재 베타 버전으로 출시 된 버전 10에서 부울도 사용할 수있게되어 있습니다.

- MySQL에서 "물리적 형태가 기본이됐다"는 얘기가있었습니다 만, 논리 형은 어떤 단점을 안고 있었다 있을까요?

다나카: 논리 형은 좋든 나쁘 든 유연한 곳이 있고, 예를 들어 마스터 테이블과 종속 테이블의 스키마가 다소 달랐다하더라도 SQL조차 통해서 버리면 오류가 안되나요.
그 사양을 사용하여 캐주얼 운용 할 수 있다는 장점이 있습니다 만, 마스터와 슬레이브에 차이가 있어도 인식하지 못할 수있는 데이터 안정성 측면에서 보면 문제가 있습니다. 따라서 "안전 측면을 기본으로한다"는 사상에서 물리 형이 기본이 된 거죠.

[비교 포인트 ⑦] 중 하나의 DB에만있는 편리한 기능

- 어느 하나에 만 유용한 기능은 있을까요?

다나카: 지금까지는 부분적으로 잘라낸 결과 집합에 집계 함수를 적용 할 수있는 윈도우 함수와 SELECT 문을 실행하기 전에 하위 쿼리를 만들 수 WITH 절 등 집계에 적합한 기능이 PostgreSQL에만있었습니다. 따라서 분석 시스템의 처리는 PostgreSQL 쪽이 강했다입니다.
단, 창 함수 WITH 절에도 MySQL도 버전 8.0에서 도입 될 예정입니다.

- 그렇게되면, 분석 계 처리는 미래에 차이가 없어 질지도 모르 네요. 다른 한쪽에 있고 다른 한쪽에는없는 기능이 있습니까?

사와다: 병렬 쿼리입니까. 이것은 처리 속도를 빠르게하기 위해 여러 CPU를 활용하여 쿼리를 실행하는 것입니다.
그리고, 많은 엔지니어가 PostgreSQL를 선택하는 이유로 꼽는 것이 PostGIS는 타사의 OSS 도구 네요. 지도와 기하 데이터 정보를 처리하는 것입니다. MySQL의 것보다 기능이 풍부하고 PostgreSQL가 가진 장점 중 하나라고 생각합니다.

PostGIS - Spatial and Geographic Objects for PostgreSQL

다나카: 뭔가 PostgreSQL로 부럽다 기능 있었던 걸까. 그래, pg_basebackup이 굉장히 편리하네요. 온라인하고 원격 데이터베이스 클러스터 기반 백업이 잡히므로.
MySQL의 경우 온라인에서 물리적 백업 수단이 XtraBackup 또는 Enterprise Backup 밖에 없기 때문에, 온라인하고 원격으로 기반 백업을 수행 할 수 없어요.

[비교 포인트 ⑧] 데이터 형의 느슨 함, 유형 변환, 문자열 비교

- 마지막 데이터 형식의 느슨 함 (암시 적으로 실시되는 형태 변환과 문자열 비교의 엄격함 등)에 대해 들려주세요.

다나카: 버전 5.6 이전의 MySQL에서는 데이터 형식의 느슨 함이 문제가되는 경우가 많았습니다. 하지만 5.7에서는 전체적으로 카타 방법으로 수정되어 이전보다 데이터 형으로 인해 버그가 발생하는 경우가 줄었습니다.
하지만 몇 가지주의해야 할 사례가 존재하고, 예를 들어 이것.

(int) 1 = (string) '1'= (string) '1Q84'
MySQL의 경우,이 3 개는 "같은 값이다"라고 인식되는 것입니다.

- 어! ? 그 이유는 무엇입니까?

다나카: 왜인가하면, 숫자 "1"과 문자열 "1"을 비교할 때 암시 적으로 문자열에서 숫자의 형태 변환이됩니다. 그래서 첫 번째와 두 번째는 같은 값으로 인식되는 것입니다.
그리고 숫자 "1"과 문자열 "1Q84"를 비교할 때에도 "1Q84"를 숫자로 암시 적 형식 변환합니다. 그러면 무슨 일이 일어나는가하면, 데이터를 전방에서 읽기 넣고해서 숫자로 인식 할 수있는 부분까지 형식 변환하는 것입니다.

- 그렇군요. 즉 「1Q84」의 「1」까지가 메모리에 적재된다고.

다나카: 것. 그래서 "같은 값"이라고 인식되어 버리는 것이군요.
그 밖에도 암시 적 형식 변환되는 경우가 있고, 예를 들어 날짜 형식 "2017-07-01"에서 숫자 "1"을 당기면 '20170700'라는 정수가 반환됩니다. 버그의 원인이되기 쉽다 사양이므로 MySQL을 사용하는 경우에는 조심하는 것이 좋다고 생각합니다.

- 형식 변환에 대해 하나의 PostgreSQL는 어떤 사양입니까?

사와다: PostgreSQL는 다양한 형식 변환에 관해서는 꽤 카타쪽에 걸고 있습니다. 방금 다나카: 씨가 말씀하신 것 같은 문자열에서 숫자 유형으로 암시 적 변환은 일어나지 않기 때문에 SQL을 쓰는 사람이 명시 적으로 캐스팅해야 안 되네요. 혹은 묵시적 ​​형 변환을 사용자가 정의하는 것으로 대응하는 방법도 있습니다.

- 문자열 비교는 어떻습니까?

다나카: MySQL은 기본적으로 문자열 비교에서 대소 문자를 구분하지 않습니다. 또한 버전 8.0부터는 기본 설정이라고 탁음과 반 탁음를 구별하지 않습니다. '은'과 '파'와 '토바'동일시되고 있으며, '병원'과 '아름다움 요인'도 동일시됩니다.

- 그 사양 것은 뭔가 이유가 있나요?

다나카: Unicode 사양에 의존하고있는 것입니다. Unicode 데이터 정렬 (정렬)의 엄격함 설정이 레벨 1부터 레벨 4까지 단계적으로 나누어 져 있습니다.
'은'과 '파'와 '토바'를 구별하기 위해서는 레벨 2 이상의 비교 '병원'과 '아름다움 요인'을 구별하기 위해서는 레벨 3 이상의 비교가 필요한 만, MySQL 기본 설정에서는 1 수준 밖에 사용하지 않는 비교되어 있습니다.
레벨을 올리면 문자열의 구분은 정확하게됩니다 만, 처리는 무거워지고 있습니다. MySQL은 "간단한 처리 속도를 향상시키는 '라는 디자인 철학을 기반으로 만들어져 있기 때문에 처리 속도를 버리면 서까지 문자열 비교의 엄격함을 가지고 같은 것은하지 않을 것입니다.

- 그렇군요. 그런 사양에서 DB의 설계 사상을 엿볼 수있는 것은 매우 재미 있네요.

[결론] 어느 쪽을 어떤 서비스에 사용하나요?
- 마지막으로 총괄로 PostgreSQL과 MySQL이 각각 어떤 서비스를 향하고 있는지를 말해 줄 수 있습니까?

사와다: PostgreSQL는 "다기능 인 것 '이 가장 큰 장점이기 때문에 그 특징이 사는 같은 시스템에는 적합하다고 생각합니다. 예를 들어, Oracle Database로부터의 마이그레이션 및 SIer 계 기업에서 사용되는 경우가 많다는 인상을 개인적으로 가지고 있습니다.
다음은 분석 기반 시스템에서 자주 사용됩니다. 그러나 이것도 전술 한 바와 같이 MySQL의 분석 기능이 서서히 충실 해오고 있기 때문에 미래에 차이는 적게 오는 것입니다.

- MySQL 분은 어떻습니까?

다나카: 기본적으로 간단한 Web 서비스에 적합하다고 생각합니다.
일정 수의 결과 세트를 가지고 와서 그 데이터를 표시하는 같은 느낌의. 예를 들어 Twitter와 같은 타임 라인의 시작 부분을 표시하고 아래로 스크롤하면 다음의 데이터를 읽어들이 같은 서비스는 MySQL에 특히 잘 어울린다 생각합니다.
하지만 버전이 올라갈 때마다 PostgreSQL도 MySQL도 고성능 해지고 있기 때문에 결국은 사용하고 싶은 분을 절대로 좋지 않을까요.

• 원글 : https://brownbears.tistory.com/57
• 원글 : https://ra2kstar.tistory.com/96
• 원글 : https://k39335.tistory.com/26

Index란?

테이블에 저장된 데이터를 빠르게 조회하기 위한 데이터베이스 객체
인덱스는 말 그대로 책의 맨 처음 또는 맨 마지막에 있는 색인이라고 할 수 있다. 이 비유를 그대로 가져와서 인덱스를 살펴본다면 데이터는 책의 내용이고 데이터가 저장된 레코드의 주소는 인덱스 목록에 있는 페이지 번호가 될 것이다. DBMS 도 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져 오려면 시간이 오래 걸린다. 그래서 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어 두는 것이다.
DBMS 의 인덱스는 항상 정렬된 상태를 유지하기 때문에 원하는 값을 탐색하는데는 빠르지만새로운 값을 추가하거나 삭제, 수정하는 경우에는 쿼리문 실행 속도가 느려진다. 결론적으로 DBMS 에서 인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져서 오히려 역효과만 불러올 수 있
다.

• 대개 B-Tree, B+Tree구조를 가짐
• Index는 논리적/물리적으로 테이블과 독립적임

Index 자료구조

그렇다면 DBMS 는 인덱스를 어떻게 관리하고 있는가

• B+-Tree 인덱스 알고리즘
  일반적으로 사용되는 인덱스 알고리즘은 B+-Tree 알고리즘이다. B+-Tree 인덱스는 칼럼의 값을 변형하지 않고(사실 값의 앞부분만 잘라서 관리한다.), 원래의 값을 이용해 인덱싱하는 알고리즘이다.
• Hash 인덱스 알고리즘
  칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로, 특정 문자로 시작하는 값으로 검색을 하는 등 전방 일치와 같이 값의 일부만으로 검색하고자 할 때는 해시 인덱스를 사용할 수 없다. 주로 메모리 기반의 데이터베이스에서 많이 사용한다.
• 왜 index 를 생성하는데 b-tree 를 사용하는가?
  데이터에 접근하는 시간복잡도가 O(1)인 hash table 이 더 효율적일 것 같은데? SELECT 질의의 조건에는 부등호(<>) 연산도 포함이 된다. hash table 을 사용하게 된다면 등호(=) 연산이 아닌 부등호 연산의 경우에 문제가 발생한다. 동등 연산(=)에 특화된 hashtable 은 데이터베이스의 자료구조로 적합하지 않다.

Index를 왜 사용할까?

SQL서버에서 테이블을 만들고 데이터를 추가, 수정, 삭제 할 때 데이터의 레코드는 내부적으로 아무런 순서없이 저장된다. 이때 데이터 저장영역을 Heap이라고 한다. Heap에서는 인덱스가 없는 테이블의 데이터를찾을 때 무조건 전체 데이터 페이지의 처음 레코드부터 끝 페이지의 마지막 레코드까지 다 읽어서 검색조건과 비교하게 된다. 이런 식의 데이터 검색방법을 테이블 스캔(table scan) 또는 풀 스캔(full scan)이라고 한다.이럴 경우 양이 많은 테이블에서 일부분의 데이터만 불러 올 때 풀 스캔을 하면 처리 성능이 떨어진다. 즉 인덱스는 데이터를 select 할 때 빨리 찾기 위해 사용된다.

WHERE 구문과 일치하는 열을 빨리 찾기 위해서.

열을 고려 대상에서 빨리 없애 버리기 위해서. 

조인 (join)을 실행할 때 다른 테이블에서 열을 추출하기 위해서.

특정하게 인덱스된 컬럼을 위한 MIN() 또는 MAX() 값을 찾기 위해서.

사용할 수 있는 키의 최 좌측 접두사 (leftmost prefix)를 가지고 정렬 및 그룹화를 하기 위해서.

데이터 열을 참조하지 않는 상태로 값을 추출하기 위해서 쿼리를 최적화 하는 경우에.

인덱스를 사용해야 하는 경우 
데이터 양이 많고 검색이 변경보다 빈번한 경우
인덱스를 걸고자 하는 필드의 값이 다양한 값을 가지는 경우 

인덱스의 사용

• 기존의 테이블에 인덱스를 추가하기

  ALTER TABLE  테이블명 ADD INDEX(필드명(크기));

mysql> ALTER TABLE temp ADD INDEX(keyword(20));

Query OK, 554604 rows affected (1.31 sec)

Records: 554604  Duplicates: 0  Warnings: 0

• 테이블 생성시 인덱스 추가하기

  CREATE TABLE 테이블 명 ( 필드명 데이터타입(데이터크기), 
  INDEX(필드명(크기)) ENGINE MyISAM; 

mysql> CREATE TABLE test (
    -> keyword varchar(20),
    -> INDEX(keyword(20)))
    -> ENGINE MyISAM;
Query OK, 0 rows affected (0.11 sec)

• FULLTEXT 인덱스 만들기
  일반적인 인덱스와는 달리 MySQL의 FULLTEXT는 매우 빠르게 모든 텍스트 열을 검색한다. 검색 엔진과 유사한 방법으로 자연 언어를 이용해 검색할 수 있는 특별한 인덱스로 모든 데이터 문자열의 단어를 저장하기 때문이다.

• 참고사항

  • FULLTEXT 인덱스는 MySQL의 기본 저장 엔진 타입인 MyISAM 테이블에만 사용된다.

  • 만약 테이블을 MyISAM 으로 변경해야 된다면

    ALTER TABLE 테이블명 ENGINE = MyISAM; 
    FULLTEXT 인데스는 CHAR 와 VARCHAR, TEXT 열로만 생성 가능하다.

    ALTER TABLE 테이블명 ADD FULLTEXT(필드명)
    
    mysql> ALTER TABLE temp ADD FULLTEXT(keyword);
    Query OK, 554604 rows affected (1.49 sec)
    Records: 554604  Duplicates: 0  Warnings: 0

인덱스의 효과

인덱스를 걸게 되면, 테이블 생성시에 인덱스의 정보도 만들게되므로 생성속도가 느려진다.

비교를 위해서 두개의 테이블을 생성하였다. 하나는 인덱스가 걸려있는 테이블 (test_index) 와 걸려 있지 않은 테이블 (test)이다. 그림을 보면 생성 속도에서 차이가 꽤 나는 것을볼 수 있다.

이런데도 인덱스를 거는 이유는 바로 조건문 등에 대한 SELECT 속도 등에서 차이가 나기 때문이다.
위 테이블의 데이터는 약 55만개의 데이터가 저장되어있다. 그 중에서 '가'로 시작되는 데이터들을 뽑아 오자

SELECT * FROM test WHERE keyword LIKE '가%' ; 
5056 rows in set (0.08 sec) 

SELECT * FROM test_index WHERE keyword LIKE '가%' ; 
5056 rows in set (0.01 sec) 

Index 생성 시, 고려할 점

인덱스를 생성 시에는 where 절과 join, order by 등과 관련된 칼럼중 사용 빈도가 높고 키 값의 선별도가 좋은 칼럼에 사용해야 한다. 반대로 사용 빈도가 낮고 칼럼의 선별도가 나쁜, 예를 들어 한 칼럼의 값이 true/false, 성별(M/F) 등에는 인덱스를 사용하지 않는 것이 좋다. 또 테이블이 작거나 자주 갱신 될 때도 사용하지 않는 것이 좋다. 선택한 키의 검색 일치율이 10%미만일 경우는 Index를 사용하는 것이 좋으나, 그 이상일 경우는 풀 스캔이 더 나을 수 있다.

SELECT 쿼리의 성능을 월등히 향상시키는 INDEX 항상 좋은 것일까? 쿼리문의 성능을 향상시킨다는데, 모든 컬럼에 INDEX 를 생성해두면 빨라지지 않을까?
결론부터 말하자면 그렇지 않다 .
우선, 첫번째 이유는 INDEX 를 생성하게 되면 INSERT, DELETE, UPDATE 쿼리문을 실행할 때 별도의 과정이 추가적으로 발생한다. INSERT 의 경우 INDEX 에 대한 데이터도 추가해야 하므로 그만큼 성능에 손실이 따른다. DELETE 의 경우 INDEX 에 존재하는 값은 삭제하지 않고 사용 안한다는 표시로 남게 된다. 즉 row 의 수는 그대로인 것이다. 이 작업이 반복되면 어떻게될까?
실제 데이터는 10 만건인데 데이터가 100 만건 있는 결과를 낳을 수도 있는 것이다. 이렇게되면 인덱스는 더 이상 제 역할을 못하게 되는 것이다. UPDATE 의 경우는 INSERT 의 경우,DELETE 의 경우의 문제점을 동시에 수반한다. 이전 데이터가 삭제되고 그 자리에 새 데이터가 들어오는 개념이기 때문이다. 즉 변경 전 데이터는 삭제되지 않고 insert 로 인한 split 도 발생하게 된다.
하지만 더 중요한 것은 컬럼을 이루고 있는 데이터의 형식에 따라서 인덱스의 성능이 악영향을 미칠 수 있다는 것이다. 즉, 데이터의 형식에 따라 인덱스를 만들면 효율적이고 만들면 비효율적은 데이터의 형식이 존재한다는 것이다. 어떤 경우에 그럴까?이름 , 나이 , 성별 세 가지의 필드를 갖고 있는 테이블을 생각해보자. 이름은 온갖 경우의 수가 존재할 것이며 나이는 INT 타입을 갖을 것이고, 성별은 남, 녀 두 가지 경우에 대해서만 데이터가 존재할 것임을 쉽게 예측할 수 있다. 이 경우 어떤 컬럼에 대해서 인덱스를 생성하는것이 효율적일까? 결론부터 말하자면 이름에 대해서만 인덱스를 생성하면 효율적이다.왜 성별이나 나이는 인덱스를 생성하면 비효율적일까? 10000 레코드에 해당하는 테이블에 대해서 2000 단위로 성별에 인덱스를 생성했다고 가정하자. 값의 range 가 적은 성별은 인덱스를 읽고 다시 한 번 디스크 I/O 가 발생하기 때문에 그 만큼 비효율적인 것이다.

• 예) 테이블 내 100개 데이터 중 pk=1을 검색했을 때, 1개의 데이터가 나올 경우 1/100 = 0.01 즉 1%의일치율을 보인다.

책에 비유를 할 때, 클러스터 인덱스는 찾고자 하는 페이지를 바로 찾는 것이고 논 클러스터 인덱스는 목차를먼저 확인한 다음, 목차에서 찾고자 하는 페이지를 찾아가는 것입니다. 또한 테이블 스캔은 처음부터 한 장씩넘기면서 찾는 방식입니다.

select * from test where name like'이%'
select * from test where name like'%이%' -- 처음 %가 붙으면 table scan이 되어 속도가 느리게 됩니다.

팁

아래의 내용을 주의, 명시해서 index를 생성하도록 합니다.

1. where절에서 자주 사용하는 컬럼
2. like '%~'는 조심 (table scan이여서 성능 감소) %는 뒤에만 사용하도록 해야함
3. between A and B (클러스터 인덱스 유리) - 범위 쿼리문에서는 클러스터 인덱스가 유리하지만 클러스터 인덱스는 테이블 당 1개만 가질 수 있다는 단점 존재
4. order by에 항상 또는 자주 사용되는 컬럼
5. join으로 자주 사용되는 컬럼
6. Foreign key (1:1 매핑)이 많을 때 -> 클러스터, 논클러스터 인덱스 둘 다 상관 없음 (상황에 따라 클러스터인덱스 사용)
7. Foreign key (1:N 매핑)이 많을 때 -> 클러스터 인덱스 사용
8. 100만건의 데이터 중 10건의 데이터 조회 -> 찾는 건이 적은 컬럼에 인덱스를 걸어주는 것이 상책중복이 많은 컬럼 (예를 들어, 성별)에는 인덱스를 거는 것이 아님조회되는 건 수가 많으면 인덱스를 걸지 않고 table scan이 더 나은편
9. not 연산자는 긍정문으로 변경
10. insert, delete 등 데이터의 변경이 많은 컬럼은 인덱스를 걸지 않은 편이 좋음

인덱스를 만드는데 시간과 저장공간이 소비되고 만들고 난 후에도 추가적인 공간이 필요. 데이터를 변경 (insert, update, delete)를 하면 (특히 insert) 인덱스를 다시 조정해야하기 때문에 자원이 많이 소모됨

Index 생성

인덱스에는 크게 clustered와 nonclustered 인덱스로 나눌 수 있다. clustered 인덱스는 물리적 정렬로 DB에데이터를 입력 시 이것을 기준으로 입력이 된다. 따라서 한 테이블에 오직 하나만 존재 할 수 있으며 table을열었을 때 order by를 사용하지 않아도 데이터가 clustered 인덱스에 따라 정렬이 되어 있는 것을 확인 할 수있다. 물리적으로 정렬이 되어 있는 만큼 가장 빠른 처리를 한다. nonclustered 인덱스는 clustered 인덱스와는 달리 중복된 값을 가지면 한 테이블에 여러 개를 생성 할 수 있다. 이 외에 unique에 대해 알아보겠다. unique는 말 그대로 중복을 허용하지 않는 값을 보호 할 때 사용한다. 예를 들어 회원 관리 프로그램에서 아이디가 중복되는 것을 막고자 한다면 이 옵션을 사용하면 된다. 우리가 자주 사용하는 primary key의 경우에는 clustered와 unique 특성을 갖게 하는 제약키이다. Index를 생성할 때 clustered가 되어있을 때, index scan이 유리하다.

PostgreSQLのデータ型とその挙動のまとめ（数値型）

SQLPostgreSQLDBpostgres

Postgresにテーブルを作成する際に、Postgresのデータ型を見てみると、数値型だけで１０個もある。。どうやって使い分けたら良いのかわからなかったので、調べてみた。

この表は、PostgreSQL公式ページに記載されている内容。

数値型の大まかな区分け

Postgresの数値型には、①整数型、②小数点型、③自動採番型の３つがある。

1. 整数型
   • smallint
   • integer
   • bigint
2. 浮動小数点データ型
   • decimal
   • numeric
   • real
   • double precision
3. 自動採番型
   • smallserial
   • serial
   • bigserial

整数型

整数型の特徴は、整数しか格納できないこと。小数を含んだ値を入れようとするとエラーになる。この３つの型の違いは、格納できる数値の最大桁数。

型名最小桁

最大桁数が多くなれば、それだけハードディスクの容量を消費することになる。公式ページによると、整数型の中ではintegerが一番バランスの取れた型となっているため、整数を扱う場合、特に理由のない場合はinteger型を利用する事との記述があった。

具体的にはHDDのコストが高く、容量を出来る限り節約したい場合のみsmallintを利用し、integerの桁数で足りない値を格納する可能性がある場合はbigintを利用すると言った具合。

浮動小数点データ型

浮動小数点データ型の特徴は、小数を格納できること。型によって桁数に制限があるものとないものがある。桁数に制限がある型を利用しているフィールドにそれ以上の桁数を持つ値が入れられた場合、自動的に計算し、値を切り詰めて格納する。
例えば、real型のフィールドに12.34567という値が入った場合、格納後の値は12.3457となる。（最後の桁を四捨五入）

型名最小桁

numericとdecimalの違い

Postgresでは、これらの値の違いはない。pgadminでは、decimal型のフィールドが選択できないようになっている（リストに表示されない）。　sqlではdecimal型を指定することはできるが、作成後に型を確認するとnumericに変換される。

自動採番型

この型は、値を入力しなくても自動的にシーケンステーブルから取得した値が登録される型となる。
serial型を指定すると、テーブル名シーケンスの値名seqという名前のシーケンスオブジェクトが作成され、関連付けられる。種類によって、最大値が異なる。

型名最小桁

シーケンスが最大値に達した場合の挙動について

最大値に達した場合の挙動は、DB作成時に選択ができるようになっている。シーケンスオブジェクトの定義をプロパティから開くと、限界リセットというチェックボックスがある。ここがONになっていると限界値に達したときに一旦リセットされる。選択した型よりも限界値が大きくなっていたりすると、値がリセットされずにエラーが出続けてしまうので注意。

※キー項目などで限界値リセットすると、当然古い値との重複が避けられない。古いものを順に削除しているログなどの情報以外は、この選択は取れない可能性が高い。

まとめ

数値を格納する型の数だけを見てもこれだけの数の型があると、どれを使うか迷ってしまいますが、HDDの価格や記録できる領域が劇的に大きくなった昨今では、real型やsmallint型などの精度を犠牲にしてでも利用領域を小さくするような型を利用するシーンは少なくなってきている様に思います。整数を扱うフィールドならinteger型を、小数を扱うフィールドならnumeric型を選択するようにしておけば、間違いがないように思います。

https://m.blog.naver.com/PostView.nhn?blogId=alice_k106&logNo=220847310053&proxyReferer=https%3A%2F%2Fwww.google.com%2F

본 포스트는 MySQL, MongoDB 등 다른 DB를 사용해 본 사람들을 대상으로 작성되었다. 왜냐면 필자가 써본 DB라고 해봤자 MySQL, MongoDB, SQLite(이건 SQL이라고 하기도 좀...) NoSQL은 Hive, HBase 등등.. 이기 때문이다. 이정도면 기본적으로 사용하는 것에는 문제가 없겠지 하는 정도로 postgreSQL을 정리해 보려고 한다. 필자도 익숙하지 않은 DB라서 삽질을 했으니 여러분은 안그러길 기원하며...

1. PostgreSQL

PostgreSQL은 한국어로 '포스트그레스큐엘' 이라고 읽는다. 이 이름도 유래가 있는 듯 하다. Postgre와 SQL을 나눠서 생각해보면 SQL을 사용할 수 있는 관계형 데이터베이스(RDBMS) 이라는 것을 암시하고 있는 듯 하다. Postgre 라는 것도 Post + Ingres의 합성어같다. Ingres DB의 후속작이라는 느낌이 있다.

즉, PostgreSQL는 MySQL, MariaDB와 비슷한 관계형 데이터베이스이다. 그러나 사용법은 꽤 상이하므로 따로 익혀두는 것이 좋다.

2. PostgreSQL 설치 방법

Ubuntu 14.04 에서 아래를 입력해 설치한다. 이 글을 작성하는 시점에서 설치되는 버전은 9.3.14 이다.

MySQL Workbench 처럼 PG Admin 이라고 하는 PostgreSQL 전용 Web Workbench가 있는 듯 한데, 버그가 많은 모양이다. 굳이 다루지 않기로 했다.

3. CLI 사용하기

설치가 완료됬으면 postgreSQL의 CLI를 사용해 볼 차례다.

psql이라는 명령어로 postgreSQL의 CLI를 사용할 수 있다. 그러나 psql 명령어를 사용할 때, 데이터베이스 내의 사용자를 명시해야 한다. 그냥 root 상태에서 psql을 입력하면 아래와 같은 메시지를 뿜는다.

postgreSQL은 root 사용자를 보안상의 이유로 좋아하지 않는 듯 하다. postgres 사용자로 전환해야만 psql 명령어를 사용할 수 있다.

postgreSQL의 CLI로 접속했다. MySQL, MongoDB, HBase, Hive에서 했던 것처럼 똑같이 DB를 제어하면 된다.

4. postgreSQL의 구조

postgreSQL은 우리가 알던 DB와 조금 다른 구조가 있다. 중간에 스키마라고 하는 계층이 존재한다.

MySQL과 비교해보면 이해가 쉽다. 최상위 계층에 Database 라고 하는 레이어가 있다. 이는 우리가 흔히 아는 데이터베이스이다. create database .... 명령어로 생성하던 것이다. 이 데이터베이스는물리적으로 데이터를 구분해 놓은 것이다.

스키마는 데이터베이스 아래에 위치한 계층이다. 데이터베이스를 생성하면 기본적으로public 스키마가 존재한다. 스키마 레벨은 데이터베이스 내에서논리적으로 구분된 계층이다. 그리고 이 아래에 테이블들이 생성된다.

정리하면MySQL에서 쓰던 데이터베이스의 개념은 PostgreSQL의 스키마와 유사하다.1그러나 조금 다른 점은, PostgreSQL의 데이터베이스는 데이터의 물리적 집합을 정의하기 때문에 데이터베이스 내의 스키마들은 조인(JOIN) 이 가능하다는 점이다. 예를 들어 위 그림에서 public 스키마의 mytable은 myscheme의 mytable3과 조인이 가능하다(고 한다).

그럼 이제 스키마가 왜 있냐.. 라는 의문이 들 법 하다. 스키마를 사용하면 1. 여러 사용자가 서로 충돌 없이 하나의 데이터베이스를 사용하는 것이 가능하며 2. 데이터베이스를 여러 개의 논리적인 그룹으로 만들어 더욱 관리하기 쉽게 하고 3. Third-Party 애플리케이션이 서로 충돌할 일 없게 다른 스키마를 사용하도록 한다고 한다.2 또는 다른 사람 소유의 데이터베이스에 테이블을 생성할 수 있다 라는 이유라고도 하는데..3 확실하지는 않다.

이와 별도로, 데이터가 저장될 공간을 뜻하는 tablespace 라는 것이 있지만 아직은 이 단계까지 자세히 보지 않기로 했다. 각 데이터베이스는 tablespace라는 단위에 저장될 수 있는데, tablespace는 실제로 파일시스템에서 저장되는 위치를 의미한다고 한다.

5. postgreSQL 사용하기

(1) 데이터베이스 목록 확인하기

현재 데이터베이스의 목록을 확인하는 명령어는 \l (역슬래시와 소문자 L) 이다. 존재하는 데이터베이스를 확인해보자.

3개의 데이터베이스가 존재한다. postgres, template0, template1 이다. 우리는 지금 postgres 라는 데이터베이스에 접속해 있는데, 이는 쉘에서 확인할 수 있는postgres=# 에서 알 수 있다.

(2) 사용하는 데이터베이스 변경하기

사용하는 데이터베이스를 다른 데이터베이스로 변경해보자. 아래의 명령어를 입력한다. 이번에는 \c 이다.

정상적으로 데이터베이스가 변경되었으며, 쉘의 template1=# 에서 이를 확인할 수 있다.

(3) 데이터베이스 생성하기

새로운 데이터베이스를 생성하고 이 데이터베이스로 변경한다. 데이터베이스 생성은 MySQL과 동일하다.create database (DB명)이다.

(4) 유저 생성하기 권한주기

CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

(5) 스키마 목록 확인, 생성하기

\dn 을 입력해 스키마 목록을 확인할 수 있다. 데이터베이스를 생성하면 기본적으로 스키마는 public 하나밖에 없다.

create SCHEMA (스키마 명) 을 입력해 새로운 스키마를 생성한다. 스키마 명, 디비 명, 테이블 명은 모두 대소문자를 구별하지 않는 듯 하다. Owner는 스키마의 소유자를 뜻하는데,create SHCEMA (스키마 명) authorization (사용자 명)으로 설정할 수 있다.

(6) 테이블 목록 확인, 생성하기

테이블을 생성하는 명령어는 MySQL과 동일하다.create table (테이블 명) (test varchar(20) ... )이런 형식이다. 조금 다른 점은, 테이블을 생성할 때, 테이블이 속하게 될 스키마를 지정할 수 있다는 것이다. 스키마를 따로 지정하지 않으면 public 스키마에 테이블이 생성된다.

아래는 create tablealiceschema.alicetable(id varchar(20) primary key, pw varchar(20)); 으로 테이블을 생성했다. aliceschema 라는 스키마에 테이블을 생성한 경우이다.

• 예제코드(http://www.postgresqltutorial.com/postgresql-insert/)

  CREATE TABLE link (
      ID serial PRIMARY KEY,
     url VARCHAR (255) NOT NULL,
     name VARCHAR (255) NOT NULL,
     description VARCHAR (255),
     rel VARCHAR (50)
  );

생성된 테이블들은\dt명령어로 확인할 수 있다.

?!? 테이블이 없다고 나온다. \dt 명령어는 public 스키마에 속한 테이블만 보여주는 것 같다. 정확히는 search_path 에 설정된 스키마를 사용한다고 한다.4 스키마에 속한 테이블을 보려면\dt aliceschema.*를 입력한다.

테이블에 대한 자세한 정보를 보려면\d를 사용한다. \d는 public 스키마에 있는 테이블만 보여줄 것이므로, 특정 스키마에 존재하는 테이블을 보려면\d aliceschema.alicetable과 같은 형식으로 입력해야 한다.

(7) 테이블에 내용 넣기 (CRUD)

• 예제코드(http://www.postgresqltutorial.com/postgresql-insert/)

  INSERT INTO link (url, name)
  VALUES
  ('http://www.google.com','Google'),
  ('http://www.yahoo.com','Yahoo'),
  ('http://www.bing.com','Bing');

CRUD는 모두 기존의 SQL과 동일하므로 insert 하나만 설명한다. 딱히 특별할 것이 없기 때문이다.

insert into (테이블 명) values(....)를 쓰는데, 한 가지 다른 점이 있다면, 스키마를 지정하지 않으면 search_path의 스키마를 지정한다는 점이다. 따라서 특정 스키마의 테이블을 쓰고 싶다면 insert into(스키마 명).(테이블 명)... 처럼 쓴다. 아래는 aliceschema의 alicetable에 데이터를 넣는 예시이댜.

ユーザーの削除

DROP USER user;

userで指定したユーザーを削除します。

実際には次のように入力します。

drop user 'user'@'localhost';

なお複数のユーザーをまとめて変更する場合は次の書式を使用して下さい。

DROP USER user1, user2, user3, ...;

サンプル

では実際にユーザーを変更してみます。現在存在しているユーザーの一覧は次の通りです。

'tonakai'@'server.example.com'を削除します。

mysql> drop user 'tonakai'@'server.example.com';

再度ユーザー一覧を確認してみます。

ユーザーが削除されていることが確認できました。

ユーザーの作成

MySQLコマンドラインツールなどを利用してMySQLサーバに接続し、様々な操作を行うにはユーザーアカウントが必要です。MySQLをインストールした直後には「root」ユーザーしか作成されていません。ここでは新しいユーザーを作成する方法を確認します。

ユーザーを作成するにはCREATE USER文を使います。基本書式は次の通りです。

CREATE USER user;
CREATE USER user IDENTIFIED BY [PASSWORD] 'password';

1つ目の構文は指定したユーザーをパスワード無しで作成します。パスワードは後からでも設定できますが、パスワード無しのアカウントはセキュリティ的に問題がありますので2つ目の構文を使ってユーザーの作成時にパスワードを設定するようにして下さい。

2つ目の構文は指定したユーザーを作成しパスワードを設定します。ユーザーの指定方法は後で説明します。パスワードは文字列なのでシングルクオーテーションで囲って指定します。

複数のユーザーをまとめて追加する場合は、次の書式を使用して下さい。

CREATE USER user1 IDENTIFIED BY [PASSWORD] 'password1', 
  user2 IDENTIFIED BY [PASSWORD] 'password2',
  user3 IDENTIFIED BY [PASSWORD] 'password3', ...;

ユーザーの書式

ユーザーは次の書式で指定します。

user_name@host_name

ユーザーはユーザー名とMySQLへ接続するホスト名の組み合わせで指定します。登録されたユーザー名を持つユーザーが存在しても、そのユーザー名で接続できるのは同時に登録されているホストからしか行えません。また同じユーザー名であっても使用しているホスト毎に異なる権限を設定することもできます。

ユーザー名とホスト名は特別な文字(例えば@など)を含まない限りは引用符で囲う必要はありませんが、特別な文字を含む場合やホスト名にワイルドカードを使用する場合はシングルクオーテーションで囲って指定して下さい。(引用符で囲む場合はユーザー名とホスト名は別々に囲って下さい)。

'user_name'@'host_name'

ホスト名はホスト名の他にIPアドレスやlocalhostを指定することができます。

'username'@'host.example.com'
'username'@'192.168.128.1'
'username'@'localhost'

では実際にユーザーを作成してみます。

mysql> create user
    -> 'kuma'@'localhost' identified by 'bear',
    -> 'saru'@'192.168.128.1' identified by 'monkey',
    -> 'shika'@'host.example.com' identified by 'deer';

3つのユーザーを作成しました。

それではユーザーの一覧を取得してみます。ユーザーの情報は「mysql」データベースの中の「user」テーブルに格納されています。このテーブルにはユーザー毎に1つのデータが作成されており、ユーザー名やホスト名だけではなくパスワードや権限などの情報が格納されています。

今回は「User」カラムと「ホスト」カラムの値を取得します。

mysql> select User,Host from mysql.user;

もともと存在していた「root」ユーザーに加えて追加した3つのユーザーが存在していることが確認できます。

ホスト名のワイルドカード指定

ホスト名にはワイルドカードである'_'と'%'が使用できます。'_'は任意の一文字を表し'%'は任意の長さの文字列に一致します。

例えば同じドメインに所属する任意のホストからアクセス可能なユーザーの指定は次のように記述できます。

'username'@'%.example.com'

同じCクラス(192.168.128.0/24)の全てのホストにからアクセス可能なユーザーの指定は次のように記述できます。

'username'@'192.168.128.%'

またホスト名を省略した場合は次のように記述したものとして扱われます。

'username'@'%'

では実際にユーザーを作成してみます。

mysql> create user
    -> 'tora'@'192.168.128.20_' identified by 'tiger',
    -> 'kirin'@'%.example.com' identified by 'giraffe',
    -> 'kaba' identified by 'hippos';

改めてユーザー情報を取得してみます。

mysql> select User,Host from mysql.user;

ワイルドカードはそのまま格納され、ホスト名を省略した場合は'ユーザー名@%'の形式で格納されていることが確認できます。

作成したユーザーでMySQLサーバへ接続

それではMySQLコマンドラインツールを使い、新しく作成したユーザーでMySQLサーバへ接続してみます。

ローカルホストからアクセスが許可されている「kuma」ユーザーを使って接続します。

問題なく接続できました。

では今度はホスト「192.168.128.1」からアクセスが許可されている「saru」ユーザーを使って接続してみます。

ローカルホストから接続しようとしましたのでユーザーは「'saru'@'localhost'」と認識されます。ユーザーとして追加されているのは「'saru'@'192.168.128.1'」ですのでアクセスが拒否されました。