mysql - 为什么mysql在使用 View 时忽略索引

Question

为什么 mysql 在使用 View 时不使用相同的索引，就像在没有 View 的情况下运行查询时一样:

我在查看之前的查询:

DESCRIBE SELECT membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death, COALESCE(memberships.death, '2099-12-31 23:59:59') AS to_date 
FROM memberships
LEFT JOIN groups ON (group_id = target_id)
LEFT JOIN citizens ON (citizen_id = member_id)
WHERE target_type = 31 AND member_type = 11
AND member_id = 5613956;
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
| id | select_type | table       | type   | possible_keys | key        | key_len | ref                                 | rows | Extra                    |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
|  1 | SIMPLE      | memberships | ref    | member,target | member     | 10      | const,const                         |    6 | Using where; Using index |
|  1 | SIMPLE      | groups      | eq_ref | group_id      | group_id   | 8       | interfaceways.memberships.target_id |    1 |                          |
|  1 | SIMPLE      | citizens    | const  | citizen_id    | citizen_id | 8       | const                               |    1 |                          |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+

它有效，而且速度很快，只是每次我想调试成员资格时编写的时间有点长。所以我基于该查询创建了一个 View :

CREATE VIEW group_members AS 
SELECT membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death, COALESCE(memberships.death, '2099-12-31 23:59:59') AS to_date 
FROM memberships 
LEFT JOIN groups ON (group_id = target_id) 
LEFT JOIN citizens ON (citizen_id = member_id) 
WHERE target_type = 31 AND member_type = 11;

但是当我尝试获取 5613956 的成员资格时，我得到了这个:

DESCRIBE SELECT * FROM group_members WHERE citizen_id = 5613956;
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+
| id | select_type | table       | type   | possible_keys | key      | key_len | ref                                 | rows    | Extra                    |
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+
|  1 | SIMPLE      | memberships | ref    | member,target | target   | 1       | const                               |    5269 | Using where; Using index |
|  1 | SIMPLE      | groups      | eq_ref | group_id      | group_id | 8       | interfaceways.memberships.target_id |       1 |                          |
|  1 | SIMPLE      | citizens    | ALL    | NULL          | NULL     | NULL    | NULL                                | 4766712 | Using where              |
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+

为什么公民的“possible_keys”为 NULL？

字段 citizen_id 是一个 SERIAL (= BIGINT(20) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT)

---

编辑 1

如果我在第一个查询中使用 citizen_id insted of member_id 我得到:

DESCRIBE SELECT membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death, COALESCE(memberships.death, '2099-12-31 23:59:59') AS to_date
FROM memberships
LEFT JOIN groups ON (group_id = target_id)
LEFT JOIN citizens ON (citizen_id = member_id)
WHERE target_type = 31 AND member_type = 11
AND citizen_id = 5613956;
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
| id | select_type | table       | type   | possible_keys | key        | key_len | ref                                 | rows | Extra                    |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
|  1 | SIMPLE      | citizens    | const  | citizen_id    | citizen_id | 8       | const                               |    1 |                          |
|  1 | SIMPLE      | memberships | ref    | member,target | member     | 10      | const,const                         |    6 | Using where; Using index |
|  1 | SIMPLE      | groups      | eq_ref | group_id      | group_id   | 8       | interfaceways.memberships.target_id |    1 |                          |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+

编辑 2

RIGHT JOIN 将问题移至已安装的成员表。 (需要 1054 x 行，而之前需要 4 766 712 x 行)

CREATE VIEW group_members AS 
SELECT membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death, COALESCE(memberships.death, '2099-12-31 23:59:59') AS to_date 
FROM memberships 
RIGHT JOIN groups ON (group_id = target_id) 
RIGHT JOIN citizens ON (citizen_id = member_id) 
WHERE target_type = 31 AND member_type = 11;

DESCRIBE SELECT * FROM group_members WHERE citizen_id = 5613956;
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+-------+--------------------------+
| id | select_type | table       | type   | possible_keys | key        | key_len | ref                                 | rows  | Extra                    |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+-------+--------------------------+
|  1 | SIMPLE      | citizens    | const  | citizen_id    | citizen_id | 8       | const                               |     1 |                          |
|  1 | SIMPLE      | memberships | index  | NULL          | member     | 45      | NULL                                | 10540 | Using where; Using index |
|  1 | SIMPLE      | groups      | eq_ref | group_id      | group_id   | 8       | interfaceways.memberships.target_id |     1 |                          |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+-------+--------------------------+

