SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#26 15.05.2012 01:48:44

inkognitotester
Участник
Зарегистрирован: 15.05.2012
Сообщений: 12

Re: Использование составных индексов

paulus написал:

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

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

Неактивен

 

#27 15.05.2012 01:53:50

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Использование составных индексов

inkognitotester написал:

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

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

Неактивен

 

#28 15.05.2012 02:04:50

inkognitotester
Участник
Зарегистрирован: 15.05.2012
Сообщений: 12

Re: Использование составных индексов

rgbeast написал:

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

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

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

Неактивен

 

#29 15.05.2012 17:00:11

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Использование составных индексов

inkognitotester написал:

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

да

inkognitotester написал:

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

в общем ни чем не плоха, а в частности зависит от задачи.

inkognitotester, обратите внимание на формат цитирований на форуме

Неактивен

 

#30 28.08.2012 14:23:00

Cioxideru
Участник
Зарегистрирован: 28.08.2012
Сообщений: 2

Re: Использование составных индексов

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

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


delimiter $$

CREATE TABLE `MY_TABLE` (
  `Customer` char(1) NOT NULL,
  `Customer_uid` decimal(14,0) NOT NULL,
  `Customer_group_uid` decimal(12,0) NOT NULL,
  `key_db_type` char(2) NOT NULL,
  `key_db_instance` int(11) NOT NULL,
  `Customer_valid_for_seq` int(11) NOT NULL,
  `share_seq` smallint(6) NOT NULL,
  `social_site_code` char(2) NOT NULL,
  `date_processed` datetime NOT NULL,
  `short_comment` varchar(100) DEFAULT NULL,
  `return_link_hash` decimal(14,0) NOT NULL,
  PRIMARY KEY (
`Customer`,
`Customer_uid`,
`Customer_group_uid`,
`key_db_type`,
`key_db_instance`,
`Customer_valid_for_seq`,
`share_seq`)
) ENGINE=InnoDB$$
 

Неактивен

 

#31 28.08.2012 17:26:12

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Использование составных индексов

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

Неактивен

 

#32 28.08.2012 18:13:14

Cioxideru
Участник
Зарегистрирован: 28.08.2012
Сообщений: 2

Re: Использование составных индексов

rgbeast написал:

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

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

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

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

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

Неактивен

 

#33 28.08.2012 19:17:21

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Использование составных индексов

Длина индекса будет суммой длины данных - причем для varchar это будет фиксированная полная длина (если utf8, то varchar(100) это 300 байт). Можете посмотреть SHOW TABLE STATUS LIKE 'MY_TABLE' - там увидите объем данных и индекса. Наверное, наиболее простым будет сделать отдельное уникальное автоинкрементируемое поле и индекс по нему. Для шардов вроде бы удобнее, когда индекс целочисленный (но я не знаю структуру вашего приложения).

Неактивен

 

#34 27.01.2017 13:26:16

fantom
Участник
Зарегистрирован: 27.01.2017
Сообщений: 2

Re: Использование составных индексов

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

Подскажите пожалуйста, если у меня есть 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, например?

Неактивен

 

#35 27.01.2017 13:45:00

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Использование составных индексов

в данных случаях индексы на tbl2 использоваться не будут

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

Неактивен

 

#36 27.01.2017 14:41:21

fantom
Участник
Зарегистрирован: 27.01.2017
Сообщений: 2

Re: Использование составных индексов

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

vasya написал:

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

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

Неактивен

 

#37 27.01.2017 14:50:19

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Использование составных индексов

создаете виртуальную колонку, индексируете и используете в запросе вместо tbl2.col1/tbl2.col2 или tbl2.col2*tbl2.col1

подробней есть в доке
https://dev.mysql.com/doc/refman/5.7/en … lumns.html
и
https://mariadb.com/kb/en/mariadb/virtu … d-columns/

Неактивен

 

#38 04.07.2017 18:10:30

tyir
Участник
Зарегистрирован: 04.07.2017
Сообщений: 3

Re: Использование составных индексов

Имеет ли смысл очередность полей при установке составного индекса в зависимости от количества уникальных значений конкретного поля?
Например есть запрос: 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)

Неактивен

 

#39 04.07.2017 18:19:24

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Использование составных индексов

