Detailed explanation of explain in Mysql

1. Index of MYSQL

Index (Index): A data structure that helps Mysql obtain data efficiently. For improving search efficiency, it can be compared to a dictionary. It can be simply understood as an ordered data structure for quick search.

What an index does: It is easy to query and sort (so adding an index affects the where statement and the order by sort statement).

Beyond the data, the database maintains data structures that satisfy a specific lookup algorithm and refer to the data in some way. In this way, advanced lookup algorithms can be implemented on these data structures. These data structures are indexes.

Indexes themselves are too large to be stored entirely in memory, so indexes are often stored on disk in the form of index files.

The index we usually say, if not specifically specified, is generally B tree index. (Clustered index, composite index, prefix index and only 1 index are all B + tree indexes by default), and there are hash indexes in addition to B tree indexes.

Advantages:

A, improve the efficiency of data retrieval and reduce the cost of IO of database
B, through the index column to sort the data, reduce the cost of data sorting, reduce the consumption of CPU.

Disadvantages:

A, the index is also a table, which holds the primary key and index fields and points to the records of the entity table, so the index also takes up space.
B, when INSERT, UPDATE and DELETE are operated on the table, MYSQL will not only update the data, but also save the corresponding information of index column fields added every time the index file is updated.

In the actual production environment, we need to analyze step by step, optimize and establish the optimal index, and optimize our query conditions.

Classification of indexes:

1. Single-valued index One index contains only one field, and one table can have multiple single-column indexes.
2. The value of a 1-only index index column must be 1-only, but null values are allowed.
3. Composite index 1 index contains multiple columns

It is recommended to establish indexes within 5 for 1 table

Syntax:

