1.mysql
2.sys
3.performance_schema
4.information_schema
1.mysql
mysql的核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.01 sec)
2.sys
Sys库所有的数据源来自：performance_schema,目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容,让DBA更快的了解DB的运行情况。
常用的 SQL 语句
生产环境下查询
谁使用了最多的资源？
mysql> select * from host_summary limit 1;
+---------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+---------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| 111.172.3.185 | 69 | 1.03 s | 14.91 ms | 36 | 2799 | 107.13 ms | 0 | 3 | 1 | 0 bytes | 0 bytes |
+---------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
1 row in set (0.04 sec)
mysql> select * from io_global_by_file_by_bytes limit 1;
+--------------------------+------------+------------+----------+-------------+---------------+------------+------------+-----------+
| file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct |
+--------------------------+------------+------------+----------+-------------+---------------+------------+------------+-----------+
| @@datadir/xb_doublewrite | 0 | 0 bytes | 0 bytes | 1196468 | 155.88 GiB | 136.61 KiB | 155.88 GiB | 100.00 |
+--------------------------+------------+------------+----------+-------------+---------------+------------+------------+-----------+
1 row in set (0.00 sec)
mysql> select * from user_summary limit 1;
+------+------------+-------------------+-----------------------+-------------+-----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| user | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_hosts | current_memory | total_memory_allocated |
+------+------------+-------------------+-----------------------+-------------+-----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| root | 610372910 | 2.97 w | 2.94 ms | 180177228 | 179758297 | 2.58 d | 18 | 128967 | 19 | 0 bytes | 0 bytes |
+------+------------+-------------------+-----------------------+-------------+-----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
1 row in set (0.01 sec)
mysql> select * from memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 157.63 MiB |
+-----------------+
1 row in set (0.00 sec)
大部分连接来自哪里及发送的 SQL 情况 查看当前连接情况：
select host, current_connections,statements from host_summary;
--查看当前正在执行的 SQL：
select conn_id, user, current_statement, last_statement from session;
执行最多的 SQL 语句是什么样?
select * from statement_analysis order by exec_count desc limit 10;
哪张表的 IO 最多？哪张表访问次数最多
select * from io_global_by_file_by_bytes limit 10;
select * from statement_analysis order by exec_count desc limit 10;
哪些语句延迟比较严重
select * from statement_analysis order by avg_latency desc limit 10;
哪些 SQL 语句使用了磁盘临时表
select db, query, tmp_tables,tmp_disk_tables from statement_analysis
where tmp_tables>0 or tmp_disk_tables >0 order by
(tmp_tables+tmp_disk_tables) desc limit 20;
哪张表占用了最多的 buffer pool
select * from innodb_buffer_stats_by_table order by pages desc limit 10;
每个库占用多少 buffer pool
select * from innodb_buffer_stats_by_schema;
每个连接分配多少内存
select b.user, current_count_used,current_allocated, current_avg_alloc,
current_max_alloc,total_allocated,current_statement from
memory_by_thread_by_current_bytes a,session b where a.thread_id =
b.thd_id;
MySQL 内部现在有多少个线程在运行
select user, COUNT(*) from processlist group by user;
select * from processlist;
3.performance_schema
主要用于收集数据库服务器性能参数：
提供进程等待的详细信息，包括锁、互斥变量、文件信息； 保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断； 对于新增和删除监控事件点都非常容易，并可以改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。 通过该库得到数据库运行的统计信息，更好分析定位问题和完善监控信息。
并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。
MySQL5.5默认是关闭的，需要手动开启，在配置文件里添加.
从MySQL5.6开始，默认打开.
CREATE TABLE `accounts` (
`USER` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`CURRENT_CONNECTIONS` bigint(20) NOT NULL,
`TOTAL_CONNECTIONS` bigint(20) NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8;
mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
常用的SQL
关于SQL维度的统计信息主要集中在events_statements_summary_by_digest表中，通过将SQL语句抽象出digest，可以统计某类SQL语句在各个维度的统计信息
-- 哪个SQL执行最多：
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC LIMIT 1\G\
-- 哪个SQL平均响应时间最多：
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC LIMIT 1\G\
-- 哪个SQL扫描的行数最多：
SUM_ROWS_EXAMINED
-- 哪个SQL使用的临时表最多：
SUM_CREATED_TMP_DISK_TABLES、SUM_CREATED_TMP_TABLES
-- 哪个SQL返回的结果集最多：
SUM_ROWS_SENT
-- 哪个SQL排序数最多：
SUM_SORT_ROWS
通过上述指标我们可以间接获得某类SQL的逻辑IO(SUM_ROWS_EXAMINED)，CPU消耗(SUM_SORT_ROWS)，网络带宽(SUM_ROWS_SENT)的对比。
通过file_summary_by_instance表，可以获得系统运行到现在，哪个文件(表)物理IO最多，这可能意味着这个表经常需要访问磁盘IO。
-- 哪个表、文件逻辑IO最多（热数据）：
SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2\G
-- 哪个索引使用最多：
SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC LIMIT 1;
通过table_io_waits_summary_by_index_usage表，可以获得系统运行到现在，哪个表的具体哪个索引(包括主键索引，二级索引)使用最多。
-- 哪个索引没有使用过：
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
-- 哪个等待事件消耗的时间最多：
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC LIMIT 1;
4.information_schema
information_schema是一个信息数据库，它保存着关于MySQL服务器所维护的所有其他数据库的信息。(如数据库名，数据库的表，表栏的数据类型与访问权限等。) 在INFORMATION_SCHEMA中，有几张只读表。它们实际上是视图，而不是基本表。
-- information_schema 常用查询案例说明：
01.获取 mysql 所有表的 SQL 语句：
SELECT * FROM information_schema.tables;
02.获取表字段的 SQL 语句
SELECT * FROM information_schema.columns;
03.获取表主键值的 SQL 语句
SELECT * FROM information_schema.key_column_usage WHERE table_schema='employees' AND table_name='employees';
04.获取表 CHECK 约束的 SQL 语句
SELECT * FROM information_schema.table_constraints;
05.获取表索引的 SQL 语句
SELECT * FROM information_schema.statistics;
06.MySQL 查询某张表在哪个数据库里：
SELECT * FROM TABLES WHERE table_name='employees';
07.查 deptno 字段在哪个数据库的哪张表里:
SELECT TABLE_SCHEMA,TABLE_NAME FROM COLUMNS WHERE COLUMN_NAME='dept_no';
08.查询 MySQL 中某个数据库中有多少张表： :
SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES WHERE table_schema = 'employees' GROUP BY table_schema;
09.查询 MySQL 中某个数据库中某个表里有多少列：
SELECT COUNT(*) FROM COLUMNS WHERE TABLE_NAME='employees' AND TABLE_SCHEMA='employees';
10.获取所有表结构(TABLES)
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='employees';
11.获取表字段(COLUMNS)
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='employees' AND TABLE_NAME='employees';
12.获取表键值
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='employees' AND TABLE_NAME='employees';
13.获取表 CHECK 约束
SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA='employees' AND TABLE_NAME='employees';
14.获取表索引
SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA='employees' AND TABLE_NAME='employees';
文章转载自数据库BOSS，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
