Intro to MySQL Information Schema

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.

Information_Schema.Tables

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)

Documentation

Information_Schema.Columns

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)

Documentation

Information_Schema.Referential_Constraints

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).

Documentation

Information_Schema.Key_Column_Usage

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

Documentation

Information_Schema.Processlist

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)

Documentation

Information_Schema.InnoDb_Lock_Waits

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.

InnoDb_Lock_Waits
InnoDb_Trx

Other views

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.



 No spam


References

Information Schema

InnoDB-Specific Information Schema

Posted in Databases, IT

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>