如何优化此MySQL查询？数百万行

吃乐无穷

我有以下查询：
9 X# i6 y+ e* p( NSELECT
    analytics.source AS referrer,
    COUNT(analytics.id) AS frequency,
    SUM(IF(transactions.status = 'COMPLETED', 1, 0)) AS sales
FROM analytics
9 l! \2 G8 d# u2 @1 ^' H9 ]1 RLEFT JOIN transactions ON analytics.id = transactions.analytics
; Y( V0 B2 T' t" ZWHERE analytics.user_id = 52094
GROUP BY analytics.source
ORDER BY frequency DESC
, y6 A0 @* g+ b5 H3 f" p8 eLIMIT 10
  v* y: l) U/ Q6 P/ v" w分析表有6000万行，而交易表有3M行。
EXPLAIN在此查询上运行时，我得到：
+------+--------------+-----------------+--------+---------------------+-------------------+----------------------+---------------------------+----------+-----------+-------------------------------------------------+
| # id |  select_type |      table      |  type  |    possible_keys    |        key        |        key_len       |            ref            |   rows   |   Extra   |                                                 |
# A/ S% e. e! j3 c3 n! m4 X, c+------+--------------+-----------------+--------+---------------------+-------------------+----------------------+---------------------------+----------+-----------+-------------------------------------------------+
/ Y. G, N  x! B$ `3 T5 ^| '1'  |  'SIMPLE'    |  'analytics'    |  'ref' |  'analytics_user_id | analytics_source' |  'analytics_user_id' |  '5'                      |  'const' |  '337662' |  'Using where; Using temporary; Using filesort' |
! `! u% ?- U5 Y  i* e+ e| '1'  |  'SIMPLE'    |  'transactions' |  'ref' |  'tran_analytics'   |  'tran_analytics' |  '5'                 |  'dijishop2.analytics.id' |  '1'     |  NULL     |                                                 |
" V* V( m6 h$ i4 z4 ]0 I- B+------+--------------+-----------------+--------+---------------------+-------------------+----------------------+---------------------------+----------+-----------+-------------------------------------------------+
我已经不知道如何优化此查询了，因为它已经非常基础了。运行此查询大约需要70秒钟。
以下是存在的索引：
; W. e* y+ j! q# r' Q+-------------+-------------+----------------------------+---------------+------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+
9 B5 c) ]" `, H* v9 A! c  k|   # Table   |  Non_unique |          Key_name          |  Seq_in_index |    Column_name   |  Collation |  Cardinality |  Sub_part |  Packed |  Null  |  Index_type |  Comment |  Index_comment |
+-------------+-------------+----------------------------+---------------+------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+
2 ~0 ~6 F7 o- {8 e8 {2 U7 G  B| 'analytics' |  '0'        |  'PRIMARY'                 |  '1'          |  'id'            |  'A'       |  '56934235'  |  NULL     |  NULL   |  ''    |  'BTREE'    |  ''      |  ''            |
| 'analytics' |  '1'        |  'analytics_user_id'       |  '1'          |  'user_id'       |  'A'       |  '130583'    |  NULL     |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
- h) x# s8 }: M4 b5 P5 E$ @| 'analytics' |  '1'        |  'analytics_product_id'    |  '1'          |  'product_id'    |  'A'       |  '490812'    |  NULL     |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
( d/ K+ f/ w, L, o1 x| 'analytics' |  '1'        |  'analytics_affil_user_id' |  '1'          |  'affil_user_id' |  'A'       |  '55222'     |  NULL     |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
| 'analytics' |  '1'        |  'analytics_source'        |  '1'          |  'source'        |  'A'       |  '24604'     |  NULL     |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
| 'analytics' |  '1'        |  'analytics_country_name'  |  '1'          |  'country_name'  |  'A'       |  '39510'     |  NULL     |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
7 d, a6 k1 G/ B2 N. J2 `| 'analytics' |  '1'        |  'analytics_gordon'        |  '1'          |  'id'            |  'A'       |  '56934235'  |  NULL     |  NULL   |  ''    |  'BTREE'    |  ''      |  ''            |
& U( [+ ~* _8 R1 J| 'analytics' |  '1'        |  'analytics_gordon'        |  '2'          |  'user_id'       |  'A'       |  '56934235'  |  NULL     |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
( L0 v5 ]9 L) e/ c4 {7 x% E; @+ L- N| 'analytics' |  '1'        |  'analytics_gordon'        |  '3'          |  'source'        |  'A'       |  '56934235'  |  NULL     |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
+-------------+-------------+----------------------------+---------------+------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+
5 h3 d2 \* m$ Y: V
+----------------+-------------+-------------------+---------------+-------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+
|    # Table     |  Non_unique |      Key_name     |  Seq_in_index |    Column_name    |  Collation |  Cardinality |  Sub_part |  Packed |  Null  |  Index_type |  Comment |  Index_comment |
9 ]& M- w% i3 L$ I8 E; S, t+----------------+-------------+-------------------+---------------+-------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+
| 'transactions' |  '0'        |  'PRIMARY'        |  '1'          |  'id'             |  'A'       |  '2436151'   |  NULL     |  NULL   |  ''    |  'BTREE'    |  ''      |  ''            |
" b1 e9 `8 A" W' u/ @, T' T' w0 e) C| 'transactions' |  '1'        |  'tran_user_id'   |  '1'          |  'user_id'        |  'A'       |  '56654'     |  NULL     |  NULL   |  ''    |  'BTREE'    |  ''      |  ''            |
| 'transactions' |  '1'        |  'transaction_id' |  '1'          |  'transaction_id' |  'A'       |  '2436151'   |  '191'    |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
; G) d- E  H# l# B| 'transactions' |  '1'        |  'tran_analytics' |  '1'          |  'analytics'      |  'A'       |  '2436151'   |  NULL     |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
| 'transactions' |  '1'        |  'tran_status'    |  '1'          |  'status'         |  'A'       |  '22'        |  NULL     |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
| 'transactions' |  '1'        |  'gordon_trans'   |  '1'          |  'status'         |  'A'       |  '22'        |  NULL     |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
| 'transactions' |  '1'        |  'gordon_trans'   |  '2'          |  'analytics'      |  'A'       |  '2436151'   |  NULL     |  NULL   |  'YES' |  'BTREE'    |  ''      |  ''            |
* P9 I% e& K4 e) d: a. }+----------------+-------------+-------------------+---------------+-------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+
7 m* ~- z# M' {1 U根据建议，在添加任何额外索引之前简化了两个表的架构，因为这并不能改善情况。
/ C8 L1 w+ J- Z: H. W9 T2 f9 ACREATE TABLE `analytics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
8 z. b# ~7 c9 E! J, p' y6 g  `user_id` int(11) DEFAULT NULL,
  `affil_user_id` int(11) DEFAULT NULL,
9 X* a- \. n5 w" e! h  `product_id` int(11) DEFAULT NULL,
. Y' h/ J# B2 C+ S2 ^  `medium` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `source` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1 T9 K' R  j) h$ f/ D  `terms` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
7 c  P. x* `) W  ^8 T  `is_browser` tinyint(1) DEFAULT NULL,
  `is_mobile` tinyint(1) DEFAULT NULL,
