¿Qué es INFORMATION_SCHEMA

El soporte para INFORMATION_SCHEMA está disponible en MySQL 5.0.2 y posterior. Proporciona acceso a los metadatos de la base de datos. Metadatos son datos acerca de los datos, tales como el nombre de la base de datos o tabla, el tipo de datos de una columna, o permisos de acceso. Otros términos que a veces se usan para esta información son diccionario de datos o catálogo del sistema . INFORMATION_SCHEMA es la base de datos de información, que almacena información acerca de todas las otras bases de datos que mantiene el servidor MySQL. Dentro del INFORMATION_SCHEMA hay varias tablas de sólo lectura. En realidad son vistas, no tablas, así que no puede ver ningún fichero asociado con ellas. Cada usuario MySQL tiene derecho a acceder a estas tablas, pero sólo a los registros que se corresponden a los objetos a los que tiene permiso de acceso.
Se trata por tanto de la manera ideal de conocer los metadatos o datos de los datos de nuestras tablas de base de datos.
RECUERDA: El usuario que consulta la base de datos sólo tiene acceso a los metadatos de los objetos sobre los que tiene permisos.
RECUERDA: Se puede consultar, pero no puede cambiar su estructura o modificar sus datos.

¿Cuanto espacio ocupan cada una de nuestras bases de datos?

Podemos hacer esta consulta para que muestre el resultado en MB...

select table_schema 'DATABASE',
    convert(sum(data_length+index_length)/1048576,decimal(6,2)) 'SIZE (MB)'
from
    information_schema.tables
group by
    table_schema;

¿Como visualizar nuestro information_schema en Workbench?

1º.- Editamos las preferencias...

 

2º.- En SQL Editor, chequeamos la opcion: Show Metadata and Internal Schemas...

3º.- Actualizamos la visualizacion de los SCHEMAS...

 Y ya tenemos visible nuestra Information_schema

¿Como saber el tamaño de las tablas de nuestra base de datos?

Pues es bien sencillo gracias a la información de Information_Schema, con una simple consulta:
Tomando como ejemplo una base de datos llamada bdturismo... 

SELECT table_name Tabla,(data_length+index_length) Tamaño FROM information_schema.tables WHERE table_schema='bdturismo';

 

El tamaño está expresado en bytes.

Display Tables with More Than a Given Number of Rows

A veces es útil para ver qué tabla supera un determinado número de filas. Que pueden indicar el tiempo para archivar algunos de los datos más antiguos por razones de rendimiento. La siguiente instrucción SQL devuelve todas las tablas de las bases de datos de usuario que contienen más de 100 filas:

SELECT CONCAT(table_schema,'.',table_name) as Nombre_de_la_tabla, table_rows 
FROM   information_schema.tables WHERE table_rows > 10
AND table_schema not in('information_schema','mysql','performance_schema')
ORDER BY table_rows desc;

Find Long Running Queries

En la versión 5.1, MySQL añade el processlist a la base de datos INFORMATION_SCHEMA. Muestra información sobre todos los procesos actualmente en ejecución:

Show processlist;

Display Access Privileges

Se puede utilizar el INFORMATION_SCHEMA para revisar los privilegios de los usuarios con el fin de ayudarle a decidir qué derechos de conceder o revocar. 
Por otra parte, la base de datos INFORMATION_SCHEMA puede proporcionar puntos de vista distintos niveles de granularidad. Mientras que el comando SHOW PRIVILEGES muestra los privilegios del sistema del servidor admite, los privilegios INFORMATION_SCHEMA.SCHEMA ver listas que los usuarios de bases de datos han sido asignados los privilegios del sistema:

 SHOW PRIVILEGES;

SELECT grantee, privilege_type, is_grantable
FROM   information_schema.schema_privileges;

Resolviendo la lentitud

INFORMATION_SCHEMA puede resultar bastannte lento para trabajar cuando se trata de recuperar la tabla de metadatos.  

Muchas personas recurren al uso de las herramientas del sistema de archivos.

 Además de ser simplemente lento INFORMATION_SCHEMA a menudo puede afectar al rendimiento del servidor dramáticamente.
La solución es simple, establecer innodb_stats_on_metadata = 0, lo que impide la actualización estadística cuando se consulta INFORMATION_SCHEMA. Esto no hará innodb opere sin estadísticas en absoluto sino que innodb seguirá calculando estadísticas para la tabla la primera vez que se abre.Una prueba:

opción 1.- 

select count(*),sum(data_length) from information_schema.tables;

 

