MySQL index optimization analysis and SQL Optimization

1. Environment configuration description MySQL version information: System Version Information: 2. Index Analysis 2.1 data preparation 2.1.1 description of database tabulation SQL table: id is an auto-incrementing primary key, name is a unique index, and age is a non-unique index. desc has no index. CREATETABLE 'index _ test' ('id' int (11) unsignedNOTNULLAUTO_INCREMENTC

1. Environment configuration description MySQL version information: System Version Information: 2. Index Analysis 2.1 data preparation 2.1.1 description of database tabulation SQL table: id is an auto-incrementing primary key, name is a unique index, and age is a non-unique index. desc has no index. create table 'index _ test' ('id' int (11) unsigned not null AUTO_INCREMENT C

1. Description of the Environment configuration MySQL version: System Version:
2 Index Analysis 2.1 data preparation 2.1.1 description of the SQL table created by the database: id is the auto-incrementing primary key, name is the unique index, age is a non-unique index, and desc is not indexed

Create table 'index _ test' ('id' int (11) unsigned not null AUTO_INCREMENT COMMENT 'auto-incrementing id', 'name' varchar (128) COLLATE utf8_bin not null default ''comment 'name', 'age' int (11) not null comment 'age', 'desc' varchar (128) character set utf8 not null default ''comment' description', 'status' tinyint (4) not null comment' status', primary key ('id '), unique key 'uniq _ name' ('name'), KEY 'idx _ age' ('age') ENGINE = InnoDB default charset = utf8;

2.1.2 test data in the table 2.2 Index Analysis 2.2.1 use explain to view the SQL Execution Plan

In MySQL, you can add an explain statement before the SQL statement to display the SQL Execution Plan. The output content is as follows:

2.2.2 explain 2.2.2.1 select_type

Select_type indicates the type of the query statement. The values are as follows:

Simple: indicates a simple single table query.

Primary: the appearance of the subquery.

Derived: Query of derived tables

Subquery: the first internal SQL statement of the subquery.

Union: The joined table of the union operation.

Union result: the result table after the join operation.

Depend union indicates the union statement in the subquery.

Depend subquery indicates the results generated in the subquery.

2.2.2.2 table

The name of the table involved in the current SQL query. Note that the table name of the intermediate result table is sometimes used here. MySQL will generate the table according to its own rules.

2.2.2.3 The value of type reflects the SQL Execution performance to a large extent. The values of type are: NULL, system, const, eq_reg, ref, range, index, ALL No query table is required for NULL, the fastest speed System: when there is only one data record in the table, the type is system. The const constant query is generally based on the unique key or primary key equivalent query. When table eq_reg is connected to table B, table a queries a row based on the unique index value.

Ref non-unique index Query

Range returns a scan using a unique index.

Index scans the entire index file. For example, index-covered queries are only slightly more efficient than full-table queries. Because index files are generally smaller than data files, more index data is read into the memory at a time, in this way, the disk IO will be less

All indicates full table scan, which is the most efficient query.

2.2.2.4 possible key indicates the indexes that may be used, and the display sequence is irrelevant to the table connection sequence. 2.2.2.5 key indicates the name of the index selected for MySQL to execute this SQL statement, force idex and ignore index can be used to forcibly change the index 2.2.2.6 key_len required for SQL Execution. The index occupies its own tree and is calculated based on the index field type, for example, if the int (11) index length is 4 varchar (128) and the encoding is U8 index length, the calculation method is: 128*3 + 2 2.2.2.7 ref, which indicates which column is used to select rows from the table, if the value is const 2.2.2.8 rows, the number of rows that must be scanned for executing the SQL statement 2.2.2.8 extra contains detailed information about the execution plan generated by MySQL:

Distinct: Find the unique value. Once it is found, it will not continue searching (for the time being, I have not figured out an example)

The record does not find the desired index.

Use file sort uses an external queue to sort data less efficiently.

Use index to overwrite the index and return data without scanning the table

Use tempoary use temporary tables to combine and return less efficient data

Use where to use the where condition to filter the returned data. The data is not filtered out at the storage engine layer of MySQL and can only be filtered at the MySQL service layer.

2.3 profiling description 2.3.1 enabling profiling because profiling is resource-consuming, the profiling function is disabled by default in MySQL, and profiling is only valid for the current session. Currently, it does not support global profiling, you can run the following command to view and develop the profiling function:

If the result returned by SELECT @ profiling is 0, the profiling function of the current session is disabled. set profiling = 1 to enable the profiling function of the current session.

2.3.2 use of profiling 2.3.2.1 to query the summary of the profiling of the current session. You can use the show profiles command to obtain the summary of the SQL statements executed by the current session 2.3.2.2 the syntax of 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

Example:

Result description:

When you use profiling to view detailed SQL Execution plans, the first two columns are mainly concerned: status and duration.

Status indicates that the SQL Execution status is the same as that shown in show full process list.

Duration indicates the execution time of each state. You can see where the main execution time of SQL is consumed.

The second thing to note is the detailed information about cup, io, and swap.

Cpu indicates the cpu consumption time.

Swap indicates the swap status of the machine

Io indicates io consumption

3. Invalid indexes are often indexed in MySQL tables and used for filtering in query conditions. However, indexes are not necessarily used, for example, in the following situations.
3.1 The filtering conditions include implicit conversions. In the following example, the name field adds a unique index, but the name field type is varchar, And the int type is added for filtering, because implicit conversion occurs, full table scan is performed. This is relatively obscure. When a project analyzed a hotel order last week, the hotel order in hive contained all the orders for the hotel project. The order id is of the varchar type, we need to count the orders that participate in an activity in QTA, and query the Order details database of QTA (QTA order details are a subset of orders in hive) the Order ID in the table is of the long type. At the beginning of the query, the Order ID in the table is directly queried before another table. The result shows that a simple SQL statement is very slow to execute. Finally, the cause is located above.

3.2 non-unique indexes are added to the age field of a functional index, but the absolute value function is used. Therefore, the indexes on the age field cannot be used. When processing the date, this kind of pitfall often occurs. The cost of 3.3 index scanning is higher than that of direct full table scanning. If only the index-filtered data is small, the full table scan will be taken directly, because the index is scanned first, and then the data is retrieved from the table based on the index, the scanning efficiency is actually lower, therefore, when "%" is used for prefix matching in full table scan 3.4, the name field adds a unique index but '%' is used as the prefix matching condition. Therefore, no index is used, scan the entire table directly. 3.5 The non-left prefix of the composite index will not be used if the filter condition of the Left prefix is not used when the composite index is used, the system still performs an index only when the full table scan is 3.5 or the index is added before and after filtering.

When you use or as a filtering condition, you must add an index before and after the or filter condition to use the index. Otherwise, the entire SQL statement cannot use the index.