MySQL index optimization analysis and SQL optimization

1 Description of the configuration environment MySQL version information: System Version information:
2 analysis of the index 2.1 data Preparation 2.1.1 Database table description of SQL table: ID is the self-increment primary key, name is a unique index, age is not a unique index, DESC no index

CREATE TABLE ' index_test ' (  ' id ' int (one) unsigned not NULL auto_increment COMMENT ' self-increment id ',  ' name ' varchar (+) COLL ATE utf8_bin NOT null DEFAULT ' COMMENT ' name ',  ' age ' int (one) NOT null COMMENT ' ages ',  ' desc ' varchar (+) CHARACTER SET UTF8 NOT null DEFAULT ' COMMENT ' description ',  ' status ' tinyint (4) NOT null COMMENT ' state ',  PRIMARY KEY (' id '),  UNIQ UE key ' Uniq_name ' (' name '),  key ' idx_age ' (' Age ')) Engine=innodb DEFAULT Charset=utf8;

2.1.2 Test data in Table 2.2 Index Analysis 2.2.1 Use explain to view the execution plan for SQL

In MySQL, you can precede SQL with the explain statement to display the execution plan for the SQL, and the output is as follows:

2.2.2 Explain detailed 2.2.2.1 Select_type

Select_type represents the type of query statement, which has the following main values:

Simple: Represents a single-table query

Primary: Represents the appearance of subqueries

Derived: query for derived tables

Subquery: The inner first SQL of a subquery

Union: The table that represents the union operation being connected

Union Result: Represents the result table after the join operation

Depend Union represents a union statement in a subquery

Depend subquery represents results generated in subqueries

2.2.2.2 table

The table name of the table involved in the current SQL query, Note that there are times when the table name of the intermediate result table, MySQL will be generated according to their own rules

The value of the 2.2.2.3 type type reflects the execution performance of SQL to a large extent, according to the highest performance, the value of type is: NULL , System,const,eq_reg,ref,range,index,all NULL does not need to look up the table, the fastest                                  system when there is only one piece of data in the table Const constant query is generally based on unique key or primary key equivalence query                                  Eq_reg table is connected when the result of the query in the B table in a table in this case by a unique index value query row

Ref non-Unique index query

Range returns a scan using a unique index

Index scans the entire index file, such as a query that overwrites an index, just slightly faster than a full table query, because the index file is generally smaller than the data file, so the index data is read into memory more, so that disk IO is less

All means full table scan, which is one of the least efficient queries

                         2.2.2.4 possible key  &nbsp ;                         represents the index that may be used, regardless of the order in which the table is connected                          2.2.2.5 key                              indicates that MySQL executes the name of the index for this SQL election, which can be obtained by force Idex and ignore index. Forced changes to SQL execution required indexes                          2.2.2.6 KEY_LEN&N Bsp                           The index's own tree is calculated based on the type of the indexed field, and nbsp                            int   For example, 11 nbsp: Index Length 4                                  &NBSp     VARCHAR (128) and the encoding is U8   index length is calculated by: 128*3+2                          2.2.2.7 ref                            indicates which column to use to select rows from a table, with a value of Cohen is const                    &NBSP ;    2.2.2.8 rows                            Indicates the number of rows that must be scanned to execute this SQL                          2.2.2.8 EXTRA&NB Sp                           contains detailed information on MySQL generation execution plan:

Distinct find unique values, once found will not continue to look for (temporarily did not think good example)

The record did not find the ideal index

Use file sort uses outer row to sort efficiency low

Use index returns data using the overwrite index, no scan table

Use tempoary using temporary tables to combine return data is less efficient

Use where to filter the returned data in the MySQL storage engine layer without filtering the data, only at the MySQL service layer to filter the data

2.3 Profiling detailed 2.3.1 Open profiling because profiling is relatively resource-intensive, so the general MySQL default is to turn off the profiling function, and Prof Iling is only valid for the current session, currently does not support global profiling, you can view and develop the profiling function with the following commands:

SELECT @ @profiling  Returns the result if 0 indicates that the profiling function of the current session is closed set profiling=1 open the profiling function of the current session

2.3.2 Profiling use 2.3.2.1 to query the summary of the current session's profiling can be obtained using the show Profiles command                            Take a summary of the SQL executed by the current session 2.3.2.2 profiling The syntax for profiling is as follows:

SHOW profile [Type [, type] ...]    [For QUERY N]    [LIMIT Row_count [OFFSET offset]]type:    all  | BLOCK IO  | CONTEXT Switches  | CPU  | IPC  | MEMORY  | PAGE Faults  | SOURCE  | SWAPS

Examples of Use:

Result Description:

When using profiling to view a detailed execution plan for SQL, the main concerns are the first two columns: Status and Duration

Status indicates that the execution status of SQL and the show full process list look the same

Duration represents the time each state executes to see where the primary execution time of SQL is consumed

The next thing to focus on is the Cup,io,swap details

Cup indicates CPU consumption time

Swap indicates the swap condition of the machine.

IO indicates the consumption of IO

3 Invalid Indexin many cases, MySQL tables are indexed, and indexes are used for filtering in query conditions, but they are not necessarily used in indexes, such as the following
3.1 The filter condition contains implicit conversions in the following example, the Name field adds a unique index, but the name field is of type varchar, and the filter is added with an int type, and an implicit conversion occurs, so a full table scan is taken. It's more obscure here. Last week, when we had a project to analyze the hotel order, the hotel order in hive included all the orders for the hotel project, the order ID was varchar type, and we needed to count the orders for an event in Qta, and we needed to query QTA's Order Details library. (QTA Order Details is a subset of orders in hive), the order ID is a long type, the first query at the time directly after a table query, and then another table query, the results see a simple SQL execution is huge slow. Finally, the reason for the analysis is located in this.

3.2 Functional indexes are not supported add a non-unique index above the age field, but an absolute value function is used, so the index above the age field cannot be used. This is a lot to do with the date. 3.3 The cost of index scanning is greater than direct full table scan if only the index filters less data, then the full table scan will be done, because the index will be scanned once before the index is used. Then according to the scan to the index back to the table to find the required data, so that the efficiency of the scan is actually lower, so the direct walk full table scan 3.4 using the "%" prefix match when the Name field added a unique index but the Use '% ' as the prefix match condition, so do not use index, direct walk full table Scan 3.5 composite Index non-left prefix matchWhen using a composite index, the index is not used if the left prefix filter condition is not used, or the full table is scanned3.5 or filters are indexed before and after they are added. Index

When you use or as a filter, the before and after filters must be indexed so that you can use the index or the entire SQL cannot use the index

MySQL index optimization analysis and SQL optimization