MySQL SUM 查询速度极慢

Question

有一个名为 transactions 的表，大约有 600 万行。下面的查询计算当前用户余额。这是我启用 slow_query_log = 'ON' 后的日志:

# Time: 170406  9:51:48
# User@Host: root[root] @  [xx.xx.xx.xx]
# Thread_id: 13  Schema: main_db  QC_hit: No
# Query_time: 38.924823  Lock_time: 0.000034  Rows_sent: 1  Rows_examined: 773550
# Rows_affected: 0
SET timestamp=1491456108;
SELECT SUM(`Transaction`.`amount`) as total
    FROM `main_db`.`transactions` AS `Transaction`
    WHERE `Transaction`.`user_id` = 1008
      AND `Transaction`.`confirmed` = 1
    LIMIT 1;

如您所见，它花费了 ~38 秒!

这是 transactions 表 EXPLAIN :

这个查询有时运行得很快(大约 1 秒)，有时非常慢!

如有任何帮助，我们将不胜感激。

附言:

InnoDB 和 transactions 表有频繁的 INSERT 和 SELECT 操作。

我尝试使用 SQL_NO_CACHE 运行查询，但它仍然时快时慢。

事务 表架构:

CREATE TABLE `transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `ref_id` varchar(40) COLLATE utf8_persian_ci NOT NULL,
  `payment_id` tinyint(3) unsigned NOT NULL,
  `amount` decimal(10,1) NOT NULL,
  `created` datetime NOT NULL,
  `private_note` varchar(6000) COLLATE utf8_persian_ci NOT NULL,
  `public_note` varchar(200) COLLATE utf8_persian_ci NOT NULL,
  `confirmed` tinyint(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13133663 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci

MySQL 在具有 12GB RAM 和 9 个逻辑 CPU 内核的 VPS 上运行。

这是 my.cnf 的一部分:

# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine  = InnoDB
# you can't just change log file size, requires special procedure
innodb_buffer_pool_size = 9G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64


# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
#
# * Fine Tuning
#
max_connections         = 500
connect_timeout         = 5
wait_timeout            = 600
max_allowed_packet      = 16M
thread_cache_size       = 128
sort_buffer_size        = 4M
bulk_insert_buffer_size = 16M
tmp_table_size          = 32M
max_heap_table_size     = 32M

Answer 1

(是的，我正在添加另一个答案。理由:它以不同的方式解决了根本问题。)

潜在的问题似乎是有一个不断增长的“交易”表，从中导出各种统计数据，例如 SUM(amount)。随着表的增长，这种性能只会越来越差。

此答案的基础是通过两种方式查看数据:“历史”和“当前”。 Transactions 是历史。一个新表将是每个用户的 Current 总数。但我看到有多种方法可以做到这一点。每个都涉及某种形式的小计，以避免添加 773K 行来获得答案。

• 传统的银行业务方式...每天晚上统计当天的交易并将它们添加到当前。
• 实体化 View 方式...每次向Transactions 添加一行时，递增Current。
• 混合:将每日小计保存在“汇总表”中。对这些小计求和以获得昨晚的 SUM。

更多讨论在我的博客 Summary Tables .

请注意，银行或混合方式的最新余额有点棘手:

1. 获取昨晚的金额
2. 添加当天发生的任何交易。

与为用户扫描所有 773K 行相比，任何一种方法都很多，但代码会更复杂。