MySQL optimization (2)

7.2.1EXPLAIN syntax (obtain SELECT-related information) The EXPLAINtbl_name or: EXPLAINSELECTselect_optionsEXPLAIN statement can be used as a synonym for DESCRIBE, or used to obtain information about the SELECT statement to be executed by MySQL. EXPLAINtbl_name syntax and DESCRIBEtbl_na

7.2.1 The EXPLAIN syntax (obtain SELECT-related information) The EXPLAIN tbl_name or: explain select select_options EXPLAIN statement can be used as a synonym for DESCRIBE, it can also be used to obtain information about the SELECT statement to be executed by MySQL. EXPLAIN tbl_name syntax and DESCRIBE tbl_na

7.2.1 EXPLAINSyntax (get SELECT)

EXPLAIN tbl_name 

Or:

EXPLAIN SELECT select_options 

EXPLAINStatement can be treated DESCRIBECan also be used to obtainSELECTStatement.

• EXPLAIN tbl_nameSyntax andDESCRIBE tbl_nameOrSHOW COLUMNS FROM tbl_nameSame.
  
• WhenSELECTUse keywords before a statementEXPLAINMYSQL will explain how to runSELECTStatement, which displays the table connection and connection sequence.
  

This topic describes the second method.EXPLAINUsage.

InEXPLAINWith the help of, you will know when to add an index to the table to use the index to find records so thatSELECTIt runs faster.

You can runANALYZE TABLETo update the statistical information of the table, such as the base of the key, which can help you make better choices in terms of optimization. For details, see "14.5.2.1ANALYZE TABLESyntax ".

You can also check whether the optimizer connects data tables in the optimal order. In order for the optimization program to followSELECTThe table names in the statement are connected sequentially and can be used at the beginning of the query.SELECT STRAIGHT_JOINNot justSELECT.

EXPLAINA row of records is returned, includingSELECTThe information of each table used in the statement. These tables are listed in the results in the read order of the MySQL query to be executed. MySQL uses the single-sweep (multi-join) method to resolve the connection. This means that MySQL reads a record from the first table, finds the corresponding record in the second table, searches in the third table, and so on. When all the tables are scanned, It outputs the selected fields and traces back to all the tables until they cannot be found, some tables may have multiple matching records. The next record will be read from the table and processed from the next table.

In MySQL version 4.1,EXPLAINThe output result format has changed, making it more suitable for example:UNIONStatement, subquery, and derived table structure. More importantly, it adds two new fields:idAndselect_type. If you use MySQL 4.1 earlier than MySQL, you will not be able to see these fields.

EXPLAINEach row of Results records displays information about each table. each row of records contains the following fields:

id

This time SELECT. In the query SELECTThere is an ordered value.

select_type

SELECTMay have the following types:

SIMPLE

Simple SELECT(Not used UNIONOr subquery)

PRIMARY

The outermost layer SELECT.

UNION

The second layer, in SELECTUsed later UNION.

DEPENDENT UNION

UNIONThe second statement SELECTDepends on external subqueries

SUBQUERY

The first in the subquery SELECT

DEPENDENT SUBQUERY

The first in the subquery SUBQUERYDependent on external subqueries

DERIVED

Derived table SELECT( FROMSubquery in the clause)

table

Record the referenced table.

type

Table connection type. The following lists various types of table connections, from the best to the worst:

system

The table has only one record (equal to the system table ). This is constA special case of table connection type.

const

A table can contain at most one matching record, which is read from the beginning of the query. Since there is only one record, the field values recorded by this row in the remaining optimization programs can be treated as a constant value. constTable query is very fast, because only one read is required! constUsed in and PRIMARY KEYOr UNIQUEThere is a fixed value comparison in the index. In the following queries, Tbl_nameIt is the const table:

SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref

A row of records is read from the table and associated with the records read from the previous table. And constThe difference is that this is the best connection type. It is used for all parts of the index for connection and the index is PRIMARY KEYOr UNIQUEType. eq_refIt can be used to search fields when "=" is compared. The comparative values can be fixed values or expressions, and the fields in the table can be used in the expression. They are ready before reading the table. In the following examples, MySQL uses eq_refConnect to process Ref_table:

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

ref

All records that match the search value in this table will be taken out and used together with the records obtained from the previous table. refThe leftmost prefix used by the Connection Program to use the key or the key is not PRIMARY KEYOr UNIQUEIndex (in other words, the connection program cannot retrieve only one record based on the key value. When only a few matching records are queried based on the key value, this is a good connection type. refIt can also be used to retrieve Fields =When the operator is used for comparison. In the following examples, MySQL uses refTo process Ref_table:

SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

ref_or_null

The connection type is similar. refThe difference is that MySQL will search for additional include NULLValue record. This connection type is optimized from MySQL 4.1.1 and is often used for subqueries. In the following example, MySQL uses ref_or_nullType to process Ref_table:

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

For details, see "7.2.6 How MySQL OptimizesIS NULL".

index_merge

This connection type means that Index MergeOptimization Method. In this case, keyFields include all indexes used, key_lenIncluding the longest part of the key used. For details, see "7.2.5 How MySQL Optimizes OR".

unique_subquery

This type is in the following format: INSubquery to replace ref:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subqueryIt is more efficient to completely replace the index lookup function of the subquery.

index_subquery

The connection type is similar. unique_subquery. It is replaced by a subquery. INBut it is used when the subquery does not have a unique index, for example:

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

Only records within a given range are retrieved and an index is used to obtain a record. keyField indicates which index is used. key_lenThe field contains the longest part of the key used. This type refThe field value is NULL. rangeUsed to compare a field and a field with any of the following operators =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, Or IN:

SELECT * FROM tbl_name
WHERE key_column = 10;

SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
WHERE key_part1= 10 AND key_part2 IN (10,20,30);

index

Connection type and ALLThe difference is that it only scans the index tree. It usually compares ALLHurry up, because the index file is usually smaller than the data file. MySQL uses this connection type when the queried field knowledge is a separate part of the index.

ALL

Scan all the tables and combine the records obtained from the previous table. If the first table is not identified constIn other cases, it is usually very bad. Normally, you can add indexes to quickly retrieve records from the table to avoid ALL.

possible_keys

possible_keysA field is the index that MySQL may use when searching for table records. Note that this field is completely independent EXPLAINThe order of the displayed tables. This means possible_keysThe indexes in these indexes may not be used in actual use. If the value of this field is NULLIndicates that no index is used. In this case, you can check WHEREWhich fields in the clause are suitable for adding indexes to improve query performance. In this case, create an index and use it again. EXPLAINCheck. For more information, see section 14.2.2. ALTER TABLESyntax ". If you want to see what indexes the table has, you can use SHOW INDEX FROM tbl_nameLet's see.

key

keyThe field displays the indexes actually used by MySQL. When no index is used, the value of this field is NULL. To force MySQL to use or ignore possible_keysThe index list in the field. You can use keywords in the query statement. FORCE INDEX, USE INDEX, Or IGNORE INDEX. If yes MyISAMAnd BDBType table, which can be used ANALYZE TABLETo help analyze which index is used better. If yes MyISAMType table, run the command myisamchk --analyzeThe same effect. For details, see section 14.5.2.1. ANALYZE TABLESyntax "and" 5.7.2 Table Maintenance and Crash Recovery ".

key_len

key_lenThe field shows the length of the index used by MySQL. When keyThe field value is NULLThe index length is NULL. Note, key_lenThe value tells you which indexes MySQL uses in the combined index.

ref

refFIELDS show which fields or constants are used keyUse the query records in the table.

rows

rowsThe field shows the number of records that MySQL considers to be retrieved in the query.

Extra

This field displays the additional MySQL information in the query. The following is an explanation of several different values of this field:

Distinct

When MySQL finds the first record of the matching Union result of the current record, it no longer searches for other records.

Not exists

MySQL creates LEFT JOINDuring optimization, when it finds in the current table that matches the previous record LEFT JOINAfter the condition, no more records will be searched. The following is an example of this type of query:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;

Ift2.idDefined NOT NULL. In this case, MySQL will scan the tablet1And t1.idValue int2. Whent2When a matched record is found, this meanst2.idCertainly not.NULLWill not be int2Same in searchidOther records of the value. You can also say thatt1For each record in, MySQL only needst2, Regardless oft2The actual number of matching records.

range checked for each record (index map: #)

MySQL does not find an appropriate available index. Instead, for each row join in the previous table, it performs a test to determine which index to use (if any ), use this index to retrieve records from the table. This process is not very fast, but it is always faster than doing table join joins without any indexes.

Using filesort

MySQL needs to perform an additional step to obtain records in an ordered order. The sorting program traverses all records based on the connection type and WHEREThe keys to be sorted for the condition record and the pointer to the record are stored. These keys have been sorted out, and the corresponding records are also sorted out. For details, see "7.2.9 How MySQL Optimizes ORDER BY".

Using index

The field information is obtained directly from the index tree, instead of scanning the actual records. Fields used for query are part of an independent index.

Using temporary

MySQL needs to create a temporary table Storage result to complete the query. This usually occurs when the query contains GROUP BYAnd ORDER BYClause, which lists fields in different ways.

Using where

WHEREThe clause is used to limit which records match the next table or send to the client. Unless you particularly want to obtain or check all records ExtraThe field value is not Using whereAnd the table connection type is ALLOr indexMay indicate a problem.

If you want to make the query as fast as possible, you should note that ExtraThe field value is Using filesortAnd Using temporary.

You can useEXPLAINIn the resultrowsThe product of the field value roughly shows how the connection works. It roughly tells us how many records MySQL will query during the Query Process. If you are using System Variables max_join_sizeTo obtain the query result. This product can also be used to determine which multiple tables will be executed.SELECTStatement. For details, see "7.5.2 Tuning Server Parameters ".

The following example shows how to useEXPLAINProvides information to optimize the performance of Multi-table joint queries.

Assume that the followingSELECTStatement, which is intended to be usedEXPLAINTo detect:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

In this example, make the following assumptions:

• The fields to be compared are defined as follows:
  

  Table	Column	Column Type
  tt	ActualPC	CHAR(10)
  tt	AssignedPC	CHAR(10)
  tt	ClientID	CHAR(10)
  et	EMPLOYID	CHAR(15)
  do	CUSTNMBR	CHAR(15)

  
  
• The data table index is as follows:
  

  Table	Index
  tt	ActualPC
  tt	AssignedPC
  tt	ClientID
  et	EMPLOYID(Primary key)
  do	CUSTNMBR(Primary key)

  
  
• tt.ActualPCIs unevenly distributed.

Before any optimization measures are taken EXPLAINThe analysis result is as follows:

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      range checked for each record (key map: 35)

Because the fieldtypeFor each table valueALLThis result means that MySQL creates a dikar product for all tables; that is, the combination of each record. This takes a long time because you need to scan the total number of records in each table. In this case, its product is 74 * 2135 * 74 * 3872 = 45,268,558,720Records. If the data table is larger, you can imagine how long it will take.

The problem here is that, when the field definition is the same, MySQL can use indexes for these fields more quickly (ISAMFor a table of the type, the index is not used unless the field definition is the same ). Under this premise,VARCHARAndCHARIs the same unless they are defined with different lengths. Becausett.ActualPCDefinedCHAR(10),et.EMPLOYIDDefinedCHAR(15), The length of the two is inconsistent.
To solve this problem, you need to useALTER TABLETo increaseActualPCFrom 10 to 15 characters:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

Nowtt.ActualPCAndet.EMPLOYIDAllVARCHAR(15)
. Run againEXPLAINStatement:

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

This is not enough. It can do better: NowrowsThe product of values is 74 times less. This query takes 2 seconds.
The second change is to eliminatett.AssignedPC = et_1.EMPLOYIDAndtt.ClientID = do.CUSTNMBRThe length of the field is inconsistent:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
    ->                MODIFY ClientID   VARCHAR(15);

NowEXPLAINThe result is as follows:

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

This seems to be the best result.
The legacy problem is that MySQL considers the field by defaulttt.ActualPCThe values are evenly distributed, but the tablettThis is not the case. Fortunately, we can easily let MySQL analyze the index distribution:

mysql> 
 
  ANALYZE TABLE tt;
  

By now, the table connection has been optimized perfectly,EXPLAINThe result is as follows:

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

Please note that,EXPLAINIn the resultrowsThe field value is also roughly guessed by the MySQL connection optimization program. Check whether the value is basically the same as the actual value. If not, you canSELECTStatementSTRAIGHT_JOINTo achieve better performance, you can also tryFROM
Each table is listed in different order in a clause.

Optimize MySQL (1) to optimize MySQL (3 )›