Задать вопрос
Melkij

Melkij

PostgreSQL DBA
Ответы пользователя по тегу MySQL

  • Можно ли сделать такой SQL-запрос?

    Melkij
    @Melkij
    PostgreSQL DBA 
    select org from tablename group by org having count(distinct active) > 1;
    Ответ написан
    2 комментария
    2 комментария

  • Когда имеет смысл делать несколько таблиц с комментариями?

    Melkij
    @Melkij
    PostgreSQL DBA
    И получается дико неудобно и значительно медленнее в итоге.
    Не касаясь лимита на число открытых файловых дескрипторов, кучу internal статистики планировщика и прочих деталей реализации.

    Форум. Банальная страница профиля пользователя, достать последние 10 сообщений этого пользователя - как? union all по всем темам с filesort последующим вместо простого index scan по составному ключу даже без необходимости в сортировке?
    Статьи. Показать для модерации последние комментарии

    А распилить большую таблицу (только на самом деле большую, а не в смешные лям строк) на части - вещь неплохая. Только не так надо пилить. Есть у mysql штатное партицирование.
    Ответ написан
    Комментировать
    Комментировать

  • Как правильно написать двойное обновление?

    Melkij
    @Melkij
    PostgreSQL DBA
    Вы сильно заблуждаетесь, если думаете, что ваш cte выполняется последовательно.
    The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables.

    https://www.postgresql.org/docs/9.6/static/queries...

    Вы выполняете на одном слепке данных одних и тех строк различающиеся действия. Не надо так. Я понятий не имею, какой эффект от этого будет.
    К тому же вы элементарно переписываетесь в один просто запрос
    UPDATE "TABLE1"
