Так ли плох MySQL LIMIT?

задняя часть база данных MySQL

Так ли плох MySQL LIMIT?

Метка: статья в публичном аккаунте "Мы все маленькие лягушки".

Недавно ряд малых партнеров задали детям вопрос о LIMIT в группе вопросов и ответов.Позвольте мне кратко описать проблему ниже.

проблема

Для плавного развития сюжета у нас сначала должна быть таблица:

CREATE TABLE t (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1)
) Engine=InnoDB CHARSET=utf8;

Таблица t содержит 3 столбца, столбец id является первичным ключом, а столбец key1 является столбцом вторичного индекса. Таблица содержит 10 тысяч записей.

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

mysql>  EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx_key1 | 303     | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Это легко понять, потому что во вторичном индексе idx_key1 столбец key1 упорядочен. Запрос состоит в том, чтобы получить первую запись, отсортированную по столбцу key1, тогда MySQL нужно только получить первую запись вторичного индекса из idx_key1, а затем напрямую вернуть таблицу, чтобы получить полную запись.

Но если мы поместим приведенное выше утверждениеLIMIT 1заменитьLIMIT 5000, 1, то необходимо выполнить полное сканирование таблицы и файловую сортировку, план выполнения следующий:

mysql>  EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9966 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

Некоторые студенты не понимают:LIMIT 5000, 1Мы также можем использовать вторичный индекс idx_key1.Мы можем сначала просмотреть 5001-ю запись вторичного индекса, а затем выполнить операцию возврата таблицы для 5001-й записи вторичного индекса.Это определенно дороже, чем полное сканирование таблицы + сортировка файлов Strong.

К сожалению, вынужден вам сообщить, что из-за недочетов в реализации MySQL описанная выше идеальная ситуация не произойдет, а будет только тупо выполнять полное сканирование таблицы + сортировку файлов, давайте поговорим о том, что происходит.

уровень сервера и уровень механизма хранения

Как мы все знаем, MySQL на самом деле разделен на серверный уровень и уровень механизма хранения:

  • Уровень сервера отвечает за обработку общих вещей, таких как управление соединениями, разбор SQL и анализ планов выполнения.

  • Уровень механизма хранения отвечает за конкретное хранение данных, например за то, хранятся ли данные в файле или в памяти, а также за конкретный формат хранения. Сейчас мы в основном используем механизм хранения InnoDB, а другие механизмы хранения используются очень редко, поэтому мы не будем задействовать другие механизмы хранения.

Выполнение оператора SQL в MySQL достигается за счет множественных взаимодействий между уровнем сервера и уровнем механизма хранения для получения конечного результата. Например, следующий запрос:

SELECT * FROM t WHERE key1 > 'a' AND key1 < 'b' AND common_field != 'a';

Уровень сервера проанализирует, что приведенный выше оператор может быть выполнен с использованием следующих двух схем:

  • Вариант 1. Используйте полное сканирование таблицы

  • Вариант 2. Используйте вторичный индекс idx_key1. В настоящее время вам необходимо просмотреть все записи вторичного индекса со значением столбца key1 между ('a', 'b'), и каждую запись вторичного индекса необходимо вернуть в таблицу. .

Уровень сервера проанализирует, какое из двух вышеперечисленных решений имеет более низкую стоимость, а затем выберет решение с более низкой стоимостью в качестве плана выполнения. Затем вызовите интерфейс, предоставляемый механизмом хранения, чтобы фактически выполнить запрос.

Здесь предполагается, что принято второе решение, то есть вышеуказанный запрос выполняется с использованием вторичного индекса idx_key1. Тогда диалог между уровнем сервера и уровнем механизма хранения может быть следующим:

image_1fhn65ebn8d8us0o5f1jcp13m59.png-76.4kB

Уровень сервера: «Эй, пожалуйста, проверьте первую запись в интервале ('a', 'b') вторичного индекса idx_key1, а затем верните мне полную запись после возвращения к таблице».

InnoDB: «Получено, давайте проверим», а затем InnoDB быстро находит первую запись вторичного индекса в интервале сканирования («a», «b») через дерево B+, соответствующее вторичному индексу idx_key1, а затем выполняет «Возврат к таблицу, чтобы получить полную запись кластеризованного индекса и вернуть ее на серверный уровень.

image_1fhn686vcklf124011dfn4notm.png-67.1kB

После того, как уровень сервера получает полную запись кластеризованного индекса, он продолжает оцениватьcommon_field!='a'Верно ли условие, если нет, отбросить запись, иначе отправить запись клиенту. Затем скажите механизму хранения: «Пожалуйста, пришлите мне следующую запись».

Советы:

Отправка записи клиенту здесь фактически отправляет ее в локальный сетевой буфер.Размер буфера контролируется параметром net_buffer_length, и по умолчанию он равен 16 КБ. Подождите, пока буфер не заполнится, прежде чем отправлять сетевые пакеты клиенту.

InnoDB: «Получено, давайте проверим». InnoDB находит следующую запись вторичного индекса в интервале ('a', 'b') idx_key1 в соответствии с атрибутом next_record записи, а затем выполняет операцию возврата таблицы, чтобы вернуть полученную полную запись кластеризованного индекса на уровень сервера.

