mysql快速查询的方法：1、查询正在运行中的事务；2、查看当前连接，并且能够知晓连接数；3、查看一个表的大小；4、查看某个数据库所有表的大小。
更多相关免费学习推荐：mysql教程(视频)
mysql快速查询的方法：
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.查看当前连接，并且能够知晓连接数
select substring_index(host,‘:‘,1) as ip , count(*) from information_schema.processlist group by ip;
3.查看一个表的大小
select concat(round(sum(data_length/1024/1024),2),‘m‘) from information_schema.tables where table_schema=‘数据库名‘ and table_name=‘表名‘;
4.查看某个数据库所有表的大小
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.查看各个库下的表数据大小
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.查看各个数据库的数据大小
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
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_typefrom information_schema.routineswhere 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.查看实例中哪些表字段字符集和默认字符集不一致
select table_schema,table_name,column_name,character_set_name from information_schema.columns where (character_set_name is null or character_set_name <> ‘utf8‘) and table_schema not in (‘information_schema‘,‘performance_schema‘,‘test‘,‘mysql‘,‘sys‘);
7.查看实例中哪些表字段字符校验规则和默认的不一致
查看当前字符集和校对规则设置
show variables like ‘collation_%‘;select table_schema,table_name,column_name,character_set_name,collation_name from information_schema.columns where (collation_name is null or collation_name <> ‘utf8_general_ci‘) and table_schema not in (‘information_schema‘,‘performance_schema‘,‘test‘,‘mysql‘,‘sys‘);
8.查看哪些账号有除了select、update、insert以外的权限
select grantee,privilege_type,concat(table_schema,‘-‘,table_name,‘-‘,column_name) from column_privileges where privilege_type not in (‘select‘,‘insert‘,‘update‘)union select grantee,privilege_type,table_schema from schema_privileges where privilege_type not in (‘select‘,‘insert‘,‘update‘)unionselect grantee,privilege_type,concat(table_schema,‘-‘,table_name) from table_privileges where privilege_type not in (‘select‘,‘insert‘,‘update‘) unionselect grantee,privilege_type,concat(‘user‘) from user_privileges where privilege_type not in (‘select‘,‘insert‘,‘update‘);
9.查看实例中哪些表不是默认存储引擎，以默认存储引擎为innodb为例
select table_name,engine from information_schema.tables where engine!=‘innodb‘ and table_schema not in (‘information_schema‘,‘performance_schema‘,‘test‘,‘mysql‘, ‘sys‘);
10.查看实例中哪些表有外键
select a.table_schema,a.table_name,a.constraint_type,a.constraint_name,b.referenced_table_name,b.referenced_column_name from information_schema.table_constraints a left join information_schema.key_column_usage b on a.constraint_name=b.constraint_name where a.constraint_type=‘foreign key‘;
11.查看实例中哪些表字段有级联更新
select table_schema,table_name,column_name,referenced_table_schema,referenced_table_name,referenced_column_name from information_schema.key_column_usage where referenced_table_schema is not null and referenced_table_name is not null and referenced_column_name is not null and table_schema not in (‘information_schema‘,‘performance_schema‘,‘test‘,‘mysql‘, ‘sys‘);
12.如何根据用户名、连接时间、执行的sql等过滤当前实例中的连接信息
select user,host,db from processlist where time>2;
13.查看数据库中没有索引的表
select table_schema,table_name from information_schema.tables where table_name not in (select distinct(any_value(table_name)) from information_schema.statistics group by index_name) and table_schema not in (‘sys‘,‘mysql‘,‘information_schema‘,‘performance_schema‘);
14.查看数据库中有索引的表，建立了哪些索引
显示结果：库名、表名、索引名
select table_schema,table_name,group_concat(index_name) from information_schema.statistics where table_schema not in (‘sys‘,‘mysql‘,‘information_schema‘,‘performance_schema‘) group by table_name ;
以上就是mysql如何快速查询的详细内容。