Оптимизация таблицы MySQL для повышения производительности ORDER BY

У меня есть таблица с этой схемой:

    CREATE TABLE `data_realtime` (
     `id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
     `timestamp` int(10) NOT NULL,
     `ticker_id` smallint(5) unsigned NOT NULL,
     `price` decimal(7,2) unsigned NOT NULL,
     `volume` mediumint(9) unsigned NOT NULL,
     `bid` decimal(7,2) unsigned DEFAULT NULL,
     `bid_sz` smallint(6) unsigned DEFAULT NULL,
     `ask` decimal(7,2) unsigned DEFAULT NULL,
     `ask_sz` smallint(6) unsigned DEFAULT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `ticker_timestamp` (`ticker_id`,`timestamp`) USING BTREE,
     CONSTRAINT `data_realtime_ibfk_2` FOREIGN KEY (`ticker_id`) REFERENCES `tickers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=11330043 DEFAULT CHARSET=latin1

Я пытаюсь выполнить простой запрос, чтобы упорядочить данные по метке времени:

select * from data_realtime ORDER BY timestamp ASC

Это занимает 2,5 с (для ~ 4,5 млн строк, которые в конечном итоге увеличатся примерно до 12 млн строк). Но если я просто бегу

select * from data_realtime

это занимает 0,25 с

У меня есть составной индекс по метке времени (с ticker_id), который, как я думал, поможет решить эту проблему.

Что я могу сделать, чтобы улучшить производительность при заказе?

Спасибо.

EDIT: чтобы добавить к исходной проблеме, у меня есть этот запрос:

SELECT data_latest.*, data_1m.timestamp timestamp_1m, data_1m.price price_1m, data_1m.volume volume_1m FROM 
    (SELECT B.* FROM 
        (SELECT ticker_id, max(timestamp) max_timestamp FROM `data_rt` GROUP BY ticker_id) 
    A 
    LEFT JOIN 
    data_rt B 
    ON 
    A.ticker_id=B.ticker_id 
    and A.max_timestamp=B.timestamp) 
data_latest 
LEFT JOIN 
data_rt data_1m 
ON 
data_latest.timestamp <= (data_1m.timestamp + (60*1) ) 
AND data_latest.timestamp > (data_1m.timestamp + 60*(1-0.5)) 
AND data_latest.timestamp>data_1m.timestamp 
AND data_latest.ticker_id=data_1m.ticker_id 
ORDER BY data_1m.timestamp ASC

Для набора из 1 млн строк требуется около 1,3 с. Добавление последнего ORDER BY резко увеличивает время. Если вместо этого я ЗАКАЗЫВАЮ метку времени, это займет всего 0,05 с.

Что я могу улучшить при сортировке с использованием временного столбца?


mysql sql-order-by
person fractal5    schedule 15.01.2018    source источник
comment
В каком порядке находится составной индекс? (timestamp, ticker_id) должно помочь; (ticker_id, отметка времени) не будет.   -  person Uueerdo    schedule 15.01.2018
comment
Да, это (ticker_id, отметка времени). Почему порядок индексации имеет значение?   -  person fractal5    schedule 15.01.2018
comment
Порядок — это порядок сортировки; в таком индексе MySQL должен был бы пройти через каждый ticket_id, проверяя значения поля метки времени под каждым. Поскольку то, поможет ли это, ОЧЕНЬ зависит от данных, принято решение игнорировать такие индексы. (Зависит от данных, например: если каждый идентификатор билета имеет разную отметку времени, это очень дорого; но может быть полезно, если бы было относительно небольшое количество значений ticket_id, каждое из которых имело множество значений отметок времени.)   -  person Uueerdo    schedule 15.01.2018
comment
Это имеет смысл. И это сработало. Теперь запрос выполняется так же быстро, как и без сортировки. Большое спасибо!   -  person fractal5    schedule 15.01.2018

Ответы (1)


arrow_upward
1
arrow_downward

Индексация может помочь ускорить запросы; но только тогда, когда индексы являются теми, которыми MySQL воспользуется. Составные индексы, такие как индекс по (a, b), помогут в запросах, включающих a и b вместе; например, с WHERE a = N AND b = M или ORDER BY a, b. Такой индекс поможет даже в запросах, включающих только a. По сути, любой составной индекс (a, b, .... n) также действует как индексы (a, b, .... n-1), (a, b, .... n-2), ... (a, b) и (a).

Однако их применимость сильно различается в зависимости от фактических значений данных (см. мой второй комментарий к самому вопросу); их нельзя использовать для последних полей в индексе, если более ранние поля также не задействованы. т.е. (a, b) не используется, когда запросы включают только b. _(a,b,c, ...,n) может и часто будет использоваться для запросов, включающих (a,b,n), но будет работать так же эффективно, как индекс (a,b).

person Uueerdo    schedule 15.01.2018
comment
Немного отклоняюсь от исходного вопроса, но если бы я хотел упорядочить по временному столбцу, как бы я использовал индексирование в этом случае? Столбец temp является производным от самого столбца меток времени, но упорядочение по нему выполняется медленнее, чем упорядочение по исходному столбцу меток времени. - person fractal5; 16.01.2018
comment
Я добавил подвопрос к исходному вопросу с подробностями о проблеме. - person fractal5; 16.01.2018
comment
Условия включения кажутся мне немного странными, и на первый взгляд я задаюсь вопросом, получите ли вы какие-либо результаты для data_1m; но обычный способ, который я использую, если мне действительно нужна производительность запроса с таким подзапросом, заключается в том, чтобы сбросить результаты подзапроса в индексированную временную таблицу в предварительном запросе, а затем вместо этого присоединиться к временной таблице. - person Uueerdo; 16.01.2018