MySQL预订网站：查询/数据库优化

Question

MySQL预订网站：查询/数据库优化

4

我在大多数查询中的表现非常糟糕。我在stackoverflow上阅读了很多，但仍有一些问题，也许有人可以帮助或给我一些提示？

基本上，我正在开发一个预订网站，其中包括以下表：

objects

+----+---------+--------+---------+------------+-------------+----------+----------+-------------+------------+-------+-------------+------+-----------+----------+-----+-----+
| id | user_id | status | type_id | privacy_id | location_id | address1 | address2 | object_name | short_name | price | currency_id | size | no_people | min_stay | lat | lng |
+----+---------+--------+---------+------------+-------------+----------+----------+-------------+------------+-------+-------------+------+-----------+----------+-----+-----+

MySQL中的OR：

CREATE TABLE IF NOT EXISTS `objects` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'object_id',
  `user_id` int(11) unsigned DEFAULT NULL,
  `status` tinyint(2) unsigned NOT NULL,
  `type_id` tinyint(3) unsigned DEFAULT NULL COMMENT 'type of object, from object_type id',
  `privacy_id` tinyint(11) unsigned NOT NULL COMMENT 'id from privacy',
  `location_id` int(11) unsigned DEFAULT NULL,
  `address1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `object_name` varchar(35) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'given name by user',
  `short_name` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'short name, selected by user',
  `price` int(6) unsigned DEFAULT NULL,
  `currency_id` tinyint(3) unsigned DEFAULT NULL,
  `size` int(4) unsigned DEFAULT NULL COMMENT 'size rounded and in m2',
  `no_people` tinyint(3) unsigned DEFAULT NULL COMMENT 'number of people',
  `min_stay` tinyint(2) unsigned DEFAULT NULL COMMENT '0=no min stay;else # nights',
  `lat` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lng` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1451046 ;

预订

+----+------------+-----------+-----------+---------+--------+
| id | by_user_id | object_id | from_date | to_date | status |
+----+------------+-----------+-----------+---------+--------+

MySQL中的OR：

CREATE TABLE IF NOT EXISTS `reservations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `by_user_id` int(11) NOT NULL COMMENT 'user_id of guest',
  `object_id` int(11) NOT NULL COMMENT 'id of object',
  `from_date` date NOT NULL COMMENT 'start date of reservation',
  `to_date` date NOT NULL COMMENT 'end date of reservation',
  `status` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=890729 ;

有几个问题：

1 - 除了主键，我没有设置任何其他关键字 - 我应该在哪里设置，应该设置哪个关键字？

2 - 我已经阅读了有关MyISAM与InnoDB的文章，我的结论是，在只读方面，MyISAM更快，而InnoDB则设计用于更频繁更新或插入数据的表。因此，当前对象使用MyISAM，预订使用InnoDB。混合使用这两种引擎是否明智？是否有更好的选择？

3 - 我需要查询在某个时间段内可用的对象（从from_date到end_date）。我已经阅读了（包括其他内容）stackoverflow上的这篇文章：MySQL select rows where date not between date

然而，当我使用建议的解决方案时，查询超时并且在返回任何结果之前非常缓慢：

SELECT DISTINCT o.id FROM objects o LEFT JOIN reservations r ON(r.object_id=o.id) WHERE

COALESCE('2012-04-05' NOT BETWEEN r.from_date AND r.to_date, TRUE)
AND COALESCE('2012-04-08' NOT BETWEEN r.from_date AND r.to_date, TRUE)
AND o.location_id=201

LIMIT 20

我错在哪里？如何最好地执行此查询？其他网站是如何处理的？我的数据库结构不适合还是只有查询有问题？我还有一些问题，但如果能得到任何帮助，我将非常感激！非常感谢您提前给出的任何提示或建议！

- Chris

3个回答

2

我不会混合使用InnoDB和MyISAM表，但我会将所有表都定义为InnoDB（以支持外键）。通常，所有带有_id后缀的列都应该是外键，引用适当的表（例如object_id => objects）。

您不必在外键上定义索引，因为它会自动定义（自MySQL 4.1.2以来），但是您可以在reservations.from_date和reservations.to_date列上定义附加索引，以便进行更快速的比较。

- jpesout

嘿，非常感谢你！ :) 你知道如何解决查询问题（如果日期在...之间），或者你认为你的优化建议能解决这个问题吗？ - Chris

不客气。我使用常规比较，但应该与您的代码具有相同的效果。索引肯定会对您有所帮助。 - jpesout

顺便提一下，要小心隐式的字符串转日期转换，因为在不同的MySQL版本上可能会有不同的运行方式。我在MySQL升级后遇到了很多问题，所以习惯性地将其明确转换为日期（比如DATE('2012-04-08')）。 - jpesout

哦，谢谢！使用DATE（'2012-04-08'）的提示肯定是一个非常棒的提示！我已经将表从MyISAM转换为InnoDB以设置索引，我会告诉你它的效果如何 :) - Chris

是的，外键的主要关注点是保持引用完整性。有一些使用它们的好理由，也有一些不使用它们的理由，我也不是在所有地方都使用它们。索引是为了优化，我的观点是如果您定义了FK，则不必在此列上定义索引，因为它会自动添加。 - jpesout

显示剩余3条评论

1

我知道这已经是一年前的事了，但如果你尝试上面那个解决方案，它的逻辑并不完整。它会忽略在查询开始之前开始并在查询结束后结束的预订。此外，between 无法处理在同一时间开始和结束的预订。

以下方法对我来说效果更好：

SELECT venues.id
FROM venues LEFT JOIN reservations r
       ON venues.id = r.venue_id && (r.date_end >':start' and  r.date_start <':end')
WHERE r.venue_id IS NULL
ORDER BY venues.id

- zoneblue

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- DRapp · Accepted Answer

看起来您正在寻找在给定的从/到日期中没有预订冲突的任何“对象”。使用coalesce()函数始终包括那些永远不会出现在预订中的对象是一个可以接受的选择，但由于它是一个左连接，我建议尝试左连接查找到日期的对象，并忽略任何已经被找到的对象。类似如下：

SELECT DISTINCT 
      o.id 
   FROM 
      objects o 
         LEFT JOIN reservations r 
            ON o.id = r.object_id
           AND (  r.from_date between '2012-04-05' and '2012-04-08'
               OR r.to_date between '2012-04-05' and '2012-04-08' )
   WHERE
          o.location_id = 201
      AND r.object_id IS NULL
  LIMIT 20

我会确保在预定表上建立索引(object_id, from_date)和(object_id, to_date)。通过明确使用from_date的范围，（以及to_date），您正在专门寻找占据此时间段的预订。如果它们被发现，那么不允许，因此WHERE子句寻找“r.object_id IS NULL”（即：在您提供的日期范围内没有冲突物）。

扩展我的前一个答案，并且通过在(id, from date)和(id, to date)上分别拥有两个独特的索引，通过分别加入每个索引上的预订并期望在BOTH预订集中得到NULL，您MIGHT可以获得更好的性能。

SELECT DISTINCT 
      o.id 
   FROM 
      objects o 
         LEFT JOIN reservations r 
            ON o.id = r.object_id
           AND r.from_date between '2012-04-05' and '2012-04-08'
         LEFT JOIN reservations r2 
            ON o.id = r2.object_id
           AND r2.to_date between '2012-04-05' and '2012-04-08'
   WHERE
          o.location_id = 201
      AND r.object_id IS NULL
      AND r2.object_id IS NULL
  LIMIT 20