SQL数据库不用SQL语句能显示全表的内容_十二 MySQL数据库SQL深入讲解

如果想要数据，请关注头条号，私信回复“mysql”

技能目标：

· 学会使用explain分析SQL

· 避免索引失效

· 设计高可用的索引

· 了解全文索引

· 优化join

12.1案例分析

12.1.1 案例概述

数据库不仅存放数据，同时还需要读取数据。在关系型数据库中读取数据采用的一般都是SQL语句(结构化查询语言)。SQL语句的编写方法会对MySQL服务器造成性能上的开销，而不同的SQL语句编写方法，对数据库性能开销也大不相同。通常情况下一个不经意SQL语句可能会造成MySQL数据库出现死锁等问题，具体的情况待会在下面示例会有讲解。在工作中，一些开发人员对于数据库的理解层次有限，往往仅根据需求来编写SQL语句，完全不考虑服务器、数据库等性能等问题，这样就会出现很多的MySql慢查询语句，增加了服务器CPU、IO的负载，严重时会造成数据库宕机等问题，此时MySql DB的作用就体现出来了。在保证数据库性能良好的前提下，找出有问题的SQL并调整优化SQL语句来达到服务器性能的最优，成为本章案例的研究重点。

12.1.2 案列前置知识点

简单的MySQL命令操作，比如启动MySQL，进入MySQL界面。会一些基本的SQL语句编写，比如GROUP BY、INNER JOIN等。对于索引相关的知识有基本认知。

1. MySQL的函数介绍

关于SQL的编写，具体可以参考MySQL手册来查找相关的函数等信息。比较常用的有：操作符、聚合函数、日期函数、数学函数等。在此不过多介绍，有兴趣的同学可以自行参考下官方手册。官方手册：https://dev.mysql.com/doc/refman/5.6/en/

2. 索引介绍

MySQL的索引有两大类：BTREE索引和哈希索引两大类。其中哈希索引存在于HEAP和MEMORY等不常用的存储引擎中，固在此案例中不做讨论。BTREE索引又可以分为主键、唯一索引、普通索引和联合索引等。

12.1.3 案例环境

1.本案例环境

本案例采用的操作系统是CentOS7.3，MySQL版本是5.6.36。同时目前用于生产的基本上都是InnoDB引擎，所以本文所有的案例都是基于InnoDB引擎来实现的。

表12-1系统环境

使用MySQL的version()函数可以获取本地机当前使用的MySQL版本，执行结果如图12.1所示。

图12.1 MySQL版本

2. 案例需求

SQL语句是开发人员必备的技术能力之一，写一个SQL语句很简单，但是写出一个高效的SQL语句还是需要具备一定的知识、能力和经验。本次案例将通过分析造成慢查询SQL语句的原因，并如何通过技术手段来避免低效的SQL语句的出现。

3. 案例实现思路

SQL语句的优化主要从以下两个方面入手。

1) 减少IO次数

MySQL基本上采用的都是innodb表，其数据是存储在磁盘之中。而磁盘的IO是非常耗时的。所以如何减少IO次数成为一个重要的思路。比如减少扫描行数、减少返回不必要的列值等方式。

2 ) 降低CPU计算

在MySQL中经常使用到聚合函数group by、排序函数order by、去重函数distincit等。这些函数对于数据库服务器的CPU是非常大的性能消耗，其涉及到建立临时表等步骤。如何优化这一块也是SQL优化的一个重点。

12.2案例实施

12.2.1 准备工作

下面是案例实施前需要完成的准备工作：

1)安装一个MySQL版本为5.6.36数据库

2)导入数据，将数据上传到/root目录下

tar zxvf test_sql.tar.gz

mysql> create database fymap;

mysql> create database um_kmg;

mysql -u root -p123456 fymap < fymap.sql

mysql -u root -p123456 um_kmg < um_kmg.sql

本次案例使用到两个数据库fymap和um_kmg。其中fymap数据库含有表demo_or_union、gwda_wgeg、gwgid_time、students、map_routes_xxxx、t1、t2。um_kmg数据库含有表goms_flight、goms_flight_runway_log。它们的表结构和索引分部如表12-2到表12-10所示。

表12-2 demo_or_union表结构

表12-3 gwda_wgeg表结构

表12-4 gwgid_time表结构

表12-5 map_routes_xxxx表结构

表12-6 students表结构

表12-7 t1表结构

表12-8 t2表结构

