Форум MySQL

Никак не могу определиться с выбором ключей в базе.
Например есть таблица PRICE, в которой каждое наименование однозначно определяется полями KOD и NUM.
Например есть в прайсе (речь о мебели) антресоль 101 и 102 и шкаф 101 и 102.
В таблице price будут строки, соответственно:
АН | 101
АН | 102
ШК | 101
ШК | 102
Можно сделать составной первичный ключ по этим 2ум полям либо добавить поле id и сделать ключ по нему.
С одной стороны во втором случае лишнее поле.
С другой стороны, есть еще таблица соответствий цветов, в которой прописано что может быть какого цвета. Значит если сделать в таблице price PK составным, то и в таблице цветов будет 2 поля:
АН | 101 | зел
АН | 101 | крас
АН | 102 | зел
вместо
1 | зел
1 | крас
2 | зел
При чем в таблице цветов снова та же ситуация: С одной стороны 1-зел, 2-крас достаточно для идентификации товара, с другой - будет еще таблица заказа, где еще будут указаны штуки каждого наименования. И т.д.

Вроде логично не делать составных ключей, а сделать для каждой таблицы id. Но тогда при выборках будет возрастать вложенность запросов!

Например для распечатки договора по таблице заказа из соответствующей таблицы нужно сперва выбрать id товара и его количество, затем по ид товара выбрать его цвет и id наименования, затем по id наименования выбрать что это и все это слепить воедино, например АН-101-зел-2шт

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

Кто как сделал бы и почему?

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

А почему речь только о каталоге?
Каталог-то не большой.
Но я ж не всю структуру бд показал.
Если для составления годового отчета надо будет перемножить (переджойнить) 6-8 таблиц? Это какого ж размера будеть промежуточная таблица???

Если объединение идет по первичному ключу, то может так статься, что данные из таблицы задействоваться не будут, достаточно будет ключей или какого-то диапазона таблицы - работать будет ключ, в этом его смысл. В любом случае оценить размер можно будет через EXPLAIN, эта команда, кстати, сообщает о том, как используется ключ.

EXPLAIN показателен когда будет объем данных.... А он будет в процессе использования. А в процессе использования менять структуру будет проблематично =)

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

PS Если вы подозреваете большие объемы в будущем, стоит думать о том, чтобы таблицы были маленькие, особенно, те, которые будете объединять, желательно, чтобы они сплошь состояли из числовых индексов, а весь текст хранился в отдельных таблицах.

Судя по всему интуиция побеждает =)
Интуитивно хотелось сделать с суррогатными ключами, но логика не позволяла =)

З.Ы. Я думал, что в индексе и при поиске по индексу любая выборка все равно сводится к работе с целыми числами.

Если у вас индекс по строковому полю, то нет, индекс будет тоже строковым (пусть и ограниченным по количеству символов...).

> На самом деле меняют, локализуют код для работы с базой данных в модели и
> постоянно проводят рефакторинг...

Значит, думаю, нужно, как кто-то тут советовал, выносить все sql-запросы в отдельный файл в виде статических функций разных вспомогательных классов, чтобы в случае чего не по всему коду их выискивать, а все в одном файле.

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

Интересно... Я подумаю. Спасибо!

А еще вопрос про дополнительный индекс.
Вообще в базе будет неограниченно расти количество договоров.
А при формировании любых отчетов идет выборка в первую очередь по дате, и потом уже к полученному приклеивается все остальное.
Я правильно понимаю, что лучше добавить индекс на поле даты заключения договора?
Вот два Эксплэйна:

