«Эта статья участвовала в мероприятии Haowen Convocation Order, щелкните, чтобы просмотреть:Двойные заявки на внутреннюю и внешнюю стороны, призовой фонд в 20 000 юаней ждет вас, чтобы бросить вызов!"

предисловие

Пейджинг — очень распространенная функция, раз уж это back-end разработка, то надо писать пейджинг, так зачем вам пейджинг?

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

Синтаксис разбивки на страницы MySQL

select * from table limit 0, 20

Мысль: можно ли реально сократить эти затраты, используя разбиение на страницы, пункт 2 выше?

создать таблицу, создать данные

CREATE TABLE t1 (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键', 
    m_id BIGINT NOT NULL COMMENT '其他id',
    `name` VARCHAR ( 255 ) COMMENT '用户名称', 
    identity_no VARCHAR ( 30 ) COMMENT '身份证号', 
    address VARCHAR ( 255 ) COMMENT '地址', 
    create_time TIMESTAMP NOT NULL COMMENT '添加时间',
    modify_time TIMESTAMP NOT NULL COMMENT '修改时间', 
    PRIMARY KEY `id` ( `id` ) 
) ENGINE INNODB DEFAULT CHARSET = 'utf8' COMMENT '深分页测试表';

-- 先初始化一条数据
INSERT INTO t1
VALUES
    ( 1, 1, '这里是随机中英文的名字—1', '100000000000000000', '这里是随机中英文的地址—1', '2010-01-01 00:00:00', '2010-01-01 00:00:00' );

-- 执行个十几次
set @i=1;
insert into t1(m_id, name, identity_no, address, create_time, modify_time) select @i:=@i+1 as m_id,   #随机生成1~10的整数        
            concat('这里是随机中英文的名字—',@i),   #按序列生成不同的name       
            100000000000000000+@i,        
            concat('这里是随机中英文的地址—',@i),        
            date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND),         
            date_add(date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND) from t1;
#注: 该方法来自网络, 方法来源: https://blog.csdn.net/mysqltop/article/details/105230327select count(1) from t1;

Суммарные данные 400 Вт+:

1: Без условий запроса, без сортировки

select id,m_id, name, identity_no, address, create_time, modify_time 
from t1 limit 1000000, 20;

Данные после 100 Вт, отнимающие много времени:

0.613s elapsed

Сортировать по первичному ключу

select id,m_id, name, identity_no, address, create_time, modify_time 
from t1 order by id limit 1000000, 20;

Затраты времени: несколько уменьшено

0.417s elapsed

Сравнение плана выполнения:

1: 2:

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

Поэтому вывод 1: Даже если кажется, что в деле нет условия и его не нужно сортировать, добавляйте порядок по первичному ключу.

На самом деле здесь есть еще один вопрос: если нет условия сортировки, какой сортировка MySQL по умолчанию?

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

2: С сортировкой - поле сортировки не имеет индекса

select id,m_id, name, identity_no, address, create_time, modify_time 
from t1 
order by create_time desc 
limit 10000, 20;

время исполнения:

2.015s elapsed

Поле сортировки имеет индекс: (Примечание: данные таблицы t2 копируются напрямую из t1, а затем добавляется индекс create_time) Тот же sql-запрос t2.

select id,m_id, name, identity_no, address, create_time, modify_time
from t2
order by create_time desc
limit 10000, 20;

время исполнения:

0.937s elapsed

Сравнение плана выполнения:

1: 2:

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

Вывод 2: Проиндексируйте общие поля, включая поля сортировки.

Новый вопрос:

Вышеуказанные два сценария, по-видимому, решили большинство проблем с пейджингом, но:

1. Нужно ли иметь индекс в поле сортировки, чтобы быть быстрее? 1w быстрее, как насчет запроса данных после 100w?
2. Что делать, если текущая таблица уже имеет несколько индексов и не подходит для добавления индекса?

3: Поле сортировки имеет индекс, но разбивка на страницы идет немного глубже: Возьмите 20 полосок от 100 Вт.

select id,m_id, name, identity_no, address, create_time, modify_time 
from t2 
order by create_time desc 
limit 1000000, 20;

Занимает много времени: очень медленно

18.350s elapsed

План реализации:

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

Поскольку оптимизатор mysql находит, что количество строк в этом sql-запросе превышает определенный процент (говорят, что 30%, но после тестирования это не совсем так), он автоматически преобразуется в полное сканирование таблицы. форсировать индекс?

Да, добавьте индекс силы (idx).

4: принудительная индексация

select id,m_id, name, identity_no, address, create_time, modify_time 
from t2 
force index(idx_create_time) 
order by create_time desc 
limit 1000000, 20;

План выполнения после принудительной индексации:

Взгляните на время выполнения:

15.197s elapsed

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

Вывод 3. Даже при наличии индекса более глубокое разбиение по страницам будет проблематичным, и его следует избегать.

Результаты вышеупомянутых попыток не очень хорошо решили проблему производительности глубокого пейджинга.Есть ли лучшее решение?

имеют!

5: с последним_Условный запрос

