MySQL data dictionary SQL 독학 강의#22편

MySQL data dictionary SQL 독학 강의#22편

 

콘텐츠 index 0. Data Dictionary 란? 1. information_schema 2. mysql

## 전편 강의 보러 가기 ##

2020/06/12 - [Database/sql 강의] - MySQL DDL문 완전정복 SQL 독학 강의#21편 -sTricky

MySQL DDL문 완전정복 SQL 독학 강의#21편 -sTricky

 

 

안녕하세요.

 

MySQL 독학 강의 22편을 포스팅합니다.

 

21편 이후로 약간의 공백이 있었지만, 다시 으라차차~! 힘을 내서 초보자 분들이 보시기 편한 포스팅을 할 수 있도록 노력하겠습니다.

 

 

0. Data Dictionary 란?

Data Dictionary란 무엇일까요? 우리말로는 데이터 사전이라고 하는데, Database에서 Data Dictionary란 지도가 될 수도 있고요, 목차가 될 수도 있구요, 요약 정보가 될 수도 있구요, 그냥 한마디로 하자면 MySQL Server내에 있는 데이터베이스 개체에 관한 정보가 모두 모아져 있는 곳입니다.

 

이 DB에 어떤 user가 생성되어 있는지, 어떤 스키마(데이터베이스), 테이블, 테이블에는 어떤 칼럼이 있고, 테이블 별로 데이터가 얼마나 있는지, 다른 오브젝트들이 어떻게 어떤 스키마에 종속되어 있는지 기타 등등 유저가 입력하는 데이터를 제외한 모든 정보가 들어 있는 것이라고 생각하면 됩니다.

 

MySQL에 있는 Data Dictionary는 크게 4가지가 있습니다. 

information_schema와 mysql, sys 마지막으로 performance_schema 가 있습니다.

 

performance_schema 는 데이터베이스내 성능 지표를 호가인 할 수 있는 스키마 입니다. 이는 MySQL 소스 코드 곳곳에 심어져 있는 instruments를 통해서 수집되고 있습니다.

 

sys의 경우 역시 MySQL 5.7 버전부터 기본 제공되며, 이전 버전에서는 추가 설치로 사용하실 수 있습니다.

 

여기는 어떻게 보면 개발자나 사용자 입장에서 위 두 스키마는 자주 사용하는 스키마는 아니기에 이번 포스팅에서는 자세한 설명은 생략하고, information_schema와 mysql에 관해서 설명을 이어가도록 하겠습니다.

 

1. information_schema

위에서도 언급했다시피 information_schema는 Data Dictionary 또는 System Catalog라고도 합니다.

여기에 있는 테이블 목록은 아래와 같습니다.

select TABLE_SCHEMA, TABLE_NAME from information_schema.TABLES
where TABLE_SCHEMA = 'information_schema';

