Как спроектировать систему подписок на сайте?
одна таблица subscribes с вот такой структурой:

subscribe_id
subscribe_author_id
subscribe_object_id

и, разумеется, выкинуть счетчики из таблицы users
больше ничего не нужно
для остального учить базовый SQL. Все подписки пользователя удаляются одним запросом. Все подписчики - тоже.

Если оставить в стороне нелепые вопросы "а для запроса на удаление тыщи записей какой-то другой SQL нужен?", и вернуться проектированию подписок, то, как правильно подсказывает Slava Rozhnev, 

Во-первых, в таблицу подписок необходимо добавить два составных индекса, 
subscribe_author_id, subscribe_object_id
subscribe_object_id, subscribe_author_id
и тогда ужасный запрос count(*) перестанет быть ночным кошмаром, а будет выполняться мгновенно

А во-вторых, в неё можно добавить внешние ключи, которые будут ссылаться на таблицу users, с опцией каскадного удаления. Тогда отдельный запрос на удаление подписок и вовсе не придётся писать руками, достаточно будет удалить только юзера.

Запроса на удаление бояться не надо. Этот запрос всего лишь пометит нужные записи, как удалённые, никто файл на диске укорачивать не будет. Потом база данных использует эти же самые ячейки для других подписок.
Второй вариант, без префиксов subscribe_ итак понятно, что от subscribers. И связь полиморфную, чтобы подписываться на кучу объектов с одним функционалом через трейт модели. для соцсетей актуально. В дальнейшем можно оптимизировать коунтерами итд