Pull to refresh

Comments 22

В чём смысл статьи, что-то не пойму. Была лишь одна всколзь рекомендация использовать индекс там где он нужен. Остальные две страницы текста ни о чём.
С выводом в конце то понятно. Ладно, я туплю наверное. И комент удалить нельзя же.
Интересная статья. А в MSSQL и Oracle как такую информацию можно узнать — по скольким строкам запрос проходит?
В Postgres — легко (explain analyze). Но статья от этого лучше не становится — и так понятно, что чем меньше строк просматривает запрос, тем лучше, но рекомендации сводятся сплошь к исключениям из правил.
Помню в одном проекте часто возникала задача узнать, пустая ли та или иная таблица или запись. И практически везде это делалось так
SELECT COUNT(ID) FROM some_table WHERE some_condition
...
if(result > 0) then ...

Вроде бы верно. Считаем записи, если больше нуля — что-то делаем/не делаем. Но если вдуматься, зачем нам считать количество? Изменится ли наше решение если записей не 3 а 100 или over9000? Нет. Для большинства СУБД придется обойти всю выборку чтобы её сосчитать. А выборка может быть многомиллионнной! А ведь нам на самом деле нужно попытаться получить хотя бы одну запись, чтобы решить как действовать дальше.
Вероятно проект был заточен под MyISAM, который хранит информацию о количестве строк в таблице.

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

Да и сам поиск произвольной строки, в разных СУБД производится по разному. Для MySQL, PG, DB2 используется limit, для M$ — top для оракла — rownum… Для кажогй СУБД всяко придется лепить что-то свое.

В самделе если задуматься не так уж и абсурдно описанное вами решение, может быть даже вполне обосновано. Да, пеереехать даже на InnoDB уже не получится, но, быть может там специфика такая, что InnoDB стопудов не нужен будет никогда.
Проект был во-первых десктопный а во вторых на Firebird :)

Избавление от многочисленных ненужных запросов типа COUNT() в какой-то момент просто оживило продукт. Всё стало летать там где раньше тупило. А фишка в том, что FB не знает наперёд сколько записей попало в выборку. И если выборка написана правильно и цепляет индексы, то поиск первой попавшей записи выполняется почти мгновенно.
А в чем тут секрет FB?
Да, я понял фичу о которой Вы говорите. FB как и оракл, судя по всему, отдает фетч еще до того, как закончил скан/джойн.
Но тогда замена count(*) limit'ом(или как там в FB?) должна бы давать преимущество лишь в случае, когда результирующий набор не пуст, если он достаточно статичен, удаления производятся весьма редко, а если производится, место удаленных записей вскорости занимают новые. Если же результирующий набор пуст, все равно придется сканировать ВСЕ, чтобы убедиться в этом. Если данные разрежены, сканировать придется все равно достаточно много. А, я так полагаю, если есть проверка на наличие строк в таблице, ситуация, когда таблица не содержит данных — скорее закономерна нежели исключительна, и из таблицы записи определенно удаляются.(если эта проверка, конечно, не является проверкой на «всякий пожарный», с целью поднять ошибку, которая в промышленной эксплуатации не должна подниматься никогда)

за спойлером я провел тесткейс с первичным ключом. на оракле. Добавил миллион строк, удалил все, кроме одной, с наибольшим ID. Попробовал count, для которого использовался index full scan, where rownum для которого использовался он же и where rownum, для которого использовался range scan. По количеству согласованных чтений у count и where rownum при index full scat — сопостовими. Range scan втрое менее эффективен (при условии, что мы заведомо не знаем минимальный id записи в таблице)
Скрытый текст
SQL> set autotrace on
SQL> select count(*) from test;

  COUNT(*)
----------
         1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=586 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'SYS_C0051757' (INDEX (UNIQUE)
          ) (Cost=586 Card=1)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2102  consistent gets
          0  physical reads
          0  redo size
        350  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select 'hello' from test where rownum = 1;

'HELL
-----
hello


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=586 Card=1)
   1    0   COUNT (STOPKEY)
   2    1     INDEX (FAST FULL SCAN) OF 'SYS_C0051757' (INDEX (UNIQUE)
          ) (Cost=586 Card=1)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2101  consistent gets
          0  physical reads
          0  redo size
        352  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select 'hello' from test where id> 0 and rownum = 1;

'HELL
-----
hello


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13)
   1    0   COUNT (STOPKEY)
   2    1     INDEX (RANGE SCAN) OF 'SYS_C0051757' (INDEX (UNIQUE)) (C
          ost=2 Card=1 Bytes=13)





Statistics
----------------------------------------------------------
         58  recursive calls
          0  db block gets
       6456  consistent gets
          0  physical reads
          0  redo size
        352  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed



PS. Не подумайте, я Вас нисколь не троллю. Мне просто эта тема очень интересна, для меня она сейчас актуальна. Лишь этим объясняется моя дотошность в этом вопросе
>> Но тогда замена count(*) limit'ом(или как там в FB?)
В FB это выглядит так: SELECT FIRST n FROM… Очень печалит что в каждой СУБД эта конструкция по-своем описана.
Ну так вот. Можно даже не делать ограничение выборки (LIMIT, TOP, FIRST etc.) Просто становимся на первую полученную запись. Любая нормальная СУБД не будет фетчить всё подряд пока не попросишь.

