Оптимизация использования MySQL в основном касается оптимизации структуры таблиц базы данных, использования соответствующих индексов и оптимизации запросов.

Оптимизация структуры библиотечных таблиц

поговорим о типах данных

Тип строки

Существует много типов строк, представляющих строки в MySQL, среди которых наиболее распространенными являются Char и VarChar, BLOB и TEXT, а также ENUM, которые можно оптимизировать. Нам нужно распознавать различия в хранении и использовании разных типов данных, а затем разумно использовать их для достижения оптимизации.

Для ознакомления с типами данных в MySQL вы можете обратиться к другому моему сообщению в блоге:Общие операции MySQL

Чар и ВарЧар

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

VARCHAR — это тип переменной длины, который экономит больше места, чем CHAR, но VARCHAR склонен к фрагментации при изменении данных, поэтому обычно используется в качестве типа данных для данных, которые изменяются нечасто. VARCHAR необходимо использовать один или два дополнительных байта для записи длины строки.Если максимальная длина столбца меньше или равна 255 байтам, для хранения длины используется дополнительный байт, в противном случае используются два байта. Например, для столбца VARCHAR(10) требуется 11 байт дискового пространства, а для столбца VARCHAR(1000) — 1002 байта.

Обратите внимание, что в версиях 5.0 или более поздних MySQL сохраняет конечные пробелы при сохранении или извлечении типов данных VARCHAR, но в версиях 4.1 или более ранних версиях MySQL удаляет конечные пробелы при сохранении или извлечении типов данных VARCHAR, точно так же, как CHAR.

БЛОБ и ТЕКСТ

BLOB — это SMALLBLOB, а TEXT — это SMALLTEXT. BLOB и TEXT являются строковыми типами данных, предназначенными для хранения больших данных, и хранятся в двоичном и строковом форматах соответственно.

Используйте ENUM вместо строкового типа

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

Например:create table enum_test(e enum('apple','banana','pear'));insert into enum_test(e) values('apple'),('banana'),('pear');Затем данные, которые мы вставляем, фактически сохраняются в таблице как целое число.

Тип номера

MySQL может указывать ширину для целочисленных типов, таких как INT(1), INT(20), но для большинства приложений это бессмысленно. Ширина целочисленного типа, указанная MySQL, используется только для установки количества символов, используемых некоторыми клиентами MySQL для отображения.Для хранения и вычисления INT(1) и INT(20) не ограничивают допустимый диапазон значений. два Все типы одинаковы.

тип даты и времени

ДатаВремя и Отметка Времени

DATETIME и TIMESTAMP — это два типа даты, эти два типа хранят данные в одном и том же формате в MySQL (оба yyyy-MM-dd HH:mm:ss), но между ними есть различия.

DATETIME может содержать широкий диапазон значений от 1001 до 9999 с точностью до секунд. MySQL использует 8 байт для хранения значений, содержащихся в типе данных DATETIME. По умолчанию MySQL отображает значения DATETIME в сортируемом однозначном формате.

TIMESTAMP содержит количество секунд, прошедших с полуночи 1 января 1970 года (по Гринвичу), что совпадает с отметкой времени UNIX. TIMESTAMP использует только 4 байта пространства для хранения, поэтому он может представлять меньший диапазон времени, чем DATETIME, и может представлять только период с 1970 по 2038 год. TIMESTAMP также имеет специальные атрибуты, которых нет у DATETIME.По умолчанию, если значение первого столбца TIMESTAMP не указано при вставке, MySQL установит значение столбца на текущее время.

использовать подходящий индекс

Оптимизация индекса

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

база индекса

В MySQL индексы реализованы на уровне механизма хранения, а не на уровне сервера. Структура индекса в Mysql включает в себя: индекс B-дерева, хэш-индекс, индекс пространственных данных (индекс R-дерева), полнотекстовый индекс и другие индексные структуры.Различные механизмы хранения реализуют вышеуказанные структуры индексов по-разному, и не все механизмы хранения. эти 5 типов структуры индекса.

Тип индекса:

В MySQL существует пять основных типов индексов:

