предисловие
- Интервьюер: Давайте поговорим о 10 миллионах данных, как вы их запрашивали?
- Brother B: Прямой пейджинговый запрос, используйте лимит пейджинга.
- Интервьюер: Вы практиковали это?
- Брат Б: Конечно
В этот момент я представляю песню «Лян Лян».
Возможно, некоторые люди никогда не сталкивались с таблицей с десятками миллионов данных и не знают, что произойдет при запросе десятков миллионов данных.
Сегодня я возьму вас на практику, на этот раз на основеMySQL 5.7.26делать тесты
Подготовить данные
Что делать, если нет 10 миллионов данных?
Создайте
Код для создания 10 миллионов? Это невозможно, это слишком медленно, это может занять целый день. Скрипты базы данных можно использовать для более быстрого выполнения.
создать таблицу
CREATE TABLE `user_operation_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`ip` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`op_data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`attr12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
Создать сценарий данных
Использование пакетных вставок будет намного быстрее, а фиксация выполняется каждые 1000 записей.Объем данных слишком велик, что также приведет к снижению эффективности пакетной вставки.
DELIMITER ;;
CREATE PROCEDURE batch_insert_log()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE userId INT DEFAULT 10000000;
set @execSql = 'INSERT INTO `test`.`user_operation_log`(`user_id`, `ip`, `op_data`, `attr1`, `attr2`, `attr3`, `attr4`, `attr5`, `attr6`, `attr7`, `attr8`, `attr9`, `attr10`, `attr11`, `attr12`) VALUES';
set @execData = '';
WHILE i<=10000000 DO
set @attr = "'测试很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长的属性'";
set @execData = concat(@execData, "(", userId + i, ", '10.0.69.175', '用户登录操作'", ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ")");
if i % 1000 = 0
then
set @stmtSql = concat(@execSql, @execData,";");
prepare stmt from @stmtSql;
execute stmt;
DEALLOCATE prepare stmt;
commit;
set @execData = "";
else
set @execData = concat(@execData, ",");
end if;
SET i=i+1;
END WHILE;
END;;
DELIMITER ;
начать тестирование
Конфигурация компьютера Brother относительно низкая: стандартный шлак win10 шлак i5, чтение и запись около 500 МБ SSD
Из-за низкой конфигурации для этого теста было подготовлено всего 3 148 000 элементов данных, которые заняли 5 ГБ на диске (если не был построен индекс) и выполнялись в течение минут 38. Учащиеся с хорошей конфигурацией компьютера могут вставить тест с многоточечными данными.
SELECT count(1) FROM `user_operation_log`
Результат возврата: 3148000
Три времени запроса:
- 14060 ms
- 13755 ms
- 13447 ms
Обычный пейджинговый запрос
MySQL поддерживает оператор LIMIT для выбора указанного количества данных, Oracle может использовать ROWNUM для выбора.
Синтаксис запроса разбивки на страницы MySQL следующий:
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
- Первый параметр указывает смещение первой возвращенной строки записи.
- Второй параметр указывает максимальное количество возвращаемых строк.
Теперь приступим к тестированию результатов запроса:
SELECT * FROM `user_operation_log` LIMIT 10000, 10
Три времени запроса:
- 59 ms
- 49 ms
- 50 ms
Вроде со скоростью все в порядке, но это локальная база данных, и скорость естественно выше.
Тест под другим углом
То же смещение, разный объем данных
SELECT * FROM `user_operation_log` LIMIT 10000, 10
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 1000
SELECT * FROM `user_operation_log` LIMIT 10000, 10000
SELECT * FROM `user_operation_log` LIMIT 10000, 100000
SELECT * FROM `user_operation_log` LIMIT 10000, 1000000
Время запроса следующее:
| количество | первый раз | второй раз | в третий раз |
|---|---|---|---|
| 10 | 53ms | 52ms | 47ms |
| 100 | 50ms | 60ms | 55ms |
| 1000 записей | 61ms | 74ms | 60ms |
| 10000 штук | 164ms | 180ms | 217ms |
| 100000 статей | 1609ms | 1741ms | 1764ms |
| 1000000 статей | 16219ms | 16889ms | 17081ms |
Из вышеприведенных результатов можно сделать вывод, что:Чем больше объем данных, тем больше времени требуется
Тот же объем данных, разное смещение
SELECT * FROM `user_operation_log` LIMIT 100, 100
SELECT * FROM `user_operation_log` LIMIT 1000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 100000, 100
SELECT * FROM `user_operation_log` LIMIT 1000000, 100
| Компенсировать | первый раз | второй раз | в третий раз |
|---|---|---|---|
| 100 | 36ms | 40ms | 36ms |
| 1000 | 31ms | 38ms | 32ms |
| 10000 | 53ms | 48ms | 51ms |
| 100000 | 622ms | 576ms | 627ms |
| 1000000 | 4891ms | 5076ms | 4856ms |
Из вышеприведенных результатов можно сделать вывод, что:Чем больше смещение, тем больше времени требуется
SELECT * FROM `user_operation_log` LIMIT 100, 100
SELECT id, attr FROM `user_operation_log` LIMIT 100, 100
Как оптимизировать
Так как мы прошли вышеперечисленное метание, то также пришли к выводу, что для двух вышеуказанных проблем: большого смещения и большого объема данных мы начинаем оптимизировать отдельно
Оптимизация проблемы большого смещения
используя подзапрос
Мы можем сначала найти идентификатор позиции смещения, а затем запросить данные.
SELECT * FROM `user_operation_log` LIMIT 1000000, 10
SELECT id FROM `user_operation_log` LIMIT 1000000, 1
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10
Результаты запроса следующие:
| sql | Проводить время |
|---|---|
| Статья 1 | 4818ms |
| Статья 2 (в случае отсутствия индекса) | 4329ms |
| Статья 2 (с индексом) | 199ms |
| Статья 3 (в случае отсутствия индекса) | 4319ms |
| Статья 3 (с индексом) | 201ms |
Из вышеприведенных результатов можно сделать вывод, что:
- Первый занимает больше всего времени, а третий чуть лучше первого
- Подзапросы быстрее с использованием индексов
Недостаток: работает только при увеличении идентификатора
В случае неинкрементного id можно использовать следующий способ записи, но этот недостаток в том, что пейджинговые запросы можно размещать только в подзапросах
Примечание. Некоторые версии mysql не поддерживают использование limit в предложении in, поэтому используются множественные вложенные выборки.
SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t)
ограничено идентификатором
Этот метод более требователен, идентификатор должен непрерывно увеличиваться, и диапазон идентификатора должен быть рассчитан, а затем использован между ними, sql выглядит следующим образом.
SELECT * FROM `user_operation_log` WHERE id between 1000000 AND 1000100 LIMIT 100
SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100
Результаты запроса следующие:
| sql | Проводить время |
|---|---|
| Статья 1 | 22ms |
| Статья 2 | 21ms |
Из результатов видно, что этот метод очень быстрый
Примечание. LIMIT здесь ограничивает количество баров, а смещение не используется.
Оптимизация проблемы большого объема данных
Количество данных, возвращаемых в результате, также напрямую влияет на скорость
SELECT * FROM `user_operation_log` LIMIT 1, 1000000
SELECT id FROM `user_operation_log` LIMIT 1, 1000000
SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM `user_operation_log` LIMIT 1, 1000000
Результаты запроса следующие:
| sql | Проводить время |
|---|---|
| Статья 1 | 15676ms |
| Статья 2 | 7298ms |
| Статья 3 | 15960ms |
Из результатов видно, что количество ненужных столбцов уменьшается, а эффективность запросов также может быть значительно повышена.
Скорость выполнения первого и третьего запросов примерно одинаковая.В этот раз вы точно будете жаловаться.Тогда зачем я пишу столько полей?
Обратите внимание, что мой сервер и клиент MySQL находятся вта же машина, поэтому данные запроса аналогичны, учащиеся, у которых есть условия, могут протестировать клиент и MySQL по отдельности.
ВЫБЕРИТЕ * разве это не ароматно?
Вот, кстати, добавь, почему SELECT * запрещен. Это просто и бездумно, это не ароматно?
Два основных момента:
- Использование базы данных "SELECT *" требует анализа большего количества объектов, полей, разрешений, атрибутов и другого связанного содержимого. Когда оператор SQL сложный и выполняется много сложных синтаксических анализов, это создает большую нагрузку на базу данных.
- Увеличьте нагрузку на сеть, * Иногда бесполезные и большие текстовые поля, такие как журнал и IconMD5, вводятся по ошибке, и размер передаваемых данных увеличивается в геометрической прогрессии. Эти накладные расходы очень заметны, особенно когда MySQL и приложение находятся на разных машинах.
Заканчивать
В конце концов, я надеюсь, что вы будете практиковать это сами, и вы определенно сможете добиться большего Добро пожаловать, чтобы оставить сообщение! !
Создайте сценарий, который я вам даю в самый раз, чего вы ждете! ! !
прежде чем служить мнеКак MySQL оптимизируется