当前位置：网站首页>MySQL optimization

The whole process of thinking and action is divided into observation （Show status） And action （Action） Two parts . Letter S Part of represents observation （ Will use the corresponding analysis tools ）, Letter A The representative part is action （ Actions that can be taken by correspondence analysis ）.

Analyze query statements ：EXPLAIN

First, query the log slowly , Find those slow queries sql sentence , Use explain Yes sql Statement for comprehensive parsing .

Looking for slow queries sql After the statement , Need to continue to use mysql Provided explain Order to inquire sql sentence Implementation plan of , see sql Whether the statement uses the index , Is there a full table scan . So as to select better indexes and write more optimized query statements .

Name	describe
id	Included in a large query statement SELECT Each keyword corresponds to a unique id identification
select_type	SELECT The type of query corresponding to the keyword
table	The table name involved in the query
type*	Access methods for single tables
possible_keys	Possible indexes
key	Index actually used
key_len*	The index length actually used , If it is a joint index composed of three fields , Two fields are actually used , Then the value is 2*
ref	When using index column equivalent queries , Information about the object matching the index column , Columns compared to indexes
rows	Estimate the number of records to be read
Extra*	Some extra information

1、table

explain The name of the table referenced by the output record .
In a query , Several tables are involved ,explain The result will have several records , Of course, sometimes records will be more than the number of tables , Because there may be the creation of temporary tables during the query .

No matter how complex our query statement is , How many tables does it contain , In the end, you also need to access each table in a single table , therefore MySQL Regulations EXPLAIN Each record output by the statement corresponds to the access method of a single table , Of this record table The column represents the table name of the table （ Sometimes it's not a real name , It may be abbreviated as ）.

2、id

In a complex query , One select Keyword corresponds to a unique id identification . But sometimes MySQL Will automatically rewrite sql Automatic override , Thus reducing the number of queries , There may be two select keyword , however id Just one , This is it. MySQL Automatically rewrites sql.

Summary ：
id If the same , It can be thought of as a group , The same select Inquire about , From top to bottom
In all groups ,id The bigger the value is. , The higher the priority , Execute first
concerns ：id Number, each number , Represents an independent query , One sql The fewer query times, the better

id by null May be a temporary table ：

3、select_type

For a large complex query statement , May contain more than one select keyword , Every select Keywords represent a small query , and select Small queries may form Find data from multiple tables , Each table in explain The statement plan corresponds to a record , however id Are all the same .

select_type Represents the query type of the current query , According to this, you can know the identity of this small query in the big query ( For example, sub query ).

name	describe
PRIMARY	The outermost select, In a statement with subqueries , The outermost select Of the query select_type Namely primary
SUBQUERY	First in subquery SELECT, The results don't depend on external queries . Subqueries do not depend on external tables .
DEPENDENT SUBQUERY	First in subquery SELECT, Depends on external queries . Subqueries do not depend on external tables .
SIMPLE	It means simple select, No, union Joint query and subquery

subquery:

dependent subquery:

4、type*

explain A record of the implementation plan represents MySQL Access method when executing query on a table ,type Column describes what the access method is , yes sql An important indicator of whether to optimize . such as , notice type yes ALL, That's over , It indicates that the current table is scanned in full , Efficiency is very low , At this time, it is time to optimize .

The result value is from good to bad ：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL Objectives of performance optimization ： At the very least range Level , The requirement is ref Level , It is best to consts Level .（ Alibaba development manual requirements ）

When we do a condition query , Index is recommended , Otherwise, a full table scan will be caused ,IO There's no guarantee about the cost and the performance of the program ！

5、possible_keys

When representing a query , Possible indexes , It's usually where If the fields in the following conditions are useful to the index, they will become possible_keys.

6、key

The index actually used in the query , Multiple indexes MySQL The determined optimal index will become key.

7、key_len*

The actual index length used ( namely ： Number of bytes )

Check for you ` Whether to make full use of the index `,` The higher the value, the better `, It is mainly aimed at the joint index , It has certain reference significance .

key_part1、key_part2 and key_part3 Three fields form a joint index

8、ref

When using the field where the index is located for equivalent query , The object information matching the fields of the column where the index is located . For example, give a constant again const Or a field .

