Существует ли реальная разница в производительности между первичными ключами INT и VARCHAR?

существует ли измеримая разница в производительности между использованием INT и VARCHAR в качестве первичного ключа в MySQL? Я хотел бы использовать VARCHAR в качестве первичного ключа для списков ссылок (подумайте о штатах США, кодах стран), и сотрудник не сдвинется с места на INT AUTO_INCREMENT в качестве первичного ключа для всех таблиц.

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

Итак, есть ли у кого-нибудь опыт работы с этим конкретным прецедентом и связанными с ним проблемами производительности?

14 135
mysql performance innodb primary-key myisam

14 ответов:

вы делаете хороший момент, что вы можете избежать некоторого количества Объединенных запросов, используя то, что называется естественный ключ вместо суррогатный ключ. Только вы можете оценить, если преимущество этого является значительным в вашем приложении.

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

Не используйте ни одну из стратегий для всех таблиц в базе данных. Вполне вероятно, что в некоторых случаях естественный ключ лучше, но в других случаях суррогатный ключ лучше.

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

63

дело не в производительности. Речь идет о том, что делает хороший первичный ключ. Уникальный и неизменный с течением времени. Вы можете подумать, что такая сущность, как код страны, никогда не меняется с течением времени и будет хорошим кандидатом на первичный ключ. Но горький опыт - это то, что редко бывает так.

INT AUTO_INCREMENT соответствует условию "уникальный и неизменный с течением времени". Отсюда и предпочтение.

67

зависит от длины.. Если varchar будет содержать 20 символов, а int-4, то если вы используете int, ваш индекс будет иметь в пять раз больше узлов на странице индексного пространства на диске... Это означает, что для обхода индекса потребуется в пять раз больше физических и/или логических операций чтения..

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

в то же время, чтобы гарантировать согласованность данных, каждая таблица, где вопросы и имейте значимый нечисловой альтернативный ключ (или уникальный индекс), чтобы гарантировать, что повторяющиеся строки не могут быть вставлены (дублировать на основе значимых атрибутов таблицы) .

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

31

абсолютно нет.

Я сделал несколько... несколько... проверка производительности между INT, VARCHAR и CHAR.

10 миллионов таблиц записей с первичным ключом (уникальным и кластеризованным) имели одинаковую скорость и производительность (и стоимость поддерева) независимо от того, какой из трех я использовал.

Как говорится... используйте все, что лучше всего подходит для вашего приложения. Не беспокойтесь о производительности.

30

для коротких кодов, вероятно, нет никакой разницы. Это особенно верно, поскольку таблица, содержащая эти коды, вероятно, будет очень маленькой (не более пары тысяч строк) и не будет часто меняться (когда в последний раз мы добавили новое состояние США).

для больших таблиц с более широким изменением среди ключа, это может быть опасно. Подумайте об использовании адреса электронной почты / имени пользователя из пользовательской таблицы, например. Что происходит, когда у вас есть несколько миллионов пользователей, и некоторые из этих пользователей длинные имена или адреса электронной почты. Теперь каждый раз, когда вам нужно присоединиться к этой таблице, используя этот ключ, он становится намного дороже.

9

я был немного раздражен отсутствием тестов для этого онлайн, поэтому я сам провел тест.

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

настройка была следующей:

  • процессор Intel® Core™ i7-7500U @ 2.70 GHz × 4
  • 15.6 GiB RAM, из которых я обеспечил около 8 ГБ было бесплатно во время теста.
  • 148.6 ГБ SSD-накопитель, с большим количеством свободного пространства.
  • в Ubuntu 16.04 64-бит
  • 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 таблицы, I используются полные имена штатов США, без сокращения или расширения их до 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 запрос по каждой таблице:

  • jan_int: 21.30 sec
  • jan_int_index: 18.79 sec
  • jan_char: 21.70 sec
  • jan_char_index: 18.85 sec
  • jan_varchar: 21.76 sec
  • jan_varchar_index: 18.86 sec

что касается размеров таблиц и индексов, вот результат 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 | 
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|

мой вывод заключается в том, что нет производительности разница для этого конкретного случая использования.

9

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

для ссылки в качестве внешнего ключа использование автоинкрементного целого числа в качестве суррогата является хорошей идеей по двум основным причинам.
- Во-первых, в соединении обычно меньше накладных расходов.
- Во-вторых, если вам нужно обновить таблицу, содержащую уникальный 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

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

6

