记录一个超长sql的优化，从4s到0.0015s

版权说明： 本文由博主keep丶原创，转载请注明出处。
原文地址： https://blog.csdn.net/qq_38688267/article/details/105634312
环境说明： Mysql版本：8.0.19

  废话不多说，直入正题，先来看看我所说的超长sql：

SELECT
	my.id,
	my.date,
	my.business_date AS businessDate,
	my.CODE,
	my.customer_id AS customerId,
	cc.NAME AS customerName,
	cc.boss_name AS bossName,
	my.contact_person_id AS contactPersonId,
	ccp.NAME AS contactPersonName,
	my.department_id AS departmentId,
	dep.NAME AS departmentName,
	csopd.NAME AS saleOrderDepartmentName,
	parentpd.NAME AS parentDepartmentName,
	parentcsopd.NAME AS saleOrderParentDepartmentName,
	ca.agent_type AS agentType,
	csoca.agent_type AS saleOrderAgentType,
	my.achievement_type AS achievementType,
	my.audit_status AS auditStatus,
	my.type,
	my.order_type AS orderType,
	my.quote_money AS quoteMoney,
	my.offset_money AS offsetMoney,
	my.receipt_money AS receiptMoney,
	my.amount,
	my.create_by AS createBy,
	su.NAME AS createByName,
	my.counselor,
	coun.NAME AS counselorName,
	my.course_id AS courseId,
	course.NAME AS courseName,
	achi.NAME AS achievementDepartmentName,
	my.create_time AS createTime,
	ifnull(
	IF
		(
			0 > csod.all_money - sum(csod.refund_money) - sum( csod.delivery_money ),
			0,
		csod.all_money - sum( csod.refund_money ) - sum( csod.delivery_money )),
		0 
	) AS unDeliveryMoney,
	ifnull(
	IF
		(
			0 > csod.all_money - sum(csod.refund_money ) - sum( csod.settle_money ),
			0,
		csod.all_money - sum( csod.refund_money ) - sum( csod.settle_money )),
		0 
	) AS unSettleMoney,
	ifnull( sum( csod.delivery_money ), 0 ) AS deliveryMoney,
	ifnull( sum( csod.settle_money ), 0 ) AS settleMoney,
	sum( csod.amount * csod.money ) AS detailTotalMoney,
	cc.market_manage AS marketManage,
	mm.NAME AS marketManageName,
	cc.server_manage AS serverManage,
	sm.NAME AS serverManageName,
	cc.invite_manage AS inviteManage,
	im.NAME AS inviteManageName,
	my.order_market_manage AS orderMarketManage,
	omm.NAME AS orderMarketManageName,
	my.order_server_manage AS orderServerManage,
	osm.NAME AS orderServerManageName,
	my.market_director AS marketDirector,
	md.NAME AS marketDirectorName,
	my.server_director AS serverDirector,
	sd.NAME AS serverDirectorName,
	au.NAME AS developManageName,
	csodm.NAME AS saleOrderDevelopManageName,
IF
	( coc.id IS NULL, 1, 2 ) AS orderContractStatus,
	coc.audit_status AS orderContractAuditStatus,
	my.extend_teacher_id AS extendTeacherId,
	(SELECT NAME FROM pub_department WHERE id=my.extend_teacher_id) AS extendTeacherName,
IF
	( ecd.sign_status IS NULL, 0, ecd.sign_status ) AS esignContractSignStatus 
FROM
	crm_sale_order my
	LEFT JOIN crm_customer cc ON cc.id = my.customer_id
	LEFT JOIN crm_contact_person ccp ON ccp.id = my.contact_person_id
	LEFT JOIN pub_department dep ON dep.id = cc.department_id
	LEFT JOIN pub_department csopd ON csopd.id = my.department_id
	LEFT JOIN pub_department parentpd ON parentpd.id = dep.parent_id
	LEFT JOIN pub_department parentcsopd ON parentcsopd.id = csopd.parent_id
	LEFT JOIN sys_user su ON su.id = my.create_by
	LEFT JOIN sys_user coun ON coun.id = my.counselor
	LEFT JOIN crm_course course ON course.id = my.course_id
	LEFT JOIN pub_department achi ON achi.id = my.achievement_department
	LEFT JOIN crm_sale_order_detail csod ON csod.sale_order_id = my.id 
	AND csod.deleted = 0
	LEFT JOIN sys_user mm ON mm.id = cc.market_manage
	LEFT JOIN sys_user sm ON sm.id = cc.server_manage
	LEFT JOIN sys_user im ON im.id = cc.invite_manage
	LEFT JOIN sys_user omm ON omm.id = my.order_market_manage
	LEFT JOIN sys_user osm ON osm.id = my.order_server_manage
	LEFT JOIN sys_user md ON md.id = my.market_director
	LEFT JOIN sys_user sd ON sd.id = my.server_director
	LEFT JOIN crm_agent ca ON ca.department_id = cc.department_id
	LEFT JOIN crm_agent csoca ON csoca.department_id = my.department_id
	LEFT JOIN sys_user au ON au.id = ca.develop_manage
	LEFT JOIN sys_user csodm ON csodm.id = csoca.develop_manage
	LEFT JOIN crm_order_contract coc ON coc.sale_order_id = my.id 
	AND coc.deleted = 0
	LEFT JOIN esign_contract ec ON ec.sale_order_id = my.id
	LEFT JOIN esign_contract_detail ecd ON ecd.contract_id = ec.id 
	AND ecd.deleted = 0 
	AND ecd.record_type = 2 
