sql - MySQL 查询太复杂了

Question

我有一个查询，但我遇到了问题 -

这是“最简洁”的写法吗？我是说这可以写得更好，我听说您可以使用更多查询“遍历记录集”——这是一件好事吗？如果可以，有人可以指出我正确的方向吗？此查询必须占用大量内存，因为它分布在 8 个表中，包括一个包含 3 个表的子查询。

注意 - 我正在使用具有只读访问权限的从属数据库，因此无法更改结构/创建表等!

非常感谢您的帮助。非常感谢。

Select travel_booking.reference As REF,
  travel_group.name As `Group Name`,
  site_user.firstname As `First Name`,
  site_user.lastname As `Last Name`,
  site_user.gender,
  travel_package.start_date As `Start Date`,
  travel_booking_option.name As `Option Name`,
  travel_booking_option.text_box_text As `Free Text`,
  travel_booking_option.select_box_option As `Select Text`,
  travel_booking_option.option_price As `Option Price`,
  content.name As `Option Type`
  From travel_booking Inner Join
  travel_booking_option On travel_booking_option.travel_bookingid =
  travel_booking.travel_bookingid Inner Join
  travel_group On travel_group.travel_groupid = travel_booking.travel_groupid
Inner Join
  site_user On site_user.site_userid = travel_booking.site_userid Inner Join
  travel_option_group On travel_option_group.travel_option_groupid =
  travel_booking_option.travel_option_groupid Inner Join
  content On travel_option_group.travel_option_type_content_realid =
  content.content_realid Inner Join
  travel_package On travel_package.content_realid =
  travel_booking.travel_packageid

 Where travel_booking_option.fee = 0 And travel_booking_option.refund = 0 And
  travel_booking_option.cancel = 0 And travel_booking.cancel = 0 And
  travel_package.live = 1 And content.live = 1

Group By travel_booking.reference,
  travel_group.name,
  site_user.firstname,
  site_user.lastname,
  site_user.gender,
  travel_package.start_date,
  travel_booking_option.name,
  travel_booking_option.text_box_text,
  travel_booking_option.select_box_option,
  travel_booking_option.option_price,
  content.name

Answer 1

您必须检查查询的执行计划以查看它是否低效。只要在执行时使用了正确的索引，就应该没问题。

您询问“使用更多查询‘遍历记录集’”。这是一种 RBAR(逐行痛苦)方法，绝对不推荐。

我会重新格式化查询以使其更具可读性，如下所示:

Select 
    travel_booking.reference As REF,
    travel_group.name As `Group Name`,
    site_user.firstname As `First Name`,
    site_user.lastname As `Last Name`,
    site_user.gender,
    travel_package.start_date As `Start Date`,
    travel_booking_option.name As `Option Name`,
    travel_booking_option.text_box_text As `Free Text`,
    travel_booking_option.select_box_option As `Select Text`,
    travel_booking_option.option_price As `Option Price`,
    content.name As `Option Type`

From travel_booking 
    Inner Join travel_booking_option 
        On travel_booking_option.travel_bookingid = travel_booking.travel_bookingid 
    Inner Join travel_group 
        On travel_group.travel_groupid = travel_booking.travel_groupid
    Inner Join site_user 
        On site_user.site_userid = travel_booking.site_userid 
    Inner Join travel_option_group 
        On travel_option_group.travel_option_groupid = travel_booking_option.travel_option_groupid 
    Inner Join content 
        On travel_option_group.travel_option_type_content_realid = content.content_realid 
    Inner Join travel_package 
        On travel_package.content_realid = travel_booking.travel_packageid

Where 1=1
    And travel_booking_option.fee = 0 
    And travel_booking_option.refund = 0 
    And travel_booking_option.cancel = 0 
    And travel_booking.cancel = 0 
    And travel_package.live = 1 
    And content.live = 1

Group By 
    travel_booking.reference,
    travel_group.name,
    site_user.firstname,
    site_user.lastname,
    site_user.gender,
    travel_package.start_date,
    travel_booking_option.name,
    travel_booking_option.text_box_text,
    travel_booking_option.select_box_option,
    travel_booking_option.option_price,
    content.name

我还会为表名使用别名。