mysql通过将or改成union来优化sql性能问题一例

某系统测试环境有支SQL执行时间较长，开发人员请求dba协助优化。

原SQL如下：
SELECT   g.id,
          ----省略-----
    FROM    g,
            y,
            t,
            o
   WHERE   g.ycon_id = y.id
           AND t.ycon_id = g.ycon_id
           AND o.bno = t.bno
           AND 1 = 1
           AND g.t_CODE = 'aa'
           AND (g.so_s_n = '123'
                OR t.cab_no = '456'
                OR t.detail_id IN
                        (SELECT   detail_id
                           FROM   go
                          WHERE   don = '789'))
           AND g.status IN ('a7', 'a8')
ORDER BY   1 ASC;

执行时间需要4.08秒。

执行计划如下：

+------+--------------+--------------------+--------+-----------------------------------------------------+---------------------------+---------+------------------------+--------+------------------------------------+
| id   | select_type  | table              | type   | possible_keys                                       | key                       | key_len | ref                    | rows   | Extra                              |
+------+--------------+--------------------+--------+-----------------------------------------------------+---------------------------+---------+------------------------+--------+------------------------------------+
|    1 | PRIMARY      | g                  | ALL    | idx_ycon_id,idx_so_serial_number                    | NULL                      | NULL    | NULL                   | 206950 | Using where; Using filesort        |
|    1 | PRIMARY      | y                  | eq_ref | id                                                  | id                        | 152     | mg.g.ycon_id           |      1 | Using index condition              |
|    1 | PRIMARY      | t                  | ref    | idx_booking_no,idx_ycon_id,idx_cabinet_no           | idx_ycon_id               | 123     | mg.y.id                |      1 | Using index condition; Using where |
|    1 | PRIMARY      | o                  | ref    | unique_booking_no                                   | unique_booking_no         | 195     | mg.t.booking_no        |      1 | Using index condition              |
|    2 | MATERIALIZED | go                 | ref    | idx_booking_req_detail_id,idx_delivery_order_number | idx_delivery_order_number | 99      | const                  |      1 | Using index condition              |
+------+--------------+--------------------+--------+-----------------------------------------------------+---------------------------+---------+------------------------+--------+------------------------------------+
5 rows in set (0.01 sec)

上面显示g表走全表扫描，需要访问206950 行记录，表结构如下：
show create table g\G
........
PRIMARY KEY (`_id`),
  UNIQUE KEY `id` (`id`),
  KEY `idx_ycon_id` (`ycon_id`),
  KEY `idx_so_serial_number` (`so_s_n`)
) ;

对上面sql，通过hint(force index)强制使用索引或强制更改表连接顺序(straight_join)，总会有一个表是全表扫描，而且y、t和o表的记录数比g表要大很多。显然，通过直接强制走索引或更改表连接顺序无法达到优化目的。

查看SQL中有两个or连接三个条件，且三个条件的字段都有索引，所以将or改成union，如下：
select a.* from (
SELECT   g.id,
          ----省略-----
    FROM    g,
            y,
            t,
            o
   WHERE   g.ycon_id = y.id
           AND t.ycon_id = g.ycon_id
           AND o.bno = t.bno
           AND 1 = 1
           AND g.t_CODE = 'aa'
           AND g.so_s_n = '123' AND g.status IN ('a7', 'a8')
union
SELECT   g.id,
          ----省略-----
    FROM    g,
            y,
            t,
            o
   WHERE   g.ycon_id = y.id
           AND t.ycon_id = g.ycon_id
           AND o.bno = t.bno
           AND 1 = 1
           AND g.t_CODE = 'aa'
           AND  t.cab_no = '456' AND g.status IN ('a7', 'a8')
union
SELECT   g.id,
          ----省略-----
    FROM    g,
            y,
            t,
            o
   WHERE   g.ycon_id = y.id
           AND t.ycon_id = g.ycon_id
           AND o.bno = t.bno
           AND 1 = 1
           AND g.t_CODE = 'aa'
           AND  t.detail_id IN
                        (SELECT   detail_id
                           FROM   go
                          WHERE   don = '789') AND g.status IN ('a7', 'a8') )a
