本帖最后由 czxin788 于 2020-5-12 15:03 编辑


explain SELECT
    -> id,
    -> customer_id,
    -> send_customer_id,
    -> send_customer_name,
    -> send_site_id,
    -> message_type,
    -> op_type,
    -> ref_type,
    -> ref_id,
    -> ref_review_id,
    -> message_name,
    -> message_body,
    -> create_time,
    -> read_time,
    -> is_read,
    -> read_site_id,
    -> is_remind,
    -> sort_id,
    -> is_valid,
    -> resource_type,
    -> ios_send_time,
    -> android_send_time,
    -> wx_send_time,
    -> notify_config_id,
    -> message_src_time,
    -> is_permit,
    -> page_storage_path,
    -> web_storage_path,
    -> app_storage_parh
    ->  FROM customer_message
    ->  WHERE 1=1
    -> and op_type in
    ->  (  
    -> '121'
    ->  ,
    -> '123'
    ->  )
    -> and create_time >= '2018-05-17 15:35:46.0'
    ->  limit 0, 9223372036854775807;
+----+-------------+------------------+------------+------+----------------+------+---------+------+---------+----------+-------------+
| id | select_type | table            | partitions | type | possible_keys  | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------------+------------+------+----------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | customer_message | NULL       | ALL  | idx_createtime | NULL | NULL    | NULL | 1536673 |    10.00 | Using where |
+----+-------------+------------------+------------+------+----------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)



show index from customer_message;
+------------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name                  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customer_message |          0 | PRIMARY                   |            1 | id          | A         |     1475167 |     NULL | NULL   |      | BTREE      |         |               |
| customer_message |          1 | idx_createtime            |            1 | create_time | A         |      714707 |     NULL | NULL   | YES  | BTREE      |         |               |
| customer_message |          1 | idx_customer_ref_id_multi |            1 | customer_id | A         |      125866 |     NULL | NULL   | YES  | BTREE      |         |               |
| customer_message |          1 | idx_customer_ref_id_multi |            2 | ref_id      | A         |      752687 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)





select count(distinct op_type) from customer_message;
+-------------------------+
| count(distinct op_type) |
+-------------------------+
|                      76 |
+-------------------------+
1 row in set (0.88 sec)


请问，上面的慢sql应该怎么优化好呢