Форум MySQL

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

Примерные таблицы: юзеры, компании, товары, новости, статьи, комментарии, логи посещений, ...

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

И так вопросы по порядку:

1. можно ли в одной базе создавать разные таблицы innodb и myisam?
2. для каких операций лучше использовать innodb, а для каких - myisam?
3. вычитал, что в innodb может встречаться deadlock, чего нету в myisam, пока не совсем пойму его причину возникновения, последствия и как избежать его, можно больше инфы и примеры?
4. в innodb нет полнотекстового поиска до версии MySQL 5.6.4, как быть - есть обходные пути или же лучше обновится? сейчас стоит версия MySQL 5.1.66-0+squeeze1
5. в каких случаях innodb проигрывает в скорости myisam?
6. вычитал, что таблицы innodb хранятся по умолчанию одним файлом - стоит разделять, если да, то как?

Логи лучше хранить в файлах. Намучаетесь потом с базой.

1. Можно.
2. Тут всё очень индивидуально. Зависит от данных, индексов и запросов. Как правило, myisam справляется чуть лучше, если запросы к таблице только select. Но в общем, нужно тестировать. И лучше не смешивать, так как придётся делить ресурсы между innodb и myisam.
3. У myisam табличные блокировки, у innodb - построчные. Myisam тоже может схлопотать дедлок, если в одном запросе идёт изменение данных сразу в нескольких таблицах и подобные запросы выполняются несколько штук одновременно, что довольно редко встречается на практике. Последствия всегда одинаковые - одна из заблокированных транзакций принудительно откатывается. Есть несколько техник, позволяющих избежать таких блокировок.
4. Лучше отдать это на откуп специализированному софту. Очень хвалят sphinx - он поставляется как плагин к mysql, так и standalone сервис. Или проекты на базе Apache Lucene - solr, nutch. Или xapian. Вобщем, много их всяких разных.
5. В очень редких. В большинстве случаев из-за неоптимальных настроек innodb.
6. Обычно разделяют для большего удобства или для повышения производительности. Например, таблицу с горячими оперативными данными можно положить на SSD, а все остальные будут лежать на SATA. Если все табличное пространство будет в одном файле - такой хак не провернешь. Регулируется настройкой innodb_file_per_table.

Советую прочитать книгу High Perfomance MySQL (как минимум 3 раза от корки до корки). Очень толковая книга. 2-е издание есть на русском языке.

> Логи лучше хранить в файлах. Намучаетесь потом с базой.
А в чем мучения заключаются? Я слышал обратное, что с базой будет быстрей.
> 1. Можно.
Конфликтов никаких не будет, если их джойнить к примеру?
> ... И лучше не смешивать, так как придётся делить ресурсы между innodb и myisam.
Даже не знаю. У меня будут таблицы, которые требуют повышенной надежности работы с ними и будут такие, которые затрагиваться будут очень редко или только для select-а.
> Myisam тоже может схлопотать дедлок
Разве? Блин, читал на многих ресурсах, что дедлока нет на этом типе.
> Последствия всегда одинаковые - одна из заблокированных транзакций принудительно откатывается.
Так в myisam нет же транзакций.

Я вообще думал, что в myisam блокируется таблица и если в этот момент идет к ней обращение, запись и тд, операция ожидает разблокирования таблицы, после чего выполняется.

> Лучше отдать это на откуп специализированному софту.
Разве встроенный полнотекстовый поиск хуже?
> Очень хвалят sphinx - он поставляется как плагин к mysql.
Часто встречал положительные отзывы.
> Советую прочитать книгу High Perfomance MySQL
Благодарю. Там уже 3 части, с какой лучше начинать, если у меня уровень ниже среднего? - оцениваю себя реально, т.к. вижу, что многого не знаю, хотя писал большие и сложные запросы, но этого мало

> А в чем мучения заключаются? Я слышал обратное, что с базой будет быстрей.
Как всегда - проблемы роста. Со временем логи увеличиваются в размере и становится неудобно хранить их в базе. Начинается усложнение - разбиение на более мелкие таблицы, оптимизация. Не стоит оно таких усилий. Быстрее и проще будет писать логи в файл (и ротировать их).
Можно конечно и в базу, но только если эти данные будут потом использоваться. Например логгировать неудачные попытки входа и автоматом блокировать дальнейшие попытки при определённом количестве неудачных логинов за какой-то промежуток времени.
А если приложение просто логгирует цикл своей жизнедеятельности - однозначно в файл. Такие логи нужны только когда произошел косяк - для выяснения причин его возникновения.

> Конфликтов никаких не будет, если их джойнить к примеру?
Если в одной транзакции были изменены данные одновременно в innodb и myisam, то при откате, изменения в innodb откатятся, а в myisam нет. В остальном ничего особенного.

>> Myisam тоже может схлопотать дедлок
> Разве? Блин, читал на многих ресурсах, что дедлока нет на этом типе.
Не стоит заморачиваться на этом. Я ещё ни разу вживую не видел дедлок в myisam и даже запросы, которые потенциально могут к нему привести. Может действительно дедлоки невозможны. Не знаю почему мне кажется по-другому. Лень проверить :)

>> Последствия всегда одинаковые - одна из заблокированных транзакций принудительно откатывается.
> Так в myisam нет же транзакций.
Так вопрос же был по innodb.

>> Лучше отдать это на откуп специализированному софту.
> Разве встроенный полнотекстовый поиск хуже?
Специализированное решение всегда лучше.

> Благодарю. Там уже 3 части, с какой лучше начинать, если у меня уровень ниже среднего?
Не понял про части. Наверное речь про издания? У книги три издания. Лучше брать самое свежее издание - третье. Для чтения этой книги достаточно минимальных знаний SQL, MySQL, индексов и хотя бы какой-нибудь практический опыт. Думаю с этим у вас проблем не возникнет.

> А если приложение просто логгирует цикл своей жизнедеятельности - однозначно в файл.
Теперь понял. В принципе так и делал ранее.
> Если в одной транзакции были изменены данные одновременно в innodb и myisam, то при откате, изменения в innodb откатятся, а в myisam нет. В остальном ничего особенного.
Понял, но я имел ввиду джойнить myisam и innodb при выборке.
> Может действительно дедлоки невозможны.
Скорее для myisam это так, что не возможны. Но я беспокоюсь за innodb, т.к. буду использовать ее полностью или частично.
> Специализированное решение всегда лучше.
Понял.
> Думаю с этим у вас проблем не возникнет.
Как бы да, но мой английский не достаточен для ее прочтения. Русскую версию для скачивания я не нашел. Нужно будет в магазинах поискать.

Чего мне не хватает на сегодня, так это знаний по транзакциям (для повышенной надежности работы с таблицами) и блокировкам (дабы не схлопотать дедлок).

2. основная фишка innodb - это транзакции. если вы их не собираетесь использовать, то в большинстве случаев вполне подойдет myisam. еще считается, что в innodb легче восстанавливать данные, хотя у знакомого наоборот с ней было больше проблем в этом плане. как раз из-за пункта 6

Транзакции - не основная фишка innodb. Innodb, в основном, выбирают из-за построчных блокировок, а не из-за наличия транзакций. Для хайлоада myisam противопоказан из-за блокировок на уровне таблицы.

Мне не нравится, что вся таблица блокируется в myisam. Довольно часто может возникать одновременная запись в одну и ту же таблицу.