MySQL의 information_schema에 대한 자세한 소개

information_schema 데이터베이스는 데이터베이스 메타데이터에 대한 액세스를 제공하는 MySQL 시스템과 함께 제공되는 데이터베이스입니다. information_schema는 문자 집합, 권한 관련, 데이터베이스 엔터티 객체정보, 외부 검사 제약 조건, 파티션 등 데이터베이스에서 해결해야 하는 대부분의 정보를 기록하는 MySQL 인스턴스의 백과사전과 같은 느낌입니다. , 압축 테이블, 테이블 정보, index정보, 매개변수, 최적화, 잠금 및 사물 등 information_schema를 통해 MySQL 인스턴스 전체의 실행 상태를 볼 수 있고, MySQL 인스턴스의 기본 정보를 파악하고, 심지어 데이터베이스를 최적화하고 유지 관리할 수도 있습니다, 하하.

다음은 제가 배운 몇 가지 작은 경험을 바탕으로 이 표를 전반적으로 분류한 것입니다. 부족한 점이 있으면 지적해 주시면 최대한 빨리 수정하겠습니다.

1: 문자셋 및 콜레이션 관련 시스템 테이블

CHARACTER_SETS: 데이터베이스 관련 문자셋 정보 저장(메모리 저장 엔진)

COLLATIONS: 문자셋에 해당하는 콜레이션 규칙

COLLATION_CHARACTER_SET_APPLICABILITY: 문자 집합과 연결 교정 간의 대응일 뿐입니다

문자 집합과 데이터 정렬의 차이점에 대해 이야기해 보겠습니다.

문자 집합(문자 집합)은 인간의 언어를 나타내는 문자열을 저장합니다. 상징. 예를 들어 'A', 'B' 등

조합 규칙은 동일한 문자 집합에 있는 문자 간의 비교 규칙을 나타냅니다.

각 문자 시퀀스는 고유하게 하나의 문자 집합에 해당합니다. set은 여러 문자 시퀀스에 해당할 수 있으며 그 중 하나는 기본 문자 시퀀스(기본 데이터 정렬)입니다

MySQL의 문자 시퀀스 이름은 명명 규칙을 따릅니다. 문자 시퀀스에 해당하는 문자 세트 이름으로 시작합니다. 대소문자 구분), _cs(대소문자 구분 표시) 또는 _bin(인코딩 값별 비교 표시)입니다. 예: 문자 시퀀스 "utf8_general_ci"에서 문자 "a"와 "A"는 동일합니다.

문자 집합 및 데이터 정렬과 관련된 MySQL 변수 를 살펴보세요.

character_set_server: 기본 내부 작동 문자 집합

Character_set_client: 클라이언트 소스 데이터에 사용되는 문자 집합

character_set_connection: 연결 레이어 문자 집합

character_set_results: queryresult 문자 집합

character_set_database: 현재 선택한 데이터베이스의 기본 문자 집합

character_set_system: 시스템 메타데이터(필드 이름) etc.) 문자 집합

MySQL의 문자 집합 변환 프로세스를 다시 살펴보겠습니다.

(1) MySQL 서버는 요청을 받으면 Character_set_client에서 요청 데이터를 Character_set_connection으로 변환합니다.

(2). 내부 작업 수행 전 요청 data_set_connection에서 내부 작업 문자 집합으로 변환됩니다. 결정 방법은 다음과 같습니다.

각 데이터 필드의 CHARACTER SET 설정 값을 사용합니다.

위 값이 없으면 사용합니다. 해당 데이터 테이블의 DEFAULT CHARACTER SET 설정 값(MySQL 확장, 비SQL 표준)

위 값이 없으면 해당 데이터베이스의 DEFAULT CHARACTER SET 설정 값을 사용합니다.

위 값이 있으면 존재하지 않는 경우, Character_set_server 설정 값을 사용하십시오.

(3). 내부 연산 문자 집합의 연산 결과를 Character_set_results로 변환합니다.

2: 권한과 관련된 일부 테이블:

SCHEMA_PRIVILEGES: 데이터베이스에 대한 관련 권한을 제공합니다. 이 테이블은 mysql.db에서 가져온 메모리 테이블입니다.

