mysql - GROUP BY WHERE range AND const ref 没有临时

Question

一个非常基本的表格，其中包含利用 TokuDB 存储引擎的多个交易所的工具报价:

CREATE TABLE `quotes` (
  `ticker` char(4) NOT NULL,
  `timestamp` time(3) NOT NULL,
  `price` decimal(7,2) unsigned NOT NULL,
  `size` smallint(5) unsigned NOT NULL,
  `exchange` char(3) NOT NULL,
  KEY `best_price` (`ticker`,`timestamp`,`exchange`,`price`),
  KEY `best_size` (`exchange`,`ticker`,`price`,`timestamp`)
) ENGINE=TokuDB

每当我查询所有交易所的最佳价格时，它总是使用一个临时表。索引中 exchange 和 price 的存在似乎只产生一个索引扫描，相当于 (ticker, timestamp) 中的聚集键TokuDB.

EXPLAIN SELECT max(price),exchange
FROM quotes
WHERE
  ticker="A" AND
  timestamp BETWEEN "15:15:22.328961" AND "15:17:22.328961"
GROUP BY exchange
ORDER BY NULL \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: quotes
         type: range
possible_keys: best_price
          key: best_price
      key_len: 9
          ref: NULL
         rows: 2690
        Extra: Using where; Using index; Using temporary

是否可以定义一个不使用临时表的配置？这只有在删除 timestamp 文章时才会出现:

EXPLAIN SELECT max(price),exchange
FROM quotes
WHERE
  ticker="A"
GROUP BY exchange
ORDER BY NULL \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: quotes
         type: range
possible_keys: best_price
          key: best_size
      key_len: 7
          ref: NULL
         rows: 96
        Extra: Using where; Using index for group-by

最佳价格查询的示例输出:

+------------+----------+
| max(price) | exchange |
+------------+----------+
|      41.06 | BTY      |
|      41.06 | DEA      |
|      41.07 | NYS      |
|      41.07 | THM      |
|      41.06 | PSE      |
|      41.07 | BAT      |
|      41.06 | DEX      |
|      41.06 | BOS      |
|      41.06 | ADC      |
|      41.06 | XPH      |
+------------+----------+
10 rows in set (0.01 sec)

瓶颈 (3ms) 是处理时间范围内的每一行:

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000071 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000016 |
| After opening tables | 0.000006 |
| System lock          | 0.000014 |
| Table lock           | 0.000002 |
| After table lock     | 0.000005 |
| init                 | 0.000038 |
| optimizing           | 0.000024 |
| statistics           | 0.000155 |
| preparing            | 0.000028 |
| executing            | 0.000003 |
| Copying to tmp table | 0.000031 |
| Copying to tmp table | 0.003381 |
| Sending data         | 0.000017 |
| end                  | 0.000004 |
| removing tmp table   | 0.000020 |
| end                  | 0.000002 |
| query end            | 0.000005 |
| closing tables       | 0.000005 |
| freeing items        | 0.000006 |
| updating status      | 0.000011 |
| cleaning up          | 0.000002 |
+----------------------+----------+

时间范围包含 2316 行，按交易所分割:

+----------+----------+
| exchange | count(*) |
+----------+----------+
| ADC      |       71 |
| BAT      |      298 |
| BOS      |      129 |
| BTY      |      266 |
| DEA      |      153 |
| DEX      |       60 |
| NYS      |      530 |
| PSE      |      325 |
| THM      |      453 |
| XPH      |       31 |
+----------+----------+

我尝试了疯狂并添加了覆盖索引的每个排列，而 MariaDB 找不到更好的键。我应该查看其他数据库吗？

时间范围和股票代码的示例数据集:http://pastebin.com/b5RcTXAs

Answer 1

答案全在于优化。 MySQL 选择使用临时表，因为它被认为比使用索引来获取每个交换更明智。如果在 exchange,ticker,timestamp 中使用聚集索引，则查询可以在没有临时索引的情况下运行:

MariaDB [trth]> explain SELECT min(ask_price),exchange
FROM quotes
USE INDEX (exchange_ticker_timestamp)
WHERE exchange IN ("NYS","BOS","CIN","ADC","DEX","DEA","MID","PSE","THM","WCB","BAT","XPH","BTY") AND 
   ticker="A" AND
   timestamp BETWEEN "15:15:22.328961" AND "15:17:22.328961"
GROUP BY exchange
ORDER BY NULL \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: quotes
         type: range
possible_keys: exchange_ticker_timestamp
          key: exchange_ticker_timestamp
      key_len: 10
          ref: NULL
         rows: 2589
        Extra: Using where; Using index

MariaDB [trth]> show profile; 
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000079 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000014 |
| After opening tables | 0.000011 |
| System lock          | 0.000014 |
| Table lock           | 0.000003 |
| After table lock     | 0.000005 |
| init                 | 0.000043 |
| optimizing           | 0.000019 |
| statistics           | 0.000234 |
| preparing            | 0.000027 |
| executing            | 0.000008 |
| Sorting result       | 0.000002 |
| Sending data         | 0.002985 |
| end                  | 0.000006 |
| query end            | 0.000010 |
| closing tables       | 0.000006 |
| freeing items        | 0.000007 |
| updating status      | 0.000138 |
| cleaning up          | 0.000004 |
+----------------------+----------+

与临时表分组比较:

MariaDB [trth]> explain SELECT min(ask_price),exchange
FROM quotes
WHERE ticker="A" AND
   timestamp BETWEEN "15:15:22.328961" AND "15:17:22.328961"
GROUP BY exchange
ORDER BY NULL \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: quotes
         type: range
possible_keys: ticker_timestamp
          key: ticker_timestamp
      key_len: 9
          ref: NULL
         rows: 1515
        Extra: Using where; Using temporary

MariaDB [trth]> show profile;  
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000091 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000037 |
| After opening tables | 0.000009 |
| System lock          | 0.000052 |
| Table lock           | 0.000004 |
| After table lock     | 0.000009 |
| init                 | 0.000049 |
| optimizing           | 0.000025 |
| statistics           | 0.000144 |
| preparing            | 0.000039 |
| executing            | 0.000003 |
| Copying to tmp table | 0.000040 |
| Copying to tmp table | 0.004674 |
| Sending data         | 0.000020 |
| end                  | 0.000003 |
| removing tmp table   | 0.000015 |
| end                  | 0.000003 |
| query end            | 0.000004 |
| closing tables       | 0.000006 |
| freeing items        | 0.000006 |
| updating status      | 0.000204 |
| cleaning up          | 0.000004 |
+----------------------+----------+

这里值得注意的是，第一个查询扫描的行数更多，但执行速度比第二个查询快。