mysql mrr and an optimizer

When testing mrr, we encountered an interesting problem

| 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 |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
//In this case, the condition in where indicates that the returned result is empty, but mysql cbo does not perform optimization processing, or does a full table scan of where. Next, add an index on 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)
//At this time, we see that the optimizer can optimize and directly return 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 |

There are many articles on the Internet that say that mrr will be used in range query, but there is no use mrr information in my test environment. It seems that when mrr is used, there are still stronger conditions. That's not to say that the range query of select * is bound to occur. I guess that the record IDS found in the index fields are not in order. Therefore, a table containing the random number of auto increasing primary key num is constructed. However, mrr does not appear in the test page. Please pay attention to it later.
http://blog.itpub.net/22664653/viewspace-1673682

Tags: MySQL

Posted on Thu, 30 Apr 2020 21:52:35 -0700 by theoph