Настоящий бой! Поговорите о том, как решить проблему глубокого пейджинга MySQL

интервью Java задняя часть

предисловие

Привет всем, ямаленький мальчик собирает улиток.

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

  • публика:маленький мальчик собирает улиток

Почему предел глубокого пейджинга работает медленно?

Взгляните на структуру таблицы ниже:

CREATE TABLE account (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  name varchar(255) DEFAULT NULL COMMENT '账户名',
  balance int(11) DEFAULT NULL COMMENT '余额',
  create_time datetime NOT NULL COMMENT '创建时间',
  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id),
  KEY idx_name (name),
  KEY idx_update_time (update_time) //索引
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

Предположим, что выполнение SQL глубокого пейджинга выглядит следующим образом:

select id,name,balance from account where update_time> '2020-09-19' limit 100000,10;

Время выполнения этого SQL выглядит следующим образом:

нужно выполнить0.742Секунды, почему происходит глубокое пейджинг?замедлятьШерстяная ткань? если заменить наlimit 0,10,нужно всего лишь0.006секунды

Давайте посмотрим на процесс выполнения SQL:

  1. пройти черезОбычное вторичное индексное деревоidx_update_time отфильтруйте условие update_time, чтобы найти идентификатор записи, удовлетворяющий условию.
  2. по ID, вернуться кдерево индексов первичного ключа, найдите строку, соответствующую записи, а затем извлеките отображаемый столбец (форма возврата)
  3. Просканировать 100010 строк, удовлетворяющих условию, затем отбросить первые 100000 строк и вернуться.

SQL的执行流程

Поток выполнения SQL

План реализацииследующее:

SQL работает медленно по двум причинам:

  1. limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。 то естьlimit 100000,10, он просканирует 100010 строк иlimit 0,10, сканирует только 10 строк.
  2. limit 100000,10Сканировать больше строк, также означаетформа возвратабольше раз.

Оптимизация по подзапросам

Из-за вышеуказанного SQL таблица была возвращена в 100 010 раз. На самом деле нам нужно всего 10 штук данных, то есть нам нужно только вернуть таблицу 10 раз. Поэтому мы можем пройтиУменьшить количество возвратовоптимизировать.

Просмотрите древовидную структуру B+

Итак, как уменьшить количество форм возврата? Давайте сначала рассмотрим структуру индекса дерева B+~

В InnoDB индексы делятся на индекс первичного ключа (кластеризованный индекс) и вторичный индекс.

  • Индекс первичного ключа, конечные узлы хранят всю строку данных
  • Вторичный индекс, узелки листьев хранятзначение первичного ключа.

Перенести условие в дерево индекса первичного ключа

Если мы перенесем условия запроса обратно в индексное дерево первичного ключа, то сможем уменьшить количество раз возврата к таблице. Если вы перейдете к запросу дерева индекса первичного ключа, условие запроса должно быть изменено на主键id, перед SQLupdate_timeА как насчет этих условий? рисоватьподзапросгде?

Как рисуется подзапрос? Поскольку конечные узлы вторичного индекса имеют идентификаторы первичного ключа, мы напрямуюupdate_timeПросто найдите идентификатор первичного ключа, и мы поместимlimit 100000Условие также передается в подзапрос, полный SQL выглядит следующим образом:

select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10;（可以加下时间条件到外面的主查询）

Эффект запроса тот же, а время выполнения всего 0,038 секунды!

Давайте посмотрим на план выполнения

Что план выполнения, таблица подзапроса использует запросidx_update_timeпоказатель.首先在索引上拿到了聚集索引的主键ID,省去了回表操作，然后第二查询直接根据第一个查询的 ID往后再去查10个就可以了!

Следовательно, это решение возможно ~

INNER JOIN ленивая ассоциация

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

Оптимизированный SQL выглядит следующим образом:

SELECT  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000, 10) AS  acct2 on acct1.id= acct2.id;

Эффект запроса также используется и занимает всего 0,034 секунды.

