Есть ли РЕАЛЬНАЯ разница в производительности между первичными ключами INT и VARCHAR?
Есть ли ощутимая разница в производительности между использованием INT и VARCHAR в качестве первичного ключа в MySQL? Я хотел бы использовать VARCHAR в качестве первичного ключа для списков ссылок (например, штаты США, коды стран), и коллега не будет указывать INT AUTO_INCREMENT в качестве первичного ключа для всех таблиц.
Мой аргумент, как подробно описано здесь, заключается в том, что разница в производительности между INT и VARCHAR незначительна, поскольку для каждой ссылки на внешний ключ INT требуется JOIN, чтобы понять смысл ссылки, ключ VARCHAR будет непосредственно представлять информацию.
Итак, есть ли у кого-нибудь опыт использования этого конкретного варианта использования и связанные с ним проблемы производительности?
14 ответов
Вы делаете хорошее замечание, что можете избежать некоторого количества соединенных запросов, используя так называемый естественный ключ вместо суррогатного ключа. Только вы можете оценить, насколько это полезно в вашей заявке.
То есть вы можете измерить запросы в вашем приложении, которые наиболее важны для быстрой работы, поскольку они работают с большими объемами данных или выполняются очень часто. Если эти запросы выигрывают от устранения объединения и не страдают от использования первичного ключа varchar, сделайте это.
Не используйте ни одну стратегию для всех таблиц в вашей базе данных. Вполне вероятно, что в некоторых случаях естественный ключ лучше, но в других случаях суррогатный ключ лучше.
Другие люди отмечают, что на практике редко встречаются естественные ключи, которые никогда не меняются и не имеют дубликатов, поэтому суррогатные ключи обычно стоят того.
Я был немного раздражен отсутствием тестов для этого онлайн, поэтому я провел тест самостоятельно.
Обратите внимание, что я не делаю это на обычной основе, поэтому, пожалуйста, проверьте мои настройки и шаги на предмет любых факторов, которые могли непреднамеренно повлиять на результаты, и оставьте свои проблемы в комментариях.
Настройка была следующей:
- Процессор Intel® Core™ i7-7500U @ 2,70 ГГц × 4
- 15.6 ГБ ОЗУ, из которых я гарантировал, что около 8 ГБ было свободно во время теста.
- SSD-накопитель на 148,6 ГБ, с большим количеством свободного места.
- Ubuntu 16.04 64-bit
- MySQL Ver 14.14 Distrib 5.7.20, для Linux (x86_64)
Столы:
create table jan_int (data1 varchar(255), data2 int(10), myindex tinyint(4)) ENGINE=InnoDB;
create table jan_int_index (data1 varchar(255), data2 int(10), myindex tinyint(4), INDEX (myindex)) ENGINE=InnoDB;
create table jan_char (data1 varchar(255), data2 int(10), myindex char(6)) ENGINE=InnoDB;
create table jan_char_index (data1 varchar(255), data2 int(10), myindex char(6), INDEX (myindex)) ENGINE=InnoDB;
create table jan_varchar (data1 varchar(255), data2 int(10), myindex varchar(63)) ENGINE=InnoDB;
create table jan_varchar_index (data1 varchar(255), data2 int(10), myindex varchar(63), INDEX (myindex)) ENGINE=InnoDB;
Затем я заполнил 10 миллионов строк в каждой таблице PHP-скриптом, суть которого такова:
$pdo = get_pdo();
$keys = [ 'alabam', 'massac', 'newyor', 'newham', 'delawa', 'califo', 'nevada', 'texas_', 'florid', 'ohio__' ];
for ($k = 0; $k < 10; $k++) {
for ($j = 0; $j < 1000; $j++) {
$val = '';
for ($i = 0; $i < 1000; $i++) {
$val .= '("' . generate_random_string() . '", ' . rand (0, 10000) . ', "' . ($keys[rand(0, 9)]) . '"),';
}
$val = rtrim($val, ',');
$pdo->query('INSERT INTO jan_char VALUES ' . $val);
}
echo "\n" . ($k + 1) . ' millon(s) rows inserted.';
}
За int столы, бит ($keys[rand(0, 9)]) был заменен просто rand(0, 9), и для varchar В таблицах я использовал полные названия штатов США, не сокращая и не расширяя их до 6 символов. generate_random_string() генерирует случайную строку из 10 символов
Затем я побежал в MySQL:
SET SESSION query_cache_type=0;- За
jan_intТаблица:SELECT count(*) FROM jan_int WHERE myindex = 5;SELECT BENCHMARK(1000000000, (SELECT count(*) FROM jan_int WHERE myindex = 5));
- Для других таблиц, таких же как выше, с
myindex = 'califo'заcharстолы иmyindex = 'california'заvarcharстолы.
Времена BENCHMARK запрос по каждой таблице:
- январь: 21.30 сек
- jan_int_index: 18,79 с
- январь: 21,70 сек
- jan_char_index: 18,85 с
- январь: 21,76 сек
- jan_varchar_index: 18,86 с
Что касается размеров таблиц и индексов, вот вывод show table status from janperformancetest; (без нескольких столбцов не показано):
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Collation |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| jan_int | InnoDB | 10 | Dynamic | 9739094 | 43 | 422510592 | 0 | 0 | 4194304 | NULL | utf8mb4_unicode_520_ci |
| jan_int_index | InnoDB | 10 | Dynamic | 9740329 | 43 | 420413440 | 0 | 132857856 | 7340032 | NULL | utf8mb4_unicode_520_ci |
| jan_char | InnoDB | 10 | Dynamic | 9726613 | 51 | 500170752 | 0 | 0 | 5242880 | NULL | utf8mb4_unicode_520_ci |
| jan_char_index | InnoDB | 10 | Dynamic | 9719059 | 52 | 513802240 | 0 | 202342400 | 5242880 | NULL | utf8mb4_unicode_520_ci |
| jan_varchar | InnoDB | 10 | Dynamic | 9722049 | 53 | 521142272 | 0 | 0 | 7340032 | NULL | utf8mb4_unicode_520_ci |
| jan_varchar_index | InnoDB | 10 | Dynamic | 9738381 | 49 | 486539264 | 0 | 202375168 | 7340032 | NULL | utf8mb4_unicode_520_ci |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Я пришел к выводу, что для этого конкретного варианта использования нет разницы в производительности.
Это не о производительности. Речь идет о том, что делает хороший первичный ключ. Уникальный и неизменный со временем. Вы можете подумать, что такой объект, как код страны, никогда не меняется со временем и будет хорошим кандидатом на первичный ключ. Но горький опыт - это редко.
INT AUTO_INCREMENT соответствует условию "уникальность и неизменность во времени". Отсюда и предпочтение.
Зависит от длины. Если varchar будет 20 символов, а int равно 4, то, если вы используете int, ваш индекс будет иметь ПЯТЬ раз больше страниц на страницу индексного пространства на диске... Это означает, что обход Индекс потребует одну пятую от физического и / или логического чтения.
Таким образом, если производительность является проблемой, если у вас есть такая возможность, всегда используйте встроенный не значащий ключ (называемый суррогатом) для ваших таблиц и для внешних ключей, которые ссылаются на строки в этих таблицах...
В то же время, чтобы гарантировать согласованность данных, каждая таблица, где это имеет значение, также должна иметь значимый нечисловой альтернативный ключ (или уникальный индекс), чтобы гарантировать невозможность вставки дублирующихся строк (дублирование на основе значимых атрибутов таблицы) .
Для конкретного использования, о котором вы говорите (например, поиск состояний), это на самом деле не имеет значения, потому что размер таблицы очень мал. В общем, на показатели с таблицами с менее чем несколькими тысячами строк не влияют показатели производительности...
Точно нет.
Я сделал несколько... несколько... проверок производительности между INT, VARCHAR и CHAR.
Таблица с 10 миллионами записей с ПЕРВИЧНЫМ КЛЮЧОМ (уникальным и кластерным) имела одинаковую скорость и производительность (и стоимость поддерева) независимо от того, какой из трех я использовал.
При этом... используйте все, что лучше для вашего приложения. Не беспокойтесь о производительности.
Для коротких кодов, вероятно, нет никакой разницы. Это особенно верно, поскольку таблица, содержащая эти коды, вероятно, будет очень маленькой (максимум несколько тысяч строк) и не будет часто меняться (когда мы в последний раз добавляли новый штат США).
Для больших таблиц с более широким разбросом среди ключей это может быть опасно. Например, подумайте об использовании адреса электронной почты / имени пользователя из таблицы "Пользователь". Что происходит, когда у вас несколько миллионов пользователей, и у некоторых из них есть длинные имена или адреса электронной почты. Теперь, когда вам нужно присоединиться к этой таблице с помощью этого ключа, она становится намного дороже.
Что касается первичного ключа, то, что физически делает строку уникальной, должно быть определено как первичный ключ.
Для ссылки в качестве внешнего ключа использование автоматически увеличивающегося целого числа в качестве суррогата является хорошей идеей по двум основным причинам.
- Во-первых, в соединении обычно меньше накладных расходов.
- Во-вторых, если вам нужно обновить таблицу, содержащую уникальный varchar, то обновление должно каскадно перейти ко всем дочерним таблицам и обновить все их, а также индексы, тогда как с помощью суррогата int ему нужно только обновить мастер таблица и ее индексы.
Недостатком использования суррогата является то, что вы можете разрешить изменение значения суррогата:
ex.
id value
1 A
2 B
3 C
Update 3 to D
id value
1 A
2 B
3 D
Update 2 to C
id value
1 A
2 C
3 D
Update 3 to B
id value
1 A
2 C
3 B
Все зависит от того, что вам действительно нужно беспокоиться в вашей структуре и что значит больше всего.
Распространенные случаи, когда суррогат AUTO_INCREMENT болит:
Распространенный шаблон схемы - это отображение "многие ко многим":
CREATE TABLE map (
id ... AUTO_INCREMENT,
foo_id ...,
bar_id ...,
PRIMARY KEY(id),
UNIQUE(foo_id, bar_id),
INDEX(bar_id) );
Производительность этого шаблона намного лучше, особенно при использовании InnoDB:
CREATE TABLE map (
# No surrogate
foo_id ...,
bar_id ...,
PRIMARY KEY(foo_id, bar_id),
INDEX (bar_id, foo_id) );
Зачем?
- Вторичные ключи InnoDB нуждаются в дополнительном поиске; переместив пару в ПК, этого можно избежать в одном направлении.
- Вторичный индекс "покрытие", поэтому он не нуждается в дополнительном поиске.
- Эта таблица меньше из-за избавления от
idи один индекс.
Другой случай (страна):
country_id INT ...
-- versus
country_code CHAR(2) CHARACTER SET ascii
Слишком часто новичок нормализует country_code в 4 байта INT вместо использования "натуральной" 2-байтовой, почти неизменной 2-байтовой строки. Быстрее, меньше, меньше СОЕДИНЕНИЙ, более читабельно.
В HauteLook мы изменили многие из наших таблиц, чтобы использовать естественные ключи. Мы испытали реальное увеличение производительности. Как вы упоминаете, многие из наших запросов теперь используют меньше объединений, что делает запросы более производительными. Мы даже будем использовать составной первичный ключ, если это имеет смысл. Тем не менее, с некоторыми таблицами легче работать, если у них есть суррогатный ключ.
Кроме того, если вы позволяете людям создавать интерфейсы для вашей базы данных, может пригодиться суррогатный ключ. Третья сторона может рассчитывать на то, что суррогатный ключ изменится только в очень редких случаях.
Я столкнулся с той же дилеммой. Я сделал DW (схему Созвездия) с 3 таблицами фактов: Дорожно-транспортные происшествия, Транспортные средства при несчастных случаях и Несчастные случаи при несчастных случаях. Данные включают все несчастные случаи, зарегистрированные в Великобритании с 1979 по 2012 год, и 60 таблиц измерений. Всего около 20 миллионов записей.
Связи таблиц фактов:
+----------+ +---------+
| Accident |>--------<| Vehicle |
+-----v----+ 1 * +----v----+
1| |1
| +----------+ |
+---<| Casualty |>---+
* +----------+ *
RDMS: MySQL 5.6
По сути, индекс несчастных случаев - это varchar(цифры и буквы) с 15 цифрами. Я старался не иметь суррогатных ключей, как только индексы аварий никогда не изменятся. На компьютере i7(8 ядер) DW стал слишком медленным для запроса после 12 миллионов записей загрузки в зависимости от размеров. После долгих переделок и добавления суррогатных ключей bigint я получил увеличение скорости в среднем на 20%. Тем не менее, к низкому приросту производительности, но допустимо попробовать. Я работаю в настройке MySQL и кластеризации.
Вопрос о MySQL, поэтому я говорю, что есть существенная разница. Если бы речь шла об Oracle (который хранит числа в виде строки - да, я сначала не мог в это поверить), тогда особой разницы нет.
Хранение в таблице не проблема, но обновление и ссылка на индекс. Запросы, включающие поиск записи на основе ее первичного ключа, являются частыми - вы хотите, чтобы они происходили как можно быстрее, потому что они происходят так часто.
Дело в том, что ЦП имеет дело с 4-байтовыми и 8-байтовыми целыми числами, естественно, в кремнии. Это действительно быстро для сравнения двух целых чисел - это происходит за один или два такта.
Теперь посмотрите на строку - она состоит из множества символов (более одного байта на символ в наши дни). Сравнение двух строк по приоритету не может быть сделано за один или два цикла. Вместо этого символы строк должны повторяться до тех пор, пока не будет найдена разница. Я уверен, что в некоторых базах данных есть уловки, позволяющие сделать это быстрее, но это не имеет значения, потому что ЦПУ выполняет сравнение int естественно и молниеносно в кремнии.
Мое общее правило - каждый первичный ключ должен быть автоинкрементным INT, особенно в OO-приложениях, использующих ORM (Hibernate, Datanucleus и т. Д.), Где есть много связей между объектами - они обычно всегда будут реализованы как простой FK и возможность для БД для их быстрого решения важен для отзывчивости вашего приложения.
Позвольте мне сказать "да", безусловно, есть разница, учитывая объем производительности (определение "из коробки"):
1- Использование суррогата int быстрее в приложении, поскольку вам не нужно использовать ToUpper(), ToLower(), ToUpperInvarient() или ToLowerInvarient() в вашем коде или в вашем запросе, и эти 4 функции имеют разные показатели производительности. См. Правила производительности Microsoft по этому вопросу. (выполнение приложения)
2 - Использование суррогата int гарантирует, что ключ не изменится с течением времени. Даже коды стран могут измениться, см. Википедию, как коды ISO менялись со временем. Это займет много времени, чтобы изменить первичный ключ для поддеревьев. (производительность обслуживания данных)
3. Кажется, есть проблемы с решениями ORM, такими как NHibernate, когда PK/FK не является int. (производительность разработчика)
Не уверен в том, что это повлияет на производительность, но кажется, что возможный компромисс, по крайней мере во время разработки, заключался бы в том, чтобы включить как автоинкрементный, целочисленный "суррогатный" ключ, так и предполагаемый, уникальный, "естественный" ключ. Это даст вам возможность оценить производительность, а также другие возможные проблемы, в том числе изменчивость естественных ключей.
Как обычно, нет общих ответов. 'Это зависит!' и я не шучу Мое понимание исходного вопроса заключалось в том, что ключи в небольших таблицах - например, страна (целочисленный идентификатор или код char/varchar) - являются внешним ключом для потенциально огромной таблицы, такой как таблица адресов / контактов.
Здесь есть два сценария, когда вы хотите вернуть данные из БД. Во-первых, это запрос типа "список / поиск", в котором вы хотите перечислить все контакты с кодами или именами штатов и стран (идентификаторы не помогут, и, следовательно, потребуется поиск). Другой сценарий получения по первичному ключу, который показывает одну запись контакта, в которой должно быть указано название штата, страны.
Что касается последнего, то, вероятно, не имеет значения, на чем основан FK, поскольку мы объединяем таблицы для одной записи или нескольких записей и для чтения ключей. Первый сценарий (поиск или список) может зависеть от нашего выбора. Поскольку требуется указывать страну (по крайней мере, узнаваемый код и, возможно, даже сам поиск включает код страны), отсутствие необходимости присоединяться к другой таблице через суррогатный ключ может потенциально (я просто осторожен, потому что на самом деле я не проверял это, но кажется весьма вероятным) улучшить производительность; несмотря на то, что это, безусловно, помогает с поиском.
Поскольку коды имеют небольшой размер - обычно не более 3 символов для страны и штата, в этом сценарии можно использовать естественные ключи в качестве внешних ключей.
Другой сценарий, где ключи зависят от более длинных значений varchar и, возможно, от больших таблиц; суррогатный ключ, вероятно, имеет преимущество.