Thalasar Ventures

Explain to understand the SQL implementation of the state.
Explain select * from wp_posts \ G; Use the show status like “Handler_read%”; to understand the effect of the index.

Handler_read_key value that the index of high effective, Handler_read_rnd_next the index value at the high inefficiency.

With the show processlist view the current operating state.
Mysql> show processlist; +—–+————-+——————–+ | Id | User | Host | db | Command | Time | State | Info
+—–+————-+——————–+ | 207 | root | 192.168.0.20:51718 | mytest | Sleep | 5 | | NULL
| 208 | root | 192.168.0.20:51719 | mytest | Sleep | 5 | | NULL
| 220 | root | 192.168.0.20:51731 | mytest | Query | 84 | Locked |
Select bookname, culture, value, type from book where id = 001
First briefly about the meaning and use of various columns, first column, id, do not say it, a logo, you should kill useful when a statement. user column, indicating a single before the user, if not root, this command will only show you the sql statement within the jurisdiction. host out to show from which this statement is on which port ip issued. Ha ha, can be used to track the problem statement users. db column, indicating a process that is connected to which databases. command column shows the current implementation of the command link, the general is dormant (sleep), queries (query), connect (connect). time out, the duration of this state, in seconds. state column shows the sql statement using the current connection status, it is important to set out, there will be follow-up of all the state’s description, please note, state is only one statement execution in the state, a sql statement, have queries, for example, may need to go through copyingto tmp table, Sorting result, Sendingdata other states in order to complete, info out to show the sql statement, because the length is limited, so long sql statement on the show was incomplete, but an important basis for judging the problem statement.

This command is the most crucial state in the column, mysql out of the state mainly in the following categories:

Checking table Are checking the data table (this is automatic).
Closing tables Is to refresh the table data changes to Disk In the same time has run out of the table is closed. This is a quick operation, if not the case, you should confirm whether the disk space is full, or whether it is in the burden of the disk.

Connect Out Copied from Server Connecting the main Server .
Copying to tmp table on disk
As temporary result set is greater than tmp_table_size, is the temporary table from Memory Storage to disk storage in order to save Memory .

Creating tmp table Are creating a temporary table to hold some query results.
Deleting from main table Server is running multi-table delete the first part, just delete the first table.

Deleting from reference tables Server is running multi-table delete the second part of the records of other tables are being removed.

Flushing tables Running FLUSH TABLES, waiting for other threads closing table.
Killed Send a request to kill a thread, then the thread will check the kill flag, and it will give up the request for the next kill. MySQL will check each of the main loop kill flag, but in some cases the thread may be too short to die. Process if the thread was locked the other thread, then kill the lock release request will take effect immediately.

Locked Locked by another query. Sending data SELECT query records are being processed, while the results are sent to the client.

Sorting for group Is being done for the GROUP BY sort.
Sorting for order Is being done for the ORDER BY sorting.
Opening tables This process should be fast, unless the interference by other factors. For example, in the Executive ALTERTABLE or LOCK TABLE statement might finish before the other thread data table can not be opened. Are trying to open a table.

Removing duplicates Is implementing a SELECT DISTINCT query mode, but MySQL can not optimize away the first stage of those duplicate records. Therefore, MySQL needs to remove duplicate records again, and then send the results to the client.

Reopen table Get a lock on a table, but must modify the following table structure to get the lock. Has released the lock, close the data table, the data are trying to reopen the table.

Repair by sorting Repair orders are sorted to create the index.
Repair with keycache Repair instruction cache index is used to create a new index one by one. It will be slower than Repair bysorting.

Searching rows for update Are about to find out the records qualified to prepare updates. It must be related to UPDATE to modify the record done before.

Sleeping Is waiting for the client sends a new request.
System lock Are waiting to get a external system lock. If the current does not run multiple mysqld servers and asked for the same table, it can increase the – skip-external-locking parameters to prohibit external system lock.

Upgrading lock INSERT DELAYED is trying to get a lock table to insert new records.
Updating
Are searching for matching records and

More MYSQL Articles