mysql如何快速查询-木庄网络博客

本文摘自PHP中文网，作者coldplay.xixi，侵删。

mysql快速查询的方法：1、查询正在运行中的事务；2、查看当前连接，并且能够知晓连接数；3、查看一个表的大小；4、查看某个数据库所有表的大小。

更多相关免费学习推荐：mysql教程(视频)

mysql快速查询的方法：

1.查询正在运行中的事务

1	`select p.id,p.user,p.host,p.db,p.command,p.time,i.trx_state,i.trx_started,p.info from information_schema.processlist p,information_schema.innodb_trx i where p.id=i.trx_mysql_thread_id;`

2.查看当前连接，并且能够知晓连接数

1	`select SUBSTRING_INDEX(host,‘:‘,1)` `as` `ip ,` `count(*) from information_schema.processlist group by ip;`

3.查看一个表的大小

1	`select concat(round(sum(DATA_LENGTH/1024/1024),2),‘M‘) from information_schema.tables where table_schema=‘数据库名‘ AND table_name=‘表名‘;`

4.查看某个数据库所有表的大小

1	`select table_name,concat(round(sum(DATA_LENGTH/1024/1024),2),‘M‘) from information_schema.tables where table_schema=‘t1‘ group by table_name;`

5.查看库的大小，剩余空间的大小

select table_schema,round((sum(data_length / 1024 / 1024) + sum(index_length / 1024 / 1024)),2) dbsize,round(sum(DATA_FREE / 1024 / 1024),2) freesize,       
round((sum(data_length / 1024 / 1024) + sum(index_length / 1024 / 1024)+sum(DATA_FREE / 1024 / 1024)),2) spsize  
from information_schema.tables 
where table_schema not in (‘mysql‘,‘information_schema‘,‘performance_schema‘) 
group by table_schema order by freesize desc;

6.查找关于锁

select r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query  
from information_schema.innodb_lock_waits w 
inner join information_schema.innodb_trx b 
on b.trx_id = w.blocking_trx_id 
inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id\G

information_schema的使用

1.查看各个库下的表数据大小

1 2	`select table_name,concat(round(sum(DATA_LENGTH/1024/1024),2),‘M‘)` `from information_schema.tables where table_schema=‘db_name‘ group by table_name;`

2.查看各个数据库的数据大小

1	`select TABLE_SCHEMA, concat(round(sum(data_length)/1024/1024,2),‘ MB‘)` `as` `data_size from information_schema.tables group by table_schema;`

3.查看实例有没有主键

select table_schema,table_name from information_schema.tables 
where (table_schema,table_name) 
not in(select distinct table_schema,table_name from information_schema.STATISTICS where INDEX_NAME=‘PRIMARY‘ ) 
and table_schema not in ( ‘sys‘,‘mysql‘,‘information_schema‘,‘performance_schema‘);

4.查看实例中哪些字段可以为null

1	`select TABLE_SCHEMA,TABLE_NAME from COLUMNS where IS_NULLABLE=‘YES‘` `and` `TABLE_SCHEMA not in (‘information_schema‘,‘performance_schema‘,‘mysql‘, ‘sys‘)\G`

5.查看实例中有哪些存储过程和函数

#存储过程
select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE
from information_schema.ROUTINES
where ROUTINE_TYPE=‘PROCEDURE‘ and ROUTINE_SCHEMA not in (‘mysql‘,‘sys‘,‘information_schema‘,‘performance_schema‘);
#函数
select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE 
from information_schema.ROUTINES 
where ROUTINE_TYPE=‘FUNCTION‘ and ROUTINE_SCHEMA not in (‘mysql‘,‘sys‘,‘information_schema‘,‘performance_schema‘);

6.查看实例中哪些表字段字符集和默认字符集不一致

阅读剩余部分