mysql优化or_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 | | ALL | NULL | NULL | NULL | NULL | NULL | |

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

3 rows in set (0.05 sec)

高下立判