Mysql or查询的问题和优化

下面Mysql中的or查询有什么问题？该如何优化？

select * from user where gender='1' or gender='2';

我们优化sql一般看sql语句是否走了索引，上面的语句使用了or查询，它会扫描全表，我们使用explain看下

mysql> explain select * from user where gender='1' or gender='2';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | idx_gender    | NULL | NULL    | NULL |    4 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这里我们可以使用union将两个sql语句分开查询。

explain
select * from user where gender='1'
union
select * from user where gender='2';

+----+--------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | user       | NULL       | ref  | idx_gender    | idx_gender | 4       | const |    2 |   100.00 | NULL            |
|  2 | UNION        | user       | NULL       | ref  | idx_gender    | idx_gender | 4       | const |    2 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL       | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.01 sec)