MySQL join 连表查询索引问题

首先先创建两个临时表，并加一条基础数据进去

create table user
(
  id              int auto_increment comment '自增主键' primary key,
  name            varchar(30) null comment '用户名称',
  create_time     datetime    not null  comment '注册时间',
  last_login_time datetime    null  comment '最后登录时间'
)  comment '测试表';
create table article
(
  id          int auto_increment  comment '自增主键'    primary key,
  user_id     int          not null  comment '用户id',
  name        varchar(30)  null  comment '帖子名称',
  topic       varchar(30)  null  comment '帖子关键词',
  content     varchar(500) null  comment '帖子内容',
  create_time datetime     not null  comment '创建时间'
)  comment '测试表2';*/insert into article values(1,1, 'name_1', 'topic_1', 'content_1', '2019-01-01 00:00:00');
insert into user values(1,'user_1', '2019-01-01 00:00:00', '2019-03-01 12:00:00');

 

为了能模拟大查询的情况，给每个表插入一些数据，user要有万级数据量，article要有百万级数据量，下面的sql每执行一次，数据量翻倍，谨慎执行！

set @i=1;
set @time=1;
insert into user(name, create_time, last_login_time)
select
       concat('user_',@i:=@i+1),
       date_add(create_time,interval +@time*cast(rand()*100 as signed) SECOND),
       null
from user;
select count(1) from user;

set @i=1;
set @time=1;
insert into article(user_id, name, topic, content, create_time)
select
       round(rand()*(select max(id) from user)),
       concat('name_',@i:=@i+1),
       concat('topic_',@i:=@i+1),
       concat('content_',@i:=@i+1),
       date_add(create_time,interval +@time*cast(rand()*100 as signed) SECOND)
from article;
select count(1) from article;

 

看下查询的SQL语句

select sql_no_cache  * from user left join article on(user.id = article.user_id)
where  user.name like 'user_4%';

没有使用缓存，user表的id是主键，article表除主键外没有任何索引，这种情况下，百万级数据查询情况如下

sql> select sql_no_cache  * from user left join article on(user.id = article.user_id)
     where  user.name like 'user_4%'
[2020-05-17 13:24:45] 500 rows retrieved starting from 1 in 4 s 681 ms (execution: 1 s 312 ms, fetching: 3 s 369 ms)

 

给article表加个索引

CREATE INDEX user_id ON article (user_id);

 

再执行一下看下效果

sql> select sql_no_cache  * from user left join article on(user.id = article.user_id)
     where  user.name like 'user_4%'
[2020-05-17 13:27:22] 500 rows retrieved starting from 1 in 142 ms (execution: 112 ms, fetching: 30 ms)

 

可以看出，加了索引后，时间缩短了97%

结论：

A表与B表使用Join联合查询的时候，针对on里面的字段加与不加索引的效率（假设on的条件是A.id=B.aid）：

1、两个字段都不加索引，效率极低

2、A表的字段加了索引，B表的字段没有加索引，效果同上

3、A表B表的字段都加了索引，效果很明显

4、A表不加索引，B表加了索引，效果同上

 

参考：

资料一

资料二