MySQL知识点总结

1. MySQL存储引擎有哪些?

1) InnoDB存储引擎

InnoDB 目前是 MySQL 的 默认事务型引擎，是当前最重要、使用最广泛的存储引擎。支持事务安全表（ACID），支持行级锁定和外键。InnoDB的主要特性包括： 1、InnoDB 为 MySQL 提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID 兼容）存储引擎。InnoDB 锁定在行级，并且在 SELECT 语句中提供了一个类似于 Oracle 的非锁定读取。这些功能增加了多用户部署和性能。在 SQL 查询中，可以自由地将 InnoDB 类型的表与其他 MySQL 的表类型混合起来，甚至在同一查询中也可以混合。 2、InnoDB 是为了处理大量数据的最大性能而设计的。它的 CPU 效率可能是任何其他基于磁盘的关系型数据库引擎无法比拟的。 3、InnoDB 存储引擎完全与 MySQL 服务器集成，InnoDB 存储引擎为在主内存中缓存数据和索引而维护它自己的缓冲池。InnoDB 将它的表和索引在一个逻辑表空间中，表空间可以包含多个文件（或原始磁盘文件）。这与 MyISAM 表不同，例如，在 MyISAM 表中每个表被存放在单独的文件中。InnoDB 表可以是任意大小，即使在文件大小被限制为 2GB 的操作系统上。 4、InnoDB 支持外键完整性约束，存储表中的数据时，每张表的存储都按照主键顺序存放，如果没有在表定义时明确指定主键，InnoDB 会为每一行生成一个 6 字节的 ROWID，并以此作为主键。 5、InnoDB 被用于许多需要高性能的大型数据库站点上。 InnoDB 不创建目录，使用 InnoDB 时，MySQL 将在 MySQL 数据目录下创建一个名为 ibdata1 的 10MB 大小的自动扩展数据文件，以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB 大小的日志文件。 场景：由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求较高（如银行），需要实现并发控制（如售票），那么选择 InnoDB 有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择 InnoDB，因为它支持事务的提交（commit）和回滚（rollback）。

2) MyISAM存储引擎

MyISAM 基于 ISAM 存储引擎，并对其进行了扩展。Он является одним из самых часто используемых存储引擎，在Web、数据仓库和其他应用程序环境中。MyISAM具有高速的插入和查询速度，但不支持事务和外键。

纠正后的文本应为：

Он является одним из самых часто используемых хранителей данных в среде Web, хранилищ данных и других приложений. **MyISAM имеет высокую скорость вставок и запросов, но не поддерживает транзакции и внешние ключи.**Основные характеристики MyISAM:1. Поддержка больших файлов (до 63 бит длины файла) на файловых системах и операционных системах, поддерживающих большие файлы. 2. При смешанном использовании операций удаления, обновления и вставки динамически размерные строки создают меньше фрагментации. Это достигается путём объединения соседних удалённых блоков и автоматического расширения до следующего удалённого блока, если он существует. 3. Каждый MyISAM-таблица может иметь максимум 64 индекса, что можно изменить перекомпиляцией. Максимальное количество столбцов в одном индексе — 16. 4. Максимальная длина ключа составляет 1000 байт, что также можно изменить при компиляции. Для ключей длиной более 250 байт используется ключ длиной более 1024 байт. 5. Столбцы BLOB и TEXT могут быть индексированы, поддерживаются полнотекстовые индексы, чего InnoDB не поддерживает. 6. Разрешены NULL в индексируемых столбцах, каждый ключ может занимать от 0 до 1 байта. 7. Все числовые ключи хранятся с использованием старшего байта в первую очередь, что позволяет повысить эффективность сжатия индексов. 8. У каждой MyISAM-таблицы есть внутренний AUTO_INCREMENT-столбец, который обновляется при выполнении операций INSERT и UPDATE. AUTO_INCREMENT-столбец обновляется быстрее, чем в InnoDB. 9. Данные и индексы могут храниться в разных директориях. 10. Каждый символьный столбец может использовать свой собственный набор символов. 11.Таблицы с VARCHAR могут иметь фиксированную или динамическую длину записей. 12. VARCHAR и CHAR столбцы могут содержать до 64 КБ.### 3) MERGE движок

MERGE движок представляет собой объединение нескольких MyISAM таблиц, которые должны иметь одинаковую структуру, хотя его использование менее распространено по сравнению с другими движками. Однако в некоторых случаях он очень полезен. В общем, Merge таблица является агрегатором нескольких одинаковых MyISAM таблиц; сама Merge таблица не содержит данных, но её можно использовать для выполнения запросов, обновлений и удалений, которые фактически выполняются на внутренних MyISAM таблицах.

