MySQL 查询优化之 or

当使用or的时候是不会用到索引的

mysql> explain SELECT * FROM aladdin_resource WHERE  state = 1 OR state = 2;
+----+-------------+------------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | aladdin_resource | ALL  | state         | NULL | NULL    | NULL | 59074 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

解决办法就是用union替换or

explain select * from aladdin_resource where state=1 union select * from aladdin_resource where state=2;
+----+--------------+------------------+------+---------------+-------+---------+-------+-------+-------------+
| id | select_type  | table            | type | possible_keys | key   | key_len | ref   | rows  | Extra       |
+----+--------------+------------------+------+---------------+-------+---------+-------+-------+-------------+
|  1 | PRIMARY      | aladdin_resource | ref  | state         | state | 2       | const |   383 | Using where |
|  2 | UNION        | aladdin_resource | ref  | state         | state | 2       | const | 21370 | Using where |
| NULL | UNION RESULT | <union1,2>       | ALL  | NULL          | NULL  | NULL    | NULL  |  NULL |             |
+----+--------------+------------------+------+---------------+-------+---------+-------+-------+-------------+
3 rows in set (0.05 sec)

活到老学到老