听说JOIN的列类型一定要一样？

导读

我们在制定表DDL设计规范时，通常都会要求一条：如果有两个表要做JOIN，那么关联条件列类型最好完全一样，才能保证查询效率，真的如此吗？

相信不少朋友主动或被动告知这样一个规范要求（其实我也制定过这个规范），当多表JOIN时，关联条件列类型最好是完全一样的，这样才可以确保查询效率。果真如此吗？

关于多表JOIN的几点结论及建议

为了节省大家时间，我先把几点结论写在前面，没耐心的同学可忽略后面测试过程。

• 当被驱动表的列是字符串类型，而驱动表的列类型是非字符串时，则会发生类型隐式转换，无法使用索引；

• 当被驱动表和驱动表的列都是字符串类型，两边无论是 CHAR 还是 VARCHAR，均不会发生类型隐式转换，都可以使用索引；

• 当被驱动表的列是字符串且其字符集比驱动表的列采用的字符集更小或无法被包含时（latin比utf8mb4小，gb2312 比 utf8mb4 小，另外 gb2312 虽然比 latin1 大，但并不兼容，也不行，详见下方测试 ），则会发生类型隐式转换，无法使用索引；

• 综上，虽然有很多场景下，JOIN列类型不一致也能用到索引，但保不准啥时候就掉坑了。因此，最后回答一下本文题目，JOIN列的类型定义最好是完全一致，包括长度，尤其是字符集。

几点说明

• 测试表t1、t2表均为UTF8MB4字符集。

• 字符串类型列char_col默认设置VARCHAR(20)。

• 测试MySQL 版本 5.7.18。

友情提醒：以下内容建议先把手机横过来看哈。

场景1：驱动表列是MEDIUMINT/INT/BIGINT

场景2：驱动列是CHAR(20)

备注：我们知道，InnoDB表索引最长768字节，那么VARCHAR(260) UTF8MB4 字符集的列上建索引不会超限吗？秘密在于，MySQL 5.7.7开始，默认启用 innodb_large_prefix 选项，允许索引最多扩展到3072字节，嘿嘿，现在秒懂了吧。

场景3：驱动列是VARCHAR(20)

场景4：驱动列是VARCHAR(260)/VARCHAR(270)

场景5：驱动列是VARCHAR(30)

场景6：最后有排序的情况
最后的排序列不属于驱动表

yejr@imysql.com[yejr]> EXPLAIN SELECT * FROM t1 LEFT JOIN
    t2 ON (t1.int_col = t2.int_col) WHERE
    t1.id >= 5000 ORDER BY t2.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 51054
filtered: 100.00
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: int_col
key: int_col
key_len: 4
ref: yejr.t1.int_col
rows: 10
filtered: 100.00
Extra: NULL

小结：当最后的排序列不属于驱动表时，则会生成临时表，且又有额外排序。

最后的排序列属于驱动表

yejr@imysql.com[yejr]> EXPLAIN SELECT * FROM t1 LEFT JOIN
    t2 ON (t1.int_col = t2.int_col) WHERE
    t1.id >= 5000 ORDER BY t1.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 51054
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: int_col
key: int_col
key_len: 4
ref: yejr.t1.int_col
rows: 10
filtered: 100.00
Extra: NULL

小结：当最后的排序列属于驱动表时，则不会生成临时表，也不需要额外排序。

更多的组合测试场景，请各位亲自行完成哈。

附录

测试表DDL

CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `int_col` int(20) unsigned NOT NULL DEFAULT '0',
  `char_col` char(20) NOT NULL DEFAULT '',
...
  PRIMARY KEY (`id`),
  KEY `int_col` (`int_col`),
  KEY `char_col` (`char_col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `t2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `int_col` int(8) unsigned NOT NULL DEFAULT '0',
  `char_col` char(20) NOT NULL DEFAULT '',
...
  PRIMARY KEY (`id`),
  KEY `int_col` (`int_col`),
  KEY `char_col` (`char_col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

修改列字符集定义的DDL样例

/*
- 只修改长度
*/
ALTER TABLE t1 MODIFY char_col 
    VARCHAR(260) NOT NULL DEFAULT '';

/*
- 同时修改字符集
*/
ALTER TABLE t2 MODIFY char_col
    VARCHAR(20) CHARACTER SET UTF8 NOT NULL DEFAULT '';

修改完列定义后，还记得要重新执行 ANALYZE TABLE 重新统计索引信息哟。

yejr@imysql.com[yejr]> ANALYZE TABLE t1, t2;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| yejr.t1 | analyze | status   | OK       |
| yejr.t2 | analyze | status   | OK       |
+---------+---------+----------+----------+

执行测试的SQL样例

/*
- char_col 可以自行替换成 int_col
- 加上 t1.id >= 5000 是为了避免预估扫描数据量太多，变成全表扫描
*/
EXPLAIN SELECT * FROM t1 LEFT JOIN
    t2 ON (t1.char_col = t2.char_col) WHERE
    t1.id >= 5000\G

参考

• UTF8字符集的表怎么直接转UTF8MB4？
  

• 细说ANALYZE TABLE

• 优化系列 | DELETE子查询改写优化

• [MySQL优化案例]系列 — 分页优化

• [MySQL优化案例]系列 — discuz!热帖翻页优化
  

知识无界限，不再加原创

喜欢就转走，铁粉加密圈

好铁观音尽在

「老叶茶馆」

http://yejinrong.com

原文来自:老叶茶馆

声明：所有来源为“聚合数据”的内容信息，未经本网许可，不得转载！如对内容有异议或投诉，请与我们联系。邮箱：marketing@think-land.com