Moment For Technology

MySQL Advanced Series: This article explains the meanings of each field

Posted on Dec. 3, 2022, 8:48 a.m. by Riley Cochran
Category: The back-end Tag: The back-end mysql

This is the second day of my participation in the August More text Challenge. For details, see:August is more challenging

Explain is useful: In order to know how to optimize the execution of SQL statements, you need to view the execution process of SQL statements to speed up the execution efficiency of SQL statements.

You can use explain+SQL statements to simulate the optimizer's execution of SQL queries to see how mysql handles SQL statements. Look at the execution plan to see if the executor is processing the SQL as we want it to.

The information contained in the Explain execution plan is as follows:

  • Id: indicates the serial number of the query
  • Select_type: indicates the query type
  • Table: table name or alias
  • Partitions: matching partitions
  • Type: indicates the access type
  • Possible_keys: Possible index
  • Key: indicates the actual index
  • Key_len: indicates the index length
  • Ref: Column to be compared with the index
  • Rows: Estimated number of rows
  • Filtered: Percentage of rows filtered by table criteria
  • -Blair: Extra information

Here's what each column represents and the corresponding SQL.

The test used mysql version 5.7, using the following three table structures

CREATE TABLE `demo`.`emp` ( `emp_id` bigint(20) NOT NULL, 'name' varchar(20) CHARACTER SET utf8MB4 COLLATE UTf8MB4_bin NULL DEFAULT NULL COMMENT 'name' varchar(20) CHARACTER SET UTf8MB4 COLLATE UTf8MB4_bin NULL DEFAULT NULL COMMENT 'empno' int(20) NOT NULL COMMENT 'iD ',' deptno 'int(20) NOT NULL COMMENT' iD ', 'sal' int(11) NOT NULL DEFAULT 0 COMMENT 'sale ', PRIMARY KEY (' emp_id') USING BTREE, INDEX 'u1' (' deptno ') USING BTREE, UNIQUE INDEX `u2`(`empno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic; CREATE TABLE 'demo'. 'dept' (' id 'bigint(20) NOT NULL,' deptno 'int(20) NOT NULL COMMENT' deptno ', 'dname' varchar(20) CHARACTER SET UTF8 COLLATE UTF8_bin NULL DEFAULT NULL COMMENT 'iD ', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `dept_u1`(`deptno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic; CREATE TABLE `demo`.`salgrade` ( `id` bigint(20) NOT NULL, `losal` int(20) NULL DEFAULT NULL, `hisal` int(20) NULL DEFAULT NULL, `emp_id` bigint(20) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;Copy the code

An id column

Select The sequence number (a group of numbers) of a query, indicating the order in which the SELECT clause or operation table is executed in the query.

The ID column is divided into three cases:

1, if the ids are the same, then the execution order is from top to bottom

mysql explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;
Copy the code

2, if the id is different, if it is a subquery, the id id will be increased, the larger the ID value, the higher the priority, the earlier the execution \

mysql explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');
Copy the code

3. The same and different ids exist simultaneously: The same ids are considered as a group and executed from top to bottom. In all groups, the larger the ID value, the higher the priority and the higher the priority

mysql explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal wheree.deptno = (select d.deptno from dept d where d.dname = 'SALES');
Copy the code

Second, select_type columns

It is mainly used to identify the type of query, whether it is a normal query, a federated query or a subqueryCopy the code

1. Sample: A simple query without subqueries and union

mysql explain select * from emp;
Copy the code

  1. Primary: If the query contains any complex subqueries, the outermost query is marked as primary
mysql explain select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');
Copy the code

  1. Union: The second and subsequent select in union, Union ALL, and subqueries is marked as union
mysql explain select * from emp where deptno = 10 union select * from emp where sal 2000;
Copy the code

4. Dependent Union: In a large query that contains a union or a union ALL, the select_type value of each query is dependent union except for the leftmost query.

mysql explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal 2000)
Copy the code

5. Union result: Select the result from the union table.

mysql explain select * from emp where deptno = 10 union select * from emp where sal 2000;
Copy the code

6. Subquery: include subquery in select or WHERE list (not in from clause)

mysql explain select * from emp where sal  (select avg(sal) from emp) ;
Copy the code

7. Dependent subquery: The first select in a dependent subquery (not in the FROM clause) that is dependent on the outside query.

mysql explain select e1.* from emp e1 WHERE e1.deptno = (SELECT deptno FROM emp e2 WHERE e1.empno = e2.empno);
Copy the code

8. Derived: **** Subqueries included in the FROM list are marked as derived, also called derived classes

mysql explain select * from ( select emp_id,count(*) from emp group by emp_id ) e;
Copy the code

9. UNCACHEABLE SUBQUERY: The result of a SUBQUERY cannot be cached, and the first row of the outer link must be reevaluated.

mysql explain select * from emp where empno = (select empno from emp where [email protected]@sort_buffer_size);
Copy the code

10. Uncacheable UNION: Indicates that the union query result cannot be cached: no specific SQL statement validation is found.

Three, the table columns

Which table is being accessed by the corresponding row, table name or alias, possibly temporary table or union merge result set.

1. If it is a specific table name, it indicates that the data is obtained from the actual physical table. Of course, it can also be an alias of the table.

The table name is of the form derivedN, representing the table derived from the query with ID N.

3. Table name is in the form of union N1,n2, etc. N1 and n2 indicate the ID of the participant in the union.

Four, the type column

Type shows the access type, and the access type indicates how I access our data. The easiest way to think about is full table scan, which is very inefficient to directly traverse a table to find the required data.

There are many types of access, and the efficiency from best to worst is:

system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL

In general, to ensure that the query is at least range level, preferably ref

1. All: Scans the entire table to find the required rows. In general, such SQL statements and large amounts of data will need to be optimized.

mysql explain select * from emp;
Copy the code

This is more efficient than all. There are two main cases. One is that the current query overwrites the index, that is, the data we need can be requested in the index, or the index is used to sort the data, so as to avoid reordering the data

mysql explain  select empno from emp;
Copy the code

=, , =, , =, IS NULL, BETWEEN, LIKE, or IN() = =, , =, =, IS NULL, BETWEEN, LIKE, or IN()

mysql explain select * from emp where empno between 100 and 200;
Copy the code

4. Index_subquery: Use indexes to associate subqueries without scanning the full table

mysql explain select * from emp where deptno not in (select deptno from emp)
Copy the code

But most of the time when you use a SELECT subquery, the MySQL query optimizer automatically optimizes the subquery to a join table query, so type is not shown as INDEx_subquery, but ref

5. Unique_subquery: This join type is similar to index_subquery in that it uses a unique index

mysql explain SELECT * from emp where emp_id not in (select emp.emp_id from emp );
Copy the code

In most cases when using a SELECT subquery, the MySQL query optimizer automatically optimizes the subquery to a join table query, so type is not shown as INDEx_subquery, but eq_ref

6. Index_merge: Multiple indexes need to be combined during a query.

Mysql not simulated

7. Ref_or_null: The query optimizer selects this access mode when a field requires both an association condition and a null value.

Mysql not simulated

8. Ref: Non-unique index is used for data lookup

mysql explain select * from emp where  deptno=10;
Copy the code

9. Eq_ref: When searching for a table with a primary key or a unique non-empty index (in fact, the unique index equivalent query type is not eq_ref but const)

mysql explain select * from salgrade s LEFT JOIN emp e on s.emp_id = e.emp_id;
Copy the code

10. Const: Matches a maximum of one data. usually equivalences are queried using primary keys or unique indexes

mysql explain select * from emp where empno = 10;
Copy the code

11. System: A table that has only one row (equal to the system table) is const and does not require disk I/O

mysql explain SELECT * FROM `mysql`.`proxies_priv`;
Copy the code

Five, possible_keys columns

Displays indexes that may apply to this table, one or more. If an index exists on the field involved in the query, it will be listed, but not necessarily used by the query.

Six, the key columns

If the index is null, the index is not used. If the overwrite index is used in the query, the index overlaps with the SELECT field in the query.

Seven, key_len columns

Represents the number of bytes used in the index. The length of the index used in the query can be calculated by key_len, as short as possible without loss of accuracy.

A larger index occupies more storage space. As a result, the number and quantity of I/OS increase, affecting the execution efficiency

Eight, the ref

Displays the column or constant used by the previous table to find the value in the index of the key column record

Nine, rows

Based on the table statistics and index usage, it is important to estimate the number of rows that need to be read to find the required record. How much data is found by the direct response SQL, as little as possible to accomplish its purpose.

Ten, filtered column

A pessimistic estimate of the percentage of the number of records in a table that meet a condition (a WHERE clause or join condition).

Eleven, extra column

Contains additional information.

1. Using filesort: mysql cannot use index to sort

mysql explain select * from emp order by sal;
Copy the code

Create a temporary table to store intermediate results. Delete the temporary table when the query is complete

mysql explain select name,count(*) from emp where deptno = 10 group by name;
Copy the code

Using index: This indicates that the current query overwrites the index and reads data directly from the index instead of accessing the data table. If the using WHERE index is present at the same time, the index is used to perform the lookup of the index key. If not, the surface index is used to read the data, not to do the actual lookup

mysql explain select deptno,count(*) from emp group by deptno limit 10;
Copy the code

Using WHERE: Using where

mysql explain select * from emp where name = 1;
Copy the code

5. Using join buffer

mysql explain select * from emp e left join dept d on e.deptno = d.deptno;
Copy the code

Impossible where: the result of the statement is always false

mysql explain select * from emp where 1=0;
Copy the code

The article has the question can give me the message, the part does not simulate out also can give the opinion

Refer to High-performance MySQL.

Mysql Advanced Series History Review:

1. Infrastructure

2. Storage engine

3. What's the difference between MyISAM and InnoDB

4. How to better select data types in table design

5. How exactly should the paradigm in database design be used

Search
About
mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.