关于【SQL优化】中-【不要让数据库帮我们做强制类型转换】和【如果需要进行 join 的字段两表的字段类型要相同】的一点疑问

[liueleven]

在提交issue之前请回答以下问题，谢谢!

建议首先查看是否已经有类似的 Issues (提交时可删除该提示)

你使用的是哪个版本

版本信息：mysql Ver 14.14 Distrib 5.7.23, for macos10.13 (x86_64) using EditLine wrapper

预期结果

1. 【不要让数据库帮我们做强制类型转换】，该结论不严谨
2. 【如果需要进行 join 的字段两表的字段类型要相同】，该结论有问题

实际结果

1. 【不要让数据库帮我们做强制类型转换】，该结论不严谨

   结论：隐式转换，where column_name=2 id是字符串类型，相当于使用字符串转整型，不用索引；而整数转字符串会用到索引

2. 【如果需要进行 join 的字段两表的字段类型要相同】，该结论有问题

   结论：关联查询中，关联字段如果没有索引,会导致后面的索引都失效

以下是我测试中用到的sql

-- =========================================隐式转换测试=====================================
-- 建表
CREATE TABLE `user` (
  `id`  int(11) unsigned NOT NULL auto_increment,
  `age` int(3) NOT NULL,
  `user_name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
-- 插入数据
insert into user (age,user_name) value(23,'13000001111');
insert into user (age,user_name)values(24,'13000001112');
insert into user (age,user_name)values(25,'13000001113');
insert into user (age,user_name)values(26,'13000001114');
insert into user (age,user_name)values(27,'13000001115');

-- 添加索引
alter table user add index idx_user_name (user_name);
-- 添加索引
alter table user add index idx_age (age);

-- user_name是字符串类型，这里不是用索引，extra的结果是Using where
explain
SELECT * from user where user_name = 13000001115;

-- user_name是字符串类型，这里用索引，extra的结果是null
explain
SELECT * from user where user_name = '13000001115';

-- age是整型，这里可以用索引,extra的结果是null
explain
SELECT * from user where age = '23';

-- age是整型，这里可以用索引,extra的结果是null
explain
SELECT * from user where age = 23;

结论：隐式转换，where column_name=2 id是字符串类型，相当于使用字符串转整型，不用索引；而整数转字符串会用到索引

-- =========================================join测试=====================================
-- 建表
CREATE TABLE `user2` (
  `id`  int(11) unsigned NOT NULL auto_increment,
  `age` varchar(3) NOT NULL,
  `phone` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
-- 插入数据
insert into user2 (age,phone) value('23',130001111);
insert into user2 (age,phone)values('24',130001112);
insert into user2 (age,phone)values('25',130001113);
insert into user2 (age,phone)values('26',130001114);
insert into user2 (age,phone)values('27',130001115);

-- 添加索引
alter table user2 add index idx_phone (phone);
-- 添加索引
alter table user2 add index idx_age (age);
-- 删除user2 age索引
drop index idx_age on user2;

-- ========== 有索引测试
-- 有索引Using where; Using index; Using join buffer (Block Nested Loop)
explain
SELECT u1.* from user u1 left join user2 u2 on u1.age = u2.age;

-- 有索引Using where; Using index
explain
SELECT u1.* from user u1 left join user2 u2 on u1.age = u2.age  where u1.id = 1;
-- ========== 没有索引测试
-- user2.age没有索引 Using where; Using join buffer (Block Nested Loop) 
explain
SELECT u1.* from user u1 left join user2 u2 on u1.age = u2.age;

-- user2.age没有索引 Using where
explain
SELECT u1.* from user u1 left join user2 u2 on u1.age = u2.age where u1.id = 1;


结论：关联查询中，关联字段如果没有索引,会导致后面的索引都失效

Using where; Using index的释意：

• using index，查询时不需要回表查询，直接通过索引就可以获得数据；如果同时出现了using where表示索引用来执行查找值，没有出现using where说明索引是用来读取数据的；效率不错

• using where，出现这个表示数据在server层过滤，反之是存储引擎存过滤；效率较低

其他相关信息

建议：MySQL部分结论受版本影响，有争议的建议注明版本

[MengXiangDing]

hi,我的MySQL版本是8.0.18
对于你的这个结论 ：( 结论：隐式转换，where column_name=2 id是字符串类型，相当于使用字符串转整型，不用索引；而整数转字符串会用到索引 ) ，在我这个版本好像是反的。

第一个是整数转字符串，没用到索引。第二个是字符串(也就是说没有隐式转换)，用到了索引

[MengXiangDing]

这是表的字段说明