show create table a; +-------+..."/>
您好,登錄后才能下訂單哦!
1、測(cè)試環(huán)境:MySQL 5.7.17
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)
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。