mysql优化——查询优化
这一篇mysql优化是注重于查询优化,根据mysql的执行情况,判断mysql什么时候需要优化,关于数据库开始阶段的数据库逻辑、物理结构的设计结构优化不是本文重点,下次再谈。
查看mysql语句的执行情况,判断是否需要进行优化
当感觉操作数据库查询语句速度变慢,不符合生产效率要求时,可按照以下步骤进行查看
1、 慢查询的开启与捕获,查看可能是哪些SQL语句造成的查询速度慢
2、 explain+SQL语句
3、 show profile分析SQL语句在服务器内执行细节和生命周期情况
4、 通过以上三个步骤大致确定问题SQL之后,可联系运维人员或者DBA进行数据库服务器参数的调整优化
以下分别通过程序员可分析的前三个方面来讨论mysql语句的查询优化
一、慢查询
慢查询日志是mysql的一个日志记录,可以用来记录mysql语句执行时间超过指定的long_query_time的SQL语句,long_query_time的默认值是10s。
慢查询日志默认情况下是不开启的,因为将数据保存到日志会对性能有一定影响,测试环境下可手动打开,但注意手动开启之后只对本次启动生效,mysql关闭之后重启恢复默认状态,要想持久生效要改变my.ini配置文件(Window系统下),其他系统变量也如此。
可通过show varaibles like "%slow_query_log%"来查看日志开启情况。
可以用set long_query_time = 3;语句来改变默认的阀值,然后我们可以用show varaiables like "long_query_
time"来查看是否更改生效,若没有生效,可尝试重启一下mysql客户端即可。
然后我们现在来测试一下,因为我们平时个人测试学习的数据库及其简单的SQL语句可能没有造成很慢的查询,我们可以采用 select sleep(time)来模拟测试
执行该函数之前slow.log文件:
执行sleep(4)函数,因为要让你设置的这个time大于记录到日志里面的时间阀值
可已看到这条慢查询话费的具体时间是4.041230,也可以看到是哪个用户在哪个数据库操作的哪条具体SQL语句,我们开启慢查询日志的目的就是找到这样的造成查速度减慢的SQL语句,为第二步的explain提供基础。
mysqldumpslow日志分析工具
在实际的数据库使用过程中可能会有多条日志记录,数据复杂,人工分析费事费力,mysql提供了一个日志分析工具mysqldumpslow。
可以根据你设定的参数查询出满足条件的日志记录,方便查看。
可用的参数有
-s, 是表示按照何种方式排序
排序方式有
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t, 是top n的意思,即为返回前面多少条的数据;
-g, 后边可以写一个正则匹配模式,大小写不敏感的;
示例:
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
二、explain+SQL语句
执行这个语句可以让开发人员看到select语句执行的详细信息,开发人员可以将上一步慢查询中捕获的慢查询SQL语句进行分析,判断查询效率低的可能原因。
可以帮助选择更好的索引和写出优化的查询语句,使用explain我们可以得到以下信息:
①表的读取顺序
②数据读取操作的类型
③哪些索引可以使用
④哪些索引实际被使用
⑤表之间的引用
⑥每张表有多少行被优化器扫描
示例
我们来逐个分析各字段
id:select查询的序列号,代表的是select执行的顺序,主要有以下三种情况:
id相同时,则按照从上到下依次执行
id不同时,id值越大优先级越高,越先被执行
id有相同有不同,则相同的id为一个组,不同组的id值按照规则二的优先级执行,同组id则按照规则一依次执行
select_type:select查询的类型,有以下常用几种
simple:表示该查询没有子查询和UNION连接查询
primary:有子查询时的最外层查询
subquery:有子查询时的内层嵌套查询
derived:在from中包含的select就称为derived(衍生) ,mysql会递归这些子查询,把结果放在临时表中
union:union的第二个或者最后一个
union result:union的结果
table:执行当前SQL语句用到的表
partitions:代表当前表所使用的分区
type:显示使用了何种查询,按照常见的几种查询最好到最坏排序为system>const>eq_ref>ref>range>index>all
system,const:mysql能够对这部分进行查询优化使能够将其转换成一个常量(system只返回一行,const有多行),如某一行的主键放入WHERE子句里的方式来选取此行的主键,MySQL就能将这个查询转换成一个常量。然后就可以高效的将表从联接执行中移除
eq_ref:使用该索引查找,mysql知道最多返回一条数据,可以在使用主键或者唯一性索引查找时用到
ref:非唯一性索引的索引查找
range:范围扫描,例如带有between或者>,<,in等
index:扫描所有索引行
all:扫描所有数据行
possible_keys/kesy:代表可能用到的索引和实际用到的索引
key_len:在索引中使用的字节数
ref:显示了之前的表在key列记录的索引中查找值所用的列或常量
rows:mysq估计的要找到满足条件的行所需要扫描的行数
filtered:给出了一个百分比的值,这个百分比的值和rows列的值一起使用,可以估计出那些将要和QEP中的前一个表进行连接的行的数目。前一个表就是指id列的值比当前表的id小的表
extra:给出一些额外但重要的信息,常见重要的信息有
using index:使用了覆盖索引,以避免扫描表(良好情况)
using filesort:索引创建数据排序方式不满足要求,mysql在外部重新排序(严重,需要优化)
using temporary:mysql创建使用了临时表来保存信息(严重,需要优化)
using where:使用了where
using join buffer:在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果(需要增加索引进行优化)
这个里面我们需要重点关注的属性是type,keys,row,extra来判断是否为一个良好的查询。
三、show profile
show profile是mysql用来分析SQL查询语句的资源使用情况的工具
使用方法:
1、 因为mysql这个功能默认是关闭的,所以先查看一下并开启
(与开启慢查询日志类似,可能需要重启mysql客户端才能生效)
2、 我们执行一些测试的SQL语句之后运行show profiles语句
3、 我们可以选择指定项指定SQL语句来分析
一般我们查看的属性就是cpu和block io两个模块
注意:
若出现以下任意一个情况,都表示这是一个糟糕的SQL语句,需要优化
1、 convering heap to MyIsam查询结果过大,内存不够,需要记录到磁盘上
2、 creating tmp table创建临时表储存数据,用完之后删除
3、 copying to tmp table on disk将临时表中的数据储存到磁盘上
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341