我有这个表格
attendance (4M rows at the moment, growing 1.2M per week):
-------------------------------------------------------------
| member_id | attendance_week | attendance_date | event_id |
------------------------------------------------------------
| INT (10) | TINYINT(2) | TIMESTAMP |TINYINT(3) |
-------------------------------------------------------------
attendance indeces:
--------------------------------------------------
| PRIMARY (attendance_week, member_id, event_id) |
| member_id (member_id) |
| event_id (event_id, attendance_week)
| total (attendance_week, event_id) |
--------------------------------------------------
members (400k rows at the moment growing 750 a week):
-------------------------
| member_id | dept_id |
-------------------------
| INT (10) |SMALLINT(5)|
-------------------------
member indeces:
-----------------------
| PRIMARY (member_id) |
|
-----------------------
活动是每周进行的，这意味着您会看到每周的member_id和event_id成对出现。
现在我必须为某个部门生成一份报告，包括每个事件、当前出席情况（即此成员是否已签到）以及他们在至少4周内的出席情况（即持续时间内出席的 / 总活动数）。
这是报告中 current_attendance 部分的内容。我获取该部门的所有成员，并使用本周的事件进行LEFT JOIN，以便获取缺席的NULL：
SELECT
m.member_id AS id,
a.event_id AS attended
FROM
members AS m
LEFT JOIN
attendance AS a
ON
a.member_id = m.member_id AND
a.attendance_week = :week AND
a.event_id = :event
WHERE
m.dept_id = :dept
GROUP BY
m.member_id
这是报告中的已出席部分。:
SELECT
a.member_id,
COUNT(a.event_id)
FROM
attendance a
JOIN
members m
ON
a.member_id = m.member_id AND
m.dept_id = :dept
WHERE
a.attendance_week BETWEEN :start AND :end
GROUP BY
a.member_id
我可以通过在第一个查询中再次使用
attendance表的LEFT JOIN来合并这两个查询。最后是
total部分。SELECT
attendance_week,
COUNT(DISTINCT event_id)
FROM
attendance
WHERE
attendance_week BETWEEN :start AND :end
GROUP BY
attendance_week
这些是将用于这些表的主要查询。目前，根据phpMyAdmin，这些查询平均运行时间为150-200ms，我认为这很慢。
EXPLAIN告诉我我的索引正在使用。所以这里是我的问题：
- 有没有其他方法可以修改我的索引和查询使其更快？
- 我假设MySQL有一个编译语句的缓存。我不是在谈结果缓存，而是PHP opcode与HTML缓存之间的区别。我已经尝试了
SQL_NO_CACHE，但仍然得到相同的响应时间，并且query_cache_size为0。我发誓曾经看到phpMyAdmin报告查询速度约为800ms（这是不可接受的），但现在我没有得到它们。如何测量每次运行查询的真实速度？ - 如果将这些查询放入存储过程中，它们会更快吗？
- 对于存储方法有什么想法？数据库当前的大小约为400MB。一年后，我不知道，也许是3GB？这是可扩展的吗？当涉及到DBA时，我真的很新，我已经阅读了主从复制和分区，但我不知道它是否适用于此。
谢谢
编辑
我刚刚意识到我的逻辑中有一个可怕的缺陷，新注册的成员将显示出低出勤率，因为第三个查询不考虑注册日期。我在我的成员表中有一个registration_date列，有没有办法将该变量合并到查询中？或者将所有三个查询合并为一个查询？因为它们都返回依赖于每个用户的值。
编辑
我已经成功合并了前两个查询：
SELECT
m.member_id AS id,
a.event_id AS attended,
COUNT(b.event_id) AS total_attended
FROM
members AS m
LEFT JOIN
attendance AS a
ON
a.member_id = m.member_id AND
a.attendance_week = :week AND
a.event_id = :event
LEFT JOIN
attendance AS b
ON
b.member_id = m.member_id AND
b.attendance_week BETWEEN :start AND :end
WHERE
m.dept_id = :dept
GROUP BY
m.member_id
第一次运行此查询需要925毫秒，而随后的请求则只需要15毫秒。
以上查询的EXPLAIN结果如下：
members table:
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: dept_id
key: dept_id
key_len: 3
ref: const
rows: 88
Extra: Using where; Using index
attendance table 1 (for the boolean attended part):
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY,member_id,event_id,total
key: PRIMARY
key_len: 6
ref: const,arms_db.m.member_id,const
rows: 1
Extra: Using index
attendance table 2 (for the total attendanded part):
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: PRIMARY,member_id,total
key: member_id
key_len: 4
ref: arms_db.m.member_id
rows: 5
Extra: Using index
最后一个查询的EXPLAIN：
id: 1
select_type: SIMPLE
table: attendance
type: range
possible_keys: PRIMARY,toral
key: total
key_len: 2
ref: NULL
rows: 9
Extra: Using where; Using index for groub-by
(event_id, attendance_week)索引，我认为这没有更好的索引了。它有多快？ - ypercubeᵀᴹ