5. Расширенные возможности MySQL

5.1 Таблица разделов

• Для пользователей таблица разделов является независимой логической таблицей, но нижний уровень состоит из нескольких физических таблиц слов.
  • Код, который реализует секционирование, на самом деле является инкапсуляцией набора объектов-обработчиков для базовых таблиц.
  • Запрос таблицы разделов будет преобразован в вызов интерфейса к объекту хранения через объект дескриптора.
  • Таким образом, раздел представляет собой черный ящик, который полностью инкапсулирует базовую реализацию уровня SQL и прозрачен для приложения, но его легко обнаружить из базовой файловой системы.Каждая таблица разделов имеет файл таблицы с именем, разделенным символом #. . . .
• Способ реализации секционированных таблиц в MySQL — инкапсуляция базовой таблицы — означает, что индексы также определяются в терминах секционированных таблиц слов, а не глобальных индексов. Это отличается от Oracle.В Oracle более гибко определять, являются ли индексы и таблицы секционированными.
• MySQL использует предложение PARTITION BY для определения данных, хранящихся в каждом разделе при создании таблицы. При выполнении запроса оптимизатор отфильтрует те разделы, в которых нет нужных нам данных в соответствии с определением раздела, так что запросу не нужно сканировать все разделы — ему нужно только найти разделы, содержащие требуемые данные .
• Одной из основных целей секционирования является разделение данных по разным таблицам в соответствии с более грубой степенью детализации. Это позволит сохранить связанные данные вместе, а также будет удобно, если вы хотите сразу удалить данные всего раздела.
• Сценарии, в которых разделение играет большую роль:
  • Таблица слишком велика, чтобы вместить всю память, или в последней части таблицы есть только горячие данные, а остальное — исторические данные.
  • Данные секционированных таблиц легче поддерживать. Например, если вы хотите удалить большое количество данных в пакетном режиме, вы можете использовать метод очистки всего раздела. Кроме того, независимый раздел можно оптимизировать, проверить и исправить.
  • Данные таблицы разделов могут быть распределены по разным физическим устройствам, что позволяет эффективно использовать несколько аппаратных устройств.
  • Секционированные таблицы можно использовать, чтобы избежать некоторых особых узких мест, таких как взаимоисключающий доступ к одному индексу InnoDB, конкуренция за блокировку inode для файловых систем ext3 и т. д.
  • Отдельные разделы также могут быть зарезервированы и восстановлены при необходимости, что очень хорошо работает с очень большими наборами данных.
• Разделенная таблица часть более важных ограничений:
  • Таблица может иметь до 1024 разделов.
  • В MySQL 5.1 выражение раздела должно быть целым числом или выражением, которое возвращает целое число. В MySQL 5.5 столбцы могут использоваться непосредственно для разделения в некоторых сценариях.
  • Если в поле раздела есть столбцы первичного ключа или уникального индекса, необходимо включить все столбцы первичного ключа и уникального индекса.
  • Ограничения внешнего ключа нельзя использовать в секционированных таблицах.

5.1.1 Принцип таблицы разделов

• Как упоминалось ранее, таблица разделов реализована несколькими связанными базовыми таблицами, которые также идентифицируются объектами Handler, поэтому к каждому разделу можно получить прямой доступ.
  • Механизм хранения управляет базовыми таблицами раздела так же, как и обычными таблицами (все базовые таблицы должны использовать один и тот же механизм хранения), а индекс секционированной таблицы просто добавляет идентичный индекс к каждой базовой таблице.
  • С точки зрения механизма хранения базовая таблица ничем не отличается от обычной таблицы, и механизму хранения не нужно знать, является ли это обычной таблицей или частью многораздельной таблицы.
• Операции с секционированными таблицами:
  • Запрос SELECT: при запросе секционированной таблицы уровень секций сначала открывает и блокирует все базовые таблицы, оптимизатор сначала определяет, можно ли фильтровать некоторые секции, а затем вызывает соответствующий интерфейс механизма хранения для доступа к данным каждой секции.
  • Операция INSERT: при записи записи уровень раздела сначала открывает и блокирует все базовые таблицы, затем определяет, какой раздел получает запись, а затем записывает запись в соответствующую базовую таблицу.
  • Операция DELETE: при удалении записи уровень раздела сначала открывает и блокирует все базовые таблицы, затем определяет раздел, соответствующий данным, и, наконец, удаляет соответствующую базовую таблицу.
  • Операция UPDATE: при обновлении записи уровень раздела сначала открывает и блокирует все базовые таблицы. MySQL сначала определяет, в каком разделе находится обновляемая запись, а затем извлекает данные и обновляет их. быть помещенным, наконец, записать в базовую таблицу и удалить базовую таблицу, в которой находятся исходные данные.
• Некоторые операции поддерживают фильтрацию.
  • При удалении записи MySQL должен сначала найти запись.Если условие WHERE соответствует выражению раздела, он может отфильтровать все разделы, которые не содержат эту запись. Это также справедливо для операторов UPDATE.
  • Если это операция INSERT, поражается только один раздел, а остальные разделы будут отфильтрованы. MySQL сначала определяет, к какому разделу принадлежит эта операция, а затем записывает запись в соответствующую базовую таблицу разделов, не выполняя операций на каких-либо других разделах.
• Хотя каждая операция «сначала открывает и блокирует все базовые таблицы», это не означает, что таблица разделов блокирует всю таблицу во время обработки. Если механизм хранения может сам реализовать блокировки на уровне строк, например InnoDB, соответствующие блокировки таблиц будут сняты на уровне раздела. Этот процесс блокировки и разблокировки аналогичен запросам в обычной InnoDB.

5.1.2 Типы секционированных таблиц

• MySQL поддерживает множество секционированных таблиц. Наиболее распространенным является разделение в соответствии с диапазоном, в каждом разделе хранятся записи, попадающие в определенный диапазон, а выражением раздела может быть столбец или выражение, содержащее столбец.
• В предложении PARTITION можно использовать различные функции, но значение, возвращаемое выражением, должно быть определенным целым числом и не может быть константой.
• MySQL также поддерживает разбиение на ключ-значение, хеш и список, некоторые из которых также поддерживают разбиение на подразбиения. В MySQL 5.5 также можно использовать разделы типа RANGE COLUMNS, так что даже разделы, основанные на времени, не нужно преобразовывать в целое число.
  • Для таблиц InnoDB, секционированных по времени, система может снизить конкуренцию за взаимоисключающий доступ к индексам посредством подсекционирования. К данным раздела за последний год будут обращаться очень часто, что приведет к большому количеству конфликтов мьютексов. Использование хэш-разбиения может разрезать данные на несколько небольших частей, что значительно снижает проблему конфликтов мьютексов.
• Другие методы разделения:
  • Разделите в соответствии со значением ключа, чтобы уменьшить конкуренцию мьютексов InnoDB
  • Используйте математическую функцию по модулю, чтобы разбить данные на разделы, а затем опросить их по разным разделам. Например, вы можете сделать по модулю 7 дату или, проще говоря, использовать функцию, которая возвращает день недели, если вы хотите сохранить данные только за самые последние дни, это удобно для разбиения
  • Предположим, в таблице есть автоматически увеличивающийся идентификатор столбца первичного ключа, и вы хотите централизованно хранить последние горячие данные в соответствии со временем. Тогда временная метка должна быть включена в первичный ключ, что противоречит смыслу самого первичного ключа. В этом случае для той же цели можно использовать выражение разделения, подобное этому:HASH(id DIV 1000000), который создаст раздел для 1 миллиона данных. С одной стороны, достигается первоначальная цель разделения, а с другой стороны, это позволяет избежать проблемы по сравнению с использованием разделения по временному диапазону, то есть при превышении определенного порога, если используется разделение по временному диапазону, новые разделы должны быть добавлено.

5.1.3 Как использовать таблицу разделов

• Предположим, нужно найти данные за последние месяцы из очень большой таблицы с 1 миллиардом записей:
  • Из-за огромного количества данных определенно невозможно сканировать всю таблицу каждый раз, когда вы запрашиваете
  • С учетом того, что для индексов требуется пространство и обслуживание, использование индексов также нежелательно.
    • Если это не покрывающий запрос, сервер должен вернуть таблицу в соответствии с результатом сканирования индекса.
    • Если вы действительно используете индексы, вы обнаружите, что данные не агрегируются так, как вы хотите, и будет большая фрагментация, в результате чего запрос будет генерировать много случайных операций ввода-вывода.
  • Остаток пути:
    • Сделать так, чтобы все запросы выполняли последовательное сканирование только таблицы данных
    • Кэшировать все таблицы и индексы в памяти
    • использовать раздел
• При понимании раздела его также можно рассматривать как начальную форму индекса, и необходимые данные могут быть расположены в какой области с очень низкими затратами. В этой области можно выполнять последовательное сканирование, строить индексы, кэшировать данные в памяти и так далее. Поскольку разделу не требуется дополнительная структура данных для записи того, какие данные есть в каждом разделе — разделу не нужно точно указывать местоположение каждого фрагмента данных, и, следовательно, не требуется никакой дополнительной структуры данных — поэтому стоимость очень низкая.

