MySQL の GROUP BY のパフォーマンスと大規模なグループ化

EXPLAIN SELECT rating, COUNT(*) FROM film GROUP BY rating\G

         type: ALL
possible_keys: NULL
          key: NULL
         rows: 1000
        Extra: Using temporary

SELECT @@internal_tmp_mem_storage_engine AS engine,
       @@temptable_max_ram, @@temptable_max_mmap, @@tmp_table_size;

SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';

EXPLAIN SELECT customer_id, COUNT(*) FROM payment GROUP BY customer_id\G

         type: index
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
         rows: 16500
        Extra: Using index

EXPLAIN FORMAT=TREE SELECT customer_id, COUNT(*) FROM payment WHERE customer_id <= 100 GROUP BY customer_id\G

-> Group aggregate: count(0)  (cost=814 rows=599)
    -> Filter: (payment.customer_id <= 100)  (cost=543 rows=2710)
        -> Covering index range scan on payment using idx_fk_customer_id over (customer_id <= 100)  (cost=543 rows=2710)

EXPLAIN FORMAT=TREE SELECT customer_id, COUNT(*) FROM payment GROUP BY customer_id HAVING customer_id <= 100\G

-> Filter: (payment.customer_id <= 100)  (cost=3324 rows=599)
    -> Group aggregate: count(0)  (cost=3324 rows=599)
        -> Covering index scan on payment using idx_fk_customer_id  (cost=1674 rows=16500)

SELECT rating, ANY_VALUE(title) AS a_title, COUNT(*) AS films
FROM film
GROUP BY rating
ORDER BY rating;

SELECT COALESCE(month, 'ALL') AS month, payments
FROM (SELECT DATE_FORMAT(payment_date, '%Y-%m') AS month, COUNT(*) AS payments
      FROM payment GROUP BY DATE_FORMAT(payment_date, '%Y-%m')) AS m
ORDER BY month;

SELECT c.last_name, p.payments, r.rentals
FROM customer AS c
JOIN (SELECT customer_id, COUNT(*) AS payments FROM payment GROUP BY customer_id) AS p
  ON p.customer_id = c.customer_id
JOIN (SELECT customer_id, COUNT(*) AS rentals FROM rental GROUP BY customer_id) AS r
  ON r.customer_id = c.customer_id
ORDER BY c.last_name
LIMIT 5;

SELECT customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM payment;