mysql join

[kagxin]

表结构和铺底数据

create table t1(id int not null primary key auto_increment, a int, b int);
create table t2 like t1;

drop procedure pre_data;
delimiter ;;
create procedure pre_data()
begin
  declare i int;
  set i=1;
  while(i<=1000000)do
    insert into t1 (a, b) values(1000001-i, i);
    set i=i+1;
  end while;

  set i=1;
  while(i<=10000000)do
    insert into t2 (a, b) values(i, i);
    set i=i+1;
  end while;

end;;
delimiter ;
call pre_data();

Block Nested-Loop Join(BNL)

被驱动表关联字段上没有加索引，使用BNL join算法进行表连接

explain select * from t1 join t2 on t1.a=t2.a;

+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   986184 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10979551 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+

Index Nested-Loop Join (INL)

从上面的explain中看，t1 为驱动表，t2为被驱动表，在t2的关联字段 a 加上索引，优化join算法BNL为INL

alter table t2 add index a_index(a);  -- 在a上添加索引
explain select * from t1 join t2 on t1.a=t2.a;

+----+-------------+-------+------------+------+---------------+---------+---------+--------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref          | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+--------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL    | NULL    | NULL         | 986184 |   100.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ref  | a_index       | a_index | 5       | toplist.t1.a |      1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+--------+----------+-------------+

使用 Extra: Using index condition; Using where，为什么是索引下推

alter table t2 add index a_index(a);  -- 在t2 a上添加索引
alter table t1 add index b_index(b);  -- 在t1 b上加索引用于where

explain select * from t1 join t2 on t1.a=t2.a where t1.b <1000;

+----+-------------+-------+------------+-------+---------------+---------+---------+--------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref          | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+---------+---------+--------------+------+----------+------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | b_index       | b_index | 5       | NULL         | 1998 |   100.00 | Using index condition; Using where |
|  1 | SIMPLE      | t2    | NULL       | ref   | a_index       | a_index | 5       | toplist.t1.a |    1 |   100.00 | NULL                               |
+----+-------------+-------+------------+-------+---------------+---------+---------+--------------+------+----------+------------------------------------+

alter table t2 add index a_index(a);  -- 在t2 a上添加索引

explain select * from t1 join t2 on t1.a=t2.a where  t2.b <1000;

+----+-------------+-------+------------+------+---------------+---------+---------+--------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref          | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+--------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL    | NULL    | NULL         | 986184 |   100.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ref  | a_index       | a_index | 5       | toplist.t1.a |      1 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+--------+----------+-------------+

alter table t2 add index a_index(a);  -- 在t2 a上添加索引
alter table t2 add index b_index(b);  -- 在t2 b上加索引用于where

explain select * from t1 join t2 on t1.a=t2.a where  t2.b <1000;

+----+-------------+-------+------------+------+-----------------+---------+---------+--------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key     | key_len | ref          | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+---------+---------+--------------+--------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL            | NULL    | NULL    | NULL         | 986184 |   100.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ref  | a_index,b_index | a_index | 5       | toplist.t1.a |      1 |     4.63 | Using where |
+----+-------------+-------+------------+------+-----------------+---------+---------+--------------+--------+----------+-------------+

explain select * from t1 join t2 on t1.a=t2.a where  t2.b <1000 order by b;

+----+-------------+-------+------------+------+-----------------+---------+---------+--------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys   | key     | key_len | ref          | rows   | filtered | Extra                                        |
+----+-------------+-------+------------+------+-----------------+---------+---------+--------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL            | NULL    | NULL    | NULL         | 986184 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | NULL       | ref  | a_index,b_index | a_index | 5       | toplist.t1.a |      1 |     4.63 | Using where                                  |
+----+-------------+-------+------------+------+-----------------+---------+---------+--------------+--------+----------+----------------------------------------------+

a,b 联合索引

alter table t2 add index a_b_index(a, b);
explain select * from t1 join t2 on t1.a=t2.a where  t2.b <1000;

+----+-------------+-------+------------+------+---------------------------+-----------+---------+--------------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys             | key       | key_len | ref          | rows   | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------------------+-----------+---------+--------------+--------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL                      | NULL      | NULL    | NULL         | 986184 |   100.00 | Using where              |
|  1 | SIMPLE      | t2    | NULL       | ref  | a_index,b_index,a_b_index | a_b_index | 5       | toplist.t1.a |      1 |     4.63 | Using where; Using index |
+----+-------------+-------+------------+------+---------------------------+-----------+---------+--------------+--------+----------+--------------------------+

