mysql - 使用大量连接优化慢速 MySQL 查询

Question

我不确定如何提高此查询的性能。这需要超过 100 秒。我添加了索引并尝试了子查询，但似乎没有任何提高性能的方法。

查询

SELECT
GiftVoucher.VoucherNumber,
GiftVoucher.DateIssued,
GiftVoucher.DateRedeemed,
R.old_name as RedeemedBy,
I.old_name as IssuedBy,
RH.Name as RedeemedForHotel,
V.old_name as VoidedBy,
GiftVoucher.VoidedReplacment,
GiftVoucher.VoidedDescription
FROM GiftVoucher
LEFT JOIN StaffToWp R ON GiftVoucher.RedeemedBy=R.old_id
LEFT JOIN StaffToWp I ON GiftVoucher.IssuedBy=I.old_id
LEFT JOIN StaffToWp V ON GiftVoucher.VoidedBy=V.old_id
LEFT JOIN Hotel RH ON GiftVoucher.RedeemedForHotelID=RH.HotelID
WHERE DateIssued > "2011-12-31 23:59:59"
LIMIT 0, 20000

GiftVoucher结构

GiftVoucher

Column  Type    Null    Default Comments
GiftVoucherID   int(11) No       
ParentGiftVoucherID int(11) Yes     NULL     
Value   decimal(19,4)   No       
VoucherNumber   varchar(150)    Yes     NULL     
SendToRecipientAddress  int(11) No       
DateIssued  datetime    No       
DateRedeemed    datetime    Yes     NULL     
GiftVoucherPurchaseID   int(11) No       
RedeemedBy  int(11) Yes     NULL     
IssuedBy    int(11) Yes     NULL     
Active  int(11) No       
RedeemedForHotelID  int(11) Yes     NULL     
RedeemedTo  int(11) Yes     NULL     
Redeemed    int(1)  No  0    
RedeemedAmount  decimal(19,4)   Yes     NULL     
Voided  int(1)  No  0    
VoidedDate  datetime    Yes     NULL     
VoidedBy    int(11) Yes     NULL     
VoidedReplacment    int(11) Yes     NULL     
VoidedDescription   mediumtext  Yes     NULL     
SystemVersion   int(11) No       
Indexes

Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
PRIMARY BTREE   Yes No  GiftVoucherID   23191   A   No  
VoidedBy    BTREE   No  No  VoidedBy    2   A   Yes 
RedeemedBy  BTREE   No  No  RedeemedBy  244 A   Yes 
IssuedBy    BTREE   No  No  IssuedBy    212 A   Yes 
DateIssued  BTREE   No  No  DateIssued  23191   A   No  
RedeemedForHotelID  BTREE   No  No  RedeemedForHotelID  10  A   Yes 

StaffToWP 结构

StaffToWp

Column  Type    Null    Default Comments
id  int(11) No       
old_id  int(11) No       
old_name    varchar(255)    No       
new_id  int(11) No       
new_name    varchar(255)    No       
Indexes

Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
PRIMARY BTREE   Yes No  id  121 A   No  
old_id  BTREE   No  No  old_id  121 A   No  


Space usage:
Data    4,524   B
Index   7,168   B
Total   11,692  B
    Row Statistics:
Format  dynamic
Rows    121
Row length ø    37
Row size ø  97 B
Next autoindex  122
Creation    Sep 16, 2015 at 12:01 PM
Last update Sep 16, 2015 at 12:01 PM
Last check  Sep 16, 2015 at 12:01 PM

酒店结构

Hotel

Column  Type    Null    Default Comments
HotelID int(11) No       
Name    varchar(250)    No       
Telephone   varchar(50) No       
AccommodationUrl    varchar(250)    No  ''   
ColourClass varchar(50) Yes     NULL     
Indexes

Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
PRIMARY BTREE   Yes No  HotelID 7   A   No  
HotelID BTREE   No  No  HotelID 7   A   No

解释结果

id  select_type table   type    possible_keys   key key_len ref rows    Extra   
1   SIMPLE  GiftVoucher ALL DateIssued  NULL    NULL    NULL    22180   Using where
1   SIMPLE  R   ref old_id  old_id  4   milsomho_voucher.GiftVoucher.RedeemedBy 1   
1   SIMPLE  I   ref old_id  old_id  4   milsomho_voucher.GiftVoucher.IssuedBy   1   
1   SIMPLE  V   ref old_id  old_id  4   milsomho_voucher.GiftVoucher.VoidedBy   1   
1   SIMPLE  RH  eq_ref  PRIMARY,HotelID PRIMARY 4   milsomho_voucher.GiftVoucher.RedeemedForHotelID 1   

Answer 1

感谢您实际包括表结构和解释计划!

DateIssued > "2011-12-31 23:59:59"

大概这个过滤器会显着减少查询返回的行数。但是您会看到 DBMS 没有使用 DateIssued 上的索引:

id  select_type table   type    possible_keys   key key_len ref rows    Extra   
1   SIMPLE  GiftVoucher ALL DateIssued  NULL    NULL    NULL    22180   Using where

可能的原因是类型不匹配，迫使 MySQL 对源表中的每一行进行类型转换。

尝试:

DateIssued > 20111231235959

您还可以考虑其他方法来更具体地了解您正在提取的数据(更多过滤)并对数据进行非规范化以减少 LEFT 连接表的数量。

基数数字看起来很低 - 这是测试数据集吗？它们过时了吗？