SET
  "Value2" = (NOT EXISTS(
    SELECT NULL
    FROM "TABLE2"
    WHERE "что-то" = "кое-что"
)
      AND NOT EXISTS(
    SELECT NULL
    FROM "TABLE3"
    WHERE "что-то" = "кое-что"
))
WHERE "кое-что" = ANY ($1 :: INT [])
    Ответ написан
    Комментировать
    Комментировать

  • Какая база луче подойдет на рабочий портал?

    Melkij
    @Melkij
    PostgreSQL DBA
    Сейчас разработчики пишут портал

    Вот у разработчиков и спрашивайте, какую СУБД они лучше знают. И админов своих спросите, какую СУБД те лучше знают. DBA у вас явно нет, иначе вопрос бы так не стоял.

    Сам headhunter использует postgresql. Но там и своя команда админов классная, и вдобавок опытная команда DBA моих нынешних коллег, специализирующихся именно на postgresql.

    Я достаточно хорошо знаю обе СУБД с точки зрения разработчика, но не умею админить mysql, так что моё мнение будет предвзятым.
    Если делаете коммерческий проект - то используйте ту СУБД, которую лучше знает ваша команда. Если разработчики попрятались за своими ORM'ами и носу не заглядывают в базу - то без разницы, в таких условиях любая СУБД будет работать одинаково плохо. Если же хоть кто-то в команде понимает, что надо делать с базой - доверьте выбор ему и поинтересуйтесь о причинах выбора.
    Ответ написан
    Комментировать
    Комментировать

  • Как в Ruby on Rails поле created_at использовать в mysql-запросе?

    Melkij
    @Melkij
    PostgreSQL DBA
    Из комментариев created_at является нормальным timestamp или datetime полем. Поэтому его не только не нужно приводить к строке и парсить повторно в дату (с неверным форматом к тому же), но ещё и очень вредно так делать, т.к. исключает возможность использования индекса.

    created_at < STR_TO_DATE('01.01.2017', '%d.%m.%Y')
    Дату возможно отформатировать в Y-m-d формат на приложении, это уже как удобнее.
    Ответ написан
    Комментировать
    Комментировать

  • Как посмотреть логи изменений в базе данных MySQL?

    Melkij
    @Melkij
    PostgreSQL DBA
    - изобрести машину времени
    - вернуться на дату 90 дней назад
    - включить логирование всех запросов либо настроить ведение и непрерывное архивирование бинарного лога
    - вернуться в настоящее время и анализировать настроенный в прошлом шаге лог

    Если вы не настроили вести лог изменений - то его никто хранить не будет. Даже mvcc версионник innodb будет стараться вычищать старые версии строк. Потому что зачем хранить кучу данных, если не было изложено требования их хранить?
    Ответ написан
    Комментировать
    Комментировать

  • Способ уменьшить размер БД InnoDB без дампа?

    Melkij
    @Melkij
    PostgreSQL DBA
    Если ненужные записи в разрезе логов = все старые логи, то делаете партицирование по дате. Ненужные более старые данные в будущем дропаются целиком всем разделом, просто и без длительных блокировок.

    2) Конвертируем таблицу (НЕ БАЗУ) в MyISAM (ALTER TABLE .... ну я просто поменял тип базы в "Операции" над таблицей), оптимизируем MyISAM таблицу.
    3) Конвертируем обратно в InnoDB (так же через phpMyAdmin).

    Блокировка записи на всё время перестроения. И, емнип, alter table tablename engine=innoodb; (даже если таблица уже в innodb) спровоцирует копирование таблицы с сопутствующим сжатием неиспользуемых пространств. Т.е. конвертирование в myisam избыточно.

    А так можно поиграться с созданием новой таблички идентичной старой, копированием через insert select в неё нужных данных, затем копированием актуальных данных и финальным rename и докопированием того что попало в старую табличку до rename. Это не сложно, если табличка именно логов - т.е. только insert и select. Если есть update или delete то надо опять же извращаться. Или согласовывать простой.
    Ответ написан
    1 комментарий
    1 комментарий

  • Как убедиться что запрос к БД выполнен?

    Melkij
    @Melkij
    PostgreSQL DBA
    Переключите PDO::ATTR_ERRMODE в PDO::ERRMODE_EXCEPTION. Если exceptin прилетел - значит запрос выполнен не был.
    Не было исключения - значит запрос выполнен.

    их хеши со старым равны

    И это криптографически плохо. См. стандартные password_hash + password_verify
    Ответ написан
    11 комментариев
    11 комментариев

  • Как выбрать id для которых в таблице есть только одна запись?

    Melkij
    @Melkij
    PostgreSQL DBA 
    select goods_id from tablename where goods_id in (?) group by goods_id having count(*)=1
    Ответ написан
    1 комментарий
    1 комментарий

  • Какие книги прочитать по лучшим практикам проектирования БД?

    Melkij
    @Melkij
    PostgreSQL DBA
    Bill Karwin SQL Antipatterns
    В общем-то, в духе рефакторинга как раз: как делают часто неправильно, почему это плохо (но когда и адекватно для задачи) и как сделать лучше.
    Ответ написан
    1 комментарий
    1 комментарий

  • Что почитать на досуге?

    Melkij
    @Melkij
    PostgreSQL DBA
    Конечно, Макконнелл "Совершенный код",
    Мартин "Чистый код"
    "Рефакторинг" Фаулера

    "Приемы объектно-ориентированного проектирования" Э. Гамма

    Поскольку в списке mysql - High Performance MySQL обязателен к прочтению
    Отдельно рекомендую "Регулярные выражения" Дж.Фриддла, отлично вправляет мозг по регуляркам.

    По базам в целом:
    короткая "Architecture of a Database System" J. M. Hellerstein, M. Stonebraker and J. Hamilton расскажет об общих подходах построения СУБД
    Bill Karwin SQL_Antipatterns - как не надо писать SQL. А вот, кстати, отличный материал как надо писать, но не книга: use-the-index-luke.com

    Это всё вполне можно читать в порядке отдыха.

    Фундаментальные, которые уже идут заметно тяжелее:
    "Transactional Information Systems" Weikum, Vossen - монументальная теория транзакционной обработки
    "Алгоритмы. Построение и анализ" Томас Х. Кормен, Чарльз И. Лейзерсон, Рональд Л. Ривест, Клиффорд Штайн - про основные алгоритмы. Всё библиотечное, но полезнейшая весь для понимания обычно скрытой машинерии и для прохождения собеседований.
    Ответ написан
    Комментировать
    Комментировать

  • Как добавить через insert values в начало таблицы?

    Melkij
    @Melkij
    PostgreSQL DBA
    В таблице в sql в принципе нет начала и конца. Есть большая куча строк.
    А порядок в этой куче появляется только когда вы явно указываете order by. Без order by база может возвращать строки в любом порядке, в каком захочет.
    Ответ написан
    2 комментария
    2 комментария

  • Насколько внешние ключи любят ресурсы?

    Melkij
    @Melkij
    PostgreSQL DBA
    Делать внешние ключи обязательно.
    И уникальные индексы проставлять. Ещё бы check constraint ставить, да у вас глупый mysql в тегах, он такое не умеет.
    Это всё ваши помощники. Они помогают искать ошибки и отлично мешают делать глупости.
    И выбирать подходящие типы данных. Ну и раз у вас mysql - то всенепременно проверить sql_mode и выставить его максимально агрессивным. В 5.7 стало гораздо лучше, до этого из коробки уж слишком много позволял делать глупостей.

    Дорастёте до террабайтной базы как авито - тогда можете начинать думать, а сколько денег нам стоит целостность внешних ключей или дешевле будет периодически проверять целостность скриптами и озадачивать разработчиков. О, кстати, вот запись доклада с pgday15, на 27 минуте как раз начинается обсуждение вопроса из зала "ребята, а вы в своём уме отключать FK?"
    Ответ написан
    5 комментариев
    5 комментариев

  • Как составить запрос для выборки по времени из TIMESTAMP?

    Melkij
    @Melkij
    PostgreSQL DBA
    Правильно, хех?
    А правильно - включает в себя "чтобы быстро работало"?
    Чтобы работало медленно всегда, но просто:
    where time(timestamp) = '14:30:00'
    Чтобы работало быстро - материализовать time(timestamp) в generated column (5.7, отдельно уточните какая версия нужна для построения индекса по ним) или в отдельное поле типа time (тогда плюс соответственно триггеры для поддержания актуальности данных в поле) и повесить поверх индекс.
    where timestamp_time_field = '14:30:00'
    Ответ написан
    1 комментарий
    1 комментарий

  • Как понять запрос к MySQL?

    Melkij
    @Melkij
    PostgreSQL DBA
    Это то, как НЕ надо делать фильтр, если нужны строки за определённый месяц.
    Потому что mysql не умеет функциональные индексы, значит если даже индекс подходящий по дате есть - использоваться он не будет. Должно быть:
    WHERE f=1 
