mysql information schema 相关表介绍与应用

information_schema 数据库介绍

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

MySQL 5.7 Reference Manual :: 24 INFORMATION_SCHEMA Tables​dev.mysql.com

重要相关表

sql语句应用

1. tables表应用，查相关表的数据大小和索引大小，sql如下： select table_name, data_length/1000000, index_length/1000000from information_schema.tables where table_schema = 'sp_etl' and table_name like 'creative%' order by data_length desc, index_length desc;
2. processlist表应用，表解析参考如下文档： Cailiang：mysql: show processlist 详解 ， https://dev.mysql.com/doc/refman/5.6/en/thread-commands.html 1）select command, count(*) from information_schema.processlist group by command;2）select ip, count(*) as num from ( select SUBSTR(host, 1, INSTR(host, ':') - 1) as ip, db , command, info from information_schema.processlist ) as a group by ip order by num desc;3）select ip, command, count(*) as num from ( select SUBSTR(host, 1, INSTR(host, ':') - 1) as ip, db , command, info from information_schema.processlist ) as a where command = 'Sleep' group by ip, command order by num desc;4）linux查某一台机器下的mysql连接情况：netstat -ntp |grep 3306|grep -v grep
3. 锁、事务、线程1）查看正在锁的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;2）查看等待锁的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;3）show status like '%lock%'如当Table_locks_waited(表锁等待时间)/Table_locks_immediate(立即释放表锁数)的比值较大，则说明我们的表锁造成的阻塞比较严重，可能需要调整Query语句，或者更改存储引擎，亦或者需要调整业务逻辑。当然，具体改善方式必须根据实际场景来判断。而Innodb_row_lock_waits较大，则说明Innodb的行锁也比较严重，且影响了其他线程的正常处理。同样需要查找出原因并解决。造成Innodb行锁严重的原因可能是Query语句所利用的索引不够合理（Innodb行锁是基于索引来锁定的），造成间隙锁过大。也可能是系统本身处理能力有限，则需要从其他方面来考虑解决.

4）查看谁在等待即被堵塞，是谁阻塞，及等待了多久，堵塞了多久，堵塞的线程空闲 了多久:

5)查看多少操作被哪些线程阻塞，该线程空闲了多长时间，所有操作中被阻塞了最大时间是多少秒，总共有几个操作被该线程阻塞: