MySQL存储引擎决策
我是一名来自荷兰的网页设计师。我对从头开始构建 Web 应用程序有点陌生，我一直使用 CMS 并自定义它们。
我正在用 php/mysql 构建一个拍卖网站，现已完成。最近我读了一些关于存储引擎的文章。
我有几个问题困扰了我一段时间：
哪种存储引擎最适合我的网站，我是否使用myIsam，innodb等。
我正在建立的网站是一个拍卖网站，它必须处理每秒进行多次读取和写入！它必须检查拍卖是否开放、关闭、暂停、当前价格等。
我知道并非数据库中的所有表都会被访问，只有 3 个。它们目前都是 myIsam。这好吗？我应该全部改变吗？一个也不改？我真的不知道该怎么办。 如果我改变它们，这会极大地影响我的网站吗？最后，这些改变是否会容易实施，还是很难实施？
我的另一个问题是关于缓存系统的。
我也读过一些有关它们的内容，但我不知道它们是否适用于我拥有的此类网站。我从数据库中获取的数据变化很大。我可以方便地缓存它们吗？ 例如，可以缓存拍卖，因为并非所有项目都会改变。但拍卖不会永远进行，几个小时后就会消失。
我不明白缓存系统如何应用于此，或者我是否以错误的方式看待这一点。还有，如果这个系统适用于我，这个改变会不会花费很大的努力，这个努力会不会带来巨大的改变。
这些都是我的问题，我想它们真的很普遍，我希望有人能帮助我解决这些问题，我真的很感激。
问候
I am a web designer from the Netherlands. I am sort of new to building web apps from scratch, i have always used CMS, and customized them.
I am building a Auction website in php/mysql, which is finished. Recently i read a few articles about storage engines.
I have a few question that have been bothering me for a while now:
What sort of storage engine is best suited for my website, do i use myIsam, innodb, etc.
The website i am building is an auction website, it has to deal with several read and writes EVERY SECOND!! It has to check whether auctions are open, closed, paused, current price, etc.
I know not all tables in my database, will be accessed, these are only 3. They are currently all myIsam. Is this good? should i change them all? Change none? I really don't know what to do.
Is i change them, will this impact my website drastically. And finnaly will these changes apply easily, or are they hard to do.
Another question i have is about Caching systems.
I read a few about them too but i don't know if they apply to the sort of website i have. The data i get out of the database changes a lot. Is it handy for me to cache them?
For example an auction can be cached, because not all items change. But the auction wont be there forever, after a few hours its gone.
I can't see how a cache system could apply to that, or am i seeing this the wrong way. And again, if this system applies to me, will this change take a lot of effort, and will this effort make a huge amount of change.
These are all the questions i have, they are really general i guess, i hope some people can help me with them, i would really appreciate that.
Regards
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一般来说，对于任何任务，默认使用 InnoDB，因为它具有 ACID 合规性和行级锁定。考虑仅在 (a) 全文搜索和 (b) InnoDB 下的性能不够好的特定情况下才使用 MyISAM（这种情况相当罕见，但某些类型的数据可以从 MyISAM 的配置文件中受益）。
像拍卖这样的功能确实需要背后有一个一致的交易数据库，因为你谈论的是人们的钱。 MyISAM 无法提供这一点。您的站点需要对所有非原子交互使用事务，因此这是不可能的。当两个人同时出价时，他们就赢得了同一场拍卖。
这并不是一个大数目。然而，由于 MyISAM 不幸的表级锁定，InnoDB 在对大表的偏写访问中通常也表现得更好。
不要急于这么做。一些网络作者似乎认为任何网站都需要内存缓存才能获得良好的性能，但事实并非如此。您应用的任何缓存解决方案都会增加复杂性和潜在的故障点，以处理更新时间和现已过时的信息。
因此，除非确实需要，否则不要诉诸缓存。如果您的常见前端页面习惯性地对数据集进行复杂、缓慢的查询，那么是的，为此拥有某种缓存，无论是在数据库本身还是在外部解决方案（如 memcache）中，都可以提高速度。但实际上，与增加缓存的复杂性和不一致性相比，您可能最好更改架构并添加索引以使查询更便宜。
在一个每秒只执行“几次”操作的网站上，您不会需要它。
In general for any task default to InnoDB for its ACID-compliance and row-level locking. Consider resorting to MyISAM only for (a) fulltext search and (b) specific cases where performance under InnoDB isn't good enough (it's fairly rare that this is significant but some types of data can benefit from MyISAM's profile).
A function like auctions really really needs a consistent transactional database behind it because you're talking about people's money. MyISAM can't offer that. Your sites need to be using transactions for all non-atomic interactions, so that it's not possible for eg. two people to win the same auction when they bid at exactly the same time.
That's not a huge amount. However, InnoDB also typically performs better in write-biased access to a large table due to MyISAM's unfortunate table-level locking.
Don't rush into it. Some web authors seem to assume that memcache is going to be needed for any site to have decent performance but that really isn't the case. Any caching solution you apply will add complexity and potential failure points to deal with update times and now-outdated information.
So don't resort to caching until you really need to. If your common front-end pages habitually do complex, slow queries over the dataset then yes, having some sort of cache for that, whether that's in the database itself or an external solution like memcache, can improve speeds. But probably you'd actually be better off changing the schema and adding indexes to make that query cheaper than adding the complexity and inconsistency of a cache.
And on a site that's only doing “several” operations a second, you ain't gonna need it.
InnoDB
在像您所描述的场景中，我会毫不犹豫地选择InnoDB。看一下您可能需要的这些：
缓存
您有多种方法来进行缓存。但是假设您的首页每秒点击 10 次，并且每五分钟更改一次。将首页存储在纯 html 文件中并每五分钟更新一次该页面是非常有意义的。
InnoDB
On a scenario like the one you are describing I would go with InnoDB without hesitating. Take a look at these that you probably are going to need:
Caching
You've got several ways to do cache. But say your front-page gets 10 hits a second and changes every five minutes. Makes perfect sense to store the front-page in a pure html file and have a cron-job update that page every five minutes.