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

测试积点老人

Mysql两表关联不使用索引的问题
两张表test_a，test_b结构和索引信息如下，通过主键inner join关联时，外表为什么不走索引呢？

1. <p>create table test_a (
   
2.   id int,
   
3.   birthday date not null,
   
4.   comment varchar (50) not null,
   
5.   primary key test_a_pk (id),
   
6.   index test_a_index (birthday)
   
7. ) engine = innodb default charset = utf8;</p><p>create table test_b (
   
8.   id int,
   
9.   salary double not null,
   
10.   struct varchar (50) not null,
    
11.   primary key test_b_pk (id),
    
12.   index test_b_index (salary)
    
13. ) engine = innodb default charset = utf8;</p><p>mysql> show index from test_a\G
    
14. *************************** 1. row ***************************
    
15.         Table: test_a
    
16.    Non_unique: 0
    
17.      Key_name: PRIMARY
    
18. Seq_in_index: 1
    
19.   Column_name: id
    
20.     Collation: A
    
21.   Cardinality: 5375084
    
22.      Sub_part: NULL
    
23.        Packed: NULL
    
24.          Null:
    
25.    Index_type: BTREE
    
26.       Comment:
    
27. Index_comment:
    
28. *************************** 2. row ***************************
    
29.         Table: test_a
    
30.    Non_unique: 1
    
31.      Key_name: test_a_index
    
32. Seq_in_index: 1
    
33.   Column_name: birthday
    
34.     Collation: A
    
35.   Cardinality: 2728
    
36.      Sub_part: NULL
    
37.        Packed: NULL
    
38.          Null:
    
39.    Index_type: BTREE
    
40.       Comment:
    
41. Index_comment:
    
42. 2 rows in set (0.00 sec)</p><p>mysql> show index from test_b\G
    
43. *************************** 1. row ***************************
    
44.         Table: test_b
    
45.    Non_unique: 0
    
46.      Key_name: PRIMARY
    
47. Seq_in_index: 1
    
48.   Column_name: id
    
49.     Collation: A
    
50.   Cardinality: 5291561
    
51.      Sub_part: NULL
    
52.        Packed: NULL
    
53.          Null:
    
54.    Index_type: BTREE
    
55.       Comment:
    
56. Index_comment:
    
57. *************************** 2. row ***************************
    
58.         Table: test_b
    
59.    Non_unique: 1
    
60.      Key_name: test_b_index
    
61. Seq_in_index: 1
    
62.   Column_name: salary
    
63.     Collation: A
    
64.   Cardinality: 943640
    
65.      Sub_part: NULL
    
66.        Packed: NULL
    
67.          Null:
    
68.    Index_type: BTREE
    
69.       Comment:
    
70. Index_comment:
    
71. 2 rows in set (0.00 sec)</p>

复制代码

海海豚

https://bbs.csdn.net/topics/397786880?list=78425671  参考下

qqq911

检查下是否都做了索引

郭小贱

可以参考这篇文章看下：https://q.cnblogs.com/q/129478/