mysql - For these two SQL statements, one uses or and the other uses union all. The performance should be better than the latter, right?

Question

explain select * from ecs_ad where ad_id =1 or ad_id = 3;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | ecs_ad | range | PRIMARY       | PRIMARY | 2       | NULL |    2 | Using index condition |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set

This is union all

explain select * from ecs_ad where ad_id = 4 union all select * from ecs_ad where ad_id = 3;
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id   | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
|    1 | PRIMARY      | ecs_ad     | const | PRIMARY       | PRIMARY | 2       | const |    1 | NULL            |
|    2 | UNION        | ecs_ad     | const | PRIMARY       | PRIMARY | 2       | const |    1 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary |
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
3 rows in set

Answer 1

First of all, the performance gap between these two queries should not be big.
Because the ad_id column is the primary key.

According to type column analysis:
or query, type: rangetype：range
union all 查询：type：const / const / ALLunion all query: type: const / const / ALL

The first thing that can be confirmed is that const is better than range.const 要优于 range.
constconst is also because ad_id is the primary key.

But union all does three operations, two primary key or unique index queries type:const, but when finally combined together type:ALL
type:ALL itself has worse performance than range

From the perspective of Type, or query performance should be better.

Extra analysis
or query: Using index conditionUsing index condition
union all 查询：NULL / NULL / Using temporaryunion all query: NULL / NULL / Using temporary

Using index condition is a newly added feature after MySQL 5.6 version, index condition push.

First, let’s talk about the processing flow when there is no index condition for push:

When the optimizer does not use ICP, the data access and extraction process is as follows:

1) When the storage engine reads the next row, it first reads the index tuple, and then uses the index tuple to locate and read the entire row of data in the base table.
2) The sever layer evaluates the where condition. If the row of data meets the where condition, it will be used, otherwise it will be discarded.
3) Execute 1) until the last row of data.

The processing flow when there is index condition push:

When the optimizer uses ICP, the server layer will push the where conditions that can be evaluated by using the index to the storage engine layer. The data access and extraction process is as follows:

1) The storage engine reads the next index tuple from the index.
2) The storage engine uses the index tuple to evaluate the pushdown index condition. If the where condition is not met, the storage engine will process the next index tuple (return to the previous step). Only when the index tuple meets the push-down index condition will the data continue to be read from the base table.
3) If the push-down index condition is met, the storage engine locates the row of the base table through the index tuple and reads the entire row of data and returns it to the server layer.
4) The server layer evaluation is not pushed down to the where condition of the storage engine layer. If the row of data satisfies the where condition, it will be used, otherwise it will be discarded.

Simply put, when there is no ICP, the storage engine returns all rows of data that meet the conditions, and performs where condition filtering at the service layer.
When there is ICP, the where condition is pushed down to the storage engine layer, and the storage engine directly returns data that meets the condition.
Performance is naturally improved a lot.

For a related introduction to ICP, you can view it here

Using temporary means an implicit temporary table, which means that MySQL will generate a temporary table to store intermediate data. Because union all is a relationship where they are processed separately and merged at the end.
From Extra, it should also be that or query performance is higher.

Overall: the performance of OR query should be better than UNION ALL.

Since the performance of all queries changes as the amount of data increases, the above analysis is just based on the Explain analysis results posted by the subject. This is not to say that OR is better than UNION ALL in all situations.

The above are personal opinions, please correct me if there are any mistakes.

Answer 2

There is no difference between these two sentences.
Generally, when OR connects two different fields and the index cannot be used, the performance will be poor and not as good as union. You can try to modify it.