MySQL性能优化-查询优化

1、如何发现有问题的SQL

使用MySQL慢查询日志

对有效率问题的SQL进行监控：

mysql>set global log_queries_not_using_indexes=on;
mysql>set global long_query_time=1;  (几秒的查询会记录到慢查询日志)
mysql> show variables like 'slow_query_log';
mysql>set global slow_query_log_file=’/home/mysql/sql_log/mysql-slow.log’
mysql>set global slow_query_log=on;

慢查询日志的存储格式

MySQL慢查询日志分析工具之mysqldumpslow

mysql安装后会将mysqldumpslow自动安装到系统。

#mysqldumpslow -h

使用show processlist和set profiling

1、链接过多你是否还能看show processlist，
那个，mysql里root比普通用户多一个链接许可，
所以，记得程序切忌用root链接，保留一个给系统分析师用。

2、数据库负载过高：
showprocesslist+一定的敏感度，
再配合用set profiling去分析具体的开销，是非常重要的一种分析查询性能的方法。

MySQL慢查询日志分析工具之pt-query-digest

2、MySQL执行计划

3、查询优化

分解大的查询

可以将一个大查询切分成多个小查询执行，每个小查询只完成整个查询任务的一小部分，每次只返回一小部分结果。

分解关联查询，即对每个要关联的表进行单表查询，然后将结果在应用程序中进行关联。

SELECT * FROM tag
    JOIN tag_post ON tag_post.tag_id=tag.id
    JOIN post ON tag_post.post_id=post.id
WHERE tag.tag = 'mysql';

可以分解成下面这些查询来代替：

SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

将一个关联查询拆解成多个单表查询有如下有点：

● 让缓存的效率更高。如果缓存的是关联查询的结果，那么其中的一个表发生变化，整个缓存就失效了。而拆分后，如果只是某个表很少的改动，并不会破坏所有的缓存。
● 可以减少锁的竞争。
● 更容易对数据库进行拆分，更容易做到高性能和可扩展。
● 查询本身的效率也有可能会有所提升。例如上面用IN()代替关联查询比随机的关联更加高效。

SELECT语句

● 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

关于IN操作符

在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。

NOT IN操作是强列不推荐使用的，因为它不能应用表的索引，推荐树勇NOT EXISTS代替。

用IN()取代OR

在MySql中，IN()先将自己列表中的数据进行排序，然后通过二分查找的方式确定列的值是否在IN()的列表中，这个时间复杂度是O(logn)。如果换成OR操作，则时间复杂度是O(n)。所以，对于IN()的列表中有大量取值的时候，用IN()替换OR操作将会更快。

子查询

MySql的子查询实现的非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。
应该尽可能用关联替换子查询，可以提高查询效率。

关于NULL

判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的。用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。

LIKE操作符

%不要用在字符串开头，这时不会使用索引。

EXISTS、IN与JOIN的比较

通常情况下，3种查询方式的执行时间：
● EXISTS <= IN <= JOIN
● NOT EXISTS <= NOT IN <= LEFT JOIN
只有当表中字段允许NULL时，NOT IN的方式最慢：
● NOT EXISTS <= LEFT JOIN <= NOT IN
但具体情况具体分析。

JOIN的性能分析

尽量用inner join，避免 LEFT JOIN 和 NULL。
在使用left join（或right join）时，应该清楚的知道以下几点：
● on与 where的执行顺序
ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件，将会额外生成一行所有列为 NULL 的数据。
在ON匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

我们知道标准查询关键字执行顺序为 from->where->group by->having->order by.
join 是在from范围类所以 先on条件筛选表，然后两表再做 join。
而对于where来说在left join结果再次筛选。

● 尽量避免子查询，而用join。
● 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
● 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样才能使用索引来优化这个过程。

在MySQL中，只有一种Join算法，就是Nested Loop Join，意思就是通过驱动表的结果集作为循环基础数据，然后一条一条通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

了解了Join的实现原理，就清楚该如何去优化一个一个Join语句：
1、尽可能减少Join语句中的Nested Loop的循环总次数：让驱动表的结果尽可能的小（永远用小结果集驱动大的结果集）。
2、优先优化Nested Loop的内层循环。
3、保证Join语句中被驱动表上Join条件字段已经被索引。
4、当无法保证被驱动表的Join条件字段被索引且内存资源充足前提下，不要太吝惜Join Buffer的设置。

WHERE语句条件顺序

● 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
● 数据量小的条件放到前面。 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
● 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
● 应尽量避免在 where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。(WHERE F1/2=100 )
● 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
● 不要在 where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
● 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描（可以选择用union/union all代替 or）。
● 对于连续的数值，能用 between 就不要用 in 了。
● 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
实际测试结果：多个字段共同组成“主键/非主键”索引，当多个字段同时出现在where语句中时，顺序的不同并不影响索引的使用；但当只有部分字段出现在where条件中时，只有前面的字段出现在where中才能正确使用索引。

COUNT()查询

COUNT()有两个不同的作用：

1、统计某个列值的数量，即统计某列值不为NULL的个数。
2、统计行数。
当使用COUNT(*)时，统计的是行数，它会忽略所有的列而直接统计所有的行数。而在括号中指定了一个列的话，则统计的是这个列上值不为NULL的个数。
可以考虑使用索引覆盖扫描或增加汇总表对COUNT()进行优化。

LIMIT查询

limit常伴随order by从句使用，因此大多时候会使用Filesorts这样会造成大量的IO问题。

● 使用有索引的列或逐渐进行order by操作
● 记录上次返回的主键，在下次查询时使用主键过滤
● 关于数据大，分页靠后的 limit优化可以参考“mysql 大分页”

UNION查询

除非确实需要服务器消除重复的行，否则一定要使用UNION ALL。如果没有ALL关键字，MySql会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。

4、注意

● sum属性时，当查询数据为空时，sum值也是空。
● sum计算double和float时，会出现精度问题，可以在查询时指定小数位。或者字段类型使用decimal(16,6)代替。
● 涉及金额的表最好建立唯一索引避免数据重复提交；
● 涉及金额的表的金钱字段，要检查不能小于0。设置无符号的int类型（unsign int（10））。
● Count()和Max()应该尽量对查询列建立索引。

5、分享日常SQL分析和自我测试的方法

1、一条SQL如果执行很慢，你用explain 解析一下，看看是否影响结果集很大，这是其一；
2、对这条很慢的SQL做一个状态拆解，在mysql中是这样操作的：

set profiling=1;

执行问题SQL;

show profile for query 1;

通常，如果这个问题SQL确实是索引出了问题，也就是影响结果集，或者说索引扫描行数较多，那么他的执行状态最多的消耗就在 sending data这个状态上，这个状态不要被名字骗了，其实负载是在i/o，硬盘扫描上。

你测试的时候就可以看，影响结果集的数字，和sending data上状态的开销，是不是线性相关，对一个复杂的数据表结构，导入上百万条记录，然后用不同索引方式和不同SQL查询，利用 explain 和set profiling 这些操作反复分析SQL的影响结果集和开销构成。