Какие стоит указать индексы? / Проектирование БД

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Какие стоит указать индексы?

19 сообщений из 19, страница 1 из 1

Какие стоит указать индексы?

#39867220

kormot

Участник

Сообщения: 292

Рейтинг: 0 / 0

Добрый день!

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

Код: sql

1.
2.
3.
4.
5.
6.
7.

Table TabA (
     id        SERIAL,
     fkObjID   BIGINT UNSIGNED NOT NULL,
     objName   CHAR(255) NOT NULL, 
     orderNum  TINYINT UNSIGNED NOT NULL,
     FK(fkObjID) => other_table(id)
)

И запросы к этой таблице будут очевидными:

Код: sql

1.
2.
3.
4.

SELECT aa.id, aa.objName 
FROM   TabA aa 
WHERE  aa.fkObjID=${OBJ_ID}
ORDER BY orderNum ASC

В такой ситуации надо ли в таблице определять такие индексы:

Код: sql

1.
2.
3.
4.
5.
6.

KEY fkIdx (fkObjID),
KEY ordIdx (orderNum)
или надо делать такой:
KEY objOrdIdx (fkObjID, orderNum)
или вообще надо определить уникальный (принципиально в задаче мне это не требуется, но и мешать уникальность не будет)
UNIQUE KEY objOrdIdx (fkObjID, orderNum)

Дайте пожалуйста дельный совет!

...

Рейтинг:

0 / 0

26.09.2019, 11:03

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39867291

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

kormotВ такой ситуации надо ли в таблице определять такие индексы

Обычно не надо. Но в зависимости от используемой СУБД и её оптимизатора могут быть нюансы.
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

26.09.2019, 13:21

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39867337

vadiminfo

Участник

Откуда: Обнинск

Сообщения: 4 779

Рейтинг: 0 / 0

kormotДобрый день!

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

Код: sql

1.
2.
3.
4.
5.
6.
7.

Table TabA (
     id        SERIAL,
     fkObjID   BIGINT UNSIGNED NOT NULL,
     objName   CHAR(255) NOT NULL, 
     orderNum  TINYINT UNSIGNED NOT NULL,
     FK(fkObjID) => other_table(id)
)

И запросы к этой таблице будут очевидными:

Код: sql

1.
2.
3.
4.

SELECT aa.id, aa.objName 
FROM   TabA aa 
WHERE  aa.fkObjID=${OBJ_ID}
ORDER BY orderNum ASC

В такой ситуации надо ли в таблице определять такие индексы:

Код: sql

1.
2.
3.
4.
5.
6.

KEY fkIdx (fkObjID),
KEY ordIdx (orderNum)
или надо делать такой:
KEY objOrdIdx (fkObjID, orderNum)
или вообще надо определить уникальный (принципиально в задаче мне это не требуется, но и мешать уникальность не будет)
UNIQUE KEY objOrdIdx (fkObjID, orderNum)

Дайте пожалуйста дельный совет!

Формально, решение принимается на таких вещах как план запроса и представлении о селективности индексируемого поля.
(Например, для поля типа пол, обычные индексы могут только ухудшить. Лучше какой-нибудь bitmap. Но такой для оперативных БД вообще не очень подходит.
У Оракла, Фул скан считывает по несколько блоков за раз, а с индексом по одному.)

Но составной индекс для такого запроса выглядит как-то не очень. Составной напрашивался бы при условии
WHERE aa.fkObjID=….
AND orderNum =…

Т.е. по этому запросу без дополнительной информации только
KEY fkIdx (fkObjID),

И то при условии что мало строк получим при условии aa.fkObjID=${OBJ_ID}.

...

Рейтинг:

0 / 0

26.09.2019, 14:36

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39867350

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

vadiminfoТ.е. по этому запросу без дополнительной информации только
KEY fkIdx (fkObjID)

И то только если индекс на внешний ключ не создаётся сервером автоматически. Как я и
сказал выше.
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

26.09.2019, 14:51

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39867389

Leonid Kudryavtsev

Участник

Сообщения: 9 300

Рейтинг: 0 / 0

Dimitry SibiryakovНо в зависимости от используемой СУБД и её оптимизатора могут быть нюансы.

