MYSQL SQL巡检脚本

海鲨数据库架构师 2021-04-02

990

很幸运搞到了脚本

来看第一个

set @dbname='fcpgdb';

select "1.too many logical read SQL examined_rows >20000 " as  '----------------------------------------------'  from dual; 

SELECT SCHEMA_NAME,
DIGEST AS digest,
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT,
ROUND(SUM_ROWS_AFFECTED/COUNT_STAR, 0) AS rows_affected_avg,
ROUND(SUM_ROWS_SENT/COUNT_STAR, 0) AS rows_sent_avg,
ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) AS rows_examined_avg,
FIRST_SEEN,
LAST_SEEN 
FROM performance_schema.events_statements_summary_by_digest 
where  DIGEST_TEXT not like '%SHOW%' and  DIGEST_TEXT not like 'desc%'
and SCHEMA_NAME=@dbname
and ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) >20000
and COUNT_STAR >200
and last_seen > date_sub(curdate(),interval 10 day) 
order by  ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0)  desc
;

第一行是数据库名字变量

第二行像ORACLE语句，是打印标题

核心SQL 是从performance_schema里面的

events_statements_summary_by_digest 获取相关的SQL执行情况，

类似ORACLE的 V$SQLAREA

考核指标是SUM_ROWS_EXAMINED 这个是扫描行数，除以执行次数。

select "2.large transaction SQL,effected_rows >100000 " as  '----------------------------------------------'  from dual; 


SELECT SCHEMA_NAME,
DIGEST AS digest,
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT,
ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) AS rows_affected_avg,
ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS rows_sent_avg,
ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS rows_examined_avg,
FIRST_SEEN,
LAST_SEEN 
FROM performance_schema.events_statements_summary_by_digest 
where  DIGEST_TEXT not like '%SHOW%' and  DIGEST_TEXT not like 'desc%'
-- and SCHEMA_NAME is not null 
-- and SCHEMA_NAME not in ('information_schema','mysql','performance_schema','sys') 
and SCHEMA_NAME=@dbname
and ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) >100000
and COUNT_STAR >200
and last_seen > date_sub(curdate(),interval 10 day) 
order by ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) desc

这个是检查 DML语句修改的行数

select "3.select SQL return too many rows :split pages, sent_rows >1000 " as  '----------------------------------------------'  from dual; 

SELECT SCHEMA_NAME,
DIGEST AS digest,
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT,
ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) AS rows_affected_avg,
ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS rows_sent_avg,
ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS rows_examined_avg,
FIRST_SEEN,
LAST_SEEN 
FROM performance_schema.events_statements_summary_by_digest 
where  DIGEST_TEXT not like '%SHOW%' and  DIGEST_TEXT not like 'desc%'
and SCHEMA_NAME=@dbname
and ROUND(SUM_ROWS_SENT / COUNT_STAR, 0)>1000
and COUNT_STAR >200
and last_seen > date_sub(curdate(),interval 10 day) 
order by ROUND(SUM_ROWS_SENT / COUNT_STAR, 0)
;

这个是返回给客户端的数据行数

select "4.redundant index" as  '----------------------------------------------' from dual;

select table_schema,
       table_name,
       redundant_index_name,
       redundant_index_columns,
       dominant_index_name,
       dominant_index_columns,
       sql_drop_index
  from sys.schema_redundant_indexes
  where table_schema=@dbname
  order by table_name;

这个呢就是检查冗余的索引

set @dbname='fcpgdb';

select "1.too many logical read SQL examined_rows >20000 " as  '----------------------------------------------'  from dual; 

SELECT SCHEMA_NAME,
DIGEST AS digest,
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT,
ROUND(SUM_ROWS_AFFECTED/COUNT_STAR, 0) AS rows_affected_avg,
ROUND(SUM_ROWS_SENT/COUNT_STAR, 0) AS rows_sent_avg,
ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) AS rows_examined_avg,
FIRST_SEEN,
LAST_SEEN 
FROM performance_schema.events_statements_summary_by_digest 
where  DIGEST_TEXT not like '%SHOW%' and  DIGEST_TEXT not like 'desc%'
and SCHEMA_NAME=@dbname
and ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) >20000
and COUNT_STAR >200
and last_seen > date_sub(curdate(),interval 10 day) 
order by  ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0)  desc
;