opción 2.- 

set global innodb_stats_on_metadata=0;

select count(*),sum(data_length) from information_schema.tables; 

Como se puede ver, las mejoras de rendimiento son enormes.

Obviamente, esta mejora se observa en grandes cantidades de datos.

¿Cómo actualizar INFORMATION_SCHEMA?

Todos los datos accesibles por INFORMATION_SCHEMA son vistas de lectura exclusiva. No existen archivos físicos que contengan los datos en el disco duro. Es por ello también por lo que la base de datos INFORMATION_SCHEMA está SIEMPRE actualizada, sean cuales sean las modificaciones efectuadas en el servidor MySQL.

¿Por que hacer las consultas con SELECT y no con SHOW? (2/2)

Information_schema, que no existía antes de la versión 5 de MySQL, es una base de datos de información. Guarda los metadatos de las otras bases de datos. Los metadatos son información que permite describir datos.

Anteriormente, los comandos SHOW permitían acceder a los diferentes metadatos, por ejemplo SHOW TABLES. Pero esta sintexis no es estándar y fue introducida por MySQL AB, de modo que este comando no se encuentra en otros SGBD.

Sin embargo, al utilizar una base de datos como banco de metadatos, cualquier usuario puede obtener fácilmente la información que le interese porque basta con efectuar consultas SELECT.

Una de las ventajas es que cada usuario puede formatear el resultado obtenido según sus necesidades. Así puede obtener exactamente la información que necesita.

Por ejemplo, el comando SHOW TABLES from 'mi_base_de_datos' devuelve unicamente el nombre de las tablas disponibles. Para obtener información util como el motor de almacenamiento utilizado, el tipo de tabla, tabla temporal o vista, el número de registros en cada tabla, etc, se puede ejecutar el comando SELECT TABLE_NAME, TABLE_TYPE, ENGINE from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'mi_base_de_datos';

Algunas consultas utiles usando Information_Schema

 * Mostrar si hay tablas sin PK (clave primaria)

SELECT t.TABLE_SCHEMA,t.TABLE_NAME,ENGINE
FROM information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c
ON t.TABLE_SCHEMA=c.TABLE_SCHEMA
AND t.TABLE_NAME=c.TABLE_NAME
AND t.TABLE_SCHEMA NOT IN ('performance_schema','information_schema','mysql')
GROUP BY t.TABLE_SCHEMA,t.TABLE_NAME
HAVING sum(if(column_key in ('PRI','UNI'), 1,0))=0;



* Mostrar numero de conexiones por cada usuario

SELECT USER, COUNT(*) FROM information_schema.processlist GROUP BY USER;

 
* Mostrar numero de conexiones por cada host

SELECT HOST, COUNT(*) FROM information_schema.processlist GROUP BY HOST;


* Mostrar la actividad del usuario root

SELECT * FROM information_schema.processlist WHERE USER = ‘root’;


* Mostrar procesos asociados con consultas SELECT o UPDATE

SELECT * FROM information_schema.processlist WHERE INFO LIKE ‘SELECT%’;

SELECT * FROM information_schema.processlist WHERE INFO LIKE ‘UPDATE%’;


* Mostrar tiempo medio de consulta para cada base de datos

SELECT DB, AVG(TIME) FROM information_schema.processlist GROUP BY DB;

¿Por que hacer las consultas con SELECT y no con SHOW? (1/2)

El comando SELECT ... FROM INFORMATION_SCHEMA es una forma más consistente de proporcionar acceso a la información proporcionada por los comandos SHOW que soporta MySQL (SHOW DATABASES, SHOW TABLES, y así). 

Usar SELECT tiene las siguientes ventajas, en comparación a SHOW:

• Cumple las reglas de Codd. Esto es, todo acceso se hace por tabla.

• Nadie necesita aprender una nueva sintaxis. Conocen cómo funciona SELECT , sólo necesitan aprender los nombres de los objetos.

• El implementador no tiene que preocuparse de palabras clave.

• Hay millones de variaciones de la salida, en lugar de sólo una. Esto proporciona flexibilidad a las aplicaciones con requerimientos cambiantes acerca de los metadatos que necesitan.

• La migración es más fácil ya que todos los otros DBMS funcionan así.

Algunas extensiones de comandos SHOW acompañan la implementación de INFORMATION_SCHEMA:

• SHOW puede usarse para obtener información acerca de la estructura de INFORMATION_SCHEMA mismo.
  

• Varios comandos SHOW aceptan una cláusula WHERE que proporciona más flexibilidad al especificar qué registros mostrar.

