示例表
CREATE TABLE
t1
(
id
int(11) NOT NULL AUTO_INCREMENT,
a
int(11) DEFAULT NULL,
b
int(11) DEFAULT NULL,
PRIMARY KEY (id
),
KEYidx_a
(a
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t2 like t1;
‐‐ 插入一些示例数据
‐‐ 往t1表插入1万行记录
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();
‐‐ 往t2表插入100行记录
drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()
begin
declare i int;
set i=1;
while(i<=100)do
insert into t2(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t2();
MySQL中常见的两种表关联算法
- Nested-Loop Join 算法
- Block Nested-Loop Join 算法
1、嵌套循环连接 Nested Loop Join 算法(BLJ)
一次一行循环地从第一张表(称为驱动表)中读取行,在这行数据中取到关联字段,根据关联段在另一张表(被驱动表)里取出满足条件的行,然后