План выполнения следующий:

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

запись лейбла

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

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

Предполагая, что последняя запись была 100000, SQL можно изменить следующим образом:

select  id,name,balance FROM account where id > 100000 order by id limit 10;

В этом случае, сколько бы страниц потом не было перевернуто, производительность будет хорошей, потому что попаданиеidпоказатель. Но ты, таким образомограниченное: Требуется поле, аналогичное непрерывному автоинкременту.

Используйте между... и...

Нередко можноlimitЗапрос преобразуется в запрос известных местоположений, так что MySQL сканирует диапазонbetween...and, можно получить соответствующие результаты.

Если вы знаете, что граничное значение равно 100000 и 100010, вы можете оптимизировать его следующим образом:

select  id,name,balance FROM account where id between 100000 and 100010 order by id desc;

Практический боевой случай

Давайте рассмотрим практический случай. Предположим, что имеется следующая структура таблицы, и есть2 миллионаданные.

CREATE TABLE account (
 id varchar(32) COLLATE utf8_bin NOT NULL COMMENT '主键',
 account_no varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '账号'
 amount decimal(20,2) DEFAULT NULL COMMENT '金额'
 type varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT '类型A，B'
 create_time datetime DEFAULT NULL COMMENT '创建时间',
 update_time datetime DEFAULT NULL COMMENT '更新时间',
 PRIMARY KEY (id),
 KEY `idx_account_no` (account_no),
 KEY `idx_create_time` (create_time)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='账户表' 

Бизнес-требования заключаются в следующем: получить данные счетов типа А в 2021 году и передать их на платформу больших данных.

Общая идея реализации

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

//查询上报总数量
Integer total = accountDAO.countAccount();

//查询上报总数量对应的SQL
<select id ='countAccount' resultType="java.lang.Integer">
  seelct count(1) 
  from account
  where create_time >='2021-01-01 00:00:00'
  and  type ='A'
</select>

//计算页数
int pageNo = total % pageSize == 0 ? total / pageSize : (total / pageSize + 1);

//分页查询，上报
for(int i = 0; i < pageNo; i++){
 List<AcctountPO> list = accountDAO.listAccountByPage(startRow,pageSize);
 startRow = (pageNo-1)*pageSize;
 //上报大数据
 postBigData(list);
}
 
//分页查询SQL（可能存在limit深分页问题，因为account表数据量几百万）
<select id ='listAccountByPage' >
  seelct * 
  from account
  where create_time >='2021-01-01 00:00:00'
  and  type ='A'
  limit #{startRow},#{pageSize}
</select>

Практический план оптимизации

Приведенная выше схема реализации будет иметь проблему ограничения глубокого пейджинга, т.к. объем данных таблицы account составляет несколько миллионов. Как его оптимизировать?

На самом деле вы можете использоватьЗаписи теговзакона, у некоторых партнеров могут возникнуть сомнения,первичный ключ id не является последовательнымДа, действительно ли можно использовать регистрацию тегов?

Конечно, id не является непрерывным, мы можем пройтиorder byПусть это продолжается. Схема оптимизации следующая:

//查询最小ID
String  lastId = accountDAO.queryMinId();

//查询最小ID对应的SQL
<select id="queryMinId" returnType=“java.lang.String”>
select MIN(id) 
from account
where create_time >='2021-01-01 00:00:00'
and type ='A'
</select>

//一页的条数
Integer pageSize = 100;

List<AcctountPO> list ;
do{
   list = listAccountByPage(lastId,pageSize);
   //标签记录法，记录上次查询过的Id
   lastId = list.get(list,size()-1).getId();
    //上报大数据
    postBigData(list);
}while(CollectionUtils.isNotEmpty(list));

<select id ="listAccountByPage">
  select * 
  from account 
  where create_time >='2021-01-01 00:00:00'
  and id > #{lastId}
  and type ='A'
  order by id asc  
  limit #{pageSize}
</select>
Категории