为了测试mysql 使用子查询后的explain PRIMARY SQL 使用的不是真实的索引，做一个简单测试：
先看表结构：
mysql > desc employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| a | enum('A') | NO | | A | |
| T | enum('A') | YES | | A | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
为了和实际情况一致，我把 a，和 T 的字段类型都设置为 enum 类型。加两个索引：
mysql> alter table employees add index emp_no2(`birth_date`)，add index emp_no3(`gender`);
mysql> show index from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees | 0 | PRIMARY | 1 | emp_no | A | 300030 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no2 | 1 | birth_date | A | 9678 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no3 | 1 | gender | A | 2 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
通过desc（当然也可以explain） 查看当前sql 执行计划，从输出的结果来看，primary 表使用的是 emp_no3 也就是 gender 列为索引：
mysql> desc select emp_no from employees where emp_no in (select emp_no from salaries);
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
| 1 | PRIMARY | employees | index | NULL | emp_no3 | 1 | NULL | 300030 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | salaries | index_subquery | PRIMARY,emp_no | emp_no | 4 | func | 4 | Using index |
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
我再次修改表的索引，使得看到的索引如下：
mysql> show index from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees | 0 | PRIMARY | 1 | emp_no | A | 299654 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no2 | 1 | birth_date | A | 9666 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no3 | 1 | gender | A | 6 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no4 | 1 | T | A | 6 | NULL | NULL | YES | BTREE | | |
| employees | 1 | emp_no1 | 1 | a | A | 2 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
通过desc 查看当前sql 执行计划，从输出的结果来看，primary 表仍然使用的是 emp_no3 ，没有选择 emp_no4 或者 emp_no1 作为索引：
mysql> desc select emp_no from employees where emp_no in (select emp_no from salaries);
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
| 1 | PRIMARY | employees | index | NULL | emp_no3 | 1 | NULL | 299654 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | salaries | index_subquery | PRIMARY,emp_no | emp_no | 4 | func | 4 | Using index |
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
难道同一类型的字段会和顺序有关吗？为了验证一下，我再次修改表，使得索引显示的顺序如下：
mysql> show index from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees | 0 | PRIMARY | 1 | emp_no | A | 300600 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no2 | 1 | birth_date | A | 10020 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no4 | 1 | T | A | 6 | NULL | NULL | YES | BTREE | | |
| employees | 1 | emp_no1 | 1 | a | A | 6 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no3 | 1 | gender | A | 2 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
再次执行，我们看到当索引的位置顺序发生变化后，primary 表的key 变成了 emp_no1，那为什么不是emp_no4 呢？ 原因是 T 这个字段未设置为 not null。
mysql> desc select emp_no from employees where emp_no in (select emp_no from salaries);
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
| 1 | PRIMARY | employees | index | NULL | emp_no1 | 1 | NULL | 301073 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | salaries | index_subquery | PRIMARY,emp_no | emp_no | 4 | func | 4 | Using index |
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
执行 alter table employees change `T` `T` enum('A') not null DEFAULT 'A';
修改表字段 T 为 not null 之后，使用的索引就变成 emp_no4 了
mysql> show index from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees | 0 | PRIMARY | 1 | emp_no | A | 299917 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no2 | 1 | birth_date | A | 9997 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no4 | 1 | T | A | 6 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no1 | 1 | a | A | 6 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no3 | 1 | gender | A | 54 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> desc select emp_no from employees where emp_no in (select emp_no from salaries);
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
| 1 | PRIMARY | employees | index | NULL | emp_no4 | 1 | NULL | 299917 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | salaries | index_subquery | PRIMARY,emp_no | emp_no | 4 | func | 4 | Using index |
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
2 rows in set (0.01 sec)
最后，我们来看看会不会用到 emp_no2 呢？我们把 emp_no1，emp_no3，emp_no4 都删除，只保留 emp_no2 和primary key 的 emp_no。
mysql> alter table employees drop index emp_no1, drop index emp_no3,drop index emp_no4;
mysql> show index from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees | 0 | PRIMARY | 1 | emp_no | A | 299917 | NULL | NULL | | BTREE | | |
| employees | 1 | emp_no2 | 1 | birth_date | A | 9997 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> desc select emp_no from employees where emp_no in (select emp_no from salaries);
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
| 1 | PRIMARY | employees | index | NULL | emp_no2 | 3 | NULL | 300718 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | salaries | index_subquery | PRIMARY,emp_no | emp_no | 4 | func | 4 | Using index |
+----+--------------------+-----------+----------------+----------------+---------+---------+------+--------+--------------------------+
没有评论:
发表评论