听说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.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) 可用

场景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.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

参考

[MySQL优化案例]系列 — RAND()优化

众所周知,在MySQL中,如果直接 ORDER BY RAND() 的话,效率非常差,因为会多次执行。事实上,如果等值查询也是用 RAND() 的话也如此,我们先来看看下面这几个SQL的不同执行计划和执行耗时。
首先,看下建表DDL,这是一个没有显式自增主键的InnoDB表:

[yejr@imysql]> show create table t_innodb_random\G
*************************** 1. row ***************************
Table: t_innodb_random
Create Table: CREATE TABLE `t_innodb_random` (
`id` int(10) unsigned NOT NULL,
`user` varchar(64) NOT NULL DEFAULT '',
KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

往这个表里灌入一些测试数据,至少10万以上, id 字段也是乱序的。

[yejr@imysql]> select count(*) from t_innodb_random\G
*************************** 1. row ***************************
count(*): 393216

1、常量等值检索:

[yejr@imysql]> explain select id from t_innodb_random where id = 13412\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_innodb_random
type: ref
possible_keys: idx_id
key: idx_id
key_len: 4
ref: const
rows: 1
Extra: Using index

[yejr@imysql]> select id from t_innodb_random where id = 13412;
1 row in set (0.00 sec)

可以看到执行计划很不错,是常量等值查询,速度非常快。

2、使用RAND()函数乘以常量,求得随机数后检索:

[yejr@imysql]> explain select id from t_innodb_random where id = round(rand()*13241324)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using where; Using index

[yejr@imysql]> select id from t_innodb_random where id = round(rand()*13241324)\G
Empty set (0.26 sec)

可以看到执行计划很糟糕,虽然是只扫描索引,但是做了全索引扫描,效率非常差。因为WHERE条件中包含了RAND(),使得MySQL把它当做变量来处理,无法用常量等值的方式查询,效率很低。

我们把常量改成取t_innodb_random表的最大id值,再乘以RAND()求得随机数后检索看看什么情况:

[yejr@imysql]> explain select id from t_innodb_random where id = round(rand()*(select max(id) from t_innodb_random))\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where id = round(rand()*(select max(id) from t_innodb_random))\G
Empty set (0.27 sec)

可以看到,执行计划依然是全索引扫描,执行耗时也基本相当。

3、改造成普通子查询模式 ,这里有两次子查询

[yejr@imysql]> explain select id from t_innodb_random where id = (select round(rand()*(select max(id) from t_innodb_random)) as nid)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using where; Using index
*************************** 2. row ***************************
id: 3
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where id = (select round(rand()*(select max(id) from t_innodb_random)) as nid)\G
Empty set (0.27 sec)

可以看到,执行计划也不好,执行耗时较慢。

4、改造成JOIN关联查询,不过最大值还是用常量表示

[yejr@imysql]> explain select id from t_innodb_random t1 join (select round(rand()*13241324) as id2) as t2 where t1.id = t2.id2\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: ref
possible_keys: idx_id
key: idx_id
key_len: 4
ref: const
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used

[yejr@imysql]> select id from t_innodb_random t1 join (select round(rand()*13241324) as id2) as t2 where t1.id = t2.id2\G
Empty set (0.00 sec)

这时候执行计划就非常完美了,和最开始的常量等值查询是一样的了,执行耗时也非常之快。
这种方法虽然很好,但是有可能查询不到记录,改造范围查找,但结果LIMIT 1就可以了:

[yejr@imysql]> explain select id from t_innodb_random where id > (select round(rand()*(select max(id) from t_innodb_random)) as nid) limit 1\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using where; Using index
*************************** 2. row ***************************
id: 3
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where id > (select round(rand()*(select max(id) from t_innodb_random)) as nid) limit 1\G
*************************** 1. row ***************************
id: 1301
1 row in set (0.00 sec)

可以看到,虽然执行计划也是全索引扫描,但是因为有了LIMIT 1,只需要找到一条记录,即可终止扫描,所以效率还是很快的。

小结:
从数据库中随机取一条记录时,可以把RAND()生成随机数放在JOIN子查询中以提高效率。

5、再来看看用ORDRR BY RAND()方式一次取得多个随机值的方式:

[yejr@imysql]> explain select id from t_innodb_random order by rand() limit 1000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using index; Using temporary; Using filesort

[yejr@imysql]> select id from t_innodb_random order by rand() limit 1000;
1000 rows in set (0.41 sec)

全索引扫描,生成排序临时表,太差太慢了。

6、把随机数放在子查询里看看:

[yejr@imysql]> explain select id from t_innodb_random where id > (select rand() * (select max(id) from t_innodb_random) as nid) limit 1000\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t_innodb_random
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 393345
Extra: Using where; Using index
*************************** 2. row ***************************
id: 3
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where id > (select rand() * (select max(id) from t_innodb_random) as nid) limit 1000\G
1000 rows in set (0.04 sec)

嗯,提速了不少,这个看起来还不赖:)

7、仿照上面的方法,改成JOIN和随机数子查询关联

[yejr@imysql]> explain select id from t_innodb_random t1 join (select rand() * (select max(id) from t_innodb_random) as nid) t2 on t1.id > t2.nid limit 1000\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: range
possible_keys: idx_id
key: idx_id
key_len: 4
ref: NULL
rows: 196672
Extra: Using where; Using index
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used
*************************** 4. row ***************************
id: 3
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random t1 join (select rand() * (select max(id) from t_innodb_random) as nid) t2 on t1.id > t2.nid limit 1000\G
1000 rows in set (0.00 sec)

可以看到,全索引检索,发现符合记录的条件后,直接取得1000行,这个方法是最快的。

综上,想从MySQL数据库中随机取一条或者N条记录时,最好把RAND()生成随机数放在JOIN子查询中以提高效率。
上面说了那么多的废话,最后简单说下,就是把下面这个SQL:

SELECT id FROM table ORDER BY RAND() LIMIT n;

改造成下面这个:

SELECT id FROM table t1 JOIN (SELECT RAND() * (SELECT MAX(id) FROM table) AS nid) t2 ON t1.id > t2.nid LIMIT n;

如果想要达到完全随机,还可以改成下面这种写法:

SELECT id FROM table t1 JOIN (SELECT round(RAND() * (SELECT MAX(id) FROM table)) AS nid FROM table LIMIT n) t2 ON t1.id = t2.nid;

就可以享受在SQL中直接取得随机数了,不用再在程序中构造一串随机数去检索了。