Несколько интересных особенностей MySQL

    В не очень далеком прошлом мне пришлось покопаться немного в исходном коде MySQL, и разобраться в некоторых аспектах его работы. В ходе работы лопаткой, и эксперимeнтов, я наткнулся на несколько очень интересных особенностей, часть из которых просто забавна, а в случае некоторых бывает очень интересно понять, чем руководствовался программист, который принимал решение сделать именно так.

    Начнем с такого интересного типа, как ENUM.

    mysql> CREATE TABLE enums(a ENUM('c', 'a', 'b'), b INT, KEY(a));
    Query OK, 0 rows affected (0.36 sec)
    
    mysql> INSERT INTO enums VALUES('a', 1), ('b', 1), ('c', 1);
    Query OK, 3 rows affected (0.05 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    


    Итак, у нас есть таблица, в ней есть два столбца. У первого, a, тип ENUM, у второго, b, INT. В таблице три строки, у всех трех значение b равно 1. Интересно, чему равны минимальный и максимальный элементы в столбце a?

    mysql> SELECT MIN(a), MAX(a) FROM enums;
    +--------+--------+
    | MIN(a) | MAX(a) |
    +--------+--------+
    | c      | b      |
    +--------+--------+
    1 row in set (0.00 sec)
    


    Кажется странным, было бы разумно, если бы самым маленьким был 'a', а самым большим — 'c'.
    А что если выбрать минимум и максимум только среди тех строк, где b = 1? То есть, среди всех строк?

    mysql> SELECT MIN(a), MAX(a) FROM enums WHERE b = 1;
    +--------+--------+
    | MIN(a) | MAX(a) |
    +--------+--------+
    | a      | c      |
    +--------+--------+
    1 row in set (0.00 sec)
    


    Вот так мы заставили MySQL поменять свое мнение о том, как сравнивать поля в ENUM, просто добавив предикат.
    Разгадка такого поведения заключается в том, что в первом случае MySQL использует индекс, а во втором нет. Это, конечно, не объясняет, почему MySQL сравнивает ENUMы по разному для сортировки в индексе, и при обычном сравнении.

    Второй пример проще и лаконичнее:

    mysql> (SELECT * FROM moo LIMIT 1) LIMIT 2;
    +------+
    | a    |
    +------+
    |    1 |
    |    2 |
    +------+
    2 rows in set (0.00 sec)
    


    Когда я показал этот запрос своему коллеге, который занимается разработкой парсера SQL, его вопрос был не «почему этот запрос возвращает две строки», а «как надо написать SQL парсер так, чтобы такой запрос был валидным, без того, чтобы написать правило, специально разрешающее такой запрос».

    Интересно, что далеко не любой SELECT в скобках сработает, в частности, UNION в скобках — это синтаксическая ошибка:

    mysql> (SELECT * FROM moo UNION ALL SELECT * FROM hru) LIMIT 2;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL SELECT * FROM hru) LIMIT 2' at line 1
    


    Еще несколько интересных примеров под катом

    Вообще, с UNION и LIMIT далеко за примером странного поведения ходить не надо:

    mysql> 
        -> SELECT 1 FROM moo LIMIT 1
        ->     UNION ALL
        -> SELECT 1 FROM hru LIMIT 1;
    +---+
    | 1 |
    +---+
    | 1 |
    +---+
    1 row in set (0.00 sec)
    


    Внезапно, вернулась только одна строка, хотя обе таблицы не пусты. Потому что второй LIMIT принадлежит всему запросу, а не только правой части UNION.

    Тут надо рассказать о такой вещи, как shift-reduce conflict. В современных базах данных с открытым кодом парсер очень часто написан на bison. Такой парсер является так называемым L1-парсером, что значит, что парсер должен понять предназначение очередного токена, посмотрев не далее чем на один токен вперед. Например, в запросе выше смотря на слово LIMIT парсер не может понять, принадлежит этот LIMIT к второму запросу, или ко всему UNION. Когда правила написаны так, что возможны ситуации, при которых понять назначение токена посмотрев только на следующий токен нельзя, это называется shift-reduce conflict. В этом случае парсер будет выбирать решение базируясь на определенном наборе правил. Это очень плохо, потому что это приводит к тому, что вполне нормальные запросы приводят к ошибкам. Что, если я хочу в предыдущем запросе сделать LIMIT и второму SELECT, и UNION?

    mysql>   SELECT 1 FROM moo
        ->       UNION ALL
        ->   SELECT 1 FROM hru LIMIT 1
        -> LIMIT 2;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 2' at line 4
    


    Так сделать нельзя, из-за shift-reduce конфликта. Смотря на первый LIMIT парсер еще не знает, что впереди будет второй, и ошибочно полагает, что первый лимит относится ко всему запросу.
    В PostgreSQL в парсере shift-reduce conflicts нет совсем. Конкретно эта ситуация там разрешена за счет того, что LIMIT может быть только у UNION, но не у SELECT'ов которые он объединяет.
    В MySQL таких конфликтов больше чем 160. Это поражает воображение, потому что это значит, что есть 160 мест, где парсер может не правильно понять, что от него хотят.

    Хороший пример такого конфликта — это соединения. Как известно, в MySQL поддерживаются CROSS JOINs, у которых нет предиката, и INNER JOINs, у которых предикат есть. Вообще говоря, CROSS JOIN и INNER JOIN — это разные вещи, но в MySQL это синонимы. То есть у INNER JOIN может не быть предиката, а у CROSS JOIN он может быть. В частности, это приводит к интересной ошибке:

    mysql> SELECT * FROM     
        ->     moo
        ->     INNER JOIN
        ->        hru
        ->        INNER JOIN
        ->        baa
        ->        ON hru.a = baa.a
        ->     ON moo.a = hru.a
        -> ;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON moo.a = hru.a' at line 8
    


    В момент, когда парсер видит первое ON, он еще не знает, что впереди его ждет второе, и сталкивается с выбором: либо это ON для hru и baa, либо hru и baa соединяются без предиката, а текущий ON — это ON для moo и результата соединения hru и baa. Парсер ошибочно выбирает второе, что приводит к совершенно не нужной в этой ситуации ошибке. Если INNER JOIN заменить на LEFT JOIN, для которого варианта без предиката не сущестует, то запрос выполнится:

    mysql> SELECT * FROM     
        ->     moo
        ->     LEFT JOIN
        ->        hru
        ->        LEFT JOIN
        ->        baa
        ->        ON hru.a = baa.a
        ->     ON moo.a = hru.a
        -> ;
    +------+------+------+------+
    | a    | a    | b    | a    |
    +------+------+------+------+
    |    1 |    1 | 1    |    1 |
    |    2 |    2 | 2    |    2 |
    +------+------+------+------+
    2 rows in set (0.00 sec)


    Тут самое интересное, это то, что в Bison надо руками указать прямо в коде количество shift-reduce conflicts, иначе код не скомпилируется. То есть в какой-то момент времени один из программистов в MySQL сделал CROSS JOIN и INNER JOIN синонимами, что уже само по себе не имеет смысла, после чего попытался собрать код, и он не собрался с ошибкой компиляции, предупреждающей, что парсер теперь не сможет распарсить определенные запросы. На что тот программист, вместо того, чтобы сделать все правильно, нашел константу, указывающую на количество ошибок в парсере, и увеличил ее.

    Хотя если говорить о том, какие интересные решения иногда программисты в MySQL принимают, то лучше всего вспомнить вот эту историю:
    http://bugs.mysql.com/bug.php?id=27877
    В ней один из программистов сознательно сделал в collation по умолчанию для utf8 букву 's' равной символу 'ß'. Это очень иронично, потому что единственный язык, в котором это хотя бы отдаленно могло бы иметь смысл — это немецкий, но именно это изменение делает этот collation совершенно не применимым к немецкому языку, потому что теперь строки, которые совершенно не равны друг другу, становятся равны.
    Это изменение было не только бесполезным, оно еще и сделало процесс перехода с 5.0 на 5.1 для баз данных с utf8 строками на немецком очень болезненным, потому что уникальные индексы внезапно начали содержать повторяющиеся элементы.

    Говоря о collations, я еще очень люблю вот такой пример:

    Пусть у нас есть таблица с тремя строками с разными collations:

    CREATE TABLE strings(
        swedish VARCHAR(100) COLLATE utf8_swedish_ci,
        spanish VARCHAR(100) COLLATE utf8_spanish_ci,
        bin VARCHAR(100) COLLATE utf8_bin
    );
    


    Выполним такой запрос:

    mysql> SELECT * FROM strings WHERE swedish > bin AND swedish < spanish;
    ERROR 1267 (HY000): Illegal mix of collations (utf8_swedish_ci,IMPLICIT) and (utf8_spanish_ci,IMPLICIT) for operation '<'
    


    MySQL разумно жалуется, что сравнивать swedish и spanish нельзя, потому что непонятно, как их сравнивать.
    Давайте напишем совершенно идентичный запрос:

    mysql> SELECT * FROM strings WHERE swedish BETWEEN bin AND spanish;
    Empty set (0.00 sec)
    


    Внезапно, запрос стал валидным, хотя он по прежднему должен сравнивать swedish и spanish строку. А если я хочу наоборот?

    mysql> SELECT * FROM strings WHERE swedish BETWEEN spanish AND bin;
    ERROR 1270 (HY000): Illegal mix of collations (utf8_swedish_ci,IMPLICIT), (utf8_spanish_ci,IMPLICIT), (utf8_bin,IMPLICIT) for operation 'between'
    


    А наоборот нельзя.
    Если покопаться в коде, то можно понять, что в MySQL BETWEEN реализован совершенно странным образом: если первый или второй парамерт имеют бинарный collation, то все строки будут сравниваться как бинарные, и collation будет проигнорирован. Но если бинарный collation у третьего аргумента, то такая же логика не применяется.

    Говоря о том, как странно работают функции в MySQL, завершим эту статью самым красивым примером.

    mysql> SELECT LEAST(9, 11);
    +--------------+
    | LEAST(9, 11) |
    +--------------+
    |            9 |
    +--------------+
    1 row in set (0.00 sec)
    


    Тут никаких сюрпризов

    mysql> SELECT LEAST("9", "11");
    +------------------+
    | LEAST("9", "11") |
    +------------------+
    | 11               |
    +------------------+
    1 row in set (0.00 sec)
    


    Это тоже разумно, строка 11 меньше чем 9. А что будет, если 11 прибавить к 11?

    mysql> SELECT LEAST("9", "11") + LEAST("9", "11");
    +-------------------------------------+
    | LEAST("9", "11") + LEAST("9", "11") |
    +-------------------------------------+
    |                                  18 |
    +-------------------------------------+
    1 row in set (0.00 sec)
    


    Конечно, 18. Получается, функция возвращает разное значение в зависимости от контекста! А можно ли заставить один и тот же LEAST вернуть три разных значения в зависимости от контекста? Оказывается, да

    mysql> SELECT LEAST("9e1", "110");
    +---------------------+
    | LEAST("9e1", "110") |
    +---------------------+
    | 110                 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT LEAST("9e1", "110") + 0;
    +-------------------------+
    | LEAST("9e1", "110") + 0 |
    +-------------------------+
    |                      90 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT LEAST("9e1", "110") & -1;
    +--------------------------+
    | LEAST("9e1", "110") & -1 |
    +--------------------------+
    |                        9 |
    +--------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS;
    +---------+------+------------------------------------------+
    | Level   | Code | Message                                  |
    +---------+------+------------------------------------------+
    | Warning | 1292 | Truncated incorrect INTEGER value: '9e1' |
    +---------+------+------------------------------------------+
    1 row in set (0.00 sec)
    


    Хотя тут надо сказать, что в одном случае мы встретили предупреждение. Но у нас все же получилось заставить один и тот же оператор с одними и теми же аргументами вернуть три разных значения.

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

    mysql> SELECT NULLIF(LEAST("9", "11"), "11") + 0;
    +------------------------------------+
    | NULLIF(LEAST("9", "11"), "11") + 0 |
    +------------------------------------+
    |                               NULL |
    +------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT NULLIF(LEAST("9", "11"), "12") + 0;
    +------------------------------------+
    | NULLIF(LEAST("9", "11"), "12") + 0 |
    +------------------------------------+
    |                                  9 |
    +------------------------------------+
    1 row in set (0.00 sec)
    


    В первом случае мы получили NULL, что говорит о том, что LEAST действительно равен строке «11». Во втором случае в таком же запросе, с такими же типами аргументов, но с другой константой в NULLIF мы получили значение 9! То есть при совершенно одинаковых типах параметров в первом случае LEAST вернул «11», а во втором — 9.
    Но можно сделать еще лучше:

    mysql> SELECT NULLIF(LEAST("9", "11"), "9") + 0;
    +-----------------------------------+
    | NULLIF(LEAST("9", "11"), "9") + 0 |
    +-----------------------------------+
    |                                 9 |
    +-----------------------------------+
    1 row in set (0.00 sec)
    


    В этом запросе LEAST вернул что-то отличное от строки «9» (иначе бы NULLIF вернул NULL), однако он в тоже самое время вернул строку «9»!
    Если посмотреть в код, то это действительно то, что происходит. LEAST выполняется дважды, первый раз сравнивая параметры, как строки, а второй раз — как целые числа.
    Поделиться публикацией

    Похожие публикации

    Комментарии 95

      +18
      Это замечательно!

      Люблю оптимизировать MySQL переходом на PostgreSQL.
      Ярчайшая компиляция примеров в качестве аргументов.
        +1
        Ага. Я вот тоже заоптимизировал таким способом.
          +1
          Эта компиляция показывает как при некоторых запросах MySQL может вести себя странно. Лично я с такими проблемами ни разу не столкнулся до сих пор… Поэтому чтобы попробовать PostgreSQL хочется чего-то еще… Не видели какого-нибудь why to choose PostgreSQL?
            +4
            Просто бери и пробуй :)
              +1
              В том-то и проблема, что взял, попробовал и не понял надо оно мне или нет. Все немного не так, как раньше. "\dt", система пользователей… А явных плюсов уцепить за несколько дней использования не удалось.
                +2
                Ну раз так… Стабильнее, больше примочек, меньше утечек, расширяемость, нет фигни с MyISAM/InnoDB…
                  +2
                  А в чем фигня с myisam/innodb? У вас падает mysql?
                    +2
                    У меня падал. Однажды была история, когда раз в неделю MySQL стабильно писал corrupt и падал. При этом PostgreSQL на той же машине вел себя безупречно. Начали разбираться. Через неделю только в одном месте прочли, что возможно, виноват аппаратный RAID со встроенным кешем. Попробовали его отключить — и все заработало как надо. Это конечно не чистый ответ на Ваш вопрос. :) Но бяка происходила на коммерческом сервисе, так что «осадочек остался». :)

                    В целом — ощущение от PostgrSQL как от более «взрослой», серьезной системы.

                      0
                      Ну вероятно если проблема в аппратаном кеше то такие же проблемы могут вылезти и в постгресе все таки.
                  +16
                  На вскидку:

                  — Нормальная консоль с autocompleate. Удобство работы.
                  — Огромное количество типов данных на все случаи жизни и функций для манипуляций с ними. Например, можно проверить входит ли ip-адрес в сеть. Или, например, очень удобно работать с гео-данными.
                  — Быстрая настройка и перенастройка репликации вообще без гемороя и без локов мастер-базы.
                  — Тёплая перезагрузка (без сброса кэшей на диск!) — с версии 9.4
                  — Надёжность хранилища из коробки, без глубокого тюнинга
                  — Полнотекстовый поиск на нормальном движке (относительно myisam)
                  — Более высокий порог вхождения для админов: подкрутил несколько всем известных крутилок в базе, несколько в ОС — и имеем базу, которая выдерживает 4K writes/s с объемом данных 350ГБ на SSD и не падает просто так. У меня есть сервера, которые буквально годами не перезагружались и просто работают.
                  — Высокое качество кода. Я имел неприятность копаться в коде mysql — это ужас какой-то. У Постгри новый резил можно смело ставить в продакшн.

                  Это только что вспомнил.
                  Я могу быть субъективным. Но просто попробуйте.
                    0
                    Мне пока из первого, что бесило в MySQL — понравилась возможность добавления столбцов и индексов без table locking.
                    А то не редко бывали случаи, когда долбаный ALTER TABLE на > 1 млн. записей убивал весь продакшн на часы.
                    Не подскажите где посмотреть про «известные крутилки в базе»?
                    Т.к. в MySQL давно про всякие innodb_flush_method, atrx и прочее в курсе, а вот PostgreSQL в это плане пока темный лес. Хотелось бы высокий порог вхождения сделать менее шершавым :)
                      +3
                      Основное в конфиге:

                      shared_buffers — дать половину оперативки. Потюнить в системе файловый кэш, чтобы уменьшить шанс двойного кэширования.
                      work_mem = 64MB (поставить для начала) и смотреть за созданием temp-файлов. Если они есть — увеличивать
                      temp_buffers = 32MB
                      maintenance_work_mem = 2GB
                      max_stack_depth = 4MB

                      В системе (FreeBSD):
                      # /boot/loader.conf:
                      kern.ipc.semmns=1024
                      kern.ipc.semmni=256

                      # /etc/sysctl.conf:
                      kern.ipc.shm_use_phys=1
                      kern.ipc.shmall=8605532
                      kern.ipc.shmmax=35248259072 # если памяти 64 гига и мы хотим половину дать постгресу.

                      + пускать всех только через pgbouncer.
                      — В общем-то, для начала этого хватит чтобы система уже быстро работала на больших объемах данных.
                      Если будет проседать дисковый i/o, нужно смотреть в сторону:

                      synchronous_commit = off
                      checkpoint_timeout
                      checkpoint_completion_target
                      (размазываем чекпоинты по времени)
                        0
                        Большое спасибо, буду разбираться.
                          0
                          ой ой ой такие советы то давать! =)
                            0
                            synchronous_commit = off

                            разве не отключит гарантию сохранности данных?
                              0
                              разве не отключит гарантию сохранности данных?


                              Конечно, отключит.
                                +8
                                Приводить такую настройку как типовой конфиг для человека, которому не хочется самому разбираться в тонкостях, жестого =)
                                  –2
                                  Ну я же и говорю «смотреть в сторону» :)
                            0
                            С помощью openarkkit можно делать не блокирующие операции над MySQL.
                              0
                              А maatkit не пробовали — там инструментов побольше, может и вам что подойдет?
                                0
                                Не пробовал. Спасибо, возьму на заметку.
                                  0
                                  Извиняюсь, похоже я отстал от жизни чутка.

                                  Maatkit has become part of Percona Toolkit, and there will be no further development or releases of Maatkit separately from Percona Toolkit.


                                  Так что теперь видимо все здесь percona.com
                              0
                              0
                              — Нормальная консоль с autocompleate. Удобство работы.

                              Это всего лишь инструмент, не СУБД.
                              С MySQL использую SQLYog или Workbench.
                                0
                                Нормальная консоль с autocompleate.

                                а вы режим auto-rehash пробовали активировать?
                                  0
                                  а вы режим auto-rehash пробовали активировать?


                                  У меня почему-то с ним клиент вообще не хотел запускаться на одной базе. Не зря наверное его отключают.
                                    0
                                    Скорее всего, что в базе было много таблиц или же они были блокированы во время соединения: auto-rehash нужно считать имена таблиц, чтобы потом их подставлять в auto-complete
                                  +1
                                  Очень интересно из отсутствующего в Вашем списке — схемы! Это отличный вариант изолировать для различных модулей ПО и структурировать таблицы базы данных, при этом оставив возможность обращаться и комбинировать таблицы разных схем.
                                    +1
                                    И еще — explain! Это просто фантастика для поиска узких мест.
                                      0
                                      Эээ, так в MySQL же database — это синоним schema. Вы не могли бы объяснить поподробнее как Вы изолируете модули и структурируете таблицы и почему того же нельзя сделать в MySQL?
                                        –1
                                        database — это объек, изолированный от других database. А схема позволяет им взаимодействовать. Например, если есть таблицы вида

                                        billing
                                        — daily
                                        — weekly
                                        — monthly
                                        users
                                        — users

                                        то в postgress можно сделать так (примерный запрос — возможно с ошбками):
                                        SELECT uu.user_id, bd.bill FROM users.users uu
                                        JOIN billing.daily bd ON bd.user_id=uu.user_id
                                        WHERE user_id=777;

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

                                        Ну и плюс доступы можно по разному на группы таблиц ставить. Например, для отдельного девелопера дать доступ в users.*, но закрыть в biling.*
                                          +2
                                          Так в MySQL тоже можно сделать такой запрос. Не нужно никаких префиксов:

                                          mysql> create schema users;
                                          Query OK, 1 row affected (0.01 sec)
                                          
                                          mysql> create table users.users(user_id int);
                                          Query OK, 0 rows affected (0.10 sec)
                                          
                                          mysql> insert into users.users values(1);
                                          Query OK, 1 row affected (0.01 sec)
                                          
                                          mysql> create schema billing;
                                          Query OK, 1 row affected (0.00 sec)
                                          
                                          mysql> create table billing.daily(user_id int, bill int);
                                          Query OK, 0 rows affected (0.07 sec)
                                          
                                          mysql> insert into billing.daily values(1,1);
                                          Query OK, 1 row affected (0.01 sec)
                                          
                                          mysql> SELECT uu.user_id, bd.bill FROM users.users uu  JOIN billing.daily bd ON bd.user_id=uu.user_id WHERE bd.user_id=1;
                                          +---------+------+
                                          | user_id | bill |
                                          +---------+------+
                                          |       1 |    1 |
                                          +---------+------+
                                          1 row in set (0.01 sec)
                                          


                                          > Например, для отдельного девелопера дать доступ в users.*, но закрыть в biling.*

                                          Это тоже можно в MySQL:

                                          mysql> grant all on billing.* to billing_user@'%';
                                          Query OK, 0 rows affected (0.01 sec)
                                          
                                          mysql> grant all on users.* to users_user@'%';
                                          Query OK, 0 rows affected (0.00 sec)
                                          


                                          И табличкам можно права назначить, и колонкам. Всё это поддерживается с самых первых версий. С 3.23 уж точно (3.23. была актуальна более 10 лет назад, сейчас актуальная версия 5.6.22)
                                            +3
                                            У меня такое ощущение, что в целом критика MySQL здесь актуальна для древних версий вроде 3.x. Читаешь и диву даешься.
                                              0
                                              Хм. Забавно. Не знал. Наличие схем как раз и стало основной причиной моего ухода в PostgreSQL. Но видимо есть какие-то нюансы.

                                              1. Мне не удалось сейчас найти достаточно информации о схемах в MySQL, по крайней мере чтобы понять их отличие от понятия database. В «мире» PostgreSQL информации много и очень «тонкой» и детальной. То есть в любом случае, в PG схемы гораздо боле распространены.
                                              2. Насколько я понял, в MySQL — схема просто синоним database. Но что это такое и чем это отличается от database — непонятно
                                              3. Для PostgreSQL в Интернете куча описаний, как написать ActiveRecord применительно к схемам Postgres. Но нет описания, как применить схемы MySQL в ActiveRecord.


                                              4. Видимо, есть все же какие-то различия, и предположу, что существенные. Был бы рад почитать мнение того, кто детально разбирался с тем и другим.

                                                Наиболее вменяемое, но ничего не объясняющее толком объяснение я нашел вот тут: www.estelnetcomputing.com/index.php?/archives/10-Database-vs.-Schema-Definition-in-Mysql-vs.-Postgres.html

                                                In mysql a database is a schema. The two words can be used interchangeably in most commands. For instance, you could say create database dbname, or create schema dbname and achieve the same result.

                                                Postgres has a different concept of schema. It is a namespace that contains tables, functions, operators and data types. It is essentially a layer between the database and the tables. In postgres, a database may contain many schemas and those schemas may contain the same tables or different ones. The «public» schema is the default schema in a database.
                                                0
                                                Вы же совершенно правильно пишете: в MySQL нет отличия понятия database от schema. Я бы даже грубо сказала, что в MySQL нет database в том виде, в котором они есть в PostgreSQL или в Oracle. В MySQL только схемы. И да, их принято называть database =)

                                                То, что в PostgreSQL называется database — это сама инсталляция MySQL. То есть, чтобы начать работать с PostgreSQL нужно его установить, затем создать базу, а уже потом создавать схемы. В MySQL этапа создания базы не существует. Если вы хотите имитировать PostgreSQL-базы в MySQL просто запустите несколько instance mysqld на одной машине (с разными портами, datadir и т.п.)
                                                  +1
                                                  В Постгресе немного сложнее иерархия, а именно: cluster (собственно инсталляция постгрес-сервера, он один) → database (много в кластере) → schema (много в базе).
                                              0
                                              то в postgress можно сделать так (примерный запрос — возможно с ошбками):
                                              SELECT uu.user_id, bd.bill FROM users.users uu
                                              JOIN billing.daily bd ON bd.user_id=uu.user_id
                                              WHERE user_id=777;


                                              То же самое и в MySQL.
                                          –1
                                          1)Сказать честно, редко сиду в консоли что в постгресе, что в мускуле.
                                          2)Ну входит айпи в подсеть можно и в мускуле через числовое хранение. Большой плюс от типов данных в постгресе был от постгиса и работой с картой, больше не вспомню насущных улучшений.
                                          3)Ой не все так просто там, вообще реляционная модель легко реплику не делает в принципе.
                                          4)В перконе и наверно в мариидб тоже есть.
                                          5)Сказать честно в штатном режиме ни разу не встречал проблем с надежностью хранения ни в постгре ни в мускуле.
                                          6)Вроде и там и там есть полнотекстовый поиск, в чем заключается ваше на нормальном движке?
                                          7)Не очень понимаю как может быть такая маленькая бд при 4 тысячах записей в секунду в неё, в целом это не удивительно и для мускула. У меня почти все сервера не перегружаются и просто работают если конечно проекты не в разработке и не требуют апгрейда ядра.
                                          8)Не смотрел, но вроде и мускульные новые релизы ставят в продакшен.
                                            0
                                            Справедливости ради:

                                            > — Нормальная консоль с autocompleate. Удобство работы.

                                            autocomplete есть в MySQL

                                            > — Быстрая настройка и перенастройка репликации вообще без гемороя и без локов мастер-базы.

                                            В первый раз слышу, что в MySQL нужно лочить мастер-базу при настроке или перенастройке репликации.

                                            > — Полнотекстовый поиск на нормальном движке (относительно myisam)

                                            В 5.6+ InnoDB поддерживает full-text
                                            –3
                                            Тоже пробовал postgres.
                                            Из неприятных моментов:
                                            — все названия таблиц, колонок только маленькими буквами. Можно ли как-то его заставить использовать camelCase названия я так и не нашел. В Sql Server это можно, в Oracle можно, в MySql можно, почему в postgres нельзя — загадка.
                                            — понятие collation такое ощущение что в postgres-е отсутствует, т.е. производить case insensitive сравнение строк легко нельзя (нужно либо использовать специальный тип, либо при сравнении использовать функции приведения к lowercase или uppercase)
                                            — значительно меньшее кол-во IDE для разработки чем для того же MySql
                                            — немного непривычный синтаксис для функций, отсутствие привычных хранимых процедур
                                            Что очень понравилось:
                                            — можно делать filtered индексы в отличие от MySql
                                            — можно писать свои функции, причем на очень широком спектре языков — вплоть до javascript — в MySql такой возможности просто нет, а жалко.
                                              +4
                                              — все названия таблиц, колонок только маленькими буквами.

                                              они по умолчанию приводятся к нижнему регистру. Если хочется с большими буквами то надо в кавычках писать, типа
                                              CREATE TABLE "myBigTable" ( "firstColumn" int ....);


                                              но от этого очень много заморок. Когда начинаешь писать в консоли это заставляет делать много кавычек:
                                              SELECT "firstColumn" as "КолоНка Любимая" FROM "схемаНаша"."табличкО";


                                              все названия всех элементов (таблиц, схем, колонок, триггеров, функций, типов… всего) можно называть и русскими буквами, и большими, и маленькими, и с пробелами… но не принято как-то… спасает "_".
                                                0
                                                Для полноты картины — если название в кавычках (например, «myBigTable»), то использовать его можно только именно так и никак иначе — попытки использовать «mybigtable», myBigTable (без кавычек) или mybigtable будут приводить к ошибкам. Поэтому, наверное, такой стиль совсем не популярен.
                                                В MySql, кстати, тоже названия таблиц приводятся к нижнему регистру во многих ОС (например, Windows). Но в нем есть настройка — lower_case_table_names, которую можно изменить и в названии можно станет использовать большие буквы.
                                                +4
                                                Вот как раз с хранимыми процедурами в PostgreSQL все шикарно.
                                                PL/pgSQL — SQL Procedural Language
                                                PL/Tcl — Tcl Procedural Language
                                                PL/Perl — Perl Procedural Language
                                                PL/Python — Python Procedural Language
                                                Ну и наконец, для извращенцев есть PL/Java
                                                  +1
                                                  Также, возможно создавать хранимые процедуры на C (в виде расширений).
                                                +2
                                                Лично для меня огромным плюсом постгреса является его умение объединять индексы. Во-первых, отпадает необходимость поддерживать актуальные составные индексы и строить новые для каждого нового вида выборки. Во-вторых в мускуле нужно следить за порядком указания полей в запросе, чтобы он пошел по индексу, а постгрес в этом плане гораздо умнее даже при составных индексах.
                                                  0
                                                  Но тем не менее в пострегсе составные индексы есть или нет?
                                                    +1
                                                    составные индексы есть.
                                                      0
                                                      Тогда разница в скорости какая-либо есть — между двумя раздельными и одним составным? Я на эту тему информации не видел.
                                                        0
                                                        Разницы нет (разве что составным не используется для условий, где выбирается только одно поле). Например, иногда нужны отдельно индексы на каждое поле (если в where усчаствует только один) + составной с уникальностью для целостности данных (связь has-many-to-many). Пример: связь таблиц users и companies через companies_users, которая содержит company_id и user_id поля (нельзя допускать, чтобы [company_id, user_id] повторялись)

                                                        # CREATE INDEX index_companies_users_on_company_id ON companies_users USING btree (company_id);
                                                        # CREATE INDEX index_companies_users_on_user_id ON companies_users USING btree (user_id);
                                                        # CREATE UNIQUE INDEX index_companies_users_on_company_id_and_user_id ON companies_users USING btree (company_id, user_id);
                                                        
                                                          +1
                                                          Разницы не может не быть принципиально. Для выборки используются разные алгоритмы, а значит сложность у них все-равно разная будет. Другое дело, что на очень большом кол-ве случаев в Postgres не нужен составной ключ, а вполне можно юзать два раздельных (по крайней мере у них так написано в книжке high performance Postgres). Но поиск по двум раздельным индексам в любом случае, насколько я понимаю, будет хуже поиска по составному индексу.
                                                            0
                                                            Я бы не сказал, что разница большая в алгоритмах. С одним (составным) индексом — Btree Index Scan, с N+1 — Bitmap Index Scan, что просто оптимизация Btree Index Scan: вместо того, чтобы зразу идти за данными по первому индексу, но выбирает все индексы по условию, сортирует в битмап структуре, и только потом идет за данными.

                                                            Отдельные индексы дают хорошую производительность для SELECT на больших данных, но при этом INSERT/UPDATE/DELETE немного проседает (нужно перестроить N+1 индекса, вместо одного композитного). Композитный экономит место на диске (опять же один индекс против N+1).

                                                            В примере я написал, что с помощью двух раздельных вы не сможете гарантировать уникальность по двум/трем/более полям — одна из основных причин создания такого индекса (выборка конечно тоже не маловажна).
                                                              0
                                                              (сообщение удалено)
                                                                0
                                                                Моя логика подсказывает, что найти записи в дереве в случае составного индекса как ни крути должно быть намного проще и быстрее, чем пройтись по двум индексам, отсортировать в обоих (добавлено: найденные) данные по физическом расположению, сделать битовые операции. Другое дело, в каком кол-ве случаев разница действительно важна.
                                                                  0
                                                                  Предположим у нас миллион записей в таблице, a = num / 1000, b = num % 1000. Тогда запрос WHERE a = X AND b = Y в случае составного индекса просто найдет одно число в индексе и вернет запись. Тот же запрос в случае двух индексов выберет тысячу записей для первого индекса, отсортирует их по адресу хранения, выберет тысячу записей из второго индекса, отсортирует их…
                                                                    0
                                                                    Да, тут будет получше для композитного. Но если рассмотреть запрос чуток посложнее «WHERE a = 5 AND b >= 42 AND c < 77», то композитный не даст огромной скорости (на больших данных), чем отдельные. В официальной документации показан пример и причина данного поведения: www.postgresql.org/docs/devel/static/indexes-multicolumn.html
                                                  –1
                                                  Не знаю как сейчас, пару лет назад с документацией по postrgresql было не очень, относительно мускула.
                                                +5
                                                Для себя такие приятности постгреса нашел:
                                                1) Как вы сами выше сказали, можно сделать ALTER TABLE на пару миллионов записей, и это не займет 4 часа. Единственная тяжелая операция в постгресе — CREATE INDEX. И даже она может быть выполнена без лока БД в бэкграунде.
                                                2) Запросы типа ALTER TABLE не коммитят автоматически транзакцию — забудьте о наполовину выполненных миграциях из-за ошибки в середине миграции.
                                                3) В целом субъективно планировщик запросов работает очень хорошо, ни разу не приходилось на каком-либо проекте заниматься оптимизацией порядка джойнов и т.п.
                                                4) EXPLAIN[ ANALYZE] это просто сказка — нате вам дерево плана запроса, нате вам ожидаемую и реальную стоимость каждого шага в понятном виде, а не этот трэш в MySQL.
                                                  0
                                                  Да, с этим уже столкнулся и лично, но спасибо, что еще раз отметили.
                                                    +3
                                                    Я когда постгревый EXPLAIN первый раз увидел — думал этого не может быть… после полушаманского мускульного — это было что-то!
                                                    +2
                                                    Приятный бонус в PostgreSQL — транзакционность DDL операций, реализованная с помощью Write-Ahead Log
                                                    • НЛО прилетело и опубликовало эту надпись здесь
                                                      0
                                                      А что, PostgreSQL вообще не имеет странностей, чтобы рекомендовать ее как панацею?
                                                      +3
                                                      Уже лет 10 прошло как я последний раз вымыл руки после mysql, а говнокод никуда не делся. Стабильность — признак мастерства.
                                                        +2
                                                        А у меня был один очень неприятный случай с MySQL

                                                        CREATE TABLE user_bak LIKE user;
                                                        BEGIN; -- начинаю транзакцию
                                                        UPDATE user_bak SET msg_disabled = 0;
                                                        DROP TABLE user;
                                                        


                                                        И на моменте DROP TABLE я внезапно получаю коммит транзакции! Оказывается в мане написано:
                                                        DROP TABLE automatically commits the current active transaction, unless you use the TEMPORARY keyword.


                                                        Поведение крайне неожиданное и мне очень повезло, что я отделялся лишь минутным даундаймом и легким испугом.
                                                          +2
                                                          Крайне неожиданно дропать таблицы в транзакции.
                                                            +5
                                                            Я привык к PostgreSQL, где реализована транзакционность DDL операций. Потому ничего зверского в деянии я не видел :)
                                                            Ну если уж нельзя делать DDL в транзакции — так поругайся и скажи, что нельзя. Это — адекватное-ожидаемое поведение, в отличие от безусловного коммита.
                                                          0
                                                          Одно из уникальных преимуществ MySQL — поддержка множества storage engine. И огромное комьюнити.
                                                          Использую PostgreSQL с 2005 года и радует что база с каждым релизом становится производительнее и надежнее. К тому же расширяемость типов данных, множество модулей и база часто выступает как полигон для отработки новых технологий распределенных СУБД, использования как column oriented DBMS и даже прототип PgOpenCL
                                                            0
                                                            SkidanovAlex, как понимаю, вы сейчас работаете в MemSQL?)
                                                            Чем продукт вашей компании лучше любого in memory data grid с поддержкой sql предикатов для выборки данных и c write-through в персистентный storage для durability?
                                                              +1
                                                              Смотря о какой конкретно комбинации in memory data grid и storage идет речь.
                                                              Ответил немного под спойлером
                                                              Скрытый текст
                                                              Наш основной in-memory engine заметно быстрее большинства конкурентов. Скорость чтений не проседает совершенно при параллельной записи, ALTER TABLE не блокирует запросы. Шардирование и репликация из коробки, транзакции на кластере. Можно гонять OLAP и OLTP параллельно, одно другому не будет мешать вообще никак.
                                                              Помимо in-memory у нас есть column store, который оптимизирован больше для OLAP, сильно сжимает данные, поддерживает sort keys. Может все, что может Red Shift, и немного больше, и не ограничен оперативной памятью, в отличие от in-memory engine. При этом in-memory и column store спрятаны под одним интерфейсом, поэтому можно в одну БД скинуть исторические данные и совсем свежие, и гонять как запросы по свежим данным, которые будут десятками тысяч в секунду выполняться, так и тяжелые аналитические запросы по историческим данным, с одной схемой, без зоопарка технологий.
                                                              Решений на рынке, у которых все это есть, мало, большинство на практике проигрывают по производительности.
                                                              Но вообще я не лучший человек для того, чтобы задавать такие вопросы. Я могу ответить на любые более технические вопросы, а по-поводу преимуществ на рынке надо спрашивать тех, кто больше работает с клиентами, знает их проблемы, и разворачивает у них MemSQL.

                                                                0
                                                                Спасибо. Как пример:
                                                                oracle coherence как IMDG + Oracle database (CacheStore)
                                                                hazelcast + PostgreSQL (MapStore/MapLoader)
                                                                infinispan + PostgreSQL
                                                              +3
                                                              Я одного не понимаю. Зачем они во время исполнения функций учитывают контекст вызова функций? Где это действительно может быть полезным?
                                                                –1
                                                                Хватит это терпеть!
                                                                  0
                                                                  Отлично иллюстрированные примеры почему разработчик на MySQL не пересядет на другую базу (хотя на PostgreSQL вроде без проблем переходится).
                                                                  С enum как раз всё боль-мень понятно, это искусственный тип, и вроде даже сортировка по номеру при об'явлении документирована, непонятно только почему с where он вдруг решил посмотреть на значение. Я даже подозреваю что сделав SELECT MIN(a+0), MAX(a+0) FROM enums WHERE b = 1 мы получим 1 и 3. Интересно посмотреть что будет если добавить «0». И вообще это странный тип, я не понимаю необходимости его использовать, кроме как для заменителя да/нет/не известно для техническинеподкованных специалистов, т.е. для структуры в которой есть однажды заданные и никогда не меняющиеся ответы (для м/ж, как выясняется, он тоже не подходит).
                                                                    –3
                                                                    В PostgreSQL в парсере shift-reduce conflicts нет совсем. Конкретно эта ситуация там разрешена за счет того, что у UNION не может быть LIMIT.

                                                                    Че, правда?
                                                                      0
                                                                      Автору и минусующим: вы либо крестик сни поясните, что имеется в виду под
                                                                      у UNION не может быть LIMIT
                                                                      , либо покайтесь в своей неправоте и примите схиму. Ибо формально в запросе с UNION может быть LIMIT
                                                                        0
                                                                        Так как вопрос «что, правда» следует за двумя утверждениями, то прокомментирую оба

                                                                        > В PostgreSQL в парсере shift-reduce conflicts нет совсем.

                                                                        github.com/postgres/postgres/blob/master/src/backend/parser/gram.y

                                                                        %pure-parser
                                                                        %expect 0
                                                                        


                                                                        > Конкретно эта ситуация там разрешена за счет того, что у UNION не может быть LIMIT.

                                                                        Тут действительно ошибка, которая меняет смысл, но не критична в контесте: все строго наоборот, у UNION может быть лимит, но у каждого из запросов в UNION не может.
                                                                        Важно то, что конфликта нет.
                                                                      0
                                                                      А можете это всё, кроме примера с collation, послать на bugs.mysql.com?
                                                                        +3
                                                                        mysql> SELECT 'aaa' = 'aaa';
                                                                        +---------------+
                                                                        | 'aaa' = 'aaa' |
                                                                        +---------------+
                                                                        |             1 |
                                                                        +---------------+
                                                                        1 row in set (0.02 sec)
                                                                        
                                                                        mysql> SELECT 'aaa' = 'aaa ';
                                                                        +----------------+
                                                                        | 'aaa' = 'aaa ' |
                                                                        +----------------+
                                                                        |              1 |
                                                                        +----------------+
                                                                        1 row in set (0.00 sec)
                                                                        
                                                                        mysql> SELECT 'aaa' = ' aaa';
                                                                        +----------------+
                                                                        | 'aaa' = ' aaa' |
                                                                        +----------------+
                                                                        |              0 |
                                                                        +----------------+
                                                                        1 row in set (0.00 sec)
                                                                        
                                                                          0
                                                                          Это как так? Причину нашли?
                                                                            0
                                                                            Особенность хранения и представления типа CHAR в MySQL:
                                                                            The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.
                                                                              0
                                                                              Аааа, прощу прощения. Не увидел пробелов.
                                                                            0
                                                                            В документации есть такой момент: конечные пробелы в случае =/<> игнорируются.
                                                                            А strcmp что даёт?
                                                                              0
                                                                              В документации есть такой момент: конечные пробелы в случае =/<> игнорируются.

                                                                              Если не затруднит, пришлите ссылочку.
                                                                              Не могу найти данную строчку в документации, но, судя по поведению, такая строчка имеет место быть.
                                                                              Заранее спасибо.
                                                                          +2
                                                                          Не могу не добавить — обновление первого TIMESTAMP поля в таблице при обновлении/добавлении данных.
                                                                          TIMESTAMP and DATETIME columns have no automatic properties unless they are specified explicitly, with this exception: By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.
                                                                            0
                                                                            Ну как раз это логично. TIMESTAMP на то и TIMESTAMP, что обновляется сам. Нелогично разрешение использовать этот тип данных как синоним DATETIME.
                                                                              0
                                                                              Шта? Смешались в кучу кони, люди…

                                                                              1. Это нелогично и нет никакого смысла в самостоятельном обновлении этого поля.
                                                                              2. DATETIME и TIMESTAMP разные типы данных. По разному хранятся. Для разных целей используются. Но оба типа можно использовать как хранилище даты и времени — на это нет и не должно быть ограничений.
                                                                            0
                                                                            Например, в запросе выше смотря на слово LIMIT парсер не может понять, принадлежит этот LIMIT к второму запросу, или ко всему UNION.

                                                                            Не пойму, зачем парсеру вообще смотреть вперед, чтобы понять, кому принадлежит LIMIT — он же пишется после выражения и принадлежит, соответственно, последнему выражению, которое может его иметь, но еще не имеет. Откуда там конфликт? Что парсер может увидеть, посмотрев на один токен вперед? Там будет число, которое никак не поможет что-либо решить.
                                                                              0
                                                                              Разумеется, смотреть вперед бесполезно. Но конфликт от этого не исчезает:
                                                                              ... UNION (SELECT ... LIMIT 1)
                                                                              или
                                                                              ... UNION (SELECT ...) LIMIT 1
                                                                                0
                                                                                Если скобок не стоит, то должен принадлежать ближайшему выражению, не вижу, что тут может быть конфликтного. Вообще, проблема «висячего else» мне кажется надуманной — правило прилеплять этот самый else к ближайшему выражению, который может его иметь, решает все проблемы без лишнего шума. В бизон-грамматиках это точно можно указывать.

                                                                                Мне непонятно, почему это не сделано, раз, и почему генератор парсеров не предполагает такое поведение по умолчанию (а то и вообще, оно должно быть единственным), два. Другого поведения как-то странно ожидать, не могу найти ни одного примера, когда требуется разбор именно по второму представленному варианту (в вашем комментарии).
                                                                                  0
                                                                                  Другого поведения как-то странно ожидать, не могу найти ни одного примера, когда требуется разбор именно по второму представленному варианту (в вашем комментарии).

                                                                                  Хм, ну вот в Постгресе оно как раз и работает по второму варианту (LIMIT относится к объединенному запросу)
                                                                              –4
                                                                              Просто оставлю это здесь…
                                                                              Изображение
                                                                              image

                                                                                –4
                                                                                Ну это же шутка! Картинка-то хорошая ведь, с намёком :-)
                                                                                  –2
                                                                                  К сожалению иногда на Хабре лучше сидеть и молчать, притом независимо от того, помочь ты хочешь, пошутить, или что-то еще. Лично мне его система доподлинно неизвестна, скорее всего она зависит от расположения планет. Или от веса и репутации автора комментария в этом сообществе. Скорее всего от второго. Ибо какое в рашке правительство, такие и люди. Потому-что заслужили.
                                                                                  У меня все)

                                                                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                                                              Самое читаемое