mysql-从大小表顺序问题探究mysql联表实现的原理

创建两个表

表big

CREATE TABLE `big` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `code` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13543532 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

表small

CREATE TABLE `small` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `code` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

分别插入数据

mysql的存储过程如下

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_big`()
BEGIN
  #Routine body goes here...
	 DECLARE i int DEFAULT 1;
	 DECLARE j int DEFAULT 1;
   DECLARE a int DEFAULT 1;
	 
	 WHILE i<1231234 DO
	 INSERT INTO `big`(id,num,`name`,`code`) VALUE(a,i,'big',j);  
	 set i=i+3;
	 set j=j+77;
	 set a=a+33;
		END WHILE;

END

1，大表插入410411条数据
2，小表插入7条数据

场景

多表联表或者联表后作为中间表等联表不加筛选条件

结构	Sql（无筛选条件）	时间（秒）	两个表加code索引
大表在前	SELECT * FROM big LEFT JOIN small ON big.code = small.code;	0.671	0.8690315
小表在前	SELECT * FROM small LEFT JOIN big ON big.code = small.code;	0.359	0.00385

表现
1，在没有索引的情况，大表的时间是小表的2倍左右，
2，加了索引后，大表在前的查询时间增加了，小表有很明显的时间缩短迹象。

结论：
全表扫描大表全量都进行回表了，增加了时间，而小表在前的时候，因为大表有索引，节约了时间。但是总体上没有筛选条件的时候，小表在前时间更加短。

建议
Left join或者right join没有筛选条件的时候，小表作为驱动表。实际上是参与笛卡尔积的小表作为驱动表。

联表加大表筛选条件

Sql 时间（秒） name加索引 两个表加code索引 name加索引两个表加code索引
SELECT
*
FROM
big
LEFT JOIN small ON big.code = small.code
WHERE
big.name = ‘check’; 0.183（时间取平均值） 0.000223 0.18250425 0.000283
SELECT
*
FROM
small
LEFT JOIN big ON big.code = small.code
WHERE
big.name = ‘check’; 0.176（时间取平均值） 0.00033325
0.00621325 0.0032485

表现
1，在没有索引的情况下，sql的时间跳动比较明显，但是大表在前查找的时间一定比小表要长；
2，在添加name索引后，两个sql的查找时间都大大缩短了，但是大表在前的时间明显小于小表在前的时间；
3，添加code索引，大表的时间没有发生变化，小表的时间虽然大大缩短了，但是没有name索引缩短的时间明显；
4，添加name和code索引，两个sql的时间都大大缩小了，但是相比name索引而言，小表的时间变长了10倍，大表的时间几乎没有变化。

建议
Left join或者right join在大表存在筛选条件的时候，返回全量数据，关联关系和筛选条件均添加索引，大表在作为驱动表。

联表加大表筛选条件，返回小表的值

Sql 时间（秒） name加索引 两个表加code索引 name加索引两个表加code索引
SELECT
small.*
FROM
big
LEFT JOIN small ON big.code = small.code
WHERE
big.name = ‘check’; 0.15947325

(0.183) 0.00041

(0.000223)
0.17326025

(0.183481) 0.00027725

(0.000273)
SELECT
small.*
FROM
small
LEFT JOIN big ON big.code = small.code
WHERE
big.name = ‘check’; 0.155488

(0.176) 0.00377525

(0.00033325) 0.006197

(0.00547125)
0.00040325

(0.0032485)

表现(对比全量返回)
1，没有索引的情况下，两个sql时间缩短
2，添加name索引的情况下，大表在前的时间增加了，小表在前的时间缩短了
3，添加code索引的情况下，两个sql时间缩短了
4，添加name和code的情况下，大表在前的时间变化不明显，小表在前的时间缩短了

建议
Left join或者right join在大表存在筛选条件的时候，返回小表数据，关联关系和筛选条件均添加索引，大表在作为驱动表。

联表加大表筛选条件，返回大表的值

Sql 时间（秒） name加索引 两个表加code索引 name加索引两个表加code索引
SELECT
big.*
FROM
big
LEFT JOIN small ON big.code = small.code
WHERE
big.name = ‘check’; 0.17469175

(0.183) 0.0002175

(0.000223)
0.190215

(0.183481) 0.00024775

(0.000273)
SELECT
big.*
FROM
small
LEFT JOIN big ON big.code = small.code
WHERE
big.name = ‘check’; 0.16516725

(0.176) 0.0003305

(0.00033325) 0.004572

(0.00547125)
0.0003895

(0.0032485)

表现(对比全量返回)
5，没有索引的情况下，两个sql时间缩短
6，添加name索引的情况下，变化不明显
7，添加code索引的情况下，大表在前的时间变化不明显，小表在前轻微缩短
8，添加name和code的情况下，大表在前的时间轻微缩短，小表在前的时间轻微增加

建议
Left join或者right join在大表存在筛选条件的时候，返回大表数据，关联关系和筛选条件均添加索引，大表在作为驱动表。

联表加小表筛选条件

Sql 时间（秒） name加索引 两个表加code索引 name加索引两个表加code索引
SELECT
*
FROM
big
LEFT JOIN small ON big.code = small.code
WHERE
small.name = ‘check’; 0.18027325

(0.183) 0.19011475

(0.000223)
0.00412125

(0.183481) 0.0003255

(0.000273)
SELECT
*
FROM
small
LEFT JOIN big ON big.code = small.code
WHERE
small.name = ‘check’; 0.190464

(0.176) 0.19005075

(0.00033325) 0.000187

(0.00547125)
0.000259

(0.0032485)

表现（相对比大表筛选条件）
1，添加name索引没有实质的效果
2，添加code索引造成明显的时间缩短

建议
Left join或者right join在小表存在筛选条件的时候，返回全量数据，关联关系和筛选条件均添加索引，小表作为驱动表。

联表加小表筛选条件，返回大表的值

Sql 时间（秒） name加索引 两个表加code索引 name加索引两个表加code索引
SELECT
big.*
FROM
big
LEFT JOIN small ON big.code = small.code
WHERE
small.name = ‘check’; 0.1872935

(0.183) 0.18916375

(0.000223)
0.001434

(0.183481) 0.0003195

(0.000273)
SELECT
big.*
FROM
small
LEFT JOIN big ON big.code = small.code
WHERE
small.name = ‘check’; 0.171845

(0.176) 0.182016

(0.00033325) 0.00021075

(0.00547125)
0.000236

(0.0032485)

表现
1，添加name索引情况下，相对大表筛选条件时间要长，但是无论大表在前还是小表在前，区别不大。
2，添加code索引和添加code、name索引的情况下，相对大表筛选条件时间要短，同样小表筛选条件下，小表在前时间更短

建议
Left join或者right join在小表存在筛选条件的时候，返回大表数据，关联关系和筛选条件均添加索引，小表作为驱动表。

联表加小表筛选条件，返回小表的值

Sql 时间（秒） name加索引 两个表加code索引 name加索引两个表加code索引
SELECT
small.*
FROM
big
LEFT JOIN small ON big.code = small.code
WHERE
small.name = ‘check’; 0.175645

(0.183) 0.16814125

(0.000223)
0.003404

(0.183481) 0.001648

(0.000273)
SELECT
small.*
FROM
small
LEFT JOIN big ON big.code = small.code
WHERE
small.name = ‘check’; 0.1563385

(0.176) 0.15505175

(0.00033325) 0.00020025

(0.00547125)
0.00031475

(0.0032485)

表现
1，添加name索引情况下，相对大表筛选条件时间要长，但是无论大表在前还是小表在前，区别不大。
2，添加code索引和添加code、name索引的情况下，相对大表筛选条件时间要短，同样小表筛选条件下，小表在前时间更短

建议
Left join或者right join在小表存在筛选条件的时候，返回小表数据，关联关系和筛选条件均添加索引，小表作为驱动表。

总结
1，大小表联表查询问题，sql执行的时间与索引相关，建议将筛选条件和关联关系的索引均添加，小驱动表的表在前原则。这里小驱动指的是表单结合where条件得到的驱动表。

另外，
2，如果是大数据表的筛选条件添加索引，能起到非常明显的效果，但是小数据表的筛选条件添加索引效果微乎其微。
3，大数据表作为筛选条件情况，只对关联关系添加索引，大数据表在前几乎没有得到优化，小数据表在前得到优化；小数据表作为筛选条件情况下，只对关联关系添加索引，不论大表在前还是小表在前都能起到明显的效果。

建议
1，如果可以调整大小表的情况下，小驱动表在前原则，将关系字段和条件字段进行添加索引。
2，在无法调整大小表顺序的情况下，大数据表的筛选字段优先添加，如果小数据表在前，关系字段一定要加索引。

原理探究
a left join b on a.code=b.code where a.name=‘check’

graph LR
a根据name索引查找到id-->a根据id查找到row

graph LR
b根据code索引查找到id-->b根据id查找到row

1，从上面可以看出两张表都进行了回表的操作，所以select返回的数据不会影响查找的时间，只是会影响返回的数据量。
2，总时间=name查找时间+a回表时间+code查找时间+b回表时间，也可以分为两大块，表a查找到code的时间+表b查找符合code条件的时间。这里的关键在于表a的name索引和表b的code索引，如果没有索引，就会变成了全表扫描。那么涉及到另一个问题，a，b表能否互换位置，表b在前，造成的问题是表b是全表扫描，然后查找对应的code去查找表a，表a这个时候就有两个字段筛选同时生效。b表可以认为是驱动大表，作为笛卡尔积的基数。

测试中发现另外一个有趣的问题，如果id有序，将会导致查询速度非常的快，这个时候将无法看出性能对比，这个是为什么呢，因为数据量太小，全表扫描的时候走了id，速度非常快。