MySQL-SQLite如何改善这个非常简单的查询? [英] MySQL - SQLite How to improve this very simple query?

问题描述

我有一张简单但又大的桌子.

I have one simple but large table.

id_tick   INTEGER      eg: 1622911
price     DOUBLE       eg: 1.31723
timestamp DATETIME     eg: '2010-04-28 09:34:23'

对于1个月的数据，我有230万行(150MB)

For 1 month of data, I have 2.3 millions rows (150MB)

我的查询旨在返回给定时间的最新价格.

My query aims at returning the latest price at a given time.

我首先建立一个SQLite表并使用查询:

I first set up a SQLite table and used the query:

SELECT max(id_tick), price, timestamp 
FROM EURUSD 
WHERE timestamp <='2010-04-16 15:22:05'

它的运行时间为1.6秒.

It is running in 1.6s.

由于我需要运行该查询数千次，所以1.6s太长了...

As I need to run this query several thousands of time, 1.6s is by far too long...

然后，我建立一个MySQL表并修改查询(max函数因MySQL与SQLite的不同而不同):

I then set up a MySQL table and modified the query (the max function differs from MySQL to SQLite):

SELECT id_tick, price, timestamp
FROM EURUSD
WHERE id_tick = (SELECT MAX(id_tick) 
                 FROM EURUSD WHERE timestamp <='2010-04-16 15:22:05')

执行时间越来越差3.6秒 (我知道我可以避免使用ORDER BY和LIMIT 1的子查询，但这不会缩短执行时间.)

Execution time is getting far worse 3.6s (I know I can avoid the sub query using ORDER BY and LIMIT 1 but it does not improve the execution time.)

我目前仅使用一个月的数据，但是在某些时候我将不得不使用数年.

I am only using one month of data for now, but I will have to use several years at some point.

那么我的问题如下:

1. 有没有一种方法可以改善我的查询?
2. 鉴于数据集很大，我应该使用其他数据库引擎吗?
3. 任何提示吗?

谢谢！

推荐答案

1)确保您在时间戳上有索引

1) Make sure you have an index on timestamp

2)假设id_tick既是主键又是聚集索引，并假设id_tick随时间增加(因为您正在执行MAX)

2) Assuming that id_tick is both the PRIMARY KEY and Clustered Index, and assuming that id_tick increments as a function of time (since you are doing a MAX)

您可以尝试以下操作:

SELECT id_tick, price, timestamp 
FROM EURUSD 
WHERE id_tick = (SELECT id_tick
                   FROM EURUSD WHERE timestamp <='2010-04-16 15:22:05'
                   ORDER BY id_tick DESC
                   LIMIT 1)

这应该与janmoesen的性能类似，因为在任何情况下id_tick和timestamp之间都应具有较高的页面相关性

This should be similar to janmoesen's performance though, since there should be high page correlation between id_tick and timestamp in any event

这篇关于MySQL-SQLite如何改善这个非常简单的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！