Оптимизация таблиц MySQL
Существует много механизмов хранения баз данных MySQL: MyISAM、InnoDB、BDB（BerkeleyDB）、Merge、Memory（Heap）、Example、Federated、
Archive, CSV, Blackhole, MaxDB и т. д.
Язык SQL состоит из четырех частей: определение данных (DDL), обработка данных (DML), управление данными (DCL) и запрос данных (DQL).
Обработка данных: выбор, вставка, обновление, удаление,
Контроль данных: предоставить, отозвать
Какие типы ячеек есть и для чего
Сначала немного теории о птимизации базы данных, включая разумный уровень изоляции транзакций, оптимизации операторов SQL и оптимизации индекса.
Индекс - это специальный файл (индекс в таблице данных InnoDB является компонентом табличного пространства), они содержат указатели ссылок на все записи в таблице данных.
Единственная задача обычного индекса (индекса, определяемого ключевым словом KEY или INDEX) - ускорить доступ к данным.
Обычный индекс позволяет столбцу индексированных данных содержать повторяющиеся значения. Если вы можете быть уверены, что столбец данных будет содержать только значения, которые отличаются друг от друга, вы должны использовать ключевое слово UNIQUE, чтобы определить его как уникальный индекс при создании индекса для этого столбца данных. Другими словами, уникальный индекс может гарантировать уникальность записей данных.
Первичный ключ - это специальный уникальный индекс. В таблице может быть определен только один индекс первичного ключа. Первичный ключ используется для уникальной идентификации записи и создается с помощью ключевого слова PRIMARY KEY.
Индекс может охватывать несколько столбцов данных, например индекс INDEX (columnA, columnB), который является объединенным индексом.
Индекс может значительно увеличить скорость запроса данных, но он снизит скорость вставки, удаления и обновления таблиц, поскольку при выполнении этих операций записи необходимо работать с индексным файлом.
Транзакция - это набор упорядоченных операций с базой данных как единое целое. Если все операции в группе выполнены успешно, транзакция считается успешной, даже если только одна операция завершилась неудачно, транзакция не удалась. Если все операции завершены, транзакция фиксируется, и ее модификации будут действовать на все другие процессы базы данных. Если операция завершится неудачно, транзакция будет отменена, и влияние всех операций транзакции будет отменено. Четыре характеристики ACID - атомарность, изоляция, согласованность и долговечность.
Какова цель индексации в MySQL?
Быстрый доступ к определенной информации в таблице данных для повышения скорости извлечения
Используя предложения группировки и сортировки для извлечения данных, вы можете значительно сократить время на группировку и сортировку в запросе.
Какое негативное влияние оказывает индекс на систему баз данных?
Для создания и обслуживания индексов требуется время. Это время увеличивается по мере увеличения объема данных; индексы должны занимать физическое пространство, не только таблицы должны занимать пространство данных, но и каждый индекс должен занимать Физическое пространство; индексы также должны динамически поддерживаться при добавлении, удалении или изменении таблиц, что снижает скорость обслуживания данных.
Каковы принципы индексации таблиц данных?
Создайте индекс для наиболее часто используемого поля, чтобы сузить область запроса.
Создавайте индексы для часто используемых полей, которые необходимо отсортировать
При каких обстоятельствах нецелесообразно создавать индекс?
Для столбцов, которые редко участвуют в запросе, или столбцов с множеством повторяющихся значений создавать индекс нецелесообразно.
Для некоторых специальных типов данных нецелесообразно создавать индексы, например текстовые поля (текст) и т. Д.
Разница между MyISAM и InnoDB в базе данных MySQL.
Самая важная особенность, которая отличает ее от других баз данных, - это подключаемый модуль хранения таблиц. Помните: механизм хранения основан на таблицах, а не на базах данных.
MyISAM - это нетранзакционный механизм хранения; подходит для приложений с частыми запросами; блокировки таблиц, отсутствие взаимоблокировок; подходит для небольших данных, небольшого параллелизма. Это : зрелый, стабильный, простой в управлении и быстрый для чтения. Некоторые функции не поддерживаются (транзакции и т. Д.), Блокировки на уровне таблицы.
Innodb - это механизм хранения, поддерживающий транзакции; он подходит для приложений с большим количеством операций вставки и обновления; если дизайн разумен, это блокировка строки (самая большая разница заключается в уровне блокировки); он подходит для больших данных и большого параллелизма. Поддерживает такие функции, как транзакции, внешние ключи и блокировка строк данных. Он занимает много места и не поддерживает полнотекстовое индексирование.
Разница между InnoDB и MyISAM?
Механизм хранения InnoDB: ориентированный в основном на приложения OLTP (онлайн-обработка транзакций, обработка онлайн-транзакций), это первый механизм хранения, который полностью поддерживает транзакции ACID (первый механизм хранения, поддерживающий транзакции, BDB был прекращен).
· Конструкция блокировки строк, поддержка внешних ключей, поддержка транзакций, поддержка параллелизма, детализация блокировки - поддержка блокировки на уровне строк mvcc;
Механизм хранения MyISAM: это механизм хранения, официально предоставляемый MySQL, в основном для приложений OLAP (онлайн-аналитическая обработка).
Возможности:
Не поддерживает транзакции, гранулярность блокировки - это блокировки на уровне таблицы, которые поддерживают одновременную вставку, и поддерживает табличный и полнотекстовый индексы. Скорость работы высокая и не позволяет выполнять операции чтения и записи слишком часто;
Транзакция - это определяемая пользователем последовательность операций с базой данных. Эти операции либо выполняются, либо не выполняются вовсе. Это неделимая единица работы. Откат транзакции означает завершение транзакции. Операция обновления базы данных отменена.
Если вы хотите изменить две разные таблицы в базе данных одновременно, если они не являются транзакцией, при изменении первой таблицы может возникнуть исключение во время изменения второй таблицы, и ее нельзя будет изменить. В настоящее время есть только вторая таблица Это все еще состояние до модификации, и первая таблица была изменена. И когда вы устанавливаете их как транзакцию, когда первая таблица изменяется, а вторая таблица изменяется ненормально, но не может быть изменена, первая таблица и вторая таблица должны вернуться в неизмененное состояние. Это так называемый откат транзакции
Значение NULL означает НЕИЗВЕСТНО (неизвестно): оно не означает "" (пустая строка). Любое сравнение со значением NULL приведет к значению NULL. Вы не можете сравнивать какое-либо значение со значением NULL и логически надеяться получить ответ.
Используйте IS NULL для оценки NULL
Разница между первичным ключом, внешним ключом и индексом?
Первичный ключ однозначно определяет запись, не может быть дублирован и не может быть пустым, используемый для обеспечения целостности данных. может быть только один первичный ключ
Внешний ключ - внешний ключ таблицы является первичным ключом другой таблицы, внешний ключ может повторяться, и он может быть нулевым - используется для установления связи с другими таблицами. таблица может иметь несколько внешних ключей
Что можно использовать, чтобы гарантировать, что поля в таблице принимают значения только в определенном диапазоне?
Проверка ограничения, которая определена в таблице базы данных, используется для ограничения значения, введенного в столбец.
Триггеры также можно использовать для ограничения допустимых значений полей в таблице базы данных, но этот подход требует, чтобы триггеры были определены в таблице, что в некоторых случаях может повлиять на производительность.
Каковы методы оптимизации операторов SQL?
(1) В предложении Where: связь между таблицами where, которые должны быть записаны перед другими условиями Where, и теми условиями, которые могут отфильтровать максимальное количество записей, должна быть записана в конце предложения Where.
(2) Используйте EXISTS вместо IN и NOT EXISTS вместо NOT IN.
(3) Избегайте использования вычислений по столбцам индекса.
(4) Избегайте использования IS NULL и IS NOT NULL в столбце индекса.
(5) Чтобы оптимизировать запрос, постарайтесь избегать полного сканирования таблицы и сначала подумайте об установлении индексов для столбцов, участвующих в where и order by.
(6) Старайтесь избегать суждения о нулевом значении поля в предложении where, иначе это заставит движок отказаться от использования индекса и выполнить полное сканирование таблицы.
(7) Старайтесь избегать выполнения операций с выражениями в полях в предложении where, что приведет к тому, что движок откажется от использования индексов и выполнит полное сканирование таблицы.
Все подзапросы можно разделить на две категории: связанные подзапросы и некоррелированные подзапросы.
(1) Некоррелированные подзапросы - это подзапросы, независимые от внешних запросов.Подзапросы выполняются один раз, а значения передаются во внешний запрос после выполнения.
(2) Выполнение связанного подзапроса зависит от данных внешнего запроса: внешний запрос выполняет строку, а подзапрос выполняется один раз.
Следовательно, некоррелированные подзапросы более эффективны, чем связанные подзапросы.
Помните принцип: чем меньше столбец, тем быстрее. Если в таблице есть только несколько столбцов (например, таблицы словаря, таблицы конфигурации), то нет причин использовать INT в качестве первичного ключа. Более экономично использовать MEDIUMINT, SMALLINT или TINYINT меньшего размера. Если вам не нужно записывать время, лучше использовать DATE, чем DATETIME. Конечно, вам также нужно оставить достаточно места для расширения.
Добавьте индекс для поля поиска
Индексы не обязательно нужны для первичных ключей или уникальных полей. Если в вашей таблице есть поле, которое вы всегда будете использовать для поиска, лучше всего проиндексировать его, если только поле, которое вы хотите искать, не является большим текстовым полем, тогда вам следует установить полнотекстовый индекс.
Используйте ENUM вместо VARCHAR
Тип ENUM очень быстрый и компактный. Фактически, он сохраняет TINYINT, но выглядит как строка снаружи. Таким образом, использование этого поля для создания некоторых списков опций становится совершенно идеальным. Например, значения таких полей, как пол, этническая принадлежность, отдел и статус, ограничены и фиксированы, поэтому вам следует использовать ENUM вместо VARCHAR.
Таблицы фиксированной длины будут быстрее
Если все поля в таблице имеют «фиксированную длину», вся таблица будет считаться «статической» или «фиксированной длины». Например, в таблице нет полей следующих типов: VARCHAR, TEXT, BLOB. Пока вы включаете одно из этих полей, таблица не является «статической таблицей фиксированной длины». Таким образом, механизм MySQL будет использовать другой метод для ее обработки.
Таблицы фиксированной длины улучшат производительность, потому что MySQL ищет быстрее, потому что с помощью этих фиксированных длин легко вычислить смещение следующих данных, поэтому чтение, естественно, будет быстрым. И если поле не фиксированной длины, то каждый раз, чтобы найти следующее, программе необходимо найти первичный ключ.
Кроме того, таблицы фиксированной длины легче кэшировать и перестраивать. Однако единственным побочным эффектом является то, что поля фиксированной длины будут тратить некоторое пространство, потому что для полей фиксированной длины необходимо выделить так много места, независимо от того, используете вы их или нет.
Datatime: время периода хранения в формате ГГГГ-ММ-ДД ЧЧ: ММ: СС с точностью до секунды, занимает 8 байтов дискового пространства, тип времени данных не имеет ничего общего с часовым поясом
Отметка времени: хранится в формате отметки времени, занимает 4 байта и имеет небольшой диапазон от 1970-1-1 до 2038-1-19. Отображение зависит от указанного часового пояса. По умолчанию его можно получить автоматически при изменении данных в первом столбце и строке. Измените значение столбца отметки времени
Дата: (день рождения) занимает меньше байтов, чем при использовании строки .datatime.int для его хранения. При использовании даты требуется всего 3 байта для хранения даты и месяца. Вы также можете использовать функцию даты и времени для вычисления даты и времени.
Время: сохранить временную часть данных.
Примечание: не используйте строковый тип для хранения данных даты и времени (обычно он занимает меньше места для хранения, чем строка, вы можете использовать функцию даты при поиске и фильтрации)
Использование int для хранения даты и времени не так хорошо, как использование типа timestamp