mysql> show index fromsalaries;+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| salaries | 0 | PRIMARY | 1 | emp_no | A | 300557 | NULL | NULL | | BTREE | | |
| salaries | 0 | PRIMARY | 2 | from_date | A | 2838426 | NULL | NULL | | BTREE | | |
| salaries | 1 | emp_no | 1 | emp_no | A | 299974 | NULL | NULL | | BTREE | | |
| salaries | 1 | idx_salary | 1 | salary | A | 73229 | NULL | NULL | | BTREE | | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00sec)
默认使用的是mrr=on,mrr_cost_based=onmysql> explain select * from salaries s where s.salary between 68000 and 70000;+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | s | NULL | range | idx_salary | idx_salary | 4 | NULL | 222726 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00sec)
设置总是开启mrr
mysql> set optimizer_switch='mrr=on,mrr_cost_based=off';
Query OK,0 rows affected (0.00sec)
mysql> explain select * from salaries s where s.salary between 68000 and 70000;+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | s | NULL | range | idx_salary | idx_salary | 4 | NULL | 222726 | 100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)