1. CREATE [UNIQUE] INDEX indexName ON myTable (columnName (length);
2. ALTER myTable Add [UNIQUE] INDEX [indexName] ON (columnName (length);

Delete: DROP INDEX [indexName] ON myTable;

View: SHOW INDEX FROM table_name\ G;

2. Role of EXPLAIN

EXPLAIN: Simulates how the Mysql optimizer executes the SQL query statement, thus knowing how Mysql handles your SQL statement. Analyze the performance bottlenecks of your query statement or table structure.

mysql> explain select * from tb_user;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE   | tb_user | ALL | NULL     | NULL | NULL  | NULL |  1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

(1) id column:

(1) The same execution sequence of id is from top to bottom

mysql> explain 
  -> SELECT*FROM tb_order tb1
  -> LEFT JOIN tb_product tb2 ON tb1.tb_product_id = tb2.id
  -> LEFT JOIN tb_user tb3 ON tb1.tb_user_id = tb3.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref            | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| 1 | SIMPLE   | tb1  | ALL  | NULL     | NULL  | NULL  | NULL           |  1 | NULL |
| 1 | SIMPLE   | tb2  | eq_ref | PRIMARY    | PRIMARY | 4    | product.tb1.tb_product_id |  1 | NULL |
| 1 | SIMPLE   | tb3  | eq_ref | PRIMARY    | PRIMARY | 4    | product.tb1.tb_user_id  |  1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+

(2) If it is a subquery, the id serial number will increase by itself, and the higher the id value, the higher the priority, and the more it will be executed first.

mysql> EXPLAIN
  -> select * from tb_product tb1 where tb1.id = (select tb_product_id from tb_order tb2 where id = tb2.id =1);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref  | rows | Extra    |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY   | tb1  | const | PRIMARY    | PRIMARY | 4    | const |  1 | NULL    |
| 2 | SUBQUERY  | tb2  | ALL  | NULL     | NULL  | NULL  | NULL |  1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

(3) The same and different id exist at the same time

mysql> EXPLAIN 
  -> select * from(select * from tb_order tb1 where tb1.id =1) s1,tb_user tb2 where s1.tb_user_id = tb2.id;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY   | <derived2> | system | NULL     | NULL  | NULL  | NULL |  1 | NULL |
| 1 | PRIMARY   | tb2    | const | PRIMARY    | PRIMARY | 4    | const |  1 | NULL |
| 2 | DERIVED   | tb1    | const | PRIMARY    | PRIMARY | 4    | const |  1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

derived2: Derived table 2 represents a derived table tb1 with id=2

(2) select_type column: Operation type of data read operation

1. SIMPLE: Simple select query, SQL contains no subquery or UNION.
2. PRIMARY: The query contains complex subquery parts, and the outermost query is marked as PRIMARY
3. SUBQUERY: Subqueries are included in the select or WHERE list
4. DERIVED: Subqueries included in the FROM list are marked as DERIVED (derived table), and MYSQL executes these subqueries recursively, placing the result set in the zero-time table.
5. UNION: If the second SELECT appears after UNION, the marked bit UNION; If UNION is included in a subquery of the FROM clause, the outer SELECT is marked as DERIVED
6. UNION RESULT: select for obtaining results from UNION table

(3) table column: Which table does the row data relate to

(4) type column: Access type from good to bad system > const > eq_ref > ref > range > index > ALL

1. system: The table has only one record (equal to the system table), which is a special case of const type and will not appear in normal business.
2. const: The data is found once through the index. This type is mainly used to compare primary key or unique indexes, because only one row of data is matched, so it is very fast; If you place the primary key after the WHERE statement, Mysql converts the query to a constant.
3. eq_ref: Only one index scan, for each index key, only one record in the table matches it. Common in primary key or only 1 index scanning.
4. ref: Non-only 1 index scan returns all rows matching a single value, which is essentially a kind of index access. It returns all rows matching a single value, that is to say, it may find multiple pieces of qualified data, so it is a mixture of search and scan.
5. range: Retrieves only rows in a given range, using 1 index to select rows. The key column shows which index is used. between appears in your WHERE statement, < , > , in, and so on, this given range scan is better than a full table scan. Because it only needs to start at one point of the index and end at another point, it doesn't need to scan all the indexes.
6. index: FUll Index Scan scans through the index tree (scans the index of the whole table and obtains data from the index).
7. ALL full table scanning obtains data from disk. Million-level data. ALL type data is optimized as much as possible.

(5) possible_keys column: Displays one or more indexes that may be applied to this table. If there is an index for the field involved in the query, the index will be listed, but it will not be actually used by the query.

(6) keys column: The index actually used. If it is NULL, no index is used. If an override index is used in a query, it appears only in the key list. Override index: The field after select is 1 with the number of fields we index.

(7) ken_len column: Indicates the number of bytes used in the index from which the index length used in the query can be calculated. Without losing accuracy, the shorter the length, the better. key_len displays the maximum possible length of the index field, not the actual length, i.e. key_len is calculated from the table definition, not retrieved from within the table.

(8) ref Column: Shows which column of the index is used, if possible, a constant. Which columns or constants are used to find values on index columns.

(9) rows column (how many rows per table are queried by the optimizer): Based on table statistics and index selection, roughly estimate the number of rows to read to find the required records.

(10) Extra column: Extended attributes, but important information.

1. Using filesort (file sorting): mysql cannot be read according to the established index order in the table.

 mysql> explain select order_number from tb_order order by order_money;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra     |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE   | tb_order | ALL | NULL     | NULL | NULL  | NULL |  1 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

Explanation: order_number is a 1-only index column in the table, but order by does not use this index column to sort, so mysql has to use another column to sort.

2. Using temporary: Mysql uses temporary tables to hold intermediate results, which are common in sorting order by and grouping query group by.

mysql> explain select order_number from tb_order group by order_money;
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra              |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE   | tb_order | ALL | NULL     | NULL | NULL  | NULL |  1 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

3. Using index indicates that the corresponding select operation uses override index, which avoids accessing the data rows of the table and is efficient.

If Using where occurs at the same time, the index is used to perform a lookup of index key values.

If using where does not appear at the same time, it indicates that the index is used to read data instead of performing a lookup action.

mysql> explain select order_number from tb_order group by order_number;
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys   | key        | key_len | ref | rows | Extra    |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| 1 | SIMPLE   | tb_order | index | index_order_number | index_order_number | 99   | NULL |  1 | Using index |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

4. Using where Lookup

5. Using join buffer: Indicates that the current sql uses connection caching.

6. impossible where: The where sentence is always false, and mysql cannot get data rows.

7. select tables optimized away:

8. distinct:

Summarize

The above is the whole content of the detailed explanation of explain in Mysql, hoping to be helpful to everyone. Interested friends can refer to: MYSQL subquery and nested query optimization instance analysis, several important MySQL variables, ORACLE SQL statement optimization technical points analysis, etc. If there are shortcomings, please leave a message and point out that this site will reply to everyone in time and make corrections. Thank you friends for your support to this site!