MySQL SQL statement analysis and optimization

This is the fifth day of my participation in the August More text Challenge. For details, see: August More Text Challenge

Due to the developing to the database will be used by the mysql database is one of the most frequently used, because it's free, open source performance is very good also, but flat in the beginning the most influence the efficiency of the query is developers to write SQL statements is undeserved, cause efficiency problem caused by large amount of data, then teach you commonly used SQL analysis and optimization method.

Explain

The explain keyword is built-in in mysql to analyze the performance of the current query statement. It is very simple to use. You only need to add explain before the select statement, and then execute the select statement.

Explain official document

Test the SQL

explain select * from sys_user
Copy the code

Results:

The above is a simple test, and you can see that the result is not the data in the sys_user table, but an analysis of the SQL.

  • Id: The id column is the sequence number of the select column. The number of the select column is the number of the IDS. The order of the IDS indicates obedience, and the larger the ID, the higher the priority.

  • Select_type: indicates that select is the query type. The options are as follows:

    1. Simple: Simple query. The query does not contain subqueries and unions
    2. Primary: the outermost select in complex queries
    3. Subquery: subquery included in select (not in FROM clause)
    4. Derived: a subquery contained in the from clause. MySQL stores the results in a temporary table, also known as a derived table, as derived translates
    5. Union: The second and subsequent select statement in the union


  • 1 and 2 represent the ids of select rows that participate in the union.
    ,2>
    ,2>
    ,2>
    ,2>
    ,2>
    ,2>
    ,2>
    ,2>
    ,2>
    ,2>

  • Access type: Access type: Access type: Access type: Access type: Access type: Access type: Access type

    1. NULL: indicates that no table or index is used during statement execution
    2. Const,system:mysql can optimize a part of a query and convert it to a constant. When all columns used for primary or unique keys are compared to constants, the table has at most one matching row and is read once, which is relatively fast. System is a special case of const, which is used only if the table is queried with only one row

    3. Eq_ref: If all parts of the primary key or unique key index are connected, at most one record will be returned. 4. Range: Range scanning occurs in(), between,>,<, >=, etc. 5. The index: Scan the whole index can get the result, general is to scan a secondary indexes, the scan will not from the index began to quickly find root node, but directly to the secondary index leaf node traversal and scanning, speed or slower, the query is commonly used to cover index, secondary indexes is compared commonly small, so this is usually faster than ALL some 6. ALL: A full table scan, which scans all the leaves of your clustered index, is the slowest.

    System > const > eq_ref > ref > range > index > ALL

  • Key: This column shows which index mysql actually uses to optimize access to this table.

  • Possible_keys: This column shows which indexes the query might use to find. Possible_keys has a column and the key is NULL. This is because there is not much data in the table, and mysql decided that the index was not helpful to this query and chose the full table query.

  • Key_len: This column shows the number of bytes used by mysql in the index. This value can be used to determine which columns of the index are used

  • Ref: This column displays the column or constant used to look up the table value in the index of the key column record

  • Row: Estimates the number of rows to read and detect, not the number of rows in the final result set

  • Extra: This line tells us something about the current query.

    1. Using index: Using an overridden index

    2. Using WHERE: The where statement is used to process the results and the queried columns are not overwritten by the index

    3. Using index condition: The queried columns are not completely overwritten by the index

    4. Using Temporary: Mysql needs to create a temporary table to process queries

    5. Using filesort: external sorting will be used instead of index sorting. If data is small, it will be sorted from memory, otherwise it will be done on disk

    6. Select tables Optimized Away: When you use some aggregate function, such as Max, to access a field with an existing index

The next part will bring you some practical tips on how to optimize SQL index, think you can also point to like it, thank you guys, Jimei!