Советы:

Независимо от того, является ли это записью кластеризованного индекса или записью вторичного индекса, она содержитnext_recordАтрибут, каждая запись подключается в связанный список в соответствии со следующей_записью, а записи в связанном списке сортируются по значению ключа (для кластерного индекса значение ключа относится к значению первичного ключа, для записей вторичного индекса ключ значение относится к значению столбца вторичного индекса).

image_1fhn686vcklf124011dfn4notm.png-67.1kB

После того, как уровень сервера получает полную запись кластеризованного индекса, он продолжает оцениватьcommon_field!='a'Верно ли условие, если нет, отбросить запись, иначе отправить запись клиенту. Затем скажите механизму хранения: «Пожалуйста, пришлите мне следующую запись».

...и затем продолжайте повторять описанный выше процесс.

до того как:

image_1fhn6et6e2qh78r1plnd53rs213.png-61.9kB

То есть, пока InnoDB не обнаружит, что следующая запись вторичного индекса, полученная в соответствии с next_record записи вторичного индекса, не находится в интервале ('a', 'b'), она сообщает уровню сервера: "Хорошо, ('a ', 'б') В интервале нет следующей записи"

Уровень сервера получает сообщение о том, что InnoDB говорит, что следующей записи нет, и завершает запрос.

Теперь всем известен базовый процесс взаимодействия между уровнем сервера и уровнем механизма хранения.

Какой к черту LIMIT?

Вы можете быть немного удивлены, узнав, что MySQL обрабатывает содержимое предложения LIMIT только тогда, когда уровень сервера готов отправить записи клиенту. В качестве примера возьмем следующее предложение:

SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;

Если приведенный выше запрос выполняется с idx_key1, то MySQL обработает его следующим образом:

  • Уровень сервера запрашивает у InnoDB первую запись.InnoDB получает первую запись вторичного индекса из idx_key1, а затем выполняет операцию возврата таблицы для получения полной записи кластеризованного индекса, а затем возвращает ее на уровень сервера. Уровень сервера готов отправить его клиенту, и обнаруживается, что есть еще одинLIMIT 5000, 1Требование означает, что только 5001-я запись в допустимых записях может быть фактически отправлена ​​клиенту, поэтому давайте сделаем здесь статистику.Мы предполагаем, что уровень сервера поддерживает переменную с именем limit_count для подсчета количества пропущенных записей, тогда limit_count должен быть установлено на 1.

  • Уровень сервера запрашивает у InnoDB следующую запись, InnoDB находит следующую запись вторичного индекса в соответствии с атрибутом next_record записи вторичного индекса и снова возвращает таблицу, чтобы получить полную запись кластеризованного индекса, и возвращает ее на уровень сервера. Когда уровень сервера отправляет его клиенту, он обнаруживает, что limit_count равен 1, поэтому он отказывается от операции отправки его клиенту и увеличивает limit_count на 1. В это время limit_count становится равным 2.

  • ... повторите вышесказанное

  • До тех пор, пока limit_count не станет равным 5000, уровень сервера фактически отправит клиенту полную запись кластеризованного индекса, возвращенную InnoDB.

Из приведенного выше процесса мы можем видеть, что, поскольку MySQL определяет, соответствует ли предложение LIMIT требованиям, прежде чем фактически отправить запись клиенту, если вторичный индекс используется для выполнения вышеуказанного запроса, это означает, что 5001 раз возвращается таблица работает. Когда уровень сервера проанализирует план выполнения, он почувствует, что стоимость выполнения такого количества раз для возврата таблицы слишком высока.Это не так быстро, как прямое полное сканирование таблицы + сортировка файлов, поэтому он выбирает последнее для выполнения запроса. .

что делать?

Из-за ограничений в реализации MySQL предложения LIMIT при работе с такими элементами, какLIMIT 5000, 1Нельзя ли ускорить запрос, используя вторичный индекс для такого оператора? Не совсем, просто перепишите приведенное выше утверждение как:

SELECT * FROM t, (SELECT id FROM t ORDER BY key1 LIMIT 5000, 1) AS d
    WHERE t.id = d.id;

так,SELECT id FROM t ORDER BY key1 LIMIT 5000, 1Поскольку подзапрос существует один, потому что подзапрос имеет только один список запросовidстолбец, MySQL может выполнить подзапрос, только просмотрев вторичный индекс idx_key1, а затем выполнить поиск в таблице t в соответствии со значением первичного ключа, полученным в подзапросе.

Это экономит операцию возврата таблицы для первых 5000 записей, что значительно повышает эффективность запросов!

плевать

Когда дядя, разработавший MySQL, сможет изменить эту супер-тупую реализацию предложения LIMIT? Пользователю необходимо вручную обмануть оптимизатор, чтобы повысить эффективность запроса~

Эта статья была впервые опубликована в публичном аккаунте «Мы все маленькие лягушки», нажмите и удерживайте, чтобы следить за маленькими лягушками, они все галантереи.

image.png

Категории