• Стратегии обеспечения масштабируемости для больших объемов данных:
  • Данные полного сканирования без каких-либо индексов:
    • Вы можете использовать простой метод секционирования, чтобы сохранить таблицу без какого-либо индекса и примерно определить местонахождение требуемых данных в соответствии с правилами секционирования. Пока условие WHERE можно использовать для ограничения требуемых данных несколькими разделами, эффективность очень высока. Конечно, необходимо выполнить некоторые простые операции, чтобы убедиться, что время ответа на запрос соответствует требованиям.
    • Использование этой стратегии предполагает, что данные не обязательно должны находиться в памяти полностью, а также предполагается, что все необходимые данные находятся на диске. Поскольку память относительно мала, данные быстро выдавливаются из памяти, поэтому кэши ничего не делают.
    • Эта стратегия применяется при обычном доступе к большим объемам данных.
    • Количество разделов, которые должен сканировать запрос, должно быть ограничено небольшим числом.
  • Индексируйте данные и изолируйте горячие точки:
    • Если данные имеют явные «горячие точки», а к другим данным редко обращаются, кроме этой части данных, то эту часть данных горячих точек можно поместить в отдельный раздел, чтобы данные в этом разделе могли иметь возможность для кэширования в середине памяти.
    • Это позволяет запросам обращаться только к небольшой секционированной таблице, эффективно использовать индексы и кэши.

5.1.4 Что пойдет не так

Две стратегии секционирования, представленные в предыдущем разделе, основаны на двух важных допущениях: запрос может отфильтровать множество дополнительных секций, а сама секция не требует больших дополнительных затрат.

Сценарии, в которых вы можете столкнуться с проблемами:

• Значения NULL делают недействительной фильтрацию разделов

  Значение выражения раздела может быть NULL: первый раздел является специальным разделом.

  • Предположим, согласноPARTITION BY RANGE YEAR(order_date)раздел, то все записи будут помещены в первый раздел, когда все order_date имеет значение NULL или очень ценное значение.
    • Предположим, у вас есть следующий запрос:WHERE order_date BETWEEN '2012-01-01' AND '2012-01-31', MySQL фактически проверит оба раздела, потому что YEAR() вернет NULL при получении недопустимого значения и поместит запись в первый раздел.
  • Если первый раздел очень большой, особенно при использовании стратегии «полное сканирование данных без каких-либо индексов», стоимость будет очень высокой.
  • Советы по оптимизации:
    • Создайте бесполезный первый раздел, например:PARTITION p_nulls VALUES LESS THAN (0). Таким образом, даже если необходимо проверить первый раздел, затраты будут очень малы.
    • (Оптимально) Первый прием оптимизации не нужен после MySQL 5.5, потому что сам столбец можно использовать напрямую вместо функций на основе столбцов для разделения.PARTITION BY RANGE COLUMNS(order_date)

• Столбцы разделов и индексов не совпадают:
  • Если определенные столбцы индекса и столбцы раздела не совпадают, запрос не сможет выполнить фильтрацию раздела.
    • Предположим, что индекс определен для столбца a, а секционирование выполняется для столбца b. Поскольку каждая секция имеет свой собственный независимый индекс, сканирование индекса для индекса в столбце a требует сканирования соответствующего индекса в каждой секции.
  • Вам следует избегать создания индекса, который не соответствует столбцу секции, если запрос также не включает условия, которые могут фильтровать секцию:
    • Другие проблемы: если в ассоциативном запросе секционированная таблица является второй таблицей в ассоциативном порядке, а индекс и условия секционирования, используемые ассоциацией, не совпадают. Затем для каждой строки, удовлетворяющей условиям в первой таблице, необходимо получить доступ и выполнить поиск по всем разделам второй таблицы.
• Стоимость выбора разделов может быть высокой
  • Разделы разных типов, из-за разных реализаций их свойства разные
    • В частности, при разделении по диапазонам стоимость ответа на вопрос «Какому разделу принадлежит эта строка» может быть очень высокой, поскольку серверу необходимо просмотреть список всех разделов, чтобы найти правильный ответ. Подобный линейный поиск неэффективен, и по мере роста числа разделов стоимость будет становиться все выше и выше.
    • Другие типы разделов, такие как разделы по ключам и разделы по хэшам, не имеют этой проблемы.
  • Для большинства систем 100 или около того разделов не проблема.
• Открытие блокировок и блокировка всех базовых таблиц может быть дорогостоящим
  • Когда запрос обращается к разделенной таблице, MySQL должен открывать блокировки и блокировать все базовые таблицы, что является еще одним накладными расходами для разделенных таблиц.
    • Эта операция выполняется перед фильтрацией секций, поэтому эти накладные расходы не могут быть уменьшены с помощью фильтрации секций, и эти накладные расходы не зависят от типа секции и влияют на все запросы.
  • Для некоторых запросов, которые сами по себе очень быстры, например для поиска одной строки на основе первичного ключа, потребуются значительные дополнительные накладные расходы.
  • Советы по оптимизации:
    • Используйте пакетные операции, чтобы уменьшить нагрузку на одну операцию, например, использование пакетной вставки или ЗАГРУЗКИ ДАННЫХ В ФАЙЛ, одновременное удаление нескольких строк данных и т. д.
    • Ограничьте количество разделов
• Обслуживание разделов может быть дорогим
  • Некоторые операции по обслуживанию разделов, такие как добавление или удаление разделов, могут быть очень быстрыми (удаление большого раздела может быть медленным, но это уже другая история).
  • Некоторые операции, такие как реорганизация разделов или такие операции, как операторы ALTER, выполняются медленнее, поскольку данные необходимо копировать.

Некоторые другие ограничения в реализации раздела:

• Все разделы должны использовать один и тот же механизм хранения
• Существуют также некоторые ограничения на функции и выражения, которые можно использовать в функциях разбиения.
• Некоторые механизмы хранения не поддерживают разбиение на разделы.
• Операцию LOAD INDEX INTO CACHE больше нельзя использовать для секционированных таблиц MyISAM.
• Для таблиц MyISAM необходимо открывать больше файловых дескрипторов при использовании секционированных таблиц. Каждый раздел является независимой таблицей для механизма хранения, и даже если таблица разделов занимает только одну запись табличного кэша, требуется несколько файловых дескрипторов.

5.1.5 Оптимизация запросов

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

  • MySQL может фильтровать секции только путем сравнения столбцов с использованием самой функции секционирования, но не на основе значения выражения, даже если выражение является функцией секционирования. Это то же самое, что использовать индекс в запросе с использованием отдельного столбца.

    -- 无法使用分区
    mysql> EXPLAIN PARTITIONS SELECT * FROM sales_by_day WHERE YEAR(day) = 2010\G;
    -- 可使用分区
    mysql> EXPLAIN PARTITIONS SELECT * FROM sales_by_day
    -> WHERE day BETWEEN '2010-01-01' AND '2010-12-31'\G;

• Оптимизатор всегда старается максимально эффективно фильтровать секции при обработке запроса. Например, если таблица разделов является второй таблицей в операции соединения, а условием соединения является ключ раздела, MySQL будет сопоставлять только строки в соответствующем разделе. (EXPLAIN не может показать фильтрацию раздела в этом случае, потому что это фильтрация раздела во время выполнения, а не на этапе оптимизации запроса)

5.1.6 Сводная таблица

Таблицы слияния были ранней простой реализацией секционирования с некоторыми другими ограничениями и отсутствием оптимизации по сравнению с секционированными таблицами. Объединенные таблицы позволяют пользователям получать доступ к каждой подтаблице по отдельности. Таблица разделов — это будущая тенденция развития, а таблица слияния — это технология, которая будет исключена и может быть удалена в будущих версиях, поэтому я не буду здесь подробно останавливаться.

5.2 Просмотры

• Само представление представляет собой виртуальную таблицу, которая не хранит никаких данных. Когда доступ к представлению осуществляется с помощью оператора SQL, возвращаемые им данные генерируются MySQL из других таблиц.

  • Представления и таблицы находятся в одном и том же пространстве имен, и MySQL обрабатывает таблицы одинаково во многих местах. Однако вы не можете создавать триггеры для представлений и не можете использовать команду DROP TABLE для удаления представлений.
  • Он был введен после версии MySQL 5.0.
  • Личное понимание: представление не оптимизирует запрос, а только обеспечивает лучшее отображение результатов, поскольку базовый принцип заключается в запросе исходной таблицы. В некоторых случаях может помочь улучшить производительность.

