一、数据库表、字段、数据量等数据统计查询:
1、查询库中表的总数量:
SELECT COUNT(*) TABLES FROM information_schema.TABLES WHERE TABLE_SCHEMA= = '库名' ;
2、查询库中每张表中的数据量和每张表数据、索引所占内存:
SELECT TABLE_NAME, concat(truncate(DATA_LENGTH/1024/1024,2),' MB') as data_size,concat(truncate(INDEX_LENGTH/1024/1024,2),' MB') as index_size,table_rows
from information_schema.tables where TABLE_SCHEMA = '库名' ORDER BY table_rows;
3、统计表中字段数量:
SELECT COUNT(*) FROM information_schema. COLUMNS WHERE table_schema = '库名' AND table_name = '表名';
4、查询整个库中字段总数:
SELECT COUNT(column_name) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '库名';
5、查询数据库中所以表、字段、字段类型、注释等信息
8 SELECT TABLE_NAME, column_name, DATA_TYPE, column_comment FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '库名' ;
二、Mysql锁表处理
1. 查看进程,查找被锁表的进程的ID
SHOW PROCESSLIST;
2. 杀掉锁表进程
kill PID
2. 查看是否锁表
SHOW OPEN TABLES WHERE In_use > 0;
3. 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
4. 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
三、Mysql事务操作
1、开启、提交、回滚事务
BEGIN; --开启事务
SELECT *;
UPDATE *;
ROLLBACK; --回滚事务
COMMIT; --提交事务
2、查看未提交的事务(3秒内未操作的事务)
SELECT
p.ID AS conn_id,
P.USER AS login_user,
P.HOST AS login_host,
p.DB AS database_name,
P.TIME AS trx_sleep_seconds,
TIME_TO_SEC(TIMEDIFF(NOW(),T.trx_started)) AS trx_open_seconds,
T.trx_started,
T.trx_isolation_level,
T.trx_tables_locked,
T.trx_rows_locked,
t.trx_state,
p.COMMAND AS process_state
FROM `information_schema`.`INNODB_TRX` t
INNER JOIN `information_schema`.`PROCESSLIST` p
ON t.trx_mysql_thread_id=p.id
WHERE t.trx_state='RUNNING'
AND p.COMMAND='Sleep'
AND P.TIME>3
ORDER BY T.trx_started ASC;
3、查看未提交的事务
select t.trx_mysql_thread_id from information_schema.innodb_trx t
4、结束未提交事务
kill PID