1、測(cè)試環(huán)境：MySQL 5.7.17
成都創(chuàng)新互聯(lián)長(zhǎng)期為超過(guò)千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開(kāi)放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為平和企業(yè)提供專(zhuān)業(yè)的網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站建設(shè)，平和網(wǎng)站改版等技術(shù)服務(wù)。擁有十多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開(kāi)發(fā)。
2、測(cè)試表結(jié)構(gòu)
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)
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、兩張表的數(shù)據(jù)量
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、查看執(zhí)行計(jì)劃
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)
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)
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數(shù)據(jù)，使兩表數(shù)據(jù)量一樣，查看執(zhí)行計(jì)劃，發(fā)現(xiàn)第三條語(yǔ)句的執(zhí)行計(jì)劃發(fā)生了變化
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)
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)
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)
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條數(shù)據(jù)，使a表數(shù)據(jù)量大于b表，查看執(zhí)行計(jì)劃，三條語(yǔ)句執(zhí)行計(jì)劃都發(fā)現(xiàn)了變化
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)
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)
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)
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)
重慶分公司
重慶分公司
