I have taken on a project from a client which requires me to set up a backend server and API (PHP) with communication to/fro an iOS App. I can't go into too much detail about the app, but for the most part, it is socially based and therefore requires me to constantly update the database upon receipt of a request.
我从一个客户端接受了一个项目,该项目要求我建立一个后端服务器和API(PHP),与iOS应用程序进行通信。我不能详细介绍该应用程序,但在大多数情况下,它是基于社交的,因此要求我在收到请求后不断更新数据库。
Currently, the database consists of three tables (with a fourth coming soon for comments) Users, Venues and Queues. At the moment, whenever I have a request, I just retrieve the relevant data from the database, but I am starting to wonder whether I need to start implementing methods to improve the 'scalability' of the database or use (something along the lines of) memcachd or Redis to improve something or other. Is this necessary as the app is social and may potentially have a large user base? And am I using a good well-performance database for hundreds of requests per second, or should I switch to another?
目前,该数据库由三个表组成(第四个表即将发表评论)用户,场地和队列。目前,每当我有一个请求时,我只是从数据库中检索相关数据,但我开始怀疑是否需要开始实现方法来改善数据库的“可扩展性”或使用(类似于memcachd或Redis改进某些东西。这是必要的,因为该应用程序是社交的,可能有一个庞大的用户群?我是否每秒使用一个性能良好的数据库来处理数百个请求,还是应该切换到另一个?
Thanks in advanced,
先谢谢了,
Max.
P.S Feel free to pick apart my structure, put it in the post just incase anyone had any interest in taking a look!
P.S随意挑选我的结构,把它放在帖子中只是任何人都有兴趣看看!
CREATE TABLE `Queues` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userID` int(11) NOT NULL,
`Creation_Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Last_Updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`venueID` int(11) NOT NULL,
`Wait_Time` int(10) NOT NULL,
`Line_Length` int(10) NOT NULL,
`Note` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;
CREATE TABLE `Users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`First_Name` text NOT NULL,
`Last_Name` text NOT NULL,
`Username` text NOT NULL,
`Password` text NOT NULL,
`Email` text NOT NULL,
`Signup_Method` text NOT NULL,
`Signup_Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Number_of_Lines` int(11) NOT NULL,
`Number_of_Venues` int(11) NOT NULL,
`Last_Updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;
CREATE TABLE `Venues` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userID` int(11) NOT NULL,
`foursquareID` int(11) NOT NULL,
`Name` text NOT NULL,
`Latitude` text NOT NULL,
`Longitude` text NOT NULL,
`Address_Line1` text NOT NULL,
`Address_Line2` text NOT NULL,
`Post_Code` text NOT NULL,
`Country` text NOT NULL,
`Description` text NOT NULL,
`Created_At` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Last_Updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
1 个解决方案
#1
2
You can put off worrying about caching methods until the need arises. For the time being, ensure your database is properly normalized. Once the site is working smoothly and starts to gain popularity, hooking in memcached will be a relatively trivial task.
在需要之前,您可以放弃对缓存方法的担忧。目前,请确保您的数据库已正确规范化。一旦网站运行顺利并开始流行起来,在memcached中挂钩将是一项相对简单的任务。
I notice a queue could belong to a user that's different than the user that owns the queue's venue: Queues.userID and Venues.userID via Queues.venueID. If this is not what you want then you'll want to remove Queues.userID as the user info can be obtained be checking the queue's venue.
我注意到一个队列可能属于一个用户,该用户与拥有队列位置的用户不同:Queues.userID和Venues.userID通过Queues.venueID。如果这不是您想要的,那么您将要删除Queues.userID,因为可以获取用户信息来检查队列的位置。
Also, if Users.Number_of_Venues is a count of the number of Venues with a certain userID, remove it as there's no reason to store it as that information can be obtained by a quick COUNT(*). To leave it would mean lack of normalization.
此外,如果Users.Number_of_Venues是具有特定用户ID的场地数量的计数,则将其删除,因为没有理由存储它,因为可以通过快速COUNT(*)获得该信息。离开它意味着缺乏正常化。
Once your schema is set, remember to properly index. That will be a life-saver in speeding up your queries.
设置好架构后,请记住正确编制索引。这将有助于加快查询速度。
#1
2
You can put off worrying about caching methods until the need arises. For the time being, ensure your database is properly normalized. Once the site is working smoothly and starts to gain popularity, hooking in memcached will be a relatively trivial task.
在需要之前,您可以放弃对缓存方法的担忧。目前,请确保您的数据库已正确规范化。一旦网站运行顺利并开始流行起来,在memcached中挂钩将是一项相对简单的任务。
I notice a queue could belong to a user that's different than the user that owns the queue's venue: Queues.userID and Venues.userID via Queues.venueID. If this is not what you want then you'll want to remove Queues.userID as the user info can be obtained be checking the queue's venue.
我注意到一个队列可能属于一个用户,该用户与拥有队列位置的用户不同:Queues.userID和Venues.userID通过Queues.venueID。如果这不是您想要的,那么您将要删除Queues.userID,因为可以获取用户信息来检查队列的位置。
Also, if Users.Number_of_Venues is a count of the number of Venues with a certain userID, remove it as there's no reason to store it as that information can be obtained by a quick COUNT(*). To leave it would mean lack of normalization.
此外,如果Users.Number_of_Venues是具有特定用户ID的场地数量的计数,则将其删除,因为没有理由存储它,因为可以通过快速COUNT(*)获得该信息。离开它意味着缺乏正常化。
Once your schema is set, remember to properly index. That will be a life-saver in speeding up your queries.
设置好架构后,请记住正确编制索引。这将有助于加快查询速度。