1、查看库下的表
Show tables;
2、查看哪些库
Show databases;
3、查看表的状态
use test
Show table status like 't1';
/*查看建表语句*/
show create table t1;
/*查看表的索引*/
show index from t1;
/*查看引擎状态，里面会记录最近的死锁信息，当然，我们的死锁信息打印进了错误日志，死锁也可查日志*/
show engine innodb status; --还可以排查一些性能问题
/*查看DB线程信息*/
show processlist 或者show full processlist
/*查看系统参数跟系统状态：*/
show global variables like "variable_name";
show global status like "variable_name";
/*看执行计划，再看mysql内部如何改写SQL*/
explain SQL;
show warnings;
/*快速造大量测试数据*/
insert into t select ... from
#查看库的总量
select count(1) frominformation_schema.SCHEMATA where schema_name not in ('mysql','information_schema','performance_schema','sys');
#查看表的总量
select count(*) frominformation_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys');
#查看每个库下多少个表：
selectTABLE_SCHEMA,count(*) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') group byTABLE_SCHEMA;
#查看100W+的表的个数：
select count(*) frominformation_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') and TABLE_ROWS>=1000000;
#查看10G+表的个数：
select count(*) frominformation_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') and DATA_FREE+DATA_LENGTH+INDEX_LENGTH>=10737418240;
#查看非innodb表个数：
select count(*) from information_schema.tables
where table_schemanot in ('mysql','information_schema','performance_schema','sys') and engine !='InnoDB';
4、表的碎片率排行top10
select table_schema,table_name,table_rows,data_length,index_length,data_free ,
data_free/(data_length+index_length+data_free)
as free_rate
from information_schema.tables
where table_schemanot in ('mysql','information_schema','performance_schema','sys')
order by free_ratedesc
limit 10;
表的碎片大小排行top10
select table_schema,table_name,table_rows,data_length,index_length,data_free
from information_schema.tables
where table_schemanot in ('mysql','information_schema','performance_schema','sys')
order by data_freedesc
limit 10;
表的行数top10，
select table_schema,table_name,table_rows,data_length,index_length,data_free
from information_schema.tables
where table_schemanot in ('mysql','information_schema','performance_schema','sys')
order by table_rowsdesc
limit 10;
表的体积top10
select table_schema,table_name,table_rows,data_length,index_length,data_free
from information_schema.tables
where table_schemanot in ('mysql','information_schema','performance_schema','sys')
order bydata_length+index_length+data_free desc
limit 10;
表的auto_increment top 10
select table_schema,table_name,table_rows,AUTO_INCREMENT
from information_schema.tables
where table_schemanot in ('mysql','information_schema','performance_schema','sys')
order byAUTO_INCREMENT desc
limit 10;
未使用的索引情况：总数，按表统计top10：
select count(1) fromsys.schema_unused_indexes;
select object_schema,object_name,count(1) from sys.schema_unused_indexes group by object_schema,object_name order by count(1) desc limit 10;
mysql监控实施
一、对数据库服务可用性进行监控
1.1 通过测试账号ping命令返回的信息判断数据库可以通过网络连接
[root@localhost]# /usr/bin/mysqladmin -uroot -p123456 ping
mysqld is alive
1.2 确认数据库是否可读写
a.检查数据库的read_only参数是否为off
[root@localhost]# mysql -uroot -p123456 -P3306 -e "show global variables like 'read_only'" | grep read_only
read_only OFF
b.执行简单的数据库查询，如：select @@version;
[root@localhost]# mysql -uroot -p123456 -P3306 -e "select @@version" | grep Mysql
5.5.56-Mysql
二、对数据库性能进行监控
2.1 监控数据库连接数可用性
a.数据库最大连接数
[root@localhost]# mysql -uroot -p123456 -e "show variables like 'max_connections'"
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
b.数据库当前打开的连接数
[root@localhost]# mysqladmin -uroot -p123456 extended-status | grep -w "Threads_connected"
| Threads_connected | 1 |
注：如何计算当前打开的连接数占用最大连接数的比例呢？
result=Threads_connected/max_connections,在做监控报警或可视化监控时能够很好的根据这个比例及时调整最大连接数。
2.2 数据库性能监控
a.QPS:每秒的查询数
QPS计算方法
Questions = SHOW GLOBAL STATUS LIKE 'Questions';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
QPS=Questions/Uptime
b.TPS:每秒的事物量（commit与rollback的之和）
TPS计算方法
Com_commit = SHOW GLOBAL STATUS LIKE 'Com_commit';
Com_rollback = SHOW GLOBAL STATUS LIKE 'Com_rollback';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
TPS=(Com_commit + Com_rollback)/Uptime
2.3 数据库并发请求数量
Mysql [(none)]> SHOW GLOBAL STATUS LIKE 'Threads_running';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 3 |
+-----------------+-------+
1 row in set (0.00 sec)
注：并发请求数量通常会远小于同一时间内连接到数据库的连接数数量。
三、对主从复制进行监控
3.1 主从复制链路状态的监控
3.2 主从复制延迟时间的监控
3.3 定期确认主从复制的数据是否一致