в HauteLook мы изменили многие из наших таблиц, чтобы использовать естественные ключи. Мы действительно испытали реальное увеличение производительности. Как вы упомянули, многие из наших запросов теперь используют меньше соединений, что делает запросы более производительными. Мы даже использовать составной первичный ключ, если это имеет смысл. При этом некоторые таблицы просто легче работать, если у них есть суррогатный ключ.

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

2

распространенные случаи, когда суррогат 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 нуждаются в дополнительном поиске; перемещая пару в PK, это избегается для одного направления.
  • вторичный индекс "покрывает", поэтому для этого не нужно экстра уважать.
  • эта таблица меньше из-за избавления от id и один индекс.

другом случае (страны):

country_id INT ...
-- versus
country_code CHAR(2) CHARACTER SET ascii

слишком часто новичок нормализует country_code в 4-байтовый INT вместо использования "естественной" 2-байтовой, почти неизменной 2-байтовой строки. Быстрее, меньше, меньше соединений, более читабельно.

2

я столкнулся с той же дилеммой. Я сделал 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%. Еще до низкого прироста производительности, но действительная попытка. Im работает в MySQL настройки и кластеризации.

1

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

0

вопрос о MySQL, поэтому я говорю, что есть существенная разница. Если бы речь шла о Oracle (который хранит числа как строку - да, я сначала не мог в это поверить), то не так много разницы.

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

дело в том, что процессор имеет дело с 4 байта и 8 байтовых целых чисел естественно, в кремний. Это очень быстро для него, чтобы сравнить два целых числа-это происходит в один или два такта.

теперь посмотрите на строку-она состоит из большого количества символов (более одного байта на символ в эти дни). Сравнение двух строк для приоритета не может быть сделано за один или два цикла. Вместо этого символы строк должны повторяться до тех пор, пока не будет найдено различие. Я уверен, что есть приемы, чтобы сделать его быстрее в некоторых базах данных, но это не имеет значения здесь, потому что сравнение int выполняется естественно и молниеносно в кремнии процессором.

мое общее правило-каждый первичный ключ должен быть автоинкрементным INT, особенно в приложениях OO, использующих ORM (Hibernate, Datanucleus, что угодно), где есть много отношений между объектами - они обычно всегда будут реализованы как простой FK, и способность БД быстро разрешать их важна для вашего приложения' s отзывчивость.

0

Как обычно, нет общих ответов. - Это зависит от обстоятельств!- и я вовсе не шучу. Мое понимание исходного вопроса было для ключей на небольших таблицах, таких как страна (целочисленный идентификатор или код char/varchar), являющийся внешним ключом к потенциально огромной таблице, такой как таблица адресов/контактов.

здесь есть два сценария, когда вы хотите, чтобы данные из БД. Во-первых, это список / поисковый запрос, в котором вы хотите перечислить все контакты с кодами или именами Штатов и стран (идентификаторы будут не поможет и, следовательно, потребуется поиск). Другой сценарий get на первичном ключе, который показывает одну запись контакта, где должно быть показано имя государства, страны.

для последнего get, вероятно, не имеет значения, на чем основан FK, поскольку мы объединяем таблицы для одной записи или нескольких записей и для чтения ключей. На первый сценарий (поиск или список) может повлиять наш выбор. Так как требуется показать страну (хотя бы узнаваемый код и возможно, даже сам поиск включает в себя код страны), не нужно присоединяться к другой таблице через суррогатный ключ может потенциально (я просто осторожен здесь, потому что я на самом деле не проверял это, но кажется весьма вероятным) повысить производительность; несмотря на то, что это, безусловно, помогает в поиске.

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

другой сценарий, где ключи зависят от более длинных значений varchar и, возможно, от больших таблиц; суррогатный ключ, вероятно, имеет преимущество.

0

позвольте мне сказать, что да, безусловно, есть разница, принимая во внимание объем производительности (из определения коробки):

1 - использование суррогатного int быстрее в приложении, потому что вам не нужно использовать ToUpper(), ToLower(), ToUpperInvarient () или ToLowerInvarient() в вашем коде или в вашем запросе, и эти 4 функции имеют разные показатели производительности. См. раздел Правила производительности Майкрософт по этому вопросу. (выполнение приложения)

2 - через суррогат int гарантирует, что ключ не будет меняться с течением времени. Даже коды стран могут меняться, см. Wikipedia как коды ISO менялись с течением времени. Это займет много времени, чтобы изменить первичный ключ для поддеревьев. (выполнение обслуживания данных)

3-Похоже, что есть проблемы с решениями ORM, такими как NHibernate, когда PK/FK не является int. (производительность разработчика)

0