+++

При двоичной сортировке и необходимости супер-быстрого выполнения запроса, может вообще появится желание аж сделать индекс:

fkObjID, orderNum, objName

1) поиск по индексу
2) исключение сортировки за счет считывания упорядоченных данных из индекса (зависит от БД и настроек, сортировка должна быть binary, для Oracle могут потребоваться хинты INDEX_ASC)
3) все поля результата в индексе, исключается обращения к исходной таблицы

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

Отстувие обращение к исходной таблице - вместо рандом доступа к индексу и исходной таблице, получаем упорядоченный (последовательный) доступ к индексу. Шминделя крутящихся дисков говорят спасибо

...

Рейтинг:

0 / 0

26.09.2019, 15:43

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39867480

KreatorXXI

Участник

Откуда: Москва

Сообщения: 1 106

Рейтинг: 0 / 0

Leonid Kudryavtsev,

мало ли какая мне сортировка в такой выборке понадобится. Надо с каждым полем (и не с одним) FK склеивать? По-моему, лишнее это.

...

Рейтинг:

0 / 0

26.09.2019, 18:12

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39867562

Дмитрий Мух

Участник

Откуда: Зеленоград

Сообщения: 2 722

Рейтинг: 0 / 0

KreatorXXILeonid Kudryavtsev,

мало ли какая мне сортировка в такой выборке понадобится. Надо с каждым полем (и не с одним) FK склеивать? По-моему, лишнее это.wat? переведите пожалуйста..

...

Рейтинг:

0 / 0

26.09.2019, 21:57

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39867688

Сергей Васкецов

Участник

Сообщения: 19 216

Рейтинг: 0 / 0

kormot,

1. Первичный ключ где?
2. Уникальный индекс надо сделать. Он покроет как FK, так и вопрос целостности данных. Уникальные индексы вообще не для скорости работы создаются и не для того, что они кому-то очень нравятся.

...

Рейтинг:

0 / 0

27.09.2019, 08:17

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39868257

stenford

Участник

Откуда: урал

Сообщения: 2 741

Рейтинг: 0 / 0

kormotДайте пожалуйста дельный совет!
для начала надо разделять логические условия на таблицу и "физические". Под логическими будут определены такие вещи как PK и требования уникальности, сервер для них создаст необходимые индексы и они не зависят ни от каких запросов. Дальнейшие индексы и необходимость в них определяется исключительно как с данными будут работать, например нужно определить кластерный индекс. Необходимость в каждом конкретном индексе определяется типами запросов к базе, OLAP/OLTP, результатами нагрузочных тестов итп

...

Рейтинг:

0 / 0

28.09.2019, 01:21

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39868321

полудух

Участник

Откуда: планета орков, г.Зверополис

Сообщения: 1 099

Рейтинг: 0 / 0

если такой индекс:

Код: sql

KEY objOrdIdx (fkObjID, orderNum)

слепить при таком ORDER BY:

Код: sql

ORDER BY orderNum ASC

то он вообще не сработает
потому что orderNum работает ПОСЛЕ fkObjID

...

Рейтинг:

0 / 0

28.09.2019, 09:06

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39868356

kormot

Участник

Сообщения: 292

Рейтинг: 0 / 0

stenfordдля начала надо разделять логические условия на таблицу и "физические". Под логическими будут определены такие вещи как PK и требования уникальности, сервер для них создаст необходимые индексы и они не зависят ни от каких запросов. Дальнейшие индексы и необходимость в них определяется исключительно как с данными будут работать, например нужно определить кластерный индекс. Необходимость в каждом конкретном индексе определяется типами запросов к базе, OLAP/OLTP, результатами нагрузочных тестов итп
Охеренно понятный и аргументированный ответ! Спасибо!

Сергей Васкецов1. Первичный ключ где?
2. Уникальный индекс надо сделать. Он покроет как FK, так и вопрос целостности данных. Уникальные индексы вообще не для скорости работы создаются и не для того, что они кому-то очень нравятся.
1. Первичный ключ там - это id SERIAL
2. Хорошо, учту.

...

Рейтинг:

0 / 0

28.09.2019, 12:25

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39868365