WHERE
	my.deleted = 0 
GROUP BY
	my.id 
ORDER BY
	id DESC 
	LIMIT 1

  113行的sql，这还是没有传条件筛选。那么执行这条sql要多久呢？

1 row in set (4.59 sec)

  看到这个速度，想来大家都觉得理所应当吧？关联这么多张表不慢才怪！或许许多大佬已经看到了这个sql各种可以优化的点了吧。别急，我们先来看看执行计划：

  看完执行计划，想必大家都看得出来这个sql在增加索引方面基本上没有什么可以优化的了，唯一没有使用到索引的那张别名为ecd的表也只有两条数据而已。
  但是有经验或者细心的小伙伴肯定发现有两个地方的Extra描述跟其他的不一样：

• 第一行：Using temporary; Using filesort
• 倒数第二行：Using where; Using join buffer (Block Nested Loop)

  第一个大家应该都知道，这是用到了非索引列排序或者使用索引列倒叙排序就会这样，大概就是存储引擎将数据缓存，然后在通过算法进行排序。这个过程肯定会影响到查询的速度。

  我以前就碰到过，有些sql不加排序只要零点几秒，加上排序就要几秒钟。但是一点办法没有，需求就是要倒叙，想来很多人也遇到过这个问题。所以MySQL在8.0后增加一种索引：倒叙索引，专门用来解决这个问题，这里我就不做扩展了，有兴趣的伙伴可以问度娘或者看看Mysql官方文档。

  第二个也经常遇到，只是没有第一个那么好理解，这里我截取Mysql官方文档片段解释：

这是另外一段描述：
Block Nested-Loop Join Algorithm
  A Block Nested-Loop (BNL) join algorithm uses buffering of rows read in outer loops to reduce the number of times that tables in inner loops must be read. For example, if 10 rows are read into a buffer and the buffer is passed to the next inner loop, each row read in the inner loop can be compared against all 10 rows in the buffer. This reduces by an order of magnitude the number of times the inner table must be read.
  Prior to MySQL 8.0.18, this algorithm was applied for equi-joins when no indexes could be used; in MySQL 8.0.18 and later, the hash join optimization is employed in such cases. Starting with MySQL 8.0.20, a hash join is also employed for any other join for which no indexes can be used, rather than the block nested-loop algorithm. See Section 8.2.1.4, “Hash Join Optimization”.
  MySQL join buffering has these characteristics:
  1. Join buffering can be used when the join is of type ALL or index (in other words, when no possible keys can be used, and a full scan is done, of either the data or index rows, respectively), or range. Use of buffering is also applicable to outer joins, as described in Section 8.2.1.12, “Block Nested-Loop and Batched Key Access Joins”.
  2. A join buffer is never allocated for the first nonconstant table, even if it would be of type ALL or index.
  3. Only columns of interest to a join are stored in its join buffer, not whole rows.
  4. The join_buffer_size system variable determines the size of each join buffer used to process a query.
  5. One buffer is allocated for each join that can be buffered, so a given query might be processed using multiple join buffers.
  6. A join buffer is allocated prior to executing the join and freed after the query is done.
 