order by a.id asc;

执行时间变为0.01秒，执行计划如下：
+------+--------------+--------------------+--------+------------------------------------------------------+---------------------------+---------+----------------------------------------------------+------+------------------------------------+
| id   | select_type  | table              | type   | possible_keys                                        | key                       | key_len | ref                                                | rows | Extra                              |
+------+--------------+--------------------+--------+------------------------------------------------------+---------------------------+---------+----------------------------------------------------+------+------------------------------------+
|    1 | PRIMARY      | <derived2>         | ALL    | NULL                                                 | NULL                      | NULL    | NULL                                               |    3 | Using filesort                     |
|    2 | DERIVED      | g                  | ref    | idx_ycon_id,idx_so_serial_number                     | idx_so_serial_number      | 195     | const                                              |    1 | Using index condition; Using where |
|    2 | DERIVED      | y                  | eq_ref | id                                                   | id                        | 152     | mg.g.ycon_id                                |    1 | Using index condition              |
|    2 | DERIVED      | t                  | ref    | idx_booking_no,idx_ycon_id                           | idx_ycon_id               | 123     | mg.y.id                                     |    1 | Using index condition; Using where |
|    2 | DERIVED      | o                  | ref    | unique_booking_no                                    | unique_booking_no         | 195     | mg.t.booking_no                             |    1 | Using index condition              |
|    3 | UNION        | t                  | ref    | idx_booking_no,idx_ycon_id,idx_cabinet_no            | idx_cabinet_no            | 99      | const                                              |    1 | Using index condition; Using where |
|    3 | UNION        | g                  | ref    | idx_ycon_id                                          | idx_ycon_id               | 123     | mg.t.ycon_id                                |    1 | Using where                        |
|    3 | UNION        | o                  | ref    | unique_booking_no                                    | unique_booking_no         | 195     | mg.t.booking_no                             |    1 | Using index condition              |
|    3 | UNION        | y                  | eq_ref | id                                                   | id                        | 152     | mg.t.ycon_id                                |    1 | Using index condition              |
|    4 | UNION        | <subquery5>        | ALL    | distinct_key                                         | NULL                      | NULL    | NULL                                               |    1 |                                    |
|    4 | UNION        | t                  | ref    | idx_booking_no,idx_booking_req_detail_id,idx_ycon_id | idx_booking_req_detail_id | 5       | mg.go_shipment_notice.booking_req_detail_id |    1 | Using where                        |
|    4 | UNION        | o                  | ref    | unique_booking_no                                    | unique_booking_no         | 195     | mg.t.booking_no                             |    1 | Using index condition              |
|    4 | UNION        | g                  | ref    | idx_ycon_id                                          | idx_ycon_id               | 123     | mg.t.ycon_id                                |    1 | Using where                        |
|    4 | UNION        | y                  | eq_ref | id                                                   | id                        | 152     | mg.t.ycon_id                                |    1 | Using index condition              |
|    5 | MATERIALIZED | go_shipment_notice | ref    | idx_booking_req_detail_id,idx_delivery_order_number  | idx_delivery_order_number | 99      | const                                              |    1 | Using index condition; Using where |
| NULL | UNION RESULT | <union2,3,4>       | ALL    | NULL                                                 | NULL                      | NULL    | NULL                                               | NULL |                                    |
+------+--------------+--------------------+--------+------------------------------------------------------+---------------------------+---------+----------------------------------------------------+------+------------------------------------+
16 rows in set (0.00 sec)

总结：mysql的sql优化有时需要通过改写sql来优化，通过将or改成union，改变sql执行计划，从而达到提升SQL性能，or改成union提升性能的前提是：or连接的各条件字段要能用到索引。