miksoft

Участник

Сообщения: 36 746

Рейтинг: 0 / 0

kormot,

СУБД-то какая?

Некоторые СУБД умеют использовать индекс KEY objOrdIdx (fkObjID, orderNum) для отбора записей по fkObjID и последующей сортировки по orderNum.

CHAR(255) выглядит странно. Почему не VARCHAR ?

И так ничего и не было сказано про статистику/демографию данных.
А это важно для решения использовать или не использовать тот или иной индекс.
Например, если условие aa.fkObjID=${OBJ_ID} отбирает половину записей таблицы, то никакие индексы не помогут, за исключением, разве что, покрывающего. Будет ли толк от покрывающего индекса - тоже зависит от множества факторов, вплоть до того, лежит ли он уже в кэше (и помещается ли туда) или нет.

...

Рейтинг:

0 / 0

28.09.2019, 12:39

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39868461

kormot

Участник

Сообщения: 292

Рейтинг: 0 / 0

miksoft,
СУБД - MariaDB 10.3
CHAR(255) - это просто тут написал. Весь пример таблиц - чисто для наглядности. Сама идея только что есть параметр по которому отбирают, и есть по которому сортируют.

Статистика базы относительно равномерная предполагается. Т.е. для разных ${ObjID} сравнимые кол-ва записей.

...

Рейтинг:

0 / 0

28.09.2019, 19:14

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39868642

softwarer

Участник

Откуда: 127.0.0.1

Сообщения: 51 965

Рейтинг: 0 / 0

kormotВ такой ситуации надо ли в таблице определять такие индексы:

Код: sql

KEY fkIdx (fkObjID)

Зависит от, но скорее стоит, чем нет.

kormot

Код: sql

KEY ordIdx (orderNum)

Совершенно бессмысленно.

kormot

Код: sql

KEY objOrdIdx (fkObjID, orderNum)

Такой индекс может иметь смысл, если количество orderNum на один objID велико и если СУБД сумеет его использовать. Но скорее всего, он окажется на уровне первого по полезности.

kormot

Код: sql

UNIQUE KEY objOrdIdx (fkObjID, orderNum)

Если мешать не будет, лучше определить. Бывает, что декларативные ограничения целостности очень лихо спасают задницу от неприятностей.

...

Рейтинг:

0 / 0

29.09.2019, 15:00

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39869877

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

Для этого запроса подойдут индексы

KEY fkIdx (fkObjID),

или

KEY objOrdIdx (fkObjID, orderNum)

один из двух.

Второй потенциально может быть испльзован для ORDER BY
но если записей немного, скажем, 10-30, то смысла в составном индексе вместо по одному полю, мало.

...

Рейтинг:

0 / 0

01.10.2019, 12:52

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39871024

kormot

Участник

Сообщения: 292

Рейтинг: 0 / 0

Большое спасибо Уважаемые, за полезные ответы!

...

Рейтинг:

0 / 0

03.10.2019, 11:15

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39871131

полудух

Участник

Откуда: планета орков, г.Зверополис

Сообщения: 1 099

Рейтинг: 0 / 0

для 10-30 индекс вообще не нужен
индекс от сотен начинается, даже от 1000

...

Рейтинг:

0 / 0

03.10.2019, 13:20

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39872707

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

kormot,

Тут надо делать индекс

KEY fkIdx (fkObjID),

ИЛИ

KEY objOrdIdx (fkObjID, orderNum)

...

Рейтинг:

0 / 0

07.10.2019, 16:10

| Ответить | Цитировать | Написать

Какие стоит указать индексы?

#39872708

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

Блин!Блин!Блин!Блин!Блин!

...

Рейтинг:

0 / 0

07.10.2019, 16:11

| Ответить | Цитировать | Написать

19 сообщений из 19, страница 1 из 1

Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Какие стоит указать индексы?

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=32&msg=39871024&tid=1539904]:	0ms
get settings:	11ms
get forum list:	15ms
check forum access:	2ms
check topic access:	2ms
track hit:	137ms
get topic data:	11ms
get forum data:	3ms
get page messages:	60ms
get tp. blocked users:	2ms
others:	13ms

total:	256ms