这里我们分成三种情况进行分析，分别是单表，两表，三表
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)