SQLite 的可扩展性如何? [英] How Scalable is SQLite?

问题描述

我最近阅读了这个关于 SQLite vs MySQL 的问题，答案指出 SQLite 没有然而，官方网站sort-of 证实了这一点.

I recently read this Question about SQLite vs MySQL and the answer pointed out that SQLite doesn't scale well and the official website sort-of confirms this, however.

SQLite 的可扩展性如何?它的最高限制是什么?

How scalable is SQLite and what are its upper most limits?

推荐答案

昨天我发布了一个小型网站^* 来跟踪您的代表，该网站为所有访问者使用共享的 SQLite 数据库.不幸的是，即使它给我的主机带来了适度的负载，它也运行得很慢.这是因为每次有人查看页面时整个数据库都被锁定，因为它包含更新/插入.我很快切换到 MySQL，虽然我没有太多时间来测试它，但它似乎比 SQLite 更具可扩展性.我只记得页面加载缓慢，并且在尝试从 SQLite 中的 shell 执行查询时偶尔会出现数据库锁定错误.也就是说，我正在从 SQLite 运行另一个站点就好了.不同之处在于该站点是静态的(即我是唯一可以更改数据库的站点)，因此它适用于并发读取.故事寓意:仅将 SQLite 用于很少更新数据库的网站(比加载的每个页面都少).

Yesterday I released a small site^* to track your rep that used a shared SQLite database for all visitors. Unfortunately, even with the modest load that it put on my host it ran quite slowly. This is because the entire database was locked every time someone viewed the page because it contained updates/inserts. I soon switched to MySQL and while I haven't had much time to test it out, it seems much more scaleable than SQLite. I just remember slow page loads and occasionally getting a database locked error when trying to execute queries from the shell in sqlite. That said, I am running another site from SQLite just fine. The difference is that the site is static (i.e. I'm the only one that can change the database) and so it works just fine for concurrent reads. Moral of the story: only use SQLite for websites where updates to the database happen rarely (less often than every page loaded).

edit:我刚刚意识到我对 SQLite 可能不公平 - 当我从网页提供 SQLite 数据库时，我没有索引它.这部分导致了我正在经历的放缓.然而，对数据库锁定的观察仍然存在 - 如果您有特别繁重的更新，SQLite 的性能将无法与 MySQL 或 Postgres 匹敌.

edit: I just realized that I may not have been fair to SQLite - I didn't index any columns in the SQLite database when I was serving it from a web page. This partially caused the slowdown I was experiencing. However, the observation of database-locking stands - if you have particularly onerous updates, SQLite performance won't match MySQL or Postgres.

另一个自从我在大约 3 个月前发布这篇文章以来，我有机会仔细检查 SQLite 的可扩展性，并且通过一些技巧，它可以具有相当大的可扩展性.正如我在第一次编辑中提到的，数据库索引大大减少了查询时间，但这更多是关于数据库的一般观察，而不是关于 SQLite.但是，您可以使用另一个技巧来加速 SQLite:transactions.每当您必须进行多个数据库写入时，请将它们放入一个事务中.不是每次发出写入查询时都写入(并锁定)文件，而是在事务完成时写入仅发生一次.

another edit: Since I posted this almost 3 months ago I've had the opportunity to closely examine the scalability of SQLite, and with a few tricks it can be quite scalable. As I mentioned in my first edit, database indexes dramatically reduce query time, but this is more of a general observation about databases than it is about SQLite. However, there is another trick you can use to speed up SQLite: transactions. Whenever you have to do multiple database writes, put them inside a transaction. Instead of writing to (and locking) the file each and every time a write query is issued, the write will only happen once when the transaction completes.

我在第一段中提到的网站已经切换回 SQLite，并且在我调整了几个地方的代码后它运行得非常流畅.

The site that I mention I released in the first paragraph has been switched back to SQLite, and it's running quite smoothly once I tuned my code in a few places.

^{* 该站点不再可用}

这篇关于SQLite 的可扩展性如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！