Блокировка проблем, вызванных многопоточностью, с помощью @Transactional

введение проблемы

@Transactional(value="transactionManager", rollbackFor = Exception.class)
public Result deleteOperation(List<String> strList){
    try{ 
        IntStream.range(0, (strList.size() + BATCH_SIZE - 1)/BATCH_SIZE) 
        .mapToobj(i -> strList.subList(i * BATCH_SIZE, Math.min(strList.size(), (i+1) * BATCH_SIZE))) 
        .forEach(batch -> deleteFunc(batch));
    } catch (Exception e){
        TransactionAspectSupport.currentTransactionStatus().setRollbackOnliy();
    } 
} 

Все данные в операции удаления в этой статье существуют в таблице данных (данные обращений), а условия фильтрации проходят через первичный ключ (индексируемые данные).Ссылка), а взаимоисключающих данных нет, то из рисунка выше видно, что нужно добавить блокировки на уровне строк, но здесь блокировки апгрейдятся, и единственная возможность — не попасть в индекс. Ключом к решению проблемы является поиск причины промаха индекса. Здесь я сначала выдвину три наиболее важных вывода этой статьи и дам теоретическую поддержку. Студенты, у которых есть сомнения, могут продолжить проводить углубленное тестирование и Так называемая практика приносит истинные знания (тестовая среда: Mysql5.6.36, InnoDB):

      Вывод 1. Составные поля не могут обращаться к индексам в подзапросах IN, независимо от того, являются ли col_1 и col_2 уникальными индексами или совместными уникальными индексами (исключая покрывающие индексы);

SELECT col_1,col_2 FROM table WHERE (col_1,col_2) IN (('a','b'),('c','d')......)

    Вывод 2: Некоторые поля в составном первичном ключе могут попасть в индекс в подзапросе IN, но должны быть соблюдены два условия: крайний левый префикс и значение в IN меньше определенного процента**(без индекса покрытия)****;**

SELECT col_1,col_2 FROM table WHERE col_1 IN ('a','b','c'......)

** Вывод 3: Одно поле может попасть в индекс в подзапросе IN, но должны быть выполнены два условия: крайний левый префикс, значение в IN меньше определенного процента**** (исключая покрывающие индексы)*** *;* *

По выводу 1 думаю может быть проблема оптимизации версии 5.6.В тесте версии 5.7 и выше данная проблема не обнаружена.По сравнению с документами в Mysql5.7 добавлена ​​оптимизация многоколоночных IN подзапросов в _Range Optimization_:

Выводы 2 и 3 связаны с оценкой стоимости оптимизатора InnoDB.Стоимость запроса происходит из четырех уровней: стоимость ввода-вывода, стоимость ЦП, стоимость операции с памятью и стоимость удаленной операции после версии 5.6; наибольшая часть - это стоимость ввода-вывода. , стоимость ЦП и стоимость работы с памятью. /O накладные расходы, InnoDB записывает стоимость доступа к странице как 1 (ввод-вывод), а стоимость чтения записи как 0,2 (ЦП).Конечно, оптимизатор здесь не может иметь возможность точно измерить объем данных, к которым необходимо получить доступ для каждой операции. , при использовании _INDEX DIVE_ точное значение может быть рассчитано по индексу. Если он не используется, будет использоваться нечеткое значение статистики , Подробную информацию см. на официальном веб-сайте в главе _Range Optimization_. _В соответствии с этим методом расчета стоимости мы можем оценить стоимость различных методов запроса:

假设表数据量为Y，IN条件中有X个字段：
··使用索引时：
  数据库会遍历IN中的值，通过索引去找到对应的记录(假设每次查询需要3次I/O，其实大部分表三次足以)，I/O
开销为3X，CPU开销为0.2X，如果出现回表I/O开销可能会翻倍；
··全表扫描时：
  遍历主键索引，并判断是否在IN条件中，I/O开销为Y，CPU开销为0.2Y；

Согласно расчету стоимости, когда количество условий в подзапросе IN меньше определенной пропорции (около 20% к 30%), стоимость использования индекса меньше, но когда она превышает определенную пропорцию, I/ O Накладные расходы занимают основную позицию, а стоимость полного сканирования таблицы в это время ниже, чем и объясняются выводы 2 и 3. Конечно, в книге по анализу движка InnoDB есть и другое объяснение, то есть индекс попадания — это дискретный ввод-вывод, что хуже, чем производительность последовательного ввода-вывода первичного ключа, но я думаю, что есть две проблемы с Логика не может быть определена оптимизатором количественно, второй индекс первичного ключа только логически хранится последовательно, а страницы памяти также физически рассредоточены.Версии Mysql выше Mysql 5.7 имеют два решения этой проблемы:

1. Используйте _FORCE INDEX_, это ключевое слово заставит использовать определенный индекс, потому что нет необходимости оценивать стоимость индекса, этоперепрыгниИНДЕКС ПОГРУЖЕНИЕ,Является ли ключевое слово выгодным для одного оператора, зависит от конкретной ситуации.В это время транзакция получает блокировку строки, и не будет конфликта при одновременном удалении;

2. Сократить количество условий в ИН, то есть уменьшить количество данных в каждом пакете бизнес-операций.В это время оптимизатор также будет использовать индекс, но есть две скрытые опасности.Первая - попадание индекс неуправляемый, если нет хита, то все равно будут конфликты, второе сокращение количества сервисов может снизить эффективность всего бизнеса, а увеличение количества потоков также приносит больше потребления;

Приведенные выше два решения, второе решение не является предпочтительным. Если ваша версия базы данных не поддерживает это ключевое слово или ключевое слово оказывает большое влияние на вашу производительность, первое решение также будет недействительным. Есть ли какое-либо подходящее решение? текущий бизнес-план? В этой статье дается небольшой совет:Преобразует совпадение равенства в совпадение диапазона и обновляет блокировку строки до блокировки _Next-Key_.

SELECT * FROM table WHERE col IN (a,b,c......)
->
SELECT * FROM table WHERE col > minCol AND col < maxCol

Каковы преимущества вышеуказанного преобразования?

1. Сопоставление диапазона этого уникального индекса требует только двух запросов уровня _CONST_, чтобы оптимизатор заблокировал диапазон данных, что ниже с точки зрения стоимости запроса;

2. Поскольку по индексу можно попасть, здесь получается блокировка _NEXT-KEY_ вместо блокировки таблицы, если блокировки разных транзакцийЕсли области не перекрываются, может быть обеспечен одновременный доступ;

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

Сравнение эффектов

Экспериментальные данные: 40 000 фрагментов данных в одной таблице, 8 000 фрагментов данных запроса.

Суммировать

1. Для базы данных практика выявляет истинные знания, а у оптимизатора много неожиданных мест;

2. Обратите внимание на различия между версиями;