MySQL show processlist sending data

In actual applications, when show processlist is used, most of my systems are sending data! That is, I have been wondering why so many sending data stays in my system. It is really not so slow to send query results to the client!
The document says:Sending dataThe thread is processing rows forSELECTStatement and is also sending data to the client.
I especially want to explain it in the yellow background. Although it literally seems that this status means sending data to the client, it is not exactly like this, for example, MySQL finds the query result (30 records are not cached in the memory and scattered in different places on the hard disk, in this case, we actually need to retrieve these 30 records from these 30 different places, that is, we need to find 30 records (especially scattered, the cache is not available :)), this task can be viewed as the thread is processing rows forSELECTStatement!

At this time, you may want to increase the memory so that MySQL can not only cache the index into the memory, but also cache more specific data into the memory!

From MySQL manual:

6.5.5.2. General thread states

The following list describes threadStateValues that are associated with general query processing and not more specialized activities such as replication. Duplicate of these are useful only for finding bugs in the server.

• After create

  Occurs when the thread creates a table (including internal temporary tables), at the end of the function that creates the table. this state is used even if the table cocould not be created due to some error.

• Analyzing

  The thread is calculatingMyISAMTable key distributions (for example,ANALYZE TABLE).

• Checking table

  The thread is refreshing Ming a table check operation.

• cleaning up

  The thread has processed one command and is preparing to free memory and reset certain state variables.

• closing tables

  Means that the thread is flushing the changed table data to disk and closing the used tables. this shoshould be a fast operation. if not, you shoshould verify that you do not have a full disk and that the disk is not in very heavy use.

• converting HEAP to MyISAM

  The thread is converting an internal temporary table fromMEMORYTable to an on-diskMyISAMTable.

• copy to tmp table

  The thread is processingALTER TABLEStatement. This State occurs after the table with the new structure has been created but before rows are copied into it.

• Copying to group table

  If a statement has differentORDER BYAndGROUP BYCriteria, the rows are sorted by group and copied to a temporary table.

• Copying to tmp table

  The server is copying to a temporary table in memory.

• Copying to tmp table on disk

  The server is copying to a temporary table on disk. The temporary result set was largertmp_table_sizeAnd the thread is changing the temporary table from in-memory to disk-based format to save memory.

• Creating index

  The thread is processingALTER TABLE ... ENABLE KEYSForMyISAMTable.

• Creating sort index

  The thread is processingSELECTThat is resolved using an internal temporary table.

• creating table

  The thread is creating a table. This includes des creation of temporary tables.

• Creating tmp table

  The thread is creating a temporary table in memory or on disk. If the table is created in memory but later is converted to an on-disk table, the State during that operation will beCopying to tmp table on disk.

• deleting from main table

  The server is executing the first part of a multiple-Table Delete. it is deleting only from the first table, and saving fields and offsets to be used for deleting from the other (reference) tables.

• deleting from reference tables

  The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.

• discard_or_import_tablespace

  The thread is processingALTER TABLE ... DISCARD TABLESPACEOrALTER TABLE ... IMPORT TABLESPACEStatement.

• end

  This occurs at the end but before the cleanupALTER TABLE,CREATE VIEW,DELETE,INSERT,SELECT, OrUPDATEStatements.

• Execution of init_command

  The thread is executing statements in the value ofinit_commandSystem variable.

• freeing items

  The thread has executed a command. This state is usually followedcleaning up.

• Flushing tables

  The thread is executingFLUSH TABLESAnd is waiting for all threads to close their tables.

• FULLTEXT initialization

  The server is preparing to perform a natural-language full-text search.

• init

  This occurs before the initializationALTER TABLE,DELETE,INSERT,SELECT, OrUPDATEStatements.

• Killed

  Someone has sentKILLStatement to the thread and it shocould abort next time it checks the kill flag. the flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. if the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock.

• Locked

  The query is locked by another query.

• logging slow query

  The thread is writing a statement to the slow-query log.

• login

  The initial state for a connection thread until the client has been authenticated successfully.

• Opening tables,Opening table

  The thread is trying to open a table. This is shocould be very fast procedure, unless something prevents opening. For example,ALTER TABLEOrLOCK TABLEStatement can prevent opening a table until the statement is finished.

• preparing

  This State occurs during query optimization.

• Purging old relay logs

  The thread is removing unneeded relay log files.

• query end

  This State occurs after processing a query but beforefreeing itemsState.

• Reading from net

  The server is reading a packet from the network.

• Removing duplicates

  The query was usingSELECT DISTINCTIn such a way that MySQL cocould not optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.

• removing tmp table

  The thread is removing an internal temporary table after processingSELECTStatement. This state is not used if no temporary table was created.

• rename

  The thread is renaming a table.

• rename result table

  The thread is processingALTER TABLEStatement, has created the new table, and is renaming it to replace the original table.

• Reopen tables

  The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is trying to reopen it.

• Repair by sorting

  The repair code is using a sort to create indexes.

• Repair done

  The thread has completed a multi-threaded repair forMyISAMTable.

• Repair with keycache

  The repair code is using creating keys one by one through the key cache. This is much slowerRepair by sorting.

• Rolling back

  The thread is rolling back a transaction.

• Saving state

  ForMyISAMTable operations such as repair or analysis, the thread is saving the new table state to.MYIFile Header. State Parameters des information such as number of rows,AUTO_INCREMENTCounter, and key distributions.

• Searching rows for update

  The thread is doing a first phase to find all matching rows before updating them. This has to be done ifUPDATEIs changing the index that is used to find the involved rows.

• Sending data

  The thread is processing rows forSELECTStatement and also is sending data to the client.

• setup

  The thread is beginningALTER TABLEOperation.

• Sorting for group

  The thread is doing a sort to satisfyGROUP BY.

• Sorting for order

  The thread is doing a sort to satisfyORDER BY.

• Sorting index

  The thread is Sorting index pages for more efficient access duringMyISAMTable optimization operation.

• Sorting result

  ForSELECTStatement, this is similarCreating sort index, But for non-temporary tables.

• statistics

  The server is calculating statistics to develop a query execution plan.

• System lock

  The thread is going to request or is waiting for an external system lock for the table. If you are not using multipleMysqldServers that are accessing the same tables, you can disable system locks with--skip-external-lockingOption.

• Table lock

  The next thread state afterSystem lock. The thread has acquired an external lock and is going to request an internal table lock.

• Updating

  The thread is searching for rows to update and is updating them.

• updating main table

  The server is executing the first part of a multiple-Table update. It is updating only the first table, and saving fields and offsets to be used for updating the other (reference) tables.

• updating reference tables

  The server is executing the second part of a multiple-Table update and updating the matched rows from the other tables.

• User lock

  The thread is going to request or is waiting for an advisory lock requested withGET_LOCK()Call.

• Waiting for tables,Waiting for table

  The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. however, to reopen the table, it must wait until all other threads have closed the table in question.

  This notification takes place if another thread has usedFLUSH TABLESOr one of the following statements on the table in question:FLUSH TABLES tbl_name,ALTER TABLE,RENAME TABLE,REPAIR TABLE,ANALYZE TABLE, OrOPTIMIZE TABLE.

• Waiting on cond

  A generic state in which the thread is waiting for a condition to become true. No specific State information is available.

• Writing to net

  The server is writing a packet to the network