当使用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)
高下立判