表12-9 goms_flight表结构

表12-10 goms_flight_runway_log 表结构

关于各个表的数据行数和文件大小见表12-11.

表12-11 各个表信息

从表12-11中能够看出，虽然只有3张表超过百万数据。但是其他表所带来的SQL语句优化的目的也是能够达到的。同时案例中同一个表出现了反复使用的情况，这是为了让同学们在学习过程中，能否更好的观察不同索引所带来的性能影响。

12.2.2 SQL语句优化

1 执行计划

在写一个SQL语句之后，千万别急于执行，一定要在一个语句之前添加explain关键词，来看看SQL语句的执行计划。如图12.12所示：。

mysql> explain select dep,arr from demo_or_union limit 40;

图12.2 explain结果图

从图12.12能明显看出explain结果分为以下几个列：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。下面对这些列的含义进行详细说明。

1) id列：表示SELECT的查询序列号。

2) select_type列：表示SELECT类型，包括了SIMPLE(简单SELECT)、PRIMARY(最外面的SELECT)、UNION(UNION中的第二个或后面的SELECT语句)、DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)、UNION RESULT(UNION的结果)、SUBQUERY(子查询中的第一个SELECT)、DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)、DERIVED等(导出表的SELECT)。

3) table列：表示使用的表名。如果有别名，此处显示别名。

4) type列：表示的是联接类型，每个类型带来的性能是不一样的。此列是重要的SQL调优指标之一。下面列出性能由高到低的type列值：

--1 system：表仅有一行。这是const联接类型的一个特例。

--2 const：表最多有一个匹配行，它将在查询开始时被读取。它与system的区别在于，system一般出现在系统表中。

--3 eq_ref：从该表中读取一行。与const类型区别在于，const用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY；而eq_ref是用于=操作符比较的带索引的列。

--4 ref：所有有匹配索引值的行将从表中读取。如果联接不能基于关键字选择单个行的话，则使用ref。ref可以用于使用=或<=>操作符的带索引的列。

--5 ref_or_null：该联接类型如同ref，与ref区别在于：执行该查询时会有Null判断。

--6 index_merge：使用了联合索引。

--7 unique_subquery：一种通过索引查找函数来替换子查询的查询方式。只适合子查询结果返回的是唯一索引或者主键。

--8 index_subquery：类似于unique_subquery。但只适合子查询结果返回的不是唯一索引和主键。

--9 range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、、BETWEEN或者IN操作符，经常出现该值。

--10 index：该联接类型与ALL相同，除了只有索引树被扫描。

--11 ALL：表示全表扫描。不能出现该值。

5) possible_keys列：表示查询语句中通过哪些索引可以找出结果。

6) key列：表示MySQL优化器选择的索引名。此列是重要的SQL调优指标之一。

7) key_len列：表示MySQL优化器选择的索引长度。对于联合索引来说，这个参数很重要，能够看出联合索引具体采用了哪几个索引字段。

8) ref列：使用哪个列或常数与key一起从表中选择行。

9) rows列：表示MySQL优化器需要在表中扫描的行数。此列是重要的SQL调优指标之一。注意该值是个预估值。

10) Extra列：MySQL解决查询的详细详细。此列是重要的SQL调优指标之一。。有以下的列值。

--1 Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。

--2 Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。

--3 Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。此值的出现一般在order by语句之中，需要考虑优化。

--4 Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

--5 Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。此值的出现一般在group by、union等语句之中，需要考虑优化。

--6 Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。

--7 Using sort_union(...), Using union(...), Using intersect(...)：这些函数的出现说明MySQL建议采用联合索引来优化此次查询

在查看执行计划之中，一定要注意type、key、rows、Extra列的值。这些是优化的重要指标。思路是：优化type列值、不要让key列值为null，减少rows列值，Extra列不要出现Using temporary、Using filesort。下面就来详细聊聊如何具体优化SQL。

2 SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了；

select id from t where num between 1 and 3;

如果实在无法使用between来完成，比如in的字段都是字符串，此时可以对in中的具体值按照顺序进行排序。在MySQL中，如果处理的是多个字符串，那么优化器会对这些字符串进行排序。通过程序排序，来减少优化器的处理步骤也是一种优化思路。但是一定要控制in中值的数量，如果真的太大，就建立一个临时表来存储这些值，通过inner join等手段来进行关联处理。

3 SELECT语句必须指明字段名称

