外连接与内连接的查询优化

数据准备

#分类
create table if not exists t_type(
  id int(10) not null primary key auto_increment,
  card int(10) not null
);

#图书
create table if not exists t_book(
   bookid int(10) not null primary key auto_increment,
   card int(10) not NULL
);

# 向分类表 t_type 中插入20条数据 
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);

...

# 向图书表 t_book 中插入20条数据 
insert into t_book(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_book(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_book(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_book(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_book(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_book(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_book(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_book(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_book(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_book(card) VALUES( FLOOR(1 +(RAND() * 20))	);

...

 添加索引 / 查询索引 / 删除索引

# 添加索引 (给表 t_book 中的列 card 添加索引 Y)
create index Y on t_book(card);

# 删除索引  DROP INDEX <索引名> ON <表名>
drop index Y on t_book;

# 查询索引
show index from t_book;

 左外连接 left  join ... on ... 

# explain + SQL语句 : 分析查询
explain select SQL_NO_CACHE * from t_type left join t_book on t_type.card=t_book.card;

 给表 t_book  添加索引，执行 explain 分析查询后效果图：

未添加索引效果图：

两个表的 card 字段 都添加索引后的效果图：

 注意：

• 两个表中的字段 card 添加索引时需要注意，列类型要一致，列名可以不一样。
• 使用左外联接 执行 explain 分析语句时，t_type在上面，上面的是主表，下面的是从表。

右外连接 right  join ... on ... 

 内连接  inner  join  ... on ...

 内连接-情况1：两表数据量一样（都是20条），表有无索引，主从表情况

# 内联接
select SQL_NO_CACHE * from t_type inner join t_book on t_type.card=t_book.card;

# explain 分析查询
explain select SQL_NO_CACHE * from t_type inner join t_book on t_type.card=t_book.card;

结论：对于内联接来说，查询优化器可以决定谁作为主表，谁作为从表出现。

给两个表都添加索引，使用内联接后效果图：（t_type 作为主表出现）

create index Y on t_book(card);
create index X on t_type(card);

explain select SQL_NO_CACHE * from t_type inner join t_book on t_type.card=t_book.card;

---

  删除 t_book 表的索引,保留t_type索引，内联接后效果图：(t_book表作为主表，在上面)

# 删除索引
drop index Y on t_book;
create index X on t_type(card);

explain select SQL_NO_CACHE * from t_type inner join t_book on t_type.card=t_book.card;

结论：对于内联接来讲，如果表的连接条件中只有一个字段有索引，则有索引的字段所在的表会作为从表（被驱动表）出现。这就是我们查询优化器优化的一个策略。

---

内联接-情况2：数据量不一样，两个表都有索引时，主从表情况 

# 添加索引
create index Y on t_book(card);
create index X on t_type(card);

explain select SQL_NO_CACHE * from t_type inner join t_book on t_type.card=t_book.card;

# 向分类表 t_type 中插入20条数据 
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);
insert into t_type(card) VALUES( FLOOR(1 +(RAND() * 20))	);

... 省略10条

 此时两个表都有索引，t_type表40条数据，t_book表 20条数据,执行 explain分析语句效果图：

（t_book 表作为主表出现）

结论：对于内连接来说，在两个表连接条件都存在索引的情况下，会选择小表作为主表（驱动表）。“小表驱动大表”

 内联接-情况3：t_type 有索引，t_book 数据量少

 数据量少的 t_book 作为主表

explain 分析查询 

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

explain select SQL_NO_CACHE * from t_type