mysql数据库管理系统读写性能_利用MySQL系统数据库做性能负载诊断

TOP SQL 统计

可以按照执行时间，阻塞时间，返回行数等等维度统计top sql。

另外可以按照时间筛选last_seen，可以统计最近某一段时间出现过的top sql

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECTschema_name,

digest_text,

count_star,

avg_timer_wait/1000000000000 ASavg_timer_wait,

max_timer_wait/1000000000000 ASmax_timer_wait,

sum_lock_time/count_star/1000000000000 ASavg_lock_time ,

sum_rows_affected/count_star ASavg_rows_affected,

sum_rows_sent/count_star ASavg_rows_sent ,

sum_rows_examined/count_star ASavg_rows_examined,

sum_created_tmp_disk_tables/count_star ASavg_create_tmp_disk_tables,

sum_created_tmp_tables/count_star ASavg_create_tmp_tables,

sum_select_full_join/count_star ASavg_select_full_join,

sum_select_full_range_join/count_star ASavg_select_full_range_join,

sum_select_range/count_star ASavg_select_range,

sum_select_range_check/count_star ASavg_select_range,

first_seen,

last_seenFROMperformance_schema.events_statements_summary_by_digestWHERE last_seen>date_add(NOW(), interval -1HOUR)ORDER BYmax_timer_wait--avg_timer_wait--sum_rows_affected/count_star--sum_lock_time/count_star--avg_lock_time--avg_rows_sent

DESClimit10;

需要注意的是，这个统计是按照MySQL执行一个事务消耗的资源做统计的，而不是一个语句，笔者一开始懵逼了一阵子，举个简单的例子。

参考如下，这里是循环写个数据的一个存储过程，调用方式就是call create_test_data(N)，写入N条测试数据。

比如call create_test_data(1000000)就是写入100W的测试数据，这个执行过程耗费了几分钟的时间，按照笔者的测试实例情况，avg_timer_wait的维度，绝对是一个TOP SQL。

但是在查询的时候，始终没有发现这个存储过程的调用被列为TOP SQL，后面尝试在存储过程内部加了一个事物，然后就顺利地收集到了整个TOP SQL.

因此说performance_schema.events_statements_summary_by_digest里面的统计，是基于事务的，而不是某一个批处理的执行时间的。

CREATE DEFINER=`root`@`%` PROCEDURE`create_test_data`(IN `loopcnt` INT)

LANGUAGE SQLNOTDETERMINISTICCONTAINSSQL

SQL SECURITY DEFINER

COMMENT''

BEGIN-- STARTTRANSACTION;while loopcnt>0doinsert into test_mrr(rand_id,create_date) values (RAND()*100000000,now(6));set loopcnt=loopcnt-1;end while;

--commit;END

另外一点比较有意思的是，这个系统表是为数不多的支持truncate的，当然它在内部，也是在不断收集的一个过程。

执行失败的SQL 统计

一直以为系统不会记录执行失败的\解析错误的SQL，比如想统计因为超时而执行失败的语句，后面才发现，这些信息，MySQL会完整地记录下来

这里会详细记录执行错误的语句，包括最终执行失败(超时之类的)，语法错误，执行过程中产生了警告之类的语句。用sum_errors>0 or sum_warnings>0去performance_schema.events_statements_summary_by_digest筛选一下即可。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;selectschema_name,

digest_text,

count_star,

first_seen,

last_seenfromperformance_schema.events_statements_summary_by_digestwhere sum_errors>0 or sum_warnings>0

order by last_seen desc;

Index使用情况统计

基于performance_schema.table_io_waits_summary_by_index_usage这个系统表，其统计的维度同样是“按照某个索引查询返回的行数的统计”。

可以按照哪些索引使用最多\最少等情况进行统计。

不过这个统计有一个给人潜在一个误区：

count_read,count_write,count_fetch,count_insert,count_update,count_delete统计了某个索引上使用到索引的情况下，受影响的行数，sum_timer_wait是累计在该索引上等待的时间。

如果使用到了该索引，但是没有数据受影响(就是没有DML语句的条件没有命中数据)，将count_***不会统计进来，但是sum_timer_wait会统计进来

这就存在一个容易受到误导的地方，这个索引明明没有命中过很多次，但是却产生了大量的timer_wait，索引看到类似的信息，也不能贸然删除索引。

等待事件统计

MySQL数据库中的任何一个动作，都需要等待(一定的时间来完成)，一共有超过1000个等待事件，分属不懂的类别，每个版本都不一样，且默认不是所有的等待事件都启用。

个人认为等待事件这个东西，仅做参考，不具备问题的诊断性，即便是再优化或者低负载的数据库，累计一段时间，某些事件仍旧会积累大量的等待事件。

这些事件的等待事件，不一定都是负面性的，比如事物的锁等待，是在并发执行过程中必然会生成的，这个等待事件的统计结果，也是累计的，单纯的看一个直接的值，不具备任何参考意义。

除非定期收集，做差值计算，根据实际情况，才具备参考意义。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT SUBSTRING_INDEX(NAME, '/', 1) as wait_type,COUNT(1)FROMperformance_schema.setup_instrumentsGROUP BY 1

ORDER BY 2 DESC;SELECTevent_name,

count_star,

sum_timer_waitFROMperformance_schema.events_waits_summary_global_by_event_nameWHERE event_name != 'idle'

order by sum_timer_wait desclimit100;

最后，需要注意的是，

1，MySQL提供的诸多的系统表(视图)中的数据，单纯的看这个值本身，因为它是一个累计值，个人觉得意义不大，尤其是avg_***，需要结合多方面的综合因素，做参考使用。

2，任何系统表的查询，都可能对系统性能的本身造成一定的影响，不要再对系统可能产生较大负面影响的情况下做数据的统计收集。

参考：

耐克的广告，竟然是这么的煽情

你能从一片空白里，看到可能吗？

有些人要看到证据，等有人做到了才敢出手。

但那些第一个行动的人，他们等过吗？

他们直接出手，不管有没有人做到过。

你能从一片空白里，看到可能吗？

不等别人，出手即证明。