SQLite 比 MySQL 快吗？

SQLite faster than MySQL?

提问by boop

I want to set up a teamspeak 3 server. I can choose between SQLite and MySQL as database. Well I usually tend to "do not use SQLite in production". But on the other hand, it's a teamspeak server. Well okay, just let me google this... I found this:

我想建立一个teamspeak 3服务器。我可以选择 SQLite 和 MySQL 作为数据库。好吧，我通常倾向于“不要在生产中使用 SQLite”。但另一方面，它是一个teamspeak服务器。好吧，让我谷歌一下……我发现了这个：

2. Speed
   SQLite3 is much faster than MySQL database. It's because file database is always faster than unix socket. When I requested edit of channel it took about 0.5-1 sec on MySQL database (127.0.0.1) and almost instantly (0.1 sec) on SQLite 3. [...]

2. 速度
   SQLite3 比 MySQL 数据库快得多。这是因为文件数据库总是比 unix socket 快。当我请求编辑频道时，在 MySQL 数据库 (127.0.0.1) 上花费了大约 0.5-1 秒，在 SQLite 3 上几乎立即（0.1 秒）。 [...]

http://forum.teamspeak.com/showthread.php/77126-SQLite-vs-MySQL-Answer-is-here

http://forum.teamspeak.com/showthread.php/77126-SQLite-vs-MySQL-Answer-is-here

I don't want to start a SQLite vs MySQL debate. I just want to ask: Is his argument even valid?I can't imagine it's true what he says. But unfortunately I'm not expert enough to answer this question myself.

我不想开始 SQLite 与 MySQL 的辩论。我只想问：他的论点还有道理吗？我无法想象他说的是真的。但不幸的是，我不够专业，无法自己回答这个问题。

Maybe TeamSpeak dev's have some major differences in their db architecture between SQLite and MySQL which explains a huge difference in speed (I can't imagine this).

也许 TeamSpeak 开发人员在 SQLite 和 MySQL 之间的数据库架构上有一些重大差异，这解释了速度上的巨大差异（我无法想象）。

回答by José Algarra

At First Access Time will Appear Faster in SQLite

在 SQLite 中首次访问时间会更快

The access time for SQLite will appear faster at first instance, but this is with a small number of users online. SQLite uses a very simplistic access algorithm, its fast but does not handle concurrency.

SQLite 的访问时间一开始看起来会更快，但这是在少数用户在线的情况下。SQLite 使用一种非常简单的访问算法，它速度快但不处理并发。

As the database starts to grow, and the amount of simultaneous access it will start to suffer. The way servers handle multiple requests is completely different and way more complex and optimized for high concurrency. For example, SQLite will lock the whole table if an update is going on, and queue the orders.

随着数据库开始增长，同时访问的数量将开始受到影响。服务器处理多个请求的方式完全不同，并且更加复杂并且针对高并发进行了优化。例如，如果更新正在进行，SQLite 将锁定整个表，并将订单排队。

RDBMS's Makes a lot of extra work that make them more Scalable

RDBMS 做了很多额外的工作，使它们更具可扩展性

MySQL for example, even with a single user will create an access QUEUE, lock tables partially instead of allowing only single user-per time executions, and other pretty complex tasks in order to make sure the database is still accessible for any other simultaneous access.

例如 MySQL，即使只有一个用户，也会创建一个访问队列，部分锁定表而不是每次只允许单个用户执行，以及其他非常复杂的任务，以确保数据库仍可用于任何其他同时访问。

This will make a single user connection slower, but pays off in the future, when 100's of users are online, and in this case, the simple "LOCK THE WHOLE TABLE AND EXECUTE A SINGLE QUERY EACH TIME" procedure of SQLite will hog the server.

这会使单个用户的连接变慢，但在未来会有回报，当 100 名用户在线时，在这种情况下，SQLite 的简单“锁定整个表并每次执行单个查询”过程将占用服务器.

SQLite is made for simplicity and Self Contained Database Applications.

