mysql如何快速查询

概述mysql如何快速查询

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如何快速查询全部内容，希望文章能够帮你解决mysql如何快速查询所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。