• Принцип работы:

  -- 实现视图最简单的办法是将SELECT语句的结果存放到临时表中。
  mysql> CREATE VIEW Oceania AS
  -> SELECT * FROM Country WHERE Continent = 'Oceania'
  -> WITH CHECK OPTION;
  -- 当需要访问视图的时候，可直接访问这个临时表
  mysql> SELECT Code, Name FROM Oceania WHERE Name = 'Australia';
  -- MySQL使用的并算法：重写含有视图的查询，将视图的定义SQL直接包含进查询的SQL中：
  mysql> SELECT Code, Name FROM Country
  -> WHERE Continent = 'Oceania' AND Name = 'Australia';
  -- MySQL 使用的临时表算法，以下SQL是为展示用的。这样做会有明显的性能问题，优化器也很难优化在这个临时表上的查询。
  mysql> CREATE TEMPORARY TABLE TMP_Oceania_123 AS
  -> SELECT * FROM Country WHERE Continent = 'Oceania';
  mysql> SELECT Code, Name FROM TMP_Oceania_123 WHERE Name = 'Australia';

  Использование MySQLАлгоритм слиянияиАлгоритм временной таблицыдля обработки представления.По возможности используйте алгоритмы слияния.

  • MySQL может даже определять вложенные представления, то есть определять другое представление поверх одного представления.
  • Вы можете использовать SHOW WARNINGS после EXPLAIN EXTENDED, чтобы увидеть результаты перезаписи запросов с использованием представлений.
    • Если представление реализовано с использованием алгоритма временной таблицы, select_type в EXPLAIN будет отображаться как производная таблица (DERIVED). Выполнение EXPLAIN может быть очень медленным, если результирующая базовая производная таблица велика. Потому что в версии 5.5 и более ранних версиях EXPLAIN необходимо выполнить и сгенерировать производную таблицу.

  Детали реализации для обоих алгоритмов:

  

  Сценарии, в которых представления реализованы с использованием алгоритма временной таблицы:

  • Представления включают GROUP BY, DISTINCT, любую агрегатную функцию, UNION, подзапросы и т. д., если между исходными записями таблицы и записями представления невозможно установить однозначное сопоставление.

  Алгоритм реализации представления является свойством самого представления и не имеет ничего общего с оператором запроса, действующим на представление. Например, алгоритм, использующий временные таблицы, можно сформулировать для представления на основе простого запроса:CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM sakila.actor;, чтобы представление создавало временную таблицу на основе любого выполняемого запроса.

5.2.1 Обновляемые представления

• Обновляемое представление означает, что связанные таблицы, участвующие в представлении, могут быть обновлены путем обновления этого представления.
  • Данные можно обновлять, удалять и даже записывать в представление, если указаны соответствующие условия.
  • Запрос на обновление представления также может быть ассоциативным оператором, но обновляемые столбцы должны поступать из одной и той же таблицы.
  • Все представления, реализованные с использованием алгоритма временной таблицы, не могут быть обновлены
  • CHECK OPTIONПредложение означает, что любая строка, обновляемая через представление, должна соответствовать определению условия WHERE самого представления. Поэтому нельзя обновлять столбцы вне представления
  • MySQL не поддерживает создание каких-либо триггеров для представлений. Некоторые реляционные базы данных позволяют триггерам INSTEAD OF в представлениях точно контролировать, что делать при изменении данных представления.

5.2.2 Влияние просмотров на производительность

• Представления также могут помочь повысить производительность в некоторых случаях, и представления могут быть объединены с другими методами повышения производительности.

• Сценарии приложений для повышения производительности:
  • При рефакторинге схемы код приложения может продолжать работать без ошибок при изменении базовой структуры таблицы представления.
  • Реализует управление разрешениями на основе столбцов, но не требует создания разрешений в реальной системе, поэтому нет дополнительных накладных расходов.
  • Используйте псевдовременный вид:
    • Хотя MySQL не может создать реальное временное представление, которое существует только в текущем соединении, он может создать представление со специальным именем, а затем удалить представление, когда соединение прервется. Таким образом, представление можно использовать в предложении FROM во время процесса соединения, пути кода MySQL для обработки представлений и подзапросов совершенно разные, поэтому их производительность также отличается.
    • Конфликтов можно избежать, используя идентификатор соединения как часть имени представления. Этот прием упрощает очистку временных представлений в случае сбоев приложения и других аварий, из-за которых их не удается очистить.
  • Представления, реализованные с использованием алгоритма временной таблицы, в какой-то момент будут выполнять неправильные запросы (хотя это может быть немного лучше, чем прямое использование эквивалентного оператора запроса).
    • MySQL выполняет этот тип представления рекурсивным образом и сначала выполнит внешний запрос.Даже если внешний оптимизатор запросов оптимизирует его хорошо, оптимизатор MySQL может быть не в состоянии выполнять больше комбинированных внутренних и внешних оптимизаций, как другие базы данных. Условие WHERE внешнего запроса не может быть "опущено" в запрос временной таблицы, формирующей представление, и временная таблица не может быть проиндексирована.
• Обратите внимание на сложность представления, возможно, оно ссылается на множество таблиц. Если вы планируете использовать представления для повышения производительности, вам необходимо провести более детальное тестирование. Даже представления, реализованные с помощью алгоритмов слияния, имеют дополнительные накладные расходы, и производительность представления трудно предсказать. Потому что в оптимизаторе MySQL путь выполнения кода представления совсем другой, и тестирование этой части кода недостаточно всесторонне, и могут быть какие-то скрытые дефекты и проблемы, поэтому текущее представление не настолько зрело.

5.2.3 Просмотр ограничений

• MySQL также не поддерживает материализованные представления (это означает, что данные результатов представления хранятся в просматриваемой таблице, и данные периодически обновляются из исходной таблицы в эту таблицу), а также не поддерживает создание индексов в представлениях. Материализованные представления и индексы можно имитировать путем создания кэш-таблиц или сводных таблиц.

• MySQL не сохраняет исходный оператор SQL определения представления, поэтому вы не можете переопределить представление, просто изменив результат после выполнения SHOW CREATE VIEW.

  • Если вы планируете изменить представление и не можете найти исходный оператор создания представления, вы можете получить некоторую информацию, используя последнюю строку файла .frm представления. Если у вас есть разрешение FILE, вы даже можете напрямую использовать LOAD_FILE() для чтения информации о создании представления в .frm, а также некоторую работу по обработке символов.

    mysql> SELECT
    -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    -> SUBSTRING_INDEX(LOAD_FILE('/var/lib/mysql/world/Oceania.frm'),
    -> '\nsource=', −1),
    -> '\\_','\_'), '\\%','\%'), '\\\\','\\'), '\\Z','\Z'), '\\t','\t'),
    -> '\\r','\r'), '\\n','\n'), '\\b','\b'), '\\\"','\"'), '\\\'','\''),
    -> '\\0','\0')
    -> AS source;

5.3 Ограничения внешнего ключа

• InnoDB в настоящее время является единственным встроенным механизмом хранения в MySQL, который поддерживает внешние ключи.
• Существует стоимость использования внешних ключей.
  • Например, внешние ключи обычно требуют еще одной операции поиска в другой таблице при каждом изменении данных. Хотя InnoDB принуждает использовать индексы для внешних ключей, она по-прежнему не может устранить накладные расходы на эту проверку ограничений. Если селективность столбца внешнего ключа низкая, это приведет к очень большому и низкому индексу селективности.
• Внешние ключи могут улучшить производительность в некоторых сценариях:
  • Если вы хотите, чтобы две связанные таблицы всегда содержали непротиворечивые данные, то использование внешних ключей намного эффективнее, чем проверка согласованности в вашем приложении.
  • Внешние ключи также более эффективны для удаления и обновления связанных данных, чем для сохранения в приложении, однако обслуживание внешнего ключа выполняется построчно, поэтому такие обновления будут медленнее, чем пакетное удаление и обновление.
• Из-за ограничений внешнего ключа запросы требуют дополнительного доступа к другим таблицам, что также означает дополнительные блокировки.
  • Если запись записывается в дочернюю таблицу, ограничение внешнего ключа заставит InnoDB проверять запись соответствующей родительской таблицы, и необходимо заблокировать соответствующую запись родительской таблицы, чтобы гарантировать, что эта запись не будет завершена, когда транзакция завершена. была удалена. Это вызывает дополнительное ожидание блокировки и даже некоторые взаимоблокировки. Поскольку прямого доступа к этим таблицам нет, такие проблемы взаимоблокировки часто трудно устранить.
• Иногда вместо внешних ключей можно использовать триггеры, которые больше подходят для обновления внешних ключей при наличии связанных данных, но если внешние ключи используются только как числовые ограничения, то триггеры или явное ограничение значения будут лучше (здесь вы можете напрямую использовать тип ENUM)
• Если ограничение является просто внешним ключом, обычно лучше реализовать ограничение в приложении. Внешние ключи принесут много дополнительного потребления.

5.4 Хранение кода внутри MySQL (пока бесполезно, краткое введение)

Он не будет использоваться какое-то время в будущем. Его необходимо использовать снова. Он больше подходит для администраторов баз данных. Здесь перечислены только наиболее часто используемые методы.