Обычный индекс: Самый простой индекс без каких-либо ограничений. Индекс типа BTREE по умолчанию в MyIASM. какALTER TABLE article ADD INDEX index_title_name ON title(100);,CREATE INDEX index_name ON table(column(100))или определить индекс непосредственно при создании таблицыindex index_title_name(title(100))
Уникальный индекс: значение индексированного столбца может быть нулевым. Аналогичен обычному индексу, за исключением того, что значение индексируемого столбца должно быть уникальным. какALTER TABLE article ADD UNIQU index_title_name ON title(100);,CREATE UNIQUE INDEX index_name ON table(column(100))Или определите индекс непосредственно при создании таблицыUNIQUE index_title_name(title(100))
Полнотекстовое индексирование: в основном используется для поиска ключевых слов в тексте, а не для прямого сравнения со значениями в индексе. Доступно только для таблиц MyISAM. Для больших данных создание полнотекстовых индексов занимает много времени и места. какALTER TABLE article ADD FULLTEXT index_content(content),CREATE FULLTEXT INDEX index_content ON article(content)иFULLTEXT (content).
Индекс первичного ключа: это специальный уникальный индекс, который не допускает нулевых значений.
Крайний левый индекс (комбинированный индекс): комбинированный индекс может лучше повысить эффективность MySQL, а крайний левый индекс следует принципу «крайний левый индекс». При создании составного индекса наиболее часто используемые (частотные) столбцы должны располагаться слева и в порядке убывания.

индексный метод:

Запросы, которые могут использовать индексы B-дерева:

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

Ограничения использования индексов B-tree

В многостолбцовом индексе поиск должен начинаться с крайнего левого столбца индекса, иначе индекс нельзя будет использовать.
В многостолбцовом индексе нельзя пропускать столбцы в индексе. Например, объединенный индекс, состоящий из трех столбцов, не может использовать только первый столбец и третий столбец для запроса и пропускать второй столбец.
операции not in и не могут использовать индексы
Если в запросе есть запрос диапазона столбца, все столбцы справа от него не могут использовать индекс.

Особенности хэш-индекса:

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

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

Ограничения использования хэш-индексов:

Хэш-индексы необходимо искать дважды.
Хэш-индексы нельзя сортировать.
Хэш-индексы не поддерживают поиск по частичному индексу или поиск по диапазону.
Расчет хэша в индексе хэша может привести к конфликту хэшей.

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

Индекс покрытия:

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

Оптимизируйте кеш и сократите дисковые операции ввода-вывода.
Уменьшите случайный ввод-вывод и замените случайный ввод-вывод последовательным вводом-выводом.
Вторичных запросов к индексам первичных ключей Innodb можно избежать.

Индексы в механизме хранения InnoDB

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

Индекс в традиционном смысле относится к индексу дерева B+, который в настоящее время является наиболее часто используемым и эффективным индексом для поиска в системах реляционных баз данных. Найдите страницу, на которой находится искомая строка данных, с помощью индекса дерева B+, а затем база данных считывает страницу в память, а затем выполняет поиск в памяти, чтобы найти соответствующие данные.

InnoDB использует блокировки строк, которые блокируются только при изменении строки. Использование индексов может сократить количество блокируемых данных в процессе запроса, увеличить параллелизм обработки данных и повысить производительность базы данных.

Примечания по использованию индексов

Убедитесь, что при поиске данных в MySQL количество соответствующих столбцов в таблице не зависит. Независимый столбец заключается в том, что столбец индекса не может быть частью выражения и не может быть параметром функции. то есть не разрешеноselect id from article where id+1=5и так далее, иначе индекс будет недоступен.
Индексирование столбцов с длинными символами сделает индекс большим и медленным. В это время следует использовать индекс префикса, то есть некоторые символы в начале столбца выбираются в качестве индекса, и выбор индекса префикса также должен обеспечивать разумную избирательность индекса (чем ближе к 1, тем лучше). ).
Если вам не нужно учитывать потребности сортировки и группировки, в объединенном индексе вы должны поместить индекс с самой высокой селективностью в передний столбец индекса, поместить часто используемые столбцы в передний столбец индекса, и поместите столбцы с меньшей шириной в передний столбец индекса Верхний столбец индекса.
использоватьpt-duplicate-key-checker h=127.0.0.1Найдите повторяющиеся и избыточные индексы, а затем удалите повторяющиеся и избыточные индексы.

Оператор запроса преобразования SQL

Процесс и статус подключения к MySQL

Статус подключения к MySQL

Протокол связи между клиентом и сервером MySQL является "полудуплексным". В любой момент либо сервер отправляет данные клиенту, либо клиент отправляет данные на сервер, и эти два действия не могут происходить одновременно. Для каждого момента вы можете передать командуshow full processlistЧтобы просмотреть статус текущего соединения mysql (столбец Command представляет текущий статус).

