MySQL的多表关联查询SQL语句优化实例

    今天我们动手练习下，两个表或多表结合查询的SQL语句是怎样优化的。

还是我们先创建两个表和准备一些数据

create table teacher(
  tid int(4) primary key,
  cid int(4) not null
);
insert teacher values(1,2);
insert teacher values(2,1);
insert teacher values(3,3);

create table course(
  cid int(4) ,
  cname varchar(20)
);
insert course values(1,'java');
insert course values(2,'javaweb');
insert course values(3,'spring');

    下边我们来写一个连接的查询语句，并查看执行计划。

mysql> explain select * from teacher t left outer join course c on t.cid=c.cid where c.cname='java';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where                                |
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

    从执行计划结果看到type是all，说明没有进行任何优化，而且出现了using join buffer，它是什么意思呢，mysql逻辑分层中有一个sql优化器，由于我们写的sql性能太差了，它为我们自动添加了连接缓存。

    那我们怎样优化呢，就算加索引应该往哪个表加，记住下边的规则。

• 小表驱动大表，当编写sql语句时，先写小表，再写大表
• 索引加在经常使用的字段上
• 左连接给左表加索引，右连接给右表加索引

    我们继续往下说，按照上边的规则，我们要加索引的话需要往左连接的表中追加，就是给teacher表的cid加索引后，在来看下执行计划。

alter table teacher add index index_cid(cid);

mysql> explain select * from teacher t left outer join course c on t.cid=c.cid where c.cname='java';
+----+-------------+-------+------------+------+---------------+-----------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL      | NULL    | NULL                |    3 |    33.33 | Using where |
|  1 | SIMPLE      | t     | NULL       | ref  | index_cid     | index_cid | 4       | explain_test1.c.cid |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

    从这个执行计划结果来看，teacher表已经优化到type是ref级别，效率大大提升了，由于检索条件是按cname检索，我们来试试往cname上在加一个索引后，我们再来分析下执行计划。

alter table course add index index_cname(cname);

mysql> explain select * from teacher t left outer join course c on t.cid=c.cid where c.cname='java';
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ref  | index_cname   | index_cname | 83      | const               |    1 |   100.00 | Using where |
|  1 | SIMPLE      | t     | NULL       | ref  | index_cid     | index_cid   | 4       | explain_test1.c.cid |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

    也同样，两张表已经提升到ref级别了，性能也大大增加了，这里我们的例子是两张表结合的优化，而三四张表结合的原理都是一样的，小表驱动大表，将索引加在经常使用的字段上。

    在文章最后，我们已怎样操作索引字段的小技巧来结束今天的文章。

• 复合索引不要跨列或无序使用
• 尽量使用全索引匹配
• 不要在索引上进行任何操作，例如计算，函数，类型转换(显式或隐式)等
• 索引列不能使用不等于(!= ，<>)或者大于(>)或者小于(<)或者is null或者is not nul
• 索引优化时尽量达到using index
• 写like语句时不要以%开头
• 索引列不能使用or