Что касается сложных выборок с условием, тут все очень просто. Условие в WHERE как в варианте с COUNT() так и без него будет одинаковым, верно? Но. В варианте с COUNT СУБД придется считать сколько записей удовлетворяют условию (т.е. найти их все), а в варианте без подсчета — найти только первую удовлетворяющую условию. Чувствуете разницу? Надеюсь понятно объяснил.
Не могу удержаться от поправки, простите уж. Это не из вредности, просто в руках зудит поумничать

Можно даже не делать ограничение выборки (LIMIT, TOP, FIRST etc.) Просто становимся на первую полученную запись. Любая нормальная СУБД не будет фетчить всё подряд пока не попросишь.

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

Припоминаю тому хороший пример. До 10й версии оракла, он в PL/SQL не буферезировал сам результат курсора, полученный в цикле for, начиная с 10й версии, цикл for стал фетчить пачками по 100 записей. Тем, кто опирался на отсутствие буферизации в цикле ранее, с выходом новой версии, пришлось переписывать свои приложения.

ну и коли уж начал отвечать, отвечу и на другую реплику:

Чувствуете разницу? Надеюсь понятно объяснил.

Да, я разницу чувствую. И Вы объяснили все более чем понятно. Но Вы говорите о частном случае, когда эта одна единственная запись может быть найдена быстрее нежели выполнятся полный скан таблицы/индекса. Я же говорю о другом частном случае, другой крайности, когда поиск одной лишь записи требует сканирования того же объема информации, что и подсчет количества. Тут все зависит от того, как данные физически лягут. И тут уж выбор программиста, что ему более по душе — стабильное но c низкой производительностью решение или же не стабильно высокопрозводительное. Кто-то предпочтет стабильное, поставит его на мониторинг, и когда таблица разрастется до критических размеров, сделает ей шринк (или ребилд индекса) в штатном режиме, в рабочее время. Кто-то другой, может не побояться быть разубженным в три утра, чтобы сделать этот же самый шринк/ребилд, ибо вчера все что надо отработало за 10 минут, а сегодня данные физически легли чуть иначе, и процесс не смог выполниться за три часа. :D
>>Я же говорю о другом частном случае, другой крайности, когда поиск одной лишь записи требует сканирования того же объема информации, что и подсчет количества

Так ведь в том и дело, что поиск одной записи не может выполнятся дольше чем подсчет количества. Может, согласен, столько же. Но дольше — нет. А может намного меньше. Или я не прав?
Но дольше — нет. А может намного меньше. Или я не прав?
Я бы согласился с тем, что тут вы абсолютно правы, если бы зафорсив range scan в тесткейсе (третий случай), я не получил бы втрое больше чтений :D
Для меня чесгря это было неожиданностью, которую я пока не могу объяснить.
Маленький тесткейс для оракла.
Запрос к пустой таблице с count(*) vs where rownum = 1
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
Connected as ZTXN@ORCL01
 
SQL> set timing on
SQL> select count(*) from test;
 
  COUNT(*)
----------
         0
 
Executed in 14.437 seconds
SQL> select 'hello' from test where rownum = 1;
 
'HELLO'
-------
 
Executed in 15.812 seconds


Секрет, в общем, прост —
Скрытый текст
create table test as select  
                       lpad('1',100,'1') val1
                       ,lpad('2',100,'2') val2
                       ,lpad('3',100,'3') val3
                       ,lpad('4',100,'4') val4
                       ,lpad('5',100,'5') val5
                       ,lpad('6',100,'6') val6
                       ,lpad('7',100,'7') val7
                       ,lpad('8',100,'8') val8
                     from dual connect by level < 1e6;
delete from test;
commit;                     

А сделайте просто SELECT ID FROM Test и время фетча одной записи посмотрите
В этом тесткейсе не было ПК.
Поведение с отбором по деградировавшему ПК я показал под спойлером в коментарии, который оказался выше. Нагенерить столько данных чтобы в этом случае полное сканирование индекса превысило бы погрешность измерения оказалось проблематичным, я не влез в отведенные мне квоты. Потому вместо более очевидных для всех таймингов пришлось показать статистику, которая может показаться менее очевидной для человека из смежной области.
Суть того примера сводится к тому же, что и этого. Листья индекса для удаленных данных так же физически не освобождаются, как и данные таблицы, а потому для чтения одной выжившей записи из былого миллиона требуется почти то же сканирование(в этом случае уже индекса), что и для подсчета общего количества.

Просто для того, чтобы не возникало сомнений, что для оракла select id для того тесткейса равнозначно select 'hello', приведу план и статистику и для него:
Скрытый текст
SQL>  select id from test where rownum = 1;

        ID
----------
   1000000


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=586 Card=1 Bytes=1
          3)

   1    0   COUNT (STOPKEY)
   2    1     INDEX (FAST FULL SCAN) OF 'SYS_C0051757' (INDEX (UNIQUE)
          ) (Cost=586 Card=1 Bytes=13)





Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
       2261  consistent gets
          0  physical reads
          0  redo size
        344  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



