MySQL 查询“陷阱”

在我们平时使用MySQL 过程中，我们经常会使用到索引来提高查询速度，但是有时候我们会因为不注意细节落入“陷阱”。

1、查询时，where条件列的类型和列定义的类型不一致

看如下例子：

我们先准备下数据

create table test_1(
  id varchar(11),
  user_name varchar(20),
  PRIMARY KEY (`id`)
)

insert into test_1 values('1000001','colin1');
insert into test_1 values('1000002','colin2');
insert into test_1 values('1000003','colin3');

首先，我们使用正确的格式查询，可以看到正常走索引，而且是const 类型

然后，我们使用不正确的格式查询，发现没有走索引，而是全表扫描

那是不是 所有的where条件列的类型和列定义的类型不一致，都会导致索引失效呢？

我们用事实说话

首先，准备数据,    看到和test_1 区别了没？就id 列不一样。

create table test_2(
  id int(11),
  user_name varchar(20),
  PRIMARY KEY (`id`)
);

insert into test_2 values(1000001,'colin1');
insert into test_2 values(1000002,'colin2');
insert into test_2 values(1000003,'colin3');

首先，我们使用正确的格式查询，可以看到正常走索引，而且是const 类型

然后，我们使用不正确的格式查询，可以看到还是正常走索引，而且是const 类型。

推测 应该是mysql 优化器做了优化处理。

总结：

虽然不是所有的查询筛选条件列的类型和列定义不一致都会导致索引失效，但是为了保险，还是建议大家  查询筛选条件列的类型和列定义保持一致

2、查询时，2个表 join列的类型类型不一致

表和数据我们还用上面的test_1 和test_2

可以看到，两个表的id列虽然都是主键索引，但是因为 test_1 表的id 列是varchar 类型，test_2 表的id列是int类型，类型不匹配导致索引失效。

varchar 和int 不一致，会导致索引失效，

那么，一些老系统在升级时经常会将int 升级成bigint ,   int和bigint 不一致会导致索引失效吗？ 我们拿事实说话

首先准备数据

create table test_3(
  id bigint(11),
  user_name varchar(20),
  PRIMARY KEY (`id`)
);

insert into test_3 values(1000001,'colin1');
insert into test_3 values(1000002,'colin2');
insert into test_3 values(1000003,'colin3');

可以看到int 和bigint 类型 不一致，也是会导致索引失效的

总结：

多表连接时，连接的列类型最好都有索引，并且列的类型必须保持一致