mysql|03-MySql性能监控工具, 性能调优 EXPLAIN 索引优化 trace监控 Sys schema视图调用数据库|sql|mysql

性能监控工具

-- 连接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 profilefor 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;