Similarmente, SHOW TABLES puede usarse con INFORMATION_SCHEMA para obtener una lista de sus tablas:

SHOW TABLES FROM INFORMATION_SCHEMA; 

Varios comandos SHOW se han extendido para permitir cláusulas WHERE :

SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW KEYS
SHOW OPEN TABLES
SHOW PROCEDURE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES

Conocer las rutinas existentes

Todos las rutinas se guardan en la BD 'information_schema' más concretamente en la tabla 'routines'.

De esta manera si lo que quieren ver son todas las rutinas creadas en nuestras bases de datos, estaría bien hacer una consulta como esta:
SELECT specific_name FROM information_schema.routines;

Si lo que quieren ver es el código de alguna rutina, para este ejemplo le vamos a llamar 'nombre_rutina':
SELECT routine_definition FROM information_schema.routines WHERE specific_name = 'nombre_rutina';

Si lo que deseamos es saber de que tipo es, es decir, si es función o procedimiento entonces:
SELECT routine_type FROM information_schema.routines WHERE specific_name = 'nombre_rutina';

Y así de esta manera podemos saber muchos detalles de nuestras rutinas.

Si quieres más información basta con que le eches un vistazo a las columnas de la tabla 'routines' del esquema 'information_schema', puedes hacer esta consulta:
SHOW columns FROM information_schema.routines;

Distintas opciones de consulta (2/2):

La tabla INFORMATION_SCHEMA SCHEMA_PRIVILEGES

La tabla SCHEMA_PRIVILEGES proporciona información acerca del esquema de permisos (base de datos). Esta información viene de la tabla de permisos mysql.db .

SELECT *  
  FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES


Las columnas que puede mostrar son: 

GRANTEE
TABLE_CATALOG
ABLE_SCHEMA
PRIVILEGE_TYPE
IS_GRANTABLE

La tabla INFORMATION_SCHEMA TABLE_PRIVILEGES

La tabla TABLE_PRIVILEGES proporciona información de permisos de tablas. Esta información viene de la tabla de permisos mysql.tables_priv .

SELECT *
 FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES


Las columnas que puede mostrar son: 

GRANTEE
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
PRIVILEGE_TYPE
IS_GRANTABLE

La tabla INFORMATION_SCHEMA COLUMN_PRIVILEGES

La tabla COLUMN_PRIVILEGES proporciona información acerca de permisos de columnas. Esta información viene de la tabla de permisos mysql.columns_priv

SELECT *
 FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES

Las columnas que puede mostrar son: 

GRANTEE
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
PRIVILEGE_TYPE
IS_GRANTABLE

La tabla INFORMATION_SCHEMA CHARACTER_SETS

La tabla CHARACTER_SETS proporciona información acerca de los conjuntos de caracteres disponibles.

SELECT *
 FROM INFORMATION_SCHEMA.CHARACTER_SETS
 [WHERE CHARACTER_SET_NAME LIKE 'nombre_del_tipo_de_caracter']  

 Las columnas que puede mostrar son: 

CHARACTER_SET_NAME
DEFAULT_COLLATE_NAME
DESCRIPTION
MAXLEN

La tabla INFORMATION_SCHEMA COLLATIONS

La tabla COLLATIONS proporciona información acerca de colaciones para cada conjunto de caracteres.

SELECT *
  FROM INFORMATION_SCHEMA.COLLATIONS
  [WHERE collation_name LIKE 'nombre_de_la_colacion'] 


Las columnas que puede mostrar son: 
 
COLLATION_NAME
CHARACTER_SET_NAME
ID
IS_DEFAULT
IS_COMPILED
SORTLEN

La tabla INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY

La tabla COLLATION_CHARACTER_SET_APPLICABILITY indica qué conjunto de caracteres es aplicable a cada colación.

SELECT *
 FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY [WHERE collation_name LIKE 'nombre_de_la_colacion']

Las columnas que puede mostrar son: 

COLLATION_NAME
CHARACTER_SET_NAME

La tabla INFORMATION_SCHEMA TABLE_CONSTRAINTS

La tabla TABLE_CONSTRAINTS describe qué tablas tienen restricciones


SELECT *
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS [WHERE table_name = 'nombre_de_la_tabla']
 [WHERE | AND table_schema = 'nombre_de_la_base_de_datos']

Las columnas que puede mostrar son: 
 
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE 

La tabla INFORMATION_SCHEMA KEY_COLUMN_USAGE

