mysql tutorialThe column introduces the execution plan explain and index data structure

Preparation

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

CREATE TABLE `emp` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', `empno` int(11) DEFAULT NULL COMMENT &#39  ;employee number', `ename` varchar(255) DEFAULT NULL COMMENT 'employee's name', `job` varchar(255) DEFAULT NULL COMMENT 'job',  `mgr` varchar(255) DEFAULT NULL COMMENT & # 39; manager's job number & # 39;, `hiredate` date DEFAULT NULL COMMENT & # 39; hire date & # 39;, `sal` double DEFAULT NULL COMMENT '  39; Salary & # 39;, `comm` double DEFAULT NULL COMMENT & # 39; Allowance & # 39;, `deptno` int(11) DEFAULT NULL COMMENT & # 39; department number & # 39;,
   PRIMARY KEY (`id`) USING BTREE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='employee table';CREATE TABLE `dept` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary 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`) USING BTREE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Department table';CREATE TABLE `salgrade` ( `id` int(11) NOT NULL COMMENT 'primary key', `grade`  varchar(255) DEFAULT NULL COMMENT 'grade', `lowsal` varchar(255) DEFAULT NULL COMMENT 'minimum wage', `hisal` varchar(255) DEFAULT NULL COMMENT '  39;Maximum Salary',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Salary grade table';CREATE TABLE `bonus` ( `id` int(11) NOT NULL COMMENT 'primary key', `ename  `varchar(255) DEFAULT NULL COMMENT 'EmployeeName', `job` varchar(255) DEFAULT NULL COMMENT 'job', `sal` double DEFAULT NULL COMMENT '  salary & # 39;, `comm` double DEFAULT NULL COMMENT & # 39; allowances & # 39;,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Bonus table'; 

The follow-up execution plan, query optimization, index optimization and other knowledge drills, based on the above several table to operate.

MySQL execution plan

To perform SQL tuning, you have to know how the SQL statement to be tuned is executed, check the SQL statement Specific execution process to speed up the execution efficiency of SQL statements.

You can use the explain + SQL statement to simulate the optimizer to execute the SQL query statement, so as to know how MySQL processes the SQL statement.

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

explain output format

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 | NULL | NULL | NULL | 1 | 100.00 |
 +----+-------------+-------+------------+------+--  -------------+------+---------+------+------+-----  -----+-------+ 

Explanation of fields such as id, select_type:

id

The sequence number of the select query contains a set of numbers, indicating the order in which the select clause or the 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 | NULL | NULL | NULL | 1 | 100.00 | NULL |
 | 1 | SIMPLE | d | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
 1 | SIMPLE | sg | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
 +----+-------------+-------+------------+------+--  -------------+------+---------+------+------+-----  -----+-------------------------------------------  --------+ 

Execute this query with explain, the id serial number is 1, then the execution order of MySQL is from top to bottom implemented.

• If the id is different, if it is a subquery, the serial number of the id will increase. The larger the id value, the higher the priority, and the earlier it will be executed

mysql> explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALEDept');
 +----+--------------+-------------+------------+--  ----+---------------+------+---------+------+-----  -+----------+-------------------------------------  ---------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
 +----+--------------+-------------+------------+--  ----+---------------+------+---------+------+-----  -+----------+-------------------------------------  ---------------+| 1 | SIMPLE |  | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | NULL |
 | 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where; Using join buffer (Block Nested Loop) |
 | 2 | MATERIALIZED | d | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
 +----+--------------+-------------+------------+--  ----+---------------+------+---------+------+-----  -+----------+-------------------------------------  ---------------+ 

The execution sequence of this example is to execute the id as 2 first, and then execute id is 1.

• The same and different ids exist at the same time: the same can be considered as a group, which is executed sequentially from top to bottom. Among all groups, the greater the id value, the priority The higher it is, the sooner it will be executed

Or the above example, first execute the id of 2, and then execute from top to bottom in order id is 1.

select_type

It is mainly used to distinguish the type of query, whether it is a normal query or a joint query Or a subquery.

• SIMPLE simple query, does not contain subquery and union

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 | NULL | NULL | NULL | 3 | 100.00 | NULL |
 +----+-------------+-------+------------+------+--  -------------+------+---------+------+------+-----  -----+-------+ 

• primary If the query contains any complex subqueries, the outermost query is marked as Primary
• union If the second select appears after union, it is marked as union

mysql> 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 |
 +----+--------------+------------+------------+---  ---+---------------+------+---------+------+------  +----------+-----------------+ 

The select_type of this statement contains primary and union

• dependent union are similar to union, where depentent means union or union all The result of the union will be affected by the external table
• union result The select to get the result from the union table
• dependent 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_len | ref | rows | filtered |
 +----+--------------------+------------+----------  --+------+---------------+------+---------+------+  ------+----------+-----------------+| 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 |
 +----+--------------------+------------+----------  --+------+---------------+------+---------+------+  ------+----------+-----------------+ 

This SQL execution containsPRIMARY, DEPENDENT SUBQUERY, DEPENDENT UNION, and UNION RESULT

• subquery Include subqueries in the select or where list

Example:

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 | NULL | NULL | NULL | 4 | 33.33 |
 | 2 | SUBQUERY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
 +----+-------------+-------+------------+------+--  -------------+------+---------+------+------+-----  -----+-------------+ 

• DERIVED out of the from clauseERE primary_key_part1=1 AND primary_key_part2=2;

  For example:

  mysql> explain select * from emp where id =  1;
   +----+-------------+-------+------------+-------+-  --------------+---------+---------+-------+------+  ----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
   +----+-------------+-------+------------+-------+-  --------------+---------+---------+-------+------+  ----------+-------+| 1 | SIMPLE | emp | NULL | const | PRIMARY |
   +----+-------------+-------+------------+-------+-  --------------+---------+---------+-------+------+  ----------+-------+ 

  • system The table has only one row of records (equal to the system table), which is const The special case of the type does not usually appear

  possible_keys

  The display may be applied in this table The index, one or more, if there is an index on the field involved in the query, the index will be listed, but not necessarily actually used by the query

  Note:

  There are two head pointers on the B+Tree, one pointing to the root node and the other pointing to the leaf node with the smallest keyword, and there is a chain between all leaf nodes (ie data nodes) ring structure.

  Therefore, two search operations can be performed on B+Tree: one is range search and paging search for the primary key, and the other is random search starting from the root node.

  Because the leaf nodes of the B+ tree only store data, and the root node only stores keys, let’s calculate that even if there are only 3 layers of B+ trees, tens of millions of levels of data can be produced.

  Technology (zhuang) technique (b) nouns you have to know

  Suppose there is such a table as follows, where id is Primary key:

  mysql> select * from stu;
   +------+---------+------+| id | name | age |
   +------+---------+------+| 1 | Jack Ma | 18 |
   | 2 | Pony | 19 |
   +------+---------+------+ 

  Return form

  We build ordinary indexes on ordinary columns, and now we will check:

  select * from stu where name='Pony'; 
  select * from stu where name='Pony'; 
  Because name has built an index, when querying, first find the B+ tree of name, and find the primary key id After that, find the B+ tree of the primary key id to find the entire row of records.  
  This will eventually return to the primary key to find the B+ tree, this is the back table.  
  Covering index
  If this is the query:
  mysql>  select id from stu where name='Pony'; 
  There is no return form, because the primary key id is found directly, and the return is over, no need to search again  other.  
  If there is no return table, it is called covering index.  
  Leftmost match
  Create a combination with two fields name and age  Index (name, age), and then there is such a query:
  select * from stu where name=? and age=? 
    At this time, query according to the composite index (name, age), first match name, and then match age, if the query becomes like this:
  select * from stu where age=? 
  If you do not search by name directly, the index will not take effect at this time  , that is, it will not be queried according to the index --- this is the leftmost match principle.  
  I have to search by age when I join, and I need an index to optimize it?  You can do this:
  
  (recommended) Change the order of the combined index (name, age) and build an (age, name) index
  Or directly put agefield
  
  
  Index pushdown
  
  It may also be called predicate pushdown  .  .  .  
  
  select t1.name,t2.name from t1 join t2 on t1.id=t2.id 
  t1  There are 10 records and t2 has 20 records.  
  Let's guess, this can be executed in this way:
  First t1, t2 are merged by id (20 after merging), and then check t1.name, t2  .name
  Or:
  First find out t1.name, t2.name, and then associate according to id
   If you don’t use the index condition push-down optimization, MySQL can only query all the rows after t1 and t2 are merged according to the index, and then compare whether all the conditions are met in turn.  
  When the index condition pushdown optimization technology is used, the data stored in the index can be used to judge whether the data corresponding to the current index meets the conditions. Only the data that meets the conditions can query the entire row of data.  
  Summary
  
  
  Explain In order to know how to optimize the execution of SQL statements, you need to check the specific execution process of SQL statements to speed up  Execution efficiency of SQL statements.  
  Index advantages and uses.  
  The data structure used by the index is B+ tree.  
  Back to table, covering index, leftmost match and index pushdown.  
  
  
  More related free learning recommendations: mysql tutorial(video)
  
  The above is the detailed content of MySQL execution plan explain and index data structure deduction. For more, please pay attention to other related articles on 1024programmer.com!  
  Free learning recommendation: mysql tutorial(video)
  
  The above is the MySQL execution plan explain and index data structure deduction  For more details, please pay attention to other related articles on 1024programmer.com!