mysql 慢查询毫秒_Mysql 慢查询优化实践

Mysql 慢查询优化实践

目标:

提高mysql运行效率,增加并发,提高响应速度

方案:

通过阿里云给的慢查询日志excel,对耗时长,开销大的sql语句进行优化,提升访问速度服务器运行效率

实践:

分析

阿里云给的数据库单日报表有以下字段

Create Time DBName

MySQL Total Execution Counts

MySQL Total Execution Times

Max Execution Time

Max Lock Time

Parse Total Row Counts

Parse Max Row Count

Return Total Row Counts

Return Max Row Count

SQL Text

hard ware- latencies

分别是

创建时间

数据库名

mysql总执行数目

muysql总执行耗时

最大执行耗时

最大锁耗时

解析总行数统计

解析最大行数

返回总计行数

返回最大行数

sql语句

硬件延迟

根据阿里云提供的慢查询记录,本次采用的优化策略如下:

查询次数超过100次/日的高频需求,按照最大查询/总查询用时最大,依次优化取得的优化收益最高.

第一条语句:

执行次数: 1114 最大耗时: 7 解析最大行数: 348325 返回最大行数 4

执行次数: 1114 最大耗时: 7 解析最大行数: 348325 返回最大行数 4

select id from appname_m_members where yiku_id = :1

可以看出,这个简单的sql不应该有这么大的解析行数,甚至最高要七秒钟.

初步判断没有在yiku_id这个字段加索引的可能性最大.现在我们需要寻求各种办法来验证下我们的猜测

分析

explain select id from appname_m_members where yiku_id = 1;

可以看到的确是没有给yiku_id增加索引.

索引的特点

对于查询操作能迅速缩小查询范围,减少row的数量,指数级提高查询速度点

对于写操作,因为需要维护索引的变更,有一定开销.如果遇到大量并发写入,会有负面影响.

在这个表用来记录我们微信用户和应用id的关系,所以读的操作较之写操作更多,所以能够增加索引.

#增加索引

ALTER TABLE `appname_m_members`

ADD INDEX `yiku_id` (`yiku_id`) ;

尝试增加索引之后,再次分析语句的执行

结果:

匹配范围 rows 从32w 降低到1

可以看到type从all的全表扫描变成ref的单个行的索引访问,rows从全表32w降为1,说明添加索引对这条语句产生了巨大效果.

第二条语句:

执行次数: 482 最大耗时: 15 解析最大行数: 764383 返回最大行数: 482

#执行次数: 482 最大耗时: 15 解析最大行数: 764383 返回最大行数: 482

select fullname as username , linkphone as userphone ,

`userimage` , `nickname` , `hospitalname` , `partmentname` ,

`doctortitle` , `iscertification` , `fullname`

from `users`

where `useruuid` = '597_f66e1cb79341cedf6f24aaf01fde8611' limit 1;

分析:

对其增加索引:

#增加索引

ALTER TABLE `users`

ADD INDEX `useruuid` (`useruuid`);

直接将扫描范围(rows)从72w降到了1,提升明显

结果:

匹配范围 rows 从72w 降低到1

第三条语句:

执行次数: 820 最大耗时: 10 解析最大行数: 167214 返回最大行数 1

#执行次数: 820 最大耗时: 10 解析最大行数: 167214 返回最大行数 1

select count ( postingid ) as postnum from mediposting

where isaudit != :1

and isgoodcase = :2

and postsection = :3

分析:

改变sql语句的顺序,按照最左原则修改如下

select count(postingid) as postnum from mediposting

where postsection = 1

and isgoodcase = 1

and isaudit != 1

结果:

主要使用的是 postsection 作为索引来统计总数,这部分无需优化.

##### 第四条语句:

执行次数: 482 最大耗时: 15 解析最大行数: 764383 返回最大行数: 482

##执行次数: 410 最大耗时: 10 解析最大行数:348325 返回最大行数 1

........

结果: 语句过长(2017个字符),嵌套了逻辑,暂不优化

第五条语句:

执行次数: 659 最大耗时: 6 解析最大行数:215115 返回最大行数 659

## 执行次数: 659 最大耗时: 6 解析最大行数:215115 返回最大行数 659

select `medigooddoc`.`docid` , `medig