建表
mysql> create table class(
-> id int(10) not null auto_increment,
-> card int(10) not null,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.21 sec)
mysql> create table book(
-> bookid int(10) not null auto_increment,
-> card int(10) not null,
-> primary key(bookid)
-> );
Query OK, 0 rows affected (0.18 sec)
//插入数据
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into class(card) values(1+(rand()*20));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)))
//查看数据
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
| 1 | 7 |
| 2 | 7 |
| 3 | 12 |
| 4 | 19 |
| 5 | 1 |
| 6 | 5 |
| 7 | 3 |
| 8 | 20 |
| 9 | 11 |
| 10 | 16 |
| 11 | 4 |
| 12 | 12 |
| 13 | 10 |
| 14 | 10 |
| 15 | 1 |
| 16 | 14 |
| 17 | 6 |
| 18 | 6 |
| 19 | 14 |
| 20 | 11 |
+--------+------+
20 rows in set (0.00 sec)
mysql> select * from class;
+----+------+
| id | card |
+----+------+
| 1 | 15 |
| 2 | 10 |
| 3 | 3 |
| 4 | 2 |
| 5 | 3 |
| 6 | 5 |
| 7 | 15 |
| 8 | 20 |
| 9 | 15 |
| 10 | 14 |
| 11 | 4 |
| 12 | 16 |
| 13 | 7 |
| 14 | 4 |
| 15 | 2 |
| 16 | 14 |
| 17 | 3 |
| 18 | 12 |
| 19 | 11 |
| 20 | 16 |
+----+------+
20 rows in set (0.00 sec)
案例1分析
两张表数据没有任何实际意义,案例也没有任何意义,只是为了出问题,解决问题关于案例并不由纠结太多- - #
1.使用left join 查询两张表
mysql> explain select * from class left join book on class.card=book.card;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 20 | NULL |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
我们发现出现了全表扫描,先来解决这个问题,但是涉及到了两张表我们应该在那张表来建立索引,大胆假设,小心求证我们一一来尝试一下就知道了首先我们选择book的card来建立索引
mysql> create index idx_book_c on book(card);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
我们在尝试一下 使用左连接查询在右表建立索引
mysql> explain select * from class left join book on class.card=book.card;
+----+-------------+-------+------+---------------+------------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-----------------+------+-------------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 20 | NULL |
| 1 | SIMPLE | book | ref | idx_book_c | idx_book_c | 4 | test.class.card | 1 | Using index |
+----+-------------+-------+------+---------------+------------+---------+-----------------+------+-------------+
2 rows in set (0.00 sec)
我们发现book全表扫描已经变成了ref 也使用到了索引,且查询行数变成了1 已经有明显的改变,我们在尝试一下在左表建立索引对比一下
//删除索引
drop index idx_book_c on book;
//在左表建立索引
create index idx_class_c on class(card);
//再次尝试
mysql> explain select * from class left join book on class.card=book.card;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | class | index | NULL | idx_class_c | 4 | NULL | 20 | Using index |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
我们发现type 为index 且 row20 并没有得到优化
案例1 结论
由于是左连接,left join 条件用于确定如何草右表搜索行,左边一定都有,所以右边使我们的关键点,一定需要建立索引.
索引我们还是删除之前的索引
mysql> drop index idx_class_c on class;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
//还是在右表建立索引吧
mysql> create index idx_book_c on book(card);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
//发现回到了刚开始的样子 - - !
mysql> explain select * from class left join book on class.card=book.card;
+----+-------------+-------+------+---------------+------------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-----------------+------+-------------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 20 | NULL |
| 1 | SIMPLE | book | ref | idx_book_c | idx_book_c | 4 | test.class.card | 1 | Using index |
+----+-------------+-------+------+---------------+------------+---------+-----------------+------+-------------+
2 rows in set (0.00 sec)
##案例2
不想多说了同理 ,如果采用右连接 当然是左表建立索引比较好