Здесь у нас оказалось ненамного больше согласованных чтений (consistent gets). Наверное потому что действительно вычитывалось значение.
Погодите, в этом примере вы оставили where rownum = 1. Мне интересен был вариант именно без него.

Листья индекса для удаленных данных так же физически не освобождаются, как и данные таблицы

А вот это уже интереснее. По долгу службы я в основном работаю с FB и неплохо его знаю. Так вот там в ранних версиях стояла проблема накопления мусора в индексах и данных, но её решили несколько лет назад. Сейчас мусор подчищается при первом же SELECTе к данным почищенной таблицы (это можно настроить). Таким образом протормозить может только первый запрос после массового удаления, в результате которого произойдет автоматическая сборка мусора.

Сейчас мусор подчищается при первом же SELECTе к данным почищенной таблицы

Наверное в этом и есть секрет FB, о котором я вопрошал ))). О том, чтобы подобным образом поступал бы и оракл, я не в курсе. Спасибо большое за информацию, положу себе в копилочку.

Погодите, в этом примере вы оставили where rownum = 1. Мне интересен был вариант именно без него.

с учетом того, что в таблице оставлена одна лишь запись, отсутствие отсечки по rownum не должно ничего менять (в плане согласованных чтений).
Скрытый текст
SQL> select id from test;

        ID
----------
   1000000


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=586 Card=1 Bytes=1
          3)

   1    0   INDEX (FAST FULL SCAN) OF 'SYS_C0051758' (INDEX (UNIQUE))
          (Cost=586 Card=1 Bytes=13)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2102  consistent gets
          0  physical reads
          0  redo size
        344  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



Ну, и для полноты картины просто приведу статистику, какой она должна быть при условии, что читается действительно одна только запись
Скрытый текст
SQL> select * from (
  2    select /*+first_rows*/ id from test order by id desc
  3  )
  4  where rownum = 1;

        ID
----------
   1000000


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2146 Card=
          1 Bytes=13)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=2146 Card=1 Bytes=13)
   3    2       INDEX (FULL SCAN DESCENDING) OF 'SYS_C0051758' (INDEX
          (UNIQUE)) (Cost=2146 Card=1 Bytes=13)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        344  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Кстати вот, чтобы не быть голословным, провел тест на FB.

тест на FB
Таблица mo_chng содержит 138877 записей. Есть поле 'tablename', не индексированное

Тест №1
Select count(id) from mo_chng where tablename='SPRT'

PLAN (MO_CHNG NATURAL)

------ Performance info ------
Prepare time = 0ms
####Execute time = 109ms
Avg fetch time = 109,00 ms
####Fetches from cache = 280 646

Тест №2
select first 1 1 from mo_chng where tablename='SPRT'

Plan
PLAN (MO_CHNG NATURAL)

------ Performance info ------
Prepare time = 0ms
#####Execute time = 15ms
Avg fetch time = 15,00 ms
####Fetches from cache = 1 301

Как видно, разница во времени на порядок. Количество чтений — на два.

но что характерно, если условие не совпадает ни для одной записи — время выполнения запрос примерно одинаковое, что ожидаемо. И в том и другом случае происходит полный обход таблицы.


Да согласен, тест несколько синтетический, но в практике именно такая тупая задача часто и решалась. А как правило это вообще проверка пустая таблица или нет, без условий. Если пустая — то время одинаково. Если нет — то COUNT() всегда больше.

PS: В любом случае, большое спасибо за интересную дискуссию!
В данном примере запрос выполнится быстро, т.к. count считает записи по полю ID, которое, судя по названию, является первичным ключем. Если бы count, выполнялся по полю не являющемся индексом, то скорость запроса была бы гораздо медленнее.
Возможно вариант с выборкой одной записи действительно был бы быстрее чем подсчет всех записей таблицы, но тут надо уже делать замеры на конкретной базе — здесь много может быть параметров влияющих на скорость.
А вот и нет. Даже если поле — первичный ключ, т.е. индексировано по-определению, все равно движку потребуется обойти всю таблицу чтобы посчитать (кроме тех движков, которые запоминают) количество записей. Если же вы выполните SELECT ID FROM tablename движок встанет на первую запись, если она есть, почти мгновенно. Логический разультат один и тот же.
Извините что влезаю, но такой дилетантский вопрос
Не помогло бы лимитировать выборку?
что то на подобии
SELECT * FROM some_table limit 1 if(проверяем есть ли запись)
О том, собственно, и идет речь.

По моим разумениям Лимит против count (не для MyISAM) даст:
— Огромный прирост производительности для случая, когда таблица заполнена данными и данные не разрежены
— Не прогнозируемый, но все же скорее прирост для случая, когда таблица заполнена данными и данные разрежены.
— Никакого прироста для случая, когда таблица данными не заполнена.

Я предполагаю что для системы которая принимает решения на основе факта наличия/отсутствия записей в таблице, случай, когда таблица не содержит записей или же данные в таблице разрежены — характерен.
Only those users with full accounts are able to leave comments. Log in, please.