TABLE_PRIVILEGES: 테이블 권한과 관련된 정보를 제공합니다. 정보는 mysql.tables_priv 테이블에서 로드됩니다.

COLUMN_PRIVILEGES: 이 테이블은 테이블에서 권한을 부여한 사용자의 개체, 어떤 테이블 및 어떤 라이브러리를 명확하게 볼 수 있습니다. 어떤 권한이 부여되는지. 권한 부여 중에 부여 옵션이 추가되면 PRIVILEGE_TYPE의 값이 YES여야 함을 알 수 있습니다.

USER_PRIVILEGES: 테이블 권한과 관련된 정보를 제공합니다. 정보는 mysql.user 테이블에서 로드됩니다.

테이블을 통해 MySQL 인증 수준, SCHEMA, TABLE 및 COLUMN 수준을 명확하게 볼 수 있습니다. , 이는 사용자에 따라 부여됩니다. MySQL 인증도 매우 상세하며 열에 따라 달라질 수 있음을 알 수 있습니다. 이는 감사와 같은 특정 애플리케이션 시나리오에서 여전히 매우 유용합니다.

3: 데이터베이스 시스템의 엔터티 개체를 저장하는 일부 테이블:

COLUMNS: 테이블, 모든 스토리지 엔진의 필드 정보를 저장합니다.

INNODB_SYS_COLUMNS: INNODB의 메타데이터를 저장하며, SYS_COLUMNS 통계 테이블에 의존합니다.

ENGINES: 엔진 유형, 이 엔진 지원 여부, 설명, 지원 여부, 분산 트랜잭션 지원 여부, 롤백 지점 지원 여부

EVENTS: MySQL에 이벤트 기록, Scheduled Job

FILES과 유사: 이 테이블은 MySQL 테이블스페이스에 저장된 파일에 대한 정보, 파일 저장소 위치, 이 테이블의 데이터를 InnoDB 인메모리에서 가져옵니다. 꺼내므로 이 테이블 자체도 메모리 테이블이므로 다시 시작할 때마다 다시 꺼내집니다. 이것이 바로 아래에서 설명할 INNODB_SYS_DATAFILES 테이블입니다. 또 한 가지 주의할 점은 이 테이블에는 임시 테이블 정보가 포함되어 있으므로 SYS_DATAFILES 테이블과 동일하지 않다는 것입니다. INNODB_SYS_DATAFILES를 살펴봐야 합니다. Undo 테이블스페이스도 InnoDB로 구성하면 함께 기록된다.

PARAMETERS: 매개변수 테이블에는 저장 프로시저 및 메서드의 일부 매개변수와 저장 프로시저의 반환 값 정보가 저장됩니다. 저장 및 방법은 ROUTINES에 저장됩니다.

PLUGINS: 기본적으로 MySQL 플러그인 정보, 활성 상태 및 기타 정보. 실제로 SHOW PLUGINS 자체는 이 테이블을 사용하여 도덕적 데이터

ROUTINES를 가져옵니다. 즉, 저장 프로시저 및 메소드 함수에 대한 일부 정보이지만 이 정보에는 사용자 정의가 포함되지 않고 일부 시스템 정보만 포함됩니다.

SCHEMATA: 이 테이블은 인스턴스 아래에 있는 데이터베이스 수와 데이터베이스의 기본 문자 집합을 제공합니다.

TRIGGERS: 이 테이블은 모든 관련 정보를 포함하여 triggers의 정보를 기록합니다. 시스템 및 자체 사용자 생성 트리거.

VIEWS: Views 정보. 이는 시스템과 사용자의 기본 보기 정보이기도 합니다.

이 테이블은 일부 데이터베이스의 엔터티 개체를 저장하므로 쿼리 및 관리가 편리합니다. DBA의 경우 이러한 테이블을 사용하면 작업을 크게 촉진하고 데이터베이스 관련 정보를 더 빠르고 편리하게 확인하고 쿼리할 수 있습니다.

4 제약 조건 정보

INNODB_SYS_FOREIGN_COLS

: 이 테이블에는 INNODB의 외래 키에 대한 메타데이터 정보와 SYS_FOREIGN_COLS에 저장된 정보도 저장됩니다.

INNODB_SYS_FOREIGN

