# My日常监控及sys库的使用
## 一、统计信息（维度）
1、统计执行次数最多的语句：
SELECT
DIGEST_TEXT,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
COUNT_STAR DESC;
2、查看平均响应时间最多的语句：
SELECT
DIGEST_TEXT,
AVG_TIMER_WAIT,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
AVG_TIMER_WAIT DESC;
3、查看排序记录数最多的
SELECT
DIGEST_TEXT,
SUM_SORT_ROWS,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
SUM_SORT_ROWS DESC;
4、扫描记录数最多的
SELECT
DIGEST_TEXT,
SUM_ROWS_EXAMINED,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
SUM_ROWS_EXAMINED DESC;
5、查看使用临时表最多的
SELECT
DIGEST_TEXT,
SUM_CREATED_TMP_TABLES,
SUM_CREATED_TMP_DISK_TABLES,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
SUM_CREATED_TMP_TABLES desc,SUM_CREATED_TMP_DISK_TABLES desc;
6、查看返回结果集最多的
SELECT
DIGEST_TEXT,
SUM_ROWS_SENT,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
SUM_ROWS_SENT desc;
## 二、统计信息(对象维度)
1、查看哪个表物理IO最多？
SELECT
file_name,
event_name,
SUM_NUMBER_OF_BYTES_READ,
SUM_NUMBER_OF_BYTES_WRITE
FROM
`performance_schema`.file_summary_by_instance
ORDER BY
SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC;
2、查看哪个表逻辑IO最多?
SELECT
object_schema,
object_name,
COUNT_READ,
COUNT_WRITE,
COUNT_FETCH,
SUM_TIMER_WAIT
FROM
`performance_schema`.table_io_waits_summary_by_table
ORDER BY
sum_timer_wait DESC;
3、查看哪个索引访问最多？
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM
`performance_schema`.table_io_waits_summary_by_index_usage
ORDER BY
SUM_TIMER_WAIT DESC;
4、查看哪个索引从来没有使用过？
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME
FROM
`performance_schema`.table_io_waits_summary_by_index_usage
WHERE
INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
AND OBJECT_SCHEMA <> 'my'
ORDER BY
OBJECT_SCHEMA,
OBJECT_NAME;
## 三、统计信息(等待事件维度)
1、查看哪个等待事件消耗的时间最多？
SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT,
AVG_TIMER_WAIT
FROM
`performance_schema`.events_waits_summary_global_by_event_name
WHERE
event_name != 'idle'
ORDER BY
SUM_TIMER_WAIT DESC;
## 四、用户、连接类
1、查看每个客户端IP过来的连接消耗资源情况。
select * from sys.host_summary;
2、查看每个用户消耗资源情况
select * from sys.user_summary;
3、查看当前连接情况（有多少连接就应该有多少行）
select host,current_connections,statements from sys.host_summary;
4、查看当前正在执行的和执行show full processlist的结果差不多
select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session;
5、查看总共分配了多少内存
select * from sys.memory_global_total;
select * from sys.memory_global_by_current_bytes;
6、每个库（database）占用多少buffer pool
select * from sys.innodb_buffer_stats_by_schema order by allocated desc;
7、统计每张表具体在InnoDB中具体的情况，比如占多少页？
select * from sys.innodb_buffer_stats_by_table;
8、查询每个连接分配了多少内存利用session表和memory_by_thread_by_current_bytes分配表进行关联查询。
SELECT
b.USER,
current_count_used,
current_allocated,
current_avg_alloc,
current_max_alloc,
total_allocated,
current_statement
FROM
sys.memory_by_thread_by_current_bytes a,
sys.SESSION b
WHERE
a.thread_id = b.thd_id;
9、查看表自增字段最大值和当前值，有时候做数据增长的监控，可以作为参考
select * from sys.schema_auto_increment_columns;
10、My索引使用情况统计
select * from sys.schema_index_statistics order by rows_selected desc;
11、My中有哪些冗余索引和无用索引若库中展示没有冗余索引，则没有数据；当有联合索引idx_abc(a,b,c)和idx_a(a)，那么idx_a就算冗余索引了。
select * from sys.schema_redundant_indexes;
12、查看库级别的锁信息，这个需要先打开MDL锁的监控：
--打开MDL锁监控
update performance_schema.setup_instruments set enabled='YES',TIMED='YES' where name='wait/lock/metadata//mdl';
select * from sys.schema_table_lock_waits;
13、My内部有多个线程在运行，线程类型及数量
select user,count(*) from sys.`processlist` group by user;
14、查看My自增id的使用情况
SELECT
table_schema,
table_name,
ENGINE,
Auto_increment
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA NOT IN ( "INFORMATION_SCHEMA", "PERFORMANCE_SCHEMA", "MY", "SYS" );
15、找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀 （此处 5分钟 可根据自己的需要调整标红处）
可复制查询结果到控制台，直接执行，杀死堵塞进程
select` `concat(``'kill '``, id, ``';'``) ``from` `information_schema.processlist ``where` `Command != ``'Sleep'` `and` `Time` `> 300 ``order` `by` `Time` `desc``;
16、按客户端 IP 分组，看哪个客户端的链接数最多
select` `client_ip,``count``(client_ip) ``as` `client_num ``from` `(``select` `substring_index(host,``':'` `,1) ``as` `client_ip ``from` `information_schema.processlist ) ``as` `connect_info ``group` `by` `client_ip ``order` `by` `client_num ``desc``;
17、查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
select` `* ``from` `information_schema.processlist ``where` `Command != ``'Sleep'` `order` `by` `Time` `desc``;
18\My查看正在运行的
完整
SELECT a.*, c.thread_id, c._text from information_schema.processlist a
LEFT JOIN performance_schema.threads b on a.id = b.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current c on c.THREAD_ID = b.THREAD_ID;
19\my 查询没有主键的表
SELECT a.TABLE_SCHEMA, a.TABLE_NAME FROM ( SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ( 'my', 'information_schema', 'performance_schema', 'sys', 'sysdb' )) AS a LEFT JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_SCHEMA NOT IN ( 'my', 'information_schema', 'performance_schema', 'sys', 'sysdb' )) AS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME WHERE b.TABLE_NAME IS NULL;
1、查看数据库中不为 InnoDB 引擎的表
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE
TABLE_SCHEMANOT IN ('sys', 'my', 'performance_schema', 'information_schema',
'test') AND ENGINE != 'InnoDB';
2、查看数据库中表的大小及数据量
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, CONCAT(round((DATA_LENGTH +
INDEX_LENGTH) /1024 / 1024, 2), 'MB') as data FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('sys', 'my', 'performance_schema',
'information_schema', 'test') ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;
3、查找数据库中无显式主键索引的表
SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.TABLES t WHERE
(t.TABLE_SCHEMA, t.TABLE_NAME)NOT IN (SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.COLUMNS WHERE COLUMN_KEY = 'PRI') AND t.TABLE_SCHEMA NOT
IN ('sys', 'my', 'performance_schema', 'information_schema', 'test');
4、查找数据库中主键为联合主键的表
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY
SEQ_IN_INDEX SEPARATOR',') cols, MAX(SEQ_IN_INDEX) len FROM
information_schema.STATISTICSWHERE INDEX_NAME = 'PRIMARY' AND TABLE_SCHEMA NOT
IN ('sys', 'my', 'performance_schema', 'information_schema', 'test') GROUP BY
TABLE_SCHEMA, TABLE_NAMEHAVING len > 1;
5、查找数据库中不为自增主键的表
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE
TABLE_SCHEMANOT IN ('sys', 'my', 'performance_schema', 'information_schema',
'test') AND (TABLE_SCHEMA,TABLE_NAME) NOT IN (SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN ('sys', 'my',
'performance_schema', 'information_schema', 'test') AND IS_NULLABLE = 'NO' AND
COLUMN_TYPELIKE '%int%' AND COLUMN_KEY = 'PRI' AND EXTRA = 'auto_increment');
6、查看数据库中存在外键约束的表
SELECT c.TABLE_SCHEMA, c.REFERENCED_TABLE_NAME, c.REFERENCED_COLUMN_NAME, c
.TABLE_NAME, c.COLUMN_NAME, c.CONSTRAINT_NAME, t.TABLE_COMMENT, r.UPDATE_RULE, r
.DELETE_RULE FROM information_schema.KEY_COLUMN_USAGE c JOIN information_schema
.TABLES t ON t.TABLE_NAME = c.TABLE_NAME JOIN information_schema.REFERENTIAL
_CONSTRAINTS r ON r.TABLE_NAME = c.TABLE_NAME AND r.CONSTRAINT_NAME = c
.CONSTRAINT_NAME AND r.REFERENCED_TABLE_NAME = c.REFERENCED_TABLE_NAME WHERE c
.REFERENCED_TABLE_NAME IS NOT NULL;
7、查找数据库中低区分度索引(区分度小于0.1)
SELECT p.TABLE_SCHEMA, p.TABLE_NAME, c.INDEX_NAME, c.car, p.car total FROM (
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(CARDINALITY) car FROM
information_schema.STATISTICSWHERE INDEX_NAME != 'PRIMARY' AND TABLE_SCHEMA NOT
IN ('sys', 'my', 'performance_schema', 'information_schema', 'test') GROUP BY
TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) cINNER JOIN (SELECT TABLE_SCHEMA,
TABLE_NAME,MAX(CARDINALITY) car from information_schema.STATISTICS WHERE
INDEX_NAME ='PRIMARY' AND TABLE_SCHEMA NOT IN ('sys', 'my',
'performance_schema', 'information_schema', 'test') GROUP BY TABLE_SCHEMA,
TABLE_NAME) pON c.TABLE_NAME = p.TABLE_NAME AND c.TABLE_SCHEMA = p.TABLE_SCHEMA
WHERE p.car > 0 AND c.car / p.car < 0.1;
8、查找数据库中重复索引前缀的索引
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, a.cols, b.INDEX_NAME, b.cols
FROM (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| ',
GROUP_CONCAT(COLUMN_NAMEORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') AS cols
FROM information_schema.STATISTICS WHERE TABLE_SCHEMA NOT IN ('sys', 'my',
'performance_schema', 'information_schema', 'test') AND INDEX_NAME != 'PRIMARY'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) a INNER JOIN (SELECT
TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| ', GROUP_CONCAT(COLUMN_NAME
ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') AS cols FROM
information_schema.STATISTICSWHERE TABLE_SCHEMA NOT IN ('sys', 'my',
'performance_schema', 'information_schema', 'test') AND INDEX_NAME != 'PRIMARY'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) b ON a.TABLE_NAME = b.TABLE_NAME
AND a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.cols LIKE CONCAT(b.cols, '%') AND
a.INDEX_NAME != b.INDEX_NAME;
9、查找数据库中包索引重复包含主键列的索引
SELECT a.*, b.pk FROM (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| '
, GROUP_CONCAT(COLUMN_NAMEORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') cols
FROM information_schema.STATISTICS WHERE INDEX_NAME != 'PRIMARY' AND
TABLE_SCHEMANOT IN ('sys', 'my', 'performance_schema', 'information_schema',
'test') GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) a INNER JOIN (SELECT
TABLE_SCHEMA, TABLE_NAME, CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY
SEQ_IN_INDEX SEPARATOR' | '), ' |') pk FROM information_schema.STATISTICS WHERE
INDEX_NAME ='PRIMARY' AND TABLE_SCHEMA NOT IN ('sys', 'my',
'performance_schema', 'information_schema', 'test') GROUP BY TABLE_SCHEMA,
TABLE_NAME) bON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.cols LIKE CONCAT('%', b.pk, '%');
10、查找数据库中没有被使用的索引
SELECT a.OBJECT_SCHEMA, a.OBJECT_NAME, a.INDEX_NAME, b.TABLE_ROWS FROM
performance_schema.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE aINNER JOIN
information_schema.TABLES bON a.OBJECT_SCHEMA = b.TABLE_SCHEMA AND
a.OBJECT_NAME = b.TABLE_SCHEMAWHERE a.INDEX_NAME IS NOT NULL AND a.INDEX_NAME !=
'PRIMARY' AND a.COUNT_STAR = 0 AND OBJECT_SCHEMA NOT IN ('sys', 'my',
'performance_schema', 'information_schema') ORDER BY OBJECT_SCHEMA, OBJECT_NAME;
11、查看数据库中的锁请求信息
SELECT r.TRX_ISOLATION_LEVEL, r.TRX_ID WAITING_TRX_ID, r.TRX_MY_THREAD_ID
WAITING_TRX_THREAD, r.TRX_STATE WAITING_TRX_STATE, lr.LOCK_MODE
WAITING_TRX_LOCK_MODE, lr.LOCK_TYPE WAITING_TRX_LOCK_TYPE, lr.LOCK_TABLE
WAITING_TRX_LOCK_TABLE, lr.LOCK_INDEX WAITING_TRX_LOCK_INDEX, r.TRX_QUERY
WAITING_TRX_QUERY, b.TRX_ID BLOCKING_TRX_ID, b.TRX_MY_THREAD_ID
BLOCKING_TRX_THREAD, b.TRX_STATE BLOCKING_TRX_STATE, lb.LOCK_MODE
BLOCKING_TRX_LOCK_MODE, lb.LOCK_TYPE BLOCKING_TRX_LOCK_TYPE, lb.LOCK_TABLE
BLOCKING_TRX_LOCK_TABLE, lb.LOCK_INDEX BLOCKING_TRX_LOCK_INDEX, 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 INNER JOIN
information_schema.INNODB_LOCKS lb ON lb.LOCK_TRX_ID = W.BLOCKING_TRX_ID INNER
JOIN information_schema.INNODB_LOCKS lr ON lr.LOCK_TRX_ID = W.REQUESTING_TRX_ID;
注：本文提供的 脚本只适应特定的数据库版本，不代表所有数据库版本。
1.数据库大表信息查看
统计某库下各表大小 不要存在过大的表信息。本身分配内存有限，过大的表会不停地刷新新旧数据，IO交付频繁，导致性能衰减。
SELECT TABLE_SCHEMA,
TABLE_NAME TABLE_NAME, TABLE_ROWS,
CONCAT(ROUND(data_length / (1024 * 1024), 2),'M') data_length,
CONCAT(ROUND(index_length / (1024 * 1024), 2),'M') index_length,
CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),2),'M') total_size,
engine
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
'performance_schema',
'sys',
'my')
ORDER BY (data_length + index_length) DESC LIMIT 10;
10 rows in set (0.20 sec)
2.存储引擎
存储引擎分布，innodb引擎最适合因为支持事务，行锁级别。
SELECT TABLE_SCHEMA,
ENGINE,
COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
'PERFORMANCE_SCHEMA',
'SYS',
'MY')
AND TABLE_TYPE='BASE TABLE'
GROUP BY TABLE_SCHEMA,
ENGINE;
非 INNODB 存储引擎表
SELECT TABLE_SCHEMA,
TABLE_NAME,
TABLE_COLLATION,
ENGINE,
TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
'SYS',
'MY',
'PERFORMANCE_SCHEMA')
AND TABLE_TYPE='BASE TABLE'
AND ENGINE NOT IN ('INNODB')
ORDER BY TABLE_ROWS DESC ;
3.主键
无主键、无唯一键表。复制主键最重要，数据操作主键效率高。
SELECT T1.TABLE_SCHEMA,
T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
'MY',
'INFORMATION_SCHEMA',
'PERFORMANCE_SCHEMA')
AND T2.TABLE_TYPE='BASE TABLE'
GROUP BY T1.TABLE_SCHEMA,
T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';
4.not utf8 table
偏生字成乱码，表情符失效问题。
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION
from information_schema.TABLES
WHERE TABLE_COLLATION NOT LIKE 'utf8%'
AND table_schema NOT IN ('information_schema' ,'my','performance_schema', 'sys');
5.字符集验证
表之间Join字符集不对称，导致索引失效。
参看系统字符集：
my> show global variables like 'collation%';
跟系统字符集不一样的数据库：
SELECT b.SCHEMA_NAME, b.DEFAULT_CHARACTER_SET_NAME, b.DEFAULT_COLLATION_NAME
from information_schema.SCHEMATA b
WHERE b.SCHEMA_NAME not in ('information_schema' ,'my','performance_schema', 'sys')
AND b.DEFAULT_COLLATION_NAME<>@@collation_server ;
跟系统字符集不一样的表和字段:
select distinct tschema,tname,tcoll
from
(
select a.TABLE_SCHEMA as tschema , a.TABLE_NAME as tname,a.TABLE_COLLATION as tcoll
from information_schema.TABLES a
WHERE a.TABLE_SCHEMA not in ('information_schema' ,'my','performance_schema', 'sys')
and a.TABLE_COLLATION<>@@collation_server
union
select a.TABLE_SCHEMA as tschema, TABLE_NAME as tname, a.COLLATION_NAME as tcoll
from information_schema.COLUMNS a
WHERE a.TABLE_SCHEMA not in ('information_schema' ,'my','performance_schema', 'sys')
and a.COLLATION_NAME<>@@collation_server ) as aa ;
6.存储过程&函数
存储过程 函数查看，确实影响my 处理能力，后期也不好维护。
##My5.7
SELECT db,type,count(*)
FROM my.proc
WHERE db not in ('my','information_schema','performance_schema','sys')
AND type='PROCEDURE'
GROUP BY db, type;
##My8.0
SELECT Routine_schema, Routine_type
FROM information_schema.Routines
WHERE Routine_schema not in ('my','information_schema','performance_schema','sys')
AND ROUTINE_TYPE='PROCEDURE'
GROUP BY Routine_schema, Routine_type;
7.统计视图
确实影响my 处理能力，后期也不好维护。特别是ddl变更要注意
SELECT TABLE_SCHEMA , COUNT(TABLE_NAME)
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA not in ('my','information_schema','performance_schema','sys')
GROUP BY TABLE_SCHEMA ;
8.自增主键查看
主要考虑自增键超出范围，需要检查一下
SELECT infotb.TABLE_SCHEMA ,
infotb.TABLE_NAME,
infotb.AUTO_INCREMENT,
infocl.COLUMN_TYPE ,
infocl.COLUMN_NAME
FROM information_schema.TABLES as infotb INNER JOIN information_schema.COLUMNS infocl
ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA
AND infotb.TABLE_NAME = infocl.TABLE_NAME
AND infocl.EXTRA='auto_increment';
自增主键使用情况统计：
SELECT infotb.TABLE_SCHEMA , infotb.TABLE_NAME, infotb.AUTO_INCREMENT,infocl.COLUMN_TYPE
FROM information_schema.TABLES as infotb INNER JOIN information_schema.COLUMNS infocl
ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA
AND infotb.TABLE_NAME = infocl.TABLE_NAME
AND infocl.EXTRA='auto_increment';
9.分区表
尽量避免分区表，分区表性能问题：体现在分区锁，初期访问加载所有分区
查看实例中的分区表相关信息
SELECT TABLE_SCHEMA,
TABLE_NAME,
count(PARTITION_NAME) AS PARTITION_COUNT,
sum(TABLE_ROWS) AS TABLE_TOTAL_ROWS,
CONCAT(ROUND(SUM(DATA_LENGTH) / (1024 * 1024), 2),'M') DATA_LENGTH,
CONCAT(ROUND(SUM(INDEX_LENGTH) / (1024 * 1024), 2),'M') INDEX_LENGTH,
CONCAT(ROUND(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)) / (1024 * 1024),2),'M') TOTAL_SIZE
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA NOT IN ('sys',
'my',
'INFORMATION_SCHEMA',
'performance_schema')
AND PARTITION_NAME IS NOT NULL
GROUP BY TABLE_SCHEMA,
TABLE_NAME
ORDER BY sum(DATA_LENGTH + INDEX_LENGTH) DESC ;
查看某分区表具体信息，此处以库名为 db、表名为 e 的分区表为例
SELECT TABLE_SCHEMA,
TABLE_NAME,
PARTITION_NAME,
PARTITION_EXPRESSION,
PARTITION_METHOD,
PARTITION_DESCRIPTION,
TABLE_ROWS,
CONCAT(ROUND(DATA_LENGTH / (1024 * 1024), 2),'M') DATA_LENGTH,
CONCAT(ROUND(INDEX_LENGTH / (1024 * 1024), 2),'M') INDEX_LENGTH,
CONCAT(ROUND(ROUND(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024),2),'M') TOTAL_SIZE
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA NOT IN ('sys',
'my',
'INFORMATION_SCHEMA',
'performance_schema')
AND PARTITION_NAME IS NOT NULL
AND TABLE_SCHEMA='db'
AND TABLE_NAME='e';
10.计划任务
在不自觉中，自动执行。确认无法维护。
SELECT EVENT_SCHEMA,EVENT_NAME
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA not in ('my','information_schema','performance_schema','sys') ;
总结
以上合规检查已经为后期排除了很多隐患。做好上线数据库设计方面合规检查，必不可少的流程。
优化、分析My表读写、索引等操作的语句效率优化问题
为什么要优化：
随着实际项目的启动，数据库经过一段时间的运行，最初的数据库设置，会与实际数据库运行性能会有一些差异，这时我们 就需要做一个优化调整。
数据库优化这个课题较大，可分为四大类：
》主机性能
》内存使用性能
》网络传输性能
》语句执行性能【软件工程师】
下面列出一些数据库优化方案：
（01）选择最有效率的表名顺序（笔试常考）
数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表放在最后，如果有3个以上的表连接查询,那就需要选择那个被其他表所引用的表放在最后。
例如：查询员工的编号，姓名，工资，工资等级，部门名
select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade,dept,emp
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)
1)如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推
2)如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推
（02）WHERE子句中的连接顺序（笔试常考）
数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。
例如：查询员工的编号，姓名，工资，部门名
select emp.empno,emp.ename,emp.sal,dept.dname
from emp,dept
where (emp.deptno = dept.deptno) and (emp.sal > 1500)
（03）SELECT子句中避免使用*号
数据库在解析的过程中,会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间
select empno,ename from emp;
（04）用TRUNCATE替代DELETE
（05）尽量多使用COMMIT
因为COMMIT会释放回滚点
（06）用WHERE子句替换HAVING子句
WHERE先执行，HAVING后执行
（07）多使用内部函数提高效率
（08）使用表的别名
salgrade s
（09）使用列的别名
ename e
直接上代码：
反映表的读写压力
SELECT file_name AS file,
count_read,
sum_number_of_bytes_read AS total_read,
count_write,
sum_number_of_bytes_write AS total_written,
(sum_number_of_bytes_read + sum_number_of_bytes_write) AS total
FROM performance_schema.file_summary_by_instance
ORDER BY sum_number_of_bytes_read+ sum_number_of_bytes_write DESC;
反映文件的延迟
SELECT (file_name) AS file,
count_star AS total,
CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') AS total_latency,
count_read,
CONCAT(ROUND(sum_timer_read / 1000000000000, 2), 's') AS read_latency,
count_write,
CONCAT(ROUND(sum_timer_write / 3600000000000000, 2), 'h')AS write_latency
FROM performance_schema.file_summary_by_instance
ORDER BY sum_timer_wait DESC;
table 的读写延迟
SELECT object_schema AS table_schema,
object_name AS table_name,
count_star AS total,
CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') as total_latency,
CONCAT(ROUND((sum_timer_wait / count_star) / 1000000, 2), 'us') AS avg_latency,
CONCAT(ROUND(max_timer_wait / 1000000000, 2), 'ms') AS max_latency
FROM performance_schema.objects_summary_global_by_type
ORDER BY sum_timer_wait DESC;
查看表操作频度
SELECT object_schema AS table_schema,
object_name AS table_name,
count_star AS rows_io_total,
count_read AS rows_read,
count_write AS rows_write,
count_fetch AS rows_fetchs,
count_insert AS rows_inserts,
count_update AS rows_updates,
count_delete AS rows_deletes,
CONCAT(ROUND(sum_timer_fetch / 3600000000000000, 2), 'h') AS fetch_latency,
CONCAT(ROUND(sum_timer_insert / 3600000000000000, 2), 'h') AS insert_latency,
CONCAT(ROUND(sum_timer_update / 3600000000000000, 2), 'h') AS update_latency,
CONCAT(ROUND(sum_timer_delete / 3600000000000000, 2), 'h') AS delete_latency
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY sum_timer_wait DESC ;
索引状况
SELECT OBJECT_SCHEMA AS table_schema,
OBJECT_NAME AS table_name,
INDEX_NAME as index_name,
COUNT_FETCH AS rows_fetched,
CONCAT(ROUND(SUM_TIMER_FETCH / 3600000000000000, 2), 'h') AS select_latency,
COUNT_INSERT AS rows_inserted,
CONCAT(ROUND(SUM_TIMER_INSERT / 3600000000000000, 2), 'h') AS insert_latency,
COUNT_UPDATE AS rows_updated,
CONCAT(ROUND(SUM_TIMER_UPDATE / 3600000000000000, 2), 'h') AS update_latency,
COUNT_DELETE AS rows_deleted,
CONCAT(ROUND(SUM_TIMER_DELETE / 3600000000000000, 2), 'h')AS delete_latency
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY sum_timer_wait DESC;
全表扫描情况
SELECT object_schema,
object_name,
count_read AS rows_full_scanned
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NULL
AND count_read > 0
ORDER BY count_read DESC;
没有使用的index
SELECT object_schema,
object_name,
index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema not in ('my','v_monitor')
AND index_name <> 'PRIMARY'
ORDER BY object_schema, object_name;
糟糕的问题摘要
SELECT (DIGEST_TEXT) AS query,
SCHEMA_NAME AS db,
IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,
COUNT_STAR AS exec_count,
SUM_ERRORS AS err_count,
SUM_WARNINGS AS warn_count,
(SUM_TIMER_WAIT) AS total_latency,
(MAX_TIMER_WAIT) AS max_latency,
(AVG_TIMER_WAIT) AS avg_latency,
(SUM_LOCK_TIME) AS lock_latency,
format(SUM_ROWS_SENT,0) AS rows_sent,
ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
SUM_ROWS_EXAMINED AS rows_examined,
ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
SUM_CREATED_TMP_TABLES AS tmp_tables,
SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
SUM_SORT_ROWS AS rows_sorted,
SUM_SORT_MERGE_PASSES AS sort_merge_passes,
DIGEST AS digest,
FIRST_SEEN AS first_seen,
LAST_SEEN as last_seen
FROM performance_schema.events_statements_summary_by_digest d
where d
ORDER BY SUM_TIMER_WAIT DESC
limit 20;