Сравнение MySQL и PostgreSQL с точки зрения разработчика

Аннотация

В статье представлен сравнительный анализ двух бесплатных свободных систем управления базами данных (СУБД): MySQL и PostgreSQL. Анализ ведётся с точки зрения использования этих СУБД в мало- и средненагруженных приложениях. Не рассматриваются вопросы масштабирования и оптимизации под проекты с многомиллионными аудиториями. Не приводятся данные сравнения производительности. Рассматриваются MySQL 5.1 и PostgreSQL 8.3.

Типы данных

Первое с чем приходится сталкиваться разработчику - это доступные типы данных. Проведём сравнение доступных типов данных.

Целые числа и числа с плавающей точкой

Я не буду указывать диапазоны возможных значений, однако укажу информационную ёмкость в байтах.

.                       |MySQL                | PostgreSQL        
TINYINT                 |1 байт               |                   
SMALLINT                |2 байта              | 2 байта           
MEDIUMINT               |3 байта              |                   
INTEGER, INT            |4 байта              | 4 байта           
BIGINT                  |8 байт               | 8 байт            
FLOAT, DOUBLE, REAL     |4 или 8 байт         | 4 или 8 байт      
DECIMAL, NUMERIC        |65 десятичных знаков | без ограничений   
SERIAL, BIGSERIAL       |8 байт               | 4 или 8 байт
BIT                     |он есть              | он есть

Примечание:
1) В MySQL есть поддержка UNSIGNED типов, однако это не входит в стандарт SQL.
2) Для типов с плавающей точкой PostgreSQL использует формат стандарта IEEE 754, поэтому можно хранить значения +Inf, -Inf и NaN, однако использовать эти значения в математических операциях не выйдет.


Как видно из таблицы, типы данных практически идентичны для двух СУБД. Различия состоят в том, что MySQL позволяет более детально использовать доступную память, но при этом работа с числами в символьном представлении ограничена 65 цифрами. Я не вижу ни одного практического применения числам с таким количеством знаков, потому можно считать что возможности MySQL и PostgreSQL в данном разделе идентичны.

Строки и данные

Размеры указываются в байтах. Не забывайте, что для одного символа UTF-8 может использоваться от 1 до 4 байт.

.                       | MySQL           | PostgreSQL
BINARY, CHAR            | 255             | 10485760
VARCHAR, VARBINARY      | 65535*          | 10485760
TINYBLOB,TINYTEXT       | 2^8             | 
BLOB, TEXT, bytea       | 2^16            | не ограничен
MEDIUMBLOB              | 2^24            | 
LONGBLOB                | 2^32            |

* ограничение вызвано максимальной длиной строки, равной 65535 байтам, но в реальности максимальная длинна гораздо меньше.


Из таблицы видно, что по ёмкости строковые типы данных в двух СУБД практически не различаются, и снова MySQL позволяет более детально контролировать формат хранения данных на жёстком диске. Однако эта гибкость MySQL вводит две небольшие проблемы на этапе проектирования: сумятицу в типах и размерах данных.

Чтобы не быть голословным приведу пример — хранение данных пользователя. Предположим, что нам потребовалось хранить о пользователе не только его имя, фамилию и отчество, но адрес и телефоны, да мало ли что мы можем предложить хранить пользователю в своём профиле. С точки зрения SQL для этого должны использоваться типы CHAR и VARCHAR. И вот тут в MySQL приходится решать какая максимальная длинна у фамилии, какая максимальная длинна у имени, какая максимальная длинна у адреса, ибо на всё про всё дано 65535 байт. В то же время в PostgreSQL мы просто указываем в качестве типа для всех столбцов таблицы VARCHAR, куда мы в случае необходимости можем уложить гораздо больше данных, чем нам позволяет MySQL. (Попрошу не предлагать использовать TEXT в MySQL для этих целей.)

Дата и время

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

Нестандартные типы