: INNODB에 저장된 외래 키에 대한 메타데이터 정보는 다음 정보와 일치합니다. SYS_FOREIGN_COLS에 저장됩니다. 정보는 일관되지만 INNODB에만 해당됩니다

KEY

_COLUMN_USAGE: 데이터베이스의 모든 제약된 열이 저장되고 제약 조건의 이름과 카테고리도 기록됩니다

왜요? 키와 제약 조건은 독립적인 것처럼 느껴지기 때문에 별도로 나열해야 합니다. 대부분의 프로덕션 환경에서는 성능이 저하되므로 외래 키를 사용하지 않지만, 다음과 같은 제약 조건을 합리적으로 사용하는 것은 여전히 ​​좋은 선택입니다. 고유한 제약 조건으로.

5 DBA는 다들 잘 알고 계시리라 생각하며, 시스템 재시작 시 이러한 테이블도 다시 로드됩니다. 즉, 메모리 테이블이다.

PARTITIONS: MySQL 파티션 테이블 관련 정보 이 테이블을 통해 파티션 관련 정보(데이터베이스의 파티션된 테이블뿐만 아니라 파티션 테이블의 파티션 및 각 데이터 정보)를 쿼리할 수 있습니다. 파티션). 파티션 관련 세부 정보는 MySQL 파티션 관리를 참조하세요.

PROCESSLIST: show processlist는 실제로 이 테이블에서 데이터를 가져오며 PROCESSLIST의 데이터가 그 기반입니다. 메모리 테이블이므로 메모리에서 쿼리하는 것과 동일하며 이러한 작업은 매우 빠릅니다.

INNODB_CMP_PER_INDEX, INNODB_CMP_PER_INDEX_RESET: 이 두 테이블은 전체 테이블 및 인덱스에 대한 정보를 포함하여 INNODB 정보 테이블 압축에 대한 관련 정보를 저장합니다. 데이터 자체도 클러스터형 인덱스로 간주될 수 있으므로 인덱스는 압축됩니다. information_schema 시리즈 11에는 압축 테이블에 대한 간략한 소개가 있습니다.

INNODB_CMPMEM, INNODB_CMPMEM_RESET: 이 두 테이블은 MySQL INNODB의 압축된 페이지에 대한 버퍼 풀 정보를 저장하지만, 한 가지 주의할 점은 이 두 테이블을 사용하여 모든 정보를 수집할 경우 성능에 영향을 미칠 수 있다는 점입니다. 심각한 영향을 미치므로 기본적으로 꺼져 있습니다. 이 기능을 활성화하려면 innodb_cmp_per_index_enabled 매개변수를 ON으로 설정해야 합니다.

INNODB_BUFFER_POOL_STATS: 이 테이블은 INNODB의 버퍼 풀에 대한 정보를 제공하며, 이는 show 엔진 innodb 상태에서 제공하는 정보와 동일합니다. 또한 show 엔진 innodb 상태에 대한 정보 소스이기도 합니다.

INNODB_BUFFER_PAGE_LRU, INNODB_BUFFER_PAGE: INNODB LRU LIST 관련 정보를 유지합니다. 자세한 내용은 편집자 노트 innodb 버퍼 풀 설명을 참조하세요.

INNODB_BUFFER_PAGE: 이 테이블은 비교적 간단하며, 버퍼에 버퍼링된 페이지 데이터를 저장합니다. . 이 테이블을 쿼리하면 성능에 심각한 영향을 미칠 수 있습니다. 서비스의 짧은 일시 중지를 허용하지 않는 한 이 명령문을 자체 프로덕션 데이터베이스에서 실행하지 마세요. 자세한 내용은 innodb 버퍼 풀

INNODB_SYS_DATAFILES 에 대한 편집자의 메모를 참조하세요. 이 테이블은 기록된 테이블의 파일 저장 위치와 테이블스페이스(INNODB)

INNODB_TEMP_TABLE_INFO의 대응입니다. 이 테이블은 INNODB의 모든 사용자가 사용하는 모든 정보를 기록하지만 In-memory에만 기록할 수 있습니다. 지속적인 정보가 없습니다.

