Форум пользователей MySQL

paulus написал:

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

если Primary key составной, то куда ссылаются остальные ключи? если например остальных - 15(ключей), насколько это усложняет жизнь MySQL?(из ветки 5.1.62)

rgbeast написал:

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

т.е. лучше в качестве primary создать быстрый и короткий ключ, а составной вынести в обычный индекс?

и чем плоха ситуация, когда в составном primary key первый ключ( Seq_in_index = 1) с Cardinality 25, а второй( Seq_in_index = 2 ) с Cardinality 12527361?

Очень хорошая темаи качественное обсуждение.
Я ищу ответа на один вопрос который меня долго мучает.
Недавно я столкнулся с интересным подходом в проектировании бд.
Он заключался в том что у таблиц не было внешних ключей FK, зато у таблиц были сложные составные PK ключи.
Этакий специфичный способ нормализации. В моей практике я вижу такое впервые.
Для меня пока понятно одно что первичный ключь очень раздут и будет неприлично много памяти.
У меня вопрос зачем это? И как быстро это будет работаьь?

Ниже пример одной из таблиц. Из 11 полей -  являются PK.

rgbeast написал:

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

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

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

Т.е как минимум это решит возможную проблему которую хотел решить DBA - уникальность записи. И позволит программисту использовать таблицы без лишних телодвижений.

Вот и получается дилемма:
1 Подход с natural keys насколько он добавит overhead по сравнению с surogate keys подходом.
2 Как это будет работать при использовании реплик, шардов(явно в структуре таблицы заложена такая фича).

Здравствуйте!

Подскажите пожалуйста, если у меня есть tbl1 JOIN tbl2 ON tbl1.col1=tbl2.col1/tbl2.col2, то как лучше настроить индексы в tbl2? Отдельно col1 и col2, или двойной (col1, col2)? И если двойной, то важен ли в данном случае порядок и будет ли он работать при tbl1 JOIN tbl2 ON tbl1.col1=tbl2.col2*tbl2.col1, например?

Спасибо за ответ!

vasya написал:

в 5.7 можно сделать индекс на генерируемую колонку

А можно чуть подробнее?

Имеет ли смысл очередность полей при установке составного индекса в зависимости от количества уникальных значений конкретного поля?
Например есть запрос: WHERE city='Moscow' and age='33'
если упростить, то age может быть от 0 до 100 (всего 101 вариация значений), а количество возможных городов city гораздо больше 101 и всего поле city имеет скажем 5000 различных значений.
Как будет правильнее создать в таком случае составной индекс?
CREATE INDEX idx1 ON table (age, city)
или
CREATE INDEX idx2 ON table (city, age)

посоветуйте пожалуйста, как быть с созданием составных индексов при следующей структуре:
Есть колонки A, B, C, D, E

A VARCHAR(128)
B VARCHAR(128)
C INT
D INT
E TINYINT

Есть ли необходимость создания всех нижеследующих индексов при условии что необходимы такие запросы:

искать по A и B или B и С (запрос вида: WHERE (A="n" AND B="m") OR (B="n" AND A="m") )
CREATE INDEX indx1 ON table (A, B)
CREATE INDEX indx2 ON table (B, A)

искать по C и D ( WHERE C="..." AND D="..." )
CREATE INDEX indx3 ON table (C, D)

искать по A и B и C ( WHERE A="..." AND B="..." AND C="..." )
CREATE INDEX indx4 ON table (A, B, C)

искать по A и B и C и D
CREATE INDEX indx5 ON table (A, B, C, D)

искать по A и B и E
CREATE INDEX indx6 ON table (A, B, E)

искать по A и B и C и D и E
CREATE INDEX indx7 ON table (A, B, C, D, E)

База большая на 100 млн строк, лишние индексы ни к чему. Я так понимаю что от индексов indx4 и indx5 можно успешно отказаться, оставив вместо них только indx7 и это никак не скажется на скорости выборки?

Отредактированно tyir (05.07.2017 12:12:08)

klow написал:

Сделать составные индексы по А, B и C, D. Возможно отдельный индекс E. Смысл делать индекс по E зависит от данных.

E может принимать только одно из трех возможных значений 0, 1 или 2.

Если создать только два индекса по А, B и второй по C, D - тогда в запросе искать по A и B и C и D будет задействовано оба индекса сразу? Это более эффективно чем один индекс по A, B, C, D ?

