Trabalhando com o INFORMATION_SCHEMA

postado em: Ferramentas, Tutoriais | Comments

Olá pessoal!

Hoje eu vou falar um pouco sobre o INFORMATION_SCHEMA e as informações contidas nesta base de dados.

O que é o INFORMATION_SCHEMA?

O INFORMATION_SCHEMA é um banco de dados somente leitura que fornece acesso aos METADADOS (metadata) do servidor. Os metadados são dados sobre os dados, tais como o nome de uma base ou de uma tabela, o tipo de dados de uma tabela, ou privilégios de acesso. Outros termos que às vezes são usados para estas informações são: data dictionary e system catalog. É o banco de dados de informações, informações sobre todos os outros bancos de dados que o servidor MySQL mantém. Dentro do INFORMATION_SCHEMA existem várias tabelas que são somente leitura, não são tabelas de base, não existem arquivos associados a elas. Além disso, não há nenhum diretório de banco de dados com esse nome.

Embora você possa selecionar o INFORMATION_SCHEMA como banco de dados padrão com uma declaração de uso, você pode apenas ler o conteúdo das tabelas, não pode executar INSERT, UPDATE, ou DELETE.

Sabemos o que é e o que existe dentro do INFORMATION_SCHEMA, mas e agora? Vou mostrar alguns comandos que vão lhes ajudar a coletar algumas informações.

Na prática

O comando abaixo, por exemplo, faz o que o “SHOW PROCESSLIST” faria, porém, assim conseguimos organizar as informações e ordenar os usuários ativos pelo número de conexões abertas. Se você preferir, pode rodar o SHOW PROCESSLIST e ver quais são as colunas que ele exibe e as utilizar no SELECT.

mysql> SELECT id,user,host,db, COUNT(user) AS ConexoesAbertas FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY user ORDER BY ConexoesAbertas DESC;
+--------+--------------+---------------------+--------------------+-----------------+
| id     | user         | host                | db                 | ConexoesAbertas |
+--------+--------------+---------------------+--------------------+-----------------+
| 877805 | system user  |                     | NULL               |               3 |
| 873260 | root         | localhost           | information_schema |               1 |
|     58 | teste        | 10.1.3.4:53793      | teste              |               1 |
|     59 | teste2       | 10.1.3.4:53796      | teste2             |               1 |
+--------+--------------+---------------------+--------------------+-----------------+

E para descobrir o espaço ocupado por um banco? Basta executar este outro SELECT passando o nome do banco e você terá esta outras informações referentes ao banco desejado.

 SELECT table_rows, data_length, index_length,round(((data_length + index_length) / 1024 / 1024),2) 'Size in MB' FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'DATABASENAME' and TABLE_TYPE='BASE TABLE' ORDER BY data_length DESC;
+------------+-------------+--------------+------------+
| table_rows | data_length | index_length | Size in MB |
+------------+-------------+--------------+------------+
|          3 |       16384 |            0 |       0.02 |
|          5 |       16384 |            0 |       0.02 |
|         32 |       16384 |            0 |       0.02 |
|          4 |       16384 |            0 |       0.02 |
|         18 |        2076 |         2048 |       0.00 |
|          0 |           0 |         1024 |       0.00 |
+------------+-------------+--------------+------------+

Mas se você quiser saber o tamanho de todos os bancos de dados do seu servidor, o SELECT é diferente e menor. Só tenha cuidado, dependendo da quantidade de bancos que existam no seu SGBD, esta query pode demorar muito a ser executada.

mysql> SELECT table_schema "Data Base Name" ,sum((( data_length + index_length ) / 1024 / 1024)) "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| teste              |           2.57631302 |
| teste2             |           2.56250000 |
| information_schema |           0.00976560 |
| mysql              |           0.78808306 |
| performance_schema |           0.00000000 |
+--------------------+----------------------+

O próximo comando exibe os nomes das tabelas de um determinado banco, o tipo da tabela e a engine de cada tabela.

mysql> SELECET table_name, table_type, engine FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'DATABASENAME';
+-----------------------+------------+--------+
| table_name            | table_type | engine |
+-----------------------+------------+--------+
| wp_commentmeta        | BASE TABLE | InnoDB |
| wp_comments           | BASE TABLE | InnoDB |
| wp_links              | BASE TABLE | InnoDB |
| wp_options            | BASE TABLE | InnoDB |
| wp_postmeta           | BASE TABLE | InnoDB |
| wp_posts              | BASE TABLE | InnoDB |
| wp_term_relationships | BASE TABLE | InnoDB |
| wp_term_taxonomy      | BASE TABLE | InnoDB |
| wp_terms              | BASE TABLE | InnoDB |
| wp_usermeta           | BASE TABLE | InnoDB |
| wp_users              | BASE TABLE | InnoDB |
+-----------------------+------------+--------+

 

Estes são apenas alguns exemplos de como utilizar o INFORMATION_SCHEMA para administrar melhor o seu SGBD. Se quiser saber mais sobre, basta acessar a documentação oficial do MySQL.

 

Allan Moraes

Allan Moraes é gaúcho e entusiasta open source. Trabalha em uma Startup com foco em Plataforma como Serviço (PaaS), é especialista em MySQL, High Availability e High Scalability Architecture.