Maximum minimum value optimization for MySQL queries

1. Suppose you use the InnoDB storage engine
2. Suppose you set a primary key (clustered index) in InnoDB

3. Because the clustered index pages are linked by a two-way link, the page is sorted in the order of the primary key
The records in each page are also maintained through a doubly linked list. The value of the primary key is stored on the clustered index
Because of the characteristics of the B + tree, the leftmost leaf node stores the smallest value, and the leaf node at the right end stores the maximum value.

4. General method of minimum value: we can see that no key is used, the line of the design is 299600 lines
Root:employees 11:00am > 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. Use the instructions above to remove the left-most leaf node. At this point we see the execution time is very short, although explain results are more confused!
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. Also when we perform Max maximum, we can first rewind the first data. Because the pages are connected through a two-way chain table.
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 Li MIT 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. We use the scope of the query, we can also use B + Tree features to quickly query the information we want. Because the index page of the B + Tree stores the scope of the primary key;
Root:employees 11:22 > Explain select emp_no from employees use INDEX (PRIMARY) where gender= ' M ' ORDER by emp_no Desc Li MIT 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 |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+

Maximum minimum value optimization for MySQL queries