#mysql #query-optimization #amazon-rds #amazon-aurora
#mysql #оптимизация запросов #amazon-rds #амазонка-аврора
Вопрос:
Я наблюдаю низкую производительность при использовании СМЕЩЕНИЯ ПРЕДЕЛА в операторе SELECT для таблицы, включающей столбец JSON.
Выполнение следующего запроса занимает 3 минуты:
SELECT t.json_column FROM table t LIMIT 501 OFFSET 216204; Однако, если я только выберу t.id , запрос займет всего пару миллисекунд.
Низкая производительность видна только тогда, когда столбец JSON является частью инструкции SELECT, но я не понимаю, почему и что я могу сделать, чтобы улучшить ее.
Чтобы дать больше контекста, версия MySQL, которую я использую, составляет 5,7, служба работает в базе данных AWS Aurora, а количество строк в таблице составляет ~216 000
Комментарии:
1. если вы не предоставляете заказ по, нет никакой гарантии, в каком порядке будут ваши результаты, или что они будут одинаковыми между двумя запросами, поэтому ваш лимит и смещение бессмысленны. можете ли вы привести пример, более близкий к тому, что вы на самом деле делаете?
2. Пожалуйста, укажите план выполнения обоих запросов.
3. Без возможности сравнить планы выполнения это обсуждение бесполезно. Голосование за закрытие.
4. @TheImpaler — Если вы видели одного
EXPLAINизLIMITних , вы видели их всех. И вы будете знать, чтоEXPLAINэто в основном бесполезно.5. Действительно ли кто-то прошел около 400 строк, чтобы перейти к этому запросу? Пожалуйста, объясните, как вы получили этот запрос.
Ответ №1:
Когда MySQL использует OFFSET , он не может просто перейти к строке 216204. Индексы MySQL индексируются по значению, а не по номеру строки. Таким образом, он должен фактически изучить все эти строки, что означает чтение страниц, на которых хранятся строки. При необходимости загружайте их с диска в оперативную память.
Когда вы только ссылаетесь t.id , он может сканировать по индексу. Я предположу id , что он проиндексирован (возможно, даже первичный ключ), и, вероятно, это целое число (4 байта) или bigint (8 байт). Независимо от этого, многие из этих записей могут поместиться на данной странице InnoDB (каждая страница имеет фиксированный размер, 16 КБ каждая).
В то время как столбец JSON похож на ТЕКСТ, большой двоичный объект или VARCHAR, в том смысле, что он, вероятно, хранится на дополнительных страницах, и он гораздо более громоздкий, чем одно целое число. Так что это займет больше страниц. Если документы JSON особенно велики, для каждого документа может потребоваться даже много страниц.
Так что это большой объем ввода-вывода для хранения всех этих документов JSON. Для загрузки 216 000 документов JSON требуется во много раз больше операций ввода-вывода, чем для ссылки только на первичный ключ. Возможно, страницы первичного ключа в любом случае уже кэшированы в оперативной памяти, поэтому для их чтения практически не требуется ввода-вывода или вообще не требуется.
С AWS Aurora может быть еще хуже, чем с традиционным MySQL, потому что Aurora использует распределенное хранилище и реплицированные буферные пулы, поэтому возникают дополнительные накладные расходы как на ввод-вывод, так и на загрузку в оперативную память.
Что вы можете сделать вместо этого?
Прекратите использовать большие значения в своем OFFSET . Вместо этого выполните поиск по значениям.
SELECT t.json_column FROM table t WHERE t.id gt;= 208000 LIMIT 501; Это быстро, потому что индекс помогает перейти непосредственно к 208 000-й строке, не просматривая все предыдущие строки. Ему нужно будет только изучить строки, начинающиеся со строки, где id=208000 (или любое другое значение, которое вы ищете). Он прекращает изучать строки, как только находит достаточно для вас LIMIT .
Комментарии:
1. это меня беспокоит;
SELECT json_column FROM table WHERE id in (SELECT id FROM table ORDER BY id LIMIT 501 OFFSET 216204)превосходитSELECT json_column FROM table ORDER BY id LIMIT 501 OFFSET 216204ли ? Я действительно не понимаю, почему это могло бы2. Это будет читаться только
idдля 216204 строк, поэтому это позволит избежать чтения такого количества документов JSON. Затем подзапрос вернет не более 501 идентификатора, и это приведет к тому, что внешний запрос будет использовать индекс для проверки только 501 строки с соответствующими документами JSON. Обычно быстрее выполнить ввод-вывод для загрузки 501 документа JSON, чем 216204 документов JSON.3. если
idэто PK, то основной индекс не крошечный, а включает в себя все столбцы таблицы.4. @TheImpaler, нет, если JSON достаточно большой. Данные типа длинной строки или большого двоичного объекта будут переполнены на другие страницы.
5.Это
SELECTпредполагает, что ниже 20800 идентификаторов не пропущено. Существует несколько способов, чтобы авто_инкремент отсутствовал.
Ответ №2:
Столбец JSON большой, правильно? Если это так, то это может работать быстрее:
SELECT t.json_column FROM ( SELECT id FROM table LIMIT 501 OFFSET 216204 ) AS ids JOIN table AS t USING(id); Внутренний запрос может быть в состоянии захватить 501 идентификатор достаточно быстро, чтобы компенсировать JOIN .
(Тем не менее, смотрите другие ответы и комментарии.)