SELECT * 增加很多不必要的消耗，比如cpu、io、内存、网络带宽等；增加了使用覆盖索引的可能性；当表结构发生改变时，前端也需要更新。所以要求直接在select后面接上字段名。

4 当只需要一条数据的时候，使用limit 1

在讲解执行计划中说明了type列的含义。此处使用limit 1为了使EXPLAIN中type列达到const类型。

5 如果排序字段没有用到索引，就尽量少排序

因为排序，Extra列会出现Using temporary、Using filesor值。此处的优化会在索引章节详细说明。(见12.2.3中order by、group by时索引的选择)

6 如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，至少有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替"or"会得到更好的效果。

mysql> show index from gwda_wgeg;

mysql> explain select mdd,dasj from gwda_wgeg where id='c4ca4238a0b923820dcc509a6f75849b' or cfd='KSG';

图12.3 or查询

从图12.3中能够看出即使id是主键，可cfd列并没有索引。采用了or查询之后，type列的值是ALL，type列的值是null，说明了该SQL查询仍然会全表扫描。如果仅仅在cfd字段添加一个索引，我们来看看执行计划。

mysql> alter table gwda_wgeg add index idx_cfd(cfd);

mysql> explain select mdd,dasj from gwda_wgeg where id='c4ca4238a0b923820dcc509a6f75849b' or cfd='KSG';

图12.4 cfd字段添加索引

发现仍然是全表扫描，要注意的是possible_keys列的值是PRIMARY,idx_cfd，说明SQL是可能会采用到这个索引，只是优化器最终没有选择。我们需要采用union函数来分开查询，来改变SQL语句。

mysql> explain select mdd,dasj from gwda_wgeg where id='c4ca4238a0b923820dcc509a6f75849b' union select mdd,dasj from gwda_wgeg where cfd='KSG';

图12.5 union执行计划

此时，查询语句都使用了索引，执行计划除了出现了Using temporary，其他都很好。但是由于扫描的行数减少很多，此时的执行计划也相较于之前的执行计划有所改善。

7 尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

8 不使用ORDER BY RAND()

select id from `dynamic` order by rand() limit 1000;

可以优化为：

select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nid limit 1000;

注意：如果是采样数据要求严格的，不建议下面的优化，毕竟随机性不如上面的语句。

9 区分in和exists， not in和not exists

select * from 表A where id in (select id from 表B)

这句相当于

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)。如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。提供的数据库里面t1和t2表是存在的。如果想尝试的同学可以先删除再按照下面语句创建。如图12.6所示：

图12.6 not in逻辑问题

从图12.6就能看出，t1表列c1,c2有值(1,2)和(1,3)，t2表列c1,c2有值(1,2)和(1,null)。如果想找出t1表中c2列值3明显不在t2表中c2列，可是采用not in的时候却无法找出。not in有明显的逻辑问题。

请思考：如果两个关联的表数据非常大的时候，如何高效的写出一个替代not exists的SQL语句？(具体答案在文章最后)

10 使用合理的分页方式以提高分页的效率

在网页显示中，常常会伴有翻页。但是随着表数据量的增加，直接使用limit分页查询会越来越慢。比如：

select id,name from product limit 866613, 20

优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法：

select id,name from product where id> 866612 limit 20

注意：此办法适用于主键是自增，或者其他字段是int型，同时还是索引，重复率很低的字段。

11分段查询

在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。如图12.7所示。

mysql> explain select dep,id from map_routes_xxxx where day>='2018-06-21' and day

mysql> explain select dep,id from map_routes_xxxx where day>='2018-06-21' and day

图12.7 分段查询

从图12.7就能看出，扫描行数明显减少，而且SQL语句执行中没有采用索引idx_day。这是因为MySQL优化器根据possible_kyes来预估扫描行数，如果扫描行数过多，即使采用索引，MySQL开销仍然很大，此时优化器会放弃索引，选择全表扫描；但是下面的SQL语句就采用了索引，这是由于优化器判断出采用索引会减少开销。虽然分段查询需要多次执行SQL语句，但是可以每次查询可以扫描少量的行数，减少服务器负载、缩短读锁的时间、降低出现死锁的情况。对于一些企业，为了保证服务器的性能和安全，SQL达到一定时间没有返回结果，会自动的kill掉该SQL语句，用这种方法可以有效的避免SQL被kill。

12.2.3 索引技巧