explain select * from t1 join t2 on t1.a=t2.a where  t2.b <1000 order by b;
+----+-------------+-------+------------+------+-------------------+-----------+---------+--------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys     | key       | key_len | ref          | rows   | filtered | Extra                                        |
+----+-------------+-------+------------+------+-------------------+-----------+---------+--------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL              | NULL      | NULL    | NULL         | 986184 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | NULL       | ref  | b_index,a_b_index | a_b_index | 5       | toplist.t1.a |      1 |     4.50 | Using where; Using index                     |
+----+-------------+-------+------------+------+-------------------+-----------+---------+--------------+--------+----------+----------------------------------------------+

表结构和铺底数据

CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB;

create table t2 like t1;
create table t3 like t2;

drop procedure pre_data;
delimiter ;;
create procedure pre_data()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 (id, a, b) values(i, 1000001-i, i);
    set i=i+1;
  end while;

  set i=1;
  while(i<=1000000)do
    insert into t2 (id, a, b) values(i, i, i);
    set i=i+1;
  end while;

  set i=1;
  while(i<=1000000)do
    insert into t3 (id, a, b) values(i, 1000000-i, i);
    set i=i+1;
  end while;

end;;
delimiter ;
call pre_data();

alter table t1 add key t1_c(c);  -- 
alter table t2 add key t2_ac(a,c);
alter table t3 add key t3_bc(b,c);

3 个表join

• 被驱动表关联字段添加索引
• 使用了索引下推优化

explain select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=190 and t2.c>=1100 and t3.c>=3000;

+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref      | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | t1_c          | t1_c  | 5       | NULL     |    1 |   100.00 | Using index condition; Using where |
|  1 | SIMPLE      | t2    | NULL       | ref   | t2_ac         | t2_ac | 5       | icp.t1.a |    1 |    33.33 | Using index condition; Using where |
|  1 | SIMPLE      | t3    | NULL       | ref   | t3_bc         | t3_bc | 5       | icp.t2.b |    1 |    33.33 | Using index condition              |
+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+------------------------------------+

排序

• t1.a 排序可以使用索引
• t2,t3表中的字段无法使用索引排序

explain select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=190 and t2.c>=1100 and t3.c>=3000 order by t1.a;

+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref      | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | t1_c          | t1_c  | 5       | NULL     |    1 |   100.00 | Using index condition; Using where |
|  1 | SIMPLE      | t2    | NULL       | ref   | t2_ab         | t2_ab | 5       | icp.t1.a |    1 |    33.33 | Using index condition; Using where |
|  1 | SIMPLE      | t3    | NULL       | ref   | t3_bc         | t3_bc | 5       | icp.t2.b |    1 |    33.33 | Using index condition              |
+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+------------------------------------+

explain select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=190 and t2.c>=1100 and t3.c>=3000 order by t2.a;

+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref      | rows | filtered | Extra                                                               |
+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | t1_c          | t1_c  | 5       | NULL     |    1 |   100.00 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | NULL       | ref   | t2_ab         | t2_ab | 5       | icp.t1.a |    1 |    33.33 | Using index condition; Using where                                  |
|  1 | SIMPLE      | t3    | NULL       | ref   | t3_bc         | t3_bc | 5       | icp.t2.b |    1 |    33.33 | Using index condition                                               |
+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+---------------------------------------------------------------------+

explain select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=190 and t2.c>=1100 and t3.c>=3000 order by t3.c;
+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref      | rows | filtered | Extra                                                               |
+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | t1_c          | t1_c  | 5       | NULL     |    1 |   100.00 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | NULL       | ref   | t2_ab         | t2_ab | 5       | icp.t1.a |    1 |    33.33 | Using index condition; Using where                                  |
|  1 | SIMPLE      | t3    | NULL       | ref   | t3_bc         | t3_bc | 5       | icp.t2.b |    1 |    33.33 | Using index condition                                               |
+----+-------------+-------+------------+-------+---------------+-------+---------+----------+------+----------+---------------------------------------------------------------------+