MySQL Operations common SQL statements

################## #分析类 ###############################################################

--table with more than 1000 rows of data in the query table

Select Concat (Table_schema, '. ', table_name) as Table_name,table_rows

From Information_schema.tables where table_rows > 1000

ORDER BY table_rows Desc;

--View partition data

SELECT partition_name,table_rows from INFORMATION_SCHEMA. partitions WHERE table_name = ' xxxx ';

--Query the 5 largest tables

SELECT concat (Table_schema, '. ', TABLE_NAME) TABLE_NAME,

Concat (Round (data_length/(1024*1024), 2), ' M ') data_length

From INFORMATION_SCHEMA. TABLES

ORDER by Data_length DESC LIMIT 5;

--Querying tables without a primary key

Select CONCAT (T.table_schema, ".", T.table_name) as table_name

From INFORMATION_SCHEMA. TABLES T

Left JOIN information_schema. Table_constraints TC

On t.table_schema = Tc.table_schema

and t.table_name = Tc.table_name

and Tc.constraint_type = ' PRIMARY KEY '

WHERE Tc.constraint_name is NULL

and T.table_type = ' BASE table ';

################## #故障诊断 ###############################################################

--Reverse sort by execution time in MySQL

Mysqladmin processlist--verbose |grep ' Query ' |awk-f ' | ' ' {print $7 $ $9} ' |sort-rn-k1

--Cat killlongquerysession.sh kills session with more than 300 execution time

#!/bin/bash

Executetime= (' Mysql-bse ' show processlist ' | grep ' Query ' |awk ' {print $6 ' "$} ' |sort-rn|head-1 ') #第6列是运行时间, first column Sess Ion ID

Time=${executetime[0]}

ID=${EXECUTETIME[1]}

While:

Do

maxtime=300

If [$time-gt $maxtime]; Then

echo $time $id >>/tmp/killqueryid.log

Mysql-bse "Kill $id"

#else

# echo $time $id

Fi

Sleep #睡眠10s

Done

--Cat killwaitsession.sh kills waiting for waiting session in Select

#!/bin/bash

For i in ' mysql-bse ' show full Processlist ' | Grep-i select |grep-i "Waiting | awk ' {print '} '

Do

Mysql-bse "Kill $i"

Done

MySQL Operations common SQL statements