在SQL语句执行过程中，经常遇到操作的表结构有索引，但MySQL优化器却没有采用。下面就一起学习讲解如何利用索引来进行查询语句编写，避免索引失效，以及如何建立一个高效的索引。

1 避免在 where 子句中对字段进行 null 值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。所以在定义表结构的时候，对含有索引的字段通常设置为not null。如果真的有需要写入null，可以采用其他特殊值来替代，比如0，''等。

2 不建议使用%前缀模糊查询

例如LIKE "%name"或者LIKE "%name%"，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE "name%"。如何查询%name%(详细请参考文章中的全文索引)？

mysql> alter table demo_or_union drop index ftk,add index idx_id(id);

mysql> show index from demo_or_union;

mysql> explain select dep,arr from demo_or_union where id like '%7c67c95e8%';

图12.8 like查询

表demo_or_union中id列是索引列，在使用id like '%7c67c95e8%'查询的时候并没有采用idx_id索引，而是选择了全表扫描，索引失效。

3 避免在where子句中对字段进行表达式操作

比如

select user_id,user_project from user_base where age*2=36;

SQL语句中对字段执行算术运算操作，这会造成引擎放弃使用索引。因此，建议将该SQL语句修改为：

select user_id,user_project from user_base where age=36/2;

再来看一个经常遇到的一个例子：

mysql> show index from gwgid_time;

mysql> explain select gwb,wgew from gwgid_time where date(create_time)='2018-06-07';

mysql> explain select gwb,wgew from gwgid_time where create_time>='2018-06-07' and create_time

图12.9 gwgid_time索引情况

图12.10 时间函数导致索引失效

一些开发人员特别喜欢对日期进行加函数处理，但从图12.10就能看出日期函数会造成索引失效。在此，建议不要对字段执行函数处理，而是对运算符另一侧的值进行处理即可。

4 避免类型转换

where 子句中出现 column 字段的类型和传入的参数类型不一致的时候会发生的类型转换。

mysql> alter table demo_or_union add index idx_arr(arr);

mysql> show index from demo_or_union;

mysql> desc demo_or_union;

图12.11 demo_or_union索引和字段类型

mysql> explain select dep from demo_or_union where arr=123;

mysql> explain select dep from demo_or_union where arr='123';

图12.12类型转换

能够看到arr字段是varchar型，同时该字段也是索引字段。但是查询的时候条件写法为arr=123，虽然type列显示采用idx_arr索引，但是rows列的值却没有减少，说明索引失效。而改为arr='1234'就会采用索引。

5 对于联合索引来说，要遵守最左前缀法则

mysql> show index from students;

图12.13 students索引情况

mysql> explain select sex from students where grade=6;

mysql> explain select sex from students where grade=6 and room=1;

图12.14 使用联合索引

mysql> explain select sex from students where name='hsbc';

图12.15 未使用联合索引

举列来说索引含有字段grade、room、name，可以直接用grade字段，也可以grade、room这样的顺序，但是name无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。这里需要特别指出的是where grade=6;和where grade=6 and room=1;时，虽然它们的key一样，但是key_len值却不一样，说明了他们使用的联合索引的字段不一样。这一点在判断联合索引使用哪些字段是非常有效。

6 必要时可以使用force index来强制查询走某个索引

有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用force index来强制优化器使用我们制定的索引。具体案例后文有介绍。

7 注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between,>,

mysql> alter table demo_or_union drop index idx_id,drop index idx_arr,add index idx_day_arr(day,arr);

mysql> show index from demo_or_union;

mysql> explain select dep from demo_or_union where day>='2018-07-01' and day

图12.16 联合索引范围查询①

mysql> alter table demo_or_union drop index idx_day_arr,add index idx_arr_day(arr,day);

mysql> show index from demo_or_union;

图12.17 更改联合索引顺序

mysql> explain select dep from demo_or_union where day>='2018-07-01' and day

图12.18 联合索引范围查询②

第一次查询的时候，采用的索引是idx_day_arr，其索引字段顺序是day,arr，执行计划的rows值是445；之后删除了索引idx_day_arr，重新创建一个idx_arr_day，索引字段顺序是arr,day，再次查看执行计划扫描行数就成了11行。这是由于第一次查询的时候虽然采用了索引idx_day_arr，但是由于day字段是个范围查询，导致后面的索引字段arr并没有能够有效的过滤数据，仍需要回表一次。

8 order by、group by时索引的选择