Не пойму, какой индекс лучше использовать и почему - идекс enabled+id_ext или просто id_ext
Поле enabled int(1)
Поле id_ext int(11)
всего 400000 записей
Запрос:

{
"query_block": {
   "select_id": 1,
   "cost_info": {
     "query_cost": "1.32"
   },
   "nested_loop": [
     {
       "table": {
         "table_name": "main",
         "access_type": "ref",
         "possible_keys": [
           "type_id",
           "id_ext"
         ],
         "key": "id_ext",
         "used_key_parts": [
           "id_ext"
         ],
         "key_length": "4",
         "ref": [
           "const"
         ],
         "rows_examined_per_scan": 1,
         "rows_produced_per_join": 0,
         "filtered": "10.00",
         "cost_info": {
           "read_cost": "1.00",
           "eval_cost": "0.02",
           "prefix_cost": "1.20",
           "data_read_per_join": "4"
         },
         "used_columns": [
           "id",
           "id_ext",
           "title",
           "type_id",
           "date_insert"
         ],
         "attached_condition": "(`db1`.`main`.`enabled` = 1)"
       }
     },
     {
       "table": {
         "table_name": "type",
         "access_type": "eq_ref",
         "possible_keys": [
           "PRIMARY"
         ],
         "key": "PRIMARY",
         "used_key_parts": [
           "id"
         ],
         "key_length": "4",
         "ref": [
           "db1.main.type_id"
         ],
         "rows_examined_per_scan": 1,
         "rows_produced_per_join": 0,
         "filtered": "100.00",
         "cost_info": {
           "read_cost": "0.10",
           "eval_cost": "0.02",
           "prefix_cost": "1.32",
           "data_read_per_join": "31"
         },
         "used_columns": [
           "id",
           "name"
         ]
       }
     }
   ]
}
}

Запрос с индексом enabled+id_ext

{
"query_block": {
   "select_id": 1,
   "cost_info": {
     "query_cost": "2.40"
   },
   "nested_loop": [
     {
       "table": {
         "table_name": "main",
         "access_type": "ref",
         "possible_keys": [
           "type_id",
           "id_ext_2"
         ],
         "key": "id_ext_2",
         "used_key_parts": [
           "id_ext",
           "enabled"
         ],
         "key_length": "5",
         "ref": [
           "const",
           "const"
         ],
         "rows_examined_per_scan": 1,
         "rows_produced_per_join": 1,
         "filtered": "100.00",
         "cost_info": {
           "read_cost": "1.00",
           "eval_cost": "0.20",
           "prefix_cost": "1.20",
           "data_read_per_join": "48"
         },
         "used_columns": [
           "id",
           "id_ext",
           "title",
           "type_id",
           "date_insert"
         ]
       }
     },
     {
       "table": {
         "table_name": "type",
         "access_type": "eq_ref",
         "possible_keys": [
           "PRIMARY"
         ],
         "key": "PRIMARY",
         "used_key_parts": [
           "id"
         ],
         "key_length": "4",
         "ref": [
           "db1.main.type_id"
         ],
         "rows_examined_per_scan": 1,
         "rows_produced_per_join": 1,
         "filtered": "100.00",
         "cost_info": {
           "read_cost": "1.00",
           "eval_cost": "0.20",
           "prefix_cost": "2.40",
           "data_read_per_join": "312"
         },
         "used_columns": [
           "id",
           "name"
         ]
       }
     }
   ]
}
}

Отредактированно Vanzent (09.08.2017 16:13:47)

В зависимости от версии MySQL explain'ы могут отличаться. Приведите все планируемые запросы и explain'ы к ним.

>1. Какие существуют техники для оптимизации запросов по диапазону значений ?

where a in (1,5,10) and ... (другое условие)
меняется на
where a = 1 and ... (другое условие)
union all
where a = 5 and ... (другое условие)
union all
where a = 10 and ... (другое условие)

в некоторых случаях такое помогает

>2. Работают ли составные индексы для запросов с конструкцией IN() ?
Ну так не для красного словца же написано
Приведите все планируемые запросы и explain'ы к ним

Как правильно сказал deadka все может отличаться в конкретной реализации. Но я бы порекомендовал, для вашего случая, 3 индекса.
1. type + station
2. time_departure
3. time_arrival