-
查询缓存情况
SHOW VARIABLES LIKE '%cache%';
-
查询慢查询情况
SHOW VARIABLES LIKE '%slow%';
SHOW GLOBAL STATUS LIKE '%slow%';
-
查看最大链接数
SHOW VARIABLES LIKE 'max_connections';
-
查看最大链接过的用户数
SHOW GLOBAL STATUS LIKE 'max_used_connections';
-
显示用户正在运行的线程
SHOW PROCESSLIST;
或
SELECT * FROM information_schema.processlist;
参考:mysql: show processlist 详解如果出现有表锁定,无法查询的情况,可以使用 该命令来查询出任务的线程的id,然后
kill id
,即可结束线程(注意不要随便在运行的服务上,做alter table操作,包含更改表备注,不然可能会导致表锁定,深刻体会)。参考:MySQL出现Waiting for table metadata lock的原因以及解决方法 -
查询出innodb的引擎的情况
SHOW ENGINE INNODB STATUS
参考:mysql性能监控指标及分析 -
在线alter表的注意事项及注意的坑。
7.1 查询事务:5.5 版本
SELECT
*
/**
a.trx_id,
a.trx_state,
a.trx_started,
a.trx_query,
b.ID,
b. USER,
b. HOST,
b.DB,
b.COMMAND,
b.TIME,
b.STATE,
b.INFO
**/
FROM
information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
WHERE
b.COMMAND = 'Sleep';
5.6版本(支持在线ddl)
SELECT
a.trx_id,
a.trx_state,
a.trx_started,
a.trx_query,
b.ID,
b.USER,
b.DB,
b.COMMAND,
b.TIME,
b.STATE,
b.INFO,
c.PROCESSLIST_USER,
c.PROCESSLIST_HOST,
c.PROCESSLIST_DB,
d.SQL_TEXT
FROM
information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
AND b.COMMAND = 'Sleep'
LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
- 查询某个表的索引情况,一般查询太慢都是没有命中联合索引.
SELECT a.TABLE_SCHEMA,
a.TABLE_NAME,
a.index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics a
GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name
having a.TABLE_NAME='dm_yy_kxd_nationnalver_ztsj_xg_day'
-
Mysql备份数据库:
mysqldump -h 127.0.0.1 -P 3306 -u root -ppsd --databases samego > /home/alic/MySQL/samego.sql
其中,
127.0.0.1
为数据库地址,3306
为数据库端口,root
为数据库账号,psd
为数据库账号密码,samego
为数据库的名称,/home/alic/MySQL/samego.sql
为备份的数据库的文件的地址. -
如果查询某个表,看起来数量很少,但是实际查询很慢,可能是因为有些数据还没实际删除。一般我们执行
delete
操作并不会实际删除表在磁盘上的数据,实际删除表在磁盘上的数据需要执行以下两个命令,optimize table t
或alter table t engine=InnoDB
。注意,如果是Mysql 5.6 版本以下的会锁表。
-
查询数据库或表的容量
查询数据库的容量SELECT table_schema AS '数据库', SUM(TRUNCATE(data_length/1024/1024, 2)) AS '数据容量(MB)', SUM(TRUNCATE(data_free/1024/1024,2)) AS '空间碎片(MB)', SUM(TRUNCATE(index_length/1024/1024, 2)) AS '索引容量(MB)' FROM information_schema.tables GROUP BY table_schema
查询表的容量
SELECT table_schema AS '数据库', table_name AS '表名', table_rows AS '记录数', TRUNCATE(data_length/1024/1024, 2) AS '数据容量(MB)', TRUNCATE(data_free/1024/1024,2) AS '空间碎片(MB)', TRUNCATE(index_length/1024/1024, 2) AS '索引容量(MB)' FROM information_schema.tables ORDER BY data_length DESC, index_length DESC;
2020-11-22 删除查询表容量中的 max_data_length 字段,该字段含义表示“表数据的最大容量”,参考,《高性能MySQL》第三版
-
查询数据库的 bin_log 日志情况
show global variables like '%log_bin%';
以下为没有开bin-log日志的
以下为开了binlog日志的,binlog并且binlog日志,在log_bin_basename
对应的value目录中
13. 查询语句没有走对索引,可以执行一下 ANALYZE TABLE tbl_name
来重新调整表,不过注意执行这个的时候可能会加读锁,会影响写操作,和并发读操作。 参考:MySQL5.6 官方文档 , MySQL为什么有时候会选错索引?
-
ALTER TABLE test_table ENGINE=INNODB;
MySQL 5.6 清除表的空间碎片,注意小心操作,如果表比较大,会耗费MySQL比较多资源 和可能锁表 -
sudo mysqlbinlog -vv --base64-output=decode-rows /data/zhangcanlong/binlog/mysql-bin.003303 --database=test_db --start-datetime='2022-03-22 14:00:00' --stop-datetime='2022-03-30 22:00:00'| grep -A 200 zhangcanlong_test_t
恢复指定时间下,指定库 test_db,指定表zhangcanlong_test_t 的binlog日志信息 -
SELECT * FROM INFORMATION_SCHEMA.TABLES
查询mysql表的各种信息,例如:创建时间,更新时间(这个表的最后更新时间,如果缓存不够,可能不会更新),表行数等;SELECT * FROM mysql.
innodb_table_statsWHERE database_name IN ('test_db') ORDER BY last_update DESC
– 其中last_update 为最后更新时间 ,这个表的准确一些