MYSQL查询优化（需要提高速度）

Question

我有一个MySQL表格，存储着Google Analytics的数据：

CREATE TABLE IF NOT EXISTS `analytics_data` (
  `ga_profile_id` int(11) NOT NULL,
  `page` varchar(200) NOT NULL,
  `source` varchar(150) NOT NULL,
  `medium` varchar(50) NOT NULL,
  `keyword` varchar(200) NOT NULL,
  `bounces` int(11) NOT NULL,
  `entrances` int(11) NOT NULL,
  `exits` int(11) NOT NULL,
  `new_visits` int(11) NOT NULL,
  `page_views` int(11) NOT NULL,
  `unique_page_views` int(11) NOT NULL,
  `time_on_page` int(11) NOT NULL,
  `visits` int(11) NOT NULL,
  `date` date NOT NULL,
  KEY `ga_profile_id` (`ga_profile_id`,`source`,`medium`,`date`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

我有一个查询，根据Google Analytics的配置文件ID（ga_profile_id）在给定的时间段内计算访问者总数：

SELECT 
    SUM( `visits` ), ( UNIX_TIMESTAMP( `date` ) - 21600 ) * 1000 AS date 
FROM `analytics_data` 
WHERE 
    `date` >= '2011-05-09' AND `date` <= '2011-06-08' AND `ga_profile_id` = [...]
GROUP BY `date`

我们大约有450万条记录。

索引数据：

Type: BTREE
Fields/Cardinality:
ga_profile_id / 100
source / 10196
medium / 10196
date / 149893

解释SELECT
- id: 1
- select_type: 简单查询
- table: analytics_data
- type: 参考查询
- possible_keys: ga_profile_id
- key: ga_profile_id
- ref: 常量
- rows: 219555
- extra: 使用where子句; 使用临时表; 使用文件排序

平均执行时间：1秒。

我们使用的是一个虚拟专用服务器，大多数查询的执行时间在0.0003-0.03秒之间。长查询（我曾经打算进行优化）一般为0.3秒。

我尝试调整键、忽略一些键、更改一些值，但似乎没有任何积极的影响。考虑到这是页面上的众多查询之一。

我正在思考将MyISAM更改为memory -- 欢迎提出任何想法。

- Kerry Jones

---

如果您创建一个复合索引 ga_profile_id + date，会怎样呢？此外，来自 EXPLAIN 的 key_length（如果我记得名字正确的话）可能会很有用。 - zerkms

您的查询有点奇怪：您将为每个不同的秒获得一个分组。这真的是您想要的吗？这似乎是非常详细的分类。 - Bohemian

请查看以下网址中提到的优化技巧：http://www.stackoverflow.com/questions/6236416/best-optimisation-tips-for-webservice-and-mysql/6237892#6237892。 - Pete Wilson

@zerkms，我展示了来自EXPLAIN的所有信息 - 这就是它所拥有的。 - Kerry Jones

@Bohemian - 我得到每个日期的一个分组 - 它是一个日期字段，而不是日期时间 :) - Kerry Jones

Answer 1

您需要按照特定顺序创建复合索引 ga_profile_id + date。使用此查询，您将获得最佳结果。

进一步的可能优化是预先计算每天访问量的总和，并将其用于快速计算。

- zerkms

---

这使得查询时间缩短到了0.09秒。非常完美。谢谢。 - Kerry Jones

Answer 2

我有一个查询，可以根据 Google Analytics 的配置文件 ID（ga_profile_id）在给定的时间段内计算访问者总数。

它似乎已经很优化了... 在你提出问题时，你剥离了查询中最有趣的部分（实际的 ga_profile_id 子句），这很可能是最具选择性的部分，因此当前使用索引。

在最好的情况下，如果你将其放在多列索引中，例如 (date, ga_profile_id) 或者反过来，取决于你的使用模式和表统计信息，你可以利用 date 上的索引。

请参见 indexes dos and donts。

- Denis de Bernardy

---

我以为我正在使用一个多列索引——里面有4个项目（另外2个是为了一些其他查询），但我可以尝试将它们移除。 - Kerry Jones

你确定索引应该从“date”开始吗？它是WHERE中的范围条件，因此第二部分将永远不会用于快速查找。 - zerkms

请务必查看它们插入的顺序。从左到右，选择性->排序标准。如果您在两列之间有任何内容需要查询，MySQL 中的索引甚至不会被考虑（在 Postgres 中，对于后一种情况，使用单列索引和位图扫描实际上更好）。 - Denis de Bernardy

@zerkms：我完全不知道“或者根据您的使用模式和表统计数据而反过来”。 - Denis de Bernardy

谢谢 @Denis - 所有这些信息对我非常有用。 - Kerry Jones

Answer 3

运行索引将是第一个和最简单的选项，但如果这不起作用，我建议更深入地研究一些基本的数据库管理策略，比如表分区。

- Nikolay Christov

Answer 4

@Kerry，看看Denis的解决方案... 他提供的唯一替代方案是将索引放在PROFILE ID FIRST，然后是Date上，否则，你的索引也会被其他在同一时间段内有操作的人所使用...

此外，@Bohemian的观点关于按秒分组是一个很有力的观点... 你可能想要根据完整日期/时间列结果的DATE ONLY部分进行排序。

- DRapp

---

Denis的解决方案不可行，因为它是范围比较。 - zerkms

Answer 5

如果您的查询中有典型的日期范围，那么您可以考虑对表进行水平分区。当大部分数据已经过时，而您只需要其中的“新鲜”数据时，将这些数据放在一个或多个分区中可能也会有所帮助，而将所有旧数据放在另一个分区中。RANGE Partitioning

- Fabian Barney

---

它总是最近的30天 - 这是一个很好的参考，但是否可能对最近的30天进行分区，而不是使用“日期小于”？ - Kerry Jones

首先，我必须说：您的查询现在非常快。如果您现在感到满意，我不建议使用分区。我知道MySQL也有一些问题。与Oracle数据库相比，这是MySQL的“新功能”，我不知道仍然存在哪些问题。关于“最近30天”的事情：我不确定，但我认为这是不可能的，因为它会导致持续的重新分区。但是，您可以每个月、每年或其他时间段添加一个分区。 - Fabian Barney