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的优化。