MySQL预订网站：查询/ db优化 [英] MySQL booking site: query/db optimization
问题描述
我在大多数查询中的效果非常差。我已经阅读了很多stackoverflow，但仍然有一些问题，也许任何人都可以帮助或给我任何提示。
基本上，我在一个预订网站工作，其中包括以下表格： 对象
+ ---- + --------- + -------- + --------- + ------- ----- + ------------- + ---------- + ---------- + -------- ----- + ------------ + ------- + ------------- + ------ + - --------- + ---------- + ----- + ----- +
| id | user_id |状态| type_id | privacy_id | location_id | address1 | address2 | object_name | short_name |价格| currency_id |大小| no_people | min_stay | lat | lng |
+ ---- + --------- + -------- + --------- + ----------- - + ------------- + ---------- + ---------- + ------------ - + ------------ + ------- + ------------- + ------ + ------ ----- + ---------- + ----- + ----- +
OR在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'对象类型，从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 utf8_unicode_ci DEFAULT NULL COMMENT'由用户'指定的名称，
`short_name` varchar（12）COLLATE utf8_unicode_ci DEFAULT NULL COMMENT'用户'选择的短名称
`price` ，
`currency_id` tinyint（3）unsigned DEFAULT NULL，
`size` int（4）unsigned DEFAULT NULL COMMENT'size rounded and in m2'，
`no_people` tinyint 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 |状态|
+ ---- + ------------ + ----------- + ----------- + --- ------ + -------- +
/ p>
CREATE TABLE如果不存在`reservations`（
`id` int（11）NOT NULL AUTO_INCREMENT，
`by_user_id` int（11）NOT NULL COMMENT'用户的user_id'，
`object_id` int（11）NOT NULL COMMENT'对象的ID'，
`from_date` date NOT NULL COMMENT'start日期，保留日期，
`to_date`日期NOT NULL COMMENT'保留结束日期'，
`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设计用于更频繁更新UPDATE或INSERT的表。所以，目前对象使用MyISAM和保留InnoDB。这是一个好主意吗？是否有更好的选择？
3 - 我需要查询在某一时段（在from_date和end_date之间）可用的对象。我已阅读（其他）stackoverflow上的这篇文章： MySQL select rows where日期不在日期之间
但是，当我使用建议的解决方案时，查询在返回任何结果之前超时（因此很慢）：
SELECT DISTINCT o.id FROM objects o LEFT JOIN保留r ON（r.object_id = o.id）WHERE
COALESCE（'2012-04-05'不在r.from_date和r.to_date之间，TRUE）
和COALESCE（'2012-04-08'不在r.from_date和r.to_date之间，TRUE）
AND o.location_id = 201
LIMIT 20
我做错了？什么是进行这样的查询的最佳解决方案？其他网站如何做呢？是我的数据库结构不是最好的，还是只是查询？
我会有一些问题，但我真的很感谢获得任何帮助！非常感谢您提供任何提示或建议！
看来您正在寻找任何对象基于提供的从/到日期有预留冲突。做一个coalesce（）总是包括那些在预订中没有找到的是一个确定的选择，然而，作为左连接，我会尝试左连接，其中IS是找到的日期，忽略任何对象FOUND。像
SELECT DISTINCT
o.id
FROM
objects o
LEFT JOIN保留r
ON o.id = r.object_id
AND（r.from_date之间的'2012-04-05'和'2012-04-08'
或r.to_date之间'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（以及到日期），您特别寻找FOR占用此时间段的预留。如果它们被找到，则不允许，因此WHERE子句寻找r.object_id IS NULL（即：在您提供的日期范围内没有发现冲突）
从我以前的答案扩展，通过在（id，从日期）和（id，到日期）两个不同的索引，你可以通过分别加入每个索引的保留获得更好的性能， BOTH个预订集...
SELECT DISTINCT
o.id
FROM
对象o
LEFT JOIN保留r
ON o.id = r.object_id
和'2012-04-05'和'2012-04-08'之间的r.from_date
LEFT JOIN保留r2
ON o.id = r2.object_id
和'2012-04-05'和'2012-04-08'之间的r2.to_date
WHERE
o.location_id = 201
AND r.object_id IS NULL
AND r2.object_id IS NULL
LIMIT 20
I have a very bad performance in most of my queries. I've read a lot on stackoverflow, but still have some questions, maybe anyone could help or give me any hints?
Basically, i am working on a booking website, having among others the following tables:
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 |
+----+---------+--------+---------+------------+-------------+----------+----------+-------------+------------+-------+-------------+------+-----------+----------+-----+-----+
OR in 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 ;
reservations
+----+------------+-----------+-----------+---------+--------+
| id | by_user_id | object_id | from_date | to_date | status |
+----+------------+-----------+-----------+---------+--------+
OR in 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 ;
There are a few questions:
1 - I have not set any additional key (except primary) - where should I set and which key should I set?
2 - I have read about MyISAM vs InnoDB, the conclusion for me was that MyISAM is faster when it comes to read-only, whereas InnoDB is designed for tables that get UPDATED or INSERTs more frequently. So, currently objects uses MyISAM and reservations InnoDB. Is this a good idea to mix? Is there a better choice?
3 - I need to query those objects that are available in a certain period (between from_date and end_date). I have read (among others) this post on stackoverflow: MySQL select rows where date not between date
However, when I use the suggested solution the query times out before returning any results (so it is really slow):
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
What am I doing wrong? What is the best solution for doing such a query? How do other sites do it? Is my database structure not the best for this or is it only the query?
I would have some more questions, but I would be really grateful for getting any help on this! Thank you very much in advance for any hint or suggestion!
It appears you are looking for any "objects" that do NOT have a reservation conflict based on the from/to dates provided. Doing a coalesce() to always include those that are not ever found in reservations is an ok choice, however, being a left-join, I would try left joining where the IS a date found, and ignoring any objects FOUND. Something like
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
I would ensure an index on the reservations table by (object_id, from_date ) and another (object_id, to_date). By explicitly using the from_date between range, (and to date also), you are specifically looking FOR a reservation occupying this time period. If they ARE found, then don't allow, hence the WHERE clause looking for "r.object_id IS NULL" (ie: nothing is found in conflict within the date range you've provided)
Expanding from my previous answer, and by having two distinct indexes on (id, from date) and (id, to date), you MIGHT get better performance by joining on reservations for each index respectively and expecting NULL in BOTH reservation sets...
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
这篇关于MySQL预订网站：查询/ db优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
