03-MySql性能监控工具, 性能调优 EXPLAIN 索引优化 trace监控 Sys schema视图调用

性能监控工具

-- 连接MySQL服务器的次数
SHOW GLOBAL STATUS LIKE 'Connections';
-- MySQL服务器的上线时间
SHOW GLOBAL STATUS LIKE 'Uptime';
-- 慢查询的次数
SHOW GLOBAL STATUS LIKE 'Slow_queries';
--  Select查询返回的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';
-- 执行INSERT操作插入的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted';
-- 执行UPDATE操作更新的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_updated';
-- 执行DELETE操作删除的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_deleted';
-- 查询操作的次数
SHOW GLOBAL STATUS LIKE 'Com_select';
-- 插入操作的次数对于批量插入的 INSERT 操作，只累加一次
SHOW GLOBAL STATUS LIKE 'Com_insert';
-- 更新操作的次数
SHOW GLOBAL STATUS LIKE 'Com_update';
-- 删除操作的次数
SHOW GLOBAL STATUS LIKE 'Com_delete';

统计sql执行扫描页数

-- 案例1
SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;
-- 查看最近一条记录查询成本, (查询的页数)
SHOW STATUS LIKE 'last_query_cost';
-- +-----------------+----------+
-- | Variable_name | Value | 
-- +-----------------+----------+
-- | Last_query_cost | 1.000000 | 
-- +-----------------+----------+
-- 可以看出上面的sql语句只查询了一页数据, 所以成本比较低

-- 案例2
SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id BETWEEN 900001 AND 900100;
-- 查询上面sql执行成本
SHOW STATUS LIKE 'last_query_cost';
-- +-----------------+-----------+
-- | Variable_name | Value | 
-- +-----------------+-----------+
-- | Last_query_cost | 21.134453 | 
-- +-----------------+-----------+
-- 可以看到这个 sql需要加载 22页的数据

慢查询日志

-- 查看慢查询是否打开
show GLOBAL variables like 'slow_query_log%'
-- 开启慢查询日志
set global slow_query_log='ON';

-- 查看慢查询达到多少秒后记录
show variables like '%long_query_time%';
-- 设置慢查询秒数
set global long_query_time = 1

-- 查看当前系统有多少条满查询sql
SHOW GLOBAL STATUS LIKE '%Slow_queries%';


-- 永久设置修改my.cnf
[mysqld]
slow_query_log=NO
slow_query_log_file=/var/mysql/slow.log
long_query_time=3
log_output=FILE


-- 获得慢查询日志后可以打开看也可以使用mysqldumpslow 工具查询慢sql

查看sql执行成本

--  查询是否开启成本计算
show variables like 'profiling';
--  开启成本计算 
set profiling = 'ON';
-- 查询执行的sql
show profiles;
-- 查询某一条sql的执行成本 57条
show profile  for query 57;
-- 查询最后执行的这一条执行成本
show profile

EXPLAIN ,sql的执行计划, 工具参数说明

参数	描述
table	对应几张表
id	每个select 对应一个ID,有时候我们写的子查询优化器直接给我吗优化成对表连接了, ID越大表示越先被执行, ID相同按照顺序执行
selectType	查询中不包含Union 或者 子查询的都叫 Simple; 不相关子查询里面的叫 SubQuery; 相关子查询叫 DEPENDENT SUBQUERY;外面的叫PRIMARY
type*	system; cons(参数为常量级别, 并且列有唯一索引); eq_ref(多表连接, 被驱动表是主键关联或有索引); ref当一个普通索引与常量查询的时候;index_merge (当两个列都有索引 都为单值索引的时候这两个索引都可以使用到);range (当我们使用 in 或者>< 的时候); index(当我们查询的字段再这个二级索引的时候就是遍历索引, 不用回表就能找到数据); all(全表扫描)
possible_keys	可能用到的索引
key	实际用到的索引
key_len	实际用到索引的长度字节, 主要针对联合索引, 越大越好, 列没有非空限制会多加一个字节记录非空状态, 列如果是变长字段会加2个字节记录变长字段列表(最大表示65535 长度 varchar最长为 65535 )
ref	表示查询的参数是什么有 const, func等
rows	预估找到行记录
filtered	根据过滤条件过滤后有多少百分比符合这个条件, 在多表连接中可以看出外部查询记录查询了多少, 预估内部查询需要执行多少次
extra	Using index(没有回表查询,直接从索引获得数据很好);Using filesort(没有使用索引的排序性能差) Using temporary(使用临时表了, 非常耗用性能)

其他工具

• 可以查询到执行计划的成本和详细信息
  EXPLAIN FORMAT=JSON SELECT * from table;
• 可以使用 Workbench 可以以图形化界面的方式进行sql的查询
• 展示查询优化器优化过后的sql语句
  SHOW WARNINGS\G
  在使用 EXPLAIN 分析后执行上面的语句可以查看执行优化器优化后的sql语句
• 分析优化器执行计划：trace

-- 开启trace追踪
SET optimizer_trace="enabled=on",end_markers_in_json=on; 
set optimizer_trace_max_mem_size=1000000;
-- 执行语句 
select * from student where id < 10;
-- 查看追踪结果
select * from information_schema.optimizer_trace\G

• Sys schema视图使用场景

-- 索引相关
#1. 查询冗余索引 
select * from sys.schema_redundant_indexes; 
#2. 查询未使用过的索引 
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况 
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname' ;

-- 表相关
# 1. 查询表的访问量 
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; 
# 2. 查询占用bufferpool较多的表 
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
# 3. 查看表的全表扫描情况 
select * from sys.statements_with_full_table_scans where db='dbname';

-- 语句相关
#1. 监控SQL执行的频率 
select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
#2. 监控使用了排序的SQL
 select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1; 
#3. 监控使用了临时表或者磁盘临时表的SQL 
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

-- IO相关
#1. 查看消耗磁盘IO的文件 
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

-- InnoDb相关
#1. 行锁阻塞情况 
select * from sys.innodb_lock_waits;