AND tab.site_id = '5568' 
AND user_payment >=  '2016-06-01' 
AND user_payment <  '2017-07-01'

    Что отлично пойдёт по индексу, например, site_id, user_payment. Надо ли сюда включать f - смотрите ваши данные, вам лучше знать вашу базу.
    Ответ написан
    2 комментария
    2 комментария

  • Нужен ли primary key для такого?

    Melkij
    @Melkij
    PostgreSQL DBA
    primary key нужен всегда.
    Если по логике вашего приложения одному user_id может соответствовать только один пользователь в каждом service_id - то PK на первые два поля.
    Если несколько - то pk на 3 поля.

    Порядок полей при указании PK таблицы связей важен. Для mysql/innodb очень важен, т.к. таблица кластеризована по PK и любое обращение по вторичному ключу влечёт поиск по PK (кроме покрывающих индексов). Порядок выбирайте исходя из наиболее частых запросов к таблице, чтобы максимум запросов могло идти по первичному ключу.
    Ответ написан
    Комментировать
    Комментировать

  • Можно ли в mysql сделать read lock c отлупом для insert и update?

  • Как написать бесконечный по времени sql запрос?

    Melkij
    @Melkij
    PostgreSQL DBA
    select sleep(столькосекундскольконравится);

    Плюс см https://dev.mysql.com/doc/refman/5.7/en/server-sys...
    Ответ написан
    Комментировать
    Комментировать

  • Как решить 1215, 'Cannot add foreign key constraint'?

    Melkij
    @Melkij
    PostgreSQL DBA
    Детали неудачного создания FK надо смотреть в innodb status, вы всё верно прикрепили в вопросу.
    Встречный вопрос вам: что именно в объяснении английским по цвету вашего терминала непонятно написано:
    Cannot find an index in the referenced table where the
    referenced columns appear as the first columns, or column types
    in the table and the referenced table do not match for constraint.

    ? На target таблице нет подходящего индекса для работы FK.
    Судя по названиям - где вы продолбали primary key?

    Ну и, конечно, нафига вам 4 одинаковых FK? На всякий случай?
    Ответ написан
    Комментировать
    Комментировать

  • Как можно реализовать изменение значения в ячейке бд (Mysql) через N-ый промежуток времени?

    Melkij
    @Melkij
    PostgreSQL DBA
    payed_until timestamp default null
    Если null либо в прошлом - значит срок оплаты прошёл. Если в будущем - значит оплата была.
    Ответ написан
    Комментировать
    Комментировать