La tabla KEY_COLUMN_USAGE describe qué columnas clave tienen restricciones

SELECT *
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 [WHERE table_name = 'nombre_de_la_tabla']
 [WHERE | AND table_schema = 'nombre_de_la_base_de_datos']

Las columnas que puede mostrar son: 

CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
POSITION_IN_UNIQUE_CONSTRAINT
REFERENCED_TABLE_SCHEMA
REFERENCED_TABLE_NAME
REFERENCED_COLUMN_NAME

La tabla INFORMATION_SCHEMA ROUTINES

La tabla ROUTINES proporciona información acerca de rutinas almacenadas (procedimientos y funciones).


SELECT *
 FROM INFORMATION_SCHEMA.ROUTINES 
 [WHERE table_name = 'nombre_de_la_tabla']

Las columnas que puede mostrar son: 
 
SPECIFIC_NAME
ROUTINE_CATALOG
ROUTINE_SCHEMA
ROUTINE_NAME
ROUTINE_TYPE
DATA_TYPE
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_NAME
COLLATION_NAME
DTD_IDENTIFIER
ROUTINE_BODY
ROUTINE_DEFINITION
EXTERNAL_NAME
EXTERNAL_LANGUAGE
PARAMETER_STYLE
IS_DETERMINISTIC
SQL_DATA_ACCESS
SQL_PATH
SECURITY_TYPE
CREATED
LAST_ALTERED
SQL_MODE
ROUTINE_COMMENT
DEFINER
CHARACTER_SET_CLIENT
COLLATION_CONNECTION
DATABASE_COLLATION 

La tabla INFORMATION_SCHEMA VIEWS

La tabla VIEWS proporciona información acerca de las vistas en las bases de datos.

SELECT *
 FROM INFORMATION_SCHEMA.VIEWS  

Las columnas que puede mostrar son:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
VIEW_DEFINITION
CHECK_OPTION
IS_UPDATABLE
DEFINER
SECURITY_TYPE
CHARACTER_SET_CLIENT
COLLATION_CONNECTION
ALGORITHM

La tabla INFORMATION_SCHEMA TRIGGERS

La tabla TRIGGERS proporciona información acerca de disparadores.
Esta tabla se implementó inicialmente en MySQL 5.0.10.
Debe tener el permiso SUPER para ver esta tabla.
Las columnas TRIGGER_SCHEMA y TRIGGER_NAME contienen el nombre de la base de datos en que se produce el dispardor, y el nombre del disparador, respectivamente.

SELECT * 
  FROM INFORMATION_SCHEMA.TRIGGERS

Las columnas que puede mostrar son: 

TRIGGER_CATALOG

TRIGGER_SCHEMA
TRIGGER_NAME
EVENT_MANIPULATION
EVENT_OBJECT_CATALOG
EVENT_OBJECT_SCHEMA
EVENT_OBJECT_TABLE
ACTION_ORDER
ACTION_CONDITION
ACTION_STATEMENT
ACTION_ORIENTATION
ACTION_TIMING
ACTION_REFERENCE_OLD_TABLE
ACTION_REFERENCE_NEW_TABLE
ACTION_REFERENCE_OLD_ROW
ACTION_REFERENCE_NEW_ROW
CREATED
SQL_MODE
DEFINER
CHARACTER_SET_CLIENT
COLLATION_CONNECTION
DATABASE_COLLATION

La tabla INFORMATION_SCHEMA PARAMETERS


La tabla PARAMETERS proporciona información acerca de los parámetros en las bases de datos.

SELECT * 
  FROM INFORMATION_SCHEMA.PARAMETERS 

Las columnas que puede mostrar son:TRIGGER_CATALOG

SPECIFIC_CATALOG
SPECIFIC_SCHEMA
SPECIFIC_NAME
ORDINAL_POSITION
PARAMETER_MODE
PARAMETER_NAME
DATA_TYPE
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_NAME
COLLATION_NAME
DTD_IDENTIFIER
ROUTINE_TYPE
 
La tabla INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS

La tabla REFERENTIAL_CONSTRAINTS proporciona información acerca de las claves foráneas en las bases de datos.

SELECT * 
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Las columnas que puede mostrar son: 

CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
UNIQUE_CONSTRAINT_CATALOG
UNIQUE_CONSTRAINT_SCHEMA
UNIQUE_CONSTRAINT_NAME
MATCH_OPTION
UPDATE_RULE
DELETE_RULE
TABLE_NAME
REFERENCED_TABLE_NAME