INNODB_METRICS: INFORMATION_SCHEMA를 보완하는 INNODB의 다양한 성능 지수를 제공합니다. MySQL 시스템 통계를 수집합니다. 이러한 통계는 활성화 또는 비활성화되도록 수동으로 구성할 수 있습니다. innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all 매개변수를 제어할 수 있습니다.

INNODB_SYS_VIRTUAL: 테이블은 INNODB 테이블의 가상 열에 대한 정보를 저장합니다. 물론 이는 비교적 간단합니다. MySQL 5.7에서는 가상 생성 열과 저장 생성 열이라는 두 가지 유형이 지원됩니다. 생성된 열만 생성합니다. 데이터 사전(테이블의 메타데이터)에 저장하면 이 데이터 열이 디스크에 유지되지 않습니다. 후자는 생성된 열을 읽을 때마다 계산하는 대신 디스크에 유지합니다. 분명히 후자는 기존 데이터에서 계산할 수 있는 데이터를 저장하고 더 많은 디스크 공간이 필요하며 실제로 데이터 열을 저장하는 것에 비해 이점이 없습니다. 따라서 MySQL 5.7에서는 생성된 열의 유형이 지정되지 않으며 기본값은 다음과 같습니다. 가상칼럼입니다.

INNODB_CMP, INNODB_CMP_RESET: INNODB 정보 테이블 압축에 대한 관련 정보를 저장합니다. 자세한 내용은 권장 사항을 참조하세요.

이 테이블들이 관리 관련 테이블로 나열되는 이유는 연결, 파티션, 압축 테이블, innodb 버퍼 풀 및 기타 테이블과 비슷하다고 생각하기 때문입니다. 이 테이블을 통해 우리 데이터베이스의 관련 기능 상태를 명확하게 볼 수 있습니다. 특히, 일부 변수를 통해 MySQL의 실행 상태를 더 쉽게 확인할 수 있어 관리가 용이해졌습니다. 관련 참고 사항에는 권장 innodb 버퍼 풀 설명, MySQL 파티션 관리 및 information_schema 시리즈 11이 포함됩니다. 모두 제가 직접 작성한 메모입니다.

6: 테이블 정보 및 인덱스 정보에 대한 일부 테이블

TABLES, TABLESPACES, INNODB_SYS_TABLES, INNODB_SYS_TABLESPACES:

TABLES 이 테이블은 의심할 여지 없이 시스템 데이터베이스 및 사용자 생성 데이터베이스를 포함하여 데이터베이스에 기록된 테이블 정보입니다. . 'test1'G와 같은 테이블 상태 표시의 소스는 이 테이블입니다.

TABLESPACES는 표시된 활성 테이블 공간입니다. 이 테이블은 innodb의 테이블 공간에 대한 정보를 제공하지 않으며 우리의 프로덕션 라이브러리가 INNODB를 사용해야 하기 때문에 우리에게 별로 유용하지 않습니다.

INNODB_SYS_TABLES 이 테이블은 SYS_TABLES 데이터 사전에서 가져오는 것에 의존합니다. 이 테이블은 행 형식, 압축된 페이지 크기 비트 수준 정보(해당되는 경우)를 포함하여 테이블의 형식 및 저장 특성을 제공합니다.

는 SYS_TABLESPACES의 INNODB 정보와 실제로 일치하는 INNODB에 대한 테이블스페이스 정보를 제공합니다. .

STATISTICS: 이 테이블은 테이블에 대한 인덱스 정보와 모든 인덱스에 대한 관련 정보를 제공합니다.

INNODB_SYS_INDEXES: INNODB 테이블의 인덱스 관련 정보를 제공합니다. SYS_INDEXES 테이블에 저장되는 정보는 기본적으로 동일합니다. 단, 후자는 모든 스토리지 엔진에 대한 인덱스 정보를 제공하는 반면, 후자는 INNODB 테이블의 인덱스만 제공합니다. INNODB 테이블 정보입니다.

INNODB_SYS_TABLESTATS:

이 테이블은 MySQL의 INNODB 테이블 정보를 기록하며 MySQL 최적화 프로그램은 실제로 MySQL 데이터베이스

에 대한 적절한 인덱스 정보를 추정합니다.

