数据API 产品矩阵 案例 关于
掌握聚合最新动态了解行业最新趋势
API接口,开发服务,免费咨询服务

听说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

子场景驱动表(t1)列被驱动表(t2)列是否可用索引
case1.1INTINT可用
case1.2INTCHAR(20)不可用
case1.3INTVARCHAR(20)不可用
case1.4INTMEDIUMINT可用
case1.5INTBIGINT可用
case1.6MEDIUMINTINT可用
case1.7MEDIUMINTBIGINT可用
case1.8BIGINTMEDIUMINT可用
case1.9BIGINTINT可用

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

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

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

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

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

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

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

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

子场景驱动表(t1)列被驱动表(t2)列是否可用索引
case5.1CHAR(30)CHAR(20)可用
case5.2CHAR(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.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

参考

知识无界限,不再加原创

喜欢就转走,铁粉加密圈

好铁观音尽在

「老叶茶馆」

http://yejinrong.com

原文来自:老叶茶馆

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

掌握聚合最新动态了解行业最新趋势
API接口,开发服务,免费咨询服务
听说JOIN的列类型一定要一样?
发布:2017-07-21 12:08:07

导读

我们在制定表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.1INTINT可用
case1.2INTCHAR(20)不可用
case1.3INTVARCHAR(20)不可用
case1.4INTMEDIUMINT可用
case1.5INTBIGINT可用
case1.6MEDIUMINTINT可用
case1.7MEDIUMINTBIGINT可用
case1.8BIGINTMEDIUMINT可用
case1.9BIGINTINT可用

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

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

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

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

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

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

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

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

子场景驱动表(t1)列被驱动表(t2)列是否可用索引
case5.1CHAR(30)CHAR(20)可用
case5.2CHAR(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.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

参考

知识无界限,不再加原创

喜欢就转走,铁粉加密圈

好铁观音尽在

「老叶茶馆」

http://yejinrong.com

原文来自:老叶茶馆

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

选择想要的接口, 看看能免费获取多少次调用 选择(单选)或填写想要的接口
  • 短信API服务
  • 银行卡四元素检测[简]
  • 身份证实名认证
  • 手机状态查询
  • 三网手机实名制认证[简]
  • 身份证OCR识别
  • 证件识别
  • 企业工商信息
短信API服务
  • 短信API服务
  • 银行卡四元素检测[简]
  • 身份证实名认证
  • 手机状态查询
  • 三网手机实名制认证[简]
  • 身份证OCR识别
  • 证件识别
  • 企业工商信息
  • 确定
选择您的身份
请选择寻找接口的目的
预计每月调用量
请选择预计每月调用量
产品研发的阶段
请选择产品研发的阶段
×

前往领取
电话 0512-88869195
×
企业用户认证,
可获得1000次免费调用
注册登录 > 企业账户认证 > 领取接口包
企业用户认证领取接口包 立即领取
× 企业用户认证,
可获得1000次免费调用,立即领取>
数 据 驱 动 未 来
Data Drives The Future