MySQL (2) Обычно используемая оптимизация MySql

база данных SQL
MySQL (2) Обычно используемая оптимизация MySql

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

Во-первых, общая древовидная структура:

  • Бинарное дерево: каждый родительский узел больше левого дочернего узла и меньше правого дочернего узла.

  • Сбалансированное бинарное дерево: на основе бинарного дерева разница высот поддерева каждого узла не превышает 1.

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

  • B+Tree: нелистовые узлы хранят только ключи, а конечные узлы хранят ключи и данные.Листовые узлы могут содержать указатель на другой конечный узел для ускорения последовательного доступа.

2. Механизм хранения MySql

  • Механизм хранения InnoDB Основная часть текущего механизма хранения MySQL, механизм хранения InnoDB поддерживает транзакции, поддерживает блокировки строк, поддерживает чтение без блокировки и поддерживает внешние ключи. 

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

И InnoDB, и MyISAM используют структуру B+Tree для индексов, но методы реализации различаются:

  1. В MyISAM поле данных листового узла хранит не данные, а адрес записи данных, поэтому алгоритм поиска индекса в MyISAM заключается в том, чтобы сначала искать индекс в соответствии с алгоритмом поиска B+Tree.Если указанный ключ существует, извлеките данные поля данных значение, а затем прочитайте соответствующую запись данных со значением поля данных в качестве адреса.

  2. В InnoDB есть кластеризованные и некластеризованные индексы (вторичные индексы):

  • Кластеризованный индекс: нелистовые узлы хранят , а point — это указатель на следующий слой. Листовой узел сохраняет информацию об этой строке, поэтому данные можно быстро получить через индекс первичного ключа. В InnoDB первичный ключ обычно представляет собой кластеризованный индекс. Если быть точным, кластеризованный индекс — это не отдельный тип индекса, а метод хранения данных. Это означает, что индекс B+tree и строки данных сохраняются в одной и той же структуре. В innoDB, если пользователь не задает индекс первичного ключа, для его замены будет случайным образом выбран уникальный ненулевой индекс.Если такого индекса нет, первичный ключ будет неявно определен как неявный кластеризованный индекс.
  • Некластеризованный индекс: конечный узел некластеризованного индекса не хранит данные, но хранит соответствующий ключ кластеризованного индекса строки данных, то есть первичный ключ. При запросе данных по некластеризованному индексу для помощи механизм хранения InnoDB будет проходить по некластеризованному индексу, чтобы найти первичный ключ, а затем найдет полную строку данных, записанных кластеризованным индексом первичного ключа.

3. Резюме

Причины использования B+Tree в качестве структуры индекса:

  • Каждый узел B-дерева содержит не только значение ключа данных, но и значение данных. Место для хранения каждой страницы ограничено.Если данные данных велики, количество ключей, которые могут быть сохранены каждым узлом (т. е. страницей), будет очень маленьким.Когда объем хранимых данных велик, он также будет приводят к B- Глубина дерева велика, что увеличивает количество дисковых операций ввода-вывода во время запроса, тем самым влияя на эффективность запроса. В B+Tree все узлы записи данных хранятся на конечных узлах того же слоя в порядке размера значения ключа, а на неконечных узлах хранится только информация о значении ключа, что может значительно увеличить количество значений ключа. хранится в каждом узле. , уменьшите высоту B+Tree.

В-четвертых, обычно используемая оптимизация MySql

  1. сбой индекса

  • Нравятся запросы, начинающиеся с %

  • (Не ноль, не вроде) не будет использовать индекс?

  • Если в условии есть или, оно не будет использоваться, даже если есть какие-то условия с индексом

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

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

  • Индекс объединения не соответствует принципу крайнего левого сопоставления

  1. **оптимизация SQL**

  • Разложите связанный запрос: ассоциация (объединение) обрабатывается в приложении, и выполняется простой sql.Преимущество состоит в том, что разложенный sql обычно прост и фиксирован, а кеш mysql можно лучше использовать. Это также может уменьшить конкуренцию за блокировку.

  • Избегайте использования * в предложении SELECT, так как это делается путем запроса словаря данных, что означает, что это займет больше времени, а оператор SQL недостаточно интуитивно понятен.

  • О пределе При использовании предела 2000, 10, mysql будет сканировать данные о смещении (2000 недопустимых запросов) и брать только последние 10, постарайтесь найти способ избежать этого.

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

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

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

  • Чтобы удалить все данные таблицы, используйте TRUNCATE вместо DELETE, вот только: DELETE удаляет одну запись за другой, а Truncate удаляет всю таблицу и сохраняет структуру таблицы, что быстрее, чем DELETE

  • Используйте внутренние функции для повышения эффективности SQL

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

  • Используйте >= вместо > , неэффективно: > 3 сначала находит записи с = 3 и сканирует до первой записи больше 3. Эффективно: >= 4 перейти непосредственно к первой записи, равной 4

  • Замените ИЛИ на В

  1. Оптимизация структуры базы данных
  • Оптимизация структуры таблицы: максимально используйте ненулевые ограничения для полей, потому что трудно оптимизировать запросы для столбцов, содержащих нулевые значения в MySql, а значения NULL сделают индексы и статистику индексов очень сложными.
  • Сравнение числовых типов намного эффективнее, чем сравнение строковых типов.
  • Попробуйте использовать TIMESTAMP вместо DATETIME (эффективность запроса)
  • Не имеют слишком большого количества одиночного поля таблицы, рекомендуется в пределах 20
  • Разумно добавлять лишние поля
  • вертикальный разделенный стол горизонтальный разделенный стол

Справочная запись в блоге:blog.CSDN.net/U013235478/…

Категории