предисловие
При проектировании таблиц в MySQL MySQL официально рекомендует не использовать uuid или непоследовательный и неповторяющийся идентификатор снежинки (длинный и уникальный, инкрементный для одной машины), но рекомендует непрерывный и самоувеличивающийся идентификатор первичного ключа. это авто_инкремент.
Так почему же uuid не рекомендуется и какой вред от использования uuid? В этом вопросе мы анализируем эту проблему со следующих частей и исследуем внутренние причины:
1. Пример программы MySQL
2. Сравнение структуры индекса с использованием uuid и автоинкрементного id
3. Резюме
Экземпляр программы MySQL
Чтобы проиллюстрировать эту проблему, мы сначала создадим три таблицы, а именно: user_auto_key user_uuid user_random_key
Они представляют автоматически увеличивающийся первичный ключ, uuid в качестве первичного ключа, случайный ключ в качестве первичного ключа, а остальные мы оставляем без изменений.
В соответствии с методом управляющей переменной мы используем только разные стратегии для генерации первичного ключа каждой таблицы, а другие поля точно такие же, а затем проверяем скорость вставки и скорость запроса таблицы.
Примечание. Случайный ключ здесь на самом деле относится к непоследовательному, неповторяющемуся и нерегулярному идентификатору, вычисленному алгоритмом снежинки: строка из 18-битных значений.
Таблица идентификаторов создается автоматически:
Таблица UUID пользователей:
Таблица случайных первичных ключей:
Одной теории недостаточно, перейдите непосредственно к программе и используйте Spring jdbcTemplate для реализации дополнительных проверочных тестов.
Техническая структура: Spring Boot+jdbcTemplate+junit+hutool, принцип работы программы заключается в подключении к собственной тестовой базе данных, а затем записи того же объема данных в той же среде для анализа времени вставки для синтеза ее эффективности.
Для достижения наиболее реалистичного эффекта все данные генерируются случайным образом, такие как имена, адреса электронной почты и адреса генерируются случайным образом:
package com.wyq.mysqldemo;
import cn.hutool.core.collection.CollectionUtil;
import com.wyq.mysqldemo.databaseobject.UserKeyAuto;
import com.wyq.mysqldemo.databaseobject.UserKeyRandom;
import com.wyq.mysqldemo.databaseobject.UserKeyUUID;
import com.wyq.mysqldemo.diffkeytest.AutoKeyTableService;
import com.wyq.mysqldemo.diffkeytest.RandomKeyTableService;
import com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService;
import com.wyq.mysqldemo.util.JdbcTemplateService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.util.StopWatch;
import java.util.List;
@SpringBootTest
class MysqlDemoApplicationTests {
@Autowired
private JdbcTemplateService jdbcTemplateService;
@Autowired
private AutoKeyTableService autoKeyTableService;
@Autowired
private UUIDKeyTableService uuidKeyTableService;
@Autowired
private RandomKeyTableService randomKeyTableService;
@Test
void testDBTime() {
StopWatch stopwatch = new StopWatch("执行sql时间消耗");
/**
* auto_increment key任务
*/
final String insertSql = "INSERT INTO user_key_auto(user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?)";
List<UserKeyAuto> insertData = autoKeyTableService.getInsertData();
stopwatch.start("自动生成key表任务开始");
long start1 = System.currentTimeMillis();
if (CollectionUtil.isNotEmpty(insertData)) {
boolean insertResult = jdbcTemplateService.insert(insertSql, insertData, false);
System.out.println(insertResult);
}
long end1 = System.currentTimeMillis();
System.out.println("auto key消耗的时间:" + (end1 - start1));
stopwatch.stop();
/**
* uudID的key
*/
final String insertSql2 = "INSERT INTO user_uuid(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
List<UserKeyUUID> insertData2 = uuidKeyTableService.getInsertData();
stopwatch.start("UUID的key表任务开始");
long begin = System.currentTimeMillis();
if (CollectionUtil.isNotEmpty(insertData)) {
boolean insertResult = jdbcTemplateService.insert(insertSql2, insertData2, true);
System.out.println(insertResult);
}
long over = System.currentTimeMillis();
System.out.println("UUID key消耗的时间:" + (over - begin));
stopwatch.stop();
/**
* 随机的long值key
*/
final String insertSql3 = "INSERT INTO user_random_key(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
List<UserKeyRandom> insertData3 = randomKeyTableService.getInsertData();
stopwatch.start("随机的long值key表任务开始");
Long start = System.currentTimeMillis();
if (CollectionUtil.isNotEmpty(insertData)) {
boolean insertResult = jdbcTemplateService.insert(insertSql3, insertData3, true);
System.out.println(insertResult);
}
Long end = System.currentTimeMillis();
System.out.println("随机key任务消耗时间:" + (end - start));
stopwatch.stop();
String result = stopwatch.prettyPrint();
System.out.println(result);
}
программа записывает результат
user_key_auto записывает результат:
user_random_key результат записи:
Результат записи таблицы user_uuid:
Результаты испытаний эффективности
Когда количество существующих данных составляет 130 Вт: давайте снова протестируем вставку данных 10 Вт, чтобы увидеть, каков будет результат:
Можно видеть, что когда объем данных составляет около 100 Вт, эффективность вставки uuid находится на дне, а в последующей последовательности добавляется 130 Вт данных, а время uuid резко падает.
Общий рейтинг эффективности использования времени: auto_key>random_key>uuid.
Эффективность uuid самая низкая, при большом объеме данных эффективность резко падает. Так почему же это происходит? С сомнением, давайте исследуем этот вопрос:
Сравнение структуры индекса с использованием uuid и автоматически увеличивающегося идентификатора
Внутренняя структура с использованием автоинкрементного идентификатора
Значение автоинкрементного первичного ключа является последовательным, поэтому InnoDB сохраняет каждую запись после записи.
Когда достигнут максимальный коэффициент заполнения страницы (максимальный коэффициент заполнения InnoDB по умолчанию составляет 15/16 от размера страницы, оставляя 1/16 места для будущих изменений).
(1) Следующая запись будет записана на новую страницу. После того, как данные будут загружены в этом порядке, страница первичного ключа будет заполнена почти последовательными записями, что увеличивает максимальную скорость заполнения страницы, и не будет страницы отходы.
(2) Вновь вставленная строка должна быть следующей строкой исходной строки максимальных данных.MySQL находит и адресует очень быстро и не будет производить дополнительное потребление для вычисления позиции новой строки.
(3) Уменьшается количество разбиений страниц и фрагментация.
Внутреннее индексирование с использованием uuid
Поскольку относительный порядок самоувеличивающегося идентификатора uuid нерегулярен, значение новой строки не обязательно больше, чем значение предыдущего первичного ключа, поэтому innodb не всегда может вставить новую строку в конец индекса. Вместо этого вы необходимо найти новое подходящее место для новой строки, чтобы выделить новое пространство.
Этот процесс требует множества дополнительных операций, а случайность данных приведет к разбросанному распределению данных, что приведет к следующим проблемам:
(1) Записанная целевая страница, вероятно, была сброшена на диск и удалена из кеша или не была загружена в кеш, innodb должен найти и прочитать целевую страницу с диска в память перед вставкой. много случайных IO.
(2) Поскольку записи происходят не по порядку, innodb приходится часто выполнять операции разделения страниц, чтобы выделить место для новых строк.Разбиение страниц приводит к перемещению большого объема данных, и для одного необходимо изменить как минимум три страницы. вставка.
(3) Из-за частого разделения страниц страницы становятся разреженными и заполняются неравномерно, что в конечном итоге приводит к фрагментации данных.
После загрузки случайных значений (uuid и идентификатор снежинки) в кластеризованный индекс (тип индекса InnoDB по умолчанию) иногда необходимо выполнить OPTIMEIZE TABLE, чтобы перестроить таблицу и оптимизировать заполнение страницы, что займет определенное время. . . .
Вывод: Используя InnoDB, вы должны вставлять как можно больше в порядке автоинкремента первичного ключа и использовать монотонно возрастающее значение кластерного ключа для вставки как можно большего количества новых строк.
Недостатки использования автоинкрементных идентификаторов
Так нет ли никакого вреда в использовании автоинкрементного идентификатора? Нет, автоинкрементный идентификатор также имеет следующие проблемы:
(1) Как только другие просканируют вашу базу данных, они смогут получить информацию о росте вашего бизнеса на основе самоувеличивающегося идентификатора базы данных, и им будет легко проанализировать вашу бизнес-ситуацию.
(2) При высоких одновременных нагрузках InnoDB вызовет явную конкуренцию за блокировку при вставке по первичному ключу, а верхняя граница первичного ключа станет точкой состязания, поскольку все вставки происходят здесь, а одновременные вставки вызовут конкуренцию за блокировку пробелов.
(3) Механизм блокировки Auto_Increment вызовет рывок блокировки самоинкремента, что приведет к определенной потере производительности.
Вложение: проблема блокировки блокировки Auto_increment, если вы хотите улучшить, вам нужно настроить конфигурацию innodb_autoinc_lock_mode.
Суммировать
Этот блог начинается с вопросов в начале, построения таблиц и использования jdbcTemplate для проверки производительности различных стратегий генерации идентификаторов при вставке данных больших объемов, а затем анализирует различные механизмы идентификаторов в MySQL, структуру индекса, преимущества и недостатки. , и подробные объяснения, почему происходит потеря производительности uuid и случайного уникального идентификатора при вставке данных, подробно объясняет эту проблему.
В реальной разработке лучше использовать идентификатор автоинкремента в соответствии с официальной рекомендацией MySQL, MySQL широк и глубок, и есть еще много моментов, которые стоит оптимизировать внутри, которые нам нужно изучить.
Обратите внимание на официальный аккаунт: программисты следуют за ветром, отвечайте на данные, чтобы получить последние данные Java-интервью в 2020 году.