我有这张 table

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每周。

现在我必须为某个部门的每个事件生成报告，current attendance (即如果该成员已经签到)，以及他们至少 4 周的出勤率(即 attended/total 持续时间的事件)

这是为了 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

这是为了 attended报告的一部分。 :

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

我可能可以通过简单地 LEFT JOIN 合并这两个查询-ing attendance在第一次查询时再次表。

最后是 total部分

SELECT
  attendance_week,
  COUNT(DISTINCT event_id)
FROM
  attendance
WHERE
  attendance_week BETWEEN :start AND :end
GROUP BY
  attendance_week

这些是将为这些表运行的主要查询。目前，查询平均运行 150 - 200 毫秒(根据 phpMyAdmin)，我认为这很慢。 EXPLAIN告诉我我的索引正在使用。

所以这是我的问题:

1. 有没有其他方法可以修改我的索引和查询以加快速度？
2. 我假设 MySQL 有编译语句的缓存。我不是在谈论结果缓存，想想 PHP 操作码与 HTML 缓存。我已经试过了 SQL_NO_CACHE我仍然得到相同的响应时间，query_cache_size是 0。我可以发誓我曾看到 phpMyAdmin 在 800ms 左右报告了一次查询(这是 Not Acceptable )，但我现在没有得到它们。如何在每次运行查询时衡量它们的真实速度？
3. 如果我将这些查询放在存储过程中会更快吗？
4. 对存储方法有什么想法吗？该数据库目前的大小约为 400MB。一年后，我不知道，也许是 3GB？这是可扩展的吗？对于 DBA，我真的很陌生，我读过主从复制和分区，但我不知道它是否适合这个。

如果您需要更多信息，请在下方评论。我会尽力提供。我确实尝试过独自完成这项工作，但考虑到巨大数据库(我迄今为止最大的数据库)和高性能的需求，我真的需要一些建议 :D

谢谢

编辑

我刚刚意识到我的逻辑有一个严重的缺陷，新注册的成员(member)会显示出勤率很低，因为第三个查询没有考虑注册日期。我的成员表中有一个 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

最佳答案

添加covering或表上的聚簇索引将为您提供最佳性能:

1. 您还可以在表成员上添加额外的索引:

   成员索引:(member_id, dept_id)

2. 您可以启用 Query Cache缓存查询输出，但查询缓存不适用于过程。要测量查询的准确速度，您可以使用 mysqlslap 客户端实用程序。

3. 存储过程中的查询在速度方面不会有太大差异，但它会节省查询解析和将输出发送到客户端的一些额外开销。

4. 使用分片或复制将数据分布在不同的服务器上将有助于提高可扩展性。对大表进行分区也会使您受益。

关于用于特定查询的 MySQL 索引，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/11880874/