模拟优化参数optimizer_switch选项mrr（Multi_Range Read）对查询优化的影响

4etfeww

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)
        可以看到，仍然是全表扫描。