1
mysql_check.txt
5墨值下载
SET @dbname = 'ddldb';
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,
of 10
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
1
2
9-数据库人的进阶之路：从PG分区、SQL优化到拥抱AI未来（罗敏）.pptx
3
1-PG版本兼容性案例（彭冲）.pptx
4
2-TDSQL PG在复杂查询场景中的挑战与实践-opensource.pdf
5
6-PostgreSQL 哈希索引原理浅析（文一）.pdf
6
8-基于PG向量和RAG技术的开源知识库问答系统MaxKB.pptx
7
3-AI时代的变革者-面向机器的接口语言(MOQL)_吕海波.pptx
8
4-IvorySQL V4：双解析器架构下的兼容性创新实践.pptx
9
7-拉起PG好伙伴DifySupaOdoo.pdf
10
《云原生安全攻防启示录》李帅臻.pdf
相关文档
评论