MySQL：因为统计信息错误引起的sql问题

客官°小女子只卖身不卖艺 2022-09-23 03:51 36阅读 0赞

发现系统以下SQL在S1执行时间3S，但是S2上执行时间0.3S，差距很大，原因为c的子查询执行计划有问题，sql如下

SELECT 
      a.gather_name,
      a.gender,
      c.* 
    FROM
      (SELECT 
        n.gather_name,
        n.gather_ids,
        n.gender 
      FROM
         n 
      WHERE n.id = 35) a 
      JOIN 
        (SELECT DISTINCT 
          d.* 
        FROM
           d 
          JOIN  e 
            ON d.`category_id` = e.`category_id` 
          JOIN  p 
            ON p.`goods_id` = e.`goods_id` 
          JOIN  s 
            ON s.`product_id` = p.`product_id` 
            AND s.`stock` - s.`fix_stock` > 0 
            AND s.status = 1 
            AND e.gender IN (0, 1, 2) 
        ORDER BY sort DESC) c 
        ON INSTR(
          a.gather_ids,
          CONCAT(',', c.category_id, ',')
        ) > 0 
    ORDER BY c.sort ASC

S1上c子查询的执行计划：

mysql> explain SELECT DISTINCT 
        ->       d.* 
        ->     FROM
        ->        d 
        ->       JOIN  e 
        ->         ON d.`category_id` = e.`category_id` 
        ->       JOIN  p 
        ->         ON p.`goods_id` = e.`goods_id` 
        ->       JOIN  s 
        ->         ON s.`product_id` = p.`product_id` 
        ->         AND s.`stock` - s.`fix_stock` > 0 
        ->         AND s.status = 1 
        ->         AND e.gender IN (0, 1, 2) 
        ->     ORDER BY sort DESC;
    +----+-------------+-------+--------+-----------------------------------+------------+---------+-----------------------+--------+----------------------------------------------+
    | id | select_type | table | type   | possible_keys                     | key        | key_len | ref                   | rows   | Extra                                        |
    +----+-------------+-------+--------+-----------------------------------+------------+---------+-----------------------+--------+----------------------------------------------+
    |  1 | SIMPLE      | e     | ALL    | PRIMARY,category_id,goods_id      | NULL       | NULL    | NULL                  | 138326 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | d     | eq_ref | PRIMARY,category_id,category_id_2 | PRIMARY    | 4       | shop_zp.e.category_id |      1 |                                              |
    |  1 | SIMPLE      | p     | ref    | PRIMARY,goods_id,product_id       | goods_id   | 4       | shop_zp.e.goods_id    |      1 | Using index; Distinct                        |
    |  1 | SIMPLE      | s     | ref    | product_id                        | product_id | 4       | shop_zp.p.product_id  |      1 | Using where; Distinct                        |
    +----+-------------+-------+--------+-----------------------------------+------------+---------+-----------------------+--------+----------------------------------------------+
    执行时间：307 rows in set (2.96 sec)
    
    加上hint，强制执行顺序的执行计划：
    mysql> explain SELECT DISTINCT 
        ->       d.* 
        ->     FROM
        ->        d 
        ->       STRAIGHT_JOIN  e 
        ->         ON d.`category_id` = e.`category_id` 
        ->       JOIN  p 
        ->         ON p.`goods_id` = e.`goods_id` 
        ->       JOIN  s 
        ->         ON s.`product_id` = p.`product_id` 
        ->         AND s.`stock` - s.`fix_stock` > 0 
        ->         AND s.status = 1 
        ->         AND e.gender IN (0, 1, 2) 
        ->     ORDER BY sort DESC;
    +----+-------------+-------+------+-----------------------------------+-------------+---------+-----------------------+------+---------------------------------+
    | id | select_type | table | type | possible_keys                     | key         | key_len | ref                   | rows | Extra                           |
    +----+-------------+-------+------+-----------------------------------+-------------+---------+-----------------------+------+---------------------------------+
    |  1 | SIMPLE      | d     | ALL  | PRIMARY,category_id,category_id_2 | NULL        | NULL    | NULL                  |  895 | Using temporary; Using filesort |
    |  1 | SIMPLE      | e     | ref  | PRIMARY,category_id,goods_id      | category_id | 4       | shop_zp.d.category_id |  289 | Using where; Distinct           |
    |  1 | SIMPLE      | p     | ref  | PRIMARY,goods_id,product_id       | goods_id    | 4       | shop_zp.e.goods_id    |    1 | Using index; Distinct           |
    |  1 | SIMPLE      | s     | ref  | product_id                        | product_id  | 4       | shop_zp.p.product_id  |    1 | Using where; Distinct           |
    +----+-------------+-------+------+-----------------------------------+-------------+---------+-----------------------+------+---------------------------------+
    执行时间：307 rows in set (0.25 sec)

查询\`information\_schema\`.\`TABLES\`，information\_schema.statistics发现以下两表和实际数据有些差距

分析后，S1的执行计划：