mysql数据库优化之语句优化

数据库优化目的

避免出现页面访问错误

1、由于数据库链接timeout产生页面5xxx错我

2、由于慢查询造成页面无法加载

3、由于阻塞造成数据无法提交



如何发现有问题的sql

使用mysql慢查日志对有效率问题的sql进行监控


show variables like '%slow%';   

set global log_queries_not_using_indexes=on;  

 代表 记录下没有使用索引的sql 后面进行优化 ,记录未使用索引的 打开
long_query_time 单位是秒 一般设置成100ms,也就是0.1秒

 如果慢查日志时间短,会有大量的日志,磁盘空间可能会被沾满



 第一款MySQL官方的mysqldumpslow 
分析慢查工具 
 安装好mysql后自带的工具,mysqldumpslow -t 3 慢查日志路径 | more  查看


但是结果信息不是很全面

所以用pt-query-digest


 --limit=A 参数  -review   expain 执行计划 如扫描行数很多,发送到客户端的很少,代表了索引不是很好 

第一部分是头显示了基本信息,时间范围 sql数量  第二部表统计信息  第三部分 sql信息





举例说明 就是,这个sql 执行了一次,但是执行时间占用了百分之73百分比,这种sql重点关注,就是执行次数多,占用总时间的百分比大。数据库主要瓶颈在io这,这个主要关注的是扫描行数,如果扫描的行数越多,说明他的io消耗也会越大。


第一个关注执行次数和执行时间占比,第二关注扫描行数examine,说明他的io消耗也会越大,第三 通过扫描行数 和发送行数,如果examine的扫描行数,远远大于send发送行数,说明sql索引命中率不高

找到慢的语句后如何进行优化

首先是用explaim这个从句,可以显示出sql的执行计划,原理数据库中的sql,先对执行计划分析,再对sql进行具体执行,那执行计划侧面的反应了sql的执行效率,



table 是这一行的数据是关于哪张表的 。

type这一列 const性能最好 性能最差就是ALL 

 const说明是个常数查找,一般是主键呢唯一索引进行查找,

eq-reg 是一种范围的查找,一般呢是唯一索引呢主键的范围查找,

ref 比较常见于连接的查询中,一个表是基于某个索引的查找,

range是索引的范围查找,

index是索引的扫描,

ALL是表扫描
没有where条件所以没有用到索引

key-len 索引的长度越短越好,因为mysql中的查询化过程中,索引长度越小越好的,mysql每次读取都是以页为单位的,一页中如果存储的索引数量越大,它的查询效率也就会越高。

rows 实际上就是表扫描的行数,

扩展列 一个是

filesort查询结果用到文件排序方式来进行优化,文件排序呢主要是在orderby程序中比较常见,

temporary 这个能用到了临时表,无论filesort 或是temporpary都使用了外部文件或是临时表进行数据的存储,这种sql一般出现在order by group by 从句中,这样的sql也是要重点关注的。以上是explain 从句使用方式还有返回的一些值,执行计划

一如何优化max()和count()函数增加索引

max()一般用于查询最大的和最后的某一件事的时间,比如查找最后的支付时间语句如下:


一、Count()和Max()优化


explain select max(payment_date)from payment \G ,它的含义是查询payment这个表,类型ALL是表扫描,没有用到索引,扫描行数是15422行,这个sql不是一个高效的sql,如果我们的数据行数非常大,并且呢查询的频率非常非常高的情况下,那么sql的IO会相当的高,这样会拖慢整个服务器的IO效率,如何来优化这种sql,通常情况下是在这个表中加一个索引,create index idx_paydate on payment(payment_date);   



 然后再过刚才的执行计划explain select max(payment_date)from payment \G 


总结 对于max()类的查询用索引来优化他,这个索引的也就是一条覆盖索引,完全通过一条索引来覆盖他,这种索引称之为覆盖索引

第二个Count() 函数


count(id)不包含空值的,count(*)包含为null的一行




二、子查询的优化


一般子查询使用的方法是  t.id的值同时包含在t1.id 里

select * from t where t.id in (select t1.tid from t1);

优化后的sql语句是使用join on

select t.id from t join t1 on t.id=t1.tid;  但是注意有个事项,join是需要去重 使用distinct 

select t.distinct  id from t join t1 on t.id=t1.tid;

三、gourp by 的优化



explain select actor,first_name,actor,last_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id\G

执行计划结果


显示使用了文件排序filesort,对演员表使用了临时表描操作temporary,

优化后的语句




执行计划中没有文件排序和临时表的方式了换成了索引,这样节省的大量的io ,优化了sql的效率,节省了服务器的资源,但是需要注意,如果增加过滤条件 ,就要在子查询里增加过滤条件,而不是在子查询外面增加过滤条件。

四、limit查询的优化方式

limit常用于分页处理,时常会伴有order by 从句使用,因此大多的时候会使用filesort 文件排序,这样就会造成大量的io问题,下面的sql语句

explain select film_id, description from sakila.film order by title limit 50,5\G


从执行计划中可以看出,使用了表扫描,扫描了1030行,同时使用了文件排序的方式,如果数据量比较大的情况下,会出现io问题。

可以看出使用了表扫描1030行,使用了文件排序的方式filesort,如果数据量大的情况下会产生大量的io问题。

如何优化这样的sql。使用主键或者索引的列来order by 操作。inodb是按照主键的逻辑顺序来进行排序的,如果使用主键来进行排序的话,就可以避免很多io操作了,这里用的是主键film_id 进行排序的


优化步骤一

使用主键进行排序,explain select film_id,description from sakila.film order by film_id limit 50,5\G


使用了索引扫描,使用了主键的排序,表扫描行数是55,因为需要列出的是50行后的5行信息,所以总共扫秒55行是最少的io操作了,没有额外的。如果想列出500行以后的信息,执行计划显示的是扫描505行,随着翻页越往后,io就会越大,如果这个表里面有几十万行数据,当翻到后面的时候响应时间就会很大。

优化步骤二


记录上一次返回主键记录的id,可以记录到上一次返回的主键id是多少,下一次操作的时候通过主键进行过滤,从而实现排序功能,比如说我们在这里使用了film_id >55,因为上次返回的是55行,film_id<60 我们要列出5行的数据,


看出使用的是索引的范围查找range ,表扫描刚只有5行,使用这种需要注意主键一定是顺序排序的,如果主键中间空缺了某一列或某几列的情况下,那么列表可能会出现不足于5行的数量,在这种情况下,我们需要注意主键是否是顺序增长的,是否是连续的,如果不连续,可以建一个附加的列,比如说是index_id这一列,我们这一列数据是自增的,并且在这一列加上索引,这样也可以实现这种效果。

整体优化的思路是避免过多的扫描,这里主要说的是sql的优化。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

NeilNiu

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值