1. 假设你使用了Innodb存储引擎

2. 假设你在innodb设定了主键(聚集索引)

3. 因为聚集索引页面之间是通过双向链表链接，页按照主键的顺序排序

每个页中的记录也是通过双向链表维护。聚集索引上存储了主键的值

由于B+树的特性，最左端的叶子节点存储最小的值，最右端的叶子节点存储最大的值。

4. 最小值的一般方法：我们可以看到没有使用key，设计的行299600行

root:employees 11:00 > select min(emp_no) from employees where gender='M';

+-------------+

| min(emp_no) |

+-------------+

| 10001 |

+-------------+

1 row in set (0.11 sec)

root:employees 11:07 > explain select min(emp_no) from employees where gender='M';

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299600 | Using where |

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

5. 利用上面的说明，取出最左端的叶子节点即可。此时我们看到执行时间很短，虽然explain结果比较困惑！

root:employees 11:12 > select emp_no from employees USE INDEX(PRIMARY) where gender='M' limit 1;

+--------+

| emp_no |

+--------+

| 10001 |

+--------+

1 row in set (0.00 sec)

root:employees 11:13 > explain select emp_no from employees USE INDEX(PRIMARY) where gender='M' limit 1;

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299600 | Using where |

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

6. 同样我们执行max最大值的时候，可以先倒排在取出第一个数据。因为页之间通过双向链表链接。

root:employees 11:18 > select max(emp_no) from employees where gender='M';

+-------------+

| max(emp_no) |

+-------------+

| 499999 |

+-------------+

1 row in set (0.22 sec)

root:employees 11:18 > select emp_no from employees USE INDEX(PRIMARY) where gender='M' order by emp_no desc limit 1;

+--------+

| emp_no |

+--------+

| 499999 |

+--------+

1 row in set (0.00 sec)

root:employees 11:18 > explain select emp_no from employees USE INDEX(PRIMARY) where gender='M' order by emp_no desc limit 1;

+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | employees | index | NULL | PRIMARY | 4 | NULL | 1 | Using where |

+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+

7.我们在查询范围的使用，也可以利用B+树的特性来迅速查询到我们想要的信息。因为B+树的索引页存储了主键的范围；

root:employees 11:22 > explain select emp_no from employees USE INDEX(PRIMARY) where gender='M' order by emp_no desc limit 1;

+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | employees | index | NULL | PRIMARY | 4 | NULL | 1 | Using where |

+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+

我有一个MySQL表(results)，包含如下所示的六列:+----+-------+-------+-------+-------+-------+

| id | can_1 | can_2 | can_3 | can_4 | can_5 |

+----+-------+-------+-------+-------+-------+

| 1 | 1 | 0 | 2 | 4 | 3 |

+----+-------+-------+-------+-------+-------+

| 2 | 2 | 1 | 5 | 3 | 4 |

+----+-------+-------+-------+-------+-------+

| 3 | 3 | 1 | 0 | 0 | 0 |

+----+-------+-------+-------+-------+-------+

| 4 | 0 | 2 | 1 | 0 | 3 |

+----+-------+-------+-------+-------+-------+

我试图根据每行中存储的值从can_1列中获取最小值大于1的行。

例如:can_1需要返回行(id)2和3，因为2是第2行的最小值，3是第3行的最小值。

使用SELECT id FROM results WHERE can_1 = (SELECT MIN(can_1) FROM results);

退货+----+

| id |

+----+

| 2 |

+----+

这是可以理解的-MIN-value检查整个列(can_1)中大于1的最小值，在can_1的情况下是2。 但是我如何组合这一点，以便整行(覆盖所有列)的最小值确定为can_1列返回的最小值，即。 结果会是这样？+----+

| id |

+----+

| 2 |

+----+

| 3 |

+----+