这两个函数出现时，一般explain都会出现Using filesort, Using temporary。可以通过建立合适的索引来杜绝它们的存在。执行结果如图12.19所示。

mysql> alter table demo_or_union drop index idx_arr_day,add index idx_arr(arr),add index idx_arr_dep(arr,dep);

mysql> show index from demo_or_union;

图12.19 demo_or_union表索引

mysql> explain select dep,arr,count(id) from demo_or_union force index(idx_arr) where arr='PVG' group by dep;

mysql> explain select dep,arr,count(id) from demo_or_union where arr='PVG' group by dep;

图12.20 group by优化

图12.20有两个知识点，第一个就是采用了force index来强制索引。表demo_or_union含有两个索引，一个是单独索引idx_arr，索引字段只有arr；另一个是联合索引idx_arr_dep，含有字段是arr和dep。为了测试索引字段不饱和group by字段的效果，强制走了idx_arr。这就是强制索引force index的用法。一般情况下不需要强制，但是一些特殊场合使用强制索引会有意想不到的效果。

第二个知识点：图12.20能够看出，采用单一索引idx_arr时，使用group by dep时出现了Using temporary; Using filesort。这两个的出现对于数据库来说是不友好的，额外增加了数据库的性能损耗。而采用联合索引idx_arr_dep之后，这两个值就不存在了。需要注意的是，如果在where条件中有范围查询的，比如有between,>,

9 全文索引

在之前的描述中，如果采用了like '%gwgw%'，会造成索引失效的情况。但是在生产过程中，这类查询又是必须，这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。

mysql> alter table demo_or_union drop index idx_arr,drop index idx_arr_dep,add index idx_id(id),add fulltext key ftk(id);

mysql> show index from demo_or_union;

图12.21 全文索引

mysql> explain select dep,arr from demo_or_union where id like '%e6dca7c67%';

mysql> explain select dep,arr from demo_or_union where match(id) against('e6dca7c67' in boolean mode);

图12.22 全文索引使用

从前面的说明就知道，即使id列有索引，但是使用like做模糊查询时，%在前端就不会使用索引。能够明显的看出建立全文索引之后，按照全文索引编辑的SQL执行计划更优。创建全文索引和使用全文索引的SQL语句如下：

创建alter table demo_or_union add fulltext key ftk(id);

查询select dep,arr from demo_or_union where match(id) against('e6dca7c67' in boolean mode);

需要注意一下几点：

1) 使用全文索引之后，其他普通索引无法使用；

2) 全文索引的使用和一些参数有关，比如ft_min_word_len，表示最小的索引字符串长度，默认是4，如果需要查询的字段小于4，则出来的结果会是错误的；ft_max_word_len，和ft_min_word_len含义相反，表示最大的字符串长度等。具体的请参考官方手册。

3) 可以采用-、+、>、表示查询结果含有该字符串的在前面显示。

4) 修改ft_min_word_len等参数时，需要将原先的索引删除，修改参数之后，重新建立索引。

5) 全文索引比较占用服务器资源，慎用。

12.2.4 关于join优化

使用2.4.1SQL语句实现查询功能时，查询结果可能会涉及到两个或多个表中的数据，需要使用 join进行两表或多表的联接。数据库表联接有内联接、外联接和交叉联接等类型分类。其中外联接又可以分为左外联接和右外联接两种类型。图12.23是两个表实现内联接后的结果示意图，图12.24是两个表实现左外联接后的结果示意图，图12.25是两个表实现右外联接后的结果示意图。

图12.23 inner join

inner join 表示取两个表的交集。

图12.24 left join

left join表示左边表所有的集合。

图12.25 right join

right join表示右边表所有的集合。

cross join：交叉连接，得到的结果是两个表的乘积，即笛卡尔积。假设集合A={a,b}， 集合B={0,1,2}，则两个集合的笛卡尔积为(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。

请思考：图12.26是full join示意图。在MySQL中没有full join，如何通过SQL来达到full join的目的？

图12.26 full join

1 尽量使用inner join，避免left join

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

2 合理利用索引

被驱动表的索引字段作为on的限制字段。

3 利用小表去驱动大表

图12.27 join原理图

从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。

4 left join的优化

在MySQL中，A LEFT JOIN B join_condition执行过程如下：

1)根据表A和A依赖的所有表设置表B。

2)根据LEFT JOIN条件中使用的所有表(除了B)设置表A。

