MySQL 统计信息不准导致的性能问题

表的统计信息错误导致优化器选择错误的执行计划。

一个客户的性能优化案例: 没有修改数据库实例的任何配置参数以及业务代码没有变更的情况下，一条 sql 出现大幅性能下降。

我们来看看出问题的sql 以及他的执行计划:

mysql> explain 
-> SELECT count(con.id)          ,
->        MAX(DAYNAME(con.date)) ,
->        now()                  ,
->        pcz.type,
->        pcz.c_c
-> FROM   con AS con
->        join orders o on con.o_id = o.id
->        JOIN pcz AS pcz ON o.d_p_c_z_id = pcz.id
->        left join c c on con.c_id = c.id
-> WHERE con.date = current_date() and pcz.type = "T_D"
-> GROUP BY con.date, pcz.c_c,  pcz.type;
+----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys     | key      | key_len | ref                        | rows | filtered | Extra                                        |
+----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | pcz   | NULL       | ALL    | PRIMARY           | NULL     | NULL    | NULL                       |  194 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | o     | NULL       | ref    | PRIMARY,dpcz_FK   | dpcz_FK  | 9       | custom.pcz.id              | 1642 |   100.00 | Using index                                  |
|  1 | SIMPLE      | con   | NULL       | ref    | FK_order,IDX_date | FK_order | 8       | custom.o.id                |    1 |     4.23 | Using where                                  |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY           | PRIMARY  | 8       | custom.con.c_id            |    1 |   100.00 | Using index                                  |
+----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+

执行计划显示 rows examined = (19410%)1642(14.23%)=1347 查看执行计划我们就发现 where 条件 con.date = current_date() 。这个条件看起来更适合作为索引过滤数据。但是 为什么 MySQL 优化器不选择该索引呢？接下来使用 force index 强制执行计划使用 con.date 字段上的索引。执行计划如下:

mysql> explain 
-> SELECT count(con.id)          ,
->        MAX(DAYNAME(con.date)) ,
->        now()                  ,
->        pcz.type,
->        pcz.c_c
-> FROM   con AS con USE INDEX(IDX_date)
->        join orders o on con.o_id = o.id
->        JOIN p_c_z AS pcz ON o.d_p_c_z_id = pcz.id
->        left join c c on con.c_id = c.id
-> WHERE con.date = current_date() and pcz.type = "T_D"
-> GROUP BY con.date, pcz.c_c,  pcz.type;
+----+-------------+-------+------------+--------+-----------------+----------+---------+---------------------------------------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys   | key      | key_len | ref                                   | rows   | filtered | Extra                           |
+----+-------------+-------+------------+--------+-----------------+----------+---------+---------------------------------------+--------+----------+---------------------------------+
|  1 | SIMPLE      | con   | NULL       | ref    | IDX_date        | IDX_date | 3       | const                                 | 110446 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY         | PRIMARY  | 8       | custom.con.c_id                       |      1 |   100.00 | Using index                     |
|  1 | SIMPLE      | o     | NULL       | eq_ref | PRIMARY,dpcz_FK | PRI