如何摆脱在MySQL中使用DISTINCT时临时的用法？

Question

当我做以下操作时，我得到了Using where; Using index; Using temporary。我如何摆脱Using temporary？

http://sqlfiddle.com/#!8/f61a2/3/0

CREATE TABLE `test` (
  `a` varchar(45) NOT NULL,
  `b` varchar(45) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `index1` (`b`,`date`),
  KEY `index2` (`b`,`date`,`a`)
) ENGINE=InnoDB;

INSERT INTO test (a, b, date) VALUES ('a1', 'b1', now());
INSERT INTO test (a, b, date) VALUES ('a1', 'b2', now());
INSERT INTO test (a, b, date) VALUES ('a2', 'b1', now());
INSERT INTO test (a, b, date) VALUES ('a2', 'b2', now());

INSERT INTO test (a, b, date) VALUES ('a3', 'b1', '2000-01-01 01:01:01');
INSERT INTO test (a, b, date) VALUES ('a3', 'b2', '2000-01-01 01:01:01');

EXPLAIN SELECT
DISTINCT a
FROM test
WHERE b IN ('b1', 'b2') AND
date > NOW() - INTERVAL 1 MONTH;

Answer 1

您是否存在性能问题，因为这听起来像是过早的优化？

使用索引的唯一好的单个查询是一个UNION (它还将过滤掉重复的查询)。

如果您在此查询上运行EXPLAIN，则在解释中不会弹出使用临时的..。

SELECT
 a
FROM test
WHERE b = 'b1' AND
date > NOW() - INTERVAL 1 MONTH

UNION

SELECT
 a
FROM test
WHERE b = 'b2' AND
date > NOW() - INTERVAL 1 MONTH
;

编辑2019-09-01

演示不再有效，因为木琴不再运行旧的MySQL版本，而且由于MySQL源代码已经更新.我建议查看大小提琴并在MySQL版本5.5/ 5.6 /5.7之间切换，然后这个问题应该在5.5到5.6之间出现。

通过查看源代码文件sql/union.cc (C++代码)的MySQL版本5.7.2 m12 (最有可能(所有)较低版本也使用此代码，但不确定这一点)，我们可以看到联合如何在MySQL中工作。您会发现，解释输出有时会告诉您谎言，因为源代码(在一个输入参数中)表明：

如果设置为is_union_distinct，则临时表将消除插入时的重复项。

这是有意义的，因为复制的记录被过滤掉了。源代码快照如下：

/*
  Create a temporary table to store the result of select_union.

  SYNOPSIS
    select_union::create_result_table()
      thd                thread handle
      column_types       a list of items used to define columns of the
                         temporary table
      is_union_distinct  if set, the temporary table will eliminate
                         duplicates on insert
      options            create options
      table_alias        name of the temporary table
      bit_fields_as_long convert bit fields to ulonglong

  DESCRIPTION
    Create a temporary table that is used to store the result of a UNION,
    derived table, or a materialized cursor.

  RETURN VALUE
    0                    The table has been created successfully.
    1                    create_tmp_table failed.
*/

bool
select_union::create_result_table(THD *thd_arg, List<Item> *column_types,
                                  bool is_union_distinct, ulonglong options,
                                  const char *table_alias,
                                  bool bit_fields_as_long, bool create_table)
{
  DBUG_ASSERT(table == 0);
  tmp_table_param.init();
  count_field_types(thd_arg->lex->current_select(), &tmp_table_param,
                    *column_types, false, true);
  tmp_table_param.skip_create_table= !create_table;
  tmp_table_param.bit_fields_as_long= bit_fields_as_long;

  if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
                                 (ORDER*) 0, is_union_distinct, 1,
                                 options, HA_POS_ERROR, (char*) table_alias)))
    return TRUE;
  if (create_table)
  {
    table->file->extra(HA_EXTRA_WRITE_CACHE);
    table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
  }
  return FALSE;

}

有一个叫做create_tmp_table的函数，因此当函数create_result_table被称为

时，MySQL总是创建一个临时表。

Answer 2

看起来这是数据类型转换的问题。下面的查询不需要临时表

EXPLAIN extended 
SELECT
DISTINCT a
FROM test
WHERE b in ( 'b1', 'b2') AND
date > CURRENT_DATE