如何优化带有时间戳的SQL查询

Question

我有一个超过500k行的表，我试图使用索引来更快地获得结果。我用BTREE索引_Date和_Time，只有当我只查询_Date和_Time列时，它才有效。我怎样才能得到快速的结果，但包括所有列?我认为TEXT类型的列是一个问题，因为它们没有长度。

SELECT _Date, _Time FROM logsTable
WHERE TIMESTAMP(_Date, _Time)
BETWEEN "2021-08-29T13:04" AND "2021-08-29T15:04" 
LIMIT 135, 15;
#THIS IS THE INDEX THAT ONLY WORKS ONLY WITH _DATE AND _TIME
ALTER TABLE logsTable
ADD INDEX `logsTable_DateTime_Index` USING BTREE (`_Date`, `_Time`) VISIBLE;

表列:

id int AI PK 
raw text 
type1 varchar(100) 
type2 varchar(100) 
desc text 
address1 varchar(100) 
address2 varchar(100) 
num varchar(100) 
_Date date 
_Time time 
serie varchar(100)

Answer 1

解决方案是使用生成的列和索引

DESC是一个保留字，应该像使用_date一样避免使用，或者需要使用反引号

最后，没有ORDER By的行限制不是一个好主意，因为您无法预测结果

create table logsTable
(
id int AUTO_INCREMENT PRIMARY KEY,
raw text ,
type1 varchar(100) ,
type2 varchar(100) ,
`desc` text, 
address1 varchar(100) ,
address2 varchar(100) ,
num varchar(100) ,
_Date date ,
_Time time ,
serie varchar(100),
mytime timestamp GENERATED ALWAYS AS (TIMESTAMP(_Date, _Time)) STORED 
, INDEX `logsTable_DateTime_Index` (mytime));    

EXPLAIN
SELECT _Date, _Time FROM logsTable
WHERE mytime
BETWEEN "2021-08-29T13:04" AND "2021-08-29T15:04" 
ORDER BY mytime
LIMIT 135, 15;

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra-: | :---------- | :-------- | :--------- | :---- | :----------------------- | :----------------------- | :------ | :--- | ---: | -------: | :--------------------1 | SIMPLE | logsTable |null| range | logsTable_DateTime_Index | logsTable_DateTime_Index | 5 |null| 1 | 100.00 | Using index condition

db<此处小提琴>

Answer 2

首先，我建议这样编写查询:

SELECT _Date, _Time
FROM logsTable
WHERE _Date = '2021-08-29' AND
_Time BETWEEN '13:04:00' AND '15:04:00' 
LIMIT 135, 15;

然后你想在logsTable(_Date, _Time)上建立索引。

最后…您正在使用LIMIT而没有ORDER BY。不建议这样做。结果以不确定的顺序返回，并且在每次运行查询时可能会有所不同。

Answer 3

基本上你需要评估哪些列应该被索引，知道什么搜索操作将在这个特定的应用程序中执行。

下面是如何索引TEXT列的答案:https://dba.stackexchange.com/questions/210403/how-do-you-index-a-text-column-in-mysql其余的列很简单，但是您需要评估是否需要索引，因为每个索引都会增加插入数据等操作的开销。