MySQL—慢sql

一、定义

一个sql执行的很慢就叫做慢sql，一般来说sql语句执行超过5s就能够算是慢sql，需要就行优化。

二、为什么要对慢sql进行治理

        每一个慢sql都会消耗一定的I/O资源，SQL执行的快慢直接决定了资源被占用时间的长短。假设业务要求每秒需要完成100条SQL的执行，而其中10条的SQL执行时间长导致只能完成90条SQL，所有新的SQL将进入排队等待。直接影响业务。

三、治理优先级

master数据库->slave数据库：采用读写分离架构，读在从库slave上执行，写在主库master上执行。但由于从库的数据都是在主库复制过去的，主库如果等待较多的情况，会加大从库的复制延时。

执行SQL次数多的优先治理
某张表被高并发集中访问的优先治理

四、MySQL执行原理

SQL实际执行部分，主要分为两步：

解析：词法解析->语法解析->逻辑计划->查询优化->物理执行计划，过程中会检查缓存是否可用，如果没有可用缓存则进入下一步mysql_execute_command执行

执行：检查用户、表权限->表加上共享读锁->取数据到query_cache->取消共享读锁

五、如何定位慢sql

定位慢 SQL 有如下两种解决方案：

• 查看慢查询日志确定已经执行完的慢查询
• show processlist 查看正在执行的慢查询

定位到慢查询语句后，可以通过 explain、show profile 和 trace 等诊断工具来分析慢查询

详情查看mysql 简单定位慢查询并分析SQL执行效率__小鱼塘的博客-优快云博客

六、SQL常见的优化方法

1.不使用子查询：

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');

在MySQL5.5版本中，内部执行计划器是先查外表再匹配内表，如果外表数据量很大，查询速度会非常慢
在MySQL5.6中，有对内查询做了优化，优化后SQL如下

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

但也仅针对select语句有效，update、delete子查询无效，所以生成环境不建议使用子查询

2.避免函数索引

SELECT * FROM t WHERE YEAR(d) >= 2016;

即使d字段有索引，也会全盘扫描，应该优化为：

SELECT * FROM t WHERE d >= '2016-01-01';

3.使用IN替换OR

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

非聚簇索引走了3次，使用IN之后只走一次：

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

4.LIKE双百分号无法使用到索引

SELECT * FROM t WHERE name LIKE '%de%';

应优化为右模糊

SELECT * FROM t WHERE name LIKE 'de%';

5.增加LIMIT M,N 限制读取的条数

6.避免数据类型不一致

SELECT * FROM t WHERE id = '19';

应优化为

SELECT * FROM t WHERE id = 19;

7.分组统计时可以禁止排序

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

默认情况下MySQL会对所有GROUP BY co1，col2 …的字段进行排序，我们可以对其使用ORDER BY NULL禁止排序，避免排序消耗资源

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

8.去除不必要的ORDER BY语句