предисловие
При проектировании таблиц в mysql, mysql официально рекомендует не использовать uuid или прерывистый и неповторяющийся идентификатор снежинки (длинный и уникальный, инкрементный для одной машины), но рекомендует непрерывный и самоувеличивающийся идентификатор первичного ключа.Официальная рекомендация - auto_increment, поэтому почему?Не рекомендуется использовать uuid.Каковы недостатки использования uuid?
В этом блоге мы разберем эту проблему и изучим внутренние причины.
Содержание этого блога
пример программы mysql
Сравнение структуры индекса с использованием uuid и автоинкрементного идентификатора
Суммировать
Во-первых, mysql и примеры программ
1.1 Чтобы проиллюстрировать эту проблему, мы сначала создадим три таблицы
Они пользователя_auto_key, user_uuuid, user_random_key, автоматическое увеличение, соответственно, первичный ключ, UUID в качестве первичного ключа,
Случайный ключ используется в качестве первичного ключа, и мы держим все остальное полностью без изменений.
В соответствии с методом управляющей переменной мы используем только разные стратегии для генерации первичного ключа каждой таблицы, а остальные поля точно такие же, а затем проверяем скорость вставки и скорость запроса таблицы:
Примечание. Случайный ключ здесь на самом деле относится к непоследовательному, неповторяющемуся и нерегулярному идентификатору, вычисленному алгоритмом снежинки: строка 18-битных значений.
1.2. Одной теории недостаточно, перейдите непосредственно к программе и используйте jdbcTemplate Spring для реализации дополнительного контрольного теста:
Техническая структура: springboot+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);
}
1.3 Результат записи программы
Видно, что когда объем данных составляет около 100 Вт, эффективность вставки uuid находится на дне, а в последующей последовательности добавляется 130 Вт данных, а время uudi резко падает.
Общий рейтинг эффективности занятости времени: auto_key>random_key>uuid, у uuid самая низкая эффективность, а в случае большого объема данных эффективность резко падает. Так почему же это происходит? С сомнением, давайте исследуем этот вопрос:
2. Сравнение структуры индекса с использованием uuid и автоинкрементного id
2.1. Внутренняя структура с использованием автоинкрементного идентификатора
Значение автоинкрементного первичного ключа является последовательным, поэтому Innodb сохраняет каждую запись после записи. Когда достигнут максимальный коэффициент заполнения страницы (максимальный коэффициент заполнения innodb по умолчанию составляет 15/16 размера страницы, а 1/16 пространства будет зарезервировано для будущих изменений):
①Следующая запись будет записана на новую страницу.После того, как данные будут загружены в этом порядке, страница первичного ключа будет заполнена почти последовательными записями, что увеличивает максимальную скорость заполнения страницы, и не будет траты страниц .
②Вновь вставленная строка будет следующей строкой исходной строки максимальных данных.MySQL находит и адресует очень быстро и не будет производить дополнительное потребление для вычисления позиции новой строки.
③Уменьшить количество разбиений страниц и фрагментацию
2.2. Используйте внутреннюю структуру индекса uuid
Поскольку относительный порядок самоувеличивающегося идентификатора uuid нерегулярен, значение новой строки не обязательно больше, чем значение предыдущего первичного ключа, поэтому innodb не всегда может вставлять новые строки в конец индекса. Вместо этого вам нужно найти новое подходящее место для новой строки, чтобы выделить новое пространство.
Этот процесс требует множества дополнительных операций, а случайность данных приведет к разбросанному распределению данных, что приведет к следующим проблемам:
① Записанная целевая страница, скорее всего, была сброшена на диск и удалена из кеша или не была загружена в кеш, innodb должен найти и прочитать целевую страницу с диска в память перед вставкой, что вызовет множество случайных ошибок. ИО
②Поскольку записи происходят не по порядку, innodb приходится часто выполнять операции разделения страниц, чтобы выделить место для новых строк.Разбиение страниц приводит к перемещению большого объема данных, и для одной вставки необходимо изменить как минимум три страницы.
③ Из-за частых разбиений страницы будут разрежены и заполнены неравномерно, что в конечном итоге приведет к фрагментации данных.
После загрузки случайных значений (uuid и id снежинки) в кластеризованный индекс (тип индекса innodb по умолчанию) иногда необходимо сделать OPTIMEIZE TABLE для перестроения таблицы и оптимизации заполнения страницы, на что уйдет определенное количество времени. .
Вывод: используйте innodb, чтобы вставить как можно больше в порядке автоинкремента первичного ключа, и используйте монотонно возрастающее значение ключа кластера, чтобы вставить как можно больше новых строк.
2.3. Недостатки использования автоинкрементного идентификатора
Итак, нет ли абсолютно никакого вреда в использовании автоматически увеличивающихся идентификаторов? Нет, автоинкрементный идентификатор также имеет следующие проблемы:
①После того как кто-то просканирует вашу базу данных, он сможет получить информацию о росте вашего бизнеса на основе самоувеличивающегося идентификатора базы данных, что позволит легко проанализировать вашу бизнес-ситуацию.
②При высоких одновременных нагрузках innodb вызовет очевидную конкуренцию за блокировку при вставке по первичному ключу, а верхняя граница первичного ключа станет точкой состязания, поскольку все вставки происходят здесь, а одновременные вставки приведут к конкуренции за блокировку за промежутки.
③Механизм блокировки Auto_Increment вызовет рывок блокировки самоинкремента, что приведет к определенной потере производительности.
Вложение: проблема блокировки блокировки Auto_increment, если вы хотите улучшить, вам нужно настроить конфигурацию innodb_autoinc_lock_mode
3. Резюме
Этот блог начинается с вопросов в начале, построения таблиц и использования jdbcTemplate для проверки производительности различных стратегий генерации идентификаторов при вставке данных больших объемов данных, а затем анализирует различные механизмы идентификаторов в структуре индекса MySQL, преимущества и недостатки, и Подробные объяснения, почему происходит потеря производительности uuid и случайного уникального идентификатора при вставке данных, подробно объясняет эту проблему.
В реальной разработке лучше использовать автоинкрементный идентификатор в соответствии с официальной рекомендацией mysql, MySQL широк и глубок, и есть еще много моментов, достойных оптимизации, которые нам нужно изучить.