Сценарий: Для хранения серверных логов обычно используются стратегии хранения, которые разделяют данные на несколько таблиц, каждая из которых названа в соответствии со своим временным диапазоном. Например, можно использовать 12 одинаковых таблиц для хранения данных серверных логов, каждая из которых названа в соответствии с месяцем. Когда требуется создать отчёт на основе всех 12 таблиц логов, это требует написания и обновления многотабличных запросов, чтобы отражать информацию в этих таблицах. Лучше объединить эти таблицы в одну с помощью одного запроса, чем писать такие запросы, которые могут привести к ошибкам. После этого можно удалить таблицу Merge, не затрагивая исходные данные. Удаление таблицы Merge удаляет только её определение, не влияя на внутренние таблицы.### 4) ПОДХОДЫ К ХРАНЕНИЮ ПАМЯТИ Память движка хранит данные таблиц в оперативной памяти, обеспечивая быстрый доступ для запросов и ссылок на данные других таблиц. Основные характеристики движка памяти:

1. Каждая таблица движка памяти может иметь до 32 индексов, каждый из которых может содержать до 16 столбцов, а максимальная длина ключа составляет 500 байт.
2. Движок памяти поддерживает выполнение индексации HASH и BTREE.
3. В одной таблице движка памяти могут существовать неуникальные ключи.
4. Таблицы движка памяти используют фиксированный формат записи.
5. Движок памяти не поддерживает столбцы типа BLOB или TEXT.
6. Движок памяти поддерживает столбцы AUTO_INCREMENT и индексы для столбцов, допускающих NULL.
7. Таблицы движка памяти доступны всем клиентам (как и любые другие не временные таблицы).
8. Данные движка памяти хранятся в оперативной памяти, которая также используется для хранения временных таблиц, создаваемых сервером при обработке запросов.
9. Когда данные движка памяти больше не нужны, следует освободить память, используя DELETE FROM, TRUNCATE TABLE или DROP TABLE.ENGINE MEMORY по умолчанию использует индекс HASH, который работает быстрее, чем B-+Tree, но также поддерживает индекс B-дерева. Поскольку данные хранятся в оперативной памяти, они неустойчивы и могут быть потеряны при неожиданных остановках mysqld, перезапусках или выключении компьютера. Поэтому срок службы таблиц ENGINE MEMORY обычно короткий и они используются только один раз. В настоящее время использование ENGINE MEMORY уменьшается по мере роста популярности NoSQL баз данных, таких как MongoDB и Redis.Сценарий использования: если требуется временная таблица для запросов в базе данных.

5) STORAGE ENGINE ARCHIVE

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

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

6) STORAGE ENGINE CSV

Использование движка CSV для MySQL базы данных создаёт таблицу, которая генерирует .CSV файл в папке data в каталоге установки MySQL с тем же именем, что и имя базы данных (поэтому он может обрабатывать файлы CSV как таблицы). Это обычный текстовый файл, где каждая строка данных занимает одну строку текста. Этот тип движка не поддерживает индексы, то есть таблицы этого типа не имеют столбцов первичного ключа; также не допускаются поля со значением NULL. Конвертация кодировки CSV требует особого внимания.Сценарий: этот движок поддерживает копирование/вывод CSV файлов из базы данных. Если вы выгрузите CSV файл из электронной таблицы и сохраните его в каталоге данных MySQL сервера, сервер сможет немедленно прочитать соответствующий CSV файл. Аналогично, если вы запишете данные в CSV таблицу, внешние программы смогут немедленно прочитать её. В контексте реализации определённого типа журналирования, CSV таблица особенно полезна как формат обмена данными.### 7) Движок STORAGE BLACKHOLE Этот движок поддерживает транзакции и поддерживает строковые блокировки уровня MVCC. Любые данные, записанные в таблицы этого движка, исчезают. Он主要用于日志记录或作为中间存储用于同步归档。除非有特殊目的，否则不建议使用此存储引擎。

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

PERFORMANCE_SCHEMA движок

Этот движок主要用于收集有关数据库服务器性能的参数。他提供了以下功能：提供有关进程等待的详细信息，包括锁、互斥量、文件信息；保存历史事件的摘要信息，以提供关于MySQL服务器性能的详细分析；对于添加和删除监控事件点非常方便，并且可以随意更改MySQL服务器的监控周期，例如（CYCLE、MICROSECOND）。MySQL用户不能创建PERFORMANCE_SCHEMA存储引擎的表。

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

8) Federated движок

Этот движок объединяет различные MySQL серверы логически в одну целостную базу данных. Этот тип движка очень подходит для распределенных приложений баз данных.

---Перевод выполнен в соответствии с указанными правилами, сохраняя структуру и форматирование исходного текста.

