MySQL Advanced Knowledge (v)--Index analysis

Preface: The Knowledge of explain (executive plan) has been studied, and the index is optimized and analyzed by explain.

0. Preparation

First create three tables: tb_emp (Staff table), tb_dept (departmental table) and Tb_desc (description table)

1) tb_emp table.

DROP TABLE IF EXISTS' tb_emp ';CREATE TABLE' tb_emp ' (' ID ')int( One) not NULLauto_increment, ' username 'varchar( -) not NULL,PRIMARY KEY(' id ')) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- ----------------------------INSERT  into' Tb_emp ' (username)VALUES('Tom','1');INSERT  into' Tb_emp ' (username)VALUES('Jack','1');INSERT  into' Tb_emp ' (username)VALUES('Mary','2');INSERT  into' Tb_emp ' (username)VALUES('Rose','3');

2) tb_dept table.

DROP TABLE IF EXISTS' tb_dept ';CREATE TABLE' tb_dept ' (' ID ')int( One) not NULLauto_increment, ' name 'varchar( -) not NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- ----------------------------INSERT  into' Tb_dept ' (name)VALUES('General Department');INSERT  into' Tb_dept ' (name)VALUES('Research');INSERT  into' Tb_dept ' (name)VALUES('Test');INSERT  into' Tb_dept ' (name)VALUES('President');

3) Tb_desc table.

DROP TABLE IF EXISTS' Tb_desc ';CREATE TABLE' Tb_desc ' (' ID ')int( One) not NULLauto_increment, ' Empid 'int( One)DEFAULT NULL, ' DeptID 'int( One)DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- ----------------------------INSERT  into' Tb_desc ' (Empid,deptid)VALUES(1,1);INSERT  into' Tb_desc ' (Empid,deptid)VALUES(2,1);INSERT  into' Tb_desc ' (Empid,deptid)VALUES(3,2);INSERT  into' Tb_desc ' (Empid,deptid)VALUES(4,3);

Note: The employee table is not directly associated with the departmental table here, and is associated with the third table (the Description table), mainly for the purpose of the join analysis.

1.left Join

#1. Execute the query first.

#2. Analysis by explain.

Analysis: From the explain execution results you can see that both tables use full table scan (all), and the join connection cache is used in the Tb_desc table and needs to be optimized. But how to optimize? Do you create an index on the left table or the right table? Because the left table is all, the right table should be indexed.

#3. The right table creates an index.

With explain execution, you can see that after creating the index, you get a good result. (type=ref,extra=using index).

Conclusion: In the case of left join, the index should be created in the right table (TB_DESC).

2.right Join

Using the example of the left join above, we swap the two table positions directly and change the left join to the right join.

Analysis:

In contrast to the left join, you can get the following conclusions:

#1. Under the left JOIN, the tb_emp is executed first, Type=all, because the left table is all in the left connection, so we create an index on the tb_desc (right table) , which results in a more desirable effect.

#2. Under the right join (we swapped the positions of Tb_emp and TB_DESC), the execution order: the →tb_desc (left table), Tb_emp. Right table Type=all, because the right connection case right table is all, so in the left table (Tb_desc, we exchanged the position) to create the index , the effect is certainly the same as the same as the.

Summary

Left joins: The right table creates an index.

Right join: Left table CREATE index.

Précis-writers: left and right outer joins, index opposite build (left: Table is built, starboard: Table built).

By Shawn chen,2018.6.23 Day, night.

Related content

MySQL advanced Knowledge (a)--basic

MySQL advanced Knowledge (ii)--join query

MySQL Advanced Knowledge (iii)--index

MySQL advanced Knowledge (iv)--explain

MySQL Advanced Knowledge (v)--Index analysis