| mysql> explain SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347); +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | people | range | age | age | 8 | NULL | 3 | Using where | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) |
对比一下查询效率
| mysql> SELECT sql_no_cache name FROM people WHERE age=19 AND zip IN (12345,12346, 12347); +----------------------------------+ | name | +----------------------------------+ | 888ba838661aff00bbbce114a2a22423 | +----------------------------------+ 1 row in set (0.06 sec) mysql> SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 AND zip IN (12345,12346, 12347); +----------------------------------+ | name | +----------------------------------+ | ed4481336eb9adca222fd404fa15658e | | 888ba838661aff00bbbce114a2a22423 | +----------------------------------+ 2 rows in set (1 min 56.09 sec) |
对于第二条sql，可以使用union改写，
| mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=19 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=20 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=21 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=22 AND zip IN (12345,12346, 12347); |
而mysql5.6引入了index condition pushdown，从优化器层面解决了此类问题。
