Влияние COUNT() на основе столбца в таблице
Редактировать:
База данных - Oracle 11gR2 - сверх Exadata (X2)
Я составляю отчет о расследовании проблемы по прошлым проблемам, и я немного запутался с ситуацией ниже.
Скажи, у меня есть стол MYACCT, Есть 138 Столбцы. Держит 10 Million записей. С регулярными обновлениями не менее 1000 записей (вставки / обновления / удаления) в час.
Первичный ключ COL1 (VARCHAR2(18)) (Приложение редко использует это, за исключением объединения с другими таблицами)
Есть еще один уникальный индекс COL2 VARCHAR2(9)), Какой из них приложение регулярно использует. Все обновления, которые я имел в виду ранее, происходят на основе этих двух столбцов. В то время как любой SELECT только операция над этой таблицей, всегда обращайтесь COL2, Так COL2 будет наш интерес.
Мы делаем запрос ниже,
SELECT COUNT(COL2) FROM MYACCT; /* Use the Unique Column (Not PK) */
С результатом нет никаких проблем, тогда как я рекомендовал изменить его как
SELECT COUNT(COL1) FROM MYACCT; /* Use the primary Index
Я только что подсчитал время, необходимое для фактического исполнения
Запрос с использованием PRIMARY KEY был быстрее на `0,8-1,0 секунды всегда!
Теперь я пытаюсь объяснить это поведение. Просто составить план объяснения за этими запросами.
Запрос 1: (с использованием первичного ключа)
SELECT COUNT(COL1) FROM MYACCT;
План:
SQL> select * from TABLE(dbms_xplan.display);
Plan hash value: 2417095184
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11337 (1)| 00:02:17 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX STORAGE FAST FULL SCAN| PK_ACCT | 10M| 11337 (1)| 00:02:17 |
---------------------------------------------------------------------------------
9 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
41332 consistent gets
0 physical reads
0 redo size
210 bytes sent via SQL*Net to client
346 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Запрос 2:(НЕ используя первичный ключ)
SELECT COUNT(COL2) FROM MYACCT;
План:
Plan hash value: 1130703739
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 7868 (1)| 00:01:35 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | INDEX STORAGE FAST FULL SCAN| MYINDX01 | 10M| 95M| 7868 (1)| 00:01:35 |
------------------------------------------------------------------------------------------
9 rows selected.
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
28151 consistent gets
23 physical reads
784 redo size
233 bytes sent via SQL*Net to client
346 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Мы можем найти с точки зрения
Costа такжеTimeЗапрос без первичного ключа выигрывает. Тогда почему же время выполнения первичного ключа быстрее???
РЕДАКТИРОВАТЬ:
SQL> select segment_name, bytes from dba_segments where segment_name in ('MYINDX01','PK_ACCT');
PK_ACCT 343932928
MYINDX01 234881024
3 ответа
Вы читаете больше данных из индекса PK, чем из другого. COL1 является VARCHAR2(18) в то время как COL2 является VARCHAR(9), что не обязательно означает что-либо, но подразумевает, что у вас, вероятно, есть COL1 которые неизменно длиннее, чем в COL2, Поэтому они будут использовать больше памяти, как в таблице, так и в индексе, и сканирование индекса должно извлечь больше данных из буфера блока и / или диска для запроса на основе PK.
Статистика выполнения показывает, что; 41332 соответствует для запроса на основе PK и только 28151 для более быстрого, поэтому он выполняет больше работы с PK. И размеры сегментов показывают это тоже - для ПК вам нужно прочитать около 328 миллионов, для Великобритании - всего 224 миллиона.
Буфер блоков, вероятно, будет иметь решающее значение, если вы видите, что версия PK иногда запускается быстрее. В примере, который вы показали, оба запроса попадают в буфер блока - 23 физических чтения - это тривиальное число. Если данные индекса не были последовательно кэшированы, то вы можете увидеть 41k последовательных операций получения по сравнению с 28K физических операций чтения, что, вероятно, приведет к обратному очевидный победитель, поскольку физическое чтение с диска будет медленнее. Это часто проявляется, если выполнение двух запросов подряд показывает один быстрее, но в обратном порядке они показывают другой как более быстрый.
Вы не можете обобщить это так: "PK-запрос медленнее, чем британский запрос"; это из-за ваших конкретных данных. Вы, вероятно, также получите лучшую производительность, если ваш ПК будет на самом деле числовой столбец, а не VARCHAR2 номера столбца, что никогда не было хорошей идеей.
Учитывая заявление как
select count(x) from some_table
Если есть индекс покрытия для столбца xоптимизатор запросов, вероятно, будет его использовать, поэтому ему не нужно извлекать [ginormous] страницу данных.
Звучит как две колонки (col1 а также col2) оба ваших похожих запроса индексируются [1]. Чего не скажешь, так это того, сгруппированы ли эти индексы или нет.
Это может иметь большое значение. Если индекс кластеризован, конечный узел в B-дереве, который является индексом, является страницей данных таблицы. Учитывая, насколько велики ваши строки (или они кажутся), это означает, что сканирование кластеризованного индекса, скорее всего, будет перемещать намного больше данных - то есть больше подкачки страниц - чем это было бы при сканировании некластеризованного индекса.
Большинство агрегатных функций исключают нули при вычислении значения агрегатной функции. count() немного отличается count(*) включает в себя нули в результатах в то время как count(expression) исключает нули из результатов. Поскольку вы не используете distinctи предполагая, что ваш col1 а также col2 столбцы not nullвы можете получить лучшую производительность, пытаясь
select count(*) from myacct
или же
select count(1) from myacct
поэтому оптимизатору не нужно учитывать, является ли столбец нулевым.
Просто мысль.
[1] И я предполагаю, что они являются единственным столбцом в соответствующем индексе.
Ваш запрос PK выполняет 0 физических чтений, предполагая, что у вас есть результаты в памяти. Таким образом, хотя план выполнения выглядит медленнее, он работает быстрее. Запрос COL2 выполняет 23 физических чтения.