mysql mrr及一个优化器的问题

原创

aoerqilengbai 2021-09-07 19:04:42 博主文章分类：MYSQL ©著作权

文章标签 mysql sed 自增测试环境 .net 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者aoerqilengbai的原创作品，请联系作者获取转载授权，否则将追究法律责任

在测试mrr的时候，遇到了一个有趣的问题,这个问题已经提交官方bug，官方已经确定是个bug

| bai_test | CREATE TABLE `bai_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11756 DEFAULT CHARSET=utf8 |
mysql> explain select * from bai_test where num<50 and num>3000000;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | bai_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5246 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
这种情况下where中的条件明明返回的结果是空的了，但是mysql cbo没有进行优化处理，还是进行了where的全表扫描，下面在num上添加索引
| bai_test | CREATE TABLE `bai_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=11756 DEFAULT CHARSET=utf8 |

mysql> explain select * from bai_test where num<50 and num>3000000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)
这个时候，我们看到优化器能进行优化，直接返回了no matching row in const table

mysql> explain select * from bai_test where num>500 and num<1000;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | bai_test | NULL       | range | idx_num       | idx_num | 5       | NULL | 33162 |   100.00 | Using index condition |
+----+-------------+--------
mysq
l> explain select * from  t_bigtable where create_time>'2017-06-01' and create_time<'2017-06-05';
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_bigtable | NULL       | range | idx_create_time | idx_create_time | 6       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+

| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |

网上有很多的文章说在范围查询的时候会遇到使用mrr的情况，但是在我的测试环境中并没有出现use mrr的信息。看来什么时候使用mrr还是有更加强的条件才行的，并不是说select *的范围查询一定会出现，我猜测的情况是在索引字段上查询出的记录id不是顺序的，所以构造了一个包含自增主键 num是随机数的表，但是测试页没有出现mrr，后面再留意下吧。
http://blog.itpub.net/22664653/viewspace-1673682