1. Оптимизация вставки данных

1.1 Подготовка данных

Сначала создайте таблицу с оператором построения тестовой таблицы индекса FULLTEXT:

Индекс FULLTEXT добавляется в поле vivo_tags.Чтобы ознакомиться с индексом FULLTEXT, обратитесь к P229~P248 документа «Инсайдер технологии MySQL — InnoDB Storage Engine» или https://www.cnblogs.com/tommy-huang/ стр/4483684.html

1.2 Оптимизация важных параметров

1.2.1 Буферный пул

Прежде чем вставлять данные, давайте сначала посмотрим на среду, в которой находится текущая база данных:

показать глобальные переменные, такие как 'innodb_buffer_pool_size'; #Общий размер буферного пула

показать глобальные переменные, такие как 'innodb_buffer_pool_instances'; #Количество пулов буферов

Видно, что буферный пул музыкальной библиотеки в тестовой среде составляет 128 МБ, а всего имеется 8 буферных пулов.

Буферный пул используется для преодоления разрыва между ЦП и дисковым вводом-выводом.При записи данных сначала измените страницы в буферном пуле (страницы являются наименьшей единицей управления механизма хранения InnoDB), а затем сбросьте их на диск по определенной частоты.Множественные пулы буферов могут уменьшить конкуренцию за ресурсы в базе данных и увеличить возможности одновременной обработки базы данных.Поэтому общий размер пула буферов и количество пулов буферов являются важными показателями, которые определяют производительность обработки базы данных. Здесь размер буферного пула может быть увеличен для повышения скорости вставки.Поскольку это общедоступная тестовая среда, здесь не вносятся никакие изменения.

1.2.2 innodb_flush_log_at_trx_commit и sync_binlog

innodb_flush_log_at_trx_commit : стратегия сброса журналов повторов на диск, по умолчанию 1, то есть каждый раз, когда транзакция фиксируется, она сбрасывается на диск. Это стратегия синхронизации с самым высоким коэффициентом безопасности, но когда вставляется большой объем данных, скорость вставки сильно снижается из-за частых операций дискового ввода-вывода.

sync_binlog: стратегия сброса двоичных журналов на диск, значение по умолчанию равно 0, то есть двоичные журналы не включены, но во многих архитектурах баз данных master-slave этот параметр больше 0. Включение этого параметра также сильно повлияет на скорость вставки.

1.3 Вставка данных

Напишите хранимую процедуру пакетной вставки:

Здесь транзакция запускается вручную, и каждые 10 000 вставленных данных контролируются вручную для однократной отправки. Это связано с тем, что в условиях по умолчанию каждый раз, когда выполняется операция вставки, она автоматически отправляется один раз, а если вставлено 10 миллионов записей, то отправляется 10 миллионов раз, что, несомненно, приводит к сбою. Однако эта операция вставки 10 000 отправляется один раз и строго не проверяется. Является ли это лучшим выбором, поскольку это связано с размером пула буферов. Если пул буферов заполнен при вставке 10 000 вставок, также будет принудительно сброшен на диск. , и если пул буферов слишком велик, 10 000 раз будет слишком мало. На самом деле, сюда можно добавить таблицу записей для записи успеха или неудачи каждой отправки, что может помочь нам отслеживать операцию вставки в реальном времени, ведь 10 миллионов вставок — это все-таки довольно долго.

Затем вызовите хранимую процедуру:

Фактический тест занял около 28 минут.

2. Полнотекстовый индекс

При запросе информации, содержащей поле 100008, используйте like для запроса:

Запрос с полнотекстовым поиском:

Странно, а почему полнотекстовый поиск тормозит?

Причина в том, что когда like запрашивает первые 10 элементов, он использует полное сканирование таблицы, и при поиске всех 10 элементов данные будут возвращены. Оглядываясь назад на хранимую процедуру в версии 1.3, мы видим, что частота попаданий в поле 100008 составляет около 33%, что эквивалентно возврату достаточного количества данных, когда полное сканирование таблицы достигает 30 записей.

Но при запросе элементов от 1 000 000 до 1 000 010 это происходит очень медленно из-за сканирования около 3 миллионов данных для получения набора результатов:

С другой стороны, метод запроса полнотекстового поиска, благодаря реализации инвертированного индекса, сначала перечисляет все инвертированные индексы из всех 100008, а затем выбирает в этой коллекции первые 10. Здесь число документов 100008 превышает 3 млн. Так что скорость медленнее.

Чтобы проверить эту точку зрения, я вставил в таблицу t_song_info_test_test два фрагмента данных.

В это время 200008 появилось только дважды в данных 10 миллионов + 2. На этом этапе выполните оператор запроса:

Это заняло всего 0,047 секунды.

3. Обратите внимание

3.1 Минимальная длина по умолчанию для полнотекстового поиска — 4

Поля длиной менее 4 не будут записаны в полнотекстовый поиск, и результаты не будут найдены.

Измените значение ft_min_word_len.

3.2 Сегментация слов не является интеллектуальной

Полнотекстовый поиск основан на символе пробела или "," и других очевидных полях-разделителях для сегментации слов. Он не поддерживает сегментацию китайских слов, как elasticsearch, но, начиная с MySQL 5.7.6, встроенная InnoDB поддерживает пользовательскую длину сегментации слов для сегментации китайских слов, вы можете обратиться к [woohoo.brief.com/afraid/from48106149…] (руководство по полнотекстовому поиску на китайском языке MySQL 5.7).