select id,m_id, name, identity_no, address, create_time, modify_time
from t2
where id > #{last_id},create_time > #{last_create_time}
order by create_time desc
limit 0, 20;

Производительность ничем не отличается от обычной неглубокой подкачки, но только если индексировано поле last_*.

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

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

6: Присоединить подзапрос к таблице

Измените sql принудительного поиска в сценарии 4 на подзапрос и сначала проверьте таблицу t2 с индексом в поле сортировки.

select id,m_id, name, identity_no, address, create_time, modify_time
from t2
force index(idx_create_time)
order by create_time desc
limit 1000000, 20;

-- 改为：
SELECT   
    id, m_id, NAME, identity_no, address, create_time, modify_time 
FROM t2
JOIN ( SELECT id FROM t2 ORDER BY create_time desc LIMIT 1000000, 20 ) x USING ( id );

время исполнения:0.742 s elapsed

Эффект очевиден. (Исходное время выполнения sql: 15 с+)

Затем протестируйте t1 без индекса в create_time, чтобы увидеть, как это работает?

-- 在t1执行：
SELECT   
    id, m_id, NAME, identity_no, address, create_time, modify_time 
FROM t1
JOIN ( SELECT id FROM t1 ORDER BY create_time desc LIMIT 1000000, 20 ) x USING ( id );

время исполнения:2.866s elapsed

Эффект очевиден. (Исходное время выполнения sql: 18 с+)

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

План реализации:

Разница между планами выполнения двух связанных запросов заключается в том, использует ли подзапрос сортировку по индексу, а 1 использует индекс, поэтому он выполняется быстрее.

Сравните планы выполнения подзапросов и планов выполнения без подзапросов:

разница:

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

Кажется, что есть еще один индекс использования, так что же такое индекс использования?

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

t1 без индекса:

разница:

На первый взгляд я обнаружил, что между этими двумя запросами нет никакой разницы, мало того, что разницы нет, так еще и подзапрос сложнее прямого запроса, но он быстрее, почему?

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

При использовании файловой сортировки у mysql есть две стратегии сортировки.

Односторонняя сортировка

1. Все данные полей запроса выносятся в буфер сортировки в соответствии с условиями.
2. Когда буфер заполнен, выполните сортировку (быструю сортировку) в соответствии с полем сортировки, а затем запишите отсортированные данные во временный файл.
3. После извлечения и сортировки всех данных все временные файлы объединяются по порядку (сортировка слиянием), а затем записываются обратно в файл до тех пор, пока все файлы не будут объединены в конце.
4. Прочитайте данные, необходимые для выполнения условий разбиения по страницам, из временного файла и верните их.Если данные разбиения на страницы могут быть получены для первого слияния, они будут возвращены напрямую (поверхностное разбиение по страницам).

двусторонняя сортировка

1. По условиям запроса row_id и поле сортировки вынимаются и помещаются в буфер сортировки (разность 1).
2. Когда буфер заполнен, выполните сортировку (быструю сортировку) в соответствии с полем сортировки, а затем запишите отсортированные данные во временный файл.
3. После извлечения и сортировки всех данных все временные файлы объединяются по порядку (сортировка слиянием), а затем записываются обратно в файл до тех пор, пока все файлы не будут объединены в конце.
4. Прочитайте row_id, который удовлетворяет условию разбиения по страницам, из временного файла, а затем прочитайте соответствующие данные строки через row_id и возвратите (разница 2).

MySQL использовал двустороннюю сортировку до версии 4.1. После этого он был оптимизирован для выполнения условий и по умолчанию используется односторонняя сортировка.Условие: размер данных поля запроса меньше значения max_length_for_sort_data, но изменений не видно в тесте, когда он изменен на минимальное значение.

Таким образом, можно определить причину, по которой метод подзапроса является быстрым.Подзапрос принимает только create_time+id в буфер сортировки (эквивалентно двусторонней сортировке).По сравнению с прямым запросом, большинство полей опущены и большой количество временных файлов уменьшено.Операции ввода-вывода, что повышает эффективность запросов.

Другой метод корректирует размер sort_buffer_size и сравнивает его вверх и вниз.

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

Суммировать

В сравнении

это не сыграно

• Вы можете обратиться к пагинации поиска Google/Baidu.Вы можете перейти только на 10 страниц до и после текущей страницы за раз, то есть вы можете перейти максимум на 10 страниц.Для достижения глубокого пейджинга требуется терпение.
• Если внешний интерфейс не имеет номера страницы и не поддерживает переход между страницами, используйте метод last_*.

Техническое направление

• Добавить сортировку по первичному ключу в запросы на разбиение на страницы без условий сортировки.
• Попробуйте проиндексировать поле сортировки
• Независимо от того, есть индекс или нет, когда количество страниц подкачки достигает определенного порога, принудительно используется метод двусторонней сортировки (инициируются два запроса через подзапросы или код)
• Соответствующим образом увеличьте размер sort_buffer_size
• Ситуация с совместным индексом, избегайте использования нескольких столбцов

Опубликовано Джейси

Обратите внимание на технологию Dewu и объединитесь, чтобы двигаться к облаку технологий.