MySQL查询索引及性能改进
我为我和我的朋友创建了一个应用程序来跟踪英雄联盟的进度。为此，我每天在MySQL数据库中多次收集有关当前级别的信息。为了获取结果并在图中显示结果，我使用以下查询/查询：
SELECT
lol_summoner.name as name, grid.series + ? as timestamp,
AVG(NULLIF(lol.points, 0)) as points
FROM
series_tmp grid
JOIN
lol ON lol.timestamp >= grid.series AND lol.timestamp < grid.series + ?
JOIN
lol_summoner ON lol.summoner = lol_summoner.id
GROUP BY
lol_summoner.name, grid.series
ORDER BY
name, timestamp ASC
SELECT
lol_summoner.name as name, grid.series + ? as timestamp,
AVG(NULLIF(lol.points, 0)) as points
FROM
series_tmp grid
JOIN
lol ON lol.timestamp >= grid.series AND lol.timestamp < grid.series + ?
JOIN
lol_summoner ON lol.summoner = lol_summoner.id
WHERE
lol_summoner.name IN (". str_repeat('?, ', count($names) - 1) ."?)
GROUP BY
lol_summoner.name, grid.series
ORDER BY
name, timestamp ASC如果我想检索数据库中保存的所有播放器，则使用第一个查询。网格表是一个临时表，它在特定的时间间隔内生成时间戳，以在此间隔的块中检索信息。此查询中的两个变量是间隔。如果我只想检索特定玩家的信息，则使用第二个查询。
网格表由以下存储过程生成，该存储过程具有三个参数(n_first -第一个时间戳、n_last -最后一个时间戳、n_increments -两个时间戳之间的增量)：
BEGIN
-- Create tmp table
DROP TEMPORARY TABLE IF EXISTS series_tmp;
CREATE TEMPORARY TABLE series_tmp (
series bigint
) engine = memory;
WHILE n_first <= n_last DO
-- Insert in tmp table
INSERT INTO series_tmp (series) VALUES (n_first);
-- Increment value by one
SET n_first = n_first + n_increment;
END WHILE;
END查询工作并在合理的时间内完成(~10秒)，但我感谢通过重写查询或向数据库添加附加索引来改进查询的任何帮助。
/Edit：
在查看了@Rick的答案之后，我将查询修改如下：
SELECT lol_summoner.name as name, (lol.timestamp div :range) * :range + :half_range as timestamp, AVG(NULLIF(lol.points, 0)) as points
FROM lol
JOIN lol_summoner ON lol.summoner = lol_summoner.id
GROUP by lol_summoner.name, lol.timestamp div :range
ORDER by name, timestamp ASC
SELECT lol_summoner.name as name, (lol.timestamp div :range) * :range + :half_range as timestamp, AVG(NULLIF(lol.points, 0)) as points
FROM lol
JOIN lol_summoner ON lol.summoner = lol_summoner.id
WHERE lol_summoner.name IN (<NAMES>)
GROUP by lol_summoner.name, lol.timestamp div " . $steps . "
ORDER by name, timestamp ASC这改善了查询执行时间，提高了很好的利润率(1s以下完成)。
回答 1
Stack Overflow用户
发布于 2016-01-07 17:41:24
问题1与解
你需要两个值之间的一系列整数吗？他们的差别是1？还是更大的价值？
首先，创建一个数字从0到足够大的值的永久表：
CREATE TABLE Num10 ( n INT );
INSERT INTO Num10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE Nums ( n INT, PRIMARY KEY(n))
SELECT a.n*1000 + b.n*100 + c.n*10 + d.n
FROM Num10 AS a
JOIN Num10 AS b -- note "cross join"
JOIN Num10 AS c
JOIN Num10 AS d;现在Nums有0..9999。(如果你需要更多的话，就把它做大。)
要获得从123到234的连续数字序列：
SELECT 123 + n FROM Nums WHERE n < 234-123+1;要获得从12345到23456的连续数字序列，步骤为15：
SELECT 12345 + 15*n FROM Nums WHERE n < (23456-12345+1)/15;JOIN到SELECT，就像其中的一个，而不是series_tmp。
除其他问题外，这将大大加速事态的发展。
问题2
你是GROUPing BY series，ORDERing是timestamp。它们是相关的，所以你可能会得到‘正确’的答案。但想想看。
问题3
你似乎在建造“水桶”(称为“系列”？)来自“时间戳”。这是正确的吗？如果是这样的话，让我们倒转--将“时间戳”转换为“桶”号：
bucket_number = (timestamp - start) / bucket_size通过在整个过程中这样做，您可以避免“问题1”，并消除我对它的解决方案。也就是说，根据桶来重新表示整个查询。
https://stackoverflow.com/questions/34657561
复制相似问题
腾讯云开发者
