Shulou(Shulou.com)06/01 Report--

Understand what is the deduction process of MySQL execution plan explain and index data structures? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

First, build the database table, the MySQL table for the demonstration, and the table statement:

CREATE TABLE `emp` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'key', `empno` int (11) DEFAULT NULL COMMENT 'employee ID', `ename` varchar (255) DEFAULT NULL COMMENT 'employee name', `job`varchar (255) DEFAULT NULL COMMENT 'job', `mgr`varchar (255) DEFAULT NULL COMMENT 'manager's job number', `hiredate`date DEFAULT NULL COMMENT 'employment date', `sal`double DEFAULT NULL COMMENT 'salary', `comm`double DEFAULT NULL COMMENT 'allowance' `deptno` int (11) DEFAULT NULL COMMENT 'Department number', PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' employee Table' CREATE TABLE `dept` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'key', `deptno` int (11) DEFAULT NULL COMMENT 'department number', `dname` varchar (255) DEFAULT NULL COMMENT 'department name', `loc`varchar (255) DEFAULT NULL COMMENT 'address', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' department table' CREATE TABLE `salgrade` (`id`salgrade`) NOT NULL COMMENT 'key', `grade`varchar 'DEFAULT NULL COMMENT' grade', `lowsal` varchar 'DEFAULT NULL COMMENT' minimum wage', `hisal` varchar 'DEFAULT NULL COMMENT' maximum wage', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' wage scale' CREATE TABLE `bonus` (`id` int (11) NOT NULL COMMENT 'key', `ename` varchar (255) DEFAULT NULL COMMENT 'employee name', `job` varchar (255) DEFAULT NULL COMMENT 'job', `sal`double DEFAULT NULL COMMENT 'salary', `comm`double DEFAULT NULL COMMENT 'allowance', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' bonus table'; copy code

Follow-up execution plan, query optimization, index optimization and other knowledge drills, based on the above tables to operate.

MySQL execution plan

To do SQL tuning, you need to know how the SQL statement to be tuned is executed. Look at the specific execution process of the SQL statement to speed up the execution of the SQL statement.

You can use explain + SQL statements to simulate the optimizer's execution of SQL query statements to know how MySQL handles SQL statements.

About explain, you can see the introduction on the official website.

The output format of explain is mysql > explain select * from emp +-- + | id | select_type | Table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | emp | NULL | ALL | NULL | 1 | 100.00 | NULL | + -+-+ copy code

Explanation of fields such as field id,select_type:

ColumnMeaningidThe SELECT identifier (the SELECT identifier) select_typeThe SELECT type (the SELECT type) tableThe table for the output row (output the table name of the row) partitionsThe matching partitions (matching partition) typeThe join type (join type) possible_keysThe possible indexes to choose (possible index selection) keyThe index actually chosen (actual selected index) key_lenThe length of the chosen key (length of selected key) refThe columns compared to the index (column compared to index) rowsEstimate of rows to Be examined (estimated rows checked) filteredPercentage of rows filtered by table condition (percentage of rows filtered by table criteria) extraAdditional information (additional information)

Id

The sequence number of a select query that contains a set of numbers indicating the order in which the select clause or operation table is executed in the query.

Id numbers are divided into three categories:

If the id is 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.lowsal and sg.hisal +- -- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+- -+ | 1 | SIMPLE | e | NULL | ALL | NULL | 1 | 100.00 | NULL | 1 | SIMPLE | d | NULL | ALL | NULL | 1 | 100.00 | Using where Using join buffer (Block Nested Loop) | | 1 | SIMPLE | sg | NULL | ALL | NULL | 1 | 100.00 | Using where Using join buffer (Block Nested Loop) | +-- +- -+ copy the code

This query is executed with explain, and the id sequence number is 1, so the order of MySQL execution is from top to bottom.

If the id is different, if it is a subquery, the sequence number of the id will be incremented. The higher the id value, the higher the priority, the more mysql > explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALEDept') will be executed first. +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- +-- -+ | 1 | SIMPLE | | NULL | ALL | NULL | 100.00 | NULL | 1 | SIMPLE | e | NULL | ALL | NULL | 2 | 50.00 | Using where Using join buffer (Block Nested Loop) | | 2 | MATERIALIZED | d | NULL | ALL | NULL | 1 | 100.00 | Using where | +- -+ copy the code

The execution order of this example is to execute id 2 and then id 1.

The same and different id exist at the same time: the same group can be considered as a group, which is executed sequentially from top to bottom. In all groups, the higher the id value is, the higher the priority is, and the first execution is.

Again in the above example, execute id 2 first, and then execute id 1 sequentially from top to bottom.

Select_type

It is mainly used to distinguish the type of query, whether it is a general query, a federated query or a subquery.

Select_type ValueJSON NameMeaningSIMPLENoneSimple SELECT (not using UNION or subqueries) PRIMARYNoneOutermost SELECTUNIONNoneSecond or later SELECT statement in a UNIONDEPENDENT UNIONdependent (true) Second or later SELECT statement in a UNION, dependent on outer queryUNION RESULTunion_resultResult of a UNION.SUBQUERYNoneFirst SELECT in subqueryDEPENDENT SUBQUERYdependent (true) First SELECT in subquery, dependent on outer queryDERIVEDNoneDerived tableMATERIALIZEDmaterialized_from_subqueryMaterialized subqueryUNCACHEABLE SUBQUERYcacheable (false) A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer queryUNCACHEABLE UNIONcacheable (false) The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) SIMPLE simple query Does not include subqueries and unionmysql > explain select * from emp +-- + | id | select_type | Table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | emp | NULL | ALL | NULL | 3 | 100.00 | NULL | + -+-+ copy code if the primary query contains any complex subqueries The outermost query is marked Primaryunion. If the second select appears after union, it is marked unionmysql > explain select * from emp where deptno = 1001 union select * from emp where sal.