select "2.large transaction SQL,effected_rows >100000 " as  '----------------------------------------------'  from dual; 


SELECT SCHEMA_NAME,
DIGEST AS digest,
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT,
ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) AS rows_affected_avg,
ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS rows_sent_avg,
ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS rows_examined_avg,
FIRST_SEEN,
LAST_SEEN 
FROM performance_schema.events_statements_summary_by_digest 
where  DIGEST_TEXT not like '%SHOW%' and  DIGEST_TEXT not like 'desc%'
-- and SCHEMA_NAME is not null 
-- and SCHEMA_NAME not in ('information_schema','mysql','performance_schema','sys') 
and SCHEMA_NAME=@dbname
and ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) >100000
and COUNT_STAR >200
and last_seen > date_sub(curdate(),interval 10 day) 
order by ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) desc
;

select "3.select SQL return too many rows :split pages, sent_rows >1000 " as  '----------------------------------------------'  from dual; 

SELECT SCHEMA_NAME,
DIGEST AS digest,
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT,
ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) AS rows_affected_avg,
ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS rows_sent_avg,
ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS rows_examined_avg,
FIRST_SEEN,
LAST_SEEN 
FROM performance_schema.events_statements_summary_by_digest 
where  DIGEST_TEXT not like '%SHOW%' and  DIGEST_TEXT not like 'desc%'
and SCHEMA_NAME=@dbname
and ROUND(SUM_ROWS_SENT / COUNT_STAR, 0)>1000
and COUNT_STAR >200
and last_seen > date_sub(curdate(),interval 10 day) 
order by ROUND(SUM_ROWS_SENT / COUNT_STAR, 0)
;

select "4.redundant index" as  '----------------------------------------------' from dual;

select table_schema,
       table_name,
       redundant_index_name,
       redundant_index_columns,
       dominant_index_name,
       dominant_index_columns,
       sql_drop_index
  from sys.schema_redundant_indexes
  where table_schema=@dbname
  order by table_name;


#select "5.no recommended data type " as '----------------------------------------------'   from dual; 


#select TABLE_SCHEMA, TABLE_NAME,COLUMN_NAME,DATA_TYPE 
#from information_schema.COLUMNS 
#where DATA_TYPE in ('enum','set','bit','binary') 
#-- and table_schema not in ('information_schema','mysql','performance_schema','sys')
#and table_schema=@dbname
#order by table_name;


select "6.too many indexes on one table >5 " as  '----------------------------------------------'  from dual; 


select table_schema,
table_name,count(*) num_idx
from 
  (select distinct table_schema,table_name, INDEX_NAME 
   from information_schema.STATISTICS
   where 
-- table_schema not in ('information_schema','mysql','performance_schema','sys')    
   table_schema=@dbname
   ) a 
group by table_schema,table_name 
having   num_idx>5 
order by table_schema,num_idx desc,table_name ;

select "7.no primary " as '----------------------------------------------'  from dual; 

select  t.table_name 
from information_schema.tables t
left join
 (select table_name from information_schema.STATISTICS
  where INDEX_NAME='PRIMARY'
  and  table_schema =@dbname
  group by   table_name 
 ) a
on  t.table_name=a.table_name
where t.table_schema  =@dbname
and a.table_name is null
order by table_name;

select "8.more than 5 columns in 1 index " as  '----------------------------------------------'  from dual; 

select table_schema, table_name,index_name,count(index_name) num_col
from information_schema.STATISTICS 
where 
table_schema=@dbname
and NON_UNIQUE=1
group by table_schema,table_name,index_name 
having   num_col>5  
order by  table_schema, num_col,table_name,index_name;





select "9.schema_name>32 " as '----------------------------------------------'  from dual; 

select *
  from information_schema.schemata
where char_length(SCHEMA_NAME) > 32;

select "9.table_name>32 " as '----------------------------------------------'  from dual; 

select TABLE_schema, TABLE_name
  from information_schema.tables
  where char_length(TABLE_name) > 32
  and table_schema=@dbname
  order by table_name
;

select "9.column_name >32 " as '----------------------------------------------'  from dual; 

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
  from information_schema.columns
where char_length(COLUMN_NAME) > 32
   and table_schema=@dbname
order by table_name
;

select "10.not  InnoDB engine " as '----------------------------------------------'  from  dual; 

select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables 
where 
 ENGINE <>'InnoDB'
 and table_schema=@dbname
order by table_name
;



select "11.database  utf8mb4 character " as '----------------------------------------------'   from dual; 

select schema_name,default_character_set_name,default_collation_name 
from information_schema.schemata 
where default_character_set_name not like '%utf8mb4%'
and schema_name=@dbname;

select "11.table  utf8mb4 character " as '----------------------------------------------'   from dual; 

select TABLE_SCHEMA, TABLE_NAME,TABLE_COLLATION
  from information_schema.tables
where TABLE_COLLATION not like 'utf8mb4%'
and table_schema=@dbname
order by table_name
;


select "11.column  utf8mb4 character " as '----------------------------------------------'   from dual; 

select TABLE_SCHEMA, TABLE_NAME,column_name, COLLATION_NAME
  from information_schema.columns
where 
   table_schema=@dbname
   and COLLATION_NAME not like 'utf8mb4%'
order by table_name
;


#select "12.table comment " as '----------------------------------------------'  from dual; 

#select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES 
#where
#table_schema=@dbname
#and TABLE_COMMENT=''
#order by table_name
#;

#select "12.column comment " as '----------------------------------------------'  from dual; 

#select distinct TABLE_SCHEMA,TABLE_NAME,column_name from information_schema.COLUMNS 
#where COLUMN_COMMENT='' and 
#table_schema=@dbname
#order by table_name
#;

select "13.columns in 1 table >100 " as '----------------------------------------------'   from dual; 


select TABLE_SCHEMA, TABLE_NAME,count(COLUMN_NAME) num_col
from information_schema.COLUMNS 
where 
table_schema=@dbname
group by TABLE_SCHEMA, TABLE_NAME 
having num_col>100
order by table_name;

select "14.dont no triggers " as '----------------------------------------------'  from dual; 


select TRIGGER_SCHEMA,TRIGGER_NAME from information_schema.TRIGGERS 
where 
TRIGGER_SCHEMA=@dbname
order by TRIGGER_NAME
;

select "15.not hash partition table " as '----------------------------------------------'   from dual; 


select distinct TABLE_NAME from information_schema.PARTITIONS 
where 
table_schema=@dbname
and PARTITION_METHOD is not null and PARTITION_METHOD<>'HASH'
order by TABLE_NAME
;

select "16.columns in the primary >3 " as '----------------------------------------------'   from dual; 


select table_schema, table_name,index_name,count(COLUMN_NAME) num_col
from information_schema.STATISTICS 
where INDEX_NAME='PRIMARY' 
and table_schema=@dbname
group by table_schema,table_name
having   num_col>3  
order by  table_schema, num_col,table_name,index_name;

select "17.组合索引第一列的离散度太低 " as  '----------------------------------------------'  from dual; 


select first.TABLE_SCHEMA,first.TABLE_NAME,first.INDEX_NAME,first.COLUMN_NAME  col1,first.CARDINALITY  CARDINALITY1 ,second.COLUMN_NAME col2 ,second.CARDINALITY  CARDINALITY2
from (
(select  TABLE_SCHEMA,TABLE_NAME,INDEX_SCHEMA,INDEX_NAME,COLUMN_NAME,  SEQ_IN_INDEX,CARDINALITY
from information_schema.STATISTICS 
where
table_schema=@dbname
and SEQ_IN_INDEX=1
) first,
(select  TABLE_SCHEMA,TABLE_NAME,INDEX_SCHEMA,INDEX_NAME,COLUMN_NAME,  SEQ_IN_INDEX,CARDINALITY
from information_schema.STATISTICS 
where 
table_schema=@dbname
and SEQ_IN_INDEX=2
) second 
)
where first.TABLE_SCHEMA=second.TABLE_SCHEMA
      and first.TABLE_NAME=second.TABLE_NAME
      and first.INDEX_NAME=second.INDEX_NAME
      and second.CARDINALITY>first.CARDINALITY
order by first.TABLE_NAME
;

select "18.no foreign key  " as '----------------------------------------------'  from dual; 


select table_name,column_name,constraint_name,referenced_table_name,referenced_column_name 
from  information_schema.key_column_usage 
where referenced_table_name is not null 
and constraint_schema=@dbname
order by TABLE_NAME
;

select "19.no DISTINCT *  " as '----------------------------------------------'  from dual; 


select SCHEMA_NAME,DIGEST
,DIGEST_TEXT 
from performance_schema.events_statements_summary_by_digest 
where DIGEST_TEXT like '%DISTINCTROW \*%' 
and SCHEMA_NAME=@dbname
;

select "20. no order by rand() " as '----------------------------------------------'  from  dual;


select SCHEMA_NAME,DIGEST
,DIGEST_TEXT 
from performance_schema.events_statements_summary_by_digest 
where DIGEST_TEXT like '%ORDER BY \`rand\`%' 
and SCHEMA_NAME=@dbname
;

select "21. no select * " as '----------------------------------------------'  from dual;

select count_star,SCHEMA_NAME, DIGEST
,DIGEST_TEXT 
from performance_schema.events_statements_summary_by_digest 
where DIGEST_TEXT like '%select \*%' 
and SCHEMA_NAME=@dbname
order by count_star desc
;




select "22.no global ALL privileges " as '----------------------------------------------'   from dual;

 select distinct concat(user,'@',host) from mysql.user where
                Select_priv='Y' and
                Insert_priv='Y' and
                Update_priv='Y' and
                Delete_priv='Y' and
                Create_priv='Y' and
                  Drop_priv='Y' and
                Reload_priv='Y' and
              Shutdown_priv='Y' and
               Process_priv='Y' and
                  File_priv='Y' and
                 Grant_priv='Y' and
            References_priv='Y' and
                 Index_priv='Y' and
                 Alter_priv='Y' and
               Show_db_priv='Y' and
                 Super_priv='Y' and
      Create_tmp_table_priv='Y' and
           Lock_tables_priv='Y' and
               Execute_priv='Y' and
            Repl_slave_priv='Y' and
           Repl_client_priv='Y' and
           Create_view_priv='Y' and
             Show_view_priv='Y' and
        Create_routine_priv='Y' and
         Alter_routine_priv='Y' and
           Create_user_priv='Y' and
                 Event_priv='Y' and
               Trigger_priv='Y' and
     Create_tablespace_priv='Y'
order by concat(user,'@',host)
;

select "22.no  ALL privileges on DB " as '----------------------------------------------'   from dual;

select distinct concat(user,'@',host) from mysql.db where
               Select_priv='Y' and
               Insert_priv='Y' and
               Update_priv='Y' and
               Delete_priv='Y' and
               Create_priv='Y' and
                 Drop_priv='Y' and
           References_priv='Y' and
                Index_priv='Y' and
                Alter_priv='Y' and
     Create_tmp_table_priv='Y' and
          Lock_tables_priv='Y' and
          Create_view_priv='Y' and
            Show_view_priv='Y' and
       Create_routine_priv='Y' and
        Alter_routine_priv='Y' and
              Execute_priv='Y' and
                Event_priv='Y' and
              Trigger_priv='Y'
order by concat(user,'@',host)
 ;


select "22.no  ALL privileges on table " as '----------------------------------------------'   from dual;

select distinct concat(user,'@',host) from mysql.tables_priv
    where Table_priv='Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create  View,Show view,Trigger'
order by concat(user,'@',host)
;

select "23.no DML on system databases  " as '----------------------------------------------'   from dual;


select distinct User from mysql.db 
where Db in ('inforation_schema','mysql','performance_schema','sys') 
and user not in ('mysql.session','mysql.sys')
and (Insert_priv='Y' or Update_priv='Y' or Delete_priv='Y')
order by User ;

mysql

文章转载自海鲨数据库架构师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MYSQL SQL巡检脚本

评论