溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
1、測試環境：MySQL 5.7.17
2、測試表結構
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、兩張表的數據量
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、查看執行計劃
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數據，使兩表數據量一樣，查看執行計劃，發現第三條語句的執行計劃發生了變化
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條數據，使a表數據量大于b表，查看執行計劃，三條語句執行計劃都發現了變化
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)
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