编辑 3一个有趣的注意事项:这更快，因为它使用索引，而 View 本身失败了

SELECT * FROM (SELECT * FROM group_members) AS t WHERE citizen_id = 5613956;
6 rows in set (0.03 sec)

DESCRIBE SELECT * FROM (SELECT * FROM group_members) AS t WHERE citizen_id = 5613956;
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
| id | select_type | table       | type   | possible_keys | key        | key_len | ref                                 | rows | Extra                    |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
|  1 | PRIMARY     | <derived2>  | ALL    | NULL          | NULL       | NULL    | NULL                                | 4423 | Using where              |
|  2 | DERIVED     | memberships | ref    | member,target | target     | 1       |                                     | 5270 | Using where; Using index |
|  2 | DERIVED     | groups      | eq_ref | group_id      | group_id   | 8       | interfaceways.memberships.target_id |    1 |                          |
|  2 | DERIVED     | citizens    | eq_ref | citizen_id    | citizen_id | 8       | interfaceways.memberships.member_id |    1 |                          |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+

EDIT 4 YaKs 评论

CREATE VIEW group_members AS 
SELECT membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death
FROM memberships 
LEFT JOIN groups ON (group_id = target_id) 
LEFT JOIN citizens ON (citizen_id = member_id) 
WHERE target_type = 31 AND member_type = 11;

DESCRIBE SELECT * FROM group_members WHERE citizen_id = 5613956;
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+
| id | select_type | table       | type   | possible_keys | key      | key_len | ref                                 | rows    | Extra                    |
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+
|  1 | SIMPLE      | memberships | ref    | member,target | target   | 1       | const                               |    5270 | Using where; Using index |
|  1 | SIMPLE      | groups      | eq_ref | group_id      | group_id | 8       | interfaceways.memberships.target_id |       1 |                          |
|  1 | SIMPLE      | citizens    | ALL    | NULL          | NULL     | NULL    | NULL                                | 4766736 | Using where              |
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+

编辑 5 jcho360 评论

ANALYZE TABLE group_members;
+-----------------------------+---------+----------+-------------------------------------------------+
| Table                       | Op      | Msg_type | Msg_text                                        |
+-----------------------------+---------+----------+-------------------------------------------------+
| interfaceways.group_members | analyze | Error    | 'interfaceways.group_members' is not BASE TABLE |
| interfaceways.group_members | analyze | error    | Corrupt                                         |
+-----------------------------+---------+----------+-------------------------------------------------+
2 rows in set (0.00 sec)

ANALYZE TABLE memberships;
+---------------------------+---------+----------+----------+
| Table                     | Op      | Msg_type | Msg_text |
+---------------------------+---------+----------+----------+
| interfaceways.memberships | analyze | status   | OK       |
+---------------------------+---------+----------+----------+
1 row in set (0.02 sec)

ANALYZE TABLE citizens;
+------------------------+---------+----------+----------+
| Table                  | Op      | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| interfaceways.citizens | analyze | status   | OK       |
+------------------------+---------+----------+----------+
1 row in set (0.11 sec)

ANALYZE TABLE groups;
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| interfaceways.groups | analyze | status   | OK       |
+----------------------+---------+----------+----------+
1 row in set (0.01 sec)

Answer 1

我正要添加评论，但它太多了。

试试这个:

Select * from 
(SELECT member_id, membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death, COALESCE(memberships.death, '2099-12-31 23:59:59') AS to_date 
FROM memberships
LEFT JOIN groups ON (group_id = target_id)
LEFT JOIN citizens ON (citizen_id = member_id)
WHERE target_type = 31 AND member_type = 11) as table1
where member_id = 5613956;

如果您愿意，您可以对该查询进行描述。我认为 View 表也会发生同样的情况，因此 where member_id=5613956 将不会使用 citizens id 索引，因为它没有在 where 条件下用于显示该查询。所以查询不是完全一样。