Состояние MySQL следующее:

Сон: поток ожидает, пока клиент отправит новый запрос

Запрос: поток выполняет запрос или отправляет результаты клиенту.

Заблокировано: на уровне сервера MySQL этот поток ожидает блокировки таблицы.

Результат сортировки: поток сортирует набор результатов.

Копирование во временную таблицу [на диск]: поток выполняет запрос и копирует набор результатов во временную таблицу.Это состояние либо выполняет операцию GROUP BY, либо операцию сортировки файлов, либо операцию UNION. Если в статусе есть флаг на диске, это означает, что MySQL помещает на диск временную таблицу в памяти.

Анализ и статистика. Поток собирает статистику механизма хранения и создает план выполнения запроса.

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

Процесс подключения к MySQL

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

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

В процессе выполнения запроса MySQL выше сервер предоставил некоторые меры оптимизации SQL, и нам также необходимо правильно понимать и использовать эти меры оптимизации.

Используйте оператор запроса объяснения+sql, чтобы проверить эффективность запроса SQL.
mysql использует оптимизатор на основе затрат. использоватьshow status like 'last_query_cost'Вы можете запросить значение last_query_cost текущего сеанса, чтобы узнать стоимость текущего запроса, рассчитанную mysql, и mysql оценит и получит план выполнения с наименьшими затратами.
MySQL поставляется с «вложенным циклом», который может оптимизировать большинство наших запросов и настроить корреляцию связанных таблиц для достижения эффективных запросов.

Оптимизируйте доступ к данным

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

Чтобы оптимизировать объем доступа к данным, вы можете использовать:

В операторе SQL-запроса разумно используйте limit, чтобы контролировать количество строк.
В запросе SQL, связанном с несколькими таблицами, запрашивайте только столбцы требуемой таблицы, старайтесь не использовать «выбрать *»
С помощью сторонней системы кэширования часто запрашиваемые данные кэшируются.
Если запросу необходимо просмотреть большой объем данных, но он возвращает лишь небольшое количество строк, вы можете использовать сканирование покрытия индекса, чтобы поместить строки, которым нужны данные, в индекс.

Рефакторинг запроса

Разбивайте большие запросы на более мелкие. Специально для удаления ненужных данных, вообще говоря, удаляйте небольшое количество данных пакетами, что может значительно сократить время удержания блокировок базы данных.
Разумная декомпозиция связанных запросов. Разложение запроса ассоциации на запрос одной таблицы может уменьшить конкуренцию блокировок; в то же время результаты запроса одной таблицы могут быть связаны на прикладном уровне, что может реализовать разделение базы данных и достичь высокой производительности и эффективности. масштабируемость. Кроме того, эффективность можно повысить за счет кэширования повторно запрашиваемых данных.
Некоторые подзапросы в MySQL неэффективны (например, подзапросы с использованием in), мы должны использовать оператор объяснения для проверки стоимости текущего запроса, а затем решить, должны ли мы использовать внутренние соединения или левые (правые) внешние соединения для переписать подзапросы MySQL in() Inquire. Но когда нам нужно вернуть некоторые столбцы в таблице, мы можем использовать подзапрос ключевого слова exists в запросе ассоциации с несколькими таблицами, что будет более эффективно. - Примечание перед MySQL 5.6
При использовании ключевого слова union для запросов SQL, если есть такие операции, как ограничение объема данных и сортировка, эти ограничения следует использовать в каждом операторе SQL.
Используйте собственный эффект сортировки первичного ключа и ключевое слово limit вместо ключевых слов max и min, чтобы получить максимальные и минимальные значения.
Когда MySQL необходимо выполнить разбиение по страницам, это достигается с помощью ограничения плюс смещение и добавления соответствующего предложения order by, чтобы можно было полностью использовать столбцы с индексами. Кроме того, при пейджинге, если смещение слишком велико, следует использовать сканирование покрытия индекса.
При выполнении связанного запроса добавьте индекс к столбцам предложений on и using и обратите внимание на порядок ассоциации, вы должны добавить индекс к таблице в главе 2 для повышения эффективности.
Убедитесь, что выражения предложений group by и order by включают столбцы только в одной таблице, и только тогда можно оптимизировать процесс с помощью индексов.