Не используйте MyISAM (MyISAM vs InnoDB)

Прочитав большое количество отзывов и рекомендаций про работу InnoDB и MyISAM, я обнаружил пример, который меня убедил, что MyISAM следует использовать предварительно подумав 10 раз. А для повседневной разработки стоит использовать только InnoDB.

Краткое сравнение:

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

А теперь позвольте привести пример, который повёрг меня в сильный ступор.

Пример:

mysql> CREATE TABLE t (a INT NOT NULL, UNIQUE(a) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t values (5), (6);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t;
+---+
| a |
+---+
| 5 |
| 6 |
+---+
2 rows in set (0.00 sec)

mysql> update t set a = 1;
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> select * from t;
+---+
| a |
+---+
| 1 |
| 6 |
+---+
2 rows in set (0.00 sec)

Как видно, что при использовании MyISAM малейшая ошибка может вызвать полную потерю информации. Можно даже с уверенностью утверждать, что целостность данных будет нарушена. С InnoDB такого бы не произошло. Для себя я сделал вывод, что при разработке следует использовать InnoDB, но не злоупотреблять её преимуществами, ибо всегда есть вероятность того, что придётся переходить на MyISAM в целях оптимизации.

P.S. А вообще, не стоит использовать MySQL, лучше использовать PostgreSQL, но эту тему я раскрою несколько позже.

Кодировки и MySQL

Проблема кодировок, пожалуй, первая проблема с которой сталкиваются пользователи MySQL начиная с 5 версии. "Вместо строки знаки вопроса", "Неправильная сортировка" таковы темы бесчисленных топиков создаваемых в интернете.
При работе с базой MySQL 5.x следует запомнить, что есть 2 основных кодировки - кодировка в которой хранятся данные и кодировка в которой передаются данные. Первая указывается при создании таблиц в базе, а вторая указывается непосредственно при соединении.
Предположим, я хочу хранить данные в UTF-8, добавлять в cp866, а получать их в cp1251. Работа будет проводиться в коммандной строке windows.

Пример:

C:\Server\mysql\bin>chcp 866
Текущая кодовая страница: 866

C:\Server\mysql\bin>mysql -uexample -pexample example
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table strings;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `strings` (string text) DEFAULT CHARACTER SET UTF8;
Query OK, 0 rows affected (0.14 sec)

mysql> set names cp866;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `strings` VALUES ('Строка'), ('Вопрос');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from `strings`;
+--------+
| string |
+--------+
| Строка |
| Вопрос |
+--------+
2 rows in set (0.00 sec)

mysql> \q
Bye

C:\Server\mysql\bin>chcp 1251
Текущая кодовая страница: 1251

C:\Server\mysql\bin>mysql -uexample -pexample example
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from `strings`;
+--------------+
| string       |
+--------------+
| Строка |
| Р’РѕРїСЂРѕСЃ |
+--------------+
2 rows in set (0.00 sec)

mysql> set names cp1251;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `strings`;
+--------+
| string |
+--------+
| Строка |
| Вопрос |
+--------+
2 rows in set (0.00 sec)

mysql>

А теперь на словах. Я установил кодировку cp866, подключился к MySQL и создал таблицу, строки которой будут храниться в UTF-8. Затем, указал базе даннных, что сейчас данные будут в cp866 и добавил 2 записи в таблицу. Закрыл соединение с базой данных и изменил текущую кодовую страницу на cp1251. Снова соединился с базой данных и попробовал получить данные из таблицы. Мне пришла белиберда, так как база данных решила, что мне нужны данные в UTF-8. После того как я указал, что мне нужны данные в cp1251, база отдала мне их в нужной кодировке.

Таким образом, можно сделать вывод, что в MySQL можно хранить данные в UTF-8 вне зависимости от того в какой кодировке вы будете передавать и получать данные.

Заметки:

• После соединеня с MySQL необходимо отправлять команду SET NAMES <кодировка>, до отправки или получения данных.
• Кодировка в которой хранятся данные определяется при создании таблицы.

Чувствительность к регистру в MySQL

По умолчанию в MySQL строки (char, varchar, text) регистронезависимы. Таким образом строки "Пример" и "примеР" идентичны. Самым популярным решением сделать строки регистрозависимыми является использование типа blob. Однако это не правильно.

Во-первых, с BLOB не будут правильно работать строковые функции. Во-вторых перестанет работать механизм смены кодировки (подробнее об этом механизме я напишу позже).

Правильным же решением этой задачи является использование COLLATION. Слово COLLATION переводится как сличение, то есть задаёт правила сравнивания строк.

Пример:

CREATE TABLE `users` (
  `username` text NOT NULL,
  `host`   text NOT NULL,
  `password` text NOT NULL,
  `email`  text ,
  `uniqueid` text ,
  `datereg` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = MyISAM
DEFAULT CHARACTER SET UTF8 COLLATE UTF8_BIN;

* This source code was highlighted with Source Code Highlighter.

В данном примере для всех строк в таблице задаётся кодировка UTF-8 и задаётся правило регистрозависимого сравнения.

Заметки:

• Команда show collation; выводит список всех поддерживаемых сличений и их параметры.
• Наличие у сличения окончания _ci (например, utf8_general_ci) указывает, что оно регистронезависимо (case insensitive). Окончание _bin указывает, что сличение регистрозависимо (сравнивается в двоичном формате).
• Подробнее можно почитать тут: link


• Примеры использования:
  • для базы link
  • для таблицы link
  • для столбца link
  • даже при выборке или вставке link