TABLE_SCHEMA	TABLE_NAME
information_schema	ALL_PLUGINS
information_schema	APPLICABLE_ROLES
information_schema	CHARACTER_SETS
information_schema	CLIENT_STATISTICS
information_schema	COLLATIONS
information_schema	COLLATION_CHARACTER_SET_APPLICABILITY
information_schema	COLUMNS
information_schema	COLUMN_PRIVILEGES
information_schema	ENABLED_ROLES
information_schema	ENGINES
information_schema	EVENTS
information_schema	FILES
information_schema	GLOBAL_STATUS
information_schema	GLOBAL_VARIABLES
information_schema	INDEX_STATISTICS
information_schema	KEY_CACHES
information_schema	KEY_COLUMN_USAGE
information_schema	PARAMETERS
information_schema	PARTITIONS
information_schema	PLUGINS
information_schema	PROCESSLIST
information_schema	PROFILING
information_schema	REFERENTIAL_CONSTRAINTS
information_schema	ROUTINES
information_schema	SCHEMATA
information_schema	SCHEMA_PRIVILEGES
information_schema	SESSION_STATUS
information_schema	SESSION_VARIABLES
information_schema	STATISTICS
information_schema	TABLES
information_schema	TABLESPACES
information_schema	TABLE_CONSTRAINTS
information_schema	TABLE_PRIVILEGES
information_schema	TABLE_STATISTICS
information_schema	TRIGGERS
information_schema	USER_PRIVILEGES
information_schema	USER_STATISTICS
information_schema	VIEWS
information_schema	INNODB_CMP
information_schema	XTRADB_INTERNAL_HASH_TABLES
information_schema	INNODB_SYS_DATAFILES
information_schema	XTRADB_RSEG
information_schema	INNODB_SYS_TABLESTATS
information_schema	INNODB_TRX
information_schema	INNODB_FT_BEING_DELETED
information_schema	INNODB_CMP_RESET
information_schema	INNODB_CMP_PER_INDEX
information_schema	INNODB_LOCKS
information_schema	INNODB_FT_DELETED
information_schema	XTRADB_READ_VIEW
information_schema	INNODB_LOCK_WAITS
information_schema	INNODB_CMPMEM_RESET
information_schema	INNODB_SYS_INDEXES
information_schema	INNODB_SYS_TABLES
information_schema	INNODB_SYS_FIELDS
information_schema	INNODB_BUFFER_PAGE_LRU
information_schema	INNODB_CHANGED_PAGES
information_schema	INNODB_FT_CONFIG
information_schema	INNODB_FT_INDEX_TABLE
information_schema	INNODB_CMP_PER_INDEX_RESET
information_schema	INNODB_SYS_TABLESPACES
information_schema	INNODB_FT_INDEX_CACHE
information_schema	INNODB_SYS_FOREIGN_COLS
information_schema	INNODB_METRICS
information_schema	INNODB_BUFFER_POOL_STATS
information_schema	INNODB_CMPMEM
information_schema	INNODB_SYS_FOREIGN
information_schema	INNODB_SYS_COLUMNS
information_schema	INNODB_FT_DEFAULT_STOPWORD
information_schema	INNODB_BUFFER_PAGE

이 많은 SYSTEM VIEW에 관해서 모두 다 알 필요는 없습니다.

 

여기에서 자주 쓰는 몇 가지 SYSTEM VIEW에 관해서 설명을 드리겠습니다.

 

1. information_schema.SCHEMATA 

MySQL 내부에 있는 스키마(데이터베이스) 목록을 볼 수 있고, 스키마 별 캐릭터 셋을 확인할 수 있습니다.

select * from information_schema.SCHEMATA

2. information_schema.TABLES

MySQL 내부에 생성되어 있는 SYSTEM VIEW 및 테이블에 관련된 정보를 볼 수 있습니다. 

생성일자, 수정 일자, 테이블 로우수, 테이블 코멘트, 테이블 스토리지 엔진 타입 등의 정보를 한눈에 확인 가능합니다.

select * from information_schema.TABLES

3. information_schema.COLUMNS

MySQL 내부에 생성되어 있는 칼럼 정보를 확인할 수 있습니다.

칼럼명, 초기입력값, 컬럼순서, 데이터타입, 데이터 길이, 컬럼 코멘트 등 칼럼에 관련된 대부분의 정보를 여기서 확인할 수 있습니다.

select * from information_schema.COLUMNS

4. information_schema.ROUTINES

MySQL 내부에 생성되어 있는 Function과 Procedule에 관한 내용들이 저장되어 있습니다.

각 프로그램별 입출력 데이터 정보와, 프로그램 소스 등의 정보가 저장되어 있습니다.

select * from information_schema.ROUTINES

5. information_schema.KEY_COLUMN_USAGE

MySQL 내부에 생성된 테이블별 PK칼럼 또는 unique 제약조건들의 목록을 확인할 수 있습니다.

select * from information_schema.KEY_COLUMN_USAGE

6. information_schema.PROCESSLIST

현재 MySQL에 접속되어 있는 세션 정보들을 확인할 수 있습니다. 

각 세션별 상태와, 어떤 user로 어디서 접속을 하고 있는지, 어떤 SQL을 실행하고 있는지 등의 정보를 확인할 수 있습니다.

select * from information_schema.PROCESSLIST