+----+-------------+------------+--------+---------------------------+------------+---------+-----------------------------+------+---------------------------------+ | id | select_type | table      | type   | possible_keys             | key        | key_len | ref                         | rows | Extra                           | +----+-------------+------------+--------+---------------------------+------------+---------+-----------------------------+------+---------------------------------+ |  1 | PRIMARY     | <derived2> | ALL    | NULL                      | NULL       | NULL    | NULL                        |   12 | Using temporary; Using filesort | |  2 | DERIVED     | <derived6> | system | NULL                      | NULL       | NULL    | NULL                        |    1 |                                 | |  2 | DERIVED     | <derived7> | system | NULL                      | NULL       | NULL    | NULL                        |    1 |                                 | |  2 | DERIVED     | <derived3> | ALL    | NULL                      | NULL       | NULL    | NULL                        |   12 |                                 | |  7 | DERIVED     | NULL       | NULL   | NULL                      | NULL       | NULL    | NULL                        | NULL | No tables used                  | |  6 | DERIVED     | NULL       | NULL   | NULL                      | NULL       | NULL    | NULL                        | NULL | No tables used                  | |  3 | DERIVED     | <derived4> | ALL    | NULL                      | NULL       | NULL    | NULL                        |   23 | Using temporary; Using filesort | |  3 | DERIVED     | color_mod  | eq_ref | PRIMARY,price_id,color_id | PRIMARY    | 4       | t.color_mod_id              |    1 |                                 | |  3 | DERIVED     | color      | eq_ref | PRIMARY                   | PRIMARY    | 1       | sfinks_l.color_mod.color_id |    1 | Using where                     | |  3 | DERIVED     | price      | eq_ref | PRIMARY,naimen_id         | PRIMARY    | 4       | sfinks_l.color_mod.price_id |    1 | Using where                     | |  3 | DERIVED     | naimen     | eq_ref | PRIMARY                   | PRIMARY    | 4       | sfinks_l.price.naimen_id    |    1 |                                 | |  4 | DERIVED     | <derived5> | ALL    | NULL                      | NULL       | NULL    | NULL                        |    6 |                                 | |  4 | DERIVED     | position   | ref    | dogovor_id                | dogovor_id | 4       | t.dogovor_id                |    1 |                                 | |  5 | DERIVED     | dogovor    | ALL    | NULL                      | NULL       | NULL    | NULL                        |    9 | Using where                     | +----+-------------+------------+--------+---------------------------+------------+---------+-----------------------------+------+---------------------------------+ | id | select_type | table      | type   | possible_keys             | key        | key_len | ref                         | rows | Extra                           | +----+-------------+------------+--------+---------------------------+------------+---------+-----------------------------+------+---------------------------------+ |  1 | PRIMARY     | <derived2> | ALL    | NULL                      | NULL       | NULL    | NULL                        |   12 | Using temporary; Using filesort | |  2 | DERIVED     | <derived6> | system | NULL                      | NULL       | NULL    | NULL                        |    1 |                                 | |  2 | DERIVED     | <derived7> | system | NULL                      | NULL       | NULL    | NULL                        |    1 |                                 | |  2 | DERIVED     | <derived3> | ALL    | NULL                      | NULL       | NULL    | NULL                        |   12 |                                 | |  7 | DERIVED     | NULL       | NULL   | NULL                      | NULL       | NULL    | NULL                        | NULL | No tables used                  | |  6 | DERIVED     | NULL       | NULL   | NULL                      | NULL       | NULL    | NULL                        | NULL | No tables used                  | |  3 | DERIVED     | <derived4> | ALL    | NULL                      | NULL       | NULL    | NULL                        |   23 | Using temporary; Using filesort | |  3 | DERIVED     | color_mod  | eq_ref | PRIMARY,price_id,color_id | PRIMARY    | 4       | t.color_mod_id              |    1 |                                 | |  3 | DERIVED     | color      | eq_ref | PRIMARY                   | PRIMARY    | 1       | sfinks_l.color_mod.color_id |    1 | Using where                     | |  3 | DERIVED     | price      | eq_ref | PRIMARY,naimen_id         | PRIMARY    | 4       | sfinks_l.color_mod.price_id |    1 | Using where                     | |  3 | DERIVED     | naimen     | eq_ref | PRIMARY                   | PRIMARY    | 4       | sfinks_l.price.naimen_id    |    1 |                                 | |  4 | DERIVED     | <derived5> | ALL    | NULL                      | NULL       | NULL    | NULL                        |    6 |                                 | |  4 | DERIVED     | position   | ref    | dogovor_id                | dogovor_id | 4       | t.dogovor_id                |    1 |                                 | |  5 | DERIVED     | dogovor    | ALL    | dogovor_add_date          | NULL       | NULL    | NULL                        |    9 | Using where                     | +----+-------------+------------+--------+---------------------------+------------+---------+-----------------------------+------+---------------------------------+

Первый без ключа на дате, второй с ключем.
Но ключ почему-то все-равно не используется.

Какой смысл и почему не используется?

А главное - почему не используется?
Последняя строка относится к самому вложенному подзапросу, а именно:

SELECT dogovor_id  FROM dogovor  WHERE dogovor_add_date BETWEEN '2012-09-17 00:00:00' AND '2012-09-23 23:59:59'   AND dogovor_status='аванс'

Из-за аванса? И что, добавить индекс на статус или тоже разбить на 2 подзапроса?

Вообще с индексированием дат в SQL-формате что-то не чисто, они у вас без аванса нормально индексируются? Были версии MySQL (не отслеживал исправили или нет), которые никак не хотели использоваться индекс по дате, поэтому высоконагруженные проекты стараются сразу переводить на UNIXSTAMP - дата компактная и прекрасно индексируется.

> стараются сразу переводить на UNIXSTAMP - дата компактная и прекрасно индексируется
Я даты в TIMESTAMP храню. По крайней мере всегда когда это возможно. Это по сути целочисленный формат, так что проблем быть не должно.
Кстати, статус тут ENUM, что по сути тоже целочисленный формат. Так что должно быть шустро.

С индексом

... FORCE INDEX(dogovor_add_date)

помогло. Эксплэйн стал:

5 | DERIVED | dogovor | range | dogovor_add_date | dogovor_add_date | 4 | NULL | 5 | Using where

вместо

5 | DERIVED | dogovor | ALL | dogovor_add_date | NULL | NULL | NULL | 14 | Using where