mysql 三连接

Question

我的mysql有问题

我有3个表：

Deposit
+-------------------+-------------+------+-----+
| Field             | Type        | Null | Key |
+-------------------+-------------+------+-----+
| id                | bigint(20)  | NO   | PRI |
| status            | int(2)      | NO   |     |
| depositDate       | datetime    | NO   | MUL |
| reversePayment_id | bigint(20)  | YES  | UNI |
| claim_id          | int(2)      | NO   | UNI |
| payment_id        | bigint(20)  | YES  | UNI |
+-------------------+-------------+------+-----+

付款

+--------------------------+---------------+------+-----+
| Field                    | Type          | Null | Key |
+--------------------------+---------------+------+-----+
| id                       | int(10)       | NO   | PRI |
| paymentDate              | timestamp     | NO   | MUL |
| pin                      | int(10)       | NO   | MUL |
| balanceChange            | decimal(15,2) | YES  |     |

索赔

+------------------------+--------------+------+-----+
| Field                  | Type         | Null | Key |
+------------------------+--------------+------+-----+
| id                     | int(11)      | NO   | PRI |
| fullName               | varchar(100) | NO   |     |
| depositSum             | blob         | NO   |     |
| ip                     | varchar(39)  | NO   |     |
| status                 | int(2)       | NO   |     |
+------------------------+--------------+------+-----+

我尝试选择存款（带索赔）付款或反向付款在两个日期之间，我使用3个连接执行此查询：

EXPLAIN SELECT this_.id AS id60_3_, ..., fcpayment2_.id AS id59_0_, ..., reversepay3_.id AS id59_1_, ...,  cl1_.id AS id61_2_, ...
FROM Deposit this_
INNER JOIN Payment fcpayment2_ ON this_.payment_id = fcpayment2_.id
LEFT OUTER JOIN Payment reversepay3_ ON this_.reversePayment_id = reversepay3_.id
INNER JOIN Claim cl1_ ON this_.claim_id = cl1_.id
WHERE (
(
fcpayment2_.paymentDate >= '2010-08-04 21:00:00'
AND fcpayment2_.paymentDate <= '2010-08-05 08:01:00'
)
OR (
reversepay3_.paymentDate >= '2010-08-04 21:00:00'
AND reversepay3_.paymentDate <= '2010-08-05 08:01:00'
)
)
ORDER BY this_.depositDate DESC 

结果是

+----+-------------+--------------+--------+--------------------------------------------------------------------+----------+---------+-----------------------------------------+--------+---------------------------------+
| id | select_type | table        | type   | possible_keys                                                      | key      | key_len | ref                                     | rows   | Extra                           |
+----+-------------+--------------+--------+--------------------------------------------------------------------+----------+---------+-----------------------------------------+--------+---------------------------------+
|  1 | SIMPLE      | cl1_         | ALL    | PRIMARY                                                            | NULL     | NULL    | NULL                                    | 426588 | Using temporary; Using filesort |
|  1 | SIMPLE      | this_        | eq_ref | claim_id,payment_id,FKDB5A0548511B6CDD,FKDB5A054867BA4108          | claim_id | 4       | portal.cl1_.id                          |      1 |                                 |
|  1 | SIMPLE      | fcpayment2_  | eq_ref | PRIMARY,paymentDate,date                                           | PRIMARY  | 4       | portal.this_.payment_id                 |      1 | Using where                     |
|  1 | SIMPLE      | reversepay3_ | eq_ref | PRIMARY                                                            | PRIMARY  | 4       | portal.this_.reversePayment_id          |      1 | Using where                     |
+----+-------------+--------------+--------+--------------------------------------------------------------------+----------+---------+-----------------------------------------+--------+---------------------------------+

为什么结果中的第一个表是cl1_和为什么mysql不使用key？

Answer 1

因为您使用了关键字“Explain”，并且 cl1_ 是您在查询中为表提供的别名。

我不明白你关于钥匙的问题。