为什么MySQL在主索引上采用独特的索引？

Question

我有2个非常简单的表如下：

create table company(
    company_id int auto_increment,
    company_no varchar(20),
    company_name varchar(20),
    primary key(company_id),
    unique index uni_company_no(company_no)
);
    
create table department(
    department_id int auto_increment,
    department_no varchar(20),
    department_name varchar(20),
    company_id int not null,
    primary key(department_id),
    unique index uni_department_no(department_no),
    index idx_company_id(company_id)
);

如果我用follwing sql查询（我知道这是一个奇怪的查询），请说明Statenment的结果表明MySQL采用唯一的索引（uni_company_no）而不是主键：

explain select dept.* from department dept right join company com on dept.company_id = com.company_id;

解释结果 ：

我的问题是：

1. 为什么MySQL不为Table Company使用Full Table Scan？
2. 如果MySQL决定使用索引，为什么不使用Table Company的主要钥匙？

我是MySQL的新手，因此任何输入都可能会有所帮助。提前致谢。

Answer 1

我想可能有MySQL中有缓存，我关闭缓存并删除唯一的索引并查询两次。答案是相同的（添加唯一索引更快）。

mysql> # table structure
show create table big_tables;
 CREATE TABLE `big_tables` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `age` bigint DEFAULT NULL,
  `data` longblob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10010001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
1 row in set (0.06 sec)

mysql> # explain query1
select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----------+------------+-----+------------+
| id       | name       | age | data       |
+----------+------------+-----+------------+
| 10000001 | R0HWlAyf7R |   0 | kHDTpsmtcg |
+----------+------------+-----+------------+
1 row in set (6.94 sec)

mysql> # explain query1
explain select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | big_tables | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |   19290 |   100.00 | Using where |
|  2 | SUBQUERY    | big_tables | NULL       | index | NULL          | PRIMARY | 8       | NULL | 9750719 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
2 rows in set (2.93 sec)

mysql> # add unique index
alter table big_tables add unique index id(id) using btree;
Query OK, 0 rows affected (35.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> # query2
select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----------+------------+-----+------------+
| id       | name       | age | data       |
+----------+------------+-----+------------+
| 10000001 | R0HWlAyf7R |   0 | kHDTpsmtcg |
+----------+------------+-----+------------+
1 row in set (1.25 sec)

mysql> # explain query2
explain select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | big_tables | NULL       | range | PRIMARY,id    | PRIMARY | 8       | NULL |   19290 |   100.00 | Using where |
|  2 | SUBQUERY    | big_tables | NULL       | index | NULL          | id      | 8       | NULL | 9750719 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
2 rows in set (1.45 sec)

mysql> # delete unique index
alter table big_tables drop index id;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> # query3
select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----------+------------+-----+------------+
| id       | name       | age | data       |
+----------+------------+-----+------------+
| 10000001 | R0HWlAyf7R |   0 | kHDTpsmtcg |
+----------+------------+-----+------------+
1 row in set (3.06 sec)

mysql> # query4
select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;
+----------+------------+-----+------------+
| id       | name       | age | data       |
+----------+------------+-----+------------+
| 10000001 | R0HWlAyf7R |   0 | kHDTpsmtcg |
+----------+------------+-----+------------+
1 row in set (2.98 sec)