可以关注以下两个视图sys.io_global_by_file_by_bytes、sys.x$io_global_by_file_by_bytes
或者表performance_schema.file_summary_by_instance
sys.io_global_by_file_by_bytes | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
file | varchar(512) | YES | NULL | ||
count_read | bigint(20) unsigned | NO | NULL | ||
total_read | text | YES | NULL | ||
avg_read | text | YES | NULL | ||
count_write | bigint(20) unsigned | NO | NULL | ||
total_written | text | YES | NULL | ||
avg_write | text | YES | NULL | ||
total | text | YES | NULL | ||
write_pct | decimal(26,2) | NO | 0.00 |
sys.x$io_global_by_file_by_bytes | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
file | varchar(512) | NO | NULL | ||
count_read | bigint(20) unsigned | NO | NULL | ||
total_read | bigint(20) | NO | NULL | ||
avg_read | decimal(23,4) | NO | 0.0000 | ||
count_write | bigint(20) unsigned | NO | NULL | ||
total_written | bigint(20) | NO | NULL | ||
avg_write | decimal(23,4) | NO | 0.0000 | ||
total | bigint(21) | NO | 0 | ||
write_pct | decimal(26,2) | NO | 0.00 |
performance_schema.file_summary_by_instance | |||||
---|---|---|---|---|---|
Field | Type | Null | Key | Default | Extra |
FILE_NAME | varchar(512) | NO | NULL | ||
EVENT_NAME | varchar(128) | NO | NULL | ||
OBJECT_INSTANCE_BEGIN | bigint(20) unsigned | NO | NULL | ||
COUNT_STAR | bigint(20) unsigned | NO | NULL | ||
SUM_TIMER_WAIT | bigint(20) unsigned | NO | NULL | ||
MIN_TIMER_WAIT | bigint(20) unsigned | NO | NULL | ||
AVG_TIMER_WAIT | bigint(20) unsigned | NO | NULL | ||
MAX_TIMER_WAIT | bigint(20) unsigned | NO | NULL | ||
COUNT_READ | bigint(20) unsigned | NO | NULL | ||
SUM_TIMER_READ | bigint(20) unsigned | NO | NULL | ||
MIN_TIMER_READ | bigint(20) unsigned | NO | NULL | ||
AVG_TIMER_READ | bigint(20) unsigned | NO | NULL | ||
MAX_TIMER_READ | bigint(20) unsigned | NO | NULL | ||
SUM_NUMBER_OF_BYTES_READ | bigint(20) | NO | NULL | ||
COUNT_WRITE | bigint(20) unsigned | NO | NULL | ||
SUM_TIMER_WRITE | bigint(20) unsigned | NO | NULL | ||
MIN_TIMER_WRITE | bigint(20) unsigned | NO | NULL | ||
AVG_TIMER_WRITE | bigint(20) unsigned | NO | NULL | ||
MAX_TIMER_WRITE | bigint(20) unsigned | NO | NULL | ||
SUM_NUMBER_OF_BYTES_WRITE | bigint(20) | NO | NULL | ||
COUNT_MISC | bigint(20) unsigned | NO | NULL | ||
SUM_TIMER_MISC | bigint(20) unsigned | NO | NULL | ||
MIN_TIMER_MISC | bigint(20) unsigned | NO | NULL | ||
AVG_TIMER_MISC | bigint(20) unsigned | NO | NULL | ||
MAX_TIMER_MISC | bigint(20) unsigned | NO | NULL |
更多参考
https://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-file-by-bytes.html
一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句
1,slow_query_log
这个参数设置为ON,可以捕获执行时间超过一定数值的SQL语句。
2,long_query_time
当SQL语句执行时间超过此数值时,就会被记录到日志中,建议设置为1或者更短。
3,slow_query_log_file
记录日志的文件名。
4,log_queries_not_using_indexes
这个参数设置为ON,可以捕获到所有未使用索引的SQL语句,尽管这个SQL语句有可能执行得挺快。
二、检测mysql中sql语句的效率的方法
1、通过查询日志
(1)、Windows下开启MySQL慢查询
MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上
代码如下
log-slow-queries = F:/MySQL/log/mysqlslowquery。log
long_query_time = 2
(2)、Linux下启用MySQL慢查询
MySQL在Windows系统中的配置文件一般是是my.cnf找到[mysqld]下面加上
代码如下
log-slow-queries=/data/mysqldata/slowquery。log
long_query_time=2
说明
log-slow-queries = F:/MySQL/log/mysqlslowquery。
为慢查询日志存放的位置,一般这个目录要有MySQL的运行帐号的可写权限,一般都将这个目录设置为MySQL的数据存放目录;
long_query_time=2中的2表示查询超过两秒才记录;
2.show processlist 命令
SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。