Федерированный (Federated) движок хранения позволяет вам обращаться к данным в удалённой базе данных из локальной базы данных. Запросы к таблицам с использованием федерированного движка отправляются на удалённую базу данных для выполнения, а сами данные не хранятся локально.Недостатки:

1. Изменение структуры локального виртуального представления не приводит к изменениям структуры удалённой таблицы.
2. Команда TRUNCATE удалит все данные из удалённой таблицы.
3. Команда DROP удалит только локальное виртуальное представление, а не удалённую таблицу.
4. Поддержка команды ALTER TABLE отсутствует.
5. Выполнение запросов типа SELECT COUNT(*), SELECT * FROM LIMIT M, N и других подобных операций очень медленно, особенно при работе с большими объёмами данных. Однако запросы по первичному ключу или индексированному столбцу выполняются быстро. Например, следующий запрос будет выполняться медленно (предположим, что id является первичным индексом):

   SELECT id FROM db.tablea WHERE id > 100 LIMIT 10;
   В то время как следующий запрос будет выполняться быстро:

   SELECT id FROM db.tablea WHERE id > 100 AND id < 150
6. Если в локальном виртуальном представлении нет индекса для определённого поля, но есть индекс в удалённой таблице, производительность запросов будет низкой. Однако если вы создадите индекс в локальном виртуальном представлении, производительность вернётся к нормальным значениям.
7. Запросы типа WHERE name LIKE "стр%" LIMIT 1 даже при наличии индекса на поле name будут выполняться медленно, так как федерированный движок загружает все записи, удовлетворяющие условию, в локальную базу данных, а затем применяет ограничение LIMIT.Сценарий использования: dblink.

Выбор движка хранения

2. Типы и функции логов MySQL

1. Лог ошибок (Error Log)

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

2. Общий лог запросов (General Query Log)

• Функция: Записывает все SQL-запросы, отправленные клиентами MySQL серверу (включая информацию о подключениях и отключениях), используемые для аудита или анализа поведения.
• Внимание: Этот лог может значительно влиять на производительность, поэтому рекомендуется использовать его только для отладки.
• Параметры конфигурации: general_log, general_log_file

3. Лог медленных запросов (Slow Query Log)

• Функция: Записывает запросы, которые выполняются дольше определенного порога времени (long_query_time) или не используют индексы, чтобы оптимизировать производительность базы данных.
• Ключевые сценарии: Определение и устранение низкоэффективных SQL-запросов.
• Параметры конфигурации: slow_query_log, slow_query_log_file, long_query_time, log_queries_not_using_indexes

5. Промежуточный журнал (Relay Log)

• Задача: В режиме репликации мастер-слейва (Master-Slave Replication) промежуточный журнал хранит события из бинарного журнала, полученные от мастера, чтобы слейв мог последовательно выполнять эти события для синхронизации данных.
• Отношение к Binlog: Промежуточный журнал является "временным хранилищем" для слейва и имеет ту же структуру, что и бинарный журнал.
• Параметры конфигурации: relay_log### 6. Транзакционный журнал (InnoDB специфический)

Redo Log (журнал повторных операций)

• Задача:
• Обеспечивает постоянство транзакций (D в ACID).
• Записывает изменения физических страниц, используемых для восстановления после сбоев, чтобы повторно выполнить уже завершенные транзакции, которые ещё не были записаны в файл данных.
• Механизм записи: Перед завершением транзакции изменения сначала записываются в Redo Log (последовательная запись, высокая производительность), а затем асинхронно обновляются в файл данных.
• Параметры конфигурации: innodb_log_file_size, innodb_log_files_in_group

Undo Log (журнал отмены операций)

• Задача:
• Поддерживает откат транзакций (атомарность).
• Реализует MVCC (многовersionная конкурирующая контроль), обеспечивая согласованное чтение.
• Характеристики:
• Логический журнал (записывает обратные SQL операции).
• Хранится в пространстве Undo.

7. Журнал DDL (метаданный журнал)

