任务目的

针对之前进行的多种查询，分析执行计划与时间消耗，通过改写SQL语句、改变多表驱动关系、创建索引、创建虚拟列与函数索引等方式，针对各条SQL语句进行优化。查看优化后的SQL语句执行计划与执行时间，与优化之前作对比。

任务步骤

1.优化单表查询

针对单表简单查询的优化，创建索引代替全表扫描。

1）查询id为9900的员工的工资。查看执行计划与时间消耗。当前是全表扫描，主要时间消耗在Sending data（收集获取和发送数据）。扫描约10000条数据，符合条件的只有1条，扫描的实际效率较低（此处为便捷，仅插入了10000条数据，实际生产环境千万行以上时性能问题会更加明显）。

SELECT id,salary FROM empsalary WHERE id=9900;
SHOW PROFILES; 
SHOW PROFILE; 
EXPLAIN SELECT id,salary FROM empsalary WHERE id=9900;

2）针对被查询的id列创建索引。期望通过索引快速定位数据位置，代替全表扫描。

CREATE INDEX i1 ON empsalary(id);

3）查看索引优化后的执行计划与时间消耗，此时总时间消耗为0.00046050秒，低于之前的时间消耗。其中Sending data项目时间消耗较少，因为用索引能够快速定位数据，不再需要全表扫描大量无用数据。执行计划中type显示为ref，表示使用了索引查询该表。

SELECT id,salary FROM empsalary WHERE id=9900;
SHOW PROFILES; 
SHOW PROFILE; 
EXPLAIN SELECT id,salary FROM empsalary WHERE id=9900;

2.优化函数查询

优化结合函数转换的单表查询。使用虚拟列，使索引对函数查询生效。

1）查询工号前三位为999的员工的工号和工资。需要使用substr对id进行截取，从第1位起，截取3位。上一部已经对id列创建了索引，查看此时的执行计划与消耗时间。

SELECT id,salary FROM empsalary WHERE substr(id,1,3)=999;
SHOW PROFILES;
EXPLAIN SELECT id,salary FROM empsalary WHERE substr(id,1,3)=999;

此时发现，虽然id列有索引，查询的也是id列，但最后一步执行计划显示的type仍是ALL，也就是没有通过索引查询，仍然是全表扫描。这是因为索引是对id列原始数据进行了提取和索引树的建立，但此次查询使用了substr函数处理id列，因此已经不再是id原始数据，和索引无法匹配，不能通过索引进行查询。

2）通过虚拟列方式，对函数查询建立索引，优化查询。此时相当于对所有id都提前执行了用到的substr函数，再对函数的结果统一进行索引。后续再使用函数查询到该数据时，就可以直接索引得到想要的数据的位置。

ALTER TABLE empsalary add column id02 int GENERATED ALWAYS AS (substr(id,1,3)) VIRTUAL;
CREATE index i2 ON empsalary(id02);

3）重新执行SQL语句，查看执行计划与时间消耗。可以看到最后一步执行计划中type已经显示为ref，说明使用了索引。总时间消耗0.00064525秒，也有显著的改善。

SELECT id,salary FROM empsalary WHERE substr(id,1,3)=999;
SHOW PROFILES;
EXPLAIN SELECT id,salary FROM empsalary WHERE substr(id,1,3)=999;

3.优化多表连接查询

查询9900号员工的领导（即部门经理）是谁。需要通过员工部门表和部门经理表联合查询。查看执行计划和消耗时间。

1）编写语句进行查询。查看执行计划与消耗时间，可以看到，两张表select_type都是simple，说明是简单的查询关系，不涉及复杂的循环子查询等。两张表type都是all，说明是全表扫描了两张表。两张表的大小分别是10行和10000行左右（执行计划中为估计的统计值，不完全精确），因此10000行的全表扫描可以用索引代替。

SELECT a.id,b.id FROM leader a JOIN empdepartment b ON a.department=b.department AND b.id=9900;
SHOW PROFILES;
EXPLAIN SELECT a.id,b.id FROM leader a JOIN empdepartment b ON a.department=b.department AND b.id=9900;

SELECT a.id,b.id FROM leader a JOIN empdepartment b ON a.department=b.department AND b.id=9900;
SHOW PROFILE;

2）在大表empdepartment被查询的department和id列上分别创建索引，代替全表扫描。

CREATE INDEX I3 ON empdepartment(id);
CREATE INDEX I4 ON empdepartment(department);

3）重新执行查询，查看优化后的执行计划与时间消耗。

SELECT a.id,b.id FROM leader a JOIN empdepartment b ON a.department=b.department AND b.id=9900;
SHOW PROFILES;
EXPLAIN SELECT a.id,b.id FROM leader a JOIN empdepartment b ON a.department=b.department AND b.id=9900;

可以看到消耗时间为0.00047075秒，好于之前的0.00470300秒。执行计划中type列显示为ref，表示使用了索引。后面也可以看到用到了刚刚创建的索引。

4.改写语句优化多表查询

查询每个部门经理的工资。其中部门经理信息记录在leader表中，而工资记录在empsalary表中，需要关联查询。

1）执行语句，查看时间消耗。此处使用EXISTS方式进行关联查询，查看执行计划与消耗时间。之前已经尝试查看过，产生了大量的executing（执行）和Sending data，一共显示了100行。

SELECT id,salary FROM empsalary a WHERE EXISTS (SELECT 1 FROM leader b WHERE a.id=b.id);
SHOW PROFILES;
SHOW PROFILE;

2）查看该语句的执行计划。

EXPLAIN SELECT id,salary FROM empsalary a WHERE EXISTS (SELECT 1 FROM leader b WHERE a.id=b.id);

首先关注select_type一栏。

a表（empsalary员工工资表）select_type为PRIMARY，表示这张表属于查询中的最外层的表。也就是先查询了b表（leader表），才进一步查询了a表。

b表leader表select_type为DEPENDENT SUBQUERY，表示将先将外层的表数据查询出来，再用查询到的结果，逐一进行子查询。

此时很明显，多表查询驱动顺序先b后a，每从b表拿到一个数据，就循环查询a表一次，一般建议小表驱动大表，否则会产生大量的循环，影响性能。

结合上一步的时间统计来看，内外部表的驱动顺序并不合适，产生了很多次循环的查询。可以在不影响结果的前提下，改写SQL语句，用其他方法获取数据进行优化。

3）改写该语句，改用IN实现该效果。此时使用IN加子查询的方式，会先获取子查询的结果，再用子查询的结果回到前面来进行查询，相当于子查询的表驱动前面主查询的表。可以看到查询的结果没有变，两语句等价。

SELECT id,salary FROM empsalary WHERE id IN (SELECT id FROM leader);

SHOW PROFILES;
SHOW PROFILE;

优化后执行的步骤比之前更简洁。时间消耗仅为0.00053025秒，远低于之前的0.12989825秒。

查看执行计划，可以看到此时执行计划步骤稍多了一些：先对leader表做子查询（最后一行），将子查询结果返回到第1行，与第2行的empsalary表联合进行查询。也就是先子查询，再主查询，小表驱动大表。

EXPLAIN SELECT id,salary FROM empsalary WHERE id IN (SELECT id FROM leader);