这里我们分成三种情况进行分析，分别是单表，两表，三表
1.单表
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) NOT NULL,
`category_id` INT(10) NOT NULL,
`views` INT(10) NOT NULL,
`comments` INT(10) NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
mysql> select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 1 | 1 | 3 | 3 | 3 | 3 |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)
案例
#查询category_id为1且comments大于1的情况下，views最多的atticle_id
mysql> EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | article | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
mysql> show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
#可以看到，type是All，是最坏的情况，extra里还出现了Using filesort，也是最坏的情况，优化是必须的~
#开始优化
#新建索引
CREATE INDEX idx_article_ccv ON `article`(`category_id`,`comments`,`views`)
mysql> show index from article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 1 | category_id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 2 | comments | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 3 | views | A | 3 | NULL | NULL | | BTREE | | |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | article | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | Using index condition; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)
虽然全表扫描的问题解决了，但是Using filesort问题没有解决~
因为comments>1这个范围导致后面的views索引失效
重新建索引
mysql> show index from article;
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_cv | 1 | category_id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_cv | 2 | views | A | 3 | NULL | NULL | | BTREE | | |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)
mysql> EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| 1 | SIMPLE | article | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | Using where |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
这样就比较好接受了
2.两张表
CREATE TABLE IF NOT EXISTS `agency`(
`agency_id` INT(10) NOT NULL AUTO_INCREMENT,
`guide_id` INT(10) NOT NULL,
PRIMARY KEY(`agency_id`)
);
CREATE TABLE IF NOT EXISTS `language`(
`language_id` INT(10) NOT NULL AUTO_INCREMENT,
`guide_id` INT(10) NOT NULL,
PRIMARY KEY(`language_id`)
);
mysql> select * from language;
+-------------+----------+
| language_id | guide_id |
+-------------+----------+
| 1 | 10 |
| 2 | 7 |
| 3 | 3 |
| 4 | 13 |
| 5 | 17 |
| 6 | 4 |
| 7 | 9 |
| 8 | 19 |
| 9 | 16 |
| 10 | 20 |
+-------------+----------+
10 rows in set (0.00 sec) mysql> select * from agency;
+-----------+----------+
| agency_id | guide_id |
+-----------+----------+
| 1 | 2 |
| 2 | 18 |
| 3 | 3 |
| 4 | 20 |
| 5 | 15 |
| 6 | 11 |
| 7 | 13 |
| 8 | 4 |
| 9 | 14 |
| 10 | 10 |
+-----------+----------+
10 rows in set (0.00 sec)
下面开始explain分析
mysql> explain select * from language left join agency on language.guide_id=agency.guide_id;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | language | ALL | NULL | NULL | NULL | NULL | 10 | NULL |
| 1 | SIMPLE | agency | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
type是All，全表扫描
(1)我们首先在TableB也就是agency表加索引(左连接加在右表上)
mysql> alter table `agency` add index `idx_agency`(`guide_id`);
此时再查看一下执行计划
mysql> explain select * from language left join agency on language.guide_id=agency.guide_id;
+----+-------------+----------+------+---------------+------------+---------+--------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------------+---------+--------------------------+------+-------------+
| 1 | SIMPLE | language | ALL | NULL | NULL | NULL | NULL | 10 | NULL |
| 1 | SIMPLE | agency | ref | idx_agency | idx_agency | 4 | db0629.language.guide_id | 1 | Using index |
+----+-------------+----------+------+---------------+------------+---------+--------------------------+------+-------------+
2 rows in set (0.00 sec)
删除索引idx_agency，在左表创建索引
mysql> drop index idx_agency on agency;
mysql> alter table language add index ldx_language(guide_id);
mysql> explain select * from language left join agency on language.guide_id=agency.guide_id;
+----+-------------+----------+-------+---------------+--------------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+--------------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | language | index | NULL | ldx_language | 4 | NULL | 10 | Using index |
| 1 | SIMPLE | agency | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+-------+---------------+--------------+---------+------+------+----------------------------------------------------+
这是由左连接特性决定的，left join条件用于确定如何从右表搜索行，左表一定都有，所以右表一定要创建索引~
同理，right join中右表中的数据全都有，所以索引建在左表上
3.三张表的情况
mysql> explain select * from agency left join language on agency.guide_id=language.guide_id left join contact on language.guide_id=contact.guide_id;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | agency | ALL | NULL | NULL | NULL | NULL | 10 | NULL |
| 1 | SIMPLE | language | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | contact | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
3 rows in set (0.00 sec)
全部全表扫描
加索引
mysql> alter table language add index idx_language(guide_id);
mysql> alter table contact add index idx_contact(guide_id);
mysql> explain select * from agency left join language on agency.guide_id=language.guide_id left join contact on language.guide_id=contact.guide_id;
+----+-------------+----------+------+---------------+--------------+---------+--------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+--------------+---------+--------------------------+------+-------------+
| 1 | SIMPLE | agency | ALL | NULL | NULL | NULL | NULL | 10 | NULL |
| 1 | SIMPLE | language | ref | idx_language | idx_language | 4 | db0629.agency.guide_id | 1 | Using index |
| 1 | SIMPLE | contact | ref | idx_contact | idx_contact | 4 | db0629.language.guide_id | 1 | Using index |
+----+-------------+----------+------+---------------+--------------+---------+--------------------------+------+-------------+
3 rows in set (0.00 sec)