5.4.1 Хранимые процедуры и функции

5.4.2 Триггеры

Вы можете выполнять некоторые специфические операции при выполнении INSERT, UPDATE или DELETE. В MySQL можно указать, следует ли запускать до или после выполнения оператора SQL.

5.4.3 События

Аналогичны задачам синхронизации LINUX, но полностью реализованы в MySQL.

5.4.4 Сохранение комментариев в хранимых процедурах

5.5 Курсор (пока бесполезен, краткое введение)

MySQL предоставляет односторонние курсоры только для чтения на сервере и может использоваться только в хранимых процедурах или клиентских API более низкого уровня. Поскольку объекты, на которые указывает курсор, хранятся во временных таблицах, а не в фактических данных запроса, курсоры MySQL всегда доступны для чтения.

5.6 Связывание переменных

• Оператор SQL для связываемой переменной:INSERT INTO tbl(col1, col2, col3) VALUES (?, ?, ?);. В SQL переменных связывания используйте вопросительные знаки, чтобы отметить позиции, которые могут получать параметры.Когда необходимо выполнить конкретный запрос, вместо этих вопросительных знаков используются конкретные значения.
• При создании переменной связывания SQL клиент (например, C или JAVA и т. д.) отправляет прототип оператора SQL на сервер. После получения кадра оператора SQL сервер анализирует и сохраняет частичный план выполнения оператора SQL и возвращает клиенту дескриптор обработки оператора SQL. Каждый раз, когда такой запрос будет выполняться в будущем, клиент будет использовать этот дескриптор.
• Причины, по которым MySQL может более эффективно выполнять большое количество повторяющихся операторов при использовании переменных связывания:
  • На стороне сервера необходимо проанализировать только один оператор SQL.
  • Некоторую часть работы оптимизатора на стороне сервера нужно выполнить только один раз, потому что он кэширует часть плана выполнения.
  • Отправка только параметров и дескрипторов в двоичном виде более эффективна, чем отправка текста ASCII каждый раз. Однако наибольшая экономия достигается за счет полей BLOB и TEXT, которые можно передавать порциями в виде переменных привязки без необходимости единовременной передачи. Двоичный протокол также может сэкономить много внутренних ресурсов на стороне клиента, уменьшив нагрузку на сеть, а также сократив затраты на преобразование данных из сохраненного необработанного формата в текстовый формат.
  • На сервер необходимо отправлять только параметры, а не весь оператор запроса, поэтому нагрузка на сеть будет меньше.
  • Когда MySQL сохраняет параметры, он сохраняет их непосредственно в кэше, и их больше не нужно многократно копировать в память.
• Переменные связывания также относительно безопаснее. Нет необходимости заниматься экранированием в приложении, что проще и понятнее, а риск SQL-инъекций и атак значительно снижается.

5.6.1 Оптимизация переменных связывания

Теоретически некоторым оптимизаторам нужно сделать это только один раз, но на практике все равно выполняются следующие операции. В зависимости от того, когда работает оптимизатор, оптимизации можно разделить на три категории:

• На этапе подготовки: сервер анализирует оператор SQL, удаляет невозможные условия и переписывает подзапросы.
• При первом выполнении: если возможно, сервер упрощает ассоциации вложенных циклов и преобразует внешние ассоциации во внутренние ассоциации.
• При каждом выполнении оператора SQL сервер делает следующее:
  • раздел фильтра
  • Если возможно, попробуйте удалить COUNT(), MIN() и MAX()
  • удалить постоянное выражение
  • Проверить таблицу констант
  • Выполните необходимое распространение эквивалентности
  • Анализируйте и оптимизируйте методы доступа к данным, такие как оптимизация ссылок, диапазонов и индексов.
  • Оптимизируйте порядок ассоциаций.

5.6.2 Связывание переменных интерфейса SQL (временно бесполезно, краткое введение)

В основном используется в хранимых процедурах.

5.6.3 Ограничения на переменные связывания

• Переменные связывания находятся на уровне сеанса, поэтому дескрипторы переменных связывания не могут совместно использоваться соединениями. Аналогичным образом, после разрыва соединения исходный дескриптор больше нельзя будет использовать. (Пул соединений и постоянные соединения могут в некоторой степени облегчить эту проблему)
• До MySQL 5.1 переменные связывания SQL не могли использовать кеш запросов.
• Не всегда использование переменных связывания приводит к повышению производительности. Если SQL выполняется только один раз, метод использования переменных связывания может потребовать дополнительной стадии подготовки и использования, чем прямое выполнение, а также требует дополнительных сетевых издержек. (Чтобы правильно использовать переменные связывания, вам также необходимо освободить связанные ресурсы после завершения использования)
• В текущей версии переменные связывания нельзя использовать в хранимых функциях, но можно использовать в хранимых процедурах.
• Если вы всегда забываете освобождать переменные ресурсы связывания, утечка ресурсов может легко произойти на стороне сервера. Ограничение связанной переменной SQL всегда является глобальным ограничением, поэтому еще одна ошибка может повлиять на все остальные потоки.
• Некоторые операции, такие как BEGIN, нельзя выполнять в переменных связывания.

Некоторые различия между тремя типами переменных связывания:

• Смоделированная переменная связывания на стороне клиента. Драйвер на стороне клиента получает SQL-запрос с параметрами, затем принимает в него указанное значение и, наконец, отправляет полный запрос на сервер.
• Переменные привязки на стороне сервера: клиент использует специальный бинарный протокол для отправки строки с параметрами на сервер, а затем использует бинарный протокол для отправки определенных значений параметров на сервер для выполнения.
• Связать переменные интерфейса SQL: клиент сначала отправляет на сервер строку с параметрами, что аналогично оператору SQL с использованием PREPARE, затем отправляет SQL, устанавливающий параметры, и, наконец, использует EXECUTE для выполнения SQL. Все это делается с использованием обычного протокола передачи текста.

5.7 Пользовательские функции (временно бесполезны, краткое введение)

Реализуйте определяемые пользователем функции (UDF) на любом языке программирования, который поддерживает соглашение о вызовах C. Пользовательские функции должны быть скомпилированы и динамически связаны с сервером.

5.8 Плагины (временно бесполезны, краткое введение)

Тип плагина:

• Плагин хранимых процедур
• Фоновый плагин
• Плагин INFORMATION_SCHEMA
• Плагин полнотекстового парсинга
• Плагин аудита
• Плагин аутентификации

5.9 Наборы символов и сопоставление

Набор символов относится к сопоставлению двоичной кодировки с символом определенного типа.Вы можете обратиться к тому, как использовать байт для представления английских символов. Сопоставление относится к набору правил сопоставления для набора символов.

5.9.1 Как MySQL использует наборы символов

Каждый набор символов может иметь несколько правил сопоставления, и существует правило сопоставления по умолчанию.Каждое правило сопоставления предназначено для определенного набора символов, поэтому набор символов и правила сопоставления вместе называются наборами символов.

В MySQL имеется множество опций для управления набором символов, и эти параметры легко спутать с набором символов. Только символьные значения действительно имеют понятие набора символов. Для других типов значений набор символов — это просто параметр, указывающий, какой набор символов использовать для сравнения или других операций.

Настройки MySQL:

• Настройки по умолчанию при создании объектов:

  • При создании базы данных набор символов по умолчанию для базы данных будет установлен в соответствии с параметром character_set_server на сервере.
  • При создании таблицы параметр набора символов таблицы будет указан в соответствии с параметром набора символов базы данных.
  • При создании столбца настройка набора символов столбца задается в соответствии с настройками таблицы.
  • Для трех вышеперечисленных слоев каждый слой указывает только значение по умолчанию.Если этот слой не определяет набор символов, вступает в силу значение по умолчанию.

• Настройки для связи сервера и клиента:

  • Когда сервер и клиент обмениваются данными, они могут использовать разные наборы символов. В это время серверная сторона выполнит необходимые работы по переводу и конвертации:

    • Сервер всегда предполагает, что клиент передает данные и операторы SQL в соответствии с символами, установленными с помощью character_set_client.
    • Когда сервер получает оператор SQL от клиента, он сначала преобразует его в набор символов character_set_connection. Он также использует этот параметр, чтобы решить, как преобразовать данные в строки.
    • Когда сервер возвращает данные или информацию об ошибке клиенту, он преобразует их в character_set_result.

    

  • При необходимости вы можете использовать оператор SET NAMES или SET CHARACTER для изменения указанных выше настроек. Однако использование этой команды на сервере может изменить только настройки на стороне сервера. Клиентские программы и клиентские API также должны использовать правильный набор символов, чтобы избежать проблем при обмене данными.

Когда MySQL сравнивает размер двух строк, он преобразует их в один и тот же набор символов, а затем сравнивает их.Если два набора символов несовместимы, будет выдана ошибка. MySQL также устанавливает «конвертируемость» для каждой строки, которая определяет приоритет набора символов значения, и, таким образом, создается впечатление, что MySQL выполняет неявное преобразование набора символов в значение.

