MySQL查询优化之or
时间：2021-07-01 10:21:17
帮助过：48人阅读
当使用or的时候是不会用到索引的
mysql> explain SELECT * FROM aladdin_resource WHEREstate = 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)
高下立判
