Multithreading in MySQL?

Are MySQL operations multithreaded?

Specifically, o开发者_如何学JAVAn running a select, does the select (or join) algorithm spawn multiple threads to run together? Would being multi-threaded prevent being able to support a lot of concurrent users?

Several background threads run in a MySQL server. Also, each database connection is served by a single thread. Parallel queries (selects using multiple threads) are not implemented in MySQL.

MySQL as is can support "a lot of concurrent users". For example Facebook started successfully with MySQL.

According to MySQL 8.0 reference manual FAQ: General

Yes. MySQL is fully multithreaded, and makes use of all CPUs made available to it. Not all CPUs may be available; modern operating systems should be able to utilize all underlying CPUs, but also make it possible to restrict a process to a specific CPU or sets of CPUs. On Windows, there is currently a limit to the number of (logical) processors that mysqld can use: a single processor group, which is limited to a maximum of 64 logical processors. Use of multiple cores may be seen in these ways: A single core is usually used to service the commands issued from one session. A few background threads make limited use of extra cores; for example, to keep background I/O tasks moving. If the database is I/O-bound (indicated by CPU consumption less than capacity), adding more CPUs is futile. If the database is partitioned into an I/O-bound part and a CPU-bond part, adding CPUs may still be useful.

But not for single query. Parallel execution can be reached only by modifying queries. Good examples can be found in this article increasing slow query performance with parallel query execution:

Now we can run some queries. The first query is very simple: find all flights per year (in the US):

    select yeard, count(*) from ontime group by yeard

As we have the index on YearD, the query will use the index. The query is simple, however, it will have to scan 150M rows. The query took 54 seconds and utilized only 1 CPU core. However, this query is perfect for running in parallel. We can run 26 parallel queries, each will count its own year. I’ve used the following shell script to run the queries in the background:

    #!/bin/bash
    date
    for y in {1988..2013}
    do
      sql="select yeard, count(*) from ontime where yeard=$y"
      mysql -vvv ontime -e "$sql" &>par_sql1/$y.log & 
    done
    wait
    date

Start: 11:41:21 EST 2014

End: 11:41:26 EST 2014

So the total execution time is ~5 (10x faster) seconds.

Besides each connection has a thread, there are several management processes which has it's own thread. It's an arrangement commonly seen in DBMS. E.g. Oracle RDMS has System Monitor, DB Writer and so on.
The sport is cached-reading and lazy-writing.