MySQL 5.6 查询优化器新特性的“BUG”

标签: 数据库 MySQL MySQL优化 OPTIMIZER_TRACE 优化 | 发表时间:2014-08-05 10:36 | 作者:yejr
出处:http://imysql.com

最近碰到一个慢SQL问题,解决过程有点小曲折,和大家分享下。 SQL本身不复杂,表结构、索引也比较简单,不过个别字段存在于多个索引中。

CREATE TABLE `pre_forum_post` (
  `pid` int(10) unsigned NOT NULL,
  `fid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `tid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `first` tinyint(1) NOT NULL DEFAULT '0',
  `author` varchar(40) NOT NULL DEFAULT '',
  `authorid` int(10) unsigned NOT NULL DEFAULT '0',
  `subject` varchar(80) NOT NULL DEFAULT '',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
  `message` mediumtext NOT NULL,
  `useip` varchar(15) NOT NULL DEFAULT '',
  `invisible` tinyint(1) NOT NULL DEFAULT '0',
  `anonymous` tinyint(1) NOT NULL DEFAULT '0',
  `usesig` tinyint(1) NOT NULL DEFAULT '0',
  `htmlon` tinyint(1) NOT NULL DEFAULT '0',
  `bbcodeoff` tinyint(1) NOT NULL DEFAULT '0',
  `smileyoff` tinyint(1) NOT NULL DEFAULT '0',
  `parseurloff` tinyint(1) NOT NULL DEFAULT '0',
  `attachment` tinyint(1) NOT NULL DEFAULT '0',
  `rate` smallint(6) NOT NULL DEFAULT '0',
  `ratetimes` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `status` int(10) NOT NULL DEFAULT '0',
  `tags` varchar(255) NOT NULL DEFAULT '0',
  `comment` tinyint(1) NOT NULL DEFAULT '0',
  `replycredit` int(10) NOT NULL DEFAULT '0',
  `position` int(8) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`tid`,`position`),
  UNIQUE KEY `pid` (`pid`),
  KEY `fid` (`fid`),
  KEY `displayorder` (`tid`,`invisible`,`dateline`),
  KEY `first` (`tid`,`first`),
  KEY `new_auth` (`authorid`,`invisible`,`tid`),
  KEY `idx_dt` (`dateline`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


"[email protected] Fri Aug  1 11:59:56 2014 11:59:56 [test]>show table status like 'pre_forum_post'\G
*************************** 1. row ***************************
           Name: pre_forum_post
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 23483977
 Avg_row_length: 203
    Data_length: 4782024708
Max_data_length: 281474976710655
   Index_length: 2466093056
      Data_free: 0
 Auto_increment: 1
    Create_time: 2014-08-01 11:00:56
    Update_time: 2014-08-01 11:08:49
     Check_time: 2014-08-01 11:12:23
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 


mysql> show index from pre_forum_post;
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| pre_forum_post |          0 | PRIMARY      |            1 | tid         | A         |      838713 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          0 | PRIMARY      |            2 | position    | A         |    23483977 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          0 | pid          |            1 | pid         | A         |    23483977 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | fid          |            1 | fid         | A         |        1470 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | displayorder |            1 | tid         | A         |      838713 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | displayorder |            2 | invisible   | A         |      869776 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | displayorder |            3 | dateline    | A         |    23483977 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | first        |            1 | tid         | A         |      838713 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | first        |            2 | first       | A         |     1174198 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | new_auth     |            1 | authorid    | A         |     1806459 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | new_auth     |            2 | invisible   | A         |     1956998 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | new_auth     |            3 | tid         | A         |    11741988 |     NULL | NULL   |      | BTREE      |         |               |
| pre_forum_post |          1 | idx_dt       |            1 | dateline    | A         |    23483977 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

我们来看下这个SQL的执行计划:

mysql> explain select * from pre_forum_post where tid=7932612 and `invisible` in('0','-2') order by dateline  limit 15\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pre_forum_post
         type: index
possible_keys: PRIMARY,displayorder,first
            key: idx_dt
      key_len: 4
          ref: NULL
           rows: 14042
          Extra: Using where

可以看到执行计划比较奇怪,从几个可选的索引中,最终选择了 idx_dt,结果悲剧了,这个SQL执行耗时很长:

mysql> select * from pre_forum_post where tid=7932612 and `invisible` in('0','-2') order by dateline  limit 15;
15 rows in set (26.78 sec)

看下MySQL的会话状态值:Handler_read_next

| Handler_read_next          | 17274153 |

从1700多万数据中选取15条记录,结果可想而知,非常慢。 我们强制指定比较靠谱的索引再看下:

mysql> explain select * from pre_forum_post force index(displayorder) where tid=7932612 and `invisible` in('0','-2') order by dateline  limit 15\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pre_forum_post
         type: range
possible_keys: displayorder
            key: displayorder
      key_len: 4
          ref: NULL
           rows: 46131
        Extra: Using index condition; Using filesort

看下实际执行的耗时:

mysql> select * from pre_forum_post force index(displayorder) where tid=7932612 and `invisible` in('0','-2') order by dateline  limit 15;
15 rows in set (0.08 sec)

尼玛,怎么可以这么快,查询优化器未免太坑爹了吧。 再看下MySQL的会话状态值:Handler_read_next

| Handler_read_next          | 31188 |

和不强制索引的情况相比,差了553倍! 所幸,5.6以上除了EXPLAIN外,还支持OPTIMIZER_TRACE,我们来观察下两种执行计划的区别,发现不强制指定索引时的执行计划有诈,会在最后判断到 ORDER BY 子句时,修改执行计划:

          {\
            "reconsidering_access_paths_for_index_ordering": {\
              "clause": "ORDER BY",\
              "index_order_summary": {\
                "table": "`pre_forum_post`",\
                "index_provides_order": true,\
                "order_direction": "asc",\
                "index": "idx_dt",\
                  "plan_changed": true,\
                "access_type": "index_scan"\
              } /* index_order_summary */\
            } /* reconsidering_access_paths_for_index_ordering */\

而在前面analyzing_range_alternatives和considered_execution_plans阶段,都认为其他几个索引也是可选择的,直到这里才给强X了,你Y的… 看起来像是MySQL 5.6查询优化器的bug了,GOOGLE了一下,还真发有人已经反馈过类似的问题: MySQL bug 70245: incorrect costing for range scan causes optimizer to choose incorrect index

看完才发现,其实不是神马BUG,而是原来从5.6开始,增加了一个选项叫 eq_range_index_dive_limit 的高级货,这货大概的用途是: 在较多等值查询(例如多值的IN查询)情景中,预估可能会扫描的记录数,从而选择相对更合适的索引,避免所谓的index dive问题。

当面临下面两种选择时:

1、索引代价较高,但结果较为精确;
2、索引代价较低,但结果可能不够精确;

简单说,选项  eq_range_index_dive_limit 的值设定了 IN列表中的条件个数上线,超过设定值时,会将执行计划分支从 1 变成 2。

该值默认为 10,但社区众多人反馈较低了,因此在5.7版本后,将默认值调整为 200了。

不过,今天我们这里的案例却是想反的,因为优化器选择了看似代价低但精确的索引,实际却选择了更低效的索引。 因此,我们需要将其阈值调低,尝试设置  eq_range_index_dive_limit = 2 后(上面的例子中,IN条件里有2个值),再看下新的查询计划:

mysql> set eq_range_index_dive_limit = 2;

mysql> explain select * from pre_forum_post where tid=7932612 and `invisible` in('0','-2') order by dateline  limit 15\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pre_forum_post
         type: range
possible_keys: PRIMARY,displayorder,first
            key: displayorder
      key_len: 4
          ref: NULL
           rows: 54
          Extra: Using index condition; Using filesort

卧槽,预估扫描记录数又降了557倍,相比最开始降了接近32万倍! 在这个案例中,虽然通过修改选项  eq_range_index_dive_limit 的阈值可以达到优化效果,但事实上更靠谱的做法是:直接删除 idx_dt 索引。 是的,没错,删除这个垃圾 重复索引,因为实际上这个索引的用处不大,够坑爹吧~~

参考资料:
http://blog.163.com/li_hx/blog/static/18399141320147521735442/
http://mysqlserverteam.com/you-asked-for-it-new-default-for-eq_range_index_dive_limit/ https://www.facebook.com/note.php?note_id=10151533648715933 http://bugs.mysql.com/bug.php?id=70586 http://bugs.mysql.com/bug.php?id=67980 http://bugs.mysql.com/bug.php?id=70331

相关 [mysql 优化 bug] 推荐:

MySQL 5.6 查询优化器新特性的“BUG”

- - MySQL中文网
最近碰到一个慢SQL问题,解决过程有点小曲折,和大家分享下. SQL本身不复杂,表结构、索引也比较简单,不过个别字段存在于多个索引中. 我们来看下这个SQL的执行计划:. 可以看到执行计划比较奇怪,从几个可选的索引中,最终选择了 idx_dt,结果悲剧了,这个SQL执行耗时很长:. mysql> select * from pre_forum_post where tid=7932612 and `invisible` in('0','-2') order by dateline limit 15; 15 rows in set (26.78 sec).

mysql order by和limit共用bug

- - 数据库 - ITeye博客
 在mysql下执行没有问题,可以得到预期结果. 但是用jdbc执行的时候就得不到预期结果了. 官网地址:http://bugs.mysql.com/bug.php?id=32933. 以下转载:http://bbs.chinaunix.net/thread-1276235-1-1.html. 我想从一个表中检索所有标题含有“中国”的数据,将它们按id排序,取前5条,所以我写了以下语句.

使用Hibernate+MySql+native SQL的BUG,以及解决办法

- - 互联网 - ITeye博客
使用Hibernate+MySql+native SQL的BUG,以及解决办法. 本来是mssql+hibernate+native SQL 应用的很和谐. 但是到了把mssql换成mysql,就出了错(同样的数据结构和数据). 看到最后Caused by: java.sql.SQLException:.

mysql优化

- - 数据库 - ITeye博客
公司网站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL  的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL  默认的系统参数能够让 MySQL运行得非常顺畅. 在Apache, PHP,  MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.

mysql优化

- - 数据库 - ITeye博客
      1.通过 show (session 或者 global) status 来查看( 当前连接 或者 数据库上次开机以来 )的服务器状态信息,默认是session.         例如:show status like '%com_%' : com_XXX表示XXX语句执行的总次数,这总次数是针对所有引擎的总和.

MySQL性能优化

- sun - IT程序员面试网
在笔试面试中,尤其是像百度,淘宝这些数据量非常大,而且用LAMP架构的公司,数据库优化方面就显得特别重要了. 此外,除了数据库索引之外,在LAMP结果如此流行的今天,数据库(尤其是MySQL)性能优化也是海量数据处理的一个热点. 下面就结合自己的经验,聊一聊MySQL数据库优化的几个方面. 首先,在数据库设计的时候,要能够充分的利用索引带来的性能提升,至于如何建立索引,建立什么样的索引,在哪些字段上建立索引,上面已经讲的很清楚了,这里不在赘述.

mysql 引擎优化

- - CSDN博客推荐文章
MySQL数 据库引擎取决于MySQL在安装的时候是如何被编译的. 要添加一个新的引擎,就必须重新编译MYSQL. 在缺省情况下,MYSQL支持三个引擎:ISAM、MYISAM和HEAP. 另外两种类型INNODB和BERKLEY(BDB),也常常可以使用. 如果技术高超,还可以使用MySQL++ API自己做一个引擎.

mysql参数优化

- - CSDN博客推荐文章
### 用来存放InnoDB的内部目录,对于大数据设置16M足够用. ### InnoDB 缓存总大小设置,一般设置为系统内存的70%-80%. ### 指定所有InnoDB数据文件的路径和大小分配. ### 文件读写io数设置:. ### InnoDB内核的并发线程数设置. ### 设置日值的大小.

Zabbix 的 MySQL 优化

- - SegmentFault 最新的文章
为 Zabbix 优化 MySQL. 标签(空格分隔): Zabbix MySQL Optimizing 优化. Aurimas Mikalauskas,原文是. Zabbix 和 MySQL. 在大型的 Zabbix 环境中,遇到的挑战大部分是 MySQL 以及更具体的说是 MySQL 磁盘 IO.

mysql优化方法

- - 数据库 - ITeye博客
通过show status和应用特点了解各种SQL的执行频率. 通过SHOW STATUS可以提供服务器状态信息,也可以使用mysqladmin extended-status命令获得. SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果. 以下几个参数对Myisam和Innodb存储引擎都计数:.