MySQL优化学习 | 找到慢SQL

这是我参与8月更文挑战的第16天，活动详情查看：8月更文挑战

前言

在开发中我们如何去定位到哪些SQL需要优化，如何让数据库自动帮我们把慢SQL找出来，让我们有效率，有针对性的去优化？

多表关联优化

当我们进行两张表之间关联查询的时候，如上图，tbl1与tbl2关联查询，关联字段为col3,数据库会根据自己判断取一张表作为条件（图中为tbl1）,然后筛选出col3=1的数据，将这些数据带入到tbl2中循环比较，取值，最后将所有结果集返回。作为条件带入的这个表就被称之为驱动表，驱动表是由数据库自己去决定选择哪一个的。

例子

客户信息有4W多条，客户联系人信息也有4W多条，关联查询客户对应的联系人信息。

查看一下执行计划，发现type出现了ALL,表明执行了全表扫描，rows显示扫描了46371行数据，可见这条SQL的执行效率其实是很低的。 EXPLAIN 执行计划中默认第一行出现的表就是驱动表，是由查询优化器自动选择的

优化

这种全表扫描的sql我们应该坚决避免的，接下来对这个查询进行优化。

关联查询优化要点

外键上加索引

查询条件上加索引

CREATE INDEX idx_customer_id ON 72crm_crm_customer_contact(customer_id)

看一下添加过索引之后的执行计划

t2表的查询走了索引，idx_custoemr_id，rows直接变成了1,这个查询效率极大的提升了。在高并发场景下，提升更加明显。

慢SQL日志

我们可以通过开启慢SQL日志功能，让数据库帮我们记录下使用过程中查询较慢的SQL语句，便于后期快速定位，有针对性的进行优化。

-- 开启慢SQL日志功能
SET GLOBAL slow_query_log = ON;
-- 慢SQL执行时间阈值，单位(秒)，1毫秒=0.001秒
SET GLOBAL long_query_time = 0.001;
-- 指定慢SQL文件名为slow-sql，慢SQL日志保存在 mysql/data 目录下
SET GLOBAL slow_query_log_file = "slow-sql.log";

注意：SET GLOBAL 只在当前连接中生效，如需做持久化配置可在my.cnf中添加配置项

-- 使用以下语句查看设置是否生效
show variables like '%query%'

我们执行一条查询语句测试一下

可以看到执行的时间为3.120s，远远大于了我们设置的阈值，我们去看一下是否有生成slow-sql.log

可以看到我们刚才的SQL有被记录。 对应参数的含义

# Time: 2021-08-16T14:01:25.943484Z -- 执行时间
# User@Host: root[root] @  [211.161.247.64]  Id:  3255 -- 环境信息
# Query_time: 0.242326  -- 查询时间
# Lock_time: 0.112309 -- 资源锁定时间
# Rows_sent: 98  -- 查询结果总行数
# Rows_examined: 98 -- 扫描行数
# SET timestamp=1629122485; --时间戳

记录未走索引的语句

在项目开发初期，数据量较小，有些数据即使没有走索引，也不会被慢SQL日志记录，那么并不意味着我们就可以放过这些SQL语句，因为随着数据量激增，总有一天会造成卡顿。 通过以下语句记录未走索引的sql语句

SET GLOBAL log_queries_not_using_indexes = ON

提示

上述示例中为了效果，我们将阈值设置的比较小，正常环境中阈值设置在300ms-500ms比较合适，超过这个时间，查询的速度就比较慢了，可以针对这些sql进行优化

扩展

反范式设计

严格参照三范式进行数据库表结构的设计是不切实际的，按照三范式的要求，表会被拆分的过于零散，导致数据库表关联复杂，给我们sql语句的编写带来非常大的压力，在阿里的开发手册中强制要求三张表关联禁止使用join。 优化方案 合理的添加冗余字段

反范式的优缺点

优点

单表查询易于优化，易于管理

SQL语句简单，易于理解，有利于程序开发，团队协作 缺点

存在数据冗余字段，写操作时需要额外更新从表数据

不合理的反范式设计会让表变得臃肿不堪