MySQL SELECT 的“SQL_BUFFER_RESULT”有什么缺点吗？

SQL_BUFFER_RESULT在服务器上为每个结果集创建一个临时表。这不是一个临时表CREATE TEMPORARY TABLE，它是一个隐式临时表，在使用 GROUP BY 子句或子查询时创建。因此，所有相同的规则都适用。

首先，让我们谈谈SQL_BUFFER_RESULT旨在解决的问题：

当客户端从服务器请求数据时，直到整个结果集被传输到客户端，查询仍然“运行”并且可能仍然持有一些锁。在数据传输过程中，它会出现在Sending data状态中。它是依赖于客户端是否在执行查询时立即获取所有数据的库，或者它是否在您选择行时涓涓细流，但一个问题的示例如下：

resultset = conn["SELECT * FROM bigtable"]
resultset.each do |row|
  data[:value] = row[:value]
  sleep 10 # do something expensive here
end

显然是人为的，但在上面的例子中，如果有 1000 行，查询仍然会主动运行 10,000 秒。这似乎有些牵强，但许多应用程序在获取每一行之间都有“思考时间”，因为它们会进行一些处理。这是“一件非常糟糕的事情”。可能发生这种类型的涓流效应的另一种情况是在慢速连接上的大型结果集。最终，问题是数据向客户端的涓涓细流导致查询保持在活动状态。 SQL_BUFFER_RESULT通过首先将结果缓冲到临时表中来解决这个问题，这使得查询结束得更快，从而释放了它的所有锁（什么锁？）。然后将结果集从临时表而不是查询本身提供给客户端。

听起来不错！

但...

• 临时表占用服务器上的内存和其他资源。大量临时表 == 大量资源。
• 超过tmp_table_size或 max_heap_table_size最小值的表最终将被转换为磁盘临时表，这意味着额外的 I/O。这也意味着您的查询需要更长的时间，因为一旦 MEMORY 表的大小达到 tmp_table_size，它就会被转换为磁盘上的 MyISAM 表。
• BLOB/TEXT 字段不能存储为内存中的临时表（MEMORY 存储引擎不支持它们），因此将始终在磁盘上创建。
• 创建临时表的成本很高，尤其是当它们在磁盘上时。
• 考虑到我们在查看解释计划时尝试优化的第一件事是“使用临时”。 SQL_BUFFER_RESULT从字面上看，每个查询都包括“使用临时”，（几乎）一直。我的一点点测试表明，即使有这个提示，MySQL 也没有使用临时表，但它们是有限的（主键上的单行查找似乎是唯一的情况）。

下面举几个例子来说明效果：

-- unindexed lookup w/out SQL_BUFFER_RESULT
mysql> explain select  * from actor where first_name = 'THORA'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 203
        Extra: Using where
1 row in set (0.00 sec)

-- unindexed lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where first_name = 'THORA'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 203
        Extra: Using where; Using temporary
1 row in set (0.00 sec)

-- indexed lookup w/out SQL_BUFFER_RESULT
mysql> explain select * from actor where last_name = 'TEMPLE'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)


-- indexed lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where last_name = 'TEMPLE'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 4
        Extra: Using where; Using temporary
1 row in set (0.00 sec)


-- primary key lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where actor_id = 200\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

解决的问题是什么？“锁。” 由于这是 SELECT 查询的提示，除非您使用 FOR UPDATE 或 LOCK IN SHARED MODE，否则读取时很少有锁，而且速度非常快，因此您主要解决的是不存在的问题。异常总是存在的，但是为几乎每个查询创建一个临时表的开销将远远超过使锁更快消失所带来的任何好处。

MySQL 的建议是在通过SQL_BUFFERED_RESULT与客户端的网络连接检索非常大的结果集时使用提示。我看不出在其他情况下使用它有什么价值。

话虽如此，您可以通过在每个会话开始时设置sql_buffer_result =1 来“全局”测试和设置它。在具有任何并发​​性的环境中，我会预测结果不佳。