Databases store information, right? Well, what if they could store information about your information so you could query it? Good news! Most database engines such as MySQL implement Information Schema, or a set of views that describe your tables and columns. If you’ve ever used a language like Java or C#, you might be familiar with reflection: the ability to read an object’s metadata. This enables you to do meta-programming, or writing logic about the program itself.
The idea of Information Schema is like reflection: they are views in your database that you can use in your programs or scripts when you need to know what the schema looks like or check the health of the instance. Unlike Show Processlist or Desc Table commands, the results are in tabular format.
I’m going to tour through six important views, and I’ll point you to some others at the end. Let’s get started.
Let’s begin at the beginning, with the tables that make up your database. This view has the basic items like table name, the storage engine, and the collation (the way the table sorts text). There’s a column called table_catalog that I want to call out, mostly because any field in these views named *_catalog is useless – it’s always “def”. The database name is in the *_schema columns.
A great use of this view is querying to see what the biggest tables are:
Select Concat(table_schema, '.', table_name) As "Name" ,Concat(Round(table_rows / 1000000, 2), 'M') As "Rows" ,Concat(Round(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') As "Row Size" ,Concat(Round(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') As "Index Size" ,Concat(Round(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') As "Total" ,Round(index_length / data_length, 2) "Row / Index Ratio" From information_schema.TABLES Order By data_length + index_length DESC Limit 10;
This gets the top 10 tables by size (thanks to Peter Zaitsev)
Another clever use is finding tables that are using the wrong collation or storage engine:
mysql> Select Table_Name -> From Information_Schema.Tables -> Where Engine <> 'innodb' and Table_Schema = 'employees'; +------------+ | Table_Name | +------------+ | offender | +------------+ 1 row in set (0.00 sec)
What would a table be without columns? This next view lists out the columns with their data types, character sets, position in the table, etc. An example use would be a migration script that would select from this view to see if the column has already been created, and if it has, move on to the next migration. You can also use view to search for a column name:
mysql> Select Table_Name, Column_Name From Information_schema.Columns -> Where Column_Name Like '%name%' And Table_Schema = 'employees'; +-------------+-------------+ | Table_Name | Column_Name | +-------------+-------------+ | departments | dept_name | | employees | first_name | | employees | last_name | | form | Name | +-------------+-------------+ 4 rows in set (0.02 sec)
This shows you foreign keys (I hope foreign keys aren’t an alien concept… get it?) This table allows you to see the relationships between tables, which will in turn help you understand the schema as a whole.
mysql> Select -> Table_Name -> ,Constraint_Name -> ,Referenced_Table_Name Ref_Tbl -> ,Unique_Constraint_Name As Ref_Cnstr -> From Information_schema.Referential_Constraints -> Where Constraint_Schema = 'employees'; +--------------+---------------------+-------------+-----------+ | Table_Name | Constraint_Name | Ref_Tbl | Ref_Cnstr | +--------------+---------------------+-------------+-----------+ | dept_emp | dept_emp_ibfk_1 | employees | PRIMARY | | dept_emp | dept_emp_ibfk_2 | departments | PRIMARY | | dept_manager | dept_manager_ibfk_1 | employees | PRIMARY | | dept_manager | dept_manager_ibfk_2 | departments | PRIMARY | | salaries | salaries_ibfk_1 | employees | PRIMARY | | titles | titles_ibfk_1 | employees | PRIMARY | +--------------+---------------------+-------------+-----------+ 6 rows in set (0.00 sec)
This is the output from running on the employees sample database. While all the foreign keys reference the primary key of another table, you can also reference any unique constraint (a primary key is just a not-null unique constraint).
While the name sounds like it deals with indexes, it doesn’t. The main purpose of this table is to show those columns that make up the primary, unique, and foreign key constraints in the system. While not super useful by itself, it can be combined with other views to help you script out things such as dropping and restoring foreign keys (say you wanted to load a big table, but didn’t want each row to be verified, so you drop the FKs then add them when the table is finished loading).
Select CONCAT('ALTER TABLE ', table_name, ' ADD CONSTRAINT ', constraint_name, ' FOREIGN KEY (', ReferencingColumns, ') REFERENCES ', referenced_table_name, '(', ReferencedColumns, ')', ' ON DELETE ', delete_rule, ' ON UPDATE ', update_rule, ';') AS CreateStatement, CONCAT('ALTER TABLE ', table_name, ' DROP FOREIGN KEY ', constraint_name, ';') AS DropStatement From ( Select kcu.constraint_name ,kcu.table_name ,kcu.referenced_table_name ,GROUP_CONCAT(DISTINCT CONCAT('`',referenced_column_name,'`') ORDER BY ordinal_position) As ReferencedColumns ,GROUP_CONCAT(DISTINCT CONCAT('`',column_name,'`') ORDER BY ordinal_position) As ReferencingColumns ,rc.update_rule ,rc.delete_rule From Information_schema.key_column_usage kcu Join Information_schema.referential_constraints rc On rc.constraint_schema = kcu.table_schema And rc.constraint_name = kcu.constraint_name And rc.table_name = kcu.table_name Where kcu.table_schema='employees' And kcu.table_name='titles' Group By kcu.constraint_name ,kcu.table_name ,kcu.referenced_table_name ,rc.update_rule ,rc.delete_rule ) AS Q
You’re probably familiar with Show Processlist, the command that has been used in anger many times by DBAs who are troubleshooting production slowness or query timeouts. This is roughly the same information, but presented in a nice tabular format.
mysql> Select User, Command, Time From Information_schema.processlist; +------+---------+------+ | User | Command | Time | +------+---------+------+ | root | Sleep | 86 | | root | Sleep | 439 | | root | Query | 0 | +------+---------+------+ 3 rows in set (0.01 sec)
This is a lifesaver: it can show you exactly who’s gumming up the works in your system. I’ve used this query many times to help troubleshoot issues and get them resolved:
Select r.trx_id waiting_trx_id ,r.trx_mysql_thread_id waiting_thread ,r.trx_query waiting_query ,b.trx_id blocking_trx_id ,b.trx_mysql_thread_id blocking_thread ,b.trx_query blocking_query From information_schema.innodb_lock_waits w Join information_schema.innodb_trx b On b.trx_id = w.blocking_trx_id Join information_schema.innodb_trx r On r.trx_id = w.requesting_trx_id;
Nice, huh? That script comes from here (http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-information-schema-examples.html). It’s not a very useful view by itself. The only thing it can tell you is whether there are a bunch of waiting queries. However, combined with Information_Schema.InnoDb_Trx, you can quickly discover what queries are destroying your system.
While that wasn’t an extensive review, it does introduce you to the idea and how to use the views to make your life easier. Read the docs and get creative in the ways you combine them. They can give you sight where previously you were blind.
Some other tables I’d look at are Information_Schema.Statistics, which shows the index columns along with their cardinality, and Information_Schema.InnoDb_Buffer_Pool_Stats, which dives into statistics on the InnoDB buffer pool.
If you’ve made it this far, you should go ahead and subscribe to my MySQL Simplified newsletter.
Want to hear more about what I’ve learned about MySQL? You can subscribe below and I’ll send you updates on a new book I’m writing that helps you find the goodies in MySQL. If you sign-up, I’ll also send out stuff on MySQL like tips and tutorials.