对于当前数据库的监控方式有很多，分为数据库自带、商用、开源三大类，每一种都有各自的特色；而对于MySQL数据库，由于其有很高的社区活跃度，监控方式更是多种多样，不管哪种监控方式最核心的就是监控数据，获取得到全面的监控数据后就是灵活的展示部分。 那我们今天就介绍一下完全采用MySQL自有方式采集获取监控数据，在单体下达到最快速、方便、损耗最小。 本次文章完全使用MySQL自带的show命令实现获取，从connects、buffercache、lock、SQL Slow、statement、Database throughputs、serverconfig这7大方面全面获取监控数据。
Connects（连接数）
最大使用连接数：show status like 'Max_used_connections';
mysql> show status like 'Max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 38 | +----------------------+-------+当前打开的连接数：show status like 'Threads_connected';
mysql> show status like 'Threads_connected'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 20 | +-------------------+-------+bufferCache（缓存）
未从缓冲池读取的次数：show status like 'Innodb_buffer_pool_reads';
mysql> show status like 'Innodb_buffer_pool_reads'; +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | Innodb_buffer_pool_reads | 152992 | +--------------------------+--------+从缓冲池读取的次数：show status like 'Innodb_buffer_pool_read_requests';
mysql> show status like 'Innodb_buffer_pool_read_requests'; +----------------------------------+------------+ | Variable_name | Value | +----------------------------------+------------+ | Innodb_buffer_pool_read_requests | 8004086499 | +----------------------------------+------------+缓冲池的总页数：show status like 'Innodb_buffer_pool_pages_total';
mysql> show status like 'Innodb_buffer_pool_pages_total'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | Innodb_buffer_pool_pages_total | 49152 | +--------------------------------+-------+缓冲池空闲的页数：show status like 'Innodb_buffer_pool_pages_free';
mysql> show status like 'Innodb_buffer_pool_pages_free'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_buffer_pool_pages_free | 1024 | +-------------------------------+-------+缓存命中率计算公式： (1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%
缓存池使用率公式： ((Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free）/Innodb_buffer_pool_pages_total)*100%
lock（锁）
锁等待个数：show status like 'Innodb_row_lock_waits';
mysql> show status like 'Innodb_row_lock_waits'; +-----------------------+--------+ | Variable_name | Value | +-----------------------+--------+ | Innodb_row_lock_waits | 187890 | +-----------------------+--------+平均每次锁等待时间：show status like 'Innodb_row_lock_time_avg';
mysql> show status like 'Innodb_row_lock_time_avg'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Innodb_row_lock_time_avg | 5 | +--------------------------+-------+查看是否存在表锁：show open TABLES where in_use>0;
mysql> show open TABLES where in_use>0; Empty set (0.00 sec)有数据代表存在锁表，空为无表锁! 锁等待统计的数量为累加数据，每次获取得时候可以跟之前得数据进行相减，得到当前统计得数据
SQL Slow
慢 SQL 指的是MySQL慢查询，具体指运行时间超过long_query_time值的SQL。我们常听MySQL中有二进制日志-binlog、中继日志-relaylog、重做回滚日志-redolog、undolog 等。针对慢查询，还有一种慢查询日志slowlog，用来记录在MySQL中响应时间超过阀值的语句。慢SQL对实际生产业务影响是致命的，所以测试人员在性能测试过程中，对数据库SQL语句执行情况实施监控，给开发提供准确的性能优化意见显得尤为重要。那怎么使用Mysql数据库提供的慢查询日志来监控SQL语句执行情况，找到消耗较高的SQL语句，以下详细说明一下慢查询日志的使用步骤：
查看sql-slow状态：show variables like 'slow_query_log'; 修改sql-slow状态：set global slow_query_log=1;
mysql> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | ON | +----------------+-------+ON为开启状态，如果为OFF
查看mysql-slow阈值：show variables like 'long_query_time'; 修改mysql-slow阈值：set global long_query_time=0.1;
mysql> show variables like 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+这个long_query_time是用来定义慢于多少秒的才算 “慢查询”，注意单位是秒，我通过执行sql指令set long_query_time=1来设置了long_query_time的值为1, 也就是执行时间超过1秒的都算慢查询.
查看mysql慢sql目录：'show variables like 'slow_query_log_file';'
mysql> show variables like 'slow_query_log_file'; +---------------------+--------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------+ | slow_query_log_file | /home/mysql/log/mysql/slow_query.log | +---------------------+--------------------------------------+ [root@localhost ~]# cat /home/mysql/log/mysql/slow_query.log # Time: 220920 16:16:40 # User@Host: root[root] @ localhost [127.0.0.1] # Thread_id: 45 Schema: nova QC_hit: No # Query_time: 2.000211 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 # Rows_affected: 0 Bytes_sent: 65 SET timestamp=1663661800; select sleep(2);格式化慢sql日志：mysqldumpslow -s at -t 1 /home/mysql/log/mysql/slow_query.log
[root@localhost ~]# mysqldumpslow -s at -t 1 /home/mysql/log/mysql/slow_query.log Reading mysql slow query log from slow.log Count: 4 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts Time: N N:N:N # User@Host: root[root] @ localhost [N.N.N.N] # Thread_id: N Schema: QC_hit: No # Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N # Rows_affected: N Bytes_sent: N SET timestamp=N; select sleep(N)结果详解： Count：表示该类型的语句执行次数； Time：表示该类型的语句执行的平均时间（总计时间）； Lock：锁时间0s； Rows：单次返回的结果数；[/collapse]
注：此语句通过 jdbc 执行不了，属于命令行执行。意思为：显示出耗时最长的 10 个 SQL 语句执行信息，10 可以修改为 TOP 个数。显示的信息为：执行次数、平均执行时间、SQL语句 备注：当mysqldumpslow命令执行失败时，将慢日志同步到本地进行格式化处理。
如何避免SQL慢查询
使用子查询 SELECT FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);子查询在 MySQL5.5 版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表 t2，当外表的数据很大时，查询速度会非常慢。在 MariaDB10/MySQL5.6 版本里，采用 join 关联方式对其进行了优化，这条 SQL 会自动转换为 SELECT t1. FROM t1 JOIN t2 ON t1.id = t2.id;但请注意的是：优化只针对 SELECT 有效，对 UPDATE/DELETE 子 查询无效， 生产环境尽量应避免使用子查询。
避免函数索引 SELECT FROM t WHERE YEAR(d) >= 2016;由于 MySQL 不像 Oracle 那样⽀持函数索引，即使 d 字段有索引，也会直接全表扫描。应改为 > SELECT FROM t WHERE d >= ‘2016-01-01’;
用 IN 来替换 OR 低效查询 慢 SELECT FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;高效查询 > SELECT FROM t WHERE LOC_IN IN (10,20,30);
LIKE 双百分号无法使用到索引 SELECT FROM t WHERE name LIKE ‘%de%’;使用 SELECT FROM t WHERE name LIKE ‘de%’;
分组统计可以禁止排序 SELECT goods_id,count() FROM t GROUP BY goods_id;默认情况下，MySQL 对所有 GROUP BY col1，col2… 的字段进⾏排序。如果查询包括 GROUP BY，想要避免排序结果的消耗，则可以指定 ORDER BY NULL 禁止排序。使用 SELECT goods_id,count () FROM t GROUP BY goods_id ORDER BY NULL;
禁止不必要的 ORDER BY 排序 SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;使用 SELECT count (1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;
statement
insert数量：show status like 'Com_insert';
mysql> show status like 'Com_insert'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_insert | 0 | +---------------+-------+delete数量：show status like 'Com_delete';
mysql> show status like 'Com_delete'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_delete | 0 | +---------------+-------+update数量：show status like 'Com_update';
mysql> show status like 'Com_update'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_update | 0 | +---------------+-------+select数量：show status like 'Com_select';
mysql> show status like 'Com_select'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 2 | +---------------+-------+Database throughputs（吞吐）
发送吞吐量：show status like 'Bytes_sent';
mysql> show status like 'Bytes_sent'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Bytes_sent | 106824 | +---------------+--------+接收吞吐量：show status like 'Bytes_received';
mysql> show status like 'Bytes_received'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | Bytes_received | 4054 | +----------------+-------+总吞吐量计算公式：Bytes_sent+Bytes_received
serverconfig（数据库参数）
查询数据库全局配置：show variables;
总结
任何东西不应过重关注其外表，要注重内在的东西，往往绚丽的外表下会有对应的负担和损耗。mysql 数据库的监控支持通过 SQL 方式从 performance_schema 库中访问对应的表数据，前提是初始化此库并开启监控数据写入。对于监控而言，不在于手段的多样性，而需要明白监控的本质，以及需要的监控项内容，找到符合自身项目特色的监控方式。在选择监控工具对 mysql 监控时，需要关注监控工具本身对于数据库服务器的消耗，不要影响到其自身的使用。
附：zabbix监控脚本
[root@localhost ~]# vim /usr/local/zabbix/etc/zabbix_agentd.conf.d/mysql.conf # 最大使用连接数: UserParameter=MySQL.Max_used_connections,/bin/mysql -h127.0.0.1 -e"show status like 'Max_used_connections';" | grep Max_used_connections | awk '{print $2}' # 当前打开的连接数: UserParameter=MySQL.Threads_connected,/bin/mysql -h127.0.0.1 -e"show status like 'Threads_connected';" | grep Threads_connected | awk '{print $2}' # 缓存池命中次数: UserParameter=MySQL.Innodb_buffer_pool_reads,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_reads';" | grep Innodb_buffer_pool_reads | awk '{print $2}' # 缓存池未命中次数: UserParameter=MySQL.Innodb_buffer_pool_read_requests,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_read_requests';" | grep Innodb_buffer_pool_read_requests | awk '{print $2}' # 缓存池命中率： UserParameter=MySQL.Innodb_buffer_pool_ead_requests_probability,/usr/local/zabbix/share/zabbix/alertscripts/Innodb_buffer_pool_ead_requests_probability.sh # 缓存池总页数： UserParameter=MySQL.Innodb_buffer_pool_pages_total,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_pages_total';" | grep Innodb_buffer_pool_pages_total | awk '{print $2}' # 缓存池空闲页数： UserParameter=MySQL.Innodb_buffer_pool_pages_free,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_pages_free';" | grep Innodb_buffer_pool_pages_free | awk '{print $2}' # 缓存池使用率： UserParameter=MySQL.Innodb_buffer_pool_used,/usr/local/zabbix/share/zabbix/alertscripts/Innodb_buffer_pool_used.sh # 锁等待个数： UserParameter=MySQL.Innodb_row_lock_waits,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_row_lock_waits';" | grep Innodb_row_lock_waits | awk '{print $2}' # 锁等待平均时间： UserParameter=MySQL.Innodb_row_lock_time_avg,/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_row_lock_time_avg';" | grep Innodb_row_lock_time_avg | awk '{print $2}' # 表锁情况： UserParameter=MySQL.tables_lock_num,/bin/mysql -h127.0.0.1 -e"show open TABLES where in_use>0;" | wc -l # 发送吞吐量: UserParameter=MySQL.Bytes_sent,/bin/mysql -h127.0.0.1 -e"show status like 'Bytes_sent';" | grep Bytes_sent | awk '{print $2}' # 接收吞吐量: UserParameter=MySQL.Bytes_received,/bin/mysql -h127.0.0.1 -e"show status like 'Bytes_received';" | grep Bytes_received | awk '{print $2}' [root@localhost ~]# cat /usr/local/zabbix/share/zabbix/alertscripts/Innodb_buffer_pool_used.sh #!/bin/bash Innodb_buffer_pool_pages_total=`/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_pages_total';" | grep Innodb_buffer_pool_pages_total | awk '{print $2}'` Innodb_buffer_pool_pages_free=`/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_pages_free';" | grep Innodb_buffer_pool_pages_free | awk '{print $2}'` Innodb_buffer_pool_used=`echo scale=2';(('$Innodb_buffer_pool_pages_total-$Innodb_buffer_pool_pages_free')/'$Innodb_buffer_pool_pages_total')' | bc` echo 0$Innodb_buffer_pool_used [root@localhost ~]# cat /usr/local/zabbix/share/zabbix/alertscripts/Innodb_buffer_pool_ead_requests_probability.sh #!/bin/bash Innodb_buffer_pool_reads=`/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_reads';" | grep Innodb_buffer_pool_reads | awk '{print $2}'` Innodb_buffer_pool_read_requests=`/bin/mysql -h127.0.0.1 -e"show status like 'Innodb_buffer_pool_read_requests';" | grep Innodb_buffer_pool_read_requests | awk '{print $2}'` Innodb_buffer_pool_ead_requests_probability=`echo scale=2';(1-'$Innodb_buffer_pool_reads'/'$Innodb_buffer_pool_read_requests')' | bc` echo 0$Innodb_buffer_pool_ead_requests_probability如果不想直接输入用户名密码可以摘/etc/my.ini文件中修改添加以下两个参数： [mysql] user=root password=p@ssw0rd
