mysql性能排查

慢日志:找出耗时sql

-- 慢日志,slow_query_log 开关, slow_query_log_file 慢日志文件路径
show variables like 'slow%';

-- 开启慢日志
set global slow_query_log='ON';
-- 设置执行超过多久才记录慢日志
show variables like 'long_query_time';
set global long_query_time=5;

-- 已出现慢日志的次数
show global status like '%Slow_queries%';

explain: 分析sql

type	sql
all	explain select * from fund; explain select url from fund;
index	explain select id from fund; explain select code from fund; explain select name from fund;
range	explain select * from fund where id between 35 and 70;
ref	联表才有
eq_ref	联表才有
const	explain select * from fund where id =35; explain select * from fund where code=‘011475’;

• possible_keys: sql可能使用的索引, Key: sql实际使用的索引, rows(rows_examined): 估算的找到所需的结果所需要读取的行数
• filtered: 表示返回结果的行数占需读取行数的百分比, 值越大越好

extra	sql
Using index	explain select id from fund; explain select code from fund; explain select name from fund;
Null	explain select * from fund; explain select url from fund; explain select * from fund where id =35; explain select * from fund where code=‘011475’;
Using where	explain select * from fund where id between 35 and 70;

• Using where：表示优化器需要通过索引回表查询数据；
• Using index：表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；

mysql性能监控参数

mysql原理

• InnoDB主键聚集索引的叶子节点存储行记录,普通索引的叶子节点存储主键值。回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低

-- 展示所有连接情况
select * from information_schema.processlist where db='sum';
-- 版本
select version();
-- 耗时任意时间的sql
select sleep(6);
-- 5.8 隐藏索引
ALTER TABLE test.user ALTER INDEX name_index INVISIBLE;
ALTER TABLE test.user ALTER INDEX name_index VISIBLE;
-- 
select * from information_schema.processlist where db='sum';