• Вы также можете использовать префиксы и предложение COLLATE, чтобы указать набор символов или набор символов сопоставления строки.

  mysql> SELECT _utf8 'hello world' COLLATE utf8_bin;

Некоторые особые случаи:

• Странная настройка character_set_database: при изменении базы данных по умолчанию эта переменная также изменится.
• LOAD DATA INFILE: база данных всегда анализирует символы в файле в соответствии с набором символов character_set_database.
• SELECT INTO OUTFILE: результаты без перекодирования письменного документа
• Встроенные escape-последовательности: MySQL анализирует escape-последовательности в соответствии с параметром character_set_client, даже если строка содержит префикс или предложение COLLATE. Потому что для парсера префикс — это не директива, а просто ключевое слово.

5.9.2 Выбор набора символов и сопоставления

• Вы можете использовать команды SHOW CHARACTERSET и SHOW COLLATION для просмотра наборов символов и правил сопоставления, поддерживаемых MYSQL.

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

• Распространенный вопрос о правилах сортировки: следует ли сравнивать строки с учетом регистра или сравнивать размеры как двоичные значения, закодированные в виде строк. Двоичная сортировка использует байты символов для прямого сравнения, в то время как сортировка с учетом регистра имеет более сложные правила сравнения для многобайтовых наборов символов, таких как немецкий.

• Как MySQL выбирает набор символов и сопоставление:

  

5.9.3 Как наборы символов и параметры сортировки влияют на запросы

Определенные наборы символов и правила сортировки могут требовать больше операций ЦП, потреблять больше памяти и места для хранения и даже влиять на нормальное использование индексов.

• Преобразование между различными наборами символов и сопоставлением может привести к дополнительным накладным расходам.
  • Индексы можно использовать для сортировки только в том случае, если набор символов, требуемый запросом сортировки, совпадает с набором символов данных сервера. Индекс сортируется в соответствии с правилами сортировки столбца данных.
• MySQL выполнит преобразование набора символов, когда это необходимо:
  • При использовании двух столбцов с разными наборами символов для связывания двух таблиц MySQL попытается преобразовать набор символов одного из столбцов.
• UTF-8 — это многобайтовая кодировка, в которой хранится символ для использования количества байтов, которые становятся. В mysql строка обычно хранится в течение фиксированного промежутка времени, и такая цель состоит в том, чтобы всегда гарантировать, что в кеше достаточно места для хранения строки.
  • В многобайтовых наборах символов символ больше не является байтом. Вы можете использовать LENGTH() и CHAR_LENGTH() для вычисления длины строки. В многобайтовых наборах символов результаты, возвращаемые двумя, будут разными, поэтому используйте последний
  • Если вы хотите проиндексировать индекс набора символов UTF-8, MySQL будет считать, что каждый символ составляет три байта, а предел самого длинного индексного префикса индекса будет сокращен до одной трети оригинала.Есть некоторые последствия для использования MySQL индексов, такие как невозможность использовать сканирование покрытия индекса.
  • Если весь набор символов UTF-8 используется напрямую, это плохо с точки зрения производительности, и это только потребует больше места для хранения, потому что многим приложениям не нужно использовать этот набор символов.
• Учитывая, что набор символов необходимо определить в соответствии с конкретной хранимой памятью:
  • Сохраняемый контент в основном состоит из английских символов, и можно использовать кодировку UTF-8, поскольку она занимает всего один байт.
  • Чтобы сохранить некоторые нелатинские символы, вы можете использовать cpl256
  • Для хранения других языков используйте UTF-8.
  • При преобразовании из кодировки определенного языка в UTF-8 использование дискового пространства будет относительно увеличиваться. Если используется таблица InnoDB, изменение набора символов может привести к тому, что размер данных превысит критическое значение, которое может быть сохранено на странице, и их необходимо будет хранить в дополнительном внешнем хранилище, что может привести к серьезной трате места и фрагментации пространства.
• Иногда вообще не нужно использовать какой-либо набор символов. Обычно вам нужно использовать наборы символов только при выполнении нечувствительных к регистру сравнений, сортировки и манипуляций со строками. Если база данных не заботится о кодировках, вы можете хранить все непосредственно в двоичных столбцах, включая данные в кодировке UTF-8. Для этого также может потребоваться набор столбцов кодирования для записи символов, что приводит ко многим трудноустранимым ошибкам. Поэтому рекомендуется по возможности этого не делать.

5.10 Полнотекстовое индексирование (временно бесполезно, краткое введение)

• Полнотекстовое индексирование имеет свой уникальный синтаксис и может работать без индекса, с индексом будет эффективнее.
• Полнотекстовое индексирование может поддерживать поиск по различному символьному содержимому, а также поиск на естественном языке и логический поиск.
• Поддерживается только движком MyISAM, InnoDB после версии 5.6 также поддерживается экспериментально.
  • Полнотекстовый индекс MyISAM представляет собой специальный индекс B-Tree с двумя уровнями. Первый уровень — это все ключевые слова, затем для каждого ключевого слова второй уровень содержит набор связанных «указателей документов».

5.10.1 Полнотекстовое индексирование естественных языков

Вычислите релевантность каждого объекта документа запросу. Релевантность основана на количестве совпадающих ключевых слов и количестве вхождений ключевого слова в документе. Слова с меньшим количеством вхождений во всем индексе будут сопоставляться более релевантно, и наоборот, очень распространенные слова не будут искаться.

5.10.2 Логическое полнотекстовое индексирование

Релевантность искомого термина можно настроить в запросе. Логический поиск отфильтровывает эти пропускающие слова через список стоп-слов, а также требует, чтобы длина искомых ключевых слов была больше ft_min_word_len и меньше ft_max_word_len. Поиск возвращает несортированные результаты.

5.10.3 Изменения в полнотекстовом индексировании в MySQL 5.1

5.10.4 Ограничения и альтернативы полнотекстовому индексированию

• предел:
  • Есть только один способ повлиять на релевантность: частотность слов.
  • размер данных
  • Также влияет на работу оптимизатора. Выбор индекса, предложения WHER, ORDER BY могут работать не так, как ожидалось.

5.10.5 Настройка и оптимизация полнотекстового индекса

• Регулярное обслуживание, такое как регулярное перестроение полнотекстового индекса, может повысить производительность.
• Убедитесь, что кэш индекса достаточно велик, чтобы все полнотекстовые индексы можно было кэшировать в памяти.
• Предоставьте хороший список стоп-слов
• Игнорирование слишком коротких слов может повысить эффективность полнотекстового индексирования.
• Как списки стоп-слов, так и допустимые минимальные длины слов могут сократить индексируемые термины, чтобы повысить эффективность полнотекстового индексирования, но в то же время снизить точность поиска.
• При импорте большого объема данных в таблицу с полнотекстовым индексом сначала ОТКЛЮЧИТЕ КЛЮЧИ, чтобы отключить полнотекстовый индекс, а затем ВКЛЮЧИТЕ КЛЮЧИ, чтобы построить полнотекстовый индекс после импорта данных.
• Если набор данных очень большой, вам необходимо вручную разделить данные, затем распределить данные по разным узлам и выполнить параллельный поиск.

5.11 Распределенные (XA) транзакции

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

• Этап 1. В транзакции XA требуется координатор транзакции, чтобы убедиться, что все участники транзакции завершили подготовительную работу.
• Фаза 2: если координатор получает сообщение о том, что все участники готовы, он сообщает всем транзакциям о необходимости фиксации.
• MySQL играет роль участника этой транзакции XA, а не координатора

5.11.1 Внутренние транзакции XA

• Роль: Координация внутреннего механизма хранения и двоичного журнала.
• Каждый механизм хранения в MySQL полностью независим и не знает о существовании друг друга, поэтому для транзакции между механизмами хранения требуется внешний координатор. Например, без использования протокола XA для фиксации транзакций между механизмами хранения просто потребовалось бы, чтобы каждая сетка механизма хранения выполнялась последовательно, и если во время фиксации хранилища произошел сбой системы, характеристики транзакции были бы нарушены.
• Если операция двоичного журнала, записанная MySQL, рассматривается как независимый механизм хранения, при отправке механизма хранения представленная информация должна быть записана в двоичный файл, который является распределенной транзакцией, но участником двоичного журнала является сама MySQL. .
• Транзакции XA сильно влияют на производительность MySQL. Начиная с MySQL 5.0, он нарушал внутреннюю "массовую фиксацию" MySQL (метод выполнения нескольких транзакций с помощью одной операции ввода-вывода на диск), заставляя MySQL выполнять несколько дополнительных вызовов fsync().

5.11.2 Внешние транзакции XA