译文：
块嵌套循环连接算法
  块嵌套循环（BNL）嵌套算法使用对在外部循环中读取的行进行缓冲，以减少必须读取内部循环中的表的次数。例如，如果将10行读入缓冲区并将缓冲区传递到下一个内部循环，则可以将内部循环中读取的每一行与缓冲区中的所有10行进行比较。这将内部表必须读取的次数减少了一个数量级。
  在MySQL 8.0.18之前，当无法使用索引时，该算法适用于等联接。在MySQL 8.0.18及更高版本中，在这种情况下采用了哈希联接优化。从MySQL 8.0.20开始，哈希联接也用于任何其他不能使用索引的联接，而不是块嵌套循环算法。请参见 第8.2.1.4节“哈希联接优化”。
  MySQL连接缓冲具有以下特征：
  1. 当连接的类型为ALL或时 index（换句话说，当无法使用任何可能的键并分别对数据或索引行进行完整扫描 时），可以使用连接缓冲 range。缓冲的使用也适用于外部联接，如第8.2.1.12节“阻止嵌套循环和批处理键访问联接”所述。
  2. 连接缓冲区永远不会分配给第一个非恒定表，即使它的类型是 ALL或 index。
  3. 联接中只有感兴趣的列存储在其联接缓冲区中，而不是整个行。
  4. 可以通过系统变量 join_buffer_size 设置用于处理查询每个联接的缓存区大小。
  5. 为每个可以缓冲的连接分配一个缓冲区，因此可以使用多个连接缓冲区来处理给定查询。
  6. 在执行连接之前分配连接缓冲区，并在查询完成后释放连接缓冲区。

   这个过程就像是一个嵌套的循环，这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接（Nested-Loop Join），这是最简单，也是最笨拙的一种连接查询算法。

   嵌套循环是一条记录一条记录循环的，速度慢，所以使用了块嵌套循环(Block Nested Loop)，把之前的一条记录变成多条，这样就减少了循环次数，也就减少了IO次数，从而加快查询速度。

拓展：
   上面说的"一条记录变成多条"具体是指多少条呢？这跟MySQL的join buffer大小有关，而join buffer的值取决于环境变量join_buffer_size，默认256k。
   关于环境变量作者也有一遍专门的博客：影响MySQL性能的环境变量详解

   块嵌套循环(Block Nested Loop)实际效率还是比较低的，所以一般出现这个并且查询效率较低时就需要将它优化。

   那么怎么优化呢？自然是加索引！如果我们再仔细看一遍这行的执行计划和相关的sql就会发现这个关联查询其实是加了索引的！ 那么为什么没有用到呢？这是Mysql优化器决定的，因为ecd这张表只有两条数据，不需要用到索引（我们之前的想法跟Mysql优化器的算法一样呢 哈哈）。但是既然出现了这种情况我们就只能强制使用索引了！使用 FORCE INDEX(INDEX_NAME)：

-- 这里就只把变化的地方写出来了: 加了 FORCE INDEX(idx_contractId)
-- 加这个的意思就是强制使用指定的索引，idx_contractId是索引名
-- 注意：要哪张表强制使用索引就把FORCE关键字写在哪张表后面
LEFT JOIN esign_contract_detail ecd FORCE INDEX(idx_contractId)
	ON ecd.contract_id = ec.id 
	AND ecd.deleted = 0 
	AND ecd.record_type = 2 

   我们再来看看加上这个之后的执行时间：

1 row in set (0.03 sec)

   看到这个数字很意外吧？起码我当时是很意外的，我当时只是抱着尝试的想法试试的，没想到效果这么明显！直接就快了150倍！同时也说明优化块嵌套循环(Block Nested Loop) 的必要性！

  你以为这个sql的优化就结束了吗？不才刚刚开始！

  除了之前提到的Mysql8的倒叙索引 ，索引方面基本上没有可以优化的了（当然前提是只聊有没有用到索引这个问题，更深层的我们以后再说）。除了通过索引优化，在sql层面也有很多：

• limit优化
• 表关联顺序优化
• 减少关联表
• 我暂时想到这几个，如果有没考虑到的大家可以指点一下呀。

limit优化

   这个优化思想很简单，就是减少数据库引擎扫描的数据量，根据执行计划的rows列，我们可以看到主表的rows的值为15946，而我们只需要1条或者几十条，那么就可以优化如下：

-- 我这里为了减少篇幅就不一一写列名直接用了*，大家在实际使用时可不要偷懒哦！！！
FROM
	(select * from crm_sale_order LIMIT 1) my
	LEFT JOIN crm_customer cc ON cc.id = my.customer_id

   这样优化之后的速度如何呢？

1 row in set (0.00 sec)
-- 实际执行时间大概是 0.0015s
-- 可以通过 profiles 看到更详细的执行时间哦，没用过的话就问下度娘吧，我这里就不做赘述了。

   这个时间就比较满意了吧？起码我是满意了。
   当然这也是因为我这些表数据量都不大，连超过5位数的都没有。不然如果数据量到了百万千万级还用这么多关联表速度肯定会非常慢的，我这里也只是抛砖引玉，给大家分享一下我的一次优化的经验。

总结
为了优化这条sql我们用到了一下几点：
1、FORCE INDEX 强制使用索引，规避块嵌套循环。
2、优化了LIMIT语句，极大减少了需要扫描的数据量。
3、使用倒叙索引优化排序问题，规避文件排序。
4、还可以减少关联表数量，将部分工作转到程序上实现。

  如果这篇文章对你有所启发或帮助，可以点赞支持一下哦~