• Задача: Записывает метаданные операций, связанных с SQL-запросами DDL (например, CREATE, ALTER), чтобы гарантировать атомарность этих операций при восстановлении после сбоев.
• Характеристики: Управление автоматическое, обычно не требует вмешательства пользователя.### Сравнение типов журналов | Тип журнала | Задача | Уровень | Тип содержимого | Сценарий активации | | :-------------- | :--------- | :---------- | :------------------- | :--------------------- | | Журнал ошибок | Диагностика ошибок сервера | Уровень сервера | Текст | По умолчанию активирован | | Журнал запросов | Записывает все SQL-запросы | Уровень сервера | Текст/SQL | Активируется временно для отладки | | Журнал медленных запросов | Оптимизация медленных SQL-запросов | Уровень сервера | Текст/SQL | Включается при необходимости оптимизации производительности | | Бинарный лог | Репликация master-slave, восстановление данных | Уровень сервера | Логический (SQL/строка) | Включается при репликации или необходимости PITR | | Relay Log | Синхронизация данных с master-сервера на slave-сервере | Уровень сервера | Логический (такой же как Binlog) | Автоматически создается при репликации master-slave | | Redo Log | Восстановление после сбоев, обеспечение устойчивости транзакций | Уровень движка InnoDB | Физический (изменение страниц) | Включен принудительно, отключение невозможно | | Undo Log | Откат транзакций, MVCC | Уровень движка InnoDB | Логический (обратное SQL) | Включен принудительно, отключение невозможно |------

Основные различия

1. Уровень:
   • Бинарный лог, ошибочный лог и другие относятся к уровню сервера и не зависят от уровня хранилища.
   • Redo/Undo Log являются реализацией на уровне движка InnoDB.
2. Цели:
   • Бинарный лог используется для репликации и восстановления; Redo Log используется для восстановления после сбоев; Undo Log используется для отката транзакций и MVCC.
3. Тип содержимого:
   • Бинарный лог записывает логические операции (SQL или изменения строк), Redo Log записывает физическое изменение страниц.
4. Время записи:
   • При завершении транзакции, сначала записывается Redo Log, затем Binlog (двухфазный процесс записи гарантирует согласованность).

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

3. Различие между Binlog, Redo Log и Undo Log в MySQL

1. Уровень принадлежности

2. Основные цели

3. Содержимое журнала

**4.Жизненный цикл

5. Сценарии использования

6. Время и процесс записи

Процесс завершения транзакции (на примере InnoDB)

1.Запись журнала отката (Undo Log): При начале транзакции записываются старые данные в журнал отката для отката и многопоточной совместной работы с контрольной точкой (MVCC).

2. Выполнение изменения данных: Изменение страниц данных в памяти (буферный бассейн).

3. Запись журнала восстановления (Redo Log): Перед завершением транзакции изменения страниц данных записываются в журнал восстановления (последовательная запись, высокая производительность).

4. Запись двоичного журнала (Binlog): При завершении транзакции операции изменения записываются в двоичный журнал.

5. Двухфазный протокол коммита (2PC):

   • Стадия подготовки (Prepare): Журнал восстановления помечает транзакцию как "готовую к коммиту".
   • Стадия коммита (Commit): После успешной записи в двоичный журнал журнал восстановления помечает транзакцию как "закоммиченную" (обеспечение согласованности двоичного журнала и журнала восстановления)

Основные различия

| **Основная цель**      | Копирование данных и восстановление           | Восстановление после сбоев, обеспечение устойчивости | Откат транзакций и многопоточная совместная работа с контрольной точкой (MVCC) |
| **Обязательность**      | Может быть отключен (необходима конфигурация `log_bin`) | Обязательно включен (основной механизм InnoDB) | Обязательно включен (транзакционная механика зависит от него) |
| **Жизненный цикл**     | Долгосрочное хранение (очистка по требованию) | Циклическое перекрытие                         | Хранение после завершения транзакции (зависит от MVCC)        |
| **Влияние на производительность** | Высокая частота записи может стать узким местом | Последовательная запись, высокая производительность | Высокая нагрузка на запись может влиять на производительность параллелизма |
------
### **8. Примеры совместной работы**
**Сценарий**: После завершения транзакции происходит сбой базы данных
1.  **Процесс восстановления после сбоя**:
   - Через журнал восстановления воспроизводятся все изменения страниц данных всех завершенных транзакций, обеспечивая устойчивость данных.      - Через двоичный журнал проверяется, какие транзакции требуют восстановления (если двоичный журнал существует, но журнал восстановления не был закоммичен, восстановление должно выполняться на основе двоичного журнала).
2.  **Откат незавершенных транзакций**:
   - Используя журнал отката, откатываются изменения незавершенных транзакций, обеспечивая атомарность.
------
### **9.  Заключение**
- **Двоичный журнал (Binary Log)**: Логический журнал уровня сервера MySQL, используемый для копирования и восстановления, универсальный для всех движков.
- **Журнал восстановления (Redo Log)**: Физический журнал InnoDB, обеспечивающий устойчивость транзакций и восстановление после сбоев.
- **Журнал отката (Undo Log)**: Логический журнал InnoDB, поддерживающий откат транзакций и многопоточную совместную работу с контрольной точкой (MVCC). Эти три компонента обеспечивают свойства ACID, высокую доступность и согласованность данных в MySQL. Понимание их различий является ключом к оптимизации производительности и диагностике проблем.