• Роль: MySQL может участвовать во внешних распределенных транзакциях.
• MySQL может завершить внешнюю распределенную транзакцию в качестве участника. Однако его поддержка протокола XA не является полной.Например, протокол XA требует, чтобы несколько соединений в транзакции могли быть связаны, но текущая версия MySQL не может этого поддерживать.
• Внешние XA-транзакции дороже внутренних из-за задержек связи и возможности выхода из строя самих участников транзакции.
  • Если вы используете транзакции XA в глобальной сети, это обычно вызвано непредсказуемой производительностью сети.
  • Лучше избегать использования транзакций XA, если существует слишком много неконтролируемых факторов, таких как нестабильная сетевая связь или пользователи, которые долго ждут без фиксации. Любая операционная стоимость, которую может совершить транзакция, имеет задержку, потому что влияет сама на себя, но и пусть все участники ждут.
• Существуют и другие способы достижения высокой производительности распределенных транзакций. Например, данные могут быть записаны локально, помещены в очередь, а затем автоматически распределены в более быстрой транзакции меньшего размера. Данные также могут быть отправлены с использованием собственного механизма репликации MySQL. Многие приложения могут полностью отказаться от использования распределенных транзакций.
• Транзакции XA — это способ синхронизации данных между несколькими серверами. Если по какой-то причине вы не можете использовать собственную репликацию MySQL или если производительность не является узким местом, вы можете попробовать ее.

5.12 Кэш запросов

Тип кеша MySQL:

• В некоторых сценариях может быть реализован план выполнения кэшированного запроса, а этапы анализа SQL и генерации плана выполнения могут быть пропущены для одного и того же типа SQL.
• Кэшировать полные результаты запроса SELECT, также известный как «кеш запроса».

Кэш запросов MySQL содержит полные результаты, возвращаемые запросом. Когда запрос попадает в кеш, MySQL немедленно возвращает результат, пропуская этапы синтаксического анализа, оптимизации и выполнения.

• Система кэширования запросов отслеживает каждую таблицу, участвующую в запросе, и если эти таблицы изменятся, все данные кэша запросов, связанные с этой таблицей, станут недействительными. Этот механизм кажется неэффективным, но стоимость внедрения очень мала, что очень важно в очень загруженной системе.
• Кэш запросов полностью прозрачен для приложения. Приложению не нужно заботиться о том, возвращает ли MySQL результат через кеш запроса или фактическое выполнение.

С увеличением размеров современных серверов общего назначения кэширование запросов стало фактором, влияющим на масштабируемость сервера. Это может стать единой точкой раздора ресурсов для всего сервера, а на многоядерных серверах также может привести к зависанию сервера. По умолчанию рекомендуется отключить кеш запросов.Если кеш запросов очень полезен, настройте небольшой объем кеша запросов (например, десятки мегабайт).