Для желающих PostgreSQL предлагает целую группу типов данных для работы, которые напрочь отстутствуют в MySQL: массивы, структуры, типы для хранения IP и MAC адресов, и даже типы для хранения параметров геометрических фигур. Желающие могут самостоятельно ознакомиться с типами данных PostgreSQL.

Выводы по типам данных

1) Типы данных, предлагаемые двумя СУБД, с функциональной точки зрения идентичны.
2) При помощи этих типов можно хранить данные в любой из СУБД, однако в MySQL разработчик вынужден на самом начальном этапе проектирования искуственно ограничивать длинну строковых данных, что не сказывается положительно на удобстве пользования системой.
3) Использование нестандартных типов в PostgreSQL позволяет довольно сильно упростить разработку, однако усложнит переход на другую СУБД.
4) MySQL позволяет точно контролировать структуру хранимых данных, однако при этом жертвуется удобством разработчика.

Возможности управления данными

Здесь я хочу сравнить две СУБД с точки зрения дополнительного функционала предлагаемого разработчику. Часть этого функционала включена в стандарт SQL.

Хранимые процедуры

Начнём с простейшего — хранимые процедуры. Грубо говоря, в MySQL вообще нет функционала хранимых процедур. Если выражаться более точно, то вообще они есть, но довольно условны. Так, например, при включённой репликации хранимые процедуры могут быть только readonly. Так что довольно популярная схема ограничения прав пользователя через хранимые процедуры вовсе не реализуема на MySQL.

Индексы и ключи

На этом фронте MySQL тоже не блещет своими возможностями. Ограничение в 1000 байт на размер ключа — куда это годится? Допустим, я разрешаю своим пользователям создавать учётные записи на любом языке (UTF-8). В качестве максимальной длинны логина я выбираю 512 символов. Так как логины должны быть уникальными, прихожу к выводу, что нужно наложить уникальный ключ на столбец, и, как выясняется, не могу, ибо ключ не вписывается в 1000 байт. Приходится идти на уступки и делать уникальность только по первым 333 символам. Кто не верит, может самостоятельно посмотреть на результат create table t( t varchar(512), key(t)) character set = utf8.
PostgreSQL такими комплексами не страдает, а просто делает уникальный ключ необходимого размера.

Проверка данных на этапе добавления

В стандарте SQL была предусмотрена инструкция CHECK, которая задаёт выражение, которому должны удовлетворять данные добавляемые в таблицу. В руководстве MySQL об этой инструкции сказано предельно просто «The CHECK clause is parsed but ignored by all storage engines.» Больше добавить мне к этому нечего, в постгре, как и ожидалось, всё в порядке.

Транзакции и внешние ключи

По умолчанию в MySQL для таблиц используется движок MyISAM, который не поддерживает ни транзакций ни внешних ключей. Это сложилось исторически и у такого подхода есть оправдание, если рассматривать работу с БД с точки производительности. Можно просто заметить, что, если вам нужны транзакции и внешние ключи, то использование storage engine InnoDB обязательно. Естественно в PostgreSQL транзакции и вешние ключи полностью функциональны.

Выводы

MySQL и PostgreSQL — это системы управления базами данных, перед которыми стоят разные задачи и стоит чётко понимать, в чём их разница. В качестве практических рекомендаций могу сказать, что MySQL показывает своё преимущество в области HighLoad, однако требует более внимательного подхода со стороны разработчика, а также накладывает довольно серьёзные ограничения на хранимые данные и на функциолнал СУБД.

Вобщем, для проектов не ориентированных на многомиллионную посещаемость, а также в академических целях я рекомендую использовать PostgreSQL. Это позволит сильно упростить разработку и отладку приложений. MySQL показывает своё преимущество исключительно на базах данных с большим количеством простых однотабличных запросов и требует к себе пристального внимания со стороны разработчкиа.


_________
Текст подготовлен в ХабраРедакторе

Не используйте 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

Правильный бекап MySQL

Задача
Орагнизовать резервное копирование базы данных на Windows машину не имея непосредственного доступа к серверу MySQL.

