MySQL 索引大表的性能

Question

长话短说:我有一个关于 2 个大表的查询。它们不是索引。它很慢。因此，我建立索引。它比较慢。为什么这是有道理的？怎样才是正确的优化方式？

背景:

我有两张 table

• person，一个包含人信息的表(id, birthdate)
• works_in，person和部门之间的0-N关系； works_in 包含id, person_id, department_id。

它们是 InnoDB 表，遗憾的是不能切换到 MyISAM，因为数据完整性是一项要求。

这 2 个表很大，除了它们各自的 id 上的 PRIMARY 之外不包含任何索引。

我正在尝试获取每个部门中最年轻的人的年龄，这是我提出的查询

SELECT MAX(YEAR(person.birthdate)) as max_year, works_in.department as department
    FROM person
    INNER JOIN works_in
        ON works_in.person_id = person.id
    WHERE person.birthdate IS NOT NULL
    GROUP BY works_in.department

查询有效，但我对性能不满意，因为它需要大约 17 秒才能运行。这是意料之中的，因为数据量很大，需要写入磁盘，而且它们不是表上的索引。

EXPLAIN 这个查询给出

| id | select_type | table   | type   | possible_keys | key     | key_len | ref                      | rows     | Extra                           | 
|----|-------------|---------|--------|---------------|---------|---------|--------------------------|----------|---------------------------------| 
| 1  | SIMPLE      | works_in| ALL    | NULL          | NULL    | NULL    | NULL                     | 22496409 | Using temporary; Using filesort | 
| 1  | SIMPLE      | person  | eq_ref | PRIMARY       | PRIMARY | 4       | dbtest.works_in.person_id| 1        | Using where                     | 

我为这两个表建立了一堆索引，

/* For works_in */
CREATE INDEX person_id ON works_in(person_id);
CREATE INDEX department_id ON works_in(department_id);
CREATE INDEX department_id_person ON works_in(department_id, person_id);
CREATE INDEX person_department_id ON works_in(person_id, department_id);
/* For person */
CREATE INDEX birthdate ON person(birthdate);

EXPLAIN 显示了一个改进，至少我是这样理解的，看到它现在使用索引并扫描更少的行。

| id | select_type | table   | type  | possible_keys                                    | key                  | key_len | ref              | rows   | Extra                                                 | 
|----|-------------|---------|-------|--------------------------------------------------|----------------------|---------|------------------|--------|-------------------------------------------------------| 
| 1  | SIMPLE      | person  | range | PRIMARY,birthdate                                | birthdate            | 4       | NULL             | 267818 | Using where; Using index; Using temporary; Using f... | 
| 1  | SIMPLE      | works_in| ref   | person,department_id_person,person_department_id | person_department_id | 4       | dbtest.person.id | 3      | Using index                                           | 

但是，查询的执行时间增加了一倍(从 ~17 秒到 ~35 秒)。

为什么这是有道理的，优化它的正确方法是什么？

编辑

使用 Gordon Linoff 的答案(第一个)，执行时间约为 9 秒(初始值的一半)。选择好的索引似乎确实有帮助，但执行时间仍然很长。关于如何改进这一点还有其他想法吗？

有关数据集的更多信息:

• person 表中大约有 5'000'000 条记录。
• 其中只有 130,000 人拥有有效的(不是 NULL)生日
• 我确实有一个部门表，其中包含大约 3'000'000 条记录(它们实际上是项目而不是部门)

Answer 1

对于这个查询:

SELECT MAX(YEAR(p.birthdate)) as max_year, wi.department as department
FROM person p INNER JOIN
     works_in wi
     ON wi.person_id = p.id
WHERE p.birthdate IS NOT NULL
GROUP BY wi.department;

最好的索引是:person(birthdate, id) 和 works_in(person_id, department)。这些覆盖索引为查询节省了读取数据页的额外开销。

顺便说一句，除非很多人的生日都是NULL(即有些部门的每个人的生日都是NULL)，查询基本上等同于:

SELECT MAX(YEAR(p.birthdate)) as max_year, wi.department as department
FROM person p INNER JOIN
     works_in wi
     ON wi.person_id = p.id
GROUP BY wi.department;

为此，最好的索引是 person(id, birthdate) 和 works_in(person_id, department)。

编辑:

我想不出一个简单的方法来解决这个问题。一种解决方案是更强大的硬件。

如果您真的很快就需要这些信息，则需要进行额外的工作。

一种方法是向departments 表添加最大出生日期，并添加触发器。对于 works_in，您需要用于 update、insert 和 delete 的触发器。对于persons，只有update(大概是insert和delete会被works_in处理) >).这节省了最后的 group by，这应该是一个很大的节省。

一种更简单的方法是仅向 works_in 添加最大出生日期。但是，您仍然需要最终聚合，这可能会很昂贵。