MySQL的一部分优化概念解析

有朋友做如下操作, 提出问题如下黑体字体,回答参见蓝色字体:

复合连接查询

EXPLAIN SELECT * FROM employee,department

WHERE department.d_id=employee.d_id

AND age>=24

ORDER BY age ASC;

+----+-------------+------------+------+---------------+------+---------+------+

------+----------------------------------------------------+

| id | select_type | table      | type | possible_keys | key  | key_len | ref  |

 rows | Extra                                              |

+----+-------------+------------+------+---------------+------+---------+------+

------+----------------------------------------------------+

|  1 | SIMPLE      | department | ALL  | NULL          | NULL | NULL    | NULL |

    3 | Using temporary; Using filesort                    |

|  1 | SIMPLE      | employee   | ALL  | NULL          | NULL | NULL    | NULL |

    4 | Using where; Using join buffer (Block Nested Loop) |

+----+-------------+------------+------+---------------+------+---------+------+

------+----------------------------------------------------+

在Extra一栏中出现了 Using Temporary，说明在查询语句中出现了GROUP BY或者是ORDER BY等语句，用以存放临时中间结果；Using filesort表示使用文件完成排序操作。对于Using filesort，查阅资料发现有如下解释：

只有在order by 数据列的时候才可能会出现using filesort，而且如果你不对进行order by的这一列设置索引的话，无论列值是否有相同的都会出现using filesort。因此，只要用到order by 的这一列都应该为其建立一个索引。

也就是说，在使用ORDER BY语句时，最好建立索引，可以消除Using filesort，由此可以进一步提高查询速度，达到查询优化的目的。

疑问：如何在使用ORDER BY的过程中添加索引？

---过程中不可能动态增加索引

（4）添加索引之后的符合查询

在表department中添加索引：

mysql> CREATE UNIQUE INDEX index_age

    -> ON employee(age);

随后建立查询，并解释：

+----+-------------+------------+------+--------------------+------+---------+-- ----+------+----------------------------------------------------+

| id | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra                                              |

+----+-------------+------------+------+--------------------+------+---------+------+------+----------------------------------------------------+

|  1 | SIMPLE      | department | ALL  | NULL               | NULL | NULL    | N ULL |    3 | Using temporary; Using filesort                    |

|  1 | SIMPLE      | employee   | ALL  | index_age,index_id | NULL | NULL    | NULL |    4 | Using where; Using join buffer (Block Nested Loop) |

+----+-------------+------------+------+--------------------+------+---------+-- ----+------+----------------------------------------------------+

结果发现Using filesort还在，这是为什么？是说建立了无效的索引吗？

---注意ORDER By用索引优化,其条件是单表上作ORDER By

（5）带有子查询的查询计划

mysql> EXPLAIN SELECT * FROM employee

    -> WHERE d_id IN

    -> (SELECT d_id FROM department);

+----+-------------+------------+-------+---------------+----------+---------+------+------+----------------------------------------------------+

| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows | Extra                                              |

+----+-------------+------------+-------+---------------+----------+---------+------+------+----------------------------------------------------+

|  1 | SIMPLE      | department | index | index_id      | index_id | 4       | NULL |    3 | Using index; LooseScan                             |

|  1 | SIMPLE      | employee   | ALL   | index_id      | NULL     | NULL    | NULL |    4 | Using where; Using join buffer (Block Nested Loop) |

+----+-------------+------------+-------+---------------+----------+---------+------+------+----------------------------------------------------+

在这里出现了Using index，说明在department表读取d_id时，只使用了索引文件。Loosescan这个在您的书中的附录中没有找到，上网查找信息表示：

LooseScan：利用索引来扫描一个子查询表可以从每个子查询的值群组中选出一个单一的值。
这是MySQL5.6查询优化器的新功能。

疑问：这句话的含义是什么？

---LooseScan,松散扫描.
---MySQL提供半连接操作(semi-join. IN子查询可能被优化为半连接)
---MySQL对半连接的优化, 提供了5种策略, 分别是DUPS_WEEDOUT, LOOSE_SCAN, FIRST_MATCH, MATERIALIZE_LOOKUP, MATERIALIZE_SCAN. 这5种策略,都是通过代价估算, 找出最优的策略. IN子查询,利用了半连接的优化.
---半连接优化的LooseScan策略, 和针对GROUP BY优化提供的Loose Index Scan是两个不同的概念。前者是消重的一种方式，要求被连接的表提供的元组是有序的,在这个有序的结果上完成消重计算，这才能满足半连接语义。后者多了一个index，这是要利用索引直接获取元组。