Решение
Для бекапа будут использоваться две утилиты:

• Putty - лучший консольный SSH клиент для Windows.
• MySQL Administrator - входт в состав MySQL GUI Tools и доступен на сайте http://dev.mysql.com

Основная функция putty - предоставить доступ к командной строке сервера. Второй предоставляемой им возможностью, которую я буду использовать, является тунелирование соединений. Не буду вдаваться в подробности этого понятия. Скажу, что тунелирование поможет мне перенести интерфейс к базе данных на Windows машину так, что мне останется только сделать бекап с локальной машины. Для непосредственного бекапа я буду использовать MySQL Administrator.

Запускаю putty, в разделе Session указываю адрес ApacheSrv. В разделе Tunnels указываю порт источник 5000 и назначение mysql.host.com:3306. Нажимаю кнопку Open, ввожу запрашиваемые логин и пароль. С этого момента начинает действовать тунелирование. Другими словами, на 5000 порту Windows машины теперь действует точная копия MySQL сервера, которая будет считать, что к ней присоединяется ApacheSrv.

Запускаю MySQL Administartor, указываю имя хоста localhost (Windows машина) , порт 5000, а также имя пользователя и пароль к БД. После соединения с базой данных выбираю Backup, New Project. Ввожу имя проекта My backup, выбираю базу данных для бекапа и нажимаю на кнопку ">", для того чтобы MySQL Administrator получил список таблиц в базе.

Нажимаю Execute Backup Now и указываю, куда мне сохранить бекап. Процесс может занять несколько часов. По окончании должно высветиться окно The Backup was finished successfully.
Для восстановления вместо Backup надо выбрать Restore и указать на сохранённый файл.

Заметки

• Имя SSH сервера, а также, логин и пароль часто совпадают с параметрами доступа по FTP.
• Адрес MySQL сервера, имя пользователя и пароль можно посмотреть в конфигурационных файлах ваших скриптов, или же обратиться к хостеру.
• Перед бекапом надо обязательно остановить сервис, который работает с базой данных. Иначе восстановление данных не гарантировано.
  
• Файлы бекапа базы данных, как правило, хорошо поддаются сжатию, так что хранить их удобнее в сжатом виде.
• Бекап необходимо проводить регулярно, и копии хранить в безопасном месте. Хранить копии на рабочей машине небезопасно. Лучше использовать внешние носители.
• Использование Web интерфейсов, таких кaк phpMyAdmin, позволяет орагнизовать бекап и восстановление только маленьких баз данных. Предложенный мной способ прекрасно справляется с гигабайтными объёмами.
• Не имеет значения сколько место вам предоставляет хостер. Для такого бекапа не портебуется дополнительного места.
  

Установка программ в Linux из под обычного пользователя

[aeremihin@apollo ~]$ wget http://download.insecure.org/nmap/dist/nmap-4.53.tgz
[aeremihin@apollo ~]$ mkdir src
[aeremihin@apollo ~]$ mkdir install
[aeremihin@apollo ~]$ mv nmap-4.53.tgz src
[aeremihin@apollo ~]$ cd src
[aeremihin@apollo src]$ tar -xf nmap-4.53.tgz
[aeremihin@apollo src]$ cd nmap-4.53
[aeremihin@apollo nmap-4.53]$ mkdir ~/install/nmap
[aeremihin@apollo nmap-4.53]$ ./configure --prefix=$HOME/install/nmap
[aeremihin@apollo nmap-4.53]$ make
[aeremihin@apollo nmap-4.53]$ make install
[aeremihin@apollo nmap-4.53]$ $HOME/install/nmap/bin/nmap www.google.ru

Заметки:

1. В директории ~/src будут находятся исходные коды, там же производится сборка программ.
2. В директории ~/install будут находятся установленные программы.
3. В директорию ~/install/nmap будет установлена программа nmap.
4. Директория для установки указывается в строке ./configure .
5. При указании имени папки слеш на конце не ставится.
6. Аналогичным образом рекомендую устанавливать публичные службы.