Как проверить тип системы хранения MySQL на Linux и Windows
Используются две основные системы хранения MySQL: MyISAM и Innodb. MyISAM является не транзакционной, и, следовательно, может быть быстрее для чтения, в то время как InnoDB полностью поддерживает транзакции (например, совершение/откат) с блокировкой отдельных элементов. Когда вы создаёте новую таблицу MySQL вы выбираете её тип (так называемую систему хранения, движок базы данных). Если вы не делаете выбор, то вы просто будете использовать движок, который в конфигурации указан как дефолтный.
Если вы хотите узнать тип существующей таблицы базы данных MySQL, есть несколько способов сделать это.
Метод первый
Если у вас есть доступ в phpMyAdmin, мы можете узнать тип баз данных из него. Просто выберите базу данных из phpMyAdmin, чтобы видеть список её таблиц. В колонке «Тип» вы увидите тип базы данных для каждой таблицы.
Метод второй
Если вы можете напрямую авторизоваться на сервере MySQL, другой способ выяснить систему хранения — это запустить следующую MySQL команду внутри вашего MySQL сервера после авторизации.
mysql> SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';
Эта команда покажет тип системы хранения таблицы под названием ‘my_table’ в базе данных ‘my_database’.
‘my_database’ — название базы данных, в которой находится интересующая вас таблица
‘my_table’ — название интересующей вас таблицы
Метод третий
И ещё один метод проверить движок MySQL, это использование mysqlshow (утилиты командной строки, которая показывает информацию о базе данных). mysqlshow поставляется вместе с установкой клиентского пакета MySQL. Для использования mysqlshow вы должны войти в MySQL сервер со своим логином.
Эта команда отобразит информацию о конкретной базе данных. В колонке «Engine» вы увидите систему хранения для каждой таблицы.
$ mysqlshow -u -p -i
Узнать тип системы хранения MySQL на Windows
Все вышеописанные методы применимы и для Windows. Небольшая коррекция есть только для третьего способа. Файл имеет имя mysqlshow.exe и, скорее всего, придётся прописывать полный путь до него. Например, я вызываю его таким образом:
C:Serverbinmysql-5.6binmysqlshow.exe -u root -p -i db_wordpress
Tech blog by @dizballanze
Здравствуйте, сегодня мы поговорим о типах таблиц в MySQL. Архитектура MySQL позволяет подключать разные движки таблиц. На данный момент MySQL поддерживает множество различных типов таблиц, каждый из которых имеет свои преимущества и недостатки. Я перечислю и коротко опишу основные типы таблиц, а затем проведу небольшой тест производительности наиболее часто используемых типов — myisam и innodb.
Для того что-бы посмотреть какие типы поддерживает ваша инсталляция MySQL необходимо выполнить следующий SQL запрос:
SHOW ENGINES;
В результате вы получаете таблицу содержащую информацию о том какие типы таблиц установлены в вашей системе и краткое описание их возможностей.
| Engine | Support | Comment | Transactions | XA | Savepoints |
|---|---|---|---|---|---|
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL CSV |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
Нас в основном будет интересовать столбец support, который содержит информацию о поддержке типа таблицы и может принимать значения: NO — не поддерживается, YES — поддерживается, DEFAULT -используется по-умолчанию. Начиная с версии 5.5.5 по-умолчанию выбран тип innodb, ранее стандартным типом был myisam.
В версии MySQL 5.5 поддерживается 9 различных типов таблиц.
- InnoDB — движок с поддержкой транзакций, откатов и защитой от потери данных. В данном типе таблиц используются блокировки на уровне записи и не блокирующее чтение, что позволило улучшить производительность при многопользовательском режиме работы. InnoDB сохраняет пользовательские данные в кластерных индексах, что позволяет компенсировать в/в для простых запросов основанных на первичных ключах.
- MyISAM — движок таблиц MySQL используемый в основном в Web-приложениях, хранилищах данных и других программных средах. Данный тип таблиц поддерживается всеми инсталляциями MySQL.
- Memory — хранит данные в оперативной памяти для очень быстрого доступа. Также известен как HEAP (куча).
- Merge — используется для логического объединения одинаковых MyISAM таблиц и обращение к ним, как к единому объекту. Хорошо подойдет для очень больших хранилищ данных.
- Archive — идеальное решение для хранения больших объёмов информации, к которой не требуется частый доступ.
- Federated — предоставляет возможность объединять различные MySQL сервера для создания одной логической базы данных из нескольких физических машин. Идеально подойдет для архитектур, которые поддерживают распределенное хранение данных.
- CSV — хранит пользовательские данные в текстовых файлах разделяя значения запятыми. Используется если необходим простой обмен с приложениями, которые умеют экспортировать/импортировать данные из CSV формата.
- Blackhole — принимает, но не возвращает никаких данных. Результатами любых запросов из таких хранилищ будут пустые выборки.
- Example — тестовый движок, не выполняет никаких функций, будет полезен только разработчикам, которые собираются писать свой движок, в качестве примера.
Сравнительная таблица основных типов таблиц
| Функция | MyISAM | Memory | InnoDB | Archive |
|---|---|---|---|---|
| Максимальный объём хранимых данных | 256TB | RAM | 64TB | Нет |
| Транзакции | Нет | Нет | Да | Нет |
| Блокировки | Таблица | Таблица | Запись | Запись |
| MVCC | Нет | Нет | Да | Нет |
| B-деревья | Да | Да | Да | Нет |
| Хэш индексы | Нет | Да | Нет | Нет |
| Индексы полнотекстового поиска | Да | Нет | Нет | Нет |
| Кластерные индексы | Нет | Нет | Да | Нет |
| Кэширование данных | Нет | Н/д | Да | Нет |
| Кэширование индексов | Да | Н/д | Да | Нет |
| Сжатие данных | Да | Нет | Да | Да |
| Шифрование данных | Да | Да | Да | Да |
| Поддержка кластерных БД | Нет | Нет | Нет | Нет |
| Репликация | Да | Да | Да | Да |
| Внешние ключи | Нет | Нет | Да | Нет |
| Бэкап | Да | Да | Да | Да |
| Кэширование запросов | Да | Да | Да | Да |
Тестирование производительность InnoDB и MyIASM
Наибольший интерес для web-разработчика составляют innodb и myisam. Сейчас мы проведем сравнительный тест производительности этих типов таблиц. Для этого сначала создадим две одинаковые по структуре таблицы, но с разным типом движка хранения:
CREATE TABLE `inno` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `data` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `myisam` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `data` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM;
Напишем небольшой скрипт который будет выполнять 3 теста: запись данных (insert), выборка по ключу, выборка по не ключевому полю.
// . class timer public $value=0; public function start() $this->value = microtime(true); > public function end() return microtime(true) - $this->value; > > $timer = new timer(); mysql_connect('localhost','root'); mysql_select_db('test'); $data = array(); $select_query = 'SELECT data FROM myisam WHERE >; for ($i = 0; $i 10000; $i++) $result = mysql_query(str_replace(':val:', $i, $select_query)); $tmp = mysql_fetch_array($result); $data[] = $tmp[0]; > echo '
MyISAM select
'; $select_query = 'SELECT * FROM myisam WHERE data = :val:'; $timer->start(); foreach ($data as $one) mysql_query(str_replace(':val:', $one, $select_query)); > echo $timer->end() . ' s
'; echo '
InnoDB select
'; $select_query = 'SELECT * FROM inno WHERE data = :val:'; $timer->start(); foreach ($data as $one) mysql_query(str_replace(':val:', $one, $select_query)); > echo $timer->end() . ' s
'; /* $data = array(); for ($i = 0; $i <= 10000; $i++) $data[] = mt_rand(0, 100500); > echo '
MyISAM select by key
'; $select_query = 'SELECT * FROM myisam WHERE >$timer->start(); for ($i = 0; $i <= 10000; $i++) mysql_query(str_replace(':val:', $i, $select_query)); > echo $timer->end() . ' s
'; echo '
InnoDB select by key
'; $select_query = 'SELECT * FROM inno WHERE >$timer->start(); for ($i = 0; $i <= 10000; $i++) mysql_query(str_replace(':val:', $i, $select_query)); > echo $timer->end() . ' s
'; */ /* $data = array(); for ($i = 0; $i <= 10000; $i++) $data[] = mt_rand(0, 100500); > echo '
MyISAM insert
'; $insert_query = 'INSERT INTO myisam VALUES (NULL,\':val:\')'; $timer->start(); foreach ($data as $one) mysql_query(str_replace(':val:', $one, $insert_query)); > echo $timer->end() . ' s
'; echo 'InnoDB insert
'; $insert_query = 'INSERT INTO inno VALUES (NULL,\':val:\')'; $timer->start(); foreach ($data as $one) mysql_query(str_replace(':val:', $one, $insert_query)); > echo $timer->end() . ' s
'; */
Для того что-бы выполнить тест, нужно раскоментить один соответствующий блок кода. И собственно, то что у меня получилось в результате тестирования:
| Тест | InnoDB | MyISAM |
|---|---|---|
| Вставка данных(insert) | 15.697 с | 1.591 с |
| Выборка по ключу | 1.678 с | 1.603 с |
| Выборка по не ключевому полю | 149.961 c | 95.984 c |
Как мы видим myisam работает значительно быстрее, особенно это заметно при вставке данных. Хотя innodb и дает ряд новых возможностей и преимуществ, такая медлительность не позволяет ему конкурировать с myisam, особенно в web-приложениях.
Какой движок таблиц поддерживает транзакции в MySQL?
Движок таблиц InnoDB поддерживает транзакции в MySQL. Используя InnoDB, вы можете создавать транзакционные таблицы, которые поддерживают операции COMMIT и ROLLBACK. Вот пример:
CREATE TABLE my_table ( id INT PRIMARY KEY, name VARCHAR(50) ) ENGINE=InnoDB; START TRANSACTION; INSERT INTO my_table (id, name) VALUES (1, 'John'); INSERT INTO my_table (id, name) VALUES (2, 'Jane'); COMMIT;
В этом примере мы создаем таблицу my_table с движком InnoDB и выполняем две операции INSERT в рамках одной транзакции. Если мы вызываем COMMIT, изменения будут сохранены в базе данных. Если что-то пошло не так, мы можем вызвать ROLLBACK, чтобы откатить все изменения в рамках транзакции.
Детальный ответ
Привет! Добро пожаловать в мир баз данных, где таблицы представляют собой ключевой элемент работы с данными. Вопрос, который тебя интересует — «какой движок таблиц поддерживает транзакции в MySQL?» Для начала, давай определимся, что такое транзакции и почему они так важны при работе с базами данных. Транзакция — это последовательность операций, которые могут быть выполнены как единое целое. Одна из главных особенностей транзакций — это их атомарность, то есть выполнение всех операций или ни одной. Если хотя бы одна операция внутри транзакции не будет выполнена успешно, все изменения откатятся, и база данных останется в неизменном состоянии. Это позволяет обеспечить целостность данных и избежать ошибок. Теперь перейдем к движкам таблиц в MySQL. MySQL поддерживает несколько движков таблиц, каждый из которых представляет собой способ хранения и обработки данных. Один из наиболее популярных движков таблиц в MySQL — InnoDB. InnoDB является транзакционным движком и обладает многими полезными особенностями, включая поддержку транзакций. Рассмотрим пример использования транзакций в MySQL с помощью таблицы, созданной с движком InnoDB:
CREATE TABLE my_table ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ) ENGINE=InnoDB;
Теперь, когда у нас есть таблица, давайте представим, что мы хотим внести изменения в несколько строк одновременно. Если мы хотим убедиться, что все операции выполняются успешно или не выполняются вообще, нам понадобится использовать транзакции:
START TRANSACTION; INSERT INTO my_table (name) VALUES ('John'); INSERT INTO my_table (name) VALUES ('Jane'); INSERT INTO my_table (name) VALUES ('Alice'); COMMIT;
Здесь мы начинаем транзакцию с помощью команды «START TRANSACTION». Затем мы выполняем несколько операций INSERT для внесения изменений в нашу таблицу. Наконец, мы зафиксируем наши изменения с помощью команды «COMMIT». Если в ходе наших операций возникнут ошибки, мы можем откатить все изменения с помощью команды «ROLLBACK». Очень важно отметить, что движок таблицы должен поддерживать транзакции, чтобы вы могли использовать их в своем коде. К счастью, InnoDB, как уже упоминалось ранее, является транзакционным движком и обеспечивает поддержку транзакций в MySQL. Итак, теперь ты знаешь, какой движок таблицы поддерживает транзакции в MySQL — это InnoDB. Знание этого может быть очень полезным при разработке веб-приложений или любого другого приложения, которое требует целостности данных и обработки транзакций. Не забывай использовать транзакции для защиты своих данных и обеспечения надежности работы с базой данных! Удачи в твоих учебных и разработческих приключениях!
Транзакции InnoDB
InnoDB это транзакционный, реляционный движок работающий на основе MySQL сервера. Начиная с 2001 года он поставляется в стандартной сборке, а с версии 5.1 может устанавливаться в качестве плагина (без необходимости перекомпилировать ядро сервера). Синтаксис очень простой.
START TRANSACTION;
.
COMMIT; — или же ROLLBACK; если что-то пошло в логике не так
Про определение
Определение транзакционности и реляционности значат во-первых значат полноценную связанность таблиц через FK и как следствие — целостность данных при удалении рядов. С MyIsam как известно приходилось вручную удалять связанные данные в нескольких таблицах, в InnoDB — каскадное удаление одним запросом. Во-вторых поскольку для БД немыслимы параллельные версии данных как в SVN и некому эти версии объединять в одну ветку, но при этом необходима параллельная работа нескольких процессов (пользователей) с одними данными, то в качестве решения становится транзакции.
Очередь из запросов-автомобилей теперь пополняется атомарной транзакцией-автобусом. Естественно это плохо, поскольку чем длиней и дольше выполняется транзакция тем больше параллельных процессов будут ждать его. Для ускорения работы создаются остановки — типы и уровни блокировки данных. Для InnoDB по умолчанию это блокирование на уровне строки (по PK ), тогда как в MyIsam атомарная операция блокирует всю таблицу.
Тразнактивность = блокировка
- READ (пока я читаю — никто не запишет) — по умолчанию на SELECT ставится
- WRITE (пока я пишу — никто не прочтёт и не запиет) — по умолчанию на UPDATE ставится
- LOW_PRIORITY WRITE (дам быстро прочитать если кто-то ждёт)
Уровни изоляции
- SERIALIZEABLE — полная независимость транзакций, в т.ч. своё чтение
- REPEATABLE READ (повторяющееся чтение) — значение для InnoDB по умолчанию. Чтение общих строк в транзакциях разрешается, но не их изменение.
- READ COMMITED (чтение фиксированного) — блокировка записи, но общее чтение. Есть проблема повторяющегося чтения, т.е. в первой транзакции несколь раз по разному читаются общие данные, потому что вторая транзакция их меняет.
- READ UNCOMMITED («грязное» чтение незафиксированного) — никакой блокировки на чтение и запись. При двух одновременных UPDATE поле получит значение последнего изменения в обоих транзакциях. Возможны множество проблем, особенно если до ROLLBACK одной транзакции изменения читает другая.
- Чтение с уровенем REPEATABLE READ (блокировка на запись). Ждёт если над данными кто-то работает.
SELECT. LOCK IN SHARE MODE - Чтение в режиме SERIALIZEABLE (блокировка на чтение и запись)
SELECT. FOR UPDATE
Травматизм deadlock’ов
- Сравнение нагрузок на MyIsam и InnoDB в 2007 году
- Ссылочная целостность