Explanation of explain functions in Mysql and mysqlexplain

Explanation of explain functions in Mysql and mysqlexplain

I. MYSQL Indexes

Index: a data structure that helps Mysql efficiently obtain data. To improve search efficiency, you can compare it to a dictionary. It can be simply understood as a sorted data structure for fast search.

Index function:It is easy to query and sort (So adding an index will affect the where statement and order by sort Statement ).

In addition to data, databases also maintain data structures that meet specific search algorithms. These data structures reference data in some way. In this way, you can implement advanced search algorithms on these data structures. These data structures are indexes.

The index itself is also very large and cannot be fully stored in the memory. Therefore, the index is often stored on the disk as an index file.

The indexes we usually call are generally B-tree indexes if not specified. (Clustered index, composite index, prefix index, and unique index are all B + tree indexes by default.) besides B tree indexes, there are also hash indexes.

Advantages:

A. Improve data retrieval efficiency and reduce database I/O costs
B. Sort data by index columns, reducing data sorting costs and CPU consumption.

Disadvantages:

A. The index is also A table. The table stores the primary key and index fields and points to the records of the object table. Therefore, the index also occupies space.
B. When performing INSERT, UPDATE, and DELETE operations on a table, MYSQL not only updates the data, but also saves the corresponding information of the index column fields added each time the index file is updated.

In the actual production environment, we need to gradually analyze, optimize the establishment of the Optimal Index, and optimize our query conditions.

Index category:

1. A single-value index contains only one field. A table can have multiple single-column indexes.
2. The value of the unique index column must be unique, but null values are allowed.
3. A composite index contains multiple columns.

We recommend that you create indexes within five for one 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;

Ii. Roles of EXPLAIN

EXPLAIN: simulate how the Mysql optimizer executes SQL query statements to know how Mysql processes your SQL statements. Analyze the performance bottleneck 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 execution sequence with the same id ranges 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 sequence number is automatically increased. The higher the id value, the higher the priority. The more advanced the id value is.

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 IDs 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 indicates that the derived table tb1 is id = 2.

(2) select_type column: Operation Type of the data read operation

1. SIMPLE: SIMPLE select query. SQL does not contain subqueries or UNION statements.
2. PRIMARY: the query contains complex subqueries. The outermost query is marked as PRIMARY.
3. SUBQUERY: SUBQUERY is included in the select or WHERE list.
4. DERIVED: subqueries contained in the from list are marked as DERIVED (DERIVED table). MYSQL recursively executes these subqueries and places the result set in the zero-time table.
5. UNION: If the second SELECT clause appears after UNION, it is marked as a UNION. If UNION is included in the subquery of the from clause, the outer SELECT is marked as DERIVED.
6. union result: select from the UNION table

(3) table column: Which table is the row data about?

(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 the const type and will not appear in business at ordinary times.
2. const: query data once through the index. This type is mainly used to compare the primary key or unique index. Because only one row of data is matched, It is very fast. If the primary key is placed behind the WHERE statement, mysql can convert the query to a constant.
3. eq_ref: unique index scan. For each index key, the table only matches one record. Common in primary key or unique index scanning.
4. ref: A non-unique index scan returns an index that matches a single row that is worth all. In essence, it is an index access. It returns all rows that match a single value, that is to say, it may find multiple matching data, so it is a mixture of search and scan.
5. range: only retrieve rows with a given range and use an index to select rows. The key column shows the index used. Queries such as between, <,>, and in appear in your WHERE statement. This given range scan is better than full table scan. Because he only needs to start from one index point, but ends with another, and does not need to scan all indexes.
6. index: FUll Index Scan scans and traverses the index tree (scans the entire table Index and obtains data from the index ).
7. ALL full table scans obtain millions of data from disks. Optimize ALL data types as much as possible.

(5) possible_keys column: displays one or more indexes that may be applied to this table. If an index exists for the fields involved in the query, the index is listed, but not necessarily used in the query.

(6) keys column: actually used index. If it is NULL, no index is used. If an overwrite index is used in the query, the index only appears in the key list. Overwrite index: the fields after the select statement are the same as the number of fields created for the index.

(7) The ken_len column indicates the number of bytes used in the index. You can use this column to calculate the length of the Index Used in the query. The shorter the length, the better. The value displayed by key_len is the maximum possible length of the index field, rather than the actual length. That is, key_len is calculated based on the table definition and is not retrieved from the table.

(8) ref column: displays which column of the index is used. If possible, it is a constant. Which columns or constants are used to find the value of the index column.

(9) rows column (How many rows are queried by optimizer in each table): roughly estimate the number of rows to be read Based on the table statistics and index selection.

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

1. Using filesort (File Sorting): mysql cannot read data according to the specified index sequence 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)

Note: order_number is a unique index column in the table, but order by does not use this index column for sorting. Therefore, mysql has to sort another column.

2. Using temporary: Mysql uses a temporary table to save intermediate results, which is common in sorting order by and grouping query group.

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 the overwriting index to avoid accessing the table's data rows, which is efficient.

If the Using where clause appears at the same time, it indicates that the index is used to search for the index key value.

If the using where clause does not appear at the same time, it indicates that the index is used to read data instead of performing the search 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 connection cache is used for the current SQL statement.

6. impossible where: The where clause is always false. mysql cannot obtain data rows.

7. select tables optimized away:

8. distinct:

Summary

The above is all the details about the role of explain in Mysql. I hope it will be helpful to you. If you are interested, refer to: MYSQL subquery and nested query optimization instance resolution, several important MySQL variables, and analysis of technical points of oracle SQL statement optimization, you are welcome to leave a message saying that the editor will promptly reply to you and make corrections. Thank you for your support!