9、rows*

This column indicates MySQL The number of rows that think you must check to execute the query . It is estimated that the current query needs to scan the number of rows of the table .

about InnoDB surface , This figure is an estimate , It may not always be accurate .

The smaller the value, the better .

10、filtered

General coordination rows See it together , The higher the value, the better .

11、Extra*

Extra Columns are used to illustrate some additional information , You can learn from additional information Mysql How to execute a given query statement .

see SQL Execution cost ：SHOW PROFILE

1、 see profile Whether the function is turned on ON open ,OFF It's not open

show variables like 'profiling';

2、 By setting profiling='ON’ To open show profile：

mysql > set profiling = 'ON';

3、show profiles; Check out the latest sql sentence . View all generated by the current session profiles

4、show profile for query sql Of the statement id; see sql The time spent in each process of execution .

status Field interpretation :


* Sending data ( The most important process *****)
   The thread is reading and processing a SELECT Line of statement , And send the data to the client . Because a large number of     Disk access （ Read operations ）,
   This state is often the longest in the life cycle of a given query .
   This field is SQL Really run the collection + Time of corresponding data , Instead of executing;
  
  -- The following is sorted alphabetically 
  
* After create
   This state is when the thread creates a table （ Include internal temporary tables ） when , Appears at the end of this table creation function . Even if some errors lead to table creation failure , This state will also be used .

* Analyzing
   When calculating MyISAM Table index distribution .（ For example ANALYZE TABLE when ）

* checking permissions
   This thread checks whether the server has the necessary permissions to execute the statement .

* Checking table
   The thread is performing a table check operation .

* cleaning up
   Thread processing a command , And is preparing to free memory and reset some state variables .

* closing tables
   The thread is refreshing the data in the changed table to disk and closing the used table . This should be a quick operation . If not 
   Then you should check whether the hard disk space is full or whether the hard disk IO Whether the bottleneck has been reached . 

* converting HEAP to MyISAM
   The thread converts an internal temporary table to MyISAM surface .

* copy to tmp table 
   The thread is processing a ALTER TABLE sentence . This state occurs after the new table structure has been created , But before the data is copied into .

* Copying to group table
   If a statement has different ORDER BY and GROUP BY Conditions , The data will be copied into a temporary table and sorted by group .

* Copying to tmp table
   Thread writes data to a temporary table in memory .   Creating temporary table to hold partial query results 

* Copying to tmp table on disk
   Thread is writing data to a temporary table on disk . The result set of the temporary table is too large . So the thread changes the temporary table from memory based mode to disk based mode , To save memory .
   But this process will be unusually slow ！！

* Creating index
   The thread is working on a MyISAM Table execution ALTER TABLE ... ENABLE KEYS sentence .

* Creating sort index
   Thread is using internal temporary table to process a SELECT  operation .

* creating table
   The thread is creating a table , Including creating temporary tables .

* Creating tmp table
   Thread is creating a temporary table in memory or disk .
   If this table is created in memory but then converted to disk , This state is running Copying to tmp table on disk  Keep... When .

* deleting from main table
   The thread is performing the first part of multi table deletion , Delete only from the first table . And save the column and offset to use from other （ Reference resources ） Table delete .

* deleting from reference tables
   The thread is executing the second part of multi table deletion , And delete matching rows from other tables .

* discard_or_import_tablespace
   Thread is executing ALTER TABLE ... DISCARD TABLESPACE  or  ALTER TABLE ... IMPORT TABLESPACE sentence .

* end
   This state appears at the end , But right ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT,    perhaps  UPDATE  Statement before cleaning up .

* executing
   The thread has started executing a statement .

* Execution of init_command
   Thread is executing at init_command Statements in the value of system variables .

* freeing items
*  The thread has executed the command . The query cache involved in this state can be released . This state is usually followed by cleaning up state .

* Flushing tables
   Thread is executing FLUSH TABLES  And wait for all threads to close their tables .

* FULLTEXT initialization
   The server is preparing for natural language full-text retrieval .

* init
   This state occurs during thread initialization ALTER TABLE, DELETE, INSERT, SELECT,  or  UPDATE The statement before .
   The operation of the server in this state , Include ： Refresh the full log 、Innodb journal , And some query cache cleanup operations .