위에서 말씀드렸다시피 더 많은 내용들이 있지만 이 정도로만 정리를 하겠습니다. 더욱 자세한 내용을 확인하고 싶으신 분들은 아래 링크에 가셔서 공식 문서를 확인하시면 상세하고 방대한 양의 정보를 확인하실 수 있으실 겁니다.

https://dev.mysql.com/doc/mysql-infoschema-excerpt/8.0/en/information-schema.html

MySQL :: MySQL Information Schema :: 1 INFORMATION_SCHEMA Tables

 

 

2. mysql

mysql 스키마에서 조회할 수 있는 정보 가운데 위에서 설명드린 information_schema를 통해 조회할 수 있는 내용들도 중복이 되어 있습니다.

 

다른 부분도 많이 있으니 따로 생성이 되어 있겠죠.

 

우선 mysql 스키마 내 테이블 목록은 아래와 같습니다.

TABLE_SCHEMA	TABLE_NAME
mysql	columns_priv
mysql	column_stats
mysql	db
mysql	event
mysql	func
mysql	general_log
mysql	gtid_slave_pos
mysql	help_category
mysql	help_keyword
mysql	help_relation
mysql	help_topic
mysql	host
mysql	index_stats
mysql	innodb_index_stats
mysql	innodb_table_stats
mysql	plugin
mysql	proc
mysql	procs_priv
mysql	proxies_priv
mysql	roles_mapping
mysql	servers
mysql	slow_log
mysql	tables_priv
mysql	table_stats
mysql	time_zone
mysql	time_zone_leap_second
mysql	time_zone_name
mysql	time_zone_transition
mysql	time_zone_transition_type
mysql	transaction_registry
mysql	user

여기서 우리가 자주 보게 되는 몇 가지를 안내해 드리겠습니다.

 

1. mysql.user

MySQL 내부에 생성된 user에 관한 정보가 들어 있습니다.

user명과 함께 host(접속지) 정보, 그리고 password가 암호화되어 저장되어 있습니다.

기본 권한을 어떻게 가지고 있는지도 확인이 가능합니다.

select * from mysql.user

2. mysql.general_log

MySQL에서 DB 로그를 Table 타입으로 저장할 때 그 로그가 쌓이는 테이블입니다.

물론 설정을 하지 않았다면 이 테이블에는 아무런 정보가 없겠죠.

SQL 실행 시간, host 및 user 정보, 상세 SQL이 저장되어 있어 필요한 데이터를 select 하여 확인할 수 있습니다.

 

* DB 로그 설정법은 아래 링크에서 확인할 수 있습니다.

https://stricky.tistory.com/289?category=1013545

slow_query_log general_log 테이블 설정 방법

select * from mysql.general_log

3. mysql.slow_log

MySQL에서 실행되는 SQL 가운데 long_query_time 파라미터에 저장되어 있는 값 보다 오래 걸리는 SQL이 저장되는 테이블입니다. 성능분석을 할 때 용이한 테이블 입니다.

select * from mysql.slow_log

 

주로 일반 사용자들이 볼만한 테이블은 이 정도입니다.

 

mysql schema와 관련하여 더 많은 정보를 보고 싶으신 분들은 아래 링크로 이동하시면 됩니다.

https://dev.mysql.com/doc/refman/8.0/en/system-schema.html

MySQL :: MySQL 8.0 Reference Manual :: 5.3 The mysql System Schema

이젠 DB를 그냥 사용하기만 하지 마시고, Data Dictionary를 통해서 더 많은 정보를 조회하셔서 편리하게 사용하실 수 있었으면 좋겠습니다.

 

---

## 다음 강의 보러 가기 ##

2020/07/08 - [Database/sql 강의] - mysql 제약조건 알아보기 SQL 독학 강의#23편

mysql 제약조건 알아보기 SQL 독학 강의#23편

 

오늘은 이렇게 MySQL의 Data Dictionary에 관해서 강의 포스팅을 해보았습니다.

 

여러분들께 조금이나마 도움이 되셨길 바랍니다.

 

감사합니다~~!!

 

open.kakao.com/o/szfhqYec

Database/남/db개발님의 오픈프로필

 

 

 

 

by.sTricky