Mysql使用指南

一、数据库表、字段、数据量等数据统计查询：

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