* Killed
   The program sent... To the thread KILL sentence , And it should give up the next time KILL Inspection of marks .
   This mark is on every MySQL Is checked in the main cycle , But in some cases , It may need to make the thread die in a very short time .
   If this thread is locked by another thread , This KILL The operation will be executed immediately after other threads release the lock .


* logging slow query
   This thread is writing statements to the slow query log .

* NULL
   The state of no operation .

* login
   Initial state of thread connection . Until the client has successfully verified .

* manage keys
   The server enables or disables table indexing .

* Opening tables, Opening table
   The thread is trying to open a table 

* optimizing
   The server performs preliminary optimization of queries .

* preparing
  This state occurs during query optimization .

* Purging old relay logs
   The thread is removing unnecessary relay log files .

* query end
   This state appears after processing a query , But in freeing items Before status .

* Reading from net
   The server is reading packets from the network .

* Removing duplicates
   Query is in use SELECT DISTINCT, In this case MySQL Some cannot be optimized at an early stage distinct operation .
   therefore ,MySQL Need an extra phase , Delete all duplicate rows before sending the results to the client .

* removing tmp table
   Thread is removing a built-in temporary table , In the execution of a SELECT After statement .  If no temporary table is generated , Then this state is not used .

* rename
*  The thread is renaming a table .

* rename result table
   Thread is processing ALTER TABLE sentence , Create a new table , And rename it to replace the original table .

* Reopen tables
   The thread obtained the table lock , But after obtaining the table lock, I found that the underlying structure of the table has changed . The thread releases this lock , Close table , And try to reopen the table .

* Repair by sorting
   The fix code is using a category to create an index .

* Repair done
   Threads complete a multithreaded MyISAM Table repair .

* Repair with keycache
   Fix code is using index cache one by one to create indexes . This is much slower than repairing by classification .

* Rolling back
   Thread is rolling back a transaction 

* Searching rows for update
   The thread is in the first stage , Find all matching rows before updating . If update Changing the index used to find related rows , You have to .

* setup
   The thread is starting a ALTER TABLE operation .

* Sorting for group
   The thread is executing an operation by GROUP BY Specified sort .

* Sorting for order
   The thread is executing an operation by ORDER BY Specified sort .

* Sorting index
   Thread is sorting index pages , In order to MyISAM Better performance when operating tables .

* Sorting result
  For one SELECT sentence , This is similar to creating a sort index , But it's a non temporary table .

* statistics
   The server calculates statistics to plan a query . If a thread is in this state for a long time , The disk of this server may be performing other work .

* System lock
  This thread is requesting or waiting for an internal or external system table lock . If this state is caused by an external lock request , And you don't use multiple that are accessing the same table mysql The server 
 
* Waiting for table level lock
   The next thread state after the system is locked . The thread has obtained an external lock and will request an internal table lock .

* Updating
   The thread looks for the row that matches the update and updates .

* updating main table
   Thread is executing the first part of multi table update , Update only from the first table . And save the column and offset to use from other （ Reference resources ） Table update .

* updating reference tables
   The thread is executing the second part of the multi table update , And update the matching rows from other tables .

* User lock
   The thread is requesting or waiting for a GET_LOCK() Call the requested Advisory lock . about SHOW PROFILE, This state means that the thread is requesting a lock .（ Instead of waiting ）

* User sleep
   The thread called a SLEEP().

* Waiting for commit lock
   An explicit or implicit statement waits to release the read lock when submitting 

* Waiting for global read lock
   Waiting for global read lock .

* Waiting for release of readlock
   Wait for the read lock to be released .

* Waiting for tables, Waiting for table, Waiting for table flush
   The thread gets a notification , The underlying table structure has changed , It needs to reopen the table to get the new structure . However , Reopen the table , It must wait until all other threads close the problematic table .
   This notification usually occurs because another thread executes on the problem table FLUSH TABLES Or one of the following statements ：
  FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

* Waiting for lock_type lock
   Wait for all kinds of table locks .

* Waiting on cond
  A normal state , The thread is waiting for a condition to be true . No specific status information is available .

* Writing to net
   The server is writing a network packet .