Início / dba / Perguntas / 3335
Accepted
ivotron
Asked: 2011-06-16 15:17:08 +0800 CST

Como o INFORMATION_SCHEMA é implementado no MySQL?

  • 772

O INFORMATION_SCHEMA é, em teoria, um conjunto de visões especificadas no padrão SQL que permitem ao usuário inspecionar os metadados do sistema. Como isso é implementado no MySQL?

Quando me conecto a uma nova instalação, vejo dois bancos de dados: mysqle information_schema. Depois de usar SHOW CREATE TABLEinstruções no information_schemabanco de dados, parece que não foi implementado como um conjunto de visualizações, mas sim com tabelas base. Essa suposição está correta? Ou existem outras tabelas do sistema que estão ocultas do usuário?

mysql information-schema

1 respostas

  1. Best Answer

    O banco de dados INFORMATION_SCHEMA é composto de tabelas temporárias usando o mecanismo de armazenamento MEMORY.

    Exemplo: Aqui está a tabela INFORMAÇÕES_SCHEMA.TABLES no MySQL 5.5.12 (versão Windows)

    mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

    Não há pasta física para essas tabelas, nem mesmo arquivos .frm. Você não pode fazer mysqldump. Você não pode abandoná-lo. Você não pode adicionar tabelas a ele. Você não pode descartar tabelas dele. Então, onde estão as mesas ???

    Todas as tabelas no banco de dados INFORMATION_SCHEMA são armazenadas diretamente na memória como tabelas do mecanismo de armazenamento MEMORY. Eles são totalmente internos ao MySQL, então os mecanismos .frm são manipulados no mysqld. Em minha resposta, mostrei primeiro o layout da tabela de INFORMATION_SCHEMA.TABLES. É uma tabela temporária na memória. Ele é manipulado usando protocolos de mecanismo de armazenamento. Assim, quando o mysqld é encerrado, todas as tabelas information_schema são descartadas. Quando o mysqld é iniciado, todas as tabelas information_schema são criadas como tabelas TEMPORARY e preenchidas novamente com metadados para cada tabela na instância do mysql.

    O banco de dados INFORMATION_SCHEMA foi introduzido pela primeira vez no MySQL 5.0 para fornecer acesso a metadados sobre tabelas de outros mecanismos de armazenamento. Por exemplo, você poderia fazer SHOW DATABASES para obter uma lista de bancos de dados. Você também pode consultá-los assim:

    SELECT schema_name database FROM information_schema.schemata;

    Você pode recuperar nomes de tabelas em um banco de dados de duas maneiras:

    use mydb
show tables;

    ou

    SELECT table_name from information_schema.tables WHERE table_schema = 'mydb';

    Desde a sua criação, o MySQL expandiu o banco de dados INFORMATION_SCHEMA para ter a lista de processos (a partir do MySQL 5.1). Na verdade, você pode consultar a lista de processos procurando por consultas de execução longa que ainda estão em execução por pelo menos 10 minutos:

    SELECT * FROM information_schema.processlist WHERE time >= 600\G

    Você pode usar o INFORMATION_SCHEMA para fazer todas as coisas elaboradas: como:

    Obtenha contagens de todas as tabelas usando mecanismos de armazenamento específicos:

    SELECT COUNT(1) TableCount,IFNULL(engine,'Total') StorageEngine
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
AND engine IS NOT NULL
GROUP BY engine WITH ROLLUP;

    Obtenha o tamanho do buffer de chave MyISAM recomendado em MB

    SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_key_buffer_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE
engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A,
(SELECT 2 pw) B;

    Obtenha o tamanho recomendado do pool de buffer InnoDB em GB

    SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 3 pw) B;

    Obtenha o uso do disco de todos os bancos de dados pelo mecanismo de armazenamento em MB

    SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize
FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 2 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

    Acredite em mim, ainda existem usos maravilhosos para INFORMAÇÃO_SCHEMA que o tempo não me permite discutir mais.

    Lembre-se de que o INFORMATION_SCHEMA é tão sensível que, se o mysql estiver em execução e você fizer o seguinte:

    cd /var/lib/mysql
mkdir junkfolder

    e então entre no mysql run

    mysql> SHOW DATABASES;

    Você verá junkfolder como um dos bancos de dados.

    Saber disso é muito importante para DBAs e desenvolvedores. Capítulo 20 (desenvolvedores) e Capítulo 31 (DBAs) do livro MySQL 5.0 Certification Study Guide

    insira a descrição da imagem aqui

    estão lá para se preparar para os Exames de Certificação de Desenvolvedor e DBA. Obtenha o livro, estude bem esses capítulos e você poderá fazer grandes coisas com o INFORMATION_SCHEMA do MySQL.

    O banco de dados INFORMAÇÕES_SCHEMA a partir do MySQL 5.5, agora possui plugins, variáveis ​​globais (status e estáticas), variáveis ​​de sessão (status e estáticas), status do mecanismo de armazenamento, instrumentação de métricas de desempenho, mapa de trigger, eventos (programáveis) e muito mais.

    Desculpe, isso pode parecer WTMI, mas sou um grande defensor do uso do banco de dados INFORMATION_SCHEMA.

    • 32

relate perguntas