5.12.1 Как MySQL попадает в кэш запросов

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

  • При оценке попадания в кеш MySQL не будет анализировать, «нормализовать» или параметризовать оператор запроса, а напрямую использовать оператор SQL и другую исходную информацию, отправленную клиентом. Любая разница в символах, таких как пробелы, комментарии, приведет к промаху кеша.

  • Когда в операторе запроса есть некоторые неопределенные данные, он не будет кэшироваться. Например, запросы, содержащие функции сейчас () или current_date не будут кэшированы.

    • Содержит любые определяемые пользователем функции, хранимые функции, пользовательские переменные, временные таблицы, системные таблицы в библиотеке MySQL или любую таблицу, содержащую привилегии уровня, не будут кэшироваться.
    • Перед проверкой кэша запросов MySQL выполняет проверку без учета регистра, чтобы увидеть, начинается ли оператор SQL с SEL.
    • При проверке кэша запросов MySQL не анализирует оператор SQL, поэтому MySQL не знает, содержит ли оператор запроса функцию, которая возвращает неопределенные данные. Но MySQL запрещает кэширование запроса всякий раз, когда находит часть, которая не может быть кэширована.

    -- 如果希望换成一个带日期的查询，那么最好将其日期提前计算好，而不要直接使用函数
    ... DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) -- Not cacheable!
    ... DATE_SUB('2007-07-14’, INTERVAL 1 DAY) -- Cacheable

  • Ни подзапросы, ни хранимые процедуры не могут использовать кэш запросов, а до версии 5.1 нельзя использовать переменные связывания. Потому что кэш запросов основан на полном операторе SELECT и проверяется только тогда, когда оператор SQL только что получен.

  • Кэш запросов во многих случаях может повысить производительность запросов, но это блокирующая и монопольная операция, кроме того, открытие кеша запросов приведет к дополнительному потреблению чтения и записи:

    • Запросы на чтение должны сначала проверять попадание в кэш перед запуском.
    • Если запрос на чтение можно кэшировать, то после завершения выполнения, если MySQL обнаружит, что запроса нет в кеше запросов, он сохранит результат в кеше запросов, что принесет дополнительное потребление системы.
    • Это также влияет на операции записи, потому что при записи данных в таблицу MySQL должен аннулировать все кэши для соответствующей таблицы. Если кеш запросов очень велик или фрагментирован, эта операция может привести к большому потреблению ресурсов системы (когда для кеша запросов выделено много памяти).

  • Для InnoDB некоторые особенности транзакций ограничивают полезность кэширования запросов. Когда оператор изменяет таблицу в транзакции, MySQL сделает недействительным соответствующий кеш запросов этой таблицы.Фактически, многоверсионная функция InnoDB временно защитит эту модификацию от других транзакций.

    • Прежде чем транзакция будет зафиксирована, связанные запросы этой таблицы не могут быть кэшированы, поэтому все запросы к этой таблице, внутренние или внешние транзакции, могут кэшироваться только после фиксации транзакции. Таким образом, длительные транзакции значительно уменьшат количество попаданий в кэш запросов.

  • Если кеш запросов использует много памяти, инвалидация кеша может стать очень серьезным узким местом.

    • Если в кэше хранится большое количество результатов запроса, вся система может на некоторое время зависнуть во время операции аннулирования кэша. Так как эта операция защищена операцией глобальной блокировки, все запросы, которым необходимо выполнить эту операцию, должны ожидать этой блокировки, и независимо от того, должен ли он определить попадание в кэш или обнаружение аннулирования кеша, необходимо дождаться этой глобальной блокировки.

5.12.2 Как кэш запросов использует память

• Кэш запросов полностью находится в памяти.

  • В дополнение к результатам запроса существует много других данных, связанных с обслуживанием, которые необходимо кэшировать. Эти базовые структуры данных управления и обслуживания требуют около 40 КБ ресурсов памяти.
  • Память, используемая для кэша запросов, разделена на блоки данных, причем блоки данных имеют переменную длину.
    • Каждый блок данных хранит свой собственный тип, размер и сами сохраненные данные, а также указатели на предыдущий и следующий блоки данных. Типы блоков данных: хранение результатов запроса, сохранение сопоставления между запросами и таблицами данных, хранение текста запроса и т. д.

• Идеальный процесс:

  • Когда сервер запускается, он инициализирует память, необходимую для кэша запросов. Этот пул памяти начинается как полный свободный блок, размер которого равен сконфигурированному кэшу запросов за вычетом пространства, занимаемого блокировками структуры данных, используемыми для хранения метаданных.
    • Malloc () Функция для операционной системы для памяти весь процесс создается только в исходном кэше запроса при выполнении один раз.
  • Когда результат запроса необходимо кэшировать, MySQL сначала запрашивает блок данных из большого свободного блока для сохранения результата.
    • Этот блок данных должен быть больше, чем конфигурация параметра query_cache_min_res_unit.Даже если результат запроса намного меньше этого, вам все равно нужно подать заявку как минимум на пространство query_cache_min_res_unit. Поскольку пространство должно быть выделено, когда запрос начинает возвращать результаты, и в это время невозможно предсказать, насколько большим будет результат запроса, MySQL не может точно выделить пространство кэша, которое точно соответствует размеру каждого результата запроса.
    • Этот блок памяти будет как можно меньше (может быть и больше, и подробности здесь приводить не будем), и тогда в нем будет храниться результат. Поскольку необходимо сначала заблокировать блок данных, а затем найти блок данных подходящего размера, условно говоря, выделение блока памяти является очень медленной операцией, и MySQL старается избегать количества раз этой операции.
    • Выделение блоков памяти здесь относится к поиску подходящего блока памяти в списке свободных блоков или освобождению и повторному использованию блоков памяти, которые используются и должны быть устранены. То есть MySQL управляет своей собственной памятью, не полагаясь на управление памятью операционной системы.
  • Если все блоки данных израсходованы, но еще остались данные для хранения, MySQL подаст заявку на новый блок данных (все еще как можно меньше), чтобы продолжить хранить результирующие данные.
  • Когда запрос завершен, если запрошенное пространство памяти все еще осталось, MySQL освободит его и поместит в свободную часть памяти.

  

• Фактический процесс:

  • Предполагая, что средний результат запроса очень мал, сервер одновременно возвращает результаты по двум разным соединениям.После возврата результатов MySQL восстанавливает оставшееся пространство блока данных и обнаруживает, что восстановленный блок данных меньше, чем query_cache_min_res_unit, поэтому его нельзя использовать напрямую. в последующих блоках памяти, используемых в распределении. Учитывая эту ситуацию, выделение блоков данных усложняется.

    • При уменьшении пространства кеша, используемого первым результатом запроса, между результатами второго запроса остается «пробел» — очень маленькое свободное пространство, которое не может быть снова использовано кешем запросов, поскольку оно меньше, чем query_cache_min_res_unit. Такой разрыв называется фрагментацией, и это классическая проблема в управлении памятью и файловой системой.
    • Фрагментация может происходить в ряде ситуаций, например, при аннулировании кеша, когда блоки данных остаются слишком маленькими для использования в последующих кешах.

    

5.12.3 При каких обстоятельствах работает кэш запросов?

• Только когда экономия ресурсов, обеспечиваемая кешем, превышает его собственное потребление ресурсов, это приведет к повышению производительности системы.
• Любая инструкция SELECT, не возвращенная из кеша запросов, называется "промахом кеша". Причины промаха кеша:
  • Оператор запроса не может быть закэширован, возможно, потому что запрос содержит неопределенную функцию или результат запроса слишком велик. Это приведет к увеличению значения состояния Qcache_not_cached.
  • MySQL никогда не обрабатывает этот запрос, поэтому результаты никогда не кэшируются.
  • Результаты запроса были кэшированы ранее, но некоторые кеши необходимо очистить, потому что память кеша запроса заканчивается, или кеш недействителен из-за модификации таблицы данных.
• На сервере много кеш-промахов, но на самом деле большая часть запросов кешируется, должно было произойти что-то вроде этого:
  • Кэш запросов не закончил прогрев. Другими словами, MySQL не кэширует все результаты запроса.
  • Оператор запроса никогда раньше не выполнялся. Если приложение не выполняет оператор запроса повторно, все равно будет много промахов кэша даже после прогрева.
  • Слишком много операций для инвалидации кеша.
    • Фрагментация кеша, нехватка памяти и модификация данных могут привести к аннулированию кеша.
    • Если настроено достаточно места для кеша и query_cache_min_resЕсли настройка единицы также является разумной, то аннулирование кеша должно быть в основном вызвано модификацией данных. доступен через Com* Просмотр данных модификации (в т.ч. Com_update, Com_delete), также можно пройтиQcache_lowmem_prunesчтобы увидеть, сколько сбоев произошло из-за нехватки памяти.
• Метод оценки использования кеша запросов:
  • Теоретически можно решить, открывать ли кеш запросов по эффективности системы, когда кеш включен или выключен. Но трудно оценить, может ли кэширование запросов привести к повышению производительности.
    • SHOW STATUS может предоставить только глобальный индикатор производительности, и трудно оценить улучшение производительности.
  • Запросы, которые потребляют много ресурсов, обычно очень подходят для кэширования.
    • Некоторые запросы на агрегированные вычисления, такие как COUNT()
    • Сложные операторы SELECT, такие как многотабличное JOIN, также требуют сортировки и разбиения на страницы.Этот тип запроса потребляет много денег при каждом выполнении, но возвращаемый набор результатов невелик, что очень удобно для кэширования запросов. Однако следует отметить, что UPDATE, DELETE и INSERT в таблице задействованы гораздо меньше, чем SELECT.
  • Непосредственными данными, позволяющими судить о том, является ли запрос действительным, является частота совпадений, которая представляет собой отношение результатов, возвращаемых кэшем запросов, к общему запросу.
    • Когда MySQL получает запрос SELECT, он либо увеличивает значение Qcache_hints, либо значение Com_select.
    • О коэффициенте попаданий в кэш запросов трудно судить. Насколько хорош процент попаданий? До тех пор, пока повышение эффективности, обеспечиваемое кэшированием запросов, больше, чем его потребление, даже процент попаданий в 30 % — это нормально; то, какие запросы кэшируются, также имеет значение, например, сам кэшированный запрос потребляет много денег, даже если частота попаданий в кэш низкая, это приемлемо
  • При рассмотрении частоты попаданий в кеш обычно необходимо учитывать дополнительное потребление, вызванное аннулированием кеша.
    • Крайний метод состоит в том, чтобы выполнить тест только на запрос для определенной таблицы, и все запросы попадают в кеш, а другая такая же таблица только модифицируется. В настоящее время частота попаданий кэша запросов составляет 100 %, но, поскольку это приведет к дополнительному потреблению операций обновления, кэш запросов не обязательно приведет к общему повышению эффективности. Здесь все операторы обновления будут выполнять проверку аннулирования кеша, и результаты проверки будут одинаковыми, что приведет к дополнительной трате ресурсов в системе.
  • В MySQL, если операции обновления смешиваются с операциями кэширования, преимущества кэширования запросов трудно измерить.
    • Если кешированный результат не используется какой-либо другой инструкцией SELECT до того, как он станет недействительным, операция кэширования является пустой тратой времени и памяти.
    • В этом можно убедиться, взглянув на относительные значения Qcache_select и Qcache_inserts. Если каждая операция запроса является промахом кеша, а затем результат запроса необходимо поместить в кеш, два значения должны быть похожими. Таким образом, после прогрева кеша в лучшем случае Query_inserts будет намного меньше, чем Query_select.
  • Ни частота попаданий, ни "соотношение INSERT и SELECT" не могут интуитивно отражать эффективность кеша.Есть еще один интуитивно понятный способ: соотношение попаданий и записей, то есть соотношение Qcache_hints и Qcache_inserts
    • Как правило, кэширование запросов эффективно, когда это соотношение больше 3:1, предпочтительно 10:1.
    • Если приложение не достигает этого соотношения, рассмотрите возможность отключения кеша запросов, если только с помощью точных расчетов нельзя узнать, что повышение производительности, вызванное попаданием, больше, чем стоимость аннулирования кеша, а кеш запросов не является узким местом приложения. система.
  • Наблюдайте за фактическим использованием памяти кэша запросов, чтобы определить, нужно ли уменьшить или увеличить объем кэша запросов.
    • Если кеш запросов составляет порядка десятков мегабайт, это потенциально рискованно. (Это связано с аппаратным обеспечением и размером системы)
  • Его необходимо рассматривать вместе с другими кэшами системы, такими как пул кэшей InnoDB или кэш индексов MyISAM.
  • Лучший способ оценить эффективность кэширования запросов — определить, увеличивается или уменьшается время, затрачиваемое на определенный тип запроса.

5.12.4 Как настроить и поддерживать кэш запросов ·

• Конфигурация:

  • query_cache_type: открывать ли кеш запросов. Может быть ВКЛ, ВЫКЛ или ТРЕБОВАНИЕ. DEMAND означает, что в кэш запросов помещаются только те операторы, которые явно указывают SQL_CACHE в операторе запроса. Эта переменная может быть на уровне сеанса или на глобальном уровне.
  • query_cache_size: общий объем памяти, используемый кешем запросов, в байтах, должен быть целым числом, кратным 1024, иначе данные, фактически выделенные MySQL, могут отличаться.
  • query_cache_min_res_unit: минимальная единица при выделении блоков памяти в кэше запросов.
  • query_cache_limit: Максимальный результат запроса, который MySQL может кэшировать.
    • Если результат запроса больше этого значения, он не будет кэшироваться. Поскольку кеш запросов начинает попытки кэшировать данные, когда данные сгенерированы, MySQL знает, превышает ли результат запроса ограничение, только когда возвращаются все результаты.
    • При превышении MySQL увеличивает значение состояния Qcache_not_cached и удаляет результат из кэша запросов. Если вы заранее знаете, что таких ситуаций много, рекомендуется добавить SQL_NO_CACHE в оператор запроса, чтобы избежать дополнительного потребления, вызванного кэшированием запросов.
  • query_cache_wlock_invalidate: если таблица данных заблокирована другими соединениями, следует ли по-прежнему возвращать результаты из кеша запросов. По умолчанию ВЫКЛ.

• уменьшить фрагментацию

  • Невозможно полностью избежать фрагментации, но правильный query_cache_min_res_unit может уменьшить потери памяти, вызванные фрагментацией.
    • Установка подходящего значения может сбалансировать размер каждого блока данных и количество запросов к блоку памяти при каждом сохранении результата, т. е. сбалансировать потери памяти и потребление ЦП.
    • Если это значение слишком мало, меньше места будет потрачено впустую, но это приведет к более частым операциям запроса блоков памяти. Если их слишком много, будет большая фрагментация.
  • Оптимальный размер этого параметра напрямую связан со средним размером результатов запроса для приложения.
    • Средний размер кэша для одного запроса можно рассчитать, разделив фактическое потребление памяти (query_cache_size — Qcache_free_memory) на Qcache_queries_in_cache.
    • Фрагментация и повторное выделение блоков памяти могут быть неизбежны, если размер результата запроса очень неравномерный.
    • Если вы обнаружите, что кэшируется очень большой результат, вы можете ограничить максимальный результат запроса, который может быть кэширован, с помощью параметра query_cache_limit.
  • Фрагментацию можно наблюдать через параметр Qcache_free_blocks, который отражает количество блоков памяти в кеше запроса.
    • Если Qcache_free_blocks достигает значения Qcache_total_blocks/2, значит, кеш запросов имеет серьезные проблемы с фрагментацией.
    • Если свободных блоков по-прежнему много, а значение состояния Qcache_lowmem_prunes продолжает увеличиваться, это означает, что кеш-результат запроса удаляется преждевременно из-за фрагментации.
  • Дефрагментацию можно выполнить с помощью FLUSH QUERY CACHE. Эта команда переупорядочит все кэши запросов и соберет все свободное пространство в одну область кэша запросов.
    • Доступ ко всем кешам запросов осуществляется, в течение этого времени никакие другие соединения не могут получить доступ к кешу запросов, что приводит к зависанию сервера на определенный период времени. Поэтому рекомендуется, чтобы размер кэша запросов был достаточно мал.
    • Очистить кеш можно с помощью RESET QUERY CACHE.

• Улучшить использование кеша запросов

  • Если в кэше запросов больше нет проблем с фрагментацией, но частота попаданий по-прежнему низкая, это также может быть вызвано тем, что объем памяти кэша запросов слишком мал. Если MySQL не может кэшировать результаты для нового запроса, он удалит старый кеш запросов.
  • При удалении старых кешей запросов значение состояния Qcache_lowmem_prunes увеличивается. Если это значение быстро увеличивается, это может быть вызвано следующими двумя причинами:
    • Если свободных блоков осталось много, то виновником может быть фрагментация
    • Если в это время нет свободных блоков, это означает, что при таком системном давлении выделенное пространство кэша запросов недостаточно велико. Вы можете увидеть, сколько неиспользуемой памяти осталось, проверив значение состояния Qcache_free_memory.

  

5.12.5 InnoDB и кэш запросов

• Поскольку InnoDB имеет собственный механизм MVVC, InnoDB будет контролировать, можно ли использовать кэш запросов в транзакции, а InnoDB будет контролировать как операции чтения, так и записи в кэш запросов.
  • Может ли транзакция получить доступ к кэшу запросов, зависит от текущего идентификатора транзакции и наличия блокировки соответствующей таблицы данных. Словарь данных в памяти каждой таблицы InnoDB хранит идентификатор транзакции.Если текущий идентификатор транзакции меньше идентификатора транзакции, доступ к кешу запросов невозможен.
  • Если на таблице есть какие-либо блокировки, то любой запрос к этой таблице не может быть кэширован.
• Кэш запросов в InnoDB:
  • Все транзакции, размер которых превышает счетчик таблицы, могут напрямую использовать (чтение и запись) кэш запросов.
  • Счетчик таблицы не обновляется непосредственно до идентификатора транзакции операции блокировки таблицы, а обновляется до идентификатора системной транзакции. Следовательно, будет обнаружено, что последующие операции обновления самой транзакции не могут читать и изменять кэш запросов.
• Хранение кеша запросов, операции извлечения и аннулирования выполняются на уровне MySQL, и InnoDB не может обойти или задержать это поведение.
  • Но InnoDB может явно указать MySQL в транзакции, когда он должен аннулировать кэш запросов для таблицы. Это необходимо, когда есть ограничения по внешнему ключу, например, у определенного SQL есть ON DELETE CASCADE.
• В принципе, в архитектуре InnoDB MVVC кэширование запросов может использоваться, когда определенные модификации не влияют на другие транзакции, читающие согласованные данные. Однако реализовать это будет очень ответственно, InnoDB упростил его, чтобы все транзакции с блокирующими операциями не использовали какой-либо кеш запросов, это ограничение не является обязательным.

5.12.6 Общая оптимизация кэша запросов

• Замена одной большой таблицы несколькими маленькими таблицами хороша для кэширования запросов. Такой дизайн позволит стратегии отказов работать с более подходящей степенью детализации. Конечно, не позволяйте этому принципу слишком сильно влиять на дизайн, ведь все остальные преимущества легко компенсируются.
• При пакетной записи необходимо выполнить только одну инвалидацию кеша, поэтому это более эффективно, чем одиночная запись. Будьте осторожны, не делайте отложенную запись и пакетную запись одновременно, иначе сервер может зависнуть на долгое время.
• Поскольку объем кэш-памяти слишком велик, сервер может зависнуть во время операций устаревания. Простое решение — контролировать размер пространства кеша или просто отключить кеш запросов.
• Вы не можете управлять кэшированием запросов на уровне базы данных или таблицы, но вы можете контролировать необходимость кэширования оператора SELECT с помощью SQL_CACHE и SQL_NO_CACHE. Вы также можете изменить query_cache_type на уровне сеанса, чтобы управлять кэшированием запросов.
• Для приложений с интенсивной записью прямое отключение кэша запросов может повысить производительность системы. Отключение кеша запросов удаляет все связанные накладные расходы, например, установка для query_cache_size значения 0
• Из-за соперничества за семафор мьютекса иногда может быть полезно отключить кэш запросов непосредственно для приложений с интенсивным чтением.
• Если вы не хотите, чтобы все запросы попадали в кэш запросов, вы можете установить для query_cache_type значение DEMAND, а затем добавить SQL_CACHE к запросу, который вы хотите кэшировать.

5.12.7 Альтернативы кэшированию запросов

Кэширование запросов работает по принципу, что самый быстрый способ выполнить запрос — не выполнять его. Но запрос все еще должен быть отправлен на серверную сторону, а серверная сторона все еще должна проделать небольшую работу. Таким образом, его можно кэшировать непосредственно на стороне клиента.

5.13 Резюме

• Секционированная таблица. Секционированная таблица — это упрощенная и простая стратегия индексирования, подходящая для сценариев фильтрации с большими объемами данных. Наиболее подходящий сценарий — выполнить полное сканирование таблицы на нескольких разделах, когда нет подходящего индекса, или только один раздел и индекс являются горячими точками, и и раздел, и индекс могут находиться в памяти; ограничить количество одиночных разделов таблицы более 150, и обратите внимание на некоторые детали, которые делают невозможным фильтрацию разделов Таблица разделов не имеет преимуществ для программ с одной записью, и вам нужно обратить внимание на производительность этого типа запроса.
• Представления: для сложных запросов к нескольким таблицам использование представлений иногда может значительно упростить проблему. Когда в представлении используется временная таблица, условие WHERE нельзя протолкнуть к каждой конкретной таблице, а также нельзя использовать какие-либо индексы, поэтому на производительность таких запросов следует обращать особое внимание. Для обхода подходит представление.
• Внешние ключи: ограничение внешнего ключа наложит ограничения на MySQL, что будет более производительным для сценариев, в которых необходимо поддерживать внешние ключи. Однако это также приведет к дополнительной сложности и дополнительному потреблению индекса, а также увеличит взаимодействие между несколькими таблицами, что приведет к большему количеству блокировок и конкуренции в системе. Внешние ключи можно рассматривать как вау-функцию для обеспечения целостности системы, но если проектируется высокопроизводительная система, внешние ключи могут быть раздуты. Многие люди не используют внешние ключи, когда их больше заботит производительность системы, а поддерживают ее через приложение.
• хранимая процедура
• связать переменную
• плагин
• Набор символов. Набор символов — это сопоставление между байтами и символами, а сопоставление относится к методу сортировки набора символов. Многие люди используют Latin1 (набор символов по умолчанию, действительный для английского и некоторых европейских языков) или UTF-8. Если вы используете UTF-8, вам нужно быть осторожным при использовании временных таблиц и буферов: MySQL выделяет пространство для хранения в соответствии с максимальным размером в три байта на символ, что может занимать больше памяти или места на диске. Будьте осторожны, чтобы сопоставить набор символов с конфигурацией набора символов MySQL, иначе некоторые индексы могут работать неправильно из-за преобразования набора символов.
• полный текстовый указатель
• Транзакции XA: мало кто использует функцию транзакций XA MySQL. Если вы действительно не понимаете значение параметра innodb_support_xa, не изменяйте значение этого параметра, не только необходимо установить этот параметр при использовании транзакций XA. InnoDB и двоичный журнал также необходимо координировать с помощью транзакций XA, чтобы обеспечить последовательное восстановление данных в случае сбоя системы.
• Кэш запросов: когда один и тот же запрос выполняется повторно, кеш запросов может немедленно вернуть результаты без необходимости повторного выполнения в базе данных. Как правило, запросы к кешу в среде с высоким параллелизмом могут привести к снижению производительности системы или даже зависанию. Если вы должны использовать кеш запросов, не устанавливайте слишком много памяти и используйте его только тогда, когда есть явная выгода. Кэш запросов — это очень удобный кеш, который полностью прозрачен для приложения и не требует никакого дополнительного кодирования, но если вы хотите более эффективный кеш запросов, рекомендуется использовать другие схемы кеширования, такие как memacched.
• ​