首页 > 代码库 > 模拟优化参数optimizer_switch选项mrr（Multi_Range Read）对查询优化的影响
模拟优化参数optimizer_switch选项mrr（Multi_Range Read）对查询优化的影响
11.1背景：模拟优化参数optimizer_switch选项mrr（Multi_Range Read）对查询优化的影响
11.2建表语句
mysql> show create table order_line\G;
*************************** 1. row ***************************
Table: order_line
Create Table: CREATE TABLE `order_line` (
`ol_o_id` int(11) NOT NULL,
`ol_d_id` tinyint(4) NOT NULL,
`ol_w_id` smallint(6) NOT NULL,
`ol_number` tinyint(4) NOT NULL,
`ol_i_id` int(11) DEFAULT NULL,
`ol_supply_w_id` smallint(6) DEFAULT NULL,
`ol_delivery_d` datetime DEFAULT NULL,
`ol_quantity` tinyint(4) DEFAULT NULL,
`ol_amount` decimal(6,2) DEFAULT NULL,
`ol_dist_info` char(24) DEFAULT NULL,
PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),
KEY `fkey_order_line_2` (`ol_supply_w_id`,`ol_i_id`),
CONSTRAINT `fkey_order_line_1` FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`) REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),
CONSTRAINT `fkey_order_line_2` FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`) REFERENCES `stock` (`s_w_id`, `s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
11.3查询语句
select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);
11.4示例及分析：
11.4.1源语句执行计划分析
mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | order_line | ALL | fkey_order_line_2 | NULL | NULL | NULL | 1701558 | 50.00 | Using where |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `tpcc`.`order_line`.`ol_o_id` AS `ol_o_id`,`tpcc`.`order_line`.`ol_d_id` AS `ol_d_id`,`tpcc`.`order_line`.`ol_w_id` AS `ol_w_id`,`tpcc`.`order_line`.`ol_number` AS `ol_number`,`tpcc`.`order_line`.`ol_i_id` AS `ol_i_id`,`tpcc`.`order_line`.`ol_supply_w_id` AS `ol_supply_w_id`,`tpcc`.`order_line`.`ol_delivery_d` AS `ol_delivery_d`,`tpcc`.`order_line`.`ol_quantity` AS `ol_quantity`,`tpcc`.`order_line`.`ol_amount` AS `ol_amount`,`tpcc`.`order_line`.`ol_dist_info` AS `ol_dist_info` from `tpcc`.`order_line` where ((`tpcc`.`order_line`.`ol_i_id` = 90000) and (`tpcc`.`order_line`.`ol_supply_w_id` > 0) and (`tpcc`.`order_line`.`ol_supply_w_id` < 6))
1 row in set (0.00 sec)
该语句执行了全表扫描。
11.4.2关闭mrr对执行计划的影响
注释掉快速预热缓冲池功能
#innodb_buffer_pool_dump_at_shutdown=1
#innodb_buffer_pool_dump_now=1
#innodb_buffer_pool_load_at_start=1
#innodb_buffer_pool_load_now=1
重启数据库示例
关闭mrr对数据查询的影响
mysql> set @@optimizer_switch=‘mrr=off,mrr_cost_based=on‘;
Query OK, 0 rows affected (0.00 sec)
mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | order_line | ALL | fkey_order_line_2 | NULL | NULL | NULL | 1638777 | 50.00 | Using where |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
执行全表扫描。
mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d | ol_quantity | ol_amount | ol_dist_info |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| 2124 | 7 | 2 | 5 | 90000 | 2 | 2014-07-24 16:12:33 | 5 | 0.00 | 3dvrSdvNlCswM5astj4EBmCd |
| 2833 | 10 | 2 | 4 | 90000 | 2 | NULL | 5 | 0.00 | jdJuW0MobORbaxEKWW5HWqK9 |
| 3268 | 2 | 3 | 11 | 90000 | 3 | NULL | 3 | 127.62 | HklNywWs02ab8XGFID2HpjK4 |
| 2838 | 3 | 3 | 5 | 90000 | 3 | NULL | 5 | 0.00 | DtqfRbDNgmJLH7Vn0xvk6uTS |
| 653 | 5 | 3 | 12 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 80.73 | QIBcVanwiqRhxVJPckA7KEbv |
| 909 | 5 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 74.72 | zcl2wdh3RGEdI8EhgOHhR8wT |
| 1129 | 5 | 3 | 5 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 62.02 | JtP5oa0koNxc2BPRoppNt98J |
| 1740 | 6 | 3 | 11 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 1.16 | 5rY0GnyMEPnQsitnTO5o5Y1I |
| 1306 | 9 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 64.57 | P8pWwOmvEZKNWCWEyuxPl4Z3 |
| 1203 | 10 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 33.59 | iCDsA2a62VtLQnUBvPGSTA5E |
| 3014 | 1 | 4 | 5 | 90000 | 4 | NULL | 8 | 551.15 | hJjs6oxAamoY5ZrhW2oboiIY |
| 840 | 5 | 4 | 9 | 90000 | 4 | 2014-07-24 15:52:26 | 5 | 55.06 | D85SGasZQaeFzFnpFOmOdtAW |
| 2444 | 7 | 4 | 7 | 90000 | 4 | NULL | 5 | 0.00 | 5RUMFQ9PDgfMyM1kPfnJayJu |
| 2577 | 4 | 5 | 3 | 90000 | 5 | NULL | 5 | 0.00 | 1sqa7AzfsPurzqvMHnpnGuKz |
| 620 | 6 | 5 | 6 | 90000 | 5 | 2014-07-24 15:52:26 | 5 | 72.78 | vskOqsHuimM1gd58oPxKoGA0 |
| 1247 | 8 | 5 | 9 | 90000 | 5 | 2014-07-24 15:52:26 | 5 | 89.96 | v0c9QGlqZwD9Mg9GtswM3f6q |
| 2628 | 9 | 5 | 9 | 90000 | 5 | NULL | 5 | 0.00 | LCLh9GhbMoJo9cXNk1Rhb9aX |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
17 rows in set (6.63 sec)
所花费时间为6.63 sec。
增加限制条件，使其使用索引。
mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1701557;
+----+-------------+------------+-------+-------------------+-------------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------+-------------------+-------------------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | order_line | range | fkey_order_line_2 | fkey_order_line_2 | 3 | NULL | 850779 | 100.00 | Using index condition |
+----+-------------+------------+-------+-------------------+-------------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `tpcc`.`order_line`.`ol_o_id` AS `ol_o_id`,`tpcc`.`order_line`.`ol_d_id` AS `ol_d_id`,`tpcc`.`order_line`.`ol_w_id` AS `ol_w_id`,`tpcc`.`order_line`.`ol_number` AS `ol_number`,`tpcc`.`order_line`.`ol_i_id` AS `ol_i_id`,`tpcc`.`order_line`.`ol_supply_w_id` AS `ol_supply_w_id`,`tpcc`.`order_line`.`ol_delivery_d` AS `ol_delivery_d`,`tpcc`.`order_line`.`ol_quantity` AS `ol_quantity`,`tpcc`.`order_line`.`ol_amount` AS `ol_amount`,`tpcc`.`order_line`.`ol_dist_info` AS `ol_dist_info` from `tpcc`.`order_line` where ((`tpcc`.`order_line`.`ol_i_id` = 90000) and (`tpcc`.`order_line`.`ol_supply_w_id` > 0) and (`tpcc`.`order_line`.`ol_supply_w_id` < 6)) limit 1500000
1 row in set (0.00 sec)
mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1701557;
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d | ol_quantity | ol_amount | ol_dist_info |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| 2124 | 7 | 2 | 5 | 90000 | 2 | 2014-07-24 16:12:33 | 5 | 0.00 | 3dvrSdvNlCswM5astj4EBmCd |
| 2833 | 10 | 2 | 4 | 90000 | 2 | NULL | 5 | 0.00 | jdJuW0MobORbaxEKWW5HWqK9 |
| 3268 | 2 | 3 | 11 | 90000 | 3 | NULL | 3 | 127.62 | HklNywWs02ab8XGFID2HpjK4 |
| 2838 | 3 | 3 | 5 | 90000 | 3 | NULL | 5 | 0.00 | DtqfRbDNgmJLH7Vn0xvk6uTS |
| 653 | 5 | 3 | 12 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 80.73 | QIBcVanwiqRhxVJPckA7KEbv |
| 909 | 5 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 74.72 | zcl2wdh3RGEdI8EhgOHhR8wT |
| 1129 | 5 | 3 | 5 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 62.02 | JtP5oa0koNxc2BPRoppNt98J |
| 1740 | 6 | 3 | 11 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 1.16 | 5rY0GnyMEPnQsitnTO5o5Y1I |
| 1306 | 9 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 64.57 | P8pWwOmvEZKNWCWEyuxPl4Z3 |
| 1203 | 10 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 33.59 | iCDsA2a62VtLQnUBvPGSTA5E |
| 3014 | 1 | 4 | 5 | 90000 | 4 | NULL | 8 | 551.15 | hJjs6oxAamoY5ZrhW2oboiIY |
| 840 | 5 | 4 | 9 | 90000 | 4 | 2014-07-24 15:52:26 | 5 | 55.06 | D85SGasZQaeFzFnpFOmOdtAW |
| 2444 | 7 | 4 | 7 | 90000 | 4 | NULL | 5 | 0.00 | 5RUMFQ9PDgfMyM1kPfnJayJu |
| 2577 | 4 | 5 | 3 | 90000 | 5 | NULL | 5 | 0.00 | 1sqa7AzfsPurzqvMHnpnGuKz |
| 620 | 6 | 5 | 6 | 90000 | 5 | 2014-07-24 15:52:26 | 5 | 72.78 | vskOqsHuimM1gd58oPxKoGA0 |
| 1247 | 8 | 5 | 9 | 90000 | 5 | 2014-07-24 15:52:26 | 5 | 89.96 | v0c9QGlqZwD9Mg9GtswM3f6q |
| 2628 | 9 | 5 | 9 | 90000 | 5 | NULL | 5 | 0.00 | LCLh9GhbMoJo9cXNk1Rhb9aX |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
17 rows in set (2.38 sec)
所花费时间为2.38sec。
11.4.2限制条件“limit n”的选择：
（1）limit n限制条件结果
mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1;
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d | ol_quantity | ol_amount | ol_dist_info |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| 2124 | 7 | 2 | 5 | 90000 | 2 | 2014-07-24 16:12:33 | 5 | 0.00 | 3dvrSdvNlCswM5astj4EBmCd |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
1 row in set (0.18 sec)
limit是对查询结果后的第一条记录的限制。
mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 2;
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d | ol_quantity | ol_amount | ol_dist_info |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| 2124 | 7 | 2 | 5 | 90000 | 2 | 2014-07-24 16:12:33 | 5 | 0.00 | 3dvrSdvNlCswM5astj4EBmCd |
| 2833 | 10 | 2 | 4 | 90000 | 2 | NULL | 5 | 0.00 | jdJuW0MobORbaxEKWW5HWqK9 |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
2 rows in set (0.19 sec)
limit是对查询结果后的前二条记录的限制。
mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 17;
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d | ol_quantity | ol_amount | ol_dist_info |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| 2124 | 7 | 2 | 5 | 90000 | 2 | 2014-07-24 16:12:33 | 5 | 0.00 | 3dvrSdvNlCswM5astj4EBmCd |
| 2833 | 10 | 2 | 4 | 90000 | 2 | NULL | 5 | 0.00 | jdJuW0MobORbaxEKWW5HWqK9 |
| 3268 | 2 | 3 | 11 | 90000 | 3 | NULL | 3 | 127.62 | HklNywWs02ab8XGFID2HpjK4 |
| 2838 | 3 | 3 | 5 | 90000 | 3 | NULL | 5 | 0.00 | DtqfRbDNgmJLH7Vn0xvk6uTS |
| 653 | 5 | 3 | 12 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 80.73 | QIBcVanwiqRhxVJPckA7KEbv |
| 909 | 5 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 74.72 | zcl2wdh3RGEdI8EhgOHhR8wT |
| 1129 | 5 | 3 | 5 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 62.02 | JtP5oa0koNxc2BPRoppNt98J |
| 1740 | 6 | 3 | 11 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 1.16 | 5rY0GnyMEPnQsitnTO5o5Y1I |
| 1306 | 9 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 64.57 | P8pWwOmvEZKNWCWEyuxPl4Z3 |
| 1203 | 10 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 33.59 | iCDsA2a62VtLQnUBvPGSTA5E |
| 3014 | 1 | 4 | 5 | 90000 | 4 | NULL | 8 | 551.15 | hJjs6oxAamoY5ZrhW2oboiIY |
| 840 | 5 | 4 | 9 | 90000 | 4 | 2014-07-24 15:52:26 | 5 | 55.06 | D85SGasZQaeFzFnpFOmOdtAW |
| 2444 | 7 | 4 | 7 | 90000 | 4 | NULL | 5 | 0.00 | 5RUMFQ9PDgfMyM1kPfnJayJu |
| 2577 | 4 | 5 | 3 | 90000 | 5 | NULL | 5 | 0.00 | 1sqa7AzfsPurzqvMHnpnGuKz |
| 620 | 6 | 5 | 6 | 90000 | 5 | 2014-07-24 15:52:26 | 5 | 72.78 | vskOqsHuimM1gd58oPxKoGA0 |
| 1247 | 8 | 5 | 9 | 90000 | 5 | 2014-07-24 15:52:26 | 5 | 89.96 | v0c9QGlqZwD9Mg9GtswM3f6q |
| 2628 | 9 | 5 | 9 | 90000 | 5 | NULL | 5 | 0.00 | LCLh9GhbMoJo9cXNk1Rhb9aX |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
17 rows in set (0.45 sec)
mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 18;
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d | ol_quantity | ol_amount | ol_dist_info |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| 2124 | 7 | 2 | 5 | 90000 | 2 | 2014-07-24 16:12:33 | 5 | 0.00 | 3dvrSdvNlCswM5astj4EBmCd |
| 2833 | 10 | 2 | 4 | 90000 | 2 | NULL | 5 | 0.00 | jdJuW0MobORbaxEKWW5HWqK9 |
| 3268 | 2 | 3 | 11 | 90000 | 3 | NULL | 3 | 127.62 | HklNywWs02ab8XGFID2HpjK4 |
| 2838 | 3 | 3 | 5 | 90000 | 3 | NULL | 5 | 0.00 | DtqfRbDNgmJLH7Vn0xvk6uTS |
| 653 | 5 | 3 | 12 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 80.73 | QIBcVanwiqRhxVJPckA7KEbv |
| 909 | 5 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 74.72 | zcl2wdh3RGEdI8EhgOHhR8wT |
| 1129 | 5 | 3 | 5 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 62.02 | JtP5oa0koNxc2BPRoppNt98J |
| 1740 | 6 | 3 | 11 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 1.16 | 5rY0GnyMEPnQsitnTO5o5Y1I |
| 1306 | 9 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 64.57 | P8pWwOmvEZKNWCWEyuxPl4Z3 |
| 1203 | 10 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 33.59 | iCDsA2a62VtLQnUBvPGSTA5E |
| 3014 | 1 | 4 | 5 | 90000 | 4 | NULL | 8 | 551.15 | hJjs6oxAamoY5ZrhW2oboiIY |
| 840 | 5 | 4 | 9 | 90000 | 4 | 2014-07-24 15:52:26 | 5 | 55.06 | D85SGasZQaeFzFnpFOmOdtAW |
| 2444 | 7 | 4 | 7 | 90000 | 4 | NULL | 5 | 0.00 | 5RUMFQ9PDgfMyM1kPfnJayJu |
| 2577 | 4 | 5 | 3 | 90000 | 5 | NULL | 5 | 0.00 | 1sqa7AzfsPurzqvMHnpnGuKz |
| 620 | 6 | 5 | 6 | 90000 | 5 | 2014-07-24 15:52:26 | 5 | 72.78 | vskOqsHuimM1gd58oPxKoGA0 |
| 1247 | 8 | 5 | 9 | 90000 | 5 | 2014-07-24 15:52:26 | 5 | 89.96 | v0c9QGlqZwD9Mg9GtswM3f6q |
| 2628 | 9 | 5 | 9 | 90000 | 5 | NULL | 5 | 0.00 | LCLh9GhbMoJo9cXNk1Rhb9aX |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
17 rows in set (0.44 sec)
limit是对查询结果后的所有记录的限制。
（2）limit n限制条件规律探索
由此，在不知道有多少查询记录的情况下，可以使用：
mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | order_line | ALL | fkey_order_line_2 | NULL | NULL | NULL | 1701558 | 50.00 | Using where |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到影响的行数为1701558，那么使用1701558看执行计划的结果：
mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1701558;
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | order_line | ALL | fkey_order_line_2 | NULL | NULL | NULL | 1701558 | 50.00 | Using where |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
使用n-1看执行计划的结果：
mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1701557;
+----+-------------+------------+-------+-------------------+-------------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------+-------------------+-------------------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | order_line | range | fkey_order_line_2 | fkey_order_line_2 | 3 | NULL | 850779 | 100.00 | Using index condition |
+----+-------------+------------+-------+-------------------+-------------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
11.4.3打开mrr对数据查询的影响
重启数据库
打开mrr功能
mysql> set @@optimizer_switch=‘mrr=on,mrr_cost_based=off‘;
Query OK, 0 rows affected (0.01 sec)
mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | order_line | ALL | fkey_order_line_2 | NULL | NULL | NULL | 1604617 | 50.00 | Using where |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `tpcc`.`order_line`.`ol_o_id` AS `ol_o_id`,`tpcc`.`order_line`.`ol_d_id` AS `ol_d_id`,`tpcc`.`order_line`.`ol_w_id` AS `ol_w_id`,`tpcc`.`order_line`.`ol_number` AS `ol_number`,`tpcc`.`order_line`.`ol_i_id` AS `ol_i_id`,`tpcc`.`order_line`.`ol_supply_w_id` AS `ol_supply_w_id`,`tpcc`.`order_line`.`ol_delivery_d` AS `ol_delivery_d`,`tpcc`.`order_line`.`ol_quantity` AS `ol_quantity`,`tpcc`.`order_line`.`ol_amount` AS `ol_amount`,`tpcc`.`order_line`.`ol_dist_info` AS `ol_dist_info` from `tpcc`.`order_line` where ((`tpcc`.`order_line`.`ol_i_id` = 90000) and (`tpcc`.`order_line`.`ol_supply_w_id` > 0) and (`tpcc`.`order_line`.`ol_supply_w_id` < 6))
1 row in set (0.01 sec)
使用的是全表扫描。
mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1604617;
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | order_line | ALL | fkey_order_line_2 | NULL | NULL | NULL | 1604617 | 50.00 | Using where |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1604616;
+----+-------------+------------+-------+-------------------+-------------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------+-------------------+-------------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | order_line | range | fkey_order_line_2 | fkey_order_line_2 | 3 | NULL | 802308 | 100.00 | Using index condition; Using MRR |
+----+-------------+------------+-------+-------------------+-------------------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `tpcc`.`order_line`.`ol_o_id` AS `ol_o_id`,`tpcc`.`order_line`.`ol_d_id` AS `ol_d_id`,`tpcc`.`order_line`.`ol_w_id` AS `ol_w_id`,`tpcc`.`order_line`.`ol_number` AS `ol_number`,`tpcc`.`order_line`.`ol_i_id` AS `ol_i_id`,`tpcc`.`order_line`.`ol_supply_w_id` AS `ol_supply_w_id`,`tpcc`.`order_line`.`ol_delivery_d` AS `ol_delivery_d`,`tpcc`.`order_line`.`ol_quantity` AS `ol_quantity`,`tpcc`.`order_line`.`ol_amount` AS `ol_amount`,`tpcc`.`order_line`.`ol_dist_info` AS `ol_dist_info` from `tpcc`.`order_line` where ((`tpcc`.`order_line`.`ol_i_id` = 90000) and (`tpcc`.`order_line`.`ol_supply_w_id` > 0) and (`tpcc`.`order_line`.`ol_supply_w_id` < 6)) limit 1604616
1 row in set (0.00 sec)
可以看到使用了索引和mrr优化参数
mysql> select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000) limit 1604616;
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| ol_o_id | ol_d_id | ol_w_id | ol_number | ol_i_id | ol_supply_w_id | ol_delivery_d | ol_quantity | ol_amount | ol_dist_info |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
| 2124 | 7 | 2 | 5 | 90000 | 2 | 2014-07-24 16:12:33 | 5 | 0.00 | 3dvrSdvNlCswM5astj4EBmCd |
| 2833 | 10 | 2 | 4 | 90000 | 2 | NULL | 5 | 0.00 | jdJuW0MobORbaxEKWW5HWqK9 |
| 3268 | 2 | 3 | 11 | 90000 | 3 | NULL | 3 | 127.62 | HklNywWs02ab8XGFID2HpjK4 |
| 2838 | 3 | 3 | 5 | 90000 | 3 | NULL | 5 | 0.00 | DtqfRbDNgmJLH7Vn0xvk6uTS |
| 653 | 5 | 3 | 12 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 80.73 | QIBcVanwiqRhxVJPckA7KEbv |
| 909 | 5 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 74.72 | zcl2wdh3RGEdI8EhgOHhR8wT |
| 1129 | 5 | 3 | 5 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 62.02 | JtP5oa0koNxc2BPRoppNt98J |
| 1740 | 6 | 3 | 11 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 1.16 | 5rY0GnyMEPnQsitnTO5o5Y1I |
| 1306 | 9 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 64.57 | P8pWwOmvEZKNWCWEyuxPl4Z3 |
| 1203 | 10 | 3 | 1 | 90000 | 3 | 2014-07-24 15:52:26 | 5 | 33.59 | iCDsA2a62VtLQnUBvPGSTA5E |
| 3014 | 1 | 4 | 5 | 90000 | 4 | NULL | 8 | 551.15 | hJjs6oxAamoY5ZrhW2oboiIY |
| 840 | 5 | 4 | 9 | 90000 | 4 | 2014-07-24 15:52:26 | 5 | 55.06 | D85SGasZQaeFzFnpFOmOdtAW |
| 2444 | 7 | 4 | 7 | 90000 | 4 | NULL | 5 | 0.00 | 5RUMFQ9PDgfMyM1kPfnJayJu |
| 2577 | 4 | 5 | 3 | 90000 | 5 | NULL | 5 | 0.00 | 1sqa7AzfsPurzqvMHnpnGuKz |
| 620 | 6 | 5 | 6 | 90000 | 5 | 2014-07-24 15:52:26 | 5 | 72.78 | vskOqsHuimM1gd58oPxKoGA0 |
| 1247 | 8 | 5 | 9 | 90000 | 5 | 2014-07-24 15:52:26 | 5 | 89.96 | v0c9QGlqZwD9Mg9GtswM3f6q |
| 2628 | 9 | 5 | 9 | 90000 | 5 | NULL | 5 | 0.00 | LCLh9GhbMoJo9cXNk1Rhb9aX |
+---------+---------+---------+-----------+---------+----------------+---------------------+-------------+-----------+--------------------------+
17 rows in set (0.44 sec)
所花费时间为0.94sec，比没有启用mrr缩短了近2sec，原先的是2.38sec。
11.5总结
（1）在索引的选择率影响索引的选择时，增加limit限制，使其使用索引，从而提高查询效率；
（2）limit n中的n的选择还有待进一步验证和探讨；
（3）MRR的使用的优化目的是减少磁盘的随机访问，其前提是使用索引，找到所需的行，并按照主键进行排序，并将随机访问转换为较为顺序的数据访问，其优化使用于range、ref和eq_ref类型的查询，也就是说非这些类型的查询，优化也就失去了作用：
mysql> explain extended select * from order_line where (ol_supply_w_id > 0 and ol_supply_w_id <6 ) and (ol_i_id =90000);
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | order_line | ALL | fkey_order_line_2 | NULL | NULL | NULL | 1604617 | 50.00 | Using where |
+----+-------------+------------+------+-------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到，仍然是全表扫描。
模拟优化参数optimizer_switch选项mrr（Multi_Range Read）对查询优化的影响