从Mysql EXPLAIN探寻数据库查询优化(五)
|
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
| 1 | SIMPLE | C | index | PRIMARY | PRIMARY | 4 | NULL | 2 | Using index |
| 1 | SIMPLE | A | ALL | idx_catid | NULL | NULL | NULL | 46585 | Using where; Using join buffer |
| 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test.A.catid | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
3 rows in set (0.00 sec)
这里显示了Mysql先将C表读入查询，并使用PRIMARY索引，然后联合A表进行查询，这时候type显示的是ALL，可以用的索引有idx_catid，但是实际没有用。 www.2cto.com
原因非常明显，因为使用的连接条件是A.sectionid=C.id，所以我们给A.sectionid加个索引先。
mysql> alter table jos_content add index idx_section(`sectionid`);
Query OK, 46585 rows affected (0.89 sec)
Records: 46585 Duplicates: 0 Warnings: 0
mysql> explain select A.id,A.title,B.title from jos_content A,jos_categories B,jos_sections C where A.catid=B.id and A.sectionid=C.id;
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
| 1 | SIMPLE | C | index | PRIMARY | PRIMARY | 4 | NULL | 2 | Using index |
| 1 | SIMPLE | A | ref | idx_catid,idx_section | idx_section | 4 | joomla_test.C.id | 23293 | Using where |
| 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test.A.catid | 1 | Using where |
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
3 rows in set (0.00 sec)
这时候显示结果告诉我们，效果很明显，在连接A表时type变成了ref，索引使用了idx_section，如果我们注意看后两列，对A表的查询结果后一次明显少了一半左右，而且没有用到join buffer。
这个表读入的顺序是Mysql优化器帮我们做的，可以得知，用记录数少的表做为基础表进行联合，将会得到更高的效率。
对于上面的语句，我们换一种写法
mysql> explain select A.id,A.title,B.title from jos_content A left join jos_categories B on A.catid=B.id left join jos_sections C on A.sectionid=C.id;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
| 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 46585 | |
| 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test.A.catid | 1 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test.A.sectionid | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+ www.2cto.com
3 rows in set (0.00 sec)
Mysql 读入表的顺序被改变了，这意味着，如果我们用left join来做连接查询，Mysql会按SQL语句中表出现的顺序读入，还有一个有变化的地方是联接B和C的type都变成了eq_ref，前边我们