Using PHP and MySQL as an example, let's suppose that there are 1000 connections (clients) accessing the site and using functions that open a connection with MySQL and make queries. How do PHP and MySQL process so many connections and queries at the same time?
If there are 1000 clients connected to the site making MySQL requests, then are there 1000 MySQL connections?
Q: "If there are 1000 clients connected to the site making MySQL requests, then there are 1000 MySQL connections?"
A: Only if the 1000 start at the same time a navigation on pages that use DB connection. After loading the page on the screen, the connection has already ended.
Usually connections are made at the beginning of the script that serves the page, and closed shortly thereafter. It is normal for you to have 1000 people using your site simultaneously, but in practice there are only 15 or 20 simultaneous connections. The time spent browsing is much more time spent viewing and reading pages than clicking links and loading data, and it is very unlikely that these clicks will happen at the same time very often.
I say normally, because the nightmare of website hosts is the persistent connection, which is opened in a script and kept in some session object for other pages to use. It is a non-recommended practice as it degrades the performance of all DB server applications, but it is still possible to do on many hosts.
What if I really need a lot of simultaneous connections?
There are many techniques for load distribution, when the application requires a lot of simultaneous access, but we are not talking about just 1000 simultaneous connections. You can put a few more zeros in this number.
Here are some very common ones:
Database mirroring / replication
In this case, you have several DB servers, and when you change something in the main one, this data is copied to the slaves. The advantage of this method is that you divide clients across multiple servers, effectively multiplying capacity by the number of instances serving the DB. Disadvantage: storage space is proportional to the number of servers.
Using techniques like map/reduce, you have the data spread across multiple machines, but without mirroring. Part is in one machine, part is in another, and your query is sent to several places at the same time, but only the "child's father" returns the answer. In this case, you already need a more advanced data management project. The advantage of this method is that you don't occupy space with redundant data. The downside is the greater complexity in the architecture. Example: Google.
Caches / Buffers
You can retain part of the data in the application, so as not to query the server all the time. An example of this case would be a data paging where you load the data and keep it in the application's cache, or even in the browser, so, when the person advances or returns pages, the data doesn't need to be reloaded. Example: domain listing of a Registro.br user – your domains are loaded in a single JSON, and when you go forward or back to the page, you're just changing the view, not reloading the data – Note that this is also a kind of early loading , see below.
Prediction / early loading
This case is a double-edged sword. Here you gain performance, but at the risk of loading data unnecessarily. The technique consists of loading more data than you need, so when the person moves to the next page, or scrolls a listing, the data is already there. The problem in this case is that you are often always carrying more data than you need. The advantage is that you did this by taking the time the user is analyzing past data.