В процессе разработки часто встречаются некоторые бизнес-сцены, необходимо найти данные в полностью размытом совпадении, вы подумаете об использовании LIKE% XXX% или LIKE% XXX для достижения, и даже если в столбце есть высокая скорость выбора Индекс не используется. В MySQL вы можете использовать функции ICP, полнотекстовый индекс, основанный на создании индекса столбца, решать такие проблемы, ниже ICP, полнотекстовый индекс, основанный на создании индексации столбца, и как их использовать для решения проблем производительности нечеткого сопоставления SQL. .
Состояние индекса Push Down ICP
Введение в ПМС
MySQL 5.6 начинает поддержкуICP (проталкивание состояния индекса), Прежде чем ICP не поддерживается, при выполнении индексного запроса данные сначала ищутся в соответствии с индексом, а затем фильтруются в соответствии с условием где, сканируя большой объем ненужных данных и увеличивая количество операций ввода-вывода в базе данных. После поддержки ICP MySQL определяет, можно ли выполнять фильтрацию по условию при извлечении данных индекса.Часть операции фильтрации по месту помещается на уровень механизма хранения, чтобы заранее отфильтровать ненужные данные, уменьшая ненужные сканируемые данные.Накладные расходы ввода-вывода. При определенных запросах уровень сервера может уменьшить количество считываемых данных уровня механизма хранения, тем самым повышая общую производительность базы данных.
ICP имеет следующие характеристики
Параметры управления, связанные с ICP
index_condition_pushdown: Проталкивание условия индекса включено по умолчанию и отключено, чтобы отключить функцию ICP.
mysql>show variables like 'optimizer_switch';
| optimizer_switch | index_condition_pushdown=on
# 开启或者关闭ICP特性
mysql>set optimizer_switch = 'index_condition_pushdown=on | off';
Процесс обработки ICP
Предположим, есть пользовательская таблица users01 (id, имя, никнейм, телефон, create_time), а данных в таблице 11W. Поскольку ICP можно использовать только для вторичных индексов, создайте составной индекс idx_name_nickname(name,nickname) для столбцов имени и псевдонима и проанализируйте оператор SQL **select * from users01, где name = 'Lyn' и псевдоним, например '%SK %' **Производительность с выключенным и включенным ICP.
Анализ производительности SQL с отключенной функцией ICP
Включите профилирование, чтобы отслеживать использование ресурсов на каждом этапе выполнения SQL.
mysql>set profiling = 1;
Тщательный анализ характеристик производительности SQL ICP
mysql>set optimizer_switch = 'index_condition_pushdown=off';
mysql>explain select * from users01 where name = 'Lyn' and nickname like '%SK%';
| 1 | SIMPLE | users01 | NULL | ref | idx_name_nickname | idx_name_nickname | 82 | const | 29016 | 100.00 | Using where |
#查看SQL执行期间各阶段的资源使用
mysql>show profile cpu,block io for query 2;
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000065 | 0.000057 | 0.000009 | 0 | 0 |
..................
| executing | 0.035773 | 0.034644 | 0.000942 | 0 | 0 |#执行阶段耗时0.035773秒。
| end | 0.000015 | 0.000006 | 0.000009 | 0 | 0 |
#status状态变量分析
| Handler_read_next | 16384 | ##请求读的行数
| Innodb_data_reads | 2989 | #数据物理读的总数
| Innodb_pages_read | 2836 | #逻辑读的总数
| Last_query_cost | 8580.324460 | #SQL语句的成本COST，主要包括IO_COST和CPU_COST。
Через Объяснение анализа оператор SQL отключается в случае отключения характеристики CP, а составной индекс idx_name_nickname, extra = using where, сначала считывайте Name = 'Lyn' все записи из механизма хранения по составному индексу префикс ID_name_nickname, затем используйте фильтр WHERE для псевдонима, например «% SK%» в конце сервера.
Handler_read_next=16384 указывает, что сканируется 16384 строки данных, а SQL фактически возвращает только 12 строк, что занимает 50 мс. Для такого типа SQL, который сканирует большое количество строк данных и возвращает лишь небольшое количество данных, его можно анализировать с двух сторон.
-
** Низкая частота выбора индекса: ** Для индекса (имя, псевдоним), где имя отображается в качестве начального столбца, CBO выберет использование индекса, поскольку индекс сканирования меньше, чем СТОИМОСТЬ полного сканирования таблицы, но из-за кардинальности столбца имени Невысокий, что приводит к сканированию большого объема данных в индексе, что приводит к низкой производительности SQL.
Column_name: name Cardinality: 6Видно, что в таблице users01 всего 6 разных значений имени, а процент выбора 6/114688 очень низкий.
-
** Неравномерное распределение данных: ** Для того, где имя = ?, когда данные имени распределены неравномерно, значение, возвращаемое SQL в первый раз, возвращает небольшой набор результатов, CBO выберет пройтись по индексу, и в то же время время выполнения SQL После кэширования плана, независимо от того, какое значение передано в имени, в будущем будет выполняться сканирование индекса.Это на самом деле неправильно.Если передано значение имени, это Fly100, которое возвращает 80% данных в таблицы, быстрее выполнить полное сканирование таблицы.
| name | count(*) |
+---------------+----------+
| Grubby | 12 |
| Lyn | 1000 |
| Fly100 | 98100 |
В MySQL 8.0 была введена функция статистики столбца гистограммы, которая в основном оптимизирована для неравномерного распределения данных столбца индекса.
ICP-анализ эксплуатационных характеристик открытых
Включить функцию ICP для анализа выполнения SQL
mysql>set optimizer_switch = 'index_condition_pushdown=on';
#执行计划
| 1 | SIMPLE | users01 | NULL | ref | idx_name_nickname | idx_name_nickname | 82 | const | 29016 | 11.11 | Using index condition |
#status状态变量分析
| Handler_read_next | 12 |
| Innodb_data_reads | 2989 |
| Innodb_pages_read | 2836 |
| Last_query_cost | 8580.324460 |
Из плана выполнения видно, что используется составной индекс idx_name_nickname, Extra=Using index condition, и сканируются только 12 строк данных, что указывает на то, что используется ICP-функция проталкивания условия индекса, и SQL выполняет всего 10 мс, что по сравнению с отключением функции ICP. , производительность SQL улучшается в 5 раз.
| Характеристики/элементы ICP | метод сканирования | Строки сканирования | вернуть количество строк | время исполнения |
|---|---|---|---|---|
| закрыть ICP | Using where | 16384 | 12 | 50ms |
| Включить ПМС | Using index condition | 12 | 12 | 10ms |
После включения функции ICP, поскольку подобное условие псевдонима может быть отфильтровано по индексу, уровень механизма хранения удаляет записи, которые не соответствуют условиям, сравнивая индекс с условием where.Этот процесс не требует чтения записей. и возвращает на уровень сервера только отфильтрованные записи.
Метод индексного сканирования, отображаемый Extra
- **используя где:** Когда в запросе используется индекс, вам нужно вернуться к таблице, чтобы запросить необходимые данные.
- **используя условие индекса: **Запрос использует индекс, но данные должны быть возвращены в таблицу.
- **using index :** Появляется, когда запрос использует покрывающий индекс.
- ** использование индекса и использование где: ** Запрос использует индекс, но необходимые данные можно найти в столбце индекса, и нет необходимости возвращаться к данным запроса.
Оптимизация перезаписи с нечетким соответствием
После включения функции ICP для условий **где имя = 'Lyn' и псевдоним типа '%SK%'** можно использовать составной индекс (имя, псевдоним), чтобы сократить ненужное сканирование данных и повысить производительность SQL. Но если такой псевдоним, как «%SK%», завершает запрос на нечеткое соответствие, можно ли использовать функцию ICP для повышения производительности? Сначала создайте одноколоночный индекс idx_nickname для псевдонима.
mysql>alter table users01 add index idx_nickname(nickname);
#SQL执行计划
| 1 | SIMPLE | users01 | NULL | ALL | NULL | NULL | NULL | NULL | 114543 | 11.11 | Using where |
Из плана выполнения видно, что type=ALL и Extra=Using, где все сканируются, а функция ICP не используется.
Вспомогательный индекс idx_nickname(псевдоним) содержит идентификатор первичного ключа, который эквивалентен составному индексу (id, псевдоним), попробуйте использовать функцию покрывающего индекса, чтобы переписать SQL как идентификатор выбора из users01, где псевдоним типа «%SK% '**.
| 1 | SIMPLE | users01 | NULL | index | NULL | idx_nickname | 83 | NULL | 114543 | 11.11 | Using where; Using index |
Как видно из плана выполнения, type=index, Extra=Using where; Используя index, индекс просматривается полностью, но нужные данные можно найти в столбце index, и нет необходимости возвращаться к таблице. Используя эту функцию, исходный оператор SQL сначала получает идентификатор первичного ключа, а затем связывает его с исходной таблицей через идентификатор и анализирует свой план выполнения.
select * from users01 a , (select id from users01 where nickname like '%SK%') b where a.id = b.id;
| 1 | SIMPLE | users01 | NULL | index | PRIMARY | idx_nickname | 83 | NULL | 114543 | 11.11 | Using where; Using index |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.users01.id | 1 | 100.00 | NULL |
Из плана выполнения индекс idx_nickname не нужно возвращать в таблицу для доступа к данным, а время выполнения сокращается с 60 мс до 40 мс.Тип = индекс означает, что используется не функция ICP, а Использование где; Использование индекса может быть использовано для сканирования индекса без возврата к таблице, способ уменьшить накладные расходы на ресурсы для повышения производительности.
полный текстовый указатель
MySQL 5.6 поддерживает полнотекстовое индексирование, которое может создавать полнотекстовые индексы для типов строк переменной длины, чтобы ускорить операции DML в бизнес-сценариях с нечетким сопоставлением. Это инвертированный индекс (обратный индекс), 6 вспомогательных индексных таблиц (вспомогательных индексных таблиц) автоматически создаются при создании полнотекстового индекса, и он поддерживает параллельное создание индексов.
Удаление данных полнотекстовой индексированной таблицы приведет к большому количеству операций удаления во вспомогательной индексной таблице.InnoDB использует внутреннее удаление меток и записывает удаленные DOC_ID в специальную таблицу FTS_*_DELETED, но размер индекса не будет уменьшен. Вам нужно установить параметр After innodb_optimize_fulltext_only=ON, а затем запустить OPTIMIZE TABLE, чтобы перестроить полнотекстовый индекс.
Функции полнотекстового индексирования
-
Начиная с MySQL 5.7, встроенный подключаемый модуль полнотекстового поиска ngram используется для поддержки сегментации китайских слов и действителен для механизмов MyISAM и InnoDB.
-
Из-за характеристик кэширования и пакетной обработки полнотекстового индекса операция Insert&Update обрабатывается при фиксации транзакции, и видны только отправленные данные.
-
Использование функции полнотекстового индексирования ПОИСКПОЗ () ... .. ПРОТИВ () для извлечения, ПОИСКПОЗ и количество столбцов в порядке () должны соответствовать определению индекса.
-
Может использоваться только для таблиц InnoDB и MyISAM, не поддерживает секционированные таблицы и не поддерживает поиск подстановочных знаков %.
-
Список MATCH() точно соответствует столбцам определения полнотекстового индекса таблицы.
-
Подсказка оптимизатора MySQL ограничена полнотекстовыми индексами.
Два режима поиска
-
IN NATURAL LANGUAGE MODE: Режим по умолчанию, поиск на естественном языке, ПРОТИВ('Следить за ветром' В РЕЖИМЕ ЕСТЕСТВЕННОГО ЯЗЫКА ) эквивалентен ПРОТИВ('Следить за ветром').
-
**В БУЛЕВОМ РЕЖИМЕ: **В логическом режиме символы до и после таблицы представляют собой строки со специальным значением, например, для поиска записей, содержащих SK, но не Lyn, вы можете использовать символы + и -.
AGAINST('+SK -Lyn' in BOOLEAN MODE);
В это время найдите такой псевдоним, как «%Lyn%», и вы можете узнать, что слово Lyn хранится в документе 4, инвертируя индекс ассоциативного массива, а затем находя его в специальной таблице вспомогательных индексов.
Полнотекстовый индексный анализ
Добавить полнотекстовый индекс, поддерживающий сегментацию китайских слов, к нику пользователей таблицы01
mysql>alter table users01 add fulltext index idx_full_nickname(nickname) with parser ngram;
Просмотр распределения данных
#设置当前的全文索引表
mysql>set global innodb_ft_aux_table = 'test/users01';
#查看数据文件
mysql>select * from information_schema.innodb_ft_index_cache;
+--------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
.............
| 看风 | 7 | 7 | 1 | 7 | 3 |
| 笑看 | 7 | 7 | 1 | 7 | 0 |
Анализ объектов, связанных с полнотекстовым индексом
#全文索引对象分析
mysql>SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES where name like 'test/%';
| 1198 | test/users01 | 139 |
#存储被标记删除同时从索引中清理的文档ID,其中_being_deleted_cache是_being_deleted表的内存版本。
| 1199 | test/fts_00000000000004ae_being_deleted | 140 |
| 1200 | test/fts_00000000000004ae_being_deleted_cache | 141 |
#存储索引内部状态信息及FTS_SYNCED_DOC_ID
| 1201 | test/fts_00000000000004ae_config | 142 |
#存储被标记删除但没有从索引中清理的文档ID,其中_deleted_cache是_deleted表的内存版本。
| 1202 | test/fts_00000000000004ae_deleted | 143 |
| 1203 | test/fts_00000000000004ae_deleted_cache | 144 |
Оптимизация нечеткого соответствия
Для прозвища состояния, как «% Kanfeng%» после оператора SQL, CBO не решит использовать индекс NickName по умолчанию. SQL должен быть записан как метод сопоставления полнотекстового индекса:match(псевдоним) против('Следи за ветром').
mysql>explain select * from users01 where match(nickname) against('看风');
| 1 | SIMPLE | users01 | NULL | fulltext | idx_full_nickname | idx_full_nickname | 0 | const | 1 | 100.00 | Using where; Ft_hints: sorted |
Полнотекстовый индекс используется для запроса, type=fulltext, и в то же время попадает в полнотекстовый индекс idx_full_nickname.Из вышеприведенного анализа видно, что в MySQL полнотекстовый индекс может использоваться для повысить эффективность SQL-запроса с полным нечетким соответствием %%.
Создать столбец
MySQL 5.7 стала поддерживать генерируемые столбцы.Сгенерированные столбцы вычисляются из значения выражений.Есть два режима: VIRTUAL и STORED.Если не указывать VIRTUAL по умолчанию, синтаксис создания следующий:
col_name data_type [GENERATED ALWAYS] AS (expr) [**VIRTUAL** | **STORED**] [NOT NULL | NULL]
Создание функций столбца
- VIRTUALСоздание колонн для сложных определений условий, упрощения и объединения запросов, не занимают пространство, рассчитываются столбцы доступа.
- STOREDСгенерированные столбцы используются в качестве материализованных кэшей, что может снизить вычислительные затраты и занять место на диске для сложных условий.
- Поддержка создания вторичных индексов, секционирования и генерации столбцов может имитировать функциональные индексы.
- Хранимые процедуры, определяемые пользователем функциональные выражения, встроенные НЕОПРЕДЕЛЕННЫЕ функции, такие как NOW(), RAND() и подзапросы, не поддерживаются.
Создать столбец с помощью
#添加基于函数reverse的生成列reverse_nickname
mysql>alter table users01 add reverse_nickname varchar(200) generated always as (reverse(nickname));
#查看生成列信息
mysql>show columns from users01;
| reverse_nickname | varchar(200) | YES | | NULL | VIRTUAL GENERATED | #虚拟生成列
Оптимизация нечеткого соответствия
Для подобных "%xxx" после условия where нельзя использовать сканирование индекса. Это можно решить, используя метод создания столбцов MySQL 5.7 для имитации функциональных индексов. Конкретные шаги заключаются в следующем:
- Обратная функция, использующая встроенный «% FY», перевернутая как «% ветровое облако», сгенерированное на основе этой функции, чтобы добавить виртуальный столбец.
- Создайте индексы для виртуальных сгенерированных столбцов.
- Перепишите SQL для фильтрации, создав столбец, например reverse('%fengyun'), и используйте индекс для созданного столбца.
Добавьте фиктивные сгенерированные столбцы и создайте индексы.
mysql>alter table users01 add reverse_nickname varchar(200) generated always as (reverse(nickname));
mysql>alter table users01 add index idx_reverse_nickname(reverse_nickname);
#SQL执行计划
| 1 | SIMPLE | users01 | NULL | range | idx_reverse_nickname | idx_reverse_nickname | 803 | NULL | 1 | 100.00 | Using where |
Видно, что для таких сценариев, как «%xxx», где нельзя использовать индекс, это можно решить путем индексирования на основе сгенерированного столбца.
Суммировать
В этой статье представлены функция толкания ICP в условиях индекса, полнотекстовые индексации и генерации функций столбцов. Эти функции могут быть использованы для оптимизации бизнеса SQL с нечетким сопоставлением, как% XXX% или как% XXX, которые могут эффективно сократить ненужные данные и уменьшить IO. Сканирование и накладные расходы CPU для улучшения стабильности обслуживания. Для новых функций, выпущенных в каждой версии MySQL, особенно связанных с оптимизатором и SQL, вы должны обратить внимание и понимать, и вы можете найти функции, подходящие для ваших собственных бизнес-сценариев.