Mysql范围查询

当查询结果在一定范围内，即有多条数据匹配查询条件时称为范围查询。唯一查询可以看成是特殊的范围查询，范围内只有一条数据。

通常where条件中使用了=, >, =, desc emps;
+------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| emp_no | int | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| first_name | varchar(14) | YES | | NULL | |
| last_name | varchar(16) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| hire_date | date | YES | | NULL | |
+------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> show table status like 'emps'\G
*************************** 1. row ***************************
Name: emps
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 299440
Avg_row_length: 54
Data_length: 16269312
Max_data_length: 0
Index_length: 5783552
Data_free: 2097152
Auto_increment: 327676
Create_time: 2023-05-26 12:09:47
Update_time: 2023-05-26 12:07:09
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> select count(1) from emps;
+----------+
| count(1) |
+----------+
| 300024 |
+----------+
1 row in set (0.01 sec)

mysql> show indexes from emps;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emps | 0 | PRIMARY | 1 | id | A | 299341 | NULL | NULL | | BTREE | | | YES | NULL |
| emps | 0 | emp_no_uk | 1 | emp_no | A | 299341 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

以emp_no为条件执行如下查询, 查询使用到了emp_no上的唯一索引

mysql> desc select * from emps e where e.emp_no desc select * from emps e where e.emp_no desc format=tree select * from emps e where e.emp_no Filter: (e.emp_no Table scan on e (cost=30178 rows=299294)
|
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


预估的使用索引的成本为40075

mysql> desc format=tree select * from emps e force index(emp_no_uk) where e.emp_no Index range scan on e using emp_no_uk over (emp_no