mysql - 如何提高大表中的插入时间？

Question

我们在 MySQL 表中有以下数据结构，它基本上记录页面上的用户操作

id int
page_id int
user_id int
action_type enum(6)
date_created datetime`

我们有以下索引:

id Primary key
user_id-page_id-date_created unique
page_id-user_id-date_created
user_id
page_id-date_created

我们的问题是，该表当前有 1.25 亿行，并且以每天 80 万行的速度增长，这使得插入需要大约 2 小时才能完成。插入是通过 3 个查询进行的，这些查询从其他 3 个表中选择数据。这次我们可以做些什么来改进？我们应该放弃 mysql 并尝试其他数据库解决方案吗？

L.E:根据您的反馈，我正在尝试提供更多信息。首先，这些表是 MyISAM，这些插入每晚在 cron 作业中发生一次，我们不会从中删除任何数据。这是我处理插入的方法。我将把大表称为 big_table，这 3 个表中的每一个都称为 content_table，因为它们的结构相似。解释将针对 3 个表中最大的一个，大约有 1.085 亿。首先我得到了我应该开始使用 php 插入的 id。 (我可以接受非索引查询 3 分钟的时间来获取它)

SELECT id FROM content_table WHERE date_created > "2012-04-18" ORDER BY id ASC LIMIT 1;
+-----------+
| id        |
+-----------+
| 107278872 |
+-----------+
1 row in set (3 min 15.52 sec)

EXPLAIN SELECT id FROM content_table WHERE date_created > "2012-04-18" ORDER BY id ASC LIMIT 1;
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | content_table    | index | NULL          | PRIMARY | 4       | NULL |    1 | Using where |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.06 sec)

然后使用这个 id 我执行以下操作

INSERT IGNORE INTO big_table (user_id, page_id, type, date_created) 
SELECT user_id, page_id, IF (is_admin,"admin_action","action") as type, created_time FROM content_table WHERE id >= "107278872";

以下是选择的解释:

EXPLAIN SELECT user_id, page_id, IF (is_admin,"admin_action","action") as type, created_time FROM content_table WHERE id >= "107278872";
+----+-------------+------------------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+------------------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | content_table    | range | PRIMARY       | PRIMARY | 4       | NULL | 777864 | Using where |
+----+-------------+------------------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

我也在 phpmyadmin 中尝试过，时间约为 0.004 秒，所以我认为是插入需要时间，而不是数据获取。我对服务器的了解是，它是四核至强@ 2.4 ghz 和 16 GB 内存，但我对存储一无所知(一旦我得到该信息，我就会回来)。并且数据不用于记录，只是我们需要统计数据，例如哪些用户在页面上最活跃，各种分组等，并且用户可以为这些指定任何间隔。

Answer 1

你可以:

1. 对您在其他 3 个表上运行的查询进行解释计划，以查看它们是否已正确建立索引。应消除表扫描。
2. 为查询中的每个 WHERE 子句向其他三个表添加索引。
3. 按天、周、月或其他合适的机制对数据进行分区，以便您可以将最旧的数据移出到报告/仓储解决方案。
4. 您可以看看触发解决方案是否可以帮助您。
5. 分析数据库并监控网络流量，以了解时间都花在哪里。

所有关系数据库都会因必须处理太多数据而受到影响。你的第一个想法不应该是放弃 MySQL；它应该弄清楚您的归档策略需要是什么。您必须准确决定在给定时间您的交易存储中需要多少数据。