Alguma desvantagem de "SQL_BUFFER_RESULT" para MySQL SELECT?

SQL_BUFFER_RESULTcria uma tabela temporária no servidor para cada conjunto de resultados. Esta não é uma tabela temporária como CREATE TEMPORARY TABLE- é uma tabela temporária implícita, como seria criada ao usar uma cláusula GROUP BY ou com uma subconsulta. Como tal, todas as mesmas regras se aplicam .

Primeiramente, vamos falar do problema que SQL_BUFFER_RESULTse pretende resolver:

Quando o cliente solicita dados do servidor, até que todo o conjunto de resultados tenha sido transmitido ao cliente, a consulta ainda está "em execução" e alguns bloqueios ainda podem ser mantidos. Enquanto os dados estão sendo transmitidos, eles aparecerão no Sending dataestado. Depende da biblioteca se o cliente apenas obtém todos os dados de uma vez quando a consulta é executada ou se eles vão aparecendo conforme você seleciona as linhas, mas um exemplo de problema é o seguinte:

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

Obviamente artificial, mas no caso acima, se houver 1.000 linhas, a consulta ainda será executada ativamente por 10.000 segundos. Isso pode parecer muito improvável, mas muitos aplicativos têm "tempo de reflexão" entre a busca de cada linha porque eles fazem algum processamento. Isso é "uma coisa muito ruim de se fazer". Outro caso em que esse tipo de efeito de gotejamento pode ocorrer é com um grande conjunto de resultados em uma conexão lenta. Em última análise, o problema é o fluxo de dados para o cliente, fazendo com que a consulta permaneça em um estado ativo. SQL_BUFFER_RESULTresolve esse problema armazenando primeiro o resultado em uma tabela temporária, o que faz com que a consulta termine mais rapidamente, liberando todos os seus bloqueios (que bloqueios?). O conjunto de resultados é então alimentado ao cliente a partir da tabela temporária em vez da própria consulta.

Isso parece ótimo!

Mas...

• As tabelas temporárias ocupam memória e outros recursos no servidor. Muitas tabelas temporárias == muitos recursos.
• As tabelas que excedem o mínimo de tmp_table_size ou max_heap_table_size acabarão sendo convertidas em tabelas temporárias em disco, o que significa E/S adicional. Isso também significa que suas consultas demoram mais, pois assim que o tamanho da tabela MEMORY atinge tmp_table_size, ela é convertida em uma tabela MyISAM em disco.
• Os campos BLOB/TEXT não podem ser armazenados como tabelas temporárias na memória (o mecanismo de armazenamento MEMORY não os suporta), portanto, sempre serão criados no disco.
• criar tabelas temporárias é caro, especialmente quando elas estão em disco.
• Considere que uma das primeiras coisas que tentamos otimizar ao olhar para um plano de explicação é "Usar temporário". SQL_BUFFER_RESULTliteralmente faz com que todas as consultas incluam "Usando temporário" (quase) o tempo todo. Meu pequeno teste mostrou que havia casos em que o MySQL não usava uma tabela temporária mesmo com essa dica, mas eles eram limitados (pesquisas de linha única na chave primária pareciam ser o único caso).

Aqui estão alguns exemplos para mostrar o efeito:

-- 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)

Para que serve o problema que está sendo resolvido? "fechaduras". Como esta é uma dica para consultas SELECT, a menos que você esteja usando FOR UPDATE ou LOCK IN SHARED MODE, há muito poucos bloqueios feitos em leituras de qualquer maneira e eles são muito rápidos, então você está resolvendo principalmente um problema que não existe. Exceções sempre existem, mas a sobrecarga de criar uma tabela temporária para quase todas as consultas superará em muito qualquer benefício obtido ao fazer com que os bloqueios desapareçam mais rapidamente.

A recomendação do MySQL é usar a SQL_BUFFERED_RESULTdica ao recuperar um conjunto de resultados muito grande em uma conexão de rede para o cliente. Não consigo ver nenhum valor em usá-lo em outro contexto.

Dito isso, você pode testar e definir isso "globalmente" definindo sql_buffer_result =1 no início de cada sessão. Em um ambiente com qualquer simultaneidade, eu prevejo resultados ruins.