Mysql 慢查询优化实践

news/2024/2/29 3:42:30

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` , `medigooddoc`.`docname` ,`medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,`medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,( initalscore+effectevaladd ) as `effectval` from `medigooddoc` where ( ( initalscore+effectevaladd ) > 80 ) order by rand ( ) limit 1 ;
分析:

rand()函数放在order by后面会被执行多次,优化方式:
求出随机id后,取得对应记录

select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,`medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,`medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,( initalscore+effectevaladd ) as `effectval` from `medigooddoc` where (initalscore+effectevaladd)  > 80and docid > ( RAND() * ((SELECT MAX(docid) FROM `medigooddoc`) -(SELECT MIN(docid) FROM `medigooddoc`))+(SELECT MIN(docid) FROM `medigooddoc`) )order by `docid` limit 1;

优化前语句:

可以看到扫描范围很大(rows) 120 770行.

可以看到

  • 查询范围最小(rows) 1
  • 获取最大值/最小值是直接从mysql查询优化器返回数据(extra).mysql文档中有以下解释:
The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned.

测试执行效率:

  • 执行10次 a: 2 941 ms b: 168 ms
  • 执行50次 a: 14 441 ms b: 828 ms
  • 执行100次a: 29 126 ms b: 1 645 ms

可以看到每百次运行时间已经从30s缩短到不到2秒,大大提高查询mysql响应速度.
但是还有个问题,总共100 000的id,原来的语句查询出的结果比较平衡,有过万也有几千,但是用这个语句后,总是出现小于一万的id,结果在我们预期之外.

修正概率偏差

方案1:

增加一次对数据库消耗不大的表查询

# php
$round = select max(docid) as max,min(docid) as min from medigooddoc;
$rand = rand($round['min'],$round['max']);
# sql
select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,`medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,`medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,( initalscore+effectevaladd ) as `effectval` from `medigooddoc` where (initalscore+effectevaladd)  > 80and docid > $randorder by `docid` limit 1;

这样的问题是:会多产生一个sql交互,数据库

方案2:

使用内连接 join 优化

#可用一
select `docid` ,`docname`,`doctitle` , `docimgurl` ,`docdep` , `dochospital` ,( initalscore+effectevaladd ) as `effectval` from `medigooddoc` as t1 join (select rand() * (select max(docid) from `medigooddoc`) as rand) as t2 where (t1.initalscore+t1.effectevaladd)  > 80and `t1`.`docid` >= t2.randorder by `docid` limit 1;

但是这样有一个问题:并不是完全平均落到每条记录上,因为记录并不是连续的

修正概率 rand * 数量范围,这样概率平均到整张表存在的记录中.

select `docid` ,`docname`,`doctitle` , `docimgurl` ,`docdep` , `dochospital` ,( initalscore+effectevaladd ) as `effectval` from `medigooddoc` as t1 join (select rand() * ((select max(docid) from `medigooddoc`)-(select min(docid) from `medigooddoc`))+(select min(docid) from `medigooddoc`)  as rand) as t2 where (t1.initalscore+t1.effectevaladd)  > 80and `t1`.`docid` >= t2.randorder by `docid` limit 1;

综合来说,因为方案1 产生了更多的数据库交互,因为我们的数据库是另一台服务器,网络连接开销是比较大的,额外的查询也会在高并发的时刻对数据库产生更大压力.

而方案2采用内连接的方式,仅需要一次数据库交互就能完成,最大最小值也是直接由mysql查询器返回,减少了种种数据库性能开销.故采用为最佳方案..

结果:

使用mysql保存的表结构信息替代了order rand()的低效率查询.

深入理解:

第六条语句:

执行次数: 729 最大耗时: 4秒 解析最大行数:130898 返回最大行数 2

select `medigooddoc`.`docid` , `medigooddoc`.`yikuid` 
from `medigooddoc` 
where ( yikuid = 597725 or yikuid = -597725 );
分析:

优化方案:

字段yikuid加索引

ALTER TABLE `medigooddoc`
ADD INDEX `YiKuID` (`YiKuID`);

再次执行explain分析

结果:

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

第七条语句

执行次数: 474 最大耗时: 5秒 解析最大行数:261797 返回最大行数 1

select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,`medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` from `medigooddoc` order by rand ( ) limit 1;
分析

方案

将获取一条随机记录 由order by rand() limit 1 改为 内连接方式

select `docid`, `docname`,`doctitle` , `docimgurl` from `medigooddoc` as  t1 inner join (select rand()  *((select MAX(docid) from `medigooddoc`)-(select MIN(docid) from `medigooddoc`))+(select MIN(docid) from `medigooddoc`)as rand) as t2 on t1.docid >= t2.randorder by docid limit 1;

再次执行explain分析

结果

用mysql存储的表信息替代了效率低下的order by rand()

第八条语句

执行次数: 136 最大耗时: 7秒 解析最大行数:301880 返回最大行数 1

select `searchrecords`.`searchid` , `searchrecords`.`searchnum` 
from `searchrecords` 
where ( searchtype = 0 ) and ( userid = 14 ) 
and ( searchmsg = '碳酸钙D3' );
分析

方案

索引的目的是为了缩小查询范围,通过文字内容的前三个字区分,通过userid进行区分,可以得到范围更精确的语句执行

ALTER TABLE searchrecords ADD INDEX searchmsg (searchmsg(5));
ALTER TABLE searchrecords ADD INDEX userid (userid);

通过文本前5个字建立索引来区分范围后,范围缩小到28个记录

再通过用户ID建立索引,进一步缩小范围,仅需要查找1条记录

分析索引对写入的影响
表主要用来记录用户搜索的高频词,主要的写操作时更新统计字段,这两个新增索引的字段并不会频繁更新,故索引开销不大.

结果

匹配范围从 29w 缩小到 1

第九条语句
select `projects`.`id` , `projects`.`guid` , 
`projects`.`getittime` , `projects`.`keywords` ,`projects`.`barcode` as `num` , `projects`.`goodcasedep` ,`projects`.`bingshi` , `pictures`.* from `projects` inner join `pictures` on projects.guid = pictures.projectid and pictures.filetype = :1 where ( islock != :2 ) and ( isgoodcase = :3 ) and ( ( goodcasedep like :4 or goodcasedep like :5 or goodcasedep like :6 or goodcasedep like :7 or goodcasedep like :8 or goodcasedep like :9 or goodcasedep like :10 or goodcasedep like :11 or goodcasedep like :12 or goodcasedep like :13 or goodcasedep like :14 or goodcasedep like :15 or goodcasedep like :16 or goodcasedep like :17 or goodcasedep like :18 or goodcasedep like :19 or goodcasedep like :20 or goodcasedep like :21 or goodcasedep like :22 or goodcasedep like :23 or goodcasedep like :24 or goodcasedep like :25 or goodcasedep like :26 or goodcasedep like :27 or goodcasedep like :28 or goodcasedep like :29 or goodcasedep like :30 or goodcasedep like :31 or goodcasedep like :32 or goodcasedep like :33 or goodcasedep like :34 or goodcasedep like :35 or goodcasedep like :36 or goodcasedep like :37 or goodcasedep like :38 or goodcasedep like :39 or goodcasedep like :40 or goodcasedep like :41 ) ) order by rand ( ) limit :42
结果:

暂不修改:超过字节限制

第十条语句

执行次数: 145 最大耗时: 2秒 解析最大行数:130898 返回最大行数 1

select `medigooddoc`.`isfollow` , `medigooddoc`.`isconsult` ,`medigooddoc`.`isphone` , `medigooddoc`.`isprivate` from `medigooddoc` where ( yikuid =  694 );
分析:

方案

增加索引

ALTER TABLE `medigooddoc` ADD INDEX YiKuID(`YiKuID`);

再次执行explain分析

结果

匹配范围从12w缩小到1

第十一条

执行次数: 148 最大耗时: 3秒 解析最大行数:74616 返回最大行数 30

select `magazinearticle`.`articleid` , 
`magazinearticle`.`articletitle` , 
`magazinearticle`.`article_publishtime` ,`magazinearticle`.`articlepicpath` ,`magazinearticle`.`articleurl` ,`magazinearticle`.`articlenum` ,`magazinearticle`.`perid` ,`magazinearticle`.`article_originallink` ,`magazinearticle`.`islink` from `magazinearticle` where ( logicdel = 0 ) and ( perid != 60 ) order by `article_publishtime` desc limit 1,30;
分析:

方案:

由于是读多写少的文章表,增加索引适用这类场景,提高查询响应速度.

ALTER TABLE `magazinearticle` ADD INDEX 
article_publishtime(`article_publishtime`);

再次执行explain分析

结果:

匹配范围 rows 从2w缩小到59

深入理解:

explain type的不同种类

类型含义
类型含义
system表只有一行
const表最多只有一行匹配,通用用于主键或者唯一索引比较时
eq_ref每次与之前的表合并行都只在该表读取一行,这是除了system,const之外最好的一种,特点是使用=,而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref如果每次只匹配少数行,那就是比较好的一种,使用=或<=>,可以是左覆盖索引或非主键或非唯一键
fulltext全文搜索
ref_or_null与ref类似,但包括NULL
index_merge表示出现了索引合并优化(包括交集,并集以及交集之间的并集),但不包括跨表和全文索引。
这个比较复杂,目前的理解是合并单表的范围索引扫描(如果成本估算比普通的range要更优的话)
unique_subquery在in子查询中,就是value in (select...)把形如“select unique_key_column”的子查询替换。
PS:所以不一定in子句中使用子查询就是低效的!
index_subquery同上,但把形如”select non_unique_key_column“的子查询替换
range常数值的范围
indexa.当查询是索引覆盖的,即所有数据均可从索引树获取的时候(Extra中有Using Index)
b.以索引顺序从索引中查找数据行的全表扫描(无 Using Index);
c.如果Extra中Using Index与Using Where同时出现的话,则是利用索引查找键值的意思;
d.如单独出现,则是用读索引来代替读行,但不用于查找
all全表扫描
第十二条

执行次数: 135 最大耗时: 3秒 解析最大行数:78395 返回最大行数 0

select distinct userid from weekhosnominate 
where userid = 351211 and datatype = 4
分析

方案
ALTER TABLE `weekhosnominate` ADD INDEX UserID(`UserID`);

再次执行explain分析

结果

匹配范围 rows 从1w缩小到288

第十三条

执行次数: 110 最大耗时: 2秒 解析最大行数:87693 返回最大行数 1

select `inspectioninfo`.`itemmsg` from `inspectioninfo` 
where ( itemid in ( 30 ,31 ) and itemtype = 0
and inspectionid = 109 ) limit 1 ;
分析

方案:

增加索引

ALTER TABLE `inspectioninfo` ADD INDEX 
InspectionID(`InspectionID`);

再次执行explain分析

结果

匹配范围 rows 从 5w 缩小到 13

第十四条语句

执行次数: 103 最大耗时: 2秒 解析最大行数:78395 返回最大行数 0

select `weekhosnominate`.`id` from `weekhosnominate` 
where ( userid = 351211 );
分析:

方案:

通过给字段 userid 建立索引来区分,缩小范围

ALTER TABLE `weekhosnominate` ADD INDEX UserID(UserID) ;

再次执行explain分析可以发现,
通过索引 userid 将范围由全表扫描的近万到索引指向的数十条记录.

结果:

匹配范围 rows 从 9k 缩小到 288

深入理解:


mysql结构

mysql索引原理

索引目的

索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的,如果我想找到m开头的单词呢?或者ze开头的单词呢?是不是觉得如果没有索引,这个事情根本无法完成?

索引原理

除了词典,生活中随处可见索引的例子,如火车站的车次表、图书的目录等。它们的原理都是一样的,通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样,但显然要复杂许多,因为不仅面临着等值查询,还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢?我们回想字典的例子,能不能把数据分成段,然后分段查询呢?最简单的如果1000条数据,1到100分成第一段,101到200分成第二段,201到300分成第三段……这样查第250条数据,只要找第三段就可以了,一下子去除了90%的无效数据。但如果是1千万的记录呢,分成几段比较好?稍有算法基础的同学会想到搜索树,其平均复杂度是lgN,具有不错的查询性能。但这里我们忽略了一个关键的问题,复杂度模型是基于每次相同的操作成本来考虑的,数据库实现比较复杂,数据保存在磁盘上,而为了提高性能,每次又可以把部分数据读入内存来计算,因为我们知道访问磁盘的成本大概是访问内存的十万倍左右,所以简单的搜索树难以满足复杂的应用场景。

磁盘IO与预读

前面提到了访问磁盘,那么这里先简单介绍一下磁盘IO和预读,磁盘读取数据靠的是机械运动,每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分,寻道时间指的是磁臂移动到指定磁道所需要的时间,主流磁盘一般在5ms以下;旋转延迟就是我们经常听说的磁盘转速,比如一个磁盘7200转,表示每分钟能转7200次,也就是说1秒钟能转120次,旋转延迟就是1/120/2 = 4.17ms;传输时间指的是从磁盘读出或将数据写入磁盘的时间,一般在零点几毫秒,相对于前两个时间可以忽略不计。那么访问一次磁盘的时间,即一次磁盘IO的时间约等于5+4.17 = 9ms左右,听起来还挺不错的,但要知道一台500 -MIPS的机器每秒可以执行5亿条指令,因为指令依靠的是电的性质,换句话说执行一次IO的时间可以执行40万条指令,数据库动辄十万百万乃至千万级数据,每次9毫秒的时间,显然是个灾难。下图是计算机硬件延迟的对比图,供大家参考:

硬件处理延迟

REFER:

MySQL索引原理及慢查询优化


https://www.jiucaihua.cn/news/show-1104866.html

相关文章

Shiro方法/注解使用

2019独角兽企业重金招聘Python工程师标准>>> #目的 最近对shiro的学习&#xff0c;有个习惯就是学习了就记录下来&#xff0c;以便以后翻出来看看&#xff0c;分享一下&#xff0c;如果说得不对&#xff0c;也有网友帮忙指正 那么这次是对注解这块进行学习 看这章之…

实战分享:如何在Kubernetes集群中引入CI/CD?

本篇文章将介绍一个新的工具&#xff0c;用以持续地在 Kubernetes 集群上部署应用。每一个采用DevOps 实践的企业都希望能够快速持续做每一件事情&#xff0c;无论是集成、部署、测试还是监控。无论小型企业还是大型企业&#xff0c;若想要成功做到 DevOps 式运维&#xff0c;C…

Astyle 以及 Keil的快捷键

!E --stylegoogle --indentspaces4 注释代码 取消注释

jquery插件-轮番图 广告

jQuery-rollad 广告轮番图--模仿网易云音乐的广告轮番图 这是网易音乐的效果图&#xff1a; 这是轮播插件的效果图&#xff1a; 你只需将项目的 html 模板插入到你的html页面中&#xff1a; <article class"jq-roll"><!--......--></article> 并在…

Grid_自绘

ZC: 测试使用的控件是 Delphi7版本的 TAdvStringGrid(第3方控件) “DrawCell(Sender: TObject; ACol, ARow: Integer; Rect: TRect; State: TGridDrawState);” 先绘制&#xff0c; “CustomCellDraw(Sender: TObject; Canvas: TCanvas; ACol, ARow: Integer; AState: TGridDra…

vue+django+mysql实现移动端二手交易应用

这是一个用vuedjangomysql实现的移动端二手交易应用。 效果展示 相关仓库 onehome&#xff1a;项目前端部分 onehomeServer&#xff1a;项目后端部分 onehomeDoc&#xff1a;项目的一些文档已完成功能 [x] 登录[x] 注册[x] 首页展示[x] 发布商品信息&#xff08;支持多图片上传…

iOS开发UI篇--iOS动画(Core Animation)总结

一、简介 IOS 动画主要是指Core Animation框架。官方使用文档地址为&#xff1a;Core Animation Guide。 Core Animation是IOS和OS X平台上负责图形渲染与动画的基础框架。Core Animation可以作用与动画视图或者其他可视元素&#xff0c;为你完成了动画所需的大部分绘帧工作。…

线程概念简介 什么是线程 多线程上篇(七)

操作系统为了程序的并发执行引入了进程的概念&#xff0c;提高了资源的利用率以及吞吐量。在20世纪 60年代人们提出了进程的概念后&#xff0c;在OS中一直都是以进程作为能拥有资源和独立运行的基本单位的。 直到 20 世纪 80 年代中期&#xff0c;人们又提出了比进程更小的能独…

一文让你彻底了解iOS字体相关知识

一、iOS原生字体展示 在label中选择字体的font&#xff0c;并把font由system改成custom后&#xff0c;就能在family中看到72种特殊字体。这些里面就有很炫的字体&#xff0c;但是全部是只针对英文数字&#xff0c;对中文无效。写了一个程序把所有的原生样式遍历出来展示可以达…

UITableViewCell的4种样式

转自http://blog.csdn.net/crazyzhang1990/article/details/12503163 1.UITableViewCellStyleDefault: Default样式&#xff1a;左边一个显示图片的imageView&#xff0c;一个标题textLabel&#xff0c;没有detailTextLabel。 2.UITableViewCellStyleSubtitle: Subtitle样式&am…