首页 > 数据库 > MySql > mysql优化——查询优化
2020
06-01

mysql优化——查询优化

attachments-2020-06-qogATNY45ed49ace3491d.jpg

这一篇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%'来查看日志开启情况。


v2-6a9bf035645c081617222e244268c718_720w.jpg


可以用set long_query_time = 3;语句来改变默认的阀值,然后我们可以用show varaiables like 'long_query_
time'来查看是否更改生效,若没有生效,可尝试重启一下mysql客户端即可。


v2-dcd1b43af8342b7dedc028570b8a5987_720w.jpg

然后我们现在来测试一下,因为我们平时个人测试学习的数据库及其简单的SQL语句可能没有造成很慢的查询,我们可以采用 select sleep(time)来模拟测试
(这个函数类似于java线程中的sleep函数)

执行该函数之前slow.log文件:

v2-8b2f52d43b752291b2302da401b1f114_720w.png


执行sleep(4)函数,因为要让你设置的这个time大于记录到日志里面的时间阀值

v2-713a0cc44ac6de67283f77a3e93caa81_720w.jpgv2-650b474eac97d7b6fa405784104dcafe_720w.jpg

可已看到这条慢查询话费的具体时间是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我们可以得到以下信息:

①表的读取顺序
②数据读取操作的类型
③哪些索引可以使用
④哪些索引实际被使用
⑤表之间的引用
⑥每张表有多少行被优化器扫描


示例

v2-40e29d8fd14c7373cddb5c01c815880b_720w.jpg


我们来逐个分析各字段

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这个功能默认是关闭的,所以先查看一下并开启

v2-10630fdd8abb62c645dafcc1608c565b_720w.jpg

v2-ef41b486d4cab93d5a7041069548de73_720w.jpg

(与开启慢查询日志类似,可能需要重启mysql客户端才能生效)


2、 我们执行一些测试的SQL语句之后运行show profiles语句

v2-5d64df54963a3820cee9bb340e4c4b7a_720w.jpg


3、 我们可以选择指定项指定SQL语句来分析

v2-6065f8ecd70fb80d6dfecc140b48ffc4_720w.jpg


一般我们查看的属性就是cpu和block io两个模块


注意:

若出现以下任意一个情况,都表示这是一个糟糕的SQL语句,需要优化
1、 convering heap to MyIsam查询结果过大,内存不够,需要记录到磁盘上
2、 creating tmp table创建临时表储存数据,用完之后删除
3、 copying to tmp table on disk将临时表中的数据储存到磁盘上

扫码芷若 获取免费视频学习资料

编程学习

查 看2019高级编程视频教程免费获取