首页 新闻 会员 周边

Mysql两表关联不使用索引的问题

0
悬赏园豆:20 [已解决问题] 解决于 2020-11-05 15:36

两张表test_a,test_b结构和索引信息如下,通过主键inner join关联时,外表为什么不走索引呢?

create table test_a (
  id int,
  birthday date not null,
  comment varchar (50) not null,
  primary key test_a_pk (id),
  index test_a_index (birthday) 
) engine = innodb default charset = utf8;

create table test_b (
  id int,
  salary double not null,
  struct varchar (50) not null,
  primary key test_b_pk (id),
  index test_b_index (salary) 
) engine = innodb default charset = utf8;

mysql> show index from test_a\G
*************************** 1. row ***************************
        Table: test_a
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 5375084
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: test_a
   Non_unique: 1
     Key_name: test_a_index
 Seq_in_index: 1
  Column_name: birthday
    Collation: A
  Cardinality: 2728
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

mysql> show index from test_b\G
*************************** 1. row ***************************
        Table: test_b
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 5291561
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: test_b
   Non_unique: 1
     Key_name: test_b_index
 Seq_in_index: 1
  Column_name: salary
    Collation: A
  Cardinality: 943640
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

Hedwiglzy的主页 Hedwiglzy | 初学一级 | 园豆:147
提问于:2020-09-28 09:50
< >
分享
最佳答案
0

“EQ_REF”就表示已经使用了主键索引了吧,建议做个实验,连接的另一个非主键id试试速度是否差不多。

收获园豆:20
会长 | 专家六级 |园豆:12401 | 2020-09-28 09:57

a表使用索引了,但是b表却是全表扫描呢

Hedwiglzy | 园豆:147 (初学一级) | 2020-09-28 10:27

@Hedwiglzy: 哦,没有可以利用索引的查询条件,用join总有一个表是全表是全表扫描的,另一个表可能用索引,不过我不知道为什么是B全表扫描,而不是A表,难道B中数据少。如果你加个查询条件, where a.Id = xx,估计就用到索引了。

join是这样一个过程:如果没有可利用索引的查询条件,首先把一个表满足条件的都查出来(全扫描),然后再遍历查出来的结果,用用来join的列去另一个表里查(用到了用来join的b表的列的索引)。看起来你的执行计划是合理的。

再举个例子,比如你和你老婆要比谁的宝物好,你依次拿出一个宝物。你老婆根据你拿出的宝物去她的百宝箱中取相同的。这个过程中,你是全表扫描,你老婆是根据你的宝物去搜索她的宝物,可以用到索引,如果有的话。

如果你拿宝物时用到了查询条件:只拿黄金做的宝物,那如果你的宝物上有成分索引,也可以用到索引。

会长 | 园豆:12401 (专家六级) | 2020-09-28 10:32

@会长: join这块不太理解,为啥不用a,b两张表的主键索引直接查找数据,而是全表扫描b,再去通过a上面的主键索引查找数据

Hedwiglzy | 园豆:147 (初学一级) | 2020-09-28 10:39

@Hedwiglzy: 再看我的答案,我举了个例子

会长 | 园豆:12401 (专家六级) | 2020-09-28 10:42

@Hedwiglzy: 你没有加任何查询条件,当然得有一个表全表扫描阿。假设让你编程实现一个Join,你怎么实现,不也得遍历一遍。遍历A,然后根据A中取出的数据查B,查B的过程中可能用到索引,如果有索引的。

会长 | 园豆:12401 (专家六级) | 2020-09-28 10:44

@会长: 我执行了下面的语句,只查询b表的id,执行计划中就显示用到了b表的test_b_index,这里为什么就会用到外部表的索引呢?而且很奇怪的是我明明查询的id,显示的却是test_b_index,extra显示的是覆盖索引,但是test_b_index是建在salary列上的

explain select b.id from test_a a inner join test_b b on a.id = b.id

Hedwiglzy | 园豆:147 (初学一级) | 2020-09-28 14:19

@Hedwiglzy: 不懂了,你研究吧,研究好了写一篇博文,发来我们也学习一下。

会长 | 园豆:12401 (专家六级) | 2020-09-28 14:42

@Hedwiglzy: 你看下索引的类型是index, 其实和ALL类型基本相同,都会进行全扫描。区别在于index只有索引树被扫描了,因为查询的字段id正好是主键索引,所以只需要使用索引树中的id信息而不需要查询全表的数据。

ExcuMe | 园豆:200 (初学一级) | 2023-03-14 11:45
其他回答(2)
0

我用上面的sql语句创建的表,查询语句跟上面的也一样,但输出结果却相反,说明不是外表不用索引的问题,而是有一个表用了索引

图片

E行者 | 园豆:1761 (小虾三级) | 2020-09-28 11:46

输出结果相反应该是你的表里面没数据,我的表里面有数据,mysql优化器选择用小表去驱动大表,所以我的a表是内部表,b表是外部表

支持(0) 反对(0) Hedwiglzy | 园豆:147 (初学一级) | 2020-09-28 14:21

@Hedwiglzy: 两个表里面各有一条记录,上面的rows显示的是1

支持(0) 反对(0) E行者 | 园豆:1761 (小虾三级) | 2020-09-28 15:18
0

mysql不是有优化器么,有时候可能会分析到你这走索引还没有全表扫描效率高,就直接全表扫描了么

吉吉的城 | 园豆:566 (小虾三级) | 2020-09-29 11:36
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册