Shulou(Shulou.com)06/01 Report--

Editor to share with you the method of mysql fast query, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Mysql fast query methods: 1, query running transactions; 2, view the current connection, and can know the number of connections; 3, view the size of a table; 4, view the size of all tables in a database.

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

two。 View the current connections and know the number of connections

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

3. View 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. View 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 out 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

The use of information_schema

1. View 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

two。 View the data size of each database

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

3. Check whether 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 what stored procedures and functions are in the instance

# stored procedure select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPEfrom information_schema.ROUTINESwhere ROUTINE_TYPE='PROCEDURE' and ROUTINE_SCHEMA not in ('mysql','sys','information_schema','performance_schema'); # function select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPEfrom information_schema.ROUTINESwhere 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 the current character set and proofreading 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 permissions other than select, update and insert

Select GRANTEE,PRIVILEGE_TYPE,concat (TABLE_SCHEMA,'-',TABLE_NAME,'-',COLUMN_NAME) from COLUMN_PRIVILEGES where PRIVILEGE_TYPE not in ('select','insert','update') unionselect 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. Check which tables in the instance are not the default storage engine. Take innodb as an example.

Select TABLE_NAME,ENGINE from information_schema.tables where engineer created 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')

twelve。 How to filter the connection information in the current instance based on user name, connection time, sql executed, etc.

Select USER,HOST,DB from processlist where TIME > 2

13. View tables in the database that do not have indexes

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

Display result: 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; these are all the contents of the mysql quick query method. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.