Теоретически должно быть несколько быстрее во втором случае, так как при поиске по индексу сравнение будет в среднем происходить быстрее. Ожидаемое преимущество незначительно. На практике может быть все иначе из-за внутренних особенностей алгоритма. Проще всего измерить.
Индекс (city,age) имеет смысл сделать на случай, если на age будет range условие age>33.

Неактивен

 

#40 05.07.2017 12:10:28

tyir
Участник
Зарегистрирован: 04.07.2017
Сообщений: 3

Re: Использование составных индексов

посоветуйте пожалуйста, как быть с созданием составных индексов при следующей структуре:
Есть колонки 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)

Неактивен

 

#41 05.07.2017 12:26:40

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Использование составных индексов

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

Отредактированно klow (05.07.2017 12:28:44)

Неактивен

 

#42 05.07.2017 12:53:17

tyir
Участник
Зарегистрирован: 04.07.2017
Сообщений: 3

Re: Использование составных индексов

klow написал:

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

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

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

Неактивен

 

#43 05.07.2017 13:27:50

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Использование составных индексов

tyir написал:

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

Тут даже не важно что там за значения храниться, а важно сколько будет строк если  будет применено условие по полям A, B, C, D. Если после фильтра будет миллионы или тысячи строк, тогда стоит. Если сотни и десятки, то смысла особого нет.

tyir написал:

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

Один индекс не будет использоваться (работать) если будет условие только по полям C и D (WHERE C="..." AND D="..." ) и отсутствуют условия по полям A и B.

Неактивен

 

#44 09.08.2017 15:19:17

Vanzent
Участник
Зарегистрирован: 09.08.2017
Сообщений: 1

Re: Использование составных индексов

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

SELECT SQL_NO_CACHE m.id, m.id_ext, m.title, m.date_insert, t.name as tname FROM main m,type t WHERE m.type_id=t.id and m.enabled=1 and m.id_ext=111111

При использовании составного индекса увеличивается значение query_cost и data_read_per_join

Explain при индексе id_ext

{
"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)

Неактивен

 

#45 28.05.2019 19:31:26

andrey_55555
Участник
Зарегистрирован: 23.01.2019
Сообщений: 6

Re: Использование составных индексов

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

Есть таблица "schedule" с обычным расписанием.

Таблица с примерно такими полями:
train_number type station time_departure time_arrival

Создал два составных ключа:
type + station + time_departure
type + station + time_arrival


Надо выбирать все рейсы по определенной станции за прошедшие несколько часов и за будущие несколько часов.

Я так понимаю для запросов
SELECT * FROM `schedule` WHERE `type`='arrival' AND `station` ='xxxx'  AND `time_arrival` > 1559037600 AND AND `time_arrival` < 1559080800 ORDER BY `time_arrival` ASC;

составной ключ работать не будет.


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

Второй вопрос:
2. Работают ли составные индексы для запросов с конструкцией IN() ?

Например я могу добавить поле: event_hours
В это поле я могу записывать значение ЧАСА прибытия или отправления.
Тогда я смогу сделать такие составные ключи:
type + station + event_hours + time_departure
type + station + event_hours + time_arrival

И написать такой запрос:
SELECT * FROM `schedule` WHERE `type`='arrival' AND `station` ='xxxx'  AND `event_hours` IN(1559037600, .... ,1559080800) ORDER BY `time_arrival` ASC;

Будет ли работать составной индекс ?


Спасибо

Отредактированно andrey_55555 (28.05.2019 19:34:11)

Неактивен

 

#46 28.05.2019 19:45:46

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Использование составных индексов

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


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#47 28.05.2019 19:53:57

andrey_55555
Участник
Зарегистрирован: 23.01.2019
Сообщений: 6

Re: Использование составных индексов

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


И ответить
2. Работают ли составные индексы для запросов с конструкцией IN() ?

Или тут есть нюансы ?

Спасибо

Неактивен

 

#48 28.05.2019 20:50:23

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Использование составных индексов

>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'ы к ним wink


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#49 28.05.2019 21:45:12

andrey_55555
Участник
Зарегистрирован: 23.01.2019
Сообщений: 6

Re: Использование составных индексов

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

Не думал что все так сложно sad

Казалось типичная ситуация....


>Приведите все планируемые запросы и explain'ы к ним

Буду править код и пробовать...



Спасибо, Вам

Неактивен

 

#50 29.05.2019 09:00:41

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Использование составных индексов

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson