mysql logical read,MYSQL SQL巡检脚本

很幸运搞到了脚本

来看第一个

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 ;