В нашем продукте мы имеем общую поисковую систему и пытаемся оптимизировать производительность поиска. Многие таблицы, используемые в запросах, допускают нулевые значения. Должны ли мы перепроектировать нашу таблицу, чтобы запретить нулевые значения для оптимизации или нет?
Наш продукт работает как на Oracle, так и на MS SQL Server.
В Oracle значения NULL не индексируются, i. е. этот запрос:
SELECT *
FROM table
WHERE column IS NULL
всегда будет использовать полное сканирование таблицы, так как индекс не охватывает нужные вам значения.
Более того, этот запрос:
SELECT column
FROM table
ORDER BY
column
также будет использовать полное сканирование таблицы и сортировку по той же причине.
Если ваши значения не позволяют внутренне разрешать NULL, тогда отметьте столбец как NOT NULL.
Дополнительный ответ, чтобы привлечь дополнительное внимание к комментарию Дэвида Олдриджа к ответу Кваснои.
Заявление:
этот запрос:
SELECT * FROM table WHERE column IS NULL
всегда будет использовать полное сканирование таблицы
неверно. Вот пример счетчика с использованием индекса с литеральным значением:
SQL> create table mytable (mycolumn)
2 as
3 select nullif(level,10000)
4 from dual
5 connect by level <= 10000
6 /
Table created.
SQL> create index i1 on mytable(mycolumn,1)
2 /
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'mytable',cascade=>true)
PL/SQL procedure successfully completed.
SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ *
2 from mytable
3 where mycolumn is null
4 /
MYCOLUMN
----------
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID daxdqjwaww1gr, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from mytable where mycolumn
is null
Plan hash value: 1816312439
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
|* 1 | INDEX RANGE SCAN| I1 | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MYCOLUMN" IS NULL)
19 rows selected.
Как вы можете видеть, используется индекс.
С уважением, Роб.
Короткий ответ: да, условно!
Основная проблема с нулевыми значениями и производительностью связана с поиском в прямом направлении.
Если вы вставляете строку в таблицу с нулевыми значениями, она помещается на естественную страницу, к которой она принадлежит. Любой запрос, ищущий эту запись, найдет ее в соответствующем месте. Легко до сих пор....
... но скажем, страница заполняется, и теперь эта строка обнимается среди других строк. Все еще хорошо...
... пока строка не будет обновлена, а нулевое значение теперь содержит что-то. Размер строки увеличился за пределы доступного для него пространства, поэтому движок базы данных должен что-то сделать.
Самая быстрая вещь для сервера - переместить строку с этой страницы в другую и заменить запись строки указателем вперед. К сожалению, это требует дополнительного поиска, когда выполняется запрос: один, чтобы найти естественное расположение строки, и один, чтобы найти его текущее местоположение.
Итак, короткий ответ на ваш вопрос - да, если эти поля не могут быть обнулены, это поможет выполнить поиск. Это особенно верно, если часто случается, что нулевые поля в записях, которые вы ищете, обновляются до ненулевых.
Конечно, есть и другие штрафы (особенно I/O, хотя и в глубину глубины индекса), связанные с более крупными наборами данных, а затем у вас есть проблемы приложений с запретом нулей в полях, которые их концептуально требуют, но эй, что другой проблема:)
Если ваш столбец не содержит NULL, лучше объявить этот столбец NOT NULL, оптимизатор может иметь более эффективный путь.
Однако, если у вас есть NULL в столбце, у вас нет большого выбора (ненулевое значение по умолчанию может создавать больше проблем, чем оно решает).
Как упоминается Quassnoi, NULL не индексируются в Oracle, или, если быть более точным, строка не будет индексироваться, если все индексированные столбцы имеют NULL, это означает:
Следующий script демонстрирует способ индексирования значений NULL:
CREATE TABLE TEST AS
SELECT CASE
WHEN MOD(ROWNUM, 100) != 0 THEN
object_id
ELSE
NULL
END object_id
FROM all_objects;
CREATE INDEX idx_null ON test(object_id, 1);
SET AUTOTRACE ON EXPLAIN
SELECT COUNT(*) FROM TEST WHERE object_id IS NULL;
Неверные поля могут иметь большое влияние на производительность при выполнении запросов "НЕ IN". Поскольку строки со всеми проиндексированными полями, установленными в null, не индексируются в индексах B-Tree, Oracle должен выполнить полное сканирование таблицы, чтобы проверить наличие нулевого значения, даже если индекс существует.
Например:
create table t1 as select rownum rn from all_objects;
create table t2 as select rownum rn from all_objects;
create unique index t1_idx on t1(rn);
create unique index t2_idx on t2(rn);
delete from t2 where rn = 3;
explain plan for
select *
from t1
where rn not in ( select rn
from t2 );
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50173 | 636K| 3162 (1)| 00:00:38 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 50205 | 637K| 24 (5)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 45404 | 576K| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Запрос должен проверять нулевые значения, поэтому он должен выполнить полное сканирование таблицы t2 для каждой строки в t1.
Теперь, если мы сделаем поля недействительными, он может использовать индекс.
alter table t1 modify rn not null;
alter table t2 modify rn not null;
explain plan for
select *
from t1
where rn not in ( select rn
from t2 );
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2412 | 62712 | 24 (9)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 2412 | 62712 | 24 (9)| 00:00:01 |
| 2 | INDEX FULL SCAN | T1_IDX | 50205 | 637K| 21 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| T2_IDX | 45498 | 577K| 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Я бы сказал, что тестирование требуется, но приятно знать опыт других людей. По моему опыту на сервере ms sql, значения NULL могут и могут вызвать серьезные проблемы с производительностью (различия). В очень простом тесте теперь я видел, как запрос возвращался через 45 секунд, когда не было установлено значение null в связанных полях в инструкции create table и более 25 минут, где она не была установлена (я отказался от ожидания и просто взял пик на расчетный план запроса).
Тестовые данные - 1 миллион строк x 20 столбцов, которые построены из 62 случайных строчных альфа-символов в стандартном ядре i5-3320 HD и 8GB RAM (SQL Server с использованием 2GB)/SQL Server 2012 Enterprise Edition на Windows 8.1. Важно использовать случайные данные/нерегулярные данные, чтобы сделать тестирование реалистичным "худшим" случаем. В обоих случаях таблица воссоздавалась и перезагружалась со случайными данными, которые занимали около 30 секунд в файлах базы данных, которые уже имели подходящее количество свободного места.
select count(field0) from myTable where field0
not in (select field1 from myTable) 1000000
CREATE TABLE [dbo].[myTable]([Field0] [nvarchar](64) , ...
vs
CREATE TABLE [dbo].[myTable]([Field0] [nvarchar](64) not null,
по соображениям производительности оба имели параметр таблицы data_compression = page set, а все остальное - по умолчанию. Нет индексов.
alter table myTable rebuild partition = all with (data_compression = page);
Отсутствие нулей является обязательным требованием для оптимизированных по памяти таблиц, для которых я специально не использую, однако сервер sql, очевидно, будет делать то, что быстрее, что в этом конкретном случае кажется массовым в пользу отсутствия нулей в данных и использования не null в таблице create.
Любые последующие запросы той же формы в этой таблице возвращаются через две секунды, поэтому я бы предположил, что стандартная статистика по умолчанию и, возможно, таблица (1.3 ГБ), помещенная в память, работают хорошо. то есть.
select count(field19) from myTable where field19
not in (select field18 from myTable) 1000000
В стороне, не имея нулей и не имеющих дело с нулевыми случаями, также делает запросы намного проще, короче, меньше подвержены ошибкам и очень обычно быстрее. Если это вообще возможно, лучше избегать нулей, как правило, на сервере ms sql, по крайней мере, если они явно не требуются и не могут быть разумно разработаны из решения.
Начиная с новой таблицы и оценивая это до 10 м строк /13 ГБ, такой же запрос занимает 12 минут, что очень респектабельно, учитывая аппаратное обеспечение и не используемые индексы. Для информационного запроса было полностью привязано IO с IO, зависающим от 20 МБ/с до 60 МБ/с. Повторение того же запроса заняло 9 минут.
Вопрос о том, следует ли использовать Nulls, потому что они влияют на производительность, является одним из тех балансирующих действий проектирования баз данных. Вы должны сбалансировать потребности бизнеса в производительности.
Нуль следует использовать, если они необходимы. Например, у вас может быть дата начала и дата окончания в таблице. Вы часто не знали дату окончания на момент создания записи. Поэтому вы должны разрешить nulls, влияют ли они на производительность или нет, поскольку данные просто не должны быть помещены. Однако, если данные должны в соответствии с бизнес-правилами присутствовать на момент создания записи, тогда вы не должны разрешать обнуляет. Это улучшило бы производительность, упростило бы кодирование и обеспечило бы сохранение целостности данных.
Если у вас есть существующие данные, которые вы хотели бы изменить, чтобы больше не допускать нули, вам необходимо рассмотреть влияние этих изменений. Во-первых, знаете ли вы, какое значение вам нужно внести в записи, которые в настоящее время являются нулевыми? Во-вторых, у вас есть много кода, который использует isnull или coalesce, которые вам нужно обновить (эти вещи замедляют работу, поэтому, если вам больше не нужно проверять их, вы должны изменить код)? Вам нужно значение по умолчанию? Вы действительно можете назначить его? Если нет, некоторые из кода вставки или обновления сломаются, если он не считает, что поле больше не может быть нулевым. Иногда люди вводят плохую информацию, чтобы позволить им избавиться от нулей. Итак, теперь поле цены должно содержать десятичные значения и такие вещи, как "неизвестный" и, следовательно, не может быть должным образом десятичным типом данных, а затем вам нужно идти на все виды длин, чтобы выполнять вычисления. Это часто создает проблемы с производительностью как плохие или худшие, чем созданный null. PLUS вам нужно пройти весь свой код и где бы вы ни ссылались, чтобы поданная была нулевой или не была нулевой, вам нужно переписать исключить или включить на основе возможных плохих значений, которые кто-то будет вставлять, поскольку данные не разрешены быть нулевым.
Я делаю много импорта данных из клиентских данных, и каждый раз, когда мы получаем файл, в котором какое-то поле, которое должно разрешать nulls, нет, мы получаем данные мусора, которые необходимо очистить, прежде чем импортировать в нашу систему. Email является одним из них. Часто данные вводятся, не зная этого значения, и обычно это какой-то тип строковых данных, поэтому пользователь может вводить что-либо здесь. Мы отправляемся на импорт электронных писем и находим вещи "Я не знаю". Трудно попытаться отправить электронное письмо на адрес "Я не знаю". Если система запрашивает действительный адрес электронной почты и проверяет что-то вроде существования знака @, мы получим "[email protected]". Как данные мусора, подобные этому, полезны для пользователей данных?
Некоторые проблемы производительности с нулями являются результатом написания несходных запросов. Иногда просто переставляя предложение where, а не устраняя нужный нуль, можно повысить производительность.
По моему опыту NULL является допустимым значением и обычно означает "не знаю". Если вы не знаете, то действительно бессмысленно составлять какое-то значение по умолчанию для столбца или пытаться принудительно установить ограничение NOT NULL. NULL просто является конкретным случаем.
Реальная проблема для NULL - это усложнение поиска. Например, вы не можете сказать WHERE column_name IN (NULL, 'value1', 'value2').
Лично, если вы найдете много своих столбцов или некоторые столбцы содержат много NULL, я думаю, вы можете вернуться к своей модели данных. Может быть, эти нулевые столбцы могут быть помещены в дочернюю таблицу? Например: таблица с телефонными номерами, где это имя, домашний телефон, сотовый телефон, факс, рабочий номер, номер аварийной ситуации и т.д. Вы можете заполнить только один или два из них, и это лучше нормализует его.
Вам нужно сделать шаг назад и посмотреть, как будут доступны данные. Это столбец, который должен иметь значение? Это столбец, который имеет значение только для определенных случаев? Это столбец, который будет запрашиваться много?