可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

This is a followup to my previous question

Optimizing query to get entire row where one field is the maximum for a group

I'll change the names from what I used there to make them a little more memorable, but these don't represent my actual use-case (so don't estimate the number of records from them).

I have a table with a schema like this:

OrderTime           DATETIME(6),
Customer            VARCHAR(50),
DrinkPrice          DECIMAL,
Bartender           VARCHAR(50),
TimeToPrepareDrink  TIME(6),
...

I'd like to extract the rows from the table representing each customer's most expensive drink order during happy hour (3 PM - 6 PM) each day. So for instance I'd want results like

Date   | Customer | OrderTime   | MaxPrice   | Bartender | ...
-------+----------+-------------+------------+-----------+-----
1/1/18 |  Alice   | 1/1/18 3:45 | 13.15      | Jane      | ...
1/1/18 |  Bob     | 1/1/18 5:12 |  9.08      | Jane      | ...
1/1/18 |  Carol   | 1/1/18 4:45 | 20.00      | Tarzan    | ...
1/2/18 |  Alice   | 1/2/18 3:45 | 13.15      | Jane      | ...
1/2/18 |  Bob     | 1/2/18 5:57 |  6.00      | Tarzan    | ...
1/2/18 |  Carol   | 1/2/18 3:13 |  6.00      | Tarzan    | ...
 ...

The table has an index on OrderTime, and contains tens of billions of records. (My customers are heavy drinkers).

Thanks to the previous question I'm able to extract this for a specific day pretty easily. I can do something like:

SELECT * FROM orders b
INNER JOIN (
    SELECT Customer, MAX(DrinkPrice) as MaxPrice
    FROM orders
    WHERE OrderTime >= '2018-01-01 15:00' 
      AND OrderTime <= '2018-01-01 18:00'
    GROUP BY Customer
) AS a
ON a.Customer = b.Customer
AND a.MaxPrice = b.DrinkPrice
WHERE b.OrderTime >= '2018-01-01 15:00'
  AND b.OrderTime <= '2018-01-01 18:00';

This query runs in less than a second. The explain plan looks like this:

+---+-------------+------------+-------+---------------+------------+--------------------+--------------------------------------------------------+
| id| select_type | table      | type  | possible_keys | key        | ref                | Extra                                                  |
+---+-------------+------------+-------+---------------+------------+--------------------+--------------------------------------------------------+
| 1 | PRIMARY     | b          | range | OrderTime     | OrderTime  | NULL               | Using index condition                                  |
| 1 | PRIMARY     | <derived2> | ref   | key0          | key0       | b.Customer,b.Price |                                                        |
| 2 | DERIVED     | orders     | range | OrderTime     | OrderTime  | NULL               | Using index condition; Using temporary; Using filesort |
+---+-------------+------------+-------+---------------+------------+--------------------+--------------------------------------------------------+

I can also get the information about the relevant rows for my query:

SELECT Date, Customer, MAX(DrinkPrice) AS MaxPrice
FROM
        orders
    INNER JOIN
        (SELECT '2018-01-01' AS Date 
         UNION
         SELECT '2018-01-02' AS Date) dates
WHERE   OrderTime >= TIMESTAMP(Date, '15:00:00')
AND OrderTime <= TIMESTAMP(Date, '18:00:00')
GROUP BY Date, Customer
 HAVING MaxPrice > 0;

This query also runs in less than a second. Here's how its explain plan looks:

+------+--------------+------------+------+---------------+------+------+------------------------------------------------+
| id   | select_type  | table      | type | possible_keys | key  | ref  | Extra                                          |
+------+--------------+------------+------+---------------+------+------+------------------------------------------------+
|    1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL | Using temporary; Using filesort                |
|    1 | PRIMARY      | orders     | ALL  | OrderTime     | NULL | NULL | Range checked for each record (index map: 0x1) |
|    2 | DERIVED      | NULL       | NULL | NULL          | NULL | NULL | No tables used                                 |
|    3 | UNION        | NULL       | NULL | NULL          | NULL | NULL | No tables used                                 |
| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL |                                                |
+------+--------------+------------+------+---------------+------+------+------------------------------------------------+

The problem now is retrieving the remaining fields from the table. I tried adapting the trick from before, like so:

SELECT * FROM
        orders a
    INNER JOIN
        (SELECT Date, Customer, MAX(DrinkPrice) AS MaxPrice
        FROM
                orders
            INNER JOIN
                (SELECT '2018-01-01' AS Date
                 UNION
                 SELECT '2018-01-02' AS Date) dates
        WHERE   OrderTime >= TIMESTAMP(Date, '15:00:00')
            AND OrderTime <= TIMESTAMP(Date, '18:00:00')
        GROUP BY Date, Customer
        HAVING MaxPrice > 0) b
    ON     a.OrderTime >= TIMESTAMP(b.Date, '15:00:00')
       AND a.OrderTime <= TIMESTAMP(b.Date, '18:00:00')
       AND a.Customer = b.Customer;

However, for reasons I don't understand, the database chooses to execute this in a way that takes forever. Explain plan:

+------+--------------+------------+------+---------------+------+------------+------------------------------------------------+
| id   | select_type  | table      | type | possible_keys | key  | ref        | Extra                                          |
+------+--------------+------------+------+---------------+------+------------+------------------------------------------------+
|    1 | PRIMARY      | a          | ALL  | OrderTime     | NULL | NULL       |                                                |
|    1 | PRIMARY      | <derived2> | ref  | key0          | key0 | a.Customer | Using where                                    |
|    2 | DERIVED      | <derived3> | ALL  | NULL          | NULL | NULL       | Using temporary; Using filesort                |
|    2 | DERIVED      | orders     | ALL  | OrderTime     | NULL | NULL       | Range checked for each record (index map: 0x1) |
|    3 | DERIVED      | NULL       | NULL | NULL          | NULL | NULL       | No tables used                                 |
|    4 | UNION        | NULL       | NULL | NULL          | NULL | NULL       | No tables used                                 |
| NULL | UNION RESULT | <union3,4> | ALL  | NULL          | NULL | NULL       |                                                |
+------+--------------+------------+------+---------------+------+------------+------------------------------------------------+

Questions:

What is going on here?
How can I fix it?

回答1:

To extract the rows from the table representing each customer's most expensive drink order during happy hour (3 PM - 6 PM) each day I would use row_number() over() within a case expression evaluating the hour of day, like this:

CREATE TABLE mytable(
   Date      DATE 
  ,Customer  VARCHAR(10)
  ,OrderTime DATETIME 
  ,MaxPrice  NUMERIC(12,2)
  ,Bartender VARCHAR(11)
);

note changes were made to OrderTime

INSERT INTO mytable(Date,Customer,OrderTime,MaxPrice,Bartender) 
VALUES 
  ('1/1/18','Alice','1/1/18 13:45',13.15,'Jane')
, ('1/1/18','Bob'  ,'1/1/18 15:12', 9.08,'Jane')
, ('1/2/18','Alice','1/2/18 13:45',13.15,'Jane')
, ('1/2/18','Bob'  ,'1/2/18 15:57', 6.00,'Tarzan')
, ('1/2/18','Carol','1/2/18 13:13', 6.00,'Tarzan')
;

The suggested query is this:

select
    *
from (
    select
        *
        , case when hour(OrderTime) between 15 and 18 then 
                row_number() over(partition by `Date`, customer
                                      order by MaxPrice DESC)
                else null 
          end rn
    from mytable
    ) d
where rn = 1
;

and the result will give access to all columns you include in the derived table.

Date       | Customer | OrderTime           | MaxPrice | Bartender | rn
:--------- | :------- | :------------------ | -------: | :-------- | -:
0001-01-18 | Bob      | 0001-01-18 15:12:00 |     9.08 | Jane      |  1
0001-02-18 | Bob      | 0001-02-18 15:57:00 |     6.00 | Tarzan    |  1

To help display how this works, running the derived table subquery:

select
*
, case when hour(OrderTime) between 15 and 18 then 
        row_number() over(partition by `Date`, customer order by MaxPrice DESC)
        else null 
  end rn
from mytable
;

produces this interim resultset:

Date       | Customer | OrderTime           | MaxPrice | Bartender |   rn
:--------- | :------- | :------------------ | -------: | :-------- | ---:
0001-01-18 | Alice    | 0001-01-18 13:45:00 |    13.15 | Jane      | null
0001-01-18 | Bob      | 0001-01-18 15:12:00 |     9.08 | Jane      |    1
0001-02-18 | Alice    | 0001-02-18 13:45:00 |    13.15 | Jane      | null
0001-02-18 | Bob      | 0001-02-18 15:57:00 |     6.00 | Tarzan    |    1
0001-02-18 | Carol    | 0001-02-18 13:13:00 |     6.00 | Tarzan    | null

db<>fiddle here

回答2:

The task seems to be a "groupwise-max" problem. Here's one approach, involving only 2 'queries' (the inner one is called a "derived table").

SELECT  x.OrderDate, x.Customer, b.OrderTime,
        x.MaxPrice, b.Bartender
    FROM  
    (
        SELECT  DATE(OrderTime) AS OrderDate,
                Customer,
                Max(Price) AS MaxPrice
            FROM  tbl
            WHERE  TIME(OrderTime) BETWEEN '15:00' AND '18:00'
            GROUP BY  OrderDate, Customer 
    ) AS x
    JOIN  tbl AS b
       ON  b.OrderDate = X.OrderDate
      AND  b.customer = x.Customer
      AND  b.Price = x.MaxPrice
    WHERE  TIME(b.OrderTime) BETWEEN '15:00' AND '18:00'
    ORDER BY  x.OrderDate, x.Customer

Desirable index:

INDEX(Customer, Price)

(There's no good reason to be using MyISAM.)

Billions of new rows per day

This adds new wrinkles. That's upwards of a terabyte of additional disk space needed each and every day?

Is it possible to summarize the data? The goal here is to add summary info as the new data comes in, and never have to re-scan the billions of old data. This may also let you remove all the secondary indexes on the Fact table.

Normalization will help shrink the table size, hence speeding up the queries. Bartender and Customer are prime candidates for such -- perhaps a SMALLINT UNSIGNED (2 bytes; 65K values) for the former and MEDIUMINT UNSIGNED (3 bytes, 16M) for the latter. That would probably shrink by 50% the 5 columns you currently show. You may get a 2x speedup on many operations after normalizing.

Normalization is best done by 'staging' the data -- Load the data into a temporary table, normalize within it, summarize it, then copy into the main Fact table.

See http://mysql.rjweb.org/doc.php/summarytables
and http://mysql.rjweb.org/doc.php/staging_table

Before getting back to the question of optimizing the one query, we need to see the schema, the data flow, whether things can be normalized, whether summary tables can be effective, etc. I would hope to have the 'answer' for the query to be mostly digested in a summary table. Sometimes this leads to a 10x speedup.