两张表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)
“EQ_REF”就表示已经使用了主键索引了吧,建议做个实验,连接的另一个非主键id试试速度是否差不多。
a表使用索引了,但是b表却是全表扫描呢
@Hedwiglzy: 哦,没有可以利用索引的查询条件,用join总有一个表是全表是全表扫描的,另一个表可能用索引,不过我不知道为什么是B全表扫描,而不是A表,难道B中数据少。如果你加个查询条件, where a.Id = xx,估计就用到索引了。
join是这样一个过程:如果没有可利用索引的查询条件,首先把一个表满足条件的都查出来(全扫描),然后再遍历查出来的结果,用用来join的列去另一个表里查(用到了用来join的b表的列的索引)。看起来你的执行计划是合理的。
再举个例子,比如你和你老婆要比谁的宝物好,你依次拿出一个宝物。你老婆根据你拿出的宝物去她的百宝箱中取相同的。这个过程中,你是全表扫描,你老婆是根据你的宝物去搜索她的宝物,可以用到索引,如果有的话。
如果你拿宝物时用到了查询条件:只拿黄金做的宝物,那如果你的宝物上有成分索引,也可以用到索引。
@会长: join这块不太理解,为啥不用a,b两张表的主键索引直接查找数据,而是全表扫描b,再去通过a上面的主键索引查找数据
@Hedwiglzy: 再看我的答案,我举了个例子
@Hedwiglzy: 你没有加任何查询条件,当然得有一个表全表扫描阿。假设让你编程实现一个Join,你怎么实现,不也得遍历一遍。遍历A,然后根据A中取出的数据查B,查B的过程中可能用到索引,如果有索引的。
@会长: 我执行了下面的语句,只查询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: 不懂了,你研究吧,研究好了写一篇博文,发来我们也学习一下。
@Hedwiglzy: 你看下索引的类型是index, 其实和ALL类型基本相同,都会进行全扫描。区别在于index只有索引树被扫描了,因为查询的字段id正好是主键索引,所以只需要使用索引树中的id信息而不需要查询全表的数据。
我用上面的sql语句创建的表,查询语句跟上面的也一样,但输出结果却相反,说明不是外表不用索引的问题,而是有一个表用了索引
输出结果相反应该是你的表里面没数据,我的表里面有数据,mysql优化器选择用小表去驱动大表,所以我的a表是内部表,b表是外部表
@Hedwiglzy: 两个表里面各有一条记录,上面的rows显示的是1
mysql不是有优化器么,有时候可能会分析到你这走索引还没有全表扫描效率高,就直接全表扫描了么