当前位置:[首页 > 韩语学习 > 听说 > 听说JOIN的列类型一定要一样?]

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

发布: 2017-11-15 11:29 | 来源:www.jptranslate.com | 查 看:

  我们在制定表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

子场景 驱动表(t1)列 被驱动表(t2)列 是否可用索引 case1.1 INT INT 可用 case1.2 INT CHAR(20) 不可用 case1.3 INT VARCHAR(20) 不可用 case1.4 INT MEDIUMINT 可用 case1.5 INT BIGINT 可用 case1.6 MEDIUMINT INT 可用 case1.7 MEDIUMINT BIGINT 可用 case1.8 BIGINT MEDIUMINT 可用 case1.9 BIGINT INT 可用

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

子场景 驱动表(t1)列 被驱动表(t2)列 是否可用索引 case2.1 CHAR(20) CHAR(20) 可用 case2.2 CHAR(20) UTF8 CHAR(20) 可用 case2.3 CHAR(20) CHAR(20) UTF8 不可用 case2.4 CHAR(20) UTF8MB4 CHAR(20) LATIN1 不可用 case2.5 CHAR(20) UTF8MB4 CHAR(20) GB2312 不可用 case2.6 CHAR(20) LATIN1 CHAR(20) UTF8MB4 可用 case2.7 CHAR(20) GB2312 CHAR(20) UTF8MB4 可用 case2.8 CHAR(20) GB2312 CHAR(20) LATIN1 SQL报错,要先转字符集 case2.9 CHAR(20) LATIN1 CHAR(20) GB2312 SQL报错,要先转字符集 case2.10 CHAR(20) VARCHAR(20) 可用 case2.11 CHAR(20) VARCHAR(30) 可用 case2.12 CHAR(20) CHAR(30) 可用 case2.13 CHAR(20) VARCHAR(260) 可用

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

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

子场景 驱动表(t1)列 被驱动表(t2)列 是否可用索引 case3.1 VARCHAR(20) CHAR(20) 可用 case3.2 VARCHAR(20) VARCHAR(20) 可用 case3.3 VARCHAR(20) VARCHAR(260) 可用

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

子场景 驱动表(t1)列 被驱动表(t2)列 是否可用索引 case4.1 VARCHAR(260) CHAR(20) 可用 case4.2 VARCHAR(260) VARCHAR(20) 可用 case4.3 VARCHAR(260) VARCHAR(260) 可用 case4.4 VARCHAR(260) VARCHAR(270) 可用 case4.5 VARCHAR(270) VARCHAR(260) 可用

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

子场景 驱动表(t1)列 被驱动表(t2)列 是否可用索引 case5.1 CHAR(30) CHAR(20) 可用 case5.2 CHAR(30) VARCHAR(20) 可用

  场景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.idG*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: NULLtype: rangepossible_keys: PRIMARYkey: PRIMARYkey_len: 8ref: NULLrows: 51054filtered: 100.00Extra: Using where; Using temporary; Using filesort*************************** 2. row ***************************id: 1select_type: SIMPLEtable: t2partitions: NULLtype: refpossible_keys: int_colkey: int_colkey_len: 4ref: yejr.t1.int_colrows: 10filtered: 100.00Extra: 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.idG*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: NULLtype: rangepossible_keys: PRIMARYkey: PRIMARYkey_len: 8ref: NULLrows: 51054filtered: 100.00Extra: Using where*************************** 2. row ***************************id: 1select_type: SIMPLEtable: t2partitions: NULLtype: refpossible_keys: int_colkey: int_colkey_len: 4ref: yejr.t1.int_colrows: 10filtered: 100.00Extra: NULL

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

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

  附录

  测试表DDL