Getting information about databases and tables
You might have already noticed an information_schema database in the list of databases. information_schema is a collection of views that consist of metadata about all the database objects. You can connect to information_schema and explore all the tables. You either query the information_schema tables or use the SHOW command, which essentially does the same.
INFORMATION_SCHEMA queries are implemented as views over the data dictionary tables. There are two types of metadata in the INFORMATION_SCHEMA tables:
- Static table metadata:
TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE, andENGINE. These statistics will be read directly from the data dictionary. - Dynamic table metadata:
AUTO_INCREMENT,AVG_ROW_LENGTH, andDATA_FREE. Dynamic metadata frequently changes (for example, theAUTO_INCREMENTvalue will advance after eachINSERT).
Check the list of all the tables:
USE INFORMATION_SCHEMA;
Database changed
SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| COLUMN_STATISTICS |
| ENGINES |
| EVENTS |
| FILES |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CACHED_INDEXES |
| INNODB_CMP |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_CMP_RESET |
| INNODB_COLUMNS |
| INNODB_DATAFILES |
| INNODB_FIELDS |
| INNODB_FOREIGN |
| INNODB_FOREIGN_COLS |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_CONFIG |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_DELETED |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_INDEXES |
| INNODB_METRICS |
| INNODB_TABLES |
| INNODB_TABLESPACES |
| INNODB_TABLESPACES_BRIEF |
| INNODB_TABLESTATS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_TRX |
| INNODB_VIRTUAL |
| KEYWORDS |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| RESOURCE_GROUPS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| ST_GEOMETRY_COLUMNS |
| ST_SPATIAL_REFERENCE_SYSTEMS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
62 rows in set (0.00 sec)
TABLES
The TABLES table contains all the information about the table, such as which database belongs to TABLE_SCHEMA, the number of rows (TABLE_ROWS), ENGINE, DATA_LENGTH, INDEX_LENGTH, and DATA_FREE:
DESC INFORMATION_SCHEMA.TABLES;
+-----------------+--------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------------------------------------------------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(64) | NO | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | NULL | |
| TABLE_NAME | varchar(64) | NO | | NULL | |
| TABLE_TYPE | enum('BASE TABLE','VIEW','SYSTEM VIEW') | NO | | NULL | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | int(2) | YES | | NULL | |
| ROW_FORMAT | enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | timestamp | NO | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(64) | YES | | NULL | |
| CHECKSUM | bigint(21) | YES | | NULL | |
| CREATE_OPTIONS | varchar(256) | YES | | NULL | |
| TABLE_COMMENT | varchar(256) | YES | | NULL | |
+-----------------+--------------------------------------------------------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
For example, you want to know DATA_LENGTH, INDEX_LENGTH, and DATE_FREE inside the employees database:
SELECT SUM(DATA_LENGTH)/1024/1024 AS DATA_SIZE_MB, SUM(INDEX_LENGTH)/1024/1024 AS INDEX_SIZE_MB, SUM(DATA_FREE)/1024/1024 AS DATA_FREE_MB FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='employees';
+--------------+---------------+--------------+
| DATA_SIZE_MB | INDEX_SIZE_MB | DATA_FREE_MB |
+--------------+---------------+--------------+
| 128.82812500 | 5.54687500 | 18.00000000 |
+--------------+---------------+--------------+
1 row in set (0.06 sec)
COLUMNS
This table lists all the columns and its definition for each table:
SELECT * FROM COLUMNS WHERE TABLE_NAME='employees'\G
...
*************************** 5. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: employees
TABLE_NAME: employees
COLUMN_NAME: hire_date
ORDINAL_POSITION: 6
COLUMN_DEFAULT: NULL
IS_NULLABLE: NO
DATA_TYPE: date
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
COLUMN_TYPE: date
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULL
*************************** 6. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: employees
TABLE_NAME: employees
COLUMN_NAME: last_name
ORDINAL_POSITION: 4
COLUMN_DEFAULT: NULL
IS_NULLABLE: NO
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 16
CHARACTER_OCTET_LENGTH: 64
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_0900_ai_ci
COLUMN_TYPE: varchar(16)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULL
6 rows in set (0.00 sec)
FILES
You have already seen that MySQL stores the InnoDB data in the .ibd files inside a directory (with the same name as the database name) in the data directory. To get more information about the files, you can query the FILES table:
SELECT * FROM FILES WHERE FILE_NAME LIKE './employees/employees.ibd'\G
*************************** 1. row ***************************
FILE_ID: 6
FILE_NAME: ./employees/employees.ibd
FILE_TYPE: TABLESPACE
TABLESPACE_NAME: employees/employees
TABLE_CATALOG:
TABLE_SCHEMA: NULL
TABLE_NAME: NULL
LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
ENGINE: InnoDB
FULLTEXT_KEYS: NULL
DELETED_ROWS: NULL
UPDATE_COUNT: NULL
FREE_EXTENTS: 2
TOTAL_EXTENTS: 29
EXTENT_SIZE: 1048576
INITIAL_SIZE: 0
MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 1048576
CREATION_TIME: NULL
LAST_UPDATE_TIME: NULL
LAST_ACCESS_TIME: NULL
RECOVER_TIME: NULL
TRANSACTION_COUNTER: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: 2097152
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
STATUS: NORMAL
EXTRA: NULL
1 row in set (0.00 sec)
You should be keen at DATA_FREE, which represents the unallocated segments plus the data that is free inside the segments due to fragmentation. When you rebuild the table, you can free up bytes shown in DATA_FREE.
INNODB_SYS_TABLESPACES
The size of the file is also available in the INNODB_TABLESPACES table:
SELECT * FROM INNODB_TABLESPACES WHERE NAME='employees/employees'\G
*************************** 1. row ***************************
SPACE: 6
NAME: employees/employees
FLAG: 16417
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 30408704
ALLOCATED_SIZE: 30412800
SERVER_VERSION: 8.0.12
SPACE_VERSION: 1
1 row in set (0.00 sec)
You can verify the same in the filesystem:
sudo ls -ltr /var/lib/mysql/employees/employees.ibd
-rw-r----- 1 mysql mysql 30408704 Sep 9 15:18 /var/lib/mysql/employees/employees.ibd
INNODB_TABLESTATS
The size of the index and approximate number of rows is available in the INNODB_TABLESTATS table:
SELECT * FROM INNODB_TABLESTATS WHERE NAME='employees/employees'\G
*************************** 1. row ***************************
TABLE_ID: 1063
NAME: employees/employees
STATS_INITIALIZED: Initialized
NUM_ROWS: 299069
CLUST_INDEX_SIZE: 929
OTHER_INDEX_SIZE: 0
MODIFIED_COUNTER: 0
AUTOINC: 0
REF_COUNT: 3
1 row in set (0.01 sec)
PROCESSLIST
One of the most used views is the process list. It lists all the queries running on the server:
SELECT * FROM PROCESSLIST\G
*************************** 1. row ***************************
ID: 8
USER: root
HOST: localhost
DB: information_schema
COMMAND: Query
TIME: 0
STATE: executing
INFO: SELECT * FROM PROCESSLIST
*************************** 2. row ***************************
ID: 4
USER: event_scheduler
HOST: localhost
DB: NULL
COMMAND: Daemon
TIME: 2381
STATE: Waiting on empty queue
INFO: NULL
2 rows in set (0.00 sec)
Or you can execute SHOW PROCESSLIST; to get the same output.
SHOW PROCESSLIST;
+----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 2399 | Waiting on empty queue | NULL |
| 8 | root | localhost | information_schema | Query | 0 | starting | SHOW PROCESSLIST |
+----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
Other tables: ROUTINES contains the definition of functions and stored routines. TRIGGERS contains the definition of triggers. VIEWS contains the definition of views.
To learn about the improvements in INFORMATION_SCHEMA, refer to http://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/.