< 5000;+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where || 2 | UNION | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 33.33 | Using where || NULL | UNION RESULT | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+复制代码 这条语句的select_type包含了primary和union dependent union 跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响union result 从union表获取结果的selectdependent subquery subquery的子查询要受到外部表查询的影响mysql>

Explain select * from emp e where e.empno in (select empno from emp where deptno = 1001 union select empno from emp where sal

< 5000);+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| 1 | PRIMARY | e | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where || 2 | DEPENDENT SUBQUERY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where || 3 | DEPENDENT UNION | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where || NULL | UNION RESULT | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+复制代码 这条SQL执行包含了PRIMARY、DEPENDENT SUBQUERY、DEPENDENT UNION和UNION RESULT subquery 在select或者where列表中包含子查询 举例： mysql>

Explain select * from emp where sal > (select avg (sal) from emp) +-+ | Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-- + -+ | 1 | PRIMARY | emp | NULL | ALL | NULL | 4 | 33.33 | Using where | 2 | SUBQUERY | emp | NULL | ALL | NULL | 4 | 100.00 | NULL | +- -+ copy the subquery that appears in the DERIVED from clause of the code Also known as derived table MATERIALIZED Materialized subquery? UNCACHEABLE SUBQUERY indicates that results using subqueries cannot be cached

For example:

Mysql > explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size) +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ | 1 | PRIMARY | emp | NULL | ALL | NULL | 4 | 100.00 | Using where | | 2 | UNCACHEABLE SUBQUERY | emp | NULL | ALL | NULL | NULL | | | NULL | NULL | 4 | 25.00 | Using where | +-+ | -+-+ copy code uncacheable union indicates that the query result of union cannot be cached

Table

Which table, table name, or alias the corresponding row is accessing, may be a temporary table or a union merge result set.

If it is a specific table name, it means that the data is obtained from the actual physical table. Of course, it can also be that the alias table name of the table is in the form of derivedN, indicating that the derived table generated by the query with id for N is in the form of union result. When there is union result, the table name is in the form of union N1, and N2 represents the id participating in union.

Type

Type shows the access type, and the access type indicates how I access our data. The easiest thing to think of is full table scanning, directly traversing a table to find the data we need, which is very inefficient.

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

System > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

In general, you have to ensure that the query reaches at least range level, preferably ref.

All full table scan. Generally speaking, if there is such a sql statement and the amount of data is large, then it needs to be optimized.

In general, you can avoid ALL by adding indexes

Index full index scanning is more efficient than all. There are two main situations: one is to overwrite the index in the current query, that is, the data we need can be obtained in the index. One is to use the index for sorting, so as to avoid data reordering. Range means that the scope is limited when using the index query, and the query is carried out within a specified range, thus avoiding the full index scan of index. Applicable operators: =, >, > =, select * from stu +-+ | id | name | age | +-+ | 1 | Jack Ma | 18 | | 2 | Pony | 19 | +-+ copy the code back to the table

We build a general index on the ordinary column, and at this time let's look up:

Select * from stu where name='Pony'; copy code

Because name has built an index, when querying, first look for the B+ tree of name, find the primary key id, and then find the B+ tree of the primary key id, so as to find the whole row of records.

This will eventually go back to the primary key to find the B + tree, and this is the return table.

Overlay index

If this is the query:

Mysql > select id from stu where name='Pony'; copy code

There is no return to the table, because directly find the primary key id, return to the end, do not need to find anything else.

If there is no return to the table, it is called overwriting index.

Leftmost match

Let's build a combined index (name, age) with two fields name and age, and then there is a query like this:

Select * from stu where name=? And age=? Copy the code

At this time, according to the composite index (name, age) query, first match name, and then match age, if the query looks like this:

Select * from stu where age=? Copy the code

If you simply do not press name, the index will not take effect, that is, it will not be queried according to the index-this is the leftmost matching principle.

To join me, I have to press age to check, but also have an index to optimize it? You can do this:

(recommended) change the order of the composite index (name, age), build (age, name) the index, or directly create a separate index for the age field.

It may also be called predicate push-down.

Select t1.name from t2.name T1 join T2 on t1.id=t2.id copy the code

T1 has 10 records and T2 has 20 records.

Let's guess, this is either done in this way:

First T1 ~ T2, press id to merge (20 items after the merger), and then check t _ 1 _ name _ t2.name

Or:

First find out the t1.name _ name _ t2.name, and then associate it according to id

If you do not use the index condition push-down optimization, MySQL can only query all the rows merged by T1 and T2 based on the index, and then compare whether all the conditions are met in turn.

When the index condition push-down optimization technology is used, the data stored in the index can be used to determine whether the data corresponding to the current index meets the conditions, and only the qualified data will query the whole row of data.

Summary Explain in order to know how to optimize the execution of SQL statements, we need to look at the specific execution process of SQL statements in order to speed up the execution efficiency of SQL statements. The advantages and usefulness of index. The data structure used in the index is B+ tree. Return to the table, overwrite the index, leftmost match and index push down. Thank you for reading! After reading the above, do you have a general understanding of the MySQL execution plan explain and index data structure deduction process? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.