MySQL 性能优化(二) 语句优化

对应不同的MySQL版本，性能有一定的差别。本次的记录是基于MySQL 5.5 版本。

一：对于一些慢查询，有效率问题的sql，MySQL提供慢查询日志进行记录，但是慢查询日志是需要开启的。

在MySQL中，set global 参数 =value 是设置参数值

show variables like '' 是查询变量的信息

记住以下几个变量，然后通过查询它们的值是否为on再进行设置

-- 指定慢查询日志存储位置 slow_query_log_file

-- 指定是否将没有索引的sql进行慢查询记录 log_queries_not_using_indexes

-- 查询超过几秒会进行慢查询记录 long_query_time

二：开启

1：查询slow_query_log是否是ON,

show variables like 'slow_query_log';  

2：查询 log_queries_not_using_indexes变量的设置

show variables like '%log%';

3：查询long_query_time设置，如果是0则代表所有语句都记录

show variables like 'long_query_time';

3:设置 

set global log_queries_not_using_indexes=on;

set global  slow_query_log= on;

set global long_query_time = 1;

4：测试，可以对某个表进行查询然后看log是否记录，这边不做记录

三：慢查询日志分析与查看

在生产环境上，如果开启慢查询日志，这个日志可能会持续增长，一天可能好几G。如果按照上面的方式进行查看，那显然是不太可能的，因此需要一些工具进行慢查询分析。通过生成报表，在通过报表的形式进行查看，可以使用以下两种工具

1：mysqldumpslow:这个是mysql官方提供的慢查询分析工具，当我们安装mysql在服务器中的时候，这个工具也会被安装进来

-- 分析前10条的慢查询数据

mysqldumpslow -t 10 /home/mysql/slow_sql.log/ 

2：pt-query-digest ：相比mysqldumpslow更完善

pt-query-digest  /home/mysql/slow_sql.log/ 

四：如何通过慢查询分析有问题的sql

1）查询次数多且每次查询的时间比较长

通常为pt-query-digest分析的前几个查询

2）IO大的SQL

注意Rows examine 项，扫描的行数越多则IO越多

3）未命中索引的sql

注意Rows examine 和Rows send的对比

当Rows examine远远大于Rows send则说明索引命中率并不高，主要是用表扫描或者索引扫描的方式查询

五：explain查询sql的执行计划

数据库的sql都会先进行执行计划的分析，才会进行sql的具体查询，所以执行计划从侧面的反应出查询效率，

explain返回的各列含义：

table：显示这一行数据是哪张表的

type：重要的一列，显示连接用到的那种类型，从好的差的分为：const,eq_reg,ref,range,index,all

pssible_keys：显示可能应用在这张表的索引，如果为空，没有可能的索引。

key：实际使用的索引。

ken_len：使用索引的长度，在不损失精确性的情况下，越短越好。

ref：显示索引被哪一列使用了。

rows：MySQL认为必须检查的用来返回请求数据的行数

extra列需要注意的返回值：

Using filesort：看到这个的时候，查询就应该优化了。说明使用了文件排序的方式，在order by中比较常见

Using temporary：看到这个的时候，查询就应该优化了，这个查询用到了临时表，通常发生在对不同的列集进行order by，而不是Group by

六：count()和max()的优化方法

max(column) 中最好加索引，防止全表扫描

count(*)跟count(cloumn)区别：

count(cloumn) 会把NULL值过滤，而count(*)则查找所有列数量。

例：

在一条sql中查出state 为1 和 state 为 2的数据

select count(state =1 or NULL) as 'state为1的列',count(state =2 or NULL) as 'state为2的列' from table_state;

七：子查询优化

通常情况下会把子查询优化成join查询，但是要注意一对多的情况，防止重复。

比如①：

select  a.name from A a where a.id in (

select a.id from A a

)

换成②select a.name from A a join A on a.id =b.id

第一种不会出现重复，第二种会重复，需要用distinct来去重

八：group by 查询优化

九：Limit查询优化

limit常用于分页处理，时长会伴随order by 从句使用，因此大多时候会使用Filesorts,这样会造成io问题

select id,name from user order by name limit 10,10;

此处中使用了表扫描和Using filesort;

优化方案一：使用索引列或者主键列进行order by

结果：没有使用文件排序，并且使用索引

分析：随着limit 操作越往后，扫描的数据越多，io就越大。

再次优化：

优化方案二：记录上一次返回的主键，在下次查询的时候使用主键过滤

select id,name from user where id >100 and id<=110 order by name limit 1,10;

这种方式避免了数据量大扫描过多的记录

缺点：主键必须顺序排序，如果中间出现空缺，那么可能出现数据不足10条的情况，可以通过创建一个自增的索引列来进行解决，但是如果进行删除的话，还是有问题