MySQL监控

Posted on 2023-12-22 14:18 linFen 阅读(83) 评论(0) 编辑收藏举报

# 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;

explain输出字段说明：

1、id：这是SELECT的查询序列号

2、select_type：select_type就是select的类型，可以有以下几种：

SIMPLE：简单SELECT(不使用UNION或子查询等)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果。
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)

3、table：显示这一行的数据是关于哪张表的

4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。

结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行

6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

8、ref：显示使用哪个列或常数与key一起从表中选择行。

9、rows：显示MySQL认为它执行查询时必须检查的行数。

10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists:MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each
Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort:看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary:看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Using where:使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

硬件配置信息
检查项	操作命令
服务器名称	hostname
服务器型号	dmidecode \|grep "Product Name"
CPU	cat /proc/cpuinfo \| grep name \| cut -f2 -d: \| uniq -c
内存	cat /proc/meminfo
磁盘	df -h
ip地址	ifconfig -a
操作系统	lsb_release -a或cat /proc/version

检查项	操作命令	备注
CPU占用率	top	CPU每个线程占用率小于80%
CPU负载	uptime	CPU负载应小于10
内存占用率	free -m	内存使用率小于80%
内存swap使用率	free -m	swap使用率小于10%
磁盘使用率	df -ah	磁盘使用率小于80%
磁盘负载	iostat -xkd 1	util应在10%以内（或者top命令查看，%wa很高而%us偏低时，可能存在IO瓶颈）
系统开放端口	netstat -an\|grep LISTEN	只显示出提供对于服务的端口，无关的端口一律关闭
进程检查	ps aux\|more	关闭占用资源的无用进程

数据库巡检
检查项	操作命令	备注
进程检查	ps -ef \| grep mysqld	检查port以及datadir
数据库版本	select version()
超大库检查	du -sh 每个数据库目录
超大表检查	show table status	检查输出后的Rows、Data_length指标，Rows应小于10000000行
错误日志	show variables like '%log_error%';	查看文件中是否有ERROR的日志
慢查询日志	show variables like '%slow%';	检查慢日志是否开启，若开启，检查慢日志文件中的语句
重要参数检查	show variables like 'back_log%';	back_log 参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。
	show variables like 'max_allowed_packet%';	客户端和服务器均有自己的max_allowed_packet变量，因此，如你打算处理大的信息包，必须增加客户端和服务器上的该变量。一般情况下，服务器默认max-allowed-packet为1MB
	show variables like 'interactive_timeout%';	交互式连接超时时间(mysql工具、mysqldump等),参数默认值：28800秒（8小时）
	show variables like 'wait_timeout%';	非交互式连接超时时间，默认的连接mysql api程序,jdbc连接数据库等,参数默认值：28800秒（8小时）
	show variables like 'skip_name_resolve%';	使用该参数后可加快内网地址的请求
	show variables like 'max_connections%';	最大连接数。max_used_connections / max_connections * 100% （理想值≈ 85%）
	show variables like 'log_bin%';	binlog日志开启，能实时记录保存DML操作
	show variables like 'expire_logs_days%';	让mysql自动清理若干天前的binlog
	show variables like 'open_files_limit%';	文件打开限制数
	show variables like 'table_open_cache%';	打开表缓存大小
	show variables like 'thread_cache_size%';	线程池缓存大小
	show variables like 'sort_buffer_size%';	排序缓冲区大小
	show variables like 'join_buffer_size%';	内连接缓冲区大小
	show variables like 'innodb_buffer_pool_size%';	InnoDB缓冲池
	show variables like 'innodb_file_per_table%';	InnoDB独立表空间
	show variables like 'innodb_open_files%';	InnoDB打开文件数
	show variables like 'innodb_thread_concurrency%';	InnoDB并发线程
	show variables like 'innodb_flush_log_at_trx_commit%';	InnoDB将缓存中的redo日志回写到日志文件的设置
	show variables like 'innodb_log_buffer_size%';	InnoDB日志缓冲大小
	show variables like 'innodb_log_file_size%';	InnoDB日志文件大小
	show variables like 'innodb_log_files_in_group%';	InnoDB日志文件组
	show variables like 'general_log%';	日志功能是否开启
	show variables like 'log_error%';	错误日志位置
	show variables like 'slow_query_log%';	慢查询日志
	show variables like 'long_query_time%';	慢查询查询时间超多久会写日志
QPS检查	间隔执行 show status like 'queries'	通过两次的间隔时间做差值，计算QPS
读写比检查	show status like 'com_%'	读请求是com_select；写请求是com_insert； com_update;com_delete 通过统计读写的请求数，算出读写比例。
当前连接数检查	show status like '%Threads_connected%'	此值为当前连接数，应当远小于max_connections
最大连接数检查	show status like '%max_used_connections%'; show variables like '%max_connections%';	若max_used_connections逼近 max_connections，则需要调大max_connections
异常连接查询	show variables like '%Aborted%'	检查Aborted_clients以及Aborted_connects值是否正常
并发线程查询	show global status like '%threads_running%';	此值应小于10，如果过大，说明并发数太多，存在慢语句。
线程缓存池检查	show status like '%threads_created%'; show status like 'connections';	threads_created/connections为缓存未命中率，若此值过高，则需要调大thread_cache_size
运行线程状态查询	show full processlist;	查看当前并发线程是否状态正常
InnoDB Buffer Pool检查	show status like 'Innodb_buffer_pool_read_requests'; show status like 'Innodb_buffer_pool_reads';	Innodb_buffer_pool_reads/ Innodb_buffer_pool_read_requests为缓存未命中率，若此值过高，则需要调大 innodb_buffer_pool_size
锁等待检查	show full processlist;	检查state列是否存在wait for xxx lock的状态，如果有则存在锁事务；
InnoDB死锁检查	show engine innodb status;	查看LATEST DETECTED DEADLOCK输出段，若存在，则需要摘取相应的语句。
InnoDB长事务检查	show engine innodb status;	检查TRANSACTIONS输出段，看是否存在ACTIVE时间过长的事务，若存在，则需要关注
表缓存检查	show global status like '%opened_tables%'; show variables like '%table_open_cache%';	若opened_tables过大，则需要调大 table_open_cache值
查询缓存检查啊	show variables like '%query_cache%'	一般情况下，需要禁用query_cache。
临时表检查	show global status like '%tmp%';	如果Created_tmp_tables， Created_tmp_disk_tables或者 Created_tmp_files较高，则需要排查慢查询（子查询、排序、分组等）
binlog是否开启	show variables like 'log_bin'
复制检查	show slave status;	检查是否正常
备份检查	检查备份脚本或备份软件是否正常，检查备份文件是否可用

刷新页面返回顶部

luluping

公告

MySQL监控