Мое понимание серии MySQL, часть б. Почему LIMIT миллион смещений такой медленный?

задняя часть MySQL
Мое понимание серии MySQL, часть б. Почему LIMIT миллион смещений такой медленный?

1. Вступительные замечания

В MySQL мы обычно используем limit для завершения функции подкачки на странице, но когда объем данных достигает большого значения, чем дальше переворачивается страница, тем медленнее будет скорость отклика интерфейса.

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

2. Моделирование сценария

2.1 Оператор создания таблицы

Сначала создадим пользовательскую таблицу user, которая имеет относительно простую структуру и содержит три поля: id, пол и имя.Чтобы сделать изменение времени выполнения SQL более очевидным, сюда копируются 9 столбцов имени.

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sex` tinyint(4) NULL DEFAULT NULL COMMENT '性别 0-男 1-女',
  `name1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `name2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `name3` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `name4` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `name5` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `name6` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `name7` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `name8` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `name9` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `sex`(`sex`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2.2 Заполнение данных

Затем я создал хранимую процедуру для заполнения данных, всего 9000000 единиц данных. Эта функция требует времени для выполнения, я выполнил ее за 617,284 секунды.

CREATE DEFINER=`root`@`localhost` PROCEDURE `data`()
begin 
  declare i int; 
  set i=1; 
  while(i<=9000000)do 
    insert into user values(i,0,i,i,i,i,i,i,i,i,i);
    set i=i+1; 
  end while;
end

После выполнения функции выполните SQL, чтобы изменить поле гендера, чтобы отразить эффект индекса.

-- 将id为偶数的user设置性别为1-女
update user set sex=1 where id%2=0;

2.3 SQL и время выполнения

SQL время исполнения
select * from user where sex = 1 limit 100, 10; OK, Time: 0.005000s
select * from user where sex = 1 limit 1000, 10; OK, Time: 0.007000s
select * from user where sex = 1 limit 10000, 10; OK, Time: 0.016000s
select * from user where sex = 1 limit 100000, 10; OK, Time: 0.169000s
select * from user where sex = 1 limit 1000000, 10; OK, Time: 5.892000s
select * from user where sex = 1 limit 10000000, 10; OK, Time: 33.465000s

Видно, что чем больше смещение лимита, тем больше время выполнения.Время выполнения на миллионном уровне составило полминуты.Это просто простая пользовательская таблица.Если это реальная бизнес-таблица, возможно выполнить Сколько времени это займет.

2.4 Анализ причин

Во-первых, давайте проанализируем процесс выполнения этого SQL.

Поскольку столбец пола является столбцом индекса, MySQL будет проходить по дереву индекса пола и находить данные с полом = 1.

Затем, поскольку значение идентификатора первичного ключа хранится в обычном индексе, а оператор запроса требует, чтобы были запрошены все столбцы, будетформа возвратаСлучай. То есть, после попадания данных со значением 1 в дерево индекса пола, взять значение на его листовом узле, то есть идентификатор первичного ключа, для запроса значений других столбцов (имя, пол) в этом строку в дереве индекса первичного ключа и, наконец, вернуться к набору результатов. Таким образом, первая строка данных успешно запрашивается.

Последний SQL-запросlimit 100, 10, то есть запрашивать данные со 101-го по 110-е, но MySQLЗапросить первые 110 строк, а затем отбросить первые 100 строк., и, в конце концов, в результирующем наборе остаются только строки со 101 по 110, и выполнение завершается.

Подводя итог, в приведенном выше процессе выполнения причины длительного времени выполнения большого смещения лимита следующие:

  • Запрос всех столбцов приводит к возвращаемой таблице
  • limit a, bОн будет запрашивать первые a + b фрагментов данных, а затем отбрасывать первый фрагмент данных.

Сочетая две вышеуказанные причины, MySQL тратит много времени на таблицу возврата, и результаты таблицы возврата не будут отображаться в наборе результатов, что приводит к тому, что время запроса становится все больше и больше.

3. План оптимизации

3.1 Индекс покрытия

Поскольку недопустимая таблица возврата является основной причиной медленного запроса, план оптимизации в основном начинается с уменьшения количества таблиц возврата.limit a, bВ приведенном выше примере мы сначала получаем идентификаторы данных от a+1 до a+b, а затем возвращаемся к таблице, чтобы получить другие данные столбца, поэтому количество операций возврата к таблице будет уменьшено, а скорость точно будет выше.

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

Основываясь на этой идее, план оптимизации состоит в том, чтобы сначала запросить идентификатор первичного ключа, а затем запросить другие данные столбца на основе идентификатора первичного ключа.Оптимизированный SQL и время выполнения следующие.

Оптимизированный SQL время исполнения
select * from user a join (select id from user where sex = 1 limit 100, 10) b on a.id=b.id; OK, Time: 0.000000s
select * from user a join (select id from user where sex = 1 limit 1000, 10) b on a.id=b.id; OK, Time: 0.00000s
select * from user a join (select id from user where sex = 1 limit 10000, 10) b on a.id=b.id; OK, Time: 0.002000s
select * from user a join (select id from user where sex = 1 limit 100000, 10) b on a.id=b.id; OK, Time: 0.015000s
select * from user a join (select id from user where sex = 1 limit 1000000, 10) b on a.id=b.id; OK, Time: 0.151000s
select * from user a join (select id from user where sex = 1 limit 10000000, 10) b on a.id=b.id; OK, Time: 1.161000s

Конечно же, с точки зрения времени выполнения, эффективность выполнения была значительно улучшена.

3.2 Условная фильтрация

Конечно, есть и ошибочный метод условной фильтрации на основе сортировки.

Например, как и в приведенном выше примере пользовательской таблицы, я хочу использовать разбивку на страницы, чтобы получить от 1000001 до 1000010 фрагментов данных, я могу написать SQL следующим образом:

select * from user where sex = 1 and id > (select id from user where sex = 1 limit 1000000, 1) limit 10;

В этом смысле оптимизация условна: идентификатор первичного ключа должен быть упорядочен.

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

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

4. Резюме

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

Надеюсь помочь всем.

Наконец, эта статья включена в личную базу знаний Юке:Backend технология, как я ее понимаю,Добро пожаловать.

Категории