предисловие
Когда таблица, которую необходимо запросить из базы данных, содержит десятки тысяч записей, запрос всех результатов за один раз будет очень медленным, особенно по мере увеличения объема данных и необходимости запроса на подкачку. Для запросов на подкачку базы данных также существует множество методов и точек оптимизации. Вот несколько известных мне методов.
Готов к работе
Чтобы протестировать некоторые из перечисленных ниже оптимизаций, ниже описана существующая таблица.
- Имя таблицы: order_history
- Описание: Таблица истории заказов для бизнеса
- Основные поля: unsigned int id, тип tinyint(4) int
- Ситуация с полем: в таблице всего 37 полей, за исключением больших данных, таких как текст, максимальное значение — varchar (500), а поле id является индексом и увеличивается.
- Объем данных: 5709294
- Версия MySQL: 5.7.16
Не так просто найти тестовую таблицу на миллион уровней в автономном режиме, если вам нужно протестировать ее самостоятельно, вы можете написать сценарий оболочки или что-то еще, чтобы вставить данные для тестирования.
Среда, в которой выполняются все следующие операторы sql, не изменилась, и ниже приведены основные результаты тестирования:
select count(*) from orders_history;
Результат возврата: 5709294
Три времени запроса:
8903 ms8323 ms8401 ms
Общий пейджинговый запрос
Общие пейджинговые запросы могут быть реализованы с помощью простого предложения limit. Ограничительная оговорка объявляется следующим образом:
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
Предложение LIMIT можно использовать для указания количества записей, возвращаемых оператором SELECT. Следует отметить следующие моменты:
- Первый параметр указывает смещение первой возвращенной строки записи.
- Второй параметр указывает максимальное количество возвращаемых строк.
- Если задан только один параметр: это означает вернуть максимальное количество строк записи
- Второй параметр равен -1, что означает извлечение всех строк записи с определенного смещения до конца набора записей.
- Смещение начальной строки записи равно 0 (вместо 1)
Ниже приведен пример приложения:
select * from orders_history where type=8 limit 1000,10;
Этот оператор запросит 10 данных после 1000-го из таблицы orders_history, то есть с 1001-го по 10010-е данные.
Записи в таблице данных по умолчанию сортируются по первичному ключу (обычно id).Вышеприведенный результат эквивалентен следующему:
select * from orders_history where type=8 order by id limit 10000,10;
Три времени запроса:
3040 ms3063 ms3018 ms
Для этого метода запроса следующие тесты проверяют влияние объема записи запроса на время:
select * from orders_history where type=8 limit 10000,1;
select * from orders_history where type=8 limit 10000,10;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 10000,1000;
select * from orders_history where type=8 limit 10000,10000;
Три времени запроса следующие:
Запрос 1 запись: 3072 мс 3092 мс 3002 мс Запрос 10 записей: 3081 мс 3077 мс 3032 мс Запрос 100 записей: 3118 мс 3200 мс 3128 мс Запрос 1000 записей: 3412 мс 3468 мс 3394 мс Запрос 10000 записей: 3749 мс 3802 мс 3602 мс
Кроме того, я сделал более десяти запросов.С точки зрения времени запроса в основном очевидно, что когда количество записей запроса меньше 100, разницы во времени запроса практически нет.Будет все больше и больше.
Тесты для смещения запроса:
select * from orders_history where type=8 limit 100,100;
select * from orders_history where type=8 limit 1000,100;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 100000,100;
select * from orders_history where type=8 limit 1000000,100;
Три времени запроса следующие:
Смещение запроса 100: 25 мс 24 мс 24 мс Смещение запроса 1000: 78 мс 76 мс 77 мс Смещение запроса 10000: 3092 мс 3212 мс 3128 мс Смещение запроса 100000: 3878 мс 3812 мс 3798 мс Смещение запроса 1000000: 1470 мс 140608 мс 140608 мс
По мере увеличения смещения запроса, особенно когда смещение запроса превышает 100 000, время запроса резко увеличивается.
Этот метод запроса подкачки начнет сканирование с первой записи в базе данных, поэтому чем дальше вы продвигаетесь, тем медленнее скорость запроса, и чем больше данных вы запрашиваете, тем медленнее общая скорость запроса.
Используйте оптимизацию подзапросов
Этот метод сначала находит идентификатор позиции смещения, а затем запрашивает его позже.Этот метод подходит для случая, когда идентификатор увеличивается.
select * from orders_history where type=8 limit 100000,1;
select id from orders_history where type=8 limit 100000,1;
select * from orders_history where type=8 and
id>=(select id from orders_history where type=8 limit 100000,1)
limit 100;
select * from orders_history where type=8 limit 100000,100;
Время запроса для 4 операторов выглядит следующим образом:
1-й оператор: 3674 мс 2-й оператор: 1315 мс 3-й оператор: 1327 мс 4-й оператор: 3710 мс
Примечание к приведенному выше запросу:
- Сравнение 1-го и 2-го оператора: использование select id вместо select * в 3 раза быстрее
- Сравнение 2-го утверждения и 3-го утверждения: скорость отличается на десятки миллисекунд
- Сравнение 3-го и 4-го оператора: в 3 раза выше скорость запроса для 3-го оператора благодаря увеличению скорости выбора идентификатора.
По сравнению с исходным методом общего запроса этот метод будет в несколько раз быстрее.
Квалифицировать оптимизацию с идентификатором
Таким образом, предполагается, что идентификатор таблицы данных постоянно увеличивается, тогда мы можем рассчитать диапазон идентификатора запроса в соответствии с количеством запрошенных страниц и количеством запрошенных записей.Вы можете использовать идентификатор между и запросить:
select * from orders_history where type=2
and id between 1000000 and 1000100 limit 100;
Время запроса: 15 мс 12 мс 9 мс
Этот метод запроса может значительно оптимизировать скорость запроса и в основном может быть выполнен в течение десятков миллисекунд. Ограничение состоит в том, что его можно использовать только тогда, когда идентификатор четко известен, однако при создании таблицы будет добавлено базовое поле идентификатора, что значительно упрощает поисковые запросы.
Есть и другой способ записи:
select * from orders_history where id >= 1000001 limit 100;
Конечно, вы также можете использовать метод in для запроса, этот метод часто используется для запроса, когда несколько таблиц связаны, и использовать коллекцию идентификаторов других запросов к таблице для запроса:
select * from orders_history where id in
(select order_id from trade_2 where goods = 'pen')
limit 100;
Будьте осторожны с этим в запросе: некоторые версии mysql не поддерживают использование limit в предложении in.
Используйте временную оптимизацию таблицы
Этот метод больше не является оптимизацией запросов, и он упоминается здесь случайно.
Для проблемы использования идентификатора для ограничения оптимизации идентификатор должен постоянно увеличиваться, но в некоторых сценариях, например, при использовании таблицы истории или при возникновении проблемы потери данных, вы можете рассмотреть возможность использования временной таблицы хранения для записи. идентификатор страницы. Используйте идентификатор страницы для запросов. Это может значительно повысить скорость традиционных запросов на подкачку, особенно когда объем данных составляет десятки миллионов.
Об описании идентификатора таблицы данных
В обычных условиях при создании таблицы в БД обязательно в каждую таблицу добавляется увеличивающее поле id, что удобно для запроса.
Если объем данных, таких как база данных заказов, очень велик, он обычно делится на подбазы данных и подтаблицы. В настоящее время не рекомендуется использовать в качестве уникального идентификатора идентификатор базы данных, вместо этого для его генерации следует использовать распределенный высокопараллельный генератор уникальных идентификаторов, а для хранения идентификатора использовать другое поле в таблице данных. уникальный идентификатор.
Использование запроса диапазона для поиска идентификатора (или индекса), а затем использование индекса для поиска данных, может повысить скорость запроса в несколько раз. То есть сначала выберите id, затем выберите *;
Наконец
Прошу всех обратить внимание на мой официальный аккаунт [Программист в погоне за ветром], в нем будут обновляться статьи, а также размещаться отсортированная информация.