Как узнать, стоит ли оптимизировать MySQL запросы?

    Мне часто задают один и тот же вопрос: как можно находить запросы, которые необходимо оптимизировать. Ведь, скажем, взглянув на отчет pt-query-digest, мы легко найдем медленные запросы или запросы, которые вызывают большую нагрузку на систему — но как мы поймем, существует или нет возможность сделать выполнение этого запроса быстрее? Полный ответ на этот вопрос определенно потребует комплексного анализа, так как существует много путей оптимизации запросов. Однако, существует одна очень полезная метрика, которую вы можете применить — соотношение между количество возвращенных запросом рядов и пройденными рядами.

    Допустим, у нас есть такой пример:

    # Time: 120911 17:09:44
    # User@Host: root[root] @ localhost []
    # Thread_id: 64914  Schema: sbtest  Last_errno: 0  Killed: 0
    # Query_time: 9.031233  Lock_time: 0.000086  Rows_sent: 0  Rows_examined: 10000000  Rows_affected: 0  Rows_read: 0
    # Bytes_sent: 213  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
    # InnoDB_trx_id: 12F03
    use sbtest;
    SET timestamp=1347397784;
    select * from sbtest where pad='abc';
    

    Запрос в данном случае вернул 0 рядов (так как у нас нет совпадений), но для этого ему пришлось пройтись по 10 миллионам рядов. Какой сценарий был бы предпочтительнее? Если бы запрос прошелся по тому же числу рядов, которые в итоге он вернет. В таком случае, если я расставлю индексы в таблице, то я получу следующую запись в slow query log, куда падают все медленные запросы:

    # Time: 120911 17:18:05
    # User@Host: root[root] @ localhost []
    # Thread_id: 65005  Schema: sbtest  Last_errno: 0  Killed: 0
    # Query_time: 0.000323  Lock_time: 0.000095  Rows_sent: 0  Rows_examined: 0  Rows_affected: 0  Rows_read: 0
    # Bytes_sent: 213  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
    # InnoDB_trx_id: 12F14
    SET timestamp=1347398285;
    select * from sbtest where pad='abc';
    

    Значение Rows_examined=0, совпадающее с Rows_sent означает, что запрос достаточно хорошо оптимизирован. Заметьте, если вы подумали, что в этом случае обращения к БД не происходит совсем — вы ошибаетесь. Проход по индексам выполняется, но, так как считаются только строки, которые были найдены и возвращены на верх для обработки MySQL-частью, то значение Rows_examined остается равным нулю.
    Казалось бы, все очень просто, но это слишком поспешный вывод. Подобная математика сработает только с запросами без агрегирующих функций/group by, плюс только для запросов, которые проходят по ровно одной таблице. А как быть с запросами, которые затрагивают более одной таблицы?

    # Time: 120911 17:25:22
    # User@Host: root[root] @ localhost []
    # Thread_id: 65098  Schema: sbtest  Last_errno: 0  Killed: 0
    # Query_time: 0.000234  Lock_time: 0.000063  Rows_sent: 1  Rows_examined: 1  Rows_affected: 0  Rows_read: 1
    # Bytes_sent: 719  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
    # InnoDB_trx_id: 12F1D
    SET timestamp=1347398722;
    select * from sbtest a,sbtest b where a.id=5 and b.id=a.k;
    
    mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k;
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
    | id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
    |  1 | SIMPLE      | a     | const | PRIMARY,k     | PRIMARY | 4       | const |    1 |       |
    |  1 | SIMPLE      | b     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
    2 rows in set (0.00 sec)
    

    В этом случае мы на самом деле делаем объединение двух таблиц, но из-за того, что тип доступа к таблицам задан как «константа», MySQL не считает из за доступ к двум таблицам. В случае с «реальным» доступом, вывод будет таким:

    # Time: 120911 17:28:12
    # User@Host: root[root] @ localhost []
    # Thread_id: 65099  Schema: sbtest  Last_errno: 0  Killed: 0
    # Query_time: 0.000273  Lock_time: 0.000052  Rows_sent: 1  Rows_examined: 2  Rows_affected: 0  Rows_read: 1
    # Bytes_sent: 719  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
    # InnoDB_trx_id: 12F23
    SET timestamp=1347398892;
    select * from sbtest a,sbtest b where a.k=2 and b.id=a.id;
    
    +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+
    | id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra |
    +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+
    |  1 | SIMPLE      | a     | ref    | PRIMARY,k     | k       | 4       | const       |    1 |       |
    |  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | sbtest.a.id |    1 |       |
    +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------+
    2 rows in set (0.00 sec)
    

    В этом случае мы имеем 2 проанализированные строки для каждого множества строк, чего и следовало ожидать, потому что у нас есть 2 (логических) таблицы используемых в этом запросе. Это правило также не будет работать в том случае, если у вас есть в запросе group:

    # Time: 120911 17:31:48
    # User@Host: root[root] @ localhost []
    # Thread_id: 65144  Schema: sbtest  Last_errno: 0  Killed: 0
    # Query_time: 5.391612  Lock_time: 0.000121  Rows_sent: 2  Rows_examined: 10000000  Rows_affected: 0  Rows_read: 2
    # Bytes_sent: 75  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
    # InnoDB_trx_id: 12F24
    SET timestamp=1347399108;
    select count(*) from sbtest group by k;
    

    Этот запрос вернет только 2 строки, хотя пройдет по 10 миллионам, и мы не можем в действительности оптимизировать этот запрос по-простому, так как проход по всем строкам действительно необходим для группировки результатов.
    В таком случае вы можете задуматься насчет удаления из запроса group by и агрегирующих функций. Тогда запрос превратится в “select * from sbtest” , который вернет все 10 миллионов строк и, следовательно, здесь не будет простых способов оптимизации.
    Этот метод создан не для того, чтобы дать вам ясный ответ «да или нет», но он может порядком помочь, какой оптимизации вы можете достигнуть в итоге. Допустим, у меня есть запрос, который использует индекс по 1000 строк и возвращает 10… У меня все еще может быть возможность уменьшить число строк, по которым он проходит, в 100 раз, — при помощи, например, добавления комбинированных индексов.

    Итак, кратко — как можно быстро узнать, стоит ли оптимизировать запрос или нет?
    — посмотрите, сколько строк возвращает запрос после удаления group by, distinct и агрегирующих функций (A)
    — возьмите число пройденных строк, деленное на число таблиц в объединении (B)
    — если B меньше или равно A, ваш запрос «идеален»
    — если B/A равен 10 или больше. то этот запрос один из самых достойных кандидатов на оптимизацию.

    Это простой метод и его можно смело использовать вместе с pt-query-digest, так так последний сообщает не только средние значения, но и краевые.

    Оригинал статьи: здесь.

    Similar posts

    Ads
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More

    Comments 22

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

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

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

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

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

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

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

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

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

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

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

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

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

                      Так ведь в том и дело, что поиск одной записи не может выполнятся дольше чем подсчет количества. Может, согласен, столько же. Но дольше — нет. А может намного меньше. Или я не прав?
                        0
                        Но дольше — нет. А может намного меньше. Или я не прав?
                        Я бы согласился с тем, что тут вы абсолютно правы, если бы зафорсив range scan в тесткейсе (третий случай), я не получил бы втрое больше чтений :D
                        Для меня чесгря это было неожиданностью, которую я пока не могу объяснить.
              0
              Маленький тесткейс для оракла.
              Запрос к пустой таблице с 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;                     
              

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

                  Просто для того, чтобы не возникало сомнений, что для оракла 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). Наверное потому что действительно вычитывалось значение.
                    0
                    Погодите, в этом примере вы оставили where rownum = 1. Мне интересен был вариант именно без него.

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

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

                      0
                      Сейчас мусор подчищается при первом же 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
                      

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

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

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

                  Only users with full accounts can post comments. Log in, please.