慢SQL如何定位？

一、慢SQL如何定位

慢SQL的监控主要通过两个途径：慢查询日志和服务监控。

1.1 慢SQL日志

开启MySQL的慢查询日志，再通过一些工具比如mysqldumpslow取分析对应的慢查询日志，现在一般的云厂商都提供了可视化的平台。

1.2 服务监控

可以再业务的基建中加入对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和警告。

二、有哪些方式优化慢SQL

优化慢SQL主要从两个方面考虑：SQL语句优化和数据库设计优化。

2.1避免不必要的列

SQL查询时应该只查询需要的列，而不要包含额外的列，例如像select *这样的写法应该尽量避免。

2.2分页优化

再数据量比较大、分页比较深的情况下，需要考虑分页优化。

例如：

select * from table where type = 2 and level = 9 order by id
asc limit 190289,10;

优化方案：

2.2.1优化方案：延迟关联

先通过where条件提取出主键，再将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行。

例如：

select a.* from table a,
(select id from table where type = 2 and level = 9 order
by id asc limit 190289,10 ) b
where a.id = b.id

2.2.2优化方案：书签方式

找到limit第一个参数对应的主键值，根据这个主键值再去过滤并limit。

例如：

select * from table where id >
  (select * from table where type = 2 and level = 9 order by id
  asc limit 190

2.3索引优化

合理设计和利用索引，是优化慢SQL的利器。

2.3.1利用覆盖索引

InnoDB使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引。

例如对于如下查询：

select name from test where city='上海'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取：

alter table test add index idx_city_1 name (city, name);

2.3.2低版本避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。

2.3.3避免使用 != 或者 <> 操作符

SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描。

例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’ ，就可以使用索引了。

2.3.4适当使用前缀索引

适当地使用前缀所云，可以降低索引的空间占用，提高索引的查询效率。

比如，邮箱的后缀都是固定的“ @xxx.com ”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引.

alter table test add index index2(email(6));

PS:需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引。

2.3.5避免列上函数运算

要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率。

select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

2.3.6正确使用联合索引

使用联合索引的时候，注意最左匹配原则。

2.4JOIN优化

2.4.1优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大.

2.4.2小表驱动大表

关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL内部会遍历驱动表，再去连接被驱动表。

比如left join，左表就是驱动表，A表小于B表，建立连接的次数就少，查询速度就被加快了。

select name from A left join B ;

2.4.3适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略.

2.4.4避免使用JOIN关联太多的表

《阿里巴巴Java开发手册》规定不要join超过三张表，第一join太多降低查询的速度，第二join的buffer会占用更多的内存。

如果不可避免要join多张表，可以考虑使用数据异构的方式异构到ES中查询。

2.5排序优化

2.5.1利用索引扫描做排序

MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的。

但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢。

因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行。

例如：

--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-
01' order by staff_id,customer_id;

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序。

2.6UNION优化

2.6.1条件下推

MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引.

最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化。

此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字， MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高。