1、测试环境：MySQL 5.7.17
2、测试表结构
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show
create
table
a;
+
-------+--------------------------------------------------------------------------------------------------------------------------------------+
|
Table
|
Create
Table
|
+
-------+--------------------------------------------------------------------------------------------------------------------------------------+
| a |
CREATE
TABLE
`a` (
`id`
int
(11)
NOT
NULL
,
`
name
`
char
(20)
DEFAULT
NULL
,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB
DEFAULT
CHARSET=gbk |
+
-------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
|
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show
create
table
b;
+
-------+------------------------------------------------------------------------------------------------------------------------------------+
|
Table
|
Create
Table
|
+
-------+------------------------------------------------------------------------------------------------------------------------------------+
| b |
CREATE
TABLE
`b` (
`id`
int
(11)
NOT
NULL
,
`tx`
char
(20)
DEFAULT
NULL
,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB
DEFAULT
CHARSET=gbk |
+
-------+------------------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
|
3、两张表的数据量
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql>
select
count
(*)
from
a;
+
----------+
|
count
(*) |
+
----------+
| 7 |
+
----------+
1 row
in
set
(0.00 sec)
mysql>
select
count
(*)
from
b;
+
----------+
|
count
(*) |
+
----------+
| 10 |
+
----------+
1 row
in
set
(0.00 sec)
|
4、查看执行计划
|
1
2
3
4
5
6
7
8
|
mysql> explain
select
name
from
a,b
where
a.id=b.id;
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | a |
NULL
|
ALL
|
PRIMARY
|
NULL
|
NULL
|
NULL
| 7 | 100.00 |
NULL
|
| 1 | SIMPLE | b |
NULL
| eq_ref |
PRIMARY
|
PRIMARY
| 4 | apex.a.id | 1 | 100.00 | Using
index
|
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
2
rows
in
set
, 1 warning (0.00 sec)
|
|
1
2
3
4
5
6
7
8
|
mysql> explain
select
name
from
a,b
where
b.id=a.id;
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | a |
NULL
|
ALL
|
PRIMARY
|
NULL
|
NULL
|
NULL
| 7 | 100.00 |
NULL
|
| 1 | SIMPLE | b |
NULL
| eq_ref |
PRIMARY
|
PRIMARY
| 4 | apex.a.id | 1 | 100.00 | Using
index
|
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
2
rows
in
set
, 1 warning (0.00 sec)
|
|
1
2
3
4
5
6
7
8
|
mysql> explain
select
name
from
b,a
where
b.id=a.id;
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | a |
NULL
|
ALL
|
PRIMARY
|
NULL
|
NULL
|
NULL
| 7 | 100.00 |
NULL
|
| 1 | SIMPLE | b |
NULL
| eq_ref |
PRIMARY
|
PRIMARY
| 4 | apex.a.id | 1 | 100.00 | Using
index
|
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
2
rows
in
set
, 1 warning (0.00 sec)
|
5、向a表插入3数据，使两表数据量一样，查看执行计划，发现第三条语句的执行计划发生了变化
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql>
insert
into
a
values
(8,
'test'
);
Query OK, 1 row affected (0.00 sec)
mysql>
insert
into
a
values
(9,
'test'
);
Query OK, 1 row affected (0.00 sec)
mysql>
insert
into
a
values
(10,
'test'
);
Query OK, 1 row affected (0.01 sec)
mysql>
select
count
(*)
from
a;
+
----------+
|
count
(*) |
+
----------+
| 10 |
+
----------+
1 row
in
set
(0.00 sec)
|
|
1
2
3
4
5
6
7
8
|
mysql> explain
select
name
from
a,b
where
a.id=b.id;
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | a |
NULL
|
ALL
|
PRIMARY
|
NULL
|
NULL
|
NULL
| 10 | 100.00 |
NULL
|
| 1 | SIMPLE | b |
NULL
| eq_ref |
PRIMARY
|
PRIMARY
| 4 | apex.a.id | 1 | 100.00 | Using
index
|
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
2
rows
in
set
, 1 warning (0.00 sec)
|
|
1
2
3
4
5
6
7
8
|
mysql> explain
select
name
from
a,b
where
b.id=a.id;
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | a |
NULL
|
ALL
|
PRIMARY
|
NULL
|
NULL
|
NULL
| 10 | 100.00 |
NULL
|
| 1 | SIMPLE | b |
NULL
| eq_ref |
PRIMARY
|
PRIMARY
| 4 | apex.a.id | 1 | 100.00 | Using
index
|
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
2
rows
in
set
, 1 warning (0.00 sec)
|
|
1
2
3
4
5
6
7
8
|
mysql> explain
select
name
from
b,a
where
b.id=a.id;
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | b |
NULL
|
index
|
PRIMARY
|
PRIMARY
| 4 |
NULL
| 10 | 100.00 | Using
index
|
| 1 | SIMPLE | a |
NULL
| eq_ref |
PRIMARY
|
PRIMARY
| 4 | apex.b.id | 1 | 100.00 |
NULL
|
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
2
rows
in
set
, 1 warning (0.00 sec)
|
6、向a表插入1条数据，使a表数据量大于b表，查看执行计划，三条语句执行计划都发现了变化
|
1
2
3
4
5
6
7
8
9
|
mysql>
insert
into
a
values
(11,
'test'
);
Query OK, 1 row affected (0.01 sec)
mysql>
select
count
(*)
from
a;
+
----------+
|
count
(*) |
+
----------+
| 11 |
+
----------+
1 row
in
set
(0.00 sec)
|
|
1
2
3
4
5
6
7
8
|
mysql> explain
select
name
from
a,b
where
a.id=b.id;
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | b |
NULL
|
index
|
PRIMARY
|
PRIMARY
| 4 |
NULL
| 10 | 100.00 | Using
index
|
| 1 | SIMPLE | a |
NULL
| eq_ref |
PRIMARY
|
PRIMARY
| 4 | apex.b.id | 1 | 100.00 |
NULL
|
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
2
rows
in
set
, 1 warning (0.00 sec)
|
|
1
2
3
4
5
6
7
8
|
mysql> explain
select
name
from
a,b
where
b.id=a.id;
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | b |
NULL
|
index
|
PRIMARY
|
PRIMARY
| 4 |
NULL
| 10 | 100.00 | Using
index
|
| 1 | SIMPLE | a |
NULL
| eq_ref |
PRIMARY
|
PRIMARY
| 4 | apex.b.id | 1 | 100.00 |
NULL
|
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
2
rows
in
set
, 1 warning (0.00 sec)
|
|
1
2
3
4
5
6
7
8
|
mysql> explain
select
name
from
b,a
where
b.id=a.id;
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type |
table
| partitions | type | possible_keys |
key
| key_len | ref |
rows
| filtered | Extra |
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | b |
NULL
|
index
|
PRIMARY
|
PRIMARY
| 4 |
NULL
| 10 | 100.00 | Using
index
|
| 1 | SIMPLE | a |
NULL
| eq_ref |
PRIMARY
|
PRIMARY
| 4 | apex.b.id | 1 | 100.00 |
NULL
|
+
----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
2
rows
in
set
, 1 warning (0.01 sec)
|
本文转自 corasql 51CTO博客，原文链接：http://blog.51cto.com/corasql/1913105，如需转载请自行联系原作者