3)LEFT JOIN条件用于确定如何从表B搜索行。(换句话说，不使用WHERE子句中的任何条件)。

4)可以对所有标准联接进行优化，只是只有从它所依赖的所有表读取的表例外。如果出现循环依赖关系，MySQL提示出现一个错误。

5)进行所有标准WHERE优化。

6)如果A中有一行匹配WHERE子句，但B中没有一行匹配ON条件，则生成另一个B行，其中所有列设置为NULL。

7)如果使用LEFT JOIN找出在某些表中不存在的行，并且进行了下面的测试：WHERE部分的col_name IS NULL，其中col_name是一个声明为 NOT NULL的列，MySQL找到匹配LEFT JOIN条件的一个行后停止(为具体的关键字组合)搜索其它行。

RIGHT JOIN的执行类似LEFT JOIN，只是表的角色反过来。在这里需要注意的是这里的where条件中，不能对B表做限制。一旦在where语句中有B表的限制条件，那么对于产生的NULL行，WHERE条件总为假，LEFT JOIN变为普通联接，也就是inner join。

mysql> use um_kmg;

mysql> alter table goms_flight_runway_log drop index idx_type_fid_time;

mysql> show index from goms_flight_runway_log;

mysql> show index from goms_flight;

图12.28 goms_flight和goms_flight_runway_log索引情况

如图12.28中两个表的索引情况，能够看出这两个表都非常的大。由于业务需要，有个SQL要执行，其执行计划如图12.29所示。

mysql> explain select t1.fnum,t1.fdst,t1.forg,t2.fid,t2.update_time from goms_flight t1 right join goms_flight_runway_log t2 on t1.fid=t2.fid where t2.op_type=0 and t1.forg='PVG' and t1.fid!='' group by t2.fid order by t2.update_time;

图12.29 优化前执行计划

从图12.29能够看出，其执行计划出现了Using temporary; Using filesort。对于这种大表来说，出现建立临时表和排序过程是非常耗时的。需要想办法将这两个值去除。再看SQL语句，虽然出现了left join，但是t1表在有t1.forg='PVG' and t1.fid!=''的判断，所以此处的left join等效于inner join。再看group by 和order by字段，group by的是t2.fid，这个字段是连接t1和t2表的字段，可以换成t1.fid，至于这里的order by t2.update_time，可以交于开发的程序完成。如此就可以避免了Using temporary; Using filesort。图12.30是优化后的执行计划。

mysql> explain select t1.fnum,t1.fdst,t1.forg,t2.fid,t2.update_time from goms_flight t1 inner join goms_flight_runway_log t2 on t1.fid=t2.fid where t2.op_type=0 and t1.forg='PVG' and t1.fid!='' group by t1.fid;

图12.30 优化后执行计划

通过上诉例子能够知道驱动表的选择和查询字段的写法在join中是非常主要的。left join具有强制选择驱动表的特性，但是由于一些特性，造成left join自动转成inner join，如果仍需要强制选择驱动表的话，那么就需要使用连接函数STRAIGHT_JOIN来进行强制选择。需要注意的是STRAIGHT_JOIN必须要是在内连接的条件下使用。该函数使用效率很低，同样是上面的SQL，演示下STRAIGHT_JOIN的使用。

mysql> alter table goms_flight_runway_log add index idx_type_fid_time(op_type,fid,update_time);

mysql> explain select t1.fnum,t1.fdst,t1.forg,t2.fid,t2.update_time from goms_flight_runway_log t2 STRAIGHT_JOIN goms_flight t1 on t1.fid=t2.fid where t2.op_type=0 and t1.forg='PVG' and t1.fid!='' group by t2.fid;

图12.31 STRAIGHT_JOIN演示

和图12.30对比下就能发现，由原先的驱动表t1变成了驱动表是t2了。这就是STRAIGHT_JOIN的用法。一般情况下很少会使用到STRAIGHT_JOIN，除非SQL使用到group by和order by时需要强制驱动表来改善执行计划。

课堂小问题解答：

1、如何高效的写出一个替代not exists的SQL语句？

原先的SQL：

select colname … from A表 where a.id not exists (select b.id from B表 where a.id=b.id)

高效的SQL是：

select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

2、MySQL中没有full join，如何通过SQL来达到full join的目的？

图12.32 full join

select * from A left join B on B.name = A.name

where B.name is null

union all

select * from B;

第 24页，共 29页