以前遇到过mysql对index_merge进行优化时,按照它自己的“猜测”优化,导致性能下降的问题, 后来靠强制force index(...)关闭优化,解决了。
最近有发现一个对联合索引的“智能”优化。这导致同一个sql5.5前后的版本执行效率完全不一样。

表结构如下:

CREATE TABLE `feed` (
  `feedid` bigint(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `typeid` int(11) NOT NULL,
  `dataid` int(11) NOT NULL,
  `invalid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`feedid`),
  UNIQUE KEY `dataid_typeid` (`dataid`,`typeid`),
  KEY `userid_t_i` (`userid`,`typeid`,`invalid`)
) ENGINE=TokuDB AUTO_INCREMENT=1516204596 DEFAULT CHARSET=utf8 `compression`='tokudb_zlib'

SQL语句时:

select feedid,userid,dataid,typeid from feed where userid = '25057158'   and typeid in (0,2,6)  and (invalid = 0 or invalid = 11) order by feedid desc limit 0,30 ;

按理说完全符合联合索引,应该很快能执行成功,但是实际上发现在新版mysql中有扫描全表。
explain看:

mysql> explain select feedid,userid,dataid,typeid from feed where userid = '25057158'   and typeid in (0,2,6)  and (invalid = 0 or invalid = 11) order by feedid desc limit 0,30 ;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | feed  | index | userid_t_i    | PRIMARY | 8       | NULL |   30 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)

可以看到居然是用了PRIMARY去做查询,实际执行的结果也如此。完全忽略了possible_keys userid_t_i。
我猜想,是mysql发现sql中出现了多处INOR, 认为这个索引的效率是极其低下的,因此“智能”换成了它认为更高效的主键作为索引。

所以如果使用强制索引,或者没有INOR, 它不会做这个优化。 用explain再验证了这一点:

mysql> explain select feedid,userid,dataid,typeid from feed force index (userid) where userid = '178804206'   and typeid in (0,2,6)  and (invalid = 0 or invalid = 11) order by feedid desc limit 0,30;
+------+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                       |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------------+
|    1 | SIMPLE      | feed  | range | userid_t_i    | userid_t_i | 12      | NULL | 2076 | Using where; Using filesort |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------------+

mysql> explain select feedid,userid,dataid,typeid from feed where userid = '25057158'   and typeid = 0  and invalid = 0  order by feedid desc limit 0,30 ;
+------+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+
| id   | select_type | table | type | possible_keys | key        | key_len | ref               | rows | Extra       |
+------+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+
|    1 | SIMPLE      | feed  | ref  | userid_t_i    | userid_t_i | 12      | const,const,const |  585 | Using where |
+------+-------------+-------+------+---------------+------------+---------+-------------------+------+-------------+

此外, 这个优化也应该是由于sql末尾有order by引起的, 如果没有排序,应该不会扫描全表:

mysql> explain select feedid,userid,dataid,typeid from feed where userid = '25057158'   and typeid in (0,2,6)  and (invalid = 0 or invalid = 11)  limit 0,30 ;
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | feed  | range | userid_t_i    | userid_t_i | 12      | NULL |  591 | Using where |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

而如果把order by feedid desc去掉的sql真实的执行一遍以后,因为有了缓存,再次去explain第一个sql的时候,居然不提示用PRIMARY key了,难道是一个智能学习的过程? 而实际的执行时也是只用了userid_t_i作为索引。

mysql> explain select feedid,userid,dataid,typeid from feed where userid = '25057158'   and typeid in (0,2,6)  and (invalid = 0 or invalid = 11)  limit 0,30 ;
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | feed  | range | userid_t_i    | userid_t_i | 12      | NULL |    7 | Using where |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+

总之,我猜测是mysql不同版本对optimizer_switch等参数的不同调整导致的, 也许可以在mysql文档中找出问题并解决。
比如: https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

标签: none

添加新评论