1 i$ r: h9 F; x2 T" r* f  `is_robot` tinyint(1) DEFAULT NULL,
  `browser` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
* r, U5 E1 M6 q6 ?  N2 [# }7 f  `robot` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `platform` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
% w( y2 D6 x4 \) z+ h  `referrer` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `domain` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
/ J" m5 \0 h6 j7 |! o9 j  `ip` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `continent_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
! w+ g/ }2 f1 e  `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
5 u; v* f" u/ `2 b  PRIMARY KEY (`id`),
  KEY `analytics_user_id` (`user_id`),
  KEY `analytics_product_id` (`product_id`),
" D) ?: E2 J! o5 J9 R8 J1 {  KEY `analytics_affil_user_id` (`affil_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=64821325 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `transactions` (
' S. W( Y  z. X. {0 F  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
3 T/ Y4 x4 a6 X# L1 B  `user_id` int(11) NOT NULL,
  `pay_key` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sender_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
$ J$ V* r0 K8 ~2 a3 S4 e1 q  `amount` decimal(10,2) DEFAULT NULL,
' F5 j; b* b6 A' o8 ]  `currency` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
7 v' s0 G8 r. K) S2 L  `status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
5 u3 t  J3 c: `; r# J1 v/ H  `analytics` int(11) DEFAULT NULL,
7 V. @, D  [7 `" H- ^4 E0 A+ n  `ip_address` varchar(46) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `session_id` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
( q" q! ~+ v' t5 q  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
* Q* K6 q/ ~4 L0 A  `eu_vat_applied` int(1) DEFAULT '0',
. v+ U0 u3 m: @% R* b: o9 B* z  PRIMARY KEY (`id`),
1 {1 V% Y! t- z. f  KEY `tran_user_id` (`user_id`),
* Q8 p+ G- F/ u5 K! t1 T  KEY `transaction_id` (`transaction_id`(191)),
  KEY `tran_analytics` (`analytics`),
+ A* \% m! W1 v9 ]  KEY `tran_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=10019356 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
如果以上无法进一步优化。关于汇总表的任何实施建议都将非常有用。我们正在AWS上使用LAMP堆栈。上面的查询正在RDS（m1.large）上运行。
               
解决方案:
4 n6 L/ h7 i8 `( z! L9 B               
: V3 {0 Q4 f& p9 Z" b1 N5 M

                我将创建以下索引（b树索引）：
, k/ T: t$ l6 U2 p' Z) Sanalytics(user_id, source, id)
% a, b' {% J( Q$ e" Ctransactions(analytics, status)
$ e9 ?- `* ^. r2 \/ m这与戈登的建议不同。
& h  v1 B; |2 ^; M, \索引中列的顺序很重要。
( G# i! M. i* D& g您要按特定条件进行过滤analytics.user_id，因此该字段必须是索引中的第一个字段。然后，您按分组analytics.source。为避免source以此排序，索引的下一个字段应该是。您还参考analytics.id，因此最好将此字段作为索引的一部分，放在最后。MySQL是否能够只读取索引而不接触表？我不知道，但是测试起来很容易。
索引transactions必须以开头analytics，因为它会在中使用JOIN。我们还需要status。
SELECT
    analytics.source AS referrer,
- m+ w/ x* }, t) y. u    COUNT(analytics.id) AS frequency,
    SUM(IF(transactions.status = 'COMPLETED', 1, 0)) AS sales
( D- g- }7 W& s. WFROM analytics
8 u. G! `# z" c. O: k( ZLEFT JOIN transactions ON analytics.id = transactions.analytics
) f1 @0 o6 t: UWHERE analytics.user_id = 52094
GROUP BY analytics.source
ORDER BY frequency DESC
LIMIT 10