Базы данных и PDO
Работа с базой данных из PHP — PDO против mysqli, подготовленные выражения и транзакции, оптимистичные и пессимистичные блокировки, индексы и поиск недостающего индекса, а также управление соединениями в модели shared-nothing под php-fpm.
14 вопросов
JuniorТеорияОчень частоЧто такое индекс в базе данных, что он ускоряет и чего стоит при записи?
Что такое индекс в базе данных, что он ускоряет и чего стоит при записи?
Индекс — отдельная отсортированная структура, обычно B-tree, отображающая значения столбца на строки, поэтому движок ищет, а не сканирует таблицу целиком. Он ускоряет выборки, join и ORDER BY по этим столбцам, но каждый INSERT, UPDATE и DELETE обязан его обновлять, поэтому запись замедляется.
Типичные ошибки
- ✗Считать индекс бесплатным — его обязана обновлять каждая запись
- ✗Думать, что индекс кэширует строки, а не является отсортированной структурой поиска
- ✗Добавлять по индексу на столбец вместо одного составного индекса под запрос
Уточняющие вопросы
- →Почему составной индекс по
(a, b)обслуживает запрос только поa, но не только поb? - →Когда столбец с низкой кардинальностью, например булев флаг, будет плохим индексом?
JuniorТеорияОчень частоЧто такое транзакция в базе данных, и что гарантирует атомарность, если один запрос внутри неё падает?
Что такое транзакция в базе данных, и что гарантирует атомарность, если один запрос внутри неё падает?
Транзакция объединяет запросы в одну единицу: либо фиксируются все изменения, либо ни одно. Атомарность означает, что сбой на середине оставляет базу ровно в прежнем состоянии — rollback откатывает и предыдущие запросы. До commit другие соединения не видят ваши незафиксированные строки.
Типичные ошибки
- ✗Считать, что уже успешные запросы остаются зафиксированными, когда падает следующий
- ✗Думать, что транзакция — это блокировка таблицы, а не единица «всё или ничего»
- ✗Полагать, что другие соединения видят строки до
COMMIT
Уточняющие вопросы
- →Что происходит с открытой транзакцией, если PHP-скрипт умирает до commit?
- →Как уровень изоляции меняет то, что ваша транзакция видит по ходу выполнения?
SeniorДизайнОчень частоПлатёжный провайдер зовёт ваш PHP-вебхук при каждой смене состояния. Он повторяет доставку на любой не-2xx ответ и по таймауту, не гарантирует порядок и может доставить одно событие дважды — иногда одновременно, на два воркера PHP-FPM. Каждое событие обязано начислить клиенту баланс ровно один раз, а дубликат не должен начислить повторно. Схема базы ваша, таблицы добавлять можно. Спроектируйте обработчик: что идентифицирует событие, где принимается решение о дедупликации, как не дать двум одновременным доставкам одного события выиграть обеим, как удержать начисление и запись о дедупликации согласованными между собой и что вернуть провайдеру, когда придёт повтор уже обработанного события.
Платёжный провайдер зовёт ваш PHP-вебхук при каждой смене состояния. Он повторяет доставку на любой не-2xx ответ и по таймауту, не гарантирует порядок и может доставить одно событие дважды — иногда одновременно, на два воркера PHP-FPM. Каждое событие обязано начислить клиенту баланс ровно один раз, а дубликат не должен начислить повторно. Схема базы ваша, таблицы добавлять можно. Спроектируйте обработчик: что идентифицирует событие, где принимается решение о дедупликации, как не дать двум одновременным доставкам одного события выиграть обеим, как удержать начисление и запись о дедупликации согласованными между собой и что вернуть провайдеру, когда придёт повтор уже обработанного события.
Ключ — идентификатор события от провайдера: таблица processed_events с уникальным ограничением по нему. В одной транзакции вставьте эту строку и примените начисление — именно уникальный индекс делает гонку безопасной: у второго воркера INSERT падает и его транзакция откатывается. Никогда не делайте «сначала проверить, потом вставить» — это окно гонки. На дубликат отвечайте 200, чтобы провайдер прекратил повторы.
Типичные ошибки
- ✗Проверять наличие идентификатора события, а потом вставлять — промежуток между ними и есть гонка
- ✗Дедуплицировать вне той транзакции, которая начисляет баланс
- ✗Отвечать не-2xx на дубликат, из-за чего провайдер повторяет его бесконечно
Уточняющие вопросы
- →Что делать, если начисление зафиксировано, но ваш ответ
200до провайдера не дошёл? - →Как обработать события, пришедшие для одного клиента в неверном порядке?
JuniorТеорияЧастоПод менеджером процессов PHP-FPM когда запрос открывает соединение с базой и когда оно закрывается?
Под менеджером процессов PHP-FPM когда запрос открывает соединение с базой и когда оно закрывается?
Воркер открывает соединение лениво при первом запросе, а PHP закрывает его в конце запроса, когда уничтожается объект PDO, — в следующий запрос ничего не переносится. Исключение — persistent-соединения: сокет остаётся привязан к этому воркеру и переиспользуется его следующим запросом.
Типичные ошибки
- ✗Думать, что
PHP-FPMпулит соединения к базе, как это делает Java-сервер приложений - ✗Считать, что объект
PDOпереживает запрос и доступен в следующем - ✗Полагать, что persistent-соединение общее для всех воркеров, а не привязано к одному
Уточняющие вопросы
- →Какое состояние persistent-соединение может утащить в следующий запрос того же воркера?
- →Как ведёт себя незакрытая транзакция, если запрос обрывается досрочно?
JuniorТеорияЧастоЧто даёт слой доступа к базе PDO по сравнению с mysqli, и где он перестаёт помогать?
Что даёт слой доступа к базе PDO по сравнению с mysqli, и где он перестаёт помогать?
PDO — единый API поверх MySQL, PostgreSQL, SQLite и других: смена драйвера меняет DSN, а не места вызова. Он поддерживает именованные плейсхолдеры и умеет бросать исключения при ошибке. mysqli работает только с MySQL. Переносимым ваш SQL ни один из них не делает — переносим только API.
Типичные ошибки
- ✗Считать, что
PDOделает переносимым сам SQL, а не только API - ✗Думать, что
mysqliне умеет подготовленные выражения - ✗Полагать, что
PDOпулит соединения между запросами PHP-FPM
Уточняющие вопросы
- →Какой атрибут
PDOзаставляет упавший запрос бросать исключение, а не возвращатьfalse? - →Когда вы всё же выберете
mysqliвместоPDOв проекте только на MySQL?
MiddleТеорияЧастоУ PHP-FPM нет пула соединений. Что ломается при росте нагрузки, и что меняют пулеры вроде pgbouncer?
У PHP-FPM нет пула соединений. Что ломается при росте нагрузки, и что меняют пулеры вроде pgbouncer?
В shared-nothing каждый воркер открывает своё соединение, поэтому их всего воркеры × серверы, и база упирается в max_connections раньше, чем насытится PHP. Внешний пулер стоит между ними и мультиплексирует множество коротких клиентских соединений на несколько серверных. Persistent-соединения PDO привязывают по сокету к воркеру — это не пул.
Типичные ошибки
- ✗Называть
PDO::ATTR_PERSISTENTпулом — это один сокет, привязанный к одному воркеру - ✗Подбирать размер пула FPM, не сверяясь с
max_connectionsбазы - ✗Считать, что пулер держит по серверному соединению на каждое клиентское
Уточняющие вопросы
- →Почему пулинг в transaction-режиме ломает серверные подготовленные выражения и session-переменные?
- →Как подобрать
pm.max_childrenотносительноmax_connectionsбазы?
MiddleПроизводительностьЧастоЭндпоинт со списком замедлился по мере роста таблицы. Как доказать, что причина — недостающий индекс?
Эндпоинт со списком замедлился по мере роста таблицы. Как доказать, что причина — недостающий индекс?
Сначала найдите медленный запрос — через slow-query log или логирование вокруг вызовов PDO, — затем выполните на нём EXPLAIN. Полное сканирование (type: ALL, rows ≈ размер таблицы, Using filesort) без пригодного ключа и есть доказательство. Добавьте составной индекс по столбцам WHERE, а затем по столбцу ORDER BY, и перезапустите EXPLAIN.
Типичные ошибки
- ✗Угадывать индекс вместо чтения типа доступа и выбранного ключа в
EXPLAIN - ✗Добавлять по индексу на столбец вместо одного составного индекса под запрос
- ✗Ставить столбец
ORDER BYперед столбцамиWHEREв составном индексе
Уточняющие вопросы
- →Почему столбцы равенства должны стоять перед диапазоном или столбцом
ORDER BYв составном индексе? - →Что говорит
Using filesort, чего не говорит одна лишь оценкаrows?
MiddleТеорияЧастоКогда выбирают оптимистичную блокировку вместо пессимистичной SELECT ... FOR UPDATE, и чего стоит каждая?
Когда выбирают оптимистичную блокировку вместо пессимистичной SELECT ... FOR UPDATE, и чего стоит каждая?
Пессимистичная блокировка держит блокировку строки (SELECT ... FOR UPDATE) до commit, поэтому писатели выстраиваются в очередь и возможен deadlock. Оптимистичная блокировку не берёт: UPDATE перепроверяет столбец version в своём WHERE, и при rowCount() = 0 вы повторяете попытку. Оптимистичная выигрывает при низкой конкуренции, пессимистичная — когда конфликты обычны.
Типичные ошибки
- ✗Думать, что оптимистичная блокировка всё же берёт блокировку в базе, просто короче
- ✗Забывать про цикл повтора — оптимистичный
UPDATEс 0 строк означает, что выиграл другой - ✗Брать пессимистичную блокировку при низкой конкуренции и зря выстраивать писателей в очередь
Уточняющие вопросы
- →Что блокирует
SELECT ... FOR UPDATE, если за столбцом вWHEREнет индекса? - →Сколько раз оптимистичное обновление должно повторяться до отказа, и зачем вообще ограничивать?
MiddleКодЧастоАтомарный перевод средств между двумя счетами через PDO
Атомарный перевод средств между двумя счетами через PDO
Оберните оба UPDATE в beginTransaction() … commit(), а в catch вызовите rollBack() и пробросьте исключение дальше, чтобы вызывающий узнал о сбое. При PDO::ERRMODE_EXCEPTION упавший запрос бросает исключение сам. Значения $fromId, $toId и сумму передавайте параметрами, не интерполируйте их.
Типичные ошибки
- ✗Обойтись без транзакции и «чинить» неудавшееся списание компенсирующей записью
- ✗Поймать исключение и выйти, из-за чего вызывающий считает перевод успешным
- ✗Интерполировать id и сумму в SQL вместо связывания параметрами
Уточняющие вопросы
- →От чего страхует
PDO::inTransaction(), еслиtransfer()могут вызвать из внешней транзакции? - →Как заставить перевод отказать при недостатке средств, не выходя из той же транзакции?
SeniorДебаггингЧастоПрод заливает ошибками SQLSTATE[HY000] [1040] Too many connections на пике нагрузки
Прод заливает ошибками SQLSTATE[HY000] [1040] Too many connections на пике нагрузки
Посчитайте потолок: четыре сервера на pm.max_children 50 — это 200 возможных воркеров, тогда как max_connections равен 151, и PHP способен запросить больше соединений, чем MySQL выдаст. ATTR_PERSISTENT усугубляет: каждый воркер держит свой сокет открытым между запросами, поэтому 148 из 151 в состоянии Sleep и лишь 3 выполняют запрос. Ограничьте пулы FPM или поставьте пулер вроде pgbouncer/ProxySQL — а не просто поднимайте max_connections.
Типичные ошибки
- ✗Поднимать
max_connections, не сверив с ним произведение серверов наpm.max_children - ✗Читать потоки
Sleepкак свободный запас, а не как воркеров с открытыми persistent-сокетами - ✗Называть
ATTR_PERSISTENTпулом соединений — он привязывает один сокет к одному воркеру
Уточняющие вопросы
- →Какая арифметика связывает
pm.max_childrenсmax_connectionsчерез несколько серверов? - →Что даёт пулер в transaction-режиме такого, чего не может
ATTR_PERSISTENT?
SeniorДизайнИногдаКомплаенс требует аудит-лог: по каждому изменению заказа, клиента или платежа вы обязаны ответить, кто что изменил, когда и откуда, — и восстановить состояние любой записи на любую прошлую дату. Приложение — PHP-монолит на PHP-FPM; запись идёт через ORM, а в нескольких legacy-углах — сырым SQL. Читают след редко, но тогда это должно быть быстро, а за год он перерастёт операционные таблицы. Обычная ошибка в приложении не должна молча пропустить запись, а аудитор обязан верить, что запись не правили задним числом. Спроектируйте это: откуда пишется запись и как она остаётся согласованной с изменением, которое описывает, что хранит одна запись, как индексировать таблицу, которая только растёт, и как не дать ей поглотить операционную базу.
Комплаенс требует аудит-лог: по каждому изменению заказа, клиента или платежа вы обязаны ответить, кто что изменил, когда и откуда, — и восстановить состояние любой записи на любую прошлую дату. Приложение — PHP-монолит на PHP-FPM; запись идёт через ORM, а в нескольких legacy-углах — сырым SQL. Читают след редко, но тогда это должно быть быстро, а за год он перерастёт операционные таблицы. Обычная ошибка в приложении не должна молча пропустить запись, а аудитор обязан верить, что запись не правили задним числом. Спроектируйте это: откуда пишется запись и как она остаётся согласованной с изменением, которое описывает, что хранит одна запись, как индексировать таблицу, которая только растёт, и как не дать ей поглотить операционную базу.
Пишите запись в той же транзакции, что и само изменение: если изменение откатится, вместе с ним уйдёт и строка аудита, тогда как лог, записанный после commit, может молча потеряться. Храните актора, тип и идентификатор сущности, действие, diff «до/после», отметку времени, идентификатор запроса и адрес. Индекс — (entity_type, entity_id, created_at): любое чтение есть история одной записи. Держите таблицу append-only, отобрав права UPDATE и DELETE, и партиционируйте либо архивируйте по времени.
Типичные ошибки
- ✗Писать строку аудита вне транзакции, из-за чего откатившееся изменение сохраняет запись
- ✗Индексировать только по времени, когда любое чтение — это история одной конкретной записи
- ✗Оставить таблицу доступной для
UPDATE, из-за чего след для аудитора не защищён от правок
Уточняющие вопросы
- →Что здесь даёт триггер базы такого, чего не может код приложения, и что при этом теряется?
- →Как не дать таблице, которая только растёт, замедлить операционную базу?
SeniorДизайнИногдаДва воркера PHP-FPM обрабатывают два заказа на один и тот же товар в одну миллисекунду. Каждый читает stock = 1, каждый решает, что заказ можно исполнить, и каждый пишет stock = 0 — вы продали одну единицу дважды. Та же форма возникает на балансе кошелька, на брони места и на купоне с единственным оставшимся использованием. Сериализовать эндпоинт целиком нельзя: остальная его часть медленная и от проверки остатка не зависит. Спроектируйте путь записи так, чтобы инвариант stock >= 0 нельзя было нарушить, сколько бы воркеров ни пришло разом. Разберите, что делает связку «прочитать, потом записать» небезопасной, что вы выберете из условного UPDATE, пессимистичной блокировки строки и оптимистичной проверки версии и почему, что обязана гарантировать сама база, чтобы ваш выбор работал, что делает код, проигравший гонку, и как вы докажете под нагрузкой, что инвариант действительно держится.
Два воркера PHP-FPM обрабатывают два заказа на один и тот же товар в одну миллисекунду. Каждый читает stock = 1, каждый решает, что заказ можно исполнить, и каждый пишет stock = 0 — вы продали одну единицу дважды. Та же форма возникает на балансе кошелька, на брони места и на купоне с единственным оставшимся использованием. Сериализовать эндпоинт целиком нельзя: остальная его часть медленная и от проверки остатка не зависит. Спроектируйте путь записи так, чтобы инвариант stock >= 0 нельзя было нарушить, сколько бы воркеров ни пришло разом. Разберите, что делает связку «прочитать, потом записать» небезопасной, что вы выберете из условного UPDATE, пессимистичной блокировки строки и оптимистичной проверки версии и почему, что обязана гарантировать сама база, чтобы ваш выбор работал, что делает код, проигравший гонку, и как вы докажете под нагрузкой, что инвариант действительно держится.
Чтение и запись — два запроса, поэтому между ними вклинивается другой воркер, и к моменту действия проверка уже устарела. Втяните проверку внутрь записи: UPDATE ... SET stock = stock - 1 WHERE id = ? AND stock >= 1, затем прочитайте rowCount() — ноль означает, что гонку вы проиграли и заказ надо отклонить. Блокировка строки в движке делает этот одиночный запрос атомарным. SELECT ... FOR UPDATE тоже работает, но выстраивает писателей в очередь, а оптимистичная проверка version требует цикла повтора.
Типичные ошибки
- ✗Считать, что одна лишь транзакция закрывает промежуток между чтением и записью
- ✗Писать абсолютное значение, посчитанное в PHP, вместо относительного условного
UPDATE - ✗Не проверять
rowCount(), из-за чего проигранная гонка молча считается успехом
Уточняющие вопросы
- →Что говорит
rowCount(), вернувший 0, чего не сказало бы исключение? - →Когда пессимистичная блокировка строки стоит той сериализации, которой она обходится?
SeniorДизайнИногдаВы проектируете мультитенантный SaaS на PHP. Данные каждого клиента должны быть изолированы, размеры тенантов — от десяти строк до десяти миллионов, и нужно уметь восстановить или выгрузить одного клиента, не трогая остальных. Рантайм — PHP-FPM за балансировщиком, поэтому каждый воркер открывает своё соединение с базой. Сравните две схемы — одна общая база со столбцом tenant_id в каждой таблице против отдельной базы на тенанта — и обоснуйте выбор. Разберите, как каждая влияет на общее число соединений, как сделать утечку данных между тенантами невозможной, какой индексации требует каждая, как раскатываются миграции и что будет, когда нагрузка одного очень крупного тенанта начнёт мешать остальным.
Вы проектируете мультитенантный SaaS на PHP. Данные каждого клиента должны быть изолированы, размеры тенантов — от десяти строк до десяти миллионов, и нужно уметь восстановить или выгрузить одного клиента, не трогая остальных. Рантайм — PHP-FPM за балансировщиком, поэтому каждый воркер открывает своё соединение с базой. Сравните две схемы — одна общая база со столбцом tenant_id в каждой таблице против отдельной базы на тенанта — и обоснуйте выбор. Разберите, как каждая влияет на общее число соединений, как сделать утечку данных между тенантами невозможной, какой индексации требует каждая, как раскатываются миграции и что будет, когда нагрузка одного очень крупного тенанта начнёт мешать остальным.
По умолчанию — общая база с tenant_id: одно соединение на воркер, один прогон миграций, дешёвое подключение клиента. Цена в том, что каждый индекс обязан начинаться с tenant_id, а один забытый WHERE утекает данными, — поэтому ограничивайте выборку в слое доступа, а не в каждом запросе. База на тенанта даёт жёсткую изоляцию и восстановление по одному клиенту, но умножает соединения и размазывает миграции.
Типичные ошибки
- ✗Полагаться на то, что каждый запрос не забудет
WHERE tenant_id, вместо ограничения в слое доступа - ✗Забывать, что база на тенанта умножает число соединений на число воркеров
- ✗Не ставить
tenant_idпервым столбцом индекса, из-за чего каждый тенант сканирует всю таблицу
Уточняющие вопросы
- →Как сделать чтение чужого тенанта невозможным, даже когда разработчик пишет сырой SQL руками?
- →Что меняется в вашем ответе, когда один тенант в сто раз крупнее всех остальных?
SeniorДизайнРедкоВам достаётся PHP-приложение на 200 тысяч строк, всё ещё вызывающее удалённые функции mysql_*, с SQL, собранным интерполяцией в сотнях мест. Оно должно всё это время обслуживать боевой трафик; тестов нет, а данные на staging не похожи на боевые. Руководство хочет PHP 8 и PDO, но переписывание не оплатит. Опишите, как вы проведёте миграцию: как найдёте и отранжируете места вызова, что поставите до того, как тронете хоть одно, как два стиля доступа к данным сосуществуют по ходу перехода, что делать с кодом, полагавшимся на неявный дескриптор соединения и на autocommit, и как на каждом шаге доказать, что поведение не изменилось.
Вам достаётся PHP-приложение на 200 тысяч строк, всё ещё вызывающее удалённые функции mysql_*, с SQL, собранным интерполяцией в сотнях мест. Оно должно всё это время обслуживать боевой трафик; тестов нет, а данные на staging не похожи на боевые. Руководство хочет PHP 8 и PDO, но переписывание не оплатит. Опишите, как вы проведёте миграцию: как найдёте и отранжируете места вызова, что поставите до того, как тронете хоть одно, как два стиля доступа к данным сосуществуют по ходу перехода, что делать с кодом, полагавшимся на неявный дескриптор соединения и на autocommit, и как на каждом шаге доказать, что поведение не изменилось.
Не переписывайте на месте. Поставьте впереди тонкий слой доступа к данным поверх PDO и переводите места вызова через него срезами, ранжируя по трафику и риску, предварительно закрыв каждый срез чёрно-ящичными тестами. По ходу заменяйте интерполяцию связанными параметрами, а прежний неявный autocommit делайте явным через beginTransaction()/commit(). Каждый срез выкатывайте под флагом и сверяйте его вывод со старым путём.
Типичные ошибки
- ✗Считать это механической заменой, когда настоящее изменение — связывание параметров
- ✗Оставлять интерполированный SQL, полагая, что
PDO«по умолчанию безопасен» - ✗Не замечать, что старый код опирался на неявный autocommit и глобальный дескриптор соединения
Уточняющие вопросы
- →Как поймать место вызова, молча полагавшееся на то, что
mysql_*возвращаетfalse, а не бросает? - →Что логировать по ходу перехода, чтобы доказать, что путь через
PDOвозвращает те же строки?