이 테이블은 메모리에 기록이 기록되는 메모리 테이블이며 매번 재시작할 때마다 다시 기록되므로 마지막 재시작 이후의 데이터베이스 통계만 기록할 수 있습니다. 이 테이블을 사용하면 인덱스 사용 횟수를 쿼리하고, 자주 사용되지 않는 인덱스를 쉽게 정리하고 삭제할 수 있으며, 테이블 업데이트 및 삽입의 효율성을 높이고, 디스크 공간을 절약할 수 있어 더욱 편리하게 인덱스를 관리할 수 있습니다. INNODB_SYS_FIELDS: 이 테이블은 INNODB 테이블 인덱스 필드 정보와 필드 순위를 기록합니다.

INNODB_FT_CONFIG: 이 테이블은 전체 텍스트 인덱스 정보를 저장합니다

INNODB_FT_DEFAULT_STOP

W

ORD : 이 테이블은 불용어 정보를 저장합니다. 이는 전체 텍스트 인덱스를 일치시키는 데 사용됩니다. innodb의 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD와 동일합니다. 이 STOPWORD는 인덱스를 생성하기 전에 생성되어야 하며 필드는 varchar로 지정되어야 합니다. 중지 단어는 중지 단어라고 합니다. 전체 텍스트 검색 중에 중지 단어 목록을 읽고 검색합니다. 다른 문자 집합 및 정렬 방법에서는 중지 단어에 따라 적중이 실패하거나 데이터를 찾을 수 없습니다. 단어를 정렬하는 방법. 이 기능을 사용하여 불필요한 필드를 필터링할 수 있습니다. INNODB_FT_INDEX_TABLE: 이 테이블은 전체 텍스트 인덱스가 있는 INNODB 테이블에 대한 인덱스 사용 정보를 저장합니다. 마찬가지로 이 테이블은 innodb_ft_aux_table을 설정한 후에만 사용할 수 있습니다. 일반적으로 비어 있습니다

INNODB_FT_INDEX_CACHE

: 이 테이블입니다. 7: MySQL 최적화와 관련된 일부 테이블에 대해

OPTIMIZER_TRACE: 최적화 추적 기능에 의해 생성된 정보를 제공합니다. 또한 작은 테스트를 수행했는데, MySQL 추적 최적화 테스트

PROFILING: SHOW PROFILE은 서버 실행 문의 작업 상태를 자세히 볼 수 있습니다. 또한 명령문을 실행하는 데 걸리는 시간을 이해하는 데도 도움이 될 수 있습니다. 일부 제한 사항으로는 구현되지 않는 기능, 다른 연결의 명령문을 보고 구문 분석할 수 없음, 구문 분석 중에 발생하는 비용 등이 있습니다.

SHOW PROFILES는 최근 서버로 전송된 여러 개의 명령문을 표시합니다. 명령문 수는 세션 변수 profiling_history_size에 따라 정의됩니다. 기본값은 15이고 최대값은 100입니다. 0으로 설정하는 것은 분석 기능을 끄는 것과 같습니다. 자세한 내용은 MySQL 프로필

INNODB_FT_BEING_

DELETE

D, INNODB_FT_DELETED:

INNODB_FT_BEING_DELETED을 참조하세요. 이 테이블은 INNODB_FT_DELETED의 스냅샷이며 OPTIMIZE TABLE 중에만 사용됩니다. 자세한 내용은 OPTIMIZE TABLE 설명을 참조하세요. 8: MySQL 트랜잭션 및 잠금과 관련된 일부 테이블

INNODB_LOCKS: 현재 획득한 잠금이지만 획득하지 않은 잠금은 제외하고 INNODB에만 해당됩니다.

INNODB_LOCK_WAITS: 하나 이상의 차단된 행에 대한 기록과 잠금 요청 및 차단된 변경 요청의 잠금 정보 등을 포함한 시스템 잠금 대기 관련 정보입니다.

INNODB_TRX: 실행 중인 모든 트랜잭션(INNODB)과 관련된 정보가 포함되어 있으며 트랜잭션이 차단되었는지 또는 잠금이 필요한지 여부가 포함됩니다.

이러한 테이블을 통해 미완성된 작업과 차단된 프로세스를 쉽게 쿼리할 수 있습니다. 자세한 내용은 information_schema 시리즈 8(사물, 잠금)을 참조하세요.

위 내용은 MySQL의 information_schema에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!