我的例子在
MYSQL VERSION是
5.6.34日志

问题摘要,以下查询需要 40秒 ORDER_ITEM

  • 758423 记录

    付款方式
  • 177272 记录

  • 提交_entry
  • 2165698 记录

    作为整个表计数。

  • 详细信息:下面:
  • 我有此查询,请引用 [1]
  • 我添加了 SQL_NO_CACHE 用于在重新测试时测试重复测试
    询问。
  • 我有优化的索引请引用 [2] ,但是没有意义
    改进。
  • 在此处查找表结构 [3]
  • 查找使用的解释计划 [4]

  • [1]
         SELECT SQL_NO_CACHE
              `payment`.`id`                                                                                    AS id,
              `order_item`.`order_id`                                                                           AS order_id,
    
    
              GROUP_CONCAT(DISTINCT (CASE WHEN submission_entry.text = '' OR submission_entry.text IS NULL
                THEN ' '
                                     ELSE submission_entry.text END) ORDER BY question.var DESC SEPARATOR 0x1D) AS buyer,
    
    
              event.name                                                                                        AS event,
              COUNT(DISTINCT CASE WHEN (`order_item`.status > 0 OR (
                `order_item`.status != -1 AND `order_item`.status >= -2 AND `payment`.payment_type_id != 8 AND
                payment.make_order_free = 1))
                THEN `order_item`.id
                             ELSE NULL END)                                                                     AS qty,
              payment.currency                                                                                  AS `currency`,
              (SELECT SUM(order_item.sub_total)
               FROM order_item
               WHERE payment_id =
                     payment.id)                                                                                AS sub_total,
              CASE WHEN payment.make_order_free = 1
                THEN ROUND(payment.total + COALESCE(refunds_total, 0), 2)
              ELSE ROUND(payment.total, 2) END                                                                  AS 'total',
              `payment_type`.`name`                                                                             AS payment_type,
              payment_status.name                                                                               AS status,
              `payment_status`.`id`                                                                             AS status_id,
              DATE_FORMAT(CONVERT_TZ(order_item.`created`, '+0:00', '-8:00'),
                          '%Y-%m-%d %H:%i')                                                                     AS 'created',
              `user`.`name`                                                                                     AS 'agent',
              event.id                                                                                          AS event_id,
              payment.checked,
              DATE_FORMAT(CONVERT_TZ(payment.checked_date, '+0:00', '-8:00'),
                          '%Y-%m-%d %H:%i')                                                                     AS checked_date,
              DATE_FORMAT(CONVERT_TZ(`payment`.`complete_date`, '+0:00', '-8:00'),
                          '%Y-%m-%d %H:%i')                                                                     AS `complete date`,
              `payment`.`delivery_status`                                                                       AS `delivered`
            FROM `order_item`
              INNER JOIN `payment`
                ON payment.id = `order_item`.`payment_id` AND (payment.status > 0.0 OR payment.status = -3.0)
              LEFT JOIN (SELECT
                           sum(`payment_refund`.total) AS `refunds_total`,
                           payment_refunds.payment_id  AS `payment_id`
                         FROM payment
                           INNER JOIN `payment_refunds` ON payment_refunds.payment_id = payment.id
                           INNER JOIN `payment` AS `payment_refund`
                             ON `payment_refund`.id = `payment_refunds`.payment_id_refund
                         GROUP BY `payment_refunds`.payment_id) AS `refunds` ON `refunds`.payment_id = payment.id
    #           INNER JOIN event_date_product ON event_date_product.id = order_item.event_date_product_id
    #           INNER JOIN event_date ON event_date.id = event_date_product.event_date_id
              INNER JOIN event ON event.id = order_item.event_id
              INNER JOIN payment_status ON payment_status.id = payment.status
              INNER JOIN payment_type ON payment_type.id = payment.payment_type_id
              LEFT JOIN user ON user.id = payment.completed_by
              LEFT JOIN submission_entry ON submission_entry.form_submission_id = `payment`.`form_submission_id`
              LEFT JOIN question ON question.id = submission_entry.question_id AND question.var IN ('name', 'email')
            WHERE 1 = '1' AND (order_item.status > 0.0 OR order_item.status = -2.0)
            GROUP BY `order_item`.`order_id`
            HAVING 1 = '1'
            ORDER BY `order_item`.`order_id` DESC
            LIMIT 10
    

    [2]
     CREATE INDEX order_id
          ON order_item (order_id);
    
        CREATE INDEX payment_id
          ON order_item (payment_id);
    
        CREATE INDEX status
          ON order_item (status);
    

    第二个表
    CREATE INDEX payment_type_id
      ON payment (payment_type_id);
    
    CREATE INDEX status
      ON payment (status);
    

    [3]
    CREATE TABLE order_item
    (
      id                         INT AUTO_INCREMENT
        PRIMARY KEY,
      order_id                   INT                                 NOT NULL,
      form_submission_id         INT                                 NULL,
      status                     DOUBLE DEFAULT '0'                  NULL,
      payment_id                 INT DEFAULT '0'                     NULL
    );
    

    第二个表
    CREATE TABLE payment
    (
      id                 INT AUTO_INCREMENT,
      payment_type_id    INT                                 NOT NULL,
      status             DOUBLE                              NOT NULL,
      form_submission_id INT                                 NOT NULL,
      PRIMARY KEY (id, payment_type_id)
    );
    

    [4] 运行代码段以HTML格式查看 EXPLAIN 的表

    <!DOCTYPE html>
    <html>
    <head>
      <title></title>
    </head>
    <body>
    <table border="1" style="border-collapse:collapse">
    <tr><th>id</th><th>select_type</th><th>table</th><th>type</th><th>possible_keys</th><th>key</th><th>key_len</th><th>ref</th><th>rows</th><th>Extra</th></tr>
    <tr><td>1</td><td>PRIMARY</td><td>payment_status</td><td>range</td><td>PRIMARY</td><td>PRIMARY</td><td>8</td><td>NULL</td><td>4</td><td>Using where; Using temporary; Using filesort</td></tr>
    <tr><td>1</td><td>PRIMARY</td><td>payment</td><td>ref</td><td>PRIMARY,payment_type_id,status</td><td>status</td><td>8</td><td>exp_live_18092017.payment_status.id</td><td>17357</td><td></td></tr>
    <tr><td>1</td><td>PRIMARY</td><td>payment_type</td><td>eq_ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment.payment_type_id</td><td>1</td><td></td></tr>
    <tr><td>1</td><td>PRIMARY</td><td>user</td><td>eq_ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment.completed_by</td><td>1</td><td></td></tr>
    <tr><td>1</td><td>PRIMARY</td><td>submission_entry</td><td>ref</td><td>form_submission_id,idx_submission_entry_1</td><td>form_submission_id</td><td>4</td><td>exp_live_18092017.payment.form_submission_id</td><td>2</td><td></td></tr>
    <tr><td>1</td><td>PRIMARY</td><td>question</td><td>eq_ref</td><td>PRIMARY,var</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.submission_entry.question_id</td><td>1</td><td>Using where</td></tr>
    <tr><td>1</td><td>PRIMARY</td><td>order_item</td><td>ref</td><td>status,payment_id</td><td>payment_id</td><td>5</td><td>exp_live_18092017.payment.id</td><td>3</td><td>Using where</td></tr>
    <tr><td>1</td><td>PRIMARY</td><td>event</td><td>eq_ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.order_item.event_id</td><td>1</td><td></td></tr>
    <tr><td>1</td><td>PRIMARY</td><td>&lt;derived3&gt;</td><td>ref</td><td>key0</td><td>key0</td><td>5</td><td>exp_live_18092017.payment.id</td><td>10</td><td>Using where</td></tr>
    <tr><td>3</td><td>DERIVED</td><td>payment_refunds</td><td>index</td><td>payment_id,payment_id_refund</td><td>payment_id</td><td>4</td><td>NULL</td><td>1110</td><td></td></tr>
    <tr><td>3</td><td>DERIVED</td><td>payment</td><td>ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment_refunds.payment_id</td><td>1</td><td>Using index</td></tr>
    <tr><td>3</td><td>DERIVED</td><td>payment_refund</td><td>ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment_refunds.payment_id_refund</td><td>1</td><td></td></tr>
    <tr><td>2</td><td>DEPENDENT SUBQUERY</td><td>order_item</td><td>ref</td><td>payment_id</td><td>payment_id</td><td>5</td><td>func</td><td>3</td><td></td></tr></table>
    </body>
    </html>


    预期的Restul

    必须少于5秒而不是40秒

    重要
    更新

    1)对评论1的答复:在这​​两个表上根本没有外键。

    UPDATE-1:
    本地上,原始查询花费 40秒
    如果我只删除了,只有,则它变为 25秒节省了15秒
    GROUP_CONCAT(DISTINCT (CASE WHEN submission_entry.text = '' OR submission_entry.text IS NULL
        THEN ' '
                             ELSE submission_entry.text END) ORDER BY question.var DESC SEPARATOR 0x1D) AS buyer
    

    如果我删除了,则它在大约40秒的同一时间删除了,无法保存!
    COUNT(DISTINCT CASE WHEN (`order_item`.status > 0 OR (
        `order_item`.status != -1 AND `order_item`.status >= -2 AND `payment`.payment_type_id != 8 AND
        payment.make_order_free = 1))
        THEN `order_item`.id
                     ELSE NULL END)                                                                     AS qty,
    

    如果我仅删除了,而仅删除,则大约需要 36秒可以节省4秒
    (SELECT SUM(order_item.sub_total)
       FROM order_item
       WHERE payment_id =
             payment.id)                                                                                AS sub_total,
      CASE WHEN payment.make_order_free = 1
        THEN ROUND(payment.total + COALESCE(refunds_total, 0), 2)
      ELSE ROUND(payment.total, 2) END                                                                  AS 'total',
    

    最佳答案

    删除HAVING 1=1;优化器可能不够聪明,无法忽略它。请提供EXPLAIN SELECT(不在html中)以查看优化程序正在做什么。

    在这种情况下拥有复合PK似乎是错误的:PRIMARY KEY (id, payment_type_id)。请说明理由。

    请解释status的含义或对DOUBLE的需求:status DOUBLE
    需要花点时间弄清楚查询为什么这么慢。让我们从扔掉标准化部分开始,例如日期,事件名称和货币。这样会使查询减少到足以找到所需的行,而不是找到每行的详细信息。如果仍然很慢,让我们对其进行调试。如果是“快速”,则一个接一个地添加其他内容,以找出导致性能问题的原因。

    只是id是每个表的PRIMARY KEY吗?还是还有更多异常(exception)(例如payment)?

    question.var指定一个值似乎是“错误的”,但是随后使用LEFT暗示它是可选的。除非我在这个问题上弄错了,否则请将所有LEFT JOINs更改为INNER JOINs

    有任何表(也许是submission_entryevent_date_product)“多对多”映射表吗?如果是这样,请按照here的提示进行操作以提高性能。

    当您回来时,请为每个表格提供SHOW CREATE TABLE

    关于mysql - 分组和分组concat,不使用主pk来优化mysql查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/46510329/

    10-16 13:32
    查看更多