MySQL部分优化之(IN,BETWEEN,UNION ALL)

使用IN条件的查询，它查询任然是使用range范围类型，

优点:

会优化在第二索引键上的查询。而这时between这些使用范围查找类型所无法做到的优化。

缺点：

in使用net算法，会对产生正确的结果的所有可能进行匹配,要是in条件要匹配的范围很大的话,这就会使查询变慢。

要在1000行的表中找到3使用in的话，也许会扫描两倍有余的rows。

1. mysql> FLUSH STATUS;
2. Query OK, 0 rows affected (0.00 sec)
3.  
4. mysql> SELECT sql_no_cache name FROM people WHERE age BETWEEN 18 AND 20 AND zip IN (12345,12346, 12347);
5. +----------------------------------+
6. | name                             |
7. +----------------------------------+
8. | ed4481336eb9adca222fd404fa15658e |
9. | 888ba838661aff00bbbce114a2a22423 |
10. +----------------------------------+
11. 2 rows IN SET (0.39 sec)
12.  
13. mysql> SHOW STATUS LIKE "Handler%";
14. +----------------------------+-------+
15. | Variable_name              | Value |
16. +----------------------------+-------+
17. | Handler_commit             | 0     |
18. | Handler_delete             | 0     |
19. | Handler_discover           | 0     |
20. | Handler_prepare            | 0     |
21. | Handler_read_first         | 0     |
22. | Handler_read_key           | 1     |
23. | Handler_read_next          | 42250 |
24. | Handler_read_prev          | 0     |
25. | Handler_read_rnd           | 0     |
26. | Handler_read_rnd_next      | 0     |
27. | Handler_rollback           | 0     |
28. | Handler_savepoint          | 0     |
29. | Handler_savepoint_rollback | 0     |
30. | Handler_update             | 0     |
31. | Handler_write              | 14    |
32. +----------------------------+-------+
33. 15 rows IN SET (0.00 sec)

可以通过上面的方法查看到server的处理过程。

注意以下两个参数：
| Handler_read_key           | 1        |    --当值为1的时候表示使用了index range扫描索引。   

| Handler_read_next          | 42250 |    --这个参数和上面的参数是相互联系的，表示在使用index range扫描中分析了多少行数据。



再次再来说一下union的优化；看一下与in相比union的用武之地：

1. mysql> EXPLAIN SELECT * FROM people WHERE age=18 ORDER BY last_online DESC LIMIT 10;
2. +----+-------------+--------+------+---------------+------+---------+-------+-------+-------------+
3. | id | select_type | TABLE  | type | possible_keys | KEY  | key_len | ref   | rows  | Extra       |
4. +----+-------------+--------+------+---------------+------+---------+-------+-------+-------------+
5. |  1 | SIMPLE      | people | ref  | age           | age  | 1       | const | 12543 | USING WHERE |
6. +----+-------------+--------+------+---------------+------+---------+-------+-------+-------------+
7. 1 row IN SET (0.00 sec)
8.  
9. mysql> EXPLAIN SELECT * FROM people WHERE age IN(18,19,20) ORDER BY last_online DESC LIMIT 10;
10. +----+-------------+--------+-------+---------------+------+---------+------+-------+-----------------------------+
11. | id | select_type | TABLE  | type  | possible_keys | KEY  | key_len | ref  | rows  | Extra                       |
12. +----+-------------+--------+-------+---------------+------+---------+------+-------+-----------------------------+
13. |  1 | SIMPLE      | people | range | age           | age  | 1       | NULL | 37915 | USING WHERE; USING filesort |
14. +----+-------------+--------+-------+---------------+------+---------+------+-------+-----------------------------+
15. 1 row IN SET (0.00 sec)

当在使用了in clause的时候同时又使用了排序。
这就会让in去匹配任何一个有可能(匹配更多冗余的数据行)很明显这是不必要的。同时有使用了filesort，对这条SQL造成的效率极低。
这个时候可以尝试使用union来做到更好的处理，
EXPLAIN (SELECT * FROM people WHERE age=18 ORDER BY last_online DESC LIMIT 10) UNION ALL (SELECT * FROM people WHERE age=19 ORDER BY last_online DESC LIMIT 10) UNION ALL (SELECT * FROM people WHERE age=20 ORDER BY last_online DESC LIMIT 10) ORDER BY last_online DESC LIMIT 10;

1. | id | select_type  | TABLE        | type | possible_keys | KEY  | key_len | ref   | rows  | Extra          |
2. +----+--------------+--------------+------+---------------+------+---------+-------+-------+----------------+
3. |  1 | PRIMARY      | people       | ref  | age           | age  | 1       | const | 12543 | USING WHERE    |
4. |  2 | UNION        | people       | ref  | age           | age  | 1       | const | 12741 | USING WHERE    |
5. |  3 | UNION        | people       | ref  | age           | age  | 1       | const | 12631 | USING WHERE    |
6. |NULL | UNION RESULT | 2,3> | ALL  | NULL          | NULL | NULL    | NULL  |  NULL | USING filesort |
7. +----+--------------+--------------+------+---------------+------+---------+-------+-------+----------------+
8. 4 rows IN SET (0.01 sec)

尽管使用了union的连接还是使用了filesort，但是这次filesort只是对每个union产生很小的结果集的排序，然后再对他们取集合，这样似乎比使用in产生的冗余扫描行，少了很多！