mysql慢查询排查方法有explain_[数据库]MySQL慢查询及优化

[数据库]MySQL慢查询及优化

0 2020-09-12 15:00:14

最近做一个CRM系统，发现了慢查询日志里记载了许多的慢sql，于是就对其进行了sql优化。在优化的过程中，自己也归纳整理了一些sql优化的方案。今天就来和大家聊聊。

**1、慢查询的分析**

常见的分析慢查询sql的方法大概有三种：explain、show profile、trace 分析 sql优化器。本文主要介绍explain的方法去分析慢sql，其余两种方法有兴趣的同学可以去了解下。

**2、explain各参数解释**

(1)explain语法：explain+“需分析的sql”；

例：我想分析“select * from table1 where b=500;”这条sql的执行效率，那么直接在sql命令行下执行“expalin select * from table1 where b=500;”就可以查看了；执行结果如下：

根据上图，可以看到有许多个字段，那这些字段分别有什么意义呢？见下图(红框标出的为重点关注字段)：

其中各个字段又可能有多个不同的值，重点关注字段select_type、type、Extra的可能值如下图所示：

其中，上图中的“type”的值的查询性能从上到下依次是最好到最差。

**3、常用sql优化方法**

(1)使用索引

大家对MySQL的索引应该不会感到陌生吧，数据量大的时候，最常见的加快查询效率的方法那肯定是加索引了，要知道MySQL的B+索引树可是能在2~4层树就能从上亿的数据中提取出相关数据的，不加索引的话得进行上亿次磁盘io查找(关于B+树的具体原理，大家可以参考下这篇文章，写得很不错，①对查询条件使用函数

如：select * from table1 where date(c) ='2020-08-20';

应改为：select * from table1 where c>='2020-08-20 00:00:00' and c<='2020-08-20 23:59:59';

②隐式转换：如把varchar类型当成int型去写

例：select * from table1 where a=1000; (其中，a字段在数据库中是varchar类型)

应改为：select * from table1 where a='1000';

③模糊查询

如：select * from table1 where a like '%1111%';

应改为：select * from table1 where a like '1111%'; 但要看具体业务，可能不对

注意：select * from table1 where a like '%1111'; 这种也是不走索引的

可以这么理解：like 匹配是%在前面的都不走索引

④范围查询

如：select * from table1 where b>=1 and b <=2000; (这条数据查询范围过大，是全表扫描，优化器选择不走索引)

应改为：select * from table1 where b>=1 and b <=1000;

select * from table1 where b>=1001 and b <=2000;

⑤计算操作 (但一般在代码层面做操作，很少会在数据库做操作)

如：select * from table1 where b-1 =1000;

应改为：select * from table1 where b =1000 + 1;

⑥OR 操作

如果条件中有OR,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引。所以除非每个列都建立了索引，否则不建议使用OR，在多列OR中，可以考虑用UNION 替换

如：select * from table1 where create_time = '2020-08-20 11:49:30' OR b > 854;

应改为：select * from table1 where create_time = '2020-08-20 11:49:30' UNION select * from table1 where b > 854;

(2)分页优化

如：select * from table1 order by a limit 99000,10; (其中，a字段有索引)

但结果不走索引，原因是扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。(关键是让排序时返回的字段尽可能少)

应改为：select * from table1 f inner join (select id from table1 order by a limit 99000,10)g on f.id = g.id;

或者 select * from table1 where id >= (select id from table1 order by a limit 99000,1) limit 10;

(3)连接查询优化

在项目中，表连接查询是比较常见的，尤其是一些统计模块。表连接有两种算法，一种叫Nested-Loop Join 算法(简称NLJ)，另一种是Block Nested-Loop Join 算法(简称BNL)(关联字段不存在索引时会使用到)。感兴趣的小伙伴可以深入去了解下相关原理哦。我们进行关联查询优化的方法是：尽量让 BNL变成 NLJ ，就是说在关联字段上加上索引。

(4)order by和group by优化

①首先我们要知道MySQL中有两种排序方式，一种是通过有序索引直接返回有序数据(Extra字段：Using index)；另一种是通过 Filesort 进行的排序，不走索引(Extra字段：Using filesort)；毫无疑问，肯定是有序索引排序更快。

常见order by优化方法：

①在排序字段上添加索引

如：select c,id from table1 order by c; (c有索引)

②多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句

如：select id,a,b from table1 order by a,b; (a,b是联合索引)

注意：select id,a,b from table1 order by b,a; (a,b是联合索引)此时不走索引，最左匹配前缀原则了解下

③对于先等值查询再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化

如：select id,a,b from table1 where a=1000 order by b; (a,b是联合索引)

如果a,b不是联合索引的话，即时有a索引，b索引，排序也不会走索引

④去掉不必要的返回字段

如：select * from table1 order by a,b; /* 根据a和b字段排序查出所有字段的值 */

应改为：select id,a,b from table1 order by a,b; /* 根据a和b字段排序查出id,a,b字段的值 */

不走索引原因：扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，

所以优化器放弃使用索引。

默认情况，会对 group by 字段排序，因此group by优化方式与 order by 基本一致。

本篇文章就写到这，你们工作中还有什么好的优化sql的方法吗？欢迎在评论区分享！

本文网址：http://www.shaoqun.com/a/472268.html

*特别声明：以上内容来自于网络收集，著作权属原作者所有，如有侵权，请联系我们：admin@shaoqun.com。

MYSQL

0