How to quickly query mysql

Mysql quick query method: 1. Query the running transaction; 2. View the current connection, and know the number of connections; 3. View the size of a table; 4. View all tables of a database size.

More related free learning recommendation: mysql tutorial (video)

mysql quick query Method:

1. Query running transactions

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. Check the current connection and be able to know the number of connections

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

3. Check the size of a table

select concat(round(sum(DATA_LENGTH/1024/  1024), 2), 'M') from information_schema.tables where table_schema='database name' AND table_name='table name';

4. Check the size of all tables in a database

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. Check the size of the library and the size of the remaining space

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. Find about locks

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

Use of information_schema

1. Check the table data size under each library

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. Check the data size of each database

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

3. Check if the instance has a primary key

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. Check which fields in the instance can be 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 .Check which stored procedures and functions are in the instance

#stored procedure
 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');
 #function
 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. Check which table field character sets in the instance are inconsistent with the default character set

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. Check which table field character verification rules in the instance are inconsistent with the default

View current character set and collation rule settings

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. Check which accounts have except select and update.Permissions other than 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')
 union
 select GRANTEE, PRIVILEGE_TYPE, concat(TABLE_SCHEMA,'-',TABLE_NAME) from TABLE_PRIVILEGES where PRIVILEGE_TYPE not in ('select','insert','update')
 union
 select GRANTEE,PRIVILEGE_TYPE,concat('user') from USER_PRIVILEGES where PRIVILEGE_TYPE not in ('select','insert','update');

9. Check which tables in the instance are not the default storage engine, Take the default storage engine as innodb as an example

select TABLE_NAME,ENGINE
 from information_schema.tables
 where ENGINE!='innodb' and TABLE_SCHEMA not in ('information_schema','performance_schema','test','mysql', 'sys');

10. Check which tables in the instance have foreign keys

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. Check which table fields in the instance have cascading updates

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. How to execute according to the user name, connection time, sql, etc. to filter the connection information in the current instance

select USER,HOST,DB from processlist where TIME>2;

13. View Tables without indexes in the database

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. Check the tables with indexes in the database and which indexes have been established

Display results: library name, table name, index name

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 ;

The above is the detailed content of how to quickly query mysql, more please pay attention 1024programmer.com Other related articles!