select

Cucuruza · Сообщение **Cucuruza** » 06 сен 2006, 13:41

Есть таблица, в ней около 50 млн. записей. Поле с типом integer содержащим одно из значений 1/2/3 (изменяется от 1 до 3). Есть составной индекс - это поле и id записи. Select выполняется быстро если указанное значении в условии запроса присутствует в таблице - с планом Natural, если значенния в таблице нет - с использованием индекса. Будет искомое значение в таблице - неизвестно. Как делать выборку?

WildSery · Сообщение **WildSery** » 06 сен 2006, 13:55

Правильно ли я понял - у тебя индекс по полю всего из трёх значений?
И к тому же одно из них может в таблице вообще не присутствовать, т.е. будет два значения на 50 млн. записей?

Хорошо бы написать селект, что конкретно ты выбираешь.

Cucuruza · Сообщение **Cucuruza** » 06 сен 2006, 13:58

Индекс из значения поля и первичного ключа.

WildSery · Сообщение **WildSery** » 06 сен 2006, 14:00

блин. просто напиши. особенно where интересует.

Cucuruza · Сообщение **Cucuruza** » 06 сен 2006, 14:05

WildSery писал(а):блин. просто напиши. особенно where интересует.

просто

Код: Выделить всё

SELECT * FROM TABLE WHERE COLUMN=3

Merlin · Сообщение **Merlin** » 06 сен 2006, 14:17

WildSery писал(а):блин. просто напиши. особенно where интересует.

Запасись терпением и набором пыточных инструментов. Прогнозирую, что вытягивание из партизана распределения значений в поле, последовательности сегментов в индексе, планов и статистики интересующих запросов растянется на пару суток. Я завтра загляну

WildSery · Сообщение **WildSery** » 06 сен 2006, 14:19

интересный у тебя селект. И куда ты пихаешь эти 16 с половиной миллионов записей?
Собственно, в этом твоя беда. Не селект быстр, а выдача первых данных. Без индекса для поиска несуществующей записи просматривается вся таблица.

ЗЫ: Судя по всему, в итоге ответ будет "читай книжку по проектированию БД".

Cucuruza · Сообщение **Cucuruza** » 06 сен 2006, 14:45

Fb 1.5.3

Индекс EJSTATE,EJOU_ID

a) В таблице есть одна запись со значением 2

Код: Выделить всё

Select * from ejou where ejstate=2

a1)

PLAN (EJOU INDEX (EJOU_IDX2))

------ Performance info ------
Prepare time = 0ms
Execute time = 0ms
Avg fetch time = 0,00 ms
Current memory = 50 642 456
Max memory = 100 249 548
Memory buffers = 2 048
Reads from disk to cache = 29
Writes from cache to disk = 6
Fetches from cache = 9 49

a2)

PLAN (EJOU NATURAL)

------ Performance info ------
Prepare time = 0ms
Execute time = 9m 21s 31ms
Avg fetch time = 561 031,00 ms
Current memory = 50 659 780
Max memory = 100 249 548
Memory buffers = 2 048
Reads from disk to cache = 752 235
Writes from cache to disk = 26
Fetches from cache = 167 155 011

b) В таблице около 30 млн. записей со значением 3
Select * from ejou where ejstate=3

b1)

PLAN (EJOU NATURAL)

------ Performance info ------
Prepare time = 0ms
Execute time = 62ms
Avg fetch time = 5,17 ms
Current memory = 50 659 780
Max memory = 100 249 548
Memory buffers = 2 048
Reads from disk to cache = 11
Writes from cache to disk = 6
Fetches from cache = 2 035

b2)

PLAN (EJOU INDEX (EJOU_IDX2))

------ Performance info ------
Prepare time = 0ms
Execute time = 2s 297ms
Avg fetch time = 191,42 ms
Current memory = 100 204 016
Max memory = 100 338 912
Memory buffers = 2 048
Reads from disk to cache = 33 229
Writes from cache to disk = 6
Fetches from cache = 35 196

Cucuruza · Сообщение **Cucuruza** » 06 сен 2006, 14:57

Забыл написать база после b/r

c) В таблице нет млн. записей со значением 0
Select * from ejou where ejstate=0

c1)

PLAN (EJOU INDEX (EJOU_IDX2))

------ Performance info ------
Prepare time = 0ms
Execute time = 0ms
Current memory = 50 677 856
Max memory = 100 338 912
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 207

c2)

PLAN (EJOU NATURAL)

------ Performance info ------
Prepare time = 0ms
Execute time = 3m 18s 282ms
Current memory = 50 660 336
Max memory = 100 338 912
Memory buffers = 2 048
Reads from disk to cache = 651 367
Writes from cache to disk = 6
Fetches from cache = 102 197 671

Cucuruza · Сообщение **Cucuruza** » 06 сен 2006, 14:59

Извеняюсь

То есть в таблице вообще нет записей со значением 0.

WildSery · Сообщение **WildSery** » 06 сен 2006, 15:19

Обрати внимание ещё раз на мои слова

Не селект быстр, а выдача первых данных.

Ни о чём не говорит?
Даю наводку - выполни те же запросы и посмотри на время выполнения, но только с выбором не SELECT * FROM, а SELECT COUNT(*) FROM, может, озарит понимание.

Очень похоже на то, что ты заинтересовался теоретическим вопросом, "почему так получается", а не потому, что тебе такой запрос действительно нужен. Всё ж ответь - для чего нужно получать запросом 30 млн. записей?

Merlin · Сообщение **Merlin** » 06 сен 2006, 15:24

