MySQL预订站点:查询/数据库优化

Question

在我的大多数查询中，我的表现都非常糟糕。我读过很多关于堆叠溢出的文章，但仍然有一些问题，也许有人能帮我，或者给我一些提示？

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

对象

+----+---------+--------+---------+------------+-------------+----------+----------+-------------+------------+-------+-------------+------+-----------+----------+-----+-----+
| 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中：

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中：

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 vs InnoDB的文章，对我来说，结论是MyISAM在只读方面更快，而InnoDB是为更新或插入更频繁的表而设计的。因此，当前对象使用MyISAM和订票InnoDB。这是个好主意吗？还有更好的选择吗？

3-我需要查询在一定时期内可用的对象(在from_date和end_date之间)。我(和其他人)读过这篇关于堆栈溢出的文章：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

我做错了什么？做这样的查询最好的解决方案是什么？其他网站是如何做到的呢？我的数据库结构不是最好的，还是仅仅是查询？

我会有更多的问题，但我会非常感谢在这方面得到任何帮助！非常感谢您的任何提示或建议。

Answer 1

根据提供的from/to日期，您似乎正在寻找没有预订冲突的任何“对象”。但是，作为一个左联接，我会尝试在找到is日期的地方左加入，而忽略找到的任何对象。有点像

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 (到目前为止也是如此)，您将专门查找占用此时间段的保留。如果找到它们，则不允许，因此查找"r.object_id是空“的WHERE子句(即:在您提供的日期范围内没有发现冲突)

从我以前的答案展开，通过在(id，from )和(id，至今)上有两个不同的索引，您可以通过分别加入每个索引的保留并在两个预订集中期望NULL来获得更好的性能.

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

Answer 2

我不会将InnoDB表和MyISAM表混合，但是我会将所有表定义为InnoDB (用于预测键支持)。通常，所有带有_id后缀的列都应该是引用适当表(object_id、=>、对象等)的外键。

您不必在外键上定义索引，因为它是自动定义的(自MySQL 4.1.2以来)，但是您可以在reservations.from_date和reservations.to_date列上定义其他索引，以便更快地进行比较。

Answer 3

我知道这已经有一年的历史了，但是如果您已经尝试过上面的解决方案，那么逻辑就不完整了。它遗漏了在查询开始之前开始和查询结束后结束的保留。此外，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