mysql 关联表多条件 mysql多表关联查询优化

转载

mob64ca13fe9c58 2023-08-19 18:34:29

文章标签 mysql 关联表多条件 MySQL 多表查询语句性能优化 ci java 文章分类 MySQL 数据库

今天我们动手练习下，两个表或多表结合查询的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级别了，性能也大大增加了，这里我们的例子是两张表结合的优化，而三四张表结合的原理都是一样的，小表驱动大表，将索引加在经常使用的字段上。

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