Кста, сервак у него какой-то интересной версии. Два раза подряд один и тот же запрос выполняет, и один раз по индексу, другой натуралом...

WildSery · Сообщение **WildSery** » 06 сен 2006, 15:28

Merlin писал(а):Кста, сервак у него какой-то интересной версии. Два раза подряд один и тот же запрос выполняет, и один раз по индексу, другой натуралом...

Не понял твоей реплики. Я почему-то думал, что автор включает/выключает индекс для следующей статистики, разве не похоже?

Merlin · Сообщение **Merlin** » 06 сен 2006, 15:42

WildSery писал(а):Не понял твоей реплики. Я почему-то думал, что автор включает/выключает индекс для следующей статистики, разве не похоже?

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

CyberMax · Сообщение **CyberMax** » 06 сен 2006, 16:28

Ничего удивительного здесь нет.
http://forum.ibase.ru/phpBB2/viewtopic. ... c&start=20
Найди пост kdv, который начинается словами "ну, тут мораль такая". Заодно всю темку прочитай - будет интересно.

Cucuruza · Сообщение **Cucuruza** » 06 сен 2006, 16:38

Выполняюся запросы с указанием плана.
Возвращать большое количество не требуется, нужна первая тысяча и по where ее не ограничить.
Select count выполняться будет очень долго.

SELECT count(*) FROM EJOU where ejstate = 0 PLAN (EJOU NATURAL)
вернул 0

План
PLAN (EJOU NATURAL)

------ Performance info ------
Prepare time = 0ms
Execute time = 3m 7s 141ms
Avg fetch time = 187 141,00 ms
Current memory = 50 668 484
Max memory = 100 338 912
Memory buffers = 2 048
Reads from disk to cache = 651 367
Writes from cache to disk = 6
Fetches from cache = 102 197 672

SELECT count(*) FROM EJOU where ejstate = 0

План
PLAN (EJOU INDEX (EJOU_IDX2))

------ Performance info ------
Prepare time = 0ms
Execute time = 0ms
Avg fetch time = 0,00 ms
Current memory = 50 668 568
Max memory = 100 338 912
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 207

SELECT count(*) FROM EJOU where ejstate = 2
вернул 1

План
PLAN (EJOU INDEX (EJOU_IDX2))

------ Performance info ------
Prepare time = 0ms
Execute time = 0ms
Avg fetch time = 0,00 ms
Current memory = 50 668 788
Max memory = 100 338 912
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 210

SELECT count(*) FROM EJOU where ejstate = 2 PLAN (EJOU NATURAL)
План
PLAN (EJOU NATURAL)

------ Performance info ------
Prepare time = 0ms
Execute time = 2m 58s 328ms
Avg fetch time = 178 328,00 ms
Current memory = 50 668 484
Max memory = 100 338 912
Memory buffers = 2 048
Reads from disk to cache = 651 367
Writes from cache to disk = 6
Fetches from cache = 102 197 672

Суть проблемы в том, нельзя предсказать как выполнять запрос, ведь даже используя например

Код: Выделить всё

CREATE PROCEDURE EJOU_EXISTS (
    IEJSTATE INTEGER)
RETURNS (
    RESULT INTEGER)
AS
BEGIN
  IF (EXISTS (SELECT * FROM EJOU EJ
              WHERE EJ.EJSTATE=:IEJSTATE)) THEN
    RESULT=1;
  ELSE
    RESULT=0;
  SUSPEND;
END

выполнение будет зависеть от того есть искомое значение в таблице или нет и в том и проблема если значение есть - выполнять быстрее натуралом, если значения нет - нужен индекс. Как узнать как выполнять запрос с индексом или без или как еще?

WildSery · Сообщение **WildSery** » 06 сен 2006, 16:54

Если ты прочитал приведённую ссылку, то уже должен понять следующее: если себя ограничить твоими условиями, то без разницы как ты сделаешь, с индексом или без, раз ты не можешь предсказать распределение значений ключевого поля.
Однако у меня складывается впечатление, что тут наличествует плохо спроектированная БД. Запросы типа "получить первую случайную тысячу записей" - это вообще LOL.

Merlin · Сообщение **Merlin** » 06 сен 2006, 17:03

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

EIJSTATE_HIST
Eijstate Int Not Primary Key
Count_In_EJOU Numeric (18,0) Default 0

и инкай-декай её на вставке-удалении триггерами на EJOU. Заглянувши в неё перед запросом, узнаешь, есть ли смысл использовать индекс. Он не только в случае отсутствия записей выигрыш даст.

CyberMax · Сообщение **CyberMax** » 06 сен 2006, 17:08

Ну, там у него не случайная первая тысяча, а просто тысяча. Хотя, конечно, сути это не меняет.

Код: Выделить всё

Как узнать как выполнять запрос с индексом или без или как еще?

Я же дал тебе ссылку. Вывод один: никак. Поэтому в твоем случае лучше с индексом. Либо подумать и перепроектировать базу. Если у тебя только одна запись с состоянием "2", то может лучше вообще сделать три таблички (по числу состояний)? Проблема отпадет сама собой.
Вообще же теоретически это можно решить введением типа "перечисление". Чтобы гистограммку для него хранить. Но его не будет, факт.

CyberMax · Сообщение **CyberMax** » 06 сен 2006, 17:10

Merlin писал(а):и инкай-декай её на вставке-удалении триггерами на EJOU. Заглянувши в неё перед запросом, узнаешь, есть ли смысл использовать индекс. Он не только в случае отсутствия записей выигрыш даст.

И что, перед запросом индекс активировать/деактивировать? Или план серверу передавать? Похоже на операцию на глазах через известное место...