SQLite 是为了简单和自包含的数据库应用程序而设计的。

If you are expecting to have 10 simultaneous access writing at the database at a time SQLite may perform well, but you won't want an 100 user application that constant writes and reads data to the database using SQLite. It wasn't designed for such scenario, and it will trash resources.

如果您希望一次对数据库进行 10 个同时访问写入，SQLite 可能表现良好，但您不希望有 100 个用户的应用程序使用 SQLite 不断地向数据库写入和读取数据。它不是为这种情况设计的，它会浪费资源。

Considering your TeamSpeak scenario you are likely to be ok with SQLite, even for some business it is OK, some websites need databases that will be read only unless when adding new content.

考虑到您的 TeamSpeak 场景，您可能对 SQLite 没问题，即使对于某些业务也可以，但某些网站需要只读的数据库，除非添加新内容。

For this kind of uses SQLite is a cheap, easy to implement, self contained, perfect solution that will get the job done.

对于这种用途，SQLite 是一种廉价、易于实现、自包含、完美的解决方案，可以完成工作。

回答by Gordon Linoff

I have not personally tested SQLite vs MySQL, but it is easy to find examples on the web that say the opposite (for instance). You do ask a question that is not quite so religious: is that argument valid?

我没有亲自测试过 SQLite 与 MySQL，但很容易在网上找到相反的例子（例如）。你确实问了一个不太虔诚的问题：这个论点有效吗？

First, the essence of the argument is somewhat specious. A Unix socket would be used to communicate to a database server. A "file database" seems to refer to the fact that communication is through a compiled-in interface. In the terminology of SQLite, it is server-less. Most databases store data in files, so the terminology "file database" is a little misleading.

首先，论证的本质有些似是而非。Unix 套接字将用于与数据库服务器通信。“文件数据库”似乎是指通信是通过编译接口进行的。在 SQLite 的术语中，它是无服务器的。大多数数据库将数据存储在文件中，因此术语“文件数据库”有点误导。

Performance of a database involves multiple factors, such as:

数据库的性能涉及多种因素，例如：

• Communication of query to the database.
• Speed of compilation (ability to store pre-compiled queries is a plus here).
• Speed of processing.
• Ability to handle complex processing.
• Compiler optimizations and execution engine algorithms.
• Communication of results back to the application.

• 与数据库的查询通信。
• 编译速度（存储预编译查询的能力在这里是一个加分项）。
• 处理速度。
• 处理复杂加工的能力。
• 编译器优化和执行引擎算法。
• 将结果传送回应用程序。

Having the interface be compiled-in affects the first and last of these. There is nothing that prevents a server-less database from excelling at the rest. However, database servers are typically millions of lines of code -- much larger than SQLite. A lot of this supports extra functionality. Some of it supports improved optimizations and better algorithms.

编译接口会影响其中的第一个和最后一个。没有什么可以阻止无服务器数据库在其他方面表现出色。然而，数据库服务器通常有数百万行代码——比 SQLite 大得多。其中很多都支持额外的功能。其中一些支持改进的优化和更好的算法。

As with most performance questions, the answer is to test the systems yourself on your data in your environment. Being server-less is not an automatic performance gain. Having a server doesn't make a database "better". They are different applications designed for different optimization points.

与大多数性能问题一样，答案是根据环境中的数据自行测试系统。无服务器并不是一种自动的性能提升。拥有服务器不会使数据库“更好”。它们是针对不同优化点设计的不同应用程序。

回答by CL.

The relevant difference is that SQLite uses a much simpler locking algorithm (a simple global database lock).

相关的区别在于 SQLite 使用了更简单的锁定算法（简单的全局数据库锁）。

Using fine-grained locking (as MySQL and most other DB servers do) is muchmore complex, and slower if there is only a single database user, but required if you want to allow more concurreny.

使用细粒度锁定（如 MySQL 和大多数其他数据库服务器所做的那样）要复杂得多，如果只有一个数据库用户，速度也会更慢，但如果您希望允许更多并发，则需要使用细粒度锁定。