mysql> SHOW VARIABLES LIKE '%threads';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_purge_threads | 1 |
| innodb_read_io_threads | 4 |
| innodb_write_io_threads | 4 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| myisam_repair_threads | 4 |
+----------------------------+-------+
6 rows in set (0.00 sec)
What I don't know is whether these "threads" are 1-1 with Linux "processes" that you see in "top" or "ps".
> if so,how many extra threads can work as worker threads?
I see it as the connection thread leaving messages under rocks, then "io_threads" doing the work -- either now or later. The connection thread waits for some mutex before continuing. Those "messages" are often "queued"; multiple connections may be queuing up similar requests. Or they could even be asking for the same block.
Some things you may not see in your sequence diagrams... Here is (roughly) the steps for processing one SQL:
1. Receive the request (over the socket or 3306)
2. Parse the request
3. Decide if the Query cache is involved -- (if so, then there are multiple changes to the flow)
4. Figure out how to run the query (this is the "optimizer"). It _may_ involve probing indexes (which may involve asking the read_io_thread to read some index blocks)
5. Chatter with the shared buffer_pool
6. Construct the resultset (read/write the buffer_pool; possibly request more blocks)
7. If a write was involved, deal with redo logging and undo logging.
8. Send the resultset (etc) back to the client (network traffic). Even writes have a meta data to send back to the client.
Virtually every step involves multiple mutexes.
Independently,
* The query cache (if turned on!) sometimes needs pruning
* "Dirty" blocks in the buffer_pool _eventually_ get flushed to disk (write_io_threads). I _think_ the connection thread is oblivious to this.
Obviously, I don't know how to get a "sequence diagram". What I am trying to do is tell you what to look for if you ever get such. And I am trying to warn you of some puzzling things that might show up.
Your _first_ SELECT will have a lot more in the diagram than the second, especially if the SQL is identical.