本文旨在整理mysql从场景化运维角度来整理，常用的检查命令和处理方式，以供参考查看，为相关活动提供便利指导。
二、MySQL巡检参考 2.1、检查MySQL主从复制健康状态show slave statusG #通过重要字段来判断当前主机的主从复制状态是否健康 …… Slave_IO_Running: Yes Slave_SQL_Running: Yes …… Slave_SQL_Running_State: Reading event from the relay log Seconds_Behind_Master: 4508 #检查进程，查看是否异常连接 show processlist; #或show full processlist; #查看当前失败连接数 show global status like 'aborted_connects'; #查看有多少由于客户没有正确关闭连接而死掉的连接数 show global status like 'aborted_clients'; #查看最大连接数 show variables like '%max_connections%'; show global status like 'max_connections'; #查看Innodb死锁 show engine innodb statusG #当遇到死锁时Innodb会回滚事务；我们应了解死锁何时发生对于追溯其发生的根本原因非常重要。我们必须知道产生了什么样的死锁，相关应用是否正确处理或已采取了相关措施 #全表扫描比例计算方式如下： ((Handler_read_rnd_next+Handler_read_rnd)/(Handler_read_rnd_next+Handler_read_rnd+Handler_read_first+Handler_read_next+Handler_read_key+Handler_read_prev)) #获取上述公式参数，执行： show global status like 'Handler_read%';
检查脚本示例1：
#!/bin/bash
# @author:
# @title:检查MySQL主从健康情况
#
# 基本配置
#
MYSQL_USER="root"
MYSQL_PASSWORD="123456"
MYSQL_HOST="localhost"
MYSQL_PORT=3306
#
# 检查MySQL主从健康情况
#
show_slave_status=`mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -A -e 'show slave statusG;' 2>/dev/null`
slave_sql_running_state=`echo "$show_slave_status" |grep Slave_SQL_Running_State |awk -F ': ' '{print $2}'` # Slave上SQL运行状态
# 检查日志同步状态，IO、SQL线程运行状态：2表示健康，!2表示不健康
Sync_status=`echo "$show_slave_status" |grep Running: |awk -F ': ' '{if($2=="Yes"){sum += 1}}; END{print sum}'`
# 如果主从复制状态是不健康的则在屏幕上打印Slave主机SQL运行状态
if [ $ms_status -eq 2 ];then
echo "ms running status: 1"
else
echo "ms running status: 0"
echo "error: $slave_sql_running_state"
fi
脚本示例2：只检查mysql进程是否存在
#!/bin/sh
#date:2015-12-07
#filename:check_mysql.sh
#作者：
#Email:
#version:v1.0
#port=`netstat -tunlp|grep 3306|wc -l`
#process=`ps -ef|grep mysqld|grep -v grep|wc -l`
value=`/application/mysql/bin/mysql -u root -poldboy -e "select version();" >/dev/null 2>&1`
while true
do
if [ $? -ne 0 ]
then
#echo "ERROR! MySQL is not running"
/etc/init.d/mysqld start
else
echo "MySQL is running,now!"
fi
sleep 5
value=`/application/mysql/bin/mysql -u root -poldboy -e "select version();" >/dev/null 2>&1`
if [ $? -ne 0 ]
then
#echo "ERROR! MySQL is not running"
/etc/init.d/mysqld start
else
echo "MySQL is running,now!"
fi
sleep 5
done
2.2、查看SQL语句执行效率
这里要用到Explain语句，Explain可以用来查看 SQL 语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。
语法：explain select … from … [where …]
输出字段说明：
2.3、mysql几种性能测试工具1、id：这是SELECt的查询序列号
2、select_type：select_type就是select的类型，可以有以下几种：
SIMPLE：简单SELECT(不使用UNIOn或子查询等)
PRIMARY：最外面的SELECT
UNIOn：UNIOn中的第二个或后面的SELECT语句
DEPENDENT UNIOn：UNIOn中的第二个或后面的SELECT语句，取决于外面的查询
UNIOn RESULT：UNIOn的结果。
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)
3、table：显示这一行的数据是关于哪张表的
4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。
结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行
6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
8、ref：显示使用哪个列或常数与key一起从表中选择行。
9、rows：显示MySQL认为它执行查询时必须检查的行数。
10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。
Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists:MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each
Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一 个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort:看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来 排序全部行
Using index:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表 的全部的请求列都是同一个索引的部分的时候
Using temporary:看到这个的时候，查询需要优化了。这 里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Using where:使用了WHERe从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index， 这就会发生，或者是查询有问题
1）mysqlslap:模拟并发测试数据库性能。
不足：不能指定生成的数据规模，测试过程不清楚针对十万级还是百万级数据做的测试，感觉不太适合做综合测试，比较适合针对既有数据库，对单个sql进行优化的测试。
eg1： mysqlslap -u root -p --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam --number-of-queries=10 eg2：指定数据库和sql语句: mysqlslap -h localhost -P 123456 --concurrency=100 --iterations=1 --create-schema='mysql' --query='select * from user;' --number-of-queries=10 -u root -p -only-print eg3： mysqlslap -uroot -p123456 --concurrency=100 --iterations=1 --engine=myisam --create-schema='haodingdan112' --query='select * From order_boxing_transit where id = 10' --number-of-queries=1 --debug-info
2）sysbench
下载：http://sourceforge.net/projects/sysbench/
tar zxf sysbench-0.4.12.tar.gz cd sysbench-0.4.12 ./autogen.sh ./configure && make && make install strip /usr/local/bin/sysbench
作用：模拟并发,可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL
优点：可以指定测试数据的规模，可以单独测试读、写的性能，也可以测试读写混合的性能。
不足：测试的时候，由于网络原因，测试的非常慢，但是最终给的结果却很好，并发支持很高，可能不太准确
3）tpcc-mysql
export C_INCLUDE_PATH=/usr/include/mysql export PATH=/usr/bin:$PATH export LD_LIBRARY_PATH=/usr/lib/mysql cd /tmp/tpcc/src make
作用：测试mysql数据库的整体性能
优点：符合tpcc标准，有标准的方法，模拟真实的交易活动，结果比较可靠。
不足：不能单独测试读或者写的性能，对于一些以查询为主或者只写的应用，就没有这么大的意义了。
eg1：单进程加载：
./tpcc_load 192.168.11.172 tpcc10 root pwd 300
eg2：并发加载
./load.sh tpcc300 300
eg3：
./tpcc_start -h192.168.11.172 -d tpcc -u root -p ‘pwd’ -w 10 -c 10 -r 10 -l 60 -i 10 -f /mnt/hgfs/mysql/tpcc100_2013522.txt
参考;http://baike.baidu.com/view/2776305.htm
4） MySQL Benchmark Suite
5）MySQL super-smack
一个强大的广受赞誉的压力测试工具，支持MySQL和PostgreSQL。
参看：http://jeremy.zawodny.com/mysql/super-smack/
6）MyBench: A Home-Grown Solution
MyBench是一种基于Perl语言易于扩展的测试工具。
注：上述来源于网络，未验证，只用于参考，待后续测试后补充。
2.4、MySQL基础巡检#确认当前MySQL版本，只针对mysql 5.6.8以上版本 select version();2.5、Mysql自带的库 1）information_schema数据库：
是MySQL自带的，提供了访问数据库元数据的方式；如数据库名或表名，列的数据类型，或访问权限等，有的也称为“数据词典”和“系统目录”。 information_schema 实际是一个信息数据库，其中保存着关于MySQL服务器所维护的所有其他数据库的信息。information_schema中，有多个只读表。它们实际上是视图，而不是基本表。
#information_schema中78个表
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS |
| APPLICABLE_ROLES |
| CHARACTER_SETS |
| CHECK_ConSTRAINTS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMNS_EXTENSIONS |
| COLUMN_PRIVILEGES |
| COLUMN_STATISTICS |
| ENABLED_ROLES |
| 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_SESSION_TEMP_TABLESPACES |
| 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 |
| ROLE_COLUMN_GRANTS |
| ROLE_ROUTINE_GRANTS |
| ROLE_TABLE_GRANTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| ST_GEOMETRY_COLUMNS |
| ST_SPATIAL_REFERENCE_SYSTEMS |
| ST_UNITS_OF_MEASURE |
| TABLES |
| TABLESPACES |
| TABLESPACES_EXTENSIONS |
| TABLES_EXTENSIONS |
| TABLE_ConSTRAINTS |
| TABLE_CONSTRAINTS_EXTENSIONS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_ATTRIBUTES |
| USER_PRIVILEGES |
| VIEWS |
| VIEW_ROUTINE_USAGE |
| VIEW_TABLE_USAGE |
+---------------------------------------+
78 rows in set (0.00 sec)
#查询的当前数据库中所有的数据库名
mysql> select schema_name from schemata;#SCHEMA_NAME字段保存了所有的数据库名
+--------------------+
| SCHEMA_NAME |
+--------------------+
| mysql |
| information_schema |
| performance_schema |
| sys |
| zabbix |
+--------------------+
#看下tables的表结构
mysql> desc 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 | YES | | NULL | |
| ROW_FORMAT | enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') | YES | | NULL | |
| TABLE_ROWS | bigint unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint unsigned | YES | | NULL | |
| DATA_LENGTH | bigint unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint unsigned | YES | | NULL | |
| DATA_FREE | bigint unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint 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 | YES | | NULL | |
| CREATE_OPTIONS | varchar(256) | YES | | NULL | |
| TABLE_COMMENT | text | YES | | NULL | |
+-----------------+--------------------------------------------------------------------+------+-----+---------+-------+
21 rows in set (0.03 sec)
mysql> select table_name,table_schema from tables;
+------------------------------------------------------+--------------------+
| TABLE_NAME | TABLE_SCHEMA |
+------------------------------------------------------+--------------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS | information_schema |
| APPLICABLE_ROLES | information_schema |
| CHARACTER_SETS | information_schema |
| CHECK_ConSTRAINTS | information_schema |
| COLLATIONS | information_schema |
| COLLATION_CHARACTER_SET_APPLICABILITY | information_schema |
| COLUMNS | information_schema |
| COLUMNS_EXTENSIONS | information_schema |
| COLUMN_PRIVILEGES | information_schema |
| COLUMN_STATISTICS | information_schema |
| ENABLED_ROLES | information_schema |
| ENGINES | information_schema |
| EVENTS | information_schema |
| FILES | information_schema |
#查询某数据库中包含的所有表
mysql> select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'mysql';
+---------------------------+
| TABLE_NAME |
+---------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
相当于show tables from mysql;
#查看某个表中的字段情况：
mysql> show columns from mysql.user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
#单表记录数超过1000W的数据库查询
mysql> select table_schema,table_name,table_rows from information_schema.tables where table_rows >=100000;
+--------------------+----------------------------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |
+--------------------+----------------------------------------------+------------+
| performance_schema | events_errors_summary_by_account_by_error | 187264 |
| performance_schema | events_errors_summary_by_host_by_error | 187264 |
| performance_schema | events_errors_summary_by_thread_by_error | 374528 |
| performance_schema | events_errors_summary_by_user_by_error | 187264 |
| performance_schema | events_waits_summary_by_thread_by_event_name | 141824 |
| performance_schema | memory_summary_by_thread_by_event_name | 115200 |
| performance_schema | session_account_connect_attrs | 131072 |
| performance_schema | session_connect_attrs | 131072 |
| performance_schema | variables_by_thread | 159488 |
| zabbix | history | 7517515 |
| zabbix | history_uint | 5756152 |
| zabbix | trends | 6700891 |
| zabbix | trends_uint | 5228958 |
+--------------------+----------------------------------------------+------------+
13 rows in set (0.00 sec)
##查看数据库所有索引
SELECt * FROM mysql.`innodb_index_stats` a WHERe a.`database_name` = 'zabbix';
#查看某一表索引
mysql> SELECt * FROM mysql.`innodb_index_stats` a WHERe a.`database_name` = 'zabbix' and a.table_name like '%events%';
+---------------+------------+------------+---------------------+--------------+------------+-------------+--------------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+--------------------------------------+
| zabbix | events | PRIMARY | 2022-01-28 03:03:27 | n_diff_pfx01 | 54917 | 20 | eventid
#数据库表空间大于1T检查
SELECT table_schema as 'Database',table_name,
CONCAT(ROUND(data_length/(1024*1024*1024),6),' G') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size',
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total'
FROM information_schema.TABLES;
+--------------------+------------------------------------------------------+------------+------------+------------+
| Database | TABLE_NAME | Data Size | Index Size | Total |
+--------------------+------------------------------------------------------+------------+------------+------------+
| information_schema | ADMINISTRABLE_ROLE_AUTHORIZATIONS | 0.000000 G | 0.000000 G | 0.000000 G |
| information_schema | APPLICABLE_ROLES | 0.000000 G | 0.000000 G | 0.000000 G |
| information_schema | CHARACTER_SETS | 0.000000 G | 0.000000 G | 0.000000 G |
information_schema数据库表说明:
2）PERFORMANCE_SCHEMA库SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。不管在哪个数据库中的表，在这里都会有一条记录对应，如果你在一个数据库中创建了一个表，相应地在这个表里，也会有一条记录对应你创建的那个表。
COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
USER_PRIVILEGES（用户权限）表：给出了关于全局权限的信息。该信息源自mysql.user授权表。是非标准表。
SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。
TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。
COLLATIONS表：提供了关于各字符集的对照信息。
COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。
KEY_COLUMN_USAGE表：描述了具有约束的键列。
ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。
TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表.
MySQL 5.5开始新增了该数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA表的。MySQL5.5默认是关闭的，需要手动开启，
在配置文件里添加：
[mysqld]
performance_schema=ON #该参数为只读参数，需要在实例启动之前设置才生效
主要功能：
①提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息。提供进程等待的详细信息，包括锁、互斥变量、文件信息；
②保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；performance_schema通过监视server的事件来实现监视server内部运行情况， “事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里?一般来说，事件可以是函数调用、 *** 作系统的等待、SQL语句执行的阶段(如sql语句执行过程中的parsing 或 sorting阶段)或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。performance_schema中的事件与写入二进制日志中的事件(描述数据修改的events)、事件计划调度程序(这是一种存储程序)的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。
③对于新增和删除监控事件点都非常容易，并可以改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。
通过该库得到数据库运行的统计信息，更好分析定位问题和完善监控信息。
当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象(如mutex或file)相关联的活动。
performance_schema存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能(如复制或事件计划程序)不同。
收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECt语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录(如动态修改performance_schema的setup_*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集)。
performance_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失(包括配置表在内的整个performance_schema下的所有数据)。
MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异。
performance_schema实现机制遵循以下设计目标：
启用performance_schema不会导致server的行为发生变化。例如，它不会改变线程调度机制，不会导致查询执行计划(如EXPLAIN)发生变化。
启用performance_schema之后，server会持续不间断地监测，开销很小。不会导致server不可用。
在该实现机制中没有增加新的关键字或语句，解析器不会变化。
即使performance_schema的监测机制在内部对某事件执行监测失败，也不会影响server正常运行。
如果在开始收集事件数据时碰到有其他线程正在针对这些事件信息进行查询，那么查询会优先执行事件数据的收集，因为事件数据的收集是一个持续不断的过程，而检索(查询)这些事件数据仅仅只是在需要查看的时候才进行检索。也可能某些事件数据永远都不会去检索。
需要很容易地添加新的instruments监测点。
instruments(事件采集项)代码版本化：如果instruments的代码发生了变更，旧的instruments代码还可以继续工作。
#查看是否开启：
mysql>show variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.04 sec)
#值为ON表示performance_schema已初始化成功且可以使用了
mysql> select version(); #从MySQL5.6开始，performance_schema引擎默认打开
+-----------+
| version() |
+-----------+
| 8.0.21 |
+-----------+
1 row in set (0.00 sec)
#检查performance_schema存储引擎支持情况
mysql> select engine,support from information_schema.engines where engine='PERFORMANCE_SCHEMA';
+--------------------+---------+
| engine | support |
+--------------------+---------+
| PERFORMANCE_SCHEMA | YES |
+--------------------+---------+
1 row in set (0.00 sec)
#查询哪些表使用performance_schema引擎，
mysql> select table_name from information_schema.tables where engine='performance_schema';
#查看数据库系统中打开了文件的对象：包括ibdata文件，redo文件，binlog文件，用户的表文件等，open_count显示当前文件打开的数目，如果重来没有打开过，不会出现在表中。
mysql> select * from file_instances limit 2,5;
+--------------------------------+---------------------------------------+------------+
| FILE_NAME | EVENT_NAME | OPEN_COUNT |
+--------------------------------+---------------------------------------+------------+
| /video/mysql/ibdata1 | wait/io/file/innodb/innodb_data_file | 3 |
| /video/mysql/#ib_16384_0.dblwr | wait/io/file/innodb/innodb_dblwr_file | 2 |
| /video/mysql/#ib_16384_1.dblwr | wait/io/file/innodb/innodb_dblwr_file | 2 |
| /video/mysql/ib_logfile0 | wait/io/file/innodb/innodb_log_file | 2 |
| /video/mysql/ib_logfile1 | wait/io/file/innodb/innodb_log_file | 2 |
+--------------------------------+---------------------------------------+------------+
5 rows in set (0.00 sec)
#查看哪个SQL执行最多
mysql> 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 1G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: BEGIN
count_star: 228311169 #执行次数
sum_rows_sent: 0
sum_rows_examined: 0
first_seen: 2021-03-16 10:43:00.830886
last_seen: 2022-01-27 22:56:10.547003
1 row in set (0.04 sec)
#哪个SQL平均响应时间最多
mysql> 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 1G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: SELECt `clock` , `ns` , VALUE FROM HISTORY WHERe `itemid` = ? AND `clock` > ? AND `clock` <= ?
count_star: 576
avg_timer_wait: 250369909000 #该SQL平均响应时间，皮秒（1000000000000皮秒=1秒）
sum_rows_sent: 1564
sum_rows_examined: 1564
first_seen: 2021-03-18 02:10:54.662146
last_seen: 2022-01-24 17:04:28.496527
1 row in set (0.00 sec)
#哪个SQL扫描的行数最多
mysql> 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 sum_rows_examined desc limit 1G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: SELECt DISTINCTROW `d` . `triggerid_down` , `d` . `triggerid_up` FROM `trigger_depends` `d` , TRIGGERS `t` , HOSTS `h` , `items` `i` , `functions` `f` WHERe `t` . `triggerid` = `d` . `triggerid_down` AND `t` . `flags` != ? AND `h` . `hostid` = `i` . `hostid` AND `i` . `itemid` = `f` . `itemid` AND `f` . `triggerid` = `d` . `triggerid_down` AND `h` . `status` IN (...)
count_star: 456338
avg_timer_wait: 14573128000
sum_rows_sent: 209333096
sum_rows_examined: 4484007395 ##主要关注
first_seen: 2021-03-16 10:43:14.363971
last_seen: 2022-01-27 23:01:58.261088
1 row in set (0.01 sec)
#哪个SQL使用的临时表最多
mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,sum_created_tmp_tables,sum_created_tmp_disk_tables,first_seen,last_seen from events_statements_summary_by_digest order by sum_created_tmp_tables desc limit 1G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: SELECt DISTINCTROW `g` . `graphid` , `g` . `name` , `g` . `width` , `g` . `height` , `g` . `yaxismin` , `g` . `yaxismax` , `g` . `show_work_period` , `g` . `show_triggers` , `g` . `graphtype` , `g` . `show_legend` , `g` . `show_3d` , `g` . `percent_left` , `g` . `percent_right` , `g` . `ymin_type` , `g` . `ymin_itemid` , `g` . `ymax_type` , `g` . `ymax_itemid` , `g` . `discover` FROM `graphs` `g` , `graphs_items` `gi` , `items` `i` , `item_discovery` `id` WHERe `g` . `graphid` = `gi` . `graphid` AND `gi` . `itemid` = `i` . `itemid` AND `i` . `itemid` = `id` . `itemid` AND `id` . `parent_itemid` = ?
count_star: 7359187
avg_timer_wait: 443879000
sum_rows_sent: 14083781
sum_rows_examined: 306592148
sum_created_tmp_tables: 7359204 ##主要关注
sum_created_tmp_disk_tables: 0
first_seen: 2021-03-16 10:43:02.510002
last_seen: 2022-01-27 23:05:19.469465
1 row in set (0.00 sec)
#哪个SQL返回的结果集最多
mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,sum_created_tmp_tables,first_seen,last_seen from events_statements_summary_by_digest order by sum_rows_sent desc limit 1G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: SELECt `i` . `itemid` , `i` . `hostid` , `i` . `templateid` FROM `items` `i` INNER JOIN HOSTS `h` ON `i` . `hostid` = `h` . `hostid` WHERe `h` . `status` = ?
count_star: 456344
avg_timer_wait: 5604268000
sum_rows_sent: 1706726560 #主要关注
sum_rows_examined: 1770168229
sum_created_tmp_tables: 0
first_seen: 2021-03-16 10:43:14.292694
last_seen: 2022-01-27 23:07:58.857928
1 row in set (0.00 sec)
#哪个SQL排序数最多，其中IO(SUM_ROWS_EXAMINED)，CPU消耗(SUM_SORT_ROWS)，网络带宽(SUM_ROWS_SENT)
mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,sum_sort_rows,first_seen,last_seen fromevents_statements_summary_by_digest order by sum_sort_rows desc limit 1G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: SELECt `pp` . `item_preprocid` , `pp` . `itemid` , `pp` . `type` , `pp` . `params` , `pp` . `step` , `i` . `hostid` , `pp` . `error_handler` , `pp` . `error_handler_params` , `i` . `type` , `i` . `key_` , `h` . `proxy_hostid` FROM `item_preproc` `pp` , `items` `i` , HOSTS `h` WHERe `pp` . `itemid` = `i` . `itemid` AND `i` . `hostid` = `h` . `hostid` AND ( `h` . `proxy_hostid` IS NULL OR `i` . `type` IN (...) ) AND `h` . `status` IN (...) AND `i` . `flags` != ? ORDER BY `pp` . `itemid`
count_star: 456346
avg_timer_wait: 14210098000
sum_rows_sent: 1069260615
sum_rows_examined: 3582380844
sum_sort_rows: 1069260615 #主要关注
first_seen: 2021-03-16 10:43:14.313793
last_seen: 2022-01-27 23:09:59.091879
1 row in set (0.00 sec)
#哪个表、文件逻辑IO最多（热数据）：file_summary_by_instance表，意味着这个表经常需要访问磁盘IO
mysql> 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 2G
*************************** 1. row ***************************
file_name: /video/mysql/#ib_16384_0.dblwr
event_name: wait/io/file/innodb/innodb_dblwr_file
count_read: 1
sum_number_of_bytes_read: 1179648
count_write: 5546947
sum_number_of_bytes_write: 523535138816
*************************** 2. row ***************************
file_name: /video/mysql/ib_logfile1
event_name: wait/io/file/innodb/innodb_log_file
count_read: 0
sum_number_of_bytes_read: 0
count_write: 180237316
sum_number_of_bytes_write: 266296773120
2 rows in set (0.00 sec)
#查看哪个表逻辑IO最多，亦即最“热”的表
mysql> SELECt
-> object_name,
-> COUNT_READ,
-> COUNT_WRITE,
-> COUNT_FETCH,
-> SUM_TIMER_WAIT
-> FROM table_io_waits_summary_by_table
-> ORDER BY sum_timer_wait DESC limit 1G
*************************** 1. row ***************************
object_name: history #关注
COUNT_READ: 384830011
COUNT_WRITE: 750076101
COUNT_FETCH: 384830011
SUM_TIMER_WAIT: 29159481894425960
1 row in set (0.01 sec)
#获得系统运行到现在，哪个表的具体哪个索引(包括主键索引，二级索引)使用最多
mysql> 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 1G
*************************** 1. row ***************************
object_name: history
index_name: NULL
count_fetch: 0
count_insert: 374268229
count_update: 0
count_delete: 0
1 row in set (0.00 sec)
+-------------+------------+-------------+--------------+--------------+--------------+
| object_name | index_name | count_fetch | count_insert | count_update | count_delete |
+-------------+------------+-------------+--------------+--------------+--------------+
| history | NULL | 0 | 374268557 | 0 | 0 |
+-------------+------------+-------------+--------------+--------------+--------------+
#获取哪个索引没有使用过
mysql> 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;
+--------------------+------------------------------------------------------+----------------------------------+
| object_schema | object_name | index_name |
+--------------------+------------------------------------------------------+----------------------------------+
| performance_schema | accounts | ACCOUNT |
| performance_schema | cond_instances | PRIMARY |
| performance_schema | cond_instances | NAME |
| performance_schema | data_lock_waits | BLOCKING_THREAD_ID |
| performance_schema | data_lock_waits | REQUESTING_ENGINE_LOCK_ID |
| performance_schema | data_lock_waits | BLOCKING_ENGINE_LOCK_ID |
| performance_schema | data_lock_waits | REQUESTING_ENGINE_TRANSACTION_ID |
| performance_schema | data_lock_waits | BLOCKING_ENGINE_TRANSACTION_ID |
| performance_schema | data_lock_waits | REQUESTING_THREAD_ID |
| performance_schema | data_locks | OBJECT_SCHEMA |
| performance_schema | data_locks | THREAD_ID |
| performance_schema | data_locks | ENGINE_TRANSACTION_ID |
| performance_schema | data_locks | PRIMARY |
| performance_schema | events_errors_summary_by_account_by_error | ACCOUNT |
| performance_schema | events_errors_summary_by_host_by_error | HOST |
| performance_schema | events_errors_summary_by_thread_by_error | THREAD_ID |
| performance_schema | events_errors_summary_by_user_by_error | USER |
| zabbix | task_result | task_result_1 |
| zabbix | task_result | PRIMARY |
| zabbix | timeperiods | PRIMARY |
| zabbix | trigger_depends | PRIMARY |
| zabbix | trigger_depends | trigger_depends_2 |
| zabbix | trigger_tag | trigger_tag_1 |
| zabbix | trigger_tag | PRIMARY |
| zabbix | triggers | triggers_1 |
| zabbix | triggers | triggers_2 |
| zabbix | triggers | triggers_3 |
| zabbix | users_groups | users_groups_2 |
| zabbix | users_groups | users_groups_1 |
| zabbix | users_groups | PRIMARY |
| zabbix | widget_field | widget_field_5 |
| zabbix | widget_field | PRIMARY |
| zabbix | widget_field | widget_field_4 |
| zabbix | widget_field | widget_field_6 |
| zabbix | widget_field | widget_field_3 |
| zabbix | widget_field | widget_field_2 |
| zabbix | widget_field | widget_field_1 |
+--------------------+------------------------------------------------------+----------------------------------+
331 rows in set (0.00 sec)
#查询哪个等待事件消耗的时间最多
mysql> 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;
+-------------------------+------------+---------------------+----------------+
| event_name | count_star | sum_timer_wait | avg_timer_wait |
+-------------------------+------------+---------------------+----------------+
| wait/io/file/sql/binlog | 195515509 | 1538632653524464190 | 7869619450 |
+-------------------------+------------+---------------------+----------------+
1 row in set (0.02 sec)
#InnoDB监控
#打开标准的innodb监控：
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
#打开innodb的锁监控：
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
#打开innodb表空间监控：
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;
#打开innodb表监控：
CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
#剖析某条SQL的执行情况，该SQL在执行各个阶段的时间消耗，可查看events_statements_xxx表和events_stages_xxx表，包括statement信息，stage信息和wait信息
#eg：比如分析包含count(*)的某条SQL语句
mysql> SELECt
-> EVENT_ID,
-> sql_text
-> FROM events_statements_history
-> WHERe sql_text LIKE '%count(*)%';
+----------+-------------------------------------------------------------------------------------------+
| EVENT_ID | sql_text |
+----------+-------------------------------------------------------------------------------------------+
| 246 | select count(*) from zabbix.task |
| 248 | select count(*) from zabbix.items |
| 242 | SELECt
EVENT_ID,
sql_text
FROM events_statements_history
WHERe sql_text LIKE '%count(*)%' |
| 243 | select count(*) from zabbix.tasks |
+----------+-------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
#查看每个阶段的时间消耗，时间单位以皮秒表示，注意默认情况下events_stages_history表中只为每个连接记录了最近10条记录，为了确保获取所有记录，需要访问events_stages_history_long表
mysql> SELECt
-> event_id,
-> EVENT_NAME,
-> SOURCE,
-> TIMER_END - TIMER_START
-> FROM events_stages_history_long
-> WHERe NESTING_EVENT_ID = 248;
#查看某个阶段的锁等待情况；针对每个stage可能出现的锁等待，一个stage会对应一个或多个wait，events_waits_history_long这个表容易爆满[默认阀值10000]。由于select count(*)需要IO(逻辑IO或者物理IO)，所以在stage/sql/Sending data阶段会有io等待的统计
mysql> SELECT
-> event_id,
-> event_name,
-> source,
-> timer_wait,
-> object_name,
-> index_name,
-> operation,
-> nesting_event_id
-> FROM events_waits_history_long
-> WHERe nesting_event_id = 248;
Empty set (0.00 sec)
更多详情参考：https://www.cnblogs.com/cchust/p/5061131.html
3）sys 库sys schem在5.7.x版本默认安装，sys schema是一组对象(包括相关的视图、存储过程和函数)，可以方便地访问performance_schema收集的数据。同时检索的数据可读性也更高(例如：performance_schema中的时间单位是皮秒，经过sys schema查询时会转换为可读的us,ms,s,min,hour,day等单位)。sys中的数据实际上主要是从performance_schema、information_schema中获取。通过sys库不仅可以完成MySQL信息的收集，还可以用来监控和排查问题。
sys库主要内容：
1)这个库是通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据
2)存储过程可以可以执行一些性能方面的配置，也可以得到一些性能诊断报告内容
3)存储函数可以查询一些性能信息
#查看用户、连接情况
查看每个客户端IP过来的连接消耗资源情况。
mysql> select host,current_connections,total_connections,unique_users,current_memory,total_memory_allocated from sys.host_summary;
+-----------+---------------------+-------------------+--------------+----------------+------------------------+
| host | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------+---------------------+-------------------+--------------+----------------+------------------------+
| localhost | 20 | 458106 | 2 | 20.99 GiB | 46.42 TiB |
+-----------+---------------------+-------------------+--------------+----------------+------------------------+
1 row in set (0.01 sec)
#查看每个用户消耗资源情况
mysql> select user,table_scans,file_ios,file_io_latency,current_connections,current_memory from sys.user_summary;
+------------+-------------+-----------+-----------------+---------------------+----------------+
| user | table_scans | file_ios | file_io_latency | current_connections | current_memory |
+------------+-------------+-----------+-----------------+---------------------+----------------+
| zabbix | 12575536 | 195754143 | 17.84 d | 19 | 10.49 GiB |
| root | 32 | 84 | 335.81 ms | 1 | 2.48 MiB |
| background | 0 | 442868581 | 14.88 d | 53 | 233.65 MiB |
+------------+-------------+-----------+-----------------+---------------------+----------------+
3 rows in set (0.01 sec)
#查看当前正在执行的SQL
mysql> select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session;
+---------+-------+---------------------+--------+---------+-------------------------------------------------------------------+-------------------------------------------------------------------+------+--------------+
| conn_id | pid | user | db | command | current_statement | last_statement | time | lock_latency |
+---------+-------+---------------------+--------+---------+-------------------------------------------------------------------+-------------------------------------------------------------------+------+--------------+
| 28 | 17167 | zabbix@localhost | zabbix | Sleep | select itemid from items where ... and flags<>2 and hostid=10084 | select itemid from items where ... and flags<>2 and hostid=10084 | 203 | 46.00 us |
| 15 | 17168 | zabbix@localhost | zabbix | Sleep | select itemid from items where ... and flags<>2 and hostid=10084 | select itemid from items where ... and flags<>2 and hostid=10084 | 164 | 52.00 us |
| 24 | 17147 | zabbix@localhost | zabbix | Sleep | select co.corr_operationid,co. ... o.correlationid and c.status=0 | select co.corr_operationid,co. ... o.correlationid and c.status=0 | 58 | 44.00 us |
| 30 | 17169 | zabbix@localhost | zabbix | Sleep | select itemid from items where ... and flags<>2 and hostid=10084 | select itemid from items where ... and flags<>2 and hostid=10084 | 44 | 42.00 us |
| 26 | 17166 | zabbix@localhost | zabbix | Sleep | commit | commit | 40 | 0 ps |
| 10 | 17149 | zabbix@localhost | zabbix | Sleep | commit | commit | 13 | 0 ps |
| 16 | 17151 | zabbix@localhost | zabbix | Sleep | select dcheckid,type,key_,snmp ... re druleid=3 order by dcheckid | select dcheckid,type,key_,snmp ... re druleid=3 order by dcheckid | 6 | 48.00 us |
| 22 | 17170 | zabbix@localhost | zabbix | Sleep | select itemid from items where ... and flags<>2 and hostid=10084 | select itemid from items where ... and flags<>2 and hostid=10084 | 5 | 60.00 us |
| 21 | 17154 | zabbix@localhost | zabbix | Sleep | commit | commit | 5 | 0 ps |
| 14 | 17165 | zabbix@localhost | zabbix | Sleep | commit | commit | 4 | 0 ps |
| 23 | 17153 | zabbix@localhost | zabbix | Sleep | commit | commit | 3 | 0 ps |
| 8 | 17159 | zabbix@localhost | zabbix | Sleep | select taskid,type,clock,ttl f ... tatus in (1,2) order by taskid | select taskid,type,clock,ttl f ... tatus in (1,2) order by taskid | 3 | 100.00 us |
| 20 | 17156 | zabbix@localhost | zabbix | Sleep | select escalationid,actionid,t ... ,triggerid,itemid,escalationid | select escalationid,actionid,t ... ,triggerid,itemid,escalationid | 3 | 47.00 us |
| 9 | 17150 | zabbix@localhost | zabbix | Sleep | select min(t.nextcheck) from h ... tus=0 or h.maintenance_type=0) | select min(t.nextcheck) from h ... tus=0 or h.maintenance_type=0) | 2 | 62.00 us |
| 11 | 17155 | zabbix@localhost | zabbix | Sleep | commit | commit | 2 | 0 ps |
| 458094 | 8986 | root@localhost | sys | Query | SET @sys.statement_truncate_le ... ('statement_truncate_len', 64) | NULL | 1 | 996.00 us |
| 13 | 17182 | zabbix@localhost | zabbix | Sleep | select h.hostid,h.host,h.name, ... tid and hd.parent_itemid=31891 | select h.hostid,h.host,h.name, ... tid and hd.parent_itemid=31891 | 1 | 59.00 us |
| 17 | 17181 | zabbix@localhost | zabbix | Sleep | select h.hostid,h.host,h.name, ... tid and hd.parent_itemid=31832 | select h.hostid,h.host,h.name, ... tid and hd.parent_itemid=31832 | 1 | 81.00 us |
| 18 | 17152 | zabbix@localhost | zabbix | Sleep | commit | commit | 1 | 0 ps |
| 19 | 17183 | zabbix@localhost | zabbix | Sleep | commit | commit | 1 | 0 ps |
| 5 | NULL | sql/event_scheduler | NULL | Sleep | NULL | NULL | NULL | NULL |
+---------+-------+---------------------+--------+---------+-------------------------------------------------------------------+-------------------------------------------------------------------+------+--------------+
21 rows in set (0.08 sec)
#查看发生IO请求前5名的文件
mysql> select * from sys.io_global_by_file_by_bytes order by total limit 5;
#查看总共分配了多少内存
mysql> select * from sys.memory_global_total;
mysql> select * from sys.memory_global_by_current_bytes;
#每个库（database）占用多少buffer pool;pages是指在buffer pool中的page数量；pages_old指在LUR 列表中处于后37%位置的page。当出现buffer page不够用时，就会征用这些page所占的空间。37%是默认位置，具体可以自定义。
mysql> select * from sys.innodb_buffer_stats_by_schema order by allocated desc;
+---------------+-----------+------------+--------+--------------+-----------+-------------+
| object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+-----------+------------+--------+--------------+-----------+-------------+
| mysql | 5.95 MiB | 3.33 MiB | 381 | 185 | 275 | 1380 |
| zabbix | 2.25 GiB | 1.74 GiB | 147654 | 104738 | 55439 | 401231 |
| sys | 16.00 KiB | 338 bytes | 1 | 0 | 1 | 6 |
+---------------+-----------+------------+--------+--------------+-----------+-------------+
3 rows in set (2.58 sec)
#统计每张表具体在InnoDB中具体的情况，比如占多少页
mysql> select * from sys.innodb_buffer_stats_by_table;
+---------------+------------------------------+------------+-------------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------------------------+------------+-------------+-------+--------------+-----------+-------------+
| zabbix | history | 873.78 MiB | 629.83 MiB | 55922 | 36446 | 25499 | 9101216 |
| zabbix | history_uint | 598.88 MiB | 449.53 MiB | 38328 | 27248 | 16863 | 6572995 |
| zabbix | trends | 457.16 MiB | 388.25 MiB | 29258 | 22582 | 7277 | 7040277 |
| zabbix | trends_uint | 337.12 MiB | 291.23 MiB | 21576 | 17373 | 4991 | 5291601 |
| zabbix | events | 16.50 MiB | 11.24 MiB | 1056 | 582 | 263 | 66166 |
| zabbix | history_str | 5.12 MiB | 2.52 MiB | 328 | 40 | 86 | 29315 |
#查询每个连接分配了多少内存
mysql> SELECt b.USER, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated, current_statement FROM sys.memory_by_thread_by_current_bytes a, sys.session b WHERe a.thread_id = b.thd_id;
#查看表自增字段最大值和当前值，有时候做数据增长的监控，可以作为参考
mysql> select * from sys.schema_auto_increment_columns;
#MySQL索引使用情况统计
mysql> select * from sys.schema_auto_increment_columns;
#MySQL中有哪些冗余索引和无用索引;若库中展示没有冗余索引，则没有数据；当有联合索引idx_abc(a,b,c)和idx_a(a)，那么idx_a就算冗余索引了。
mysql> select * from sys.schema_redundant_indexes;
#查看INNODB 锁信息
mysql> select * from sys.innodb_lock_waits;
#查看库级别的锁信息，这个需要先打开MDL锁的监控：
#打开MDL锁监控
update performance_schema.setup_instruments set enabled='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl';
mysql> select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+------------+------------+---------------+
| NAME | ENABLED | TIMED | PROPERTIES | VOLATILITY | documentATION |
+----------------------------+---------+-------+------------+------------+---------------+
| wait/lock/metadata/sql/mdl | YES | YES | | 0 | NULL |
+----------------------------+---------+-------+------------+------------+---------------+
1 row in set (0.00 sec)
#MySQL内部有多个线程在运行，线程类型及数量
mysql> select user,count(*) from sys.`processlist` group by user;
#查看MySQL自增id的使用情况
mysql> SELECt
-> table_schema,
-> table_name,
-> ENGINE,
-> Auto_increment
-> FROM
-> information_schema.TABLES
-> WHERe
-> TABLE_SCHEMA NOT IN ( "INFORMATION_SCHEMA", "PERFORMANCE_SCHEMA", "MYSQL", "SYS" );
+--------------------+------------------------------------------------------+--------------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | AUTO_INCREMENT |
+--------------------+------------------------------------------------------+--------------------+----------------+
| information_schema | ADMINISTRABLE_ROLE_AUTHORIZATIONS | NULL | NULL |
2.6、数据引擎
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) #查看当前数据库默认隔离级别 mysql> show global variables like '%isolation%'; +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | +-----------------------+----------------+ 1 row in set (0.00 sec) mysql> select @@global.transaction_isolation; +--------------------------------+ | @@global.transaction_isolation | +--------------------------------+ | READ-COMMITTED | +--------------------------------+ 1 row in set (0.00 sec) #查看默认自动提交事务是否开启 mysql> show global variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> select @@global.autocommit; +---------------------+ | @@global.autocommit | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec)2.7、基础巡检
#数据文件存放路径 mysql> show variables like 'datadir'; +---------------+---------------+ | Variable_name | Value | +---------------+---------------+ | datadir | /video/mysql/ | +---------------+---------------+ 1 row in set (0.00 sec) #告警 mysql错误日志存放路径 mysql> show variables where variable_name = 'log_error'; +---------------+-----------------------------+ | Variable_name | Value | +---------------+-----------------------------+ | log_error | /video/mysql/log/mysqld.log | +---------------+-----------------------------+ 1 row in set (0.00 sec) #慢查询日志 mysql> show variables WHERe variable_name = 'slow_query_log_file'; +---------------------+---------------------------------------------+ | Variable_name | Value | +---------------------+---------------------------------------------+ | slow_query_log_file | /video/mysql/yangguangcaigoutest02-slow.log | +---------------------+---------------------------------------------+ 1 row in set (0.00 sec) #查询写入慢查询日志的时间阈值 mysql> show variables WHERe variable_name = 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 2.000000 | +-----------------+----------+ 1 row in set (0.00 sec) #查询哪个sql消耗资源情况 mysql> select Id,User,Host,db,Time,Info from information_schema.`PROCESSLIST` where info is not null; +--------+------+-----------+------+------+-----------------------------------------------------------------------------------------------+ | Id | User | Host | db | Time | Info | +--------+------+-----------+------+------+-----------------------------------------------------------------------------------------------+ | 458094 | root | localhost | sys | 0 | select Id,User,Host,db,Time,Info from information_schema.`PROCESSLIST` where info is not null | +--------+------+-----------+------+------+-----------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) #查询是否锁表 show OPEN TABLES where In_use > 0; #查询所有数据的大小 mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables; +-----------+ | data | +-----------+ | 1825.38MB | +-----------+ 1 row in set (0.00 sec) # 检查mysql数据库各个表空间的大小(数据空间和索引空间以及总和) select TABLE_NAME, concat(truncate(data_length/1024/1024/1024, 2), 'GB') as data_size, concat(truncate(index_length /1024/1024/1024, 2), 'GB') as index_size, truncate(data_length/1024/1024/1024, 2)+ truncate(index_length /1024/1024/1024, 2) from information_schema.tables where TABLE_SCHEMA = 'smapuum' order by data_length desc; #查看锁情况 mysql> show status like 'innodb_row_lock_%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 124 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 15 | | Innodb_row_lock_waits | 386 | +-------------------------------+-------+ 5 rows in set (0.04 sec) #nnodb_row_lock_current_waits : 当前等待锁的数量 #Innodb_row_lock_time : 系统启动到现在，锁定的总时间长度 #Innodb_row_lock_time_avg : 每次平均锁定的时间 #Innodb_row_lock_time_max : 最长一次锁定时间 #Innodb_row_lock_waits : 系统启动到现在总共锁定的次数 #查询是否锁表 mysql> show OPEN TABLES where In_use > 0; #锁等待的对应关系 mysql> mysql> select * from sys.innodb_lock_waitsG #当前运行的所有事务 select * from information_schema.innodb_trxG
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）