Статьи

Понимание максимального количества столбцов в таблице MySQL

Сентябрь 17, 2018

 Эта статья написана Стюартом Смитом в блоге MySQL Performance

Изначально этот пост представлял собой два набора опросов: «Каково максимальное количество столбцов в MySQL?» и «Каково минимальное максимальное количество столбцов в MySQL?». Прежде чем читать, обдумайте эти вопросы и придумайте свои собственные ответы … и посмотрите, правы вы или можете доказать, что я неправ!

Еще в 2009 году я закончил то, что казалось эпической задачей в базе кода Drizzle: удаление файла FRM. Зачем? Мы чувствовали, что нехорошо сохранять произвольные и неясные ограничения, существовавшие в 1980-х годах в 21-м веке, и вместо этого хотели создать модульную систему, в которой сами механизмы хранения имели свои собственные метаданные. Это был радикальный отход от философии MySQL, и она действительно окупилась в базе кода Drizzle. Однако … для тех, кто использует MySQL, Percona Server, MariaDB или любые другие ветви / вилки MySQL, вы получаете эти странные ограничения.

Почему я обсуждаю файл FRM? Если мы посмотрим на определение MAX_FIELDS в MySQL, то увидим, что оно определено как 4096. Это, однако, не является истинным пределом. Чтобы узнать, каков фактический лимит, мы должны углубиться в файл FRM.

Что такое файл FRM? Это файл FoRM от UNIREG. Это FRM, а не FORM, так как вы использовали только три символа после точки в имени файла. Еще в 1979 году Монти разработал собственную базу данных под названием UNIREG . UNIREG была текстовой программой для ввода записей в базу данных в стиле ISAM. Он будет иметь текстовый интерфейс 80 × 24 для ввода данных и отдельный интерфейс для создания отчетов. Это развилось в основанный на SQL MySQL, с MySQL 1.0, выпущенным в 1995.

Файл FoRM определяет, какие поля, на каком экране вводить, а также любые ограничения относительно того, что можно вводить. Например, если у вас было больше определенного числа полей, вам понадобится более одного экрана 80 × 24 для ввода всех данных! У вас также могут быть такие вещи, как поля NEXT_NUMBER (которые мы сегодня знаем как auto_increment), поля CASEUP и CASEDN, которые, хотя и не реализованы в MySQL, определения могут все еще находиться в источнике. По сути, именно поэтому у нас не может быть хороших вещей (таких как значения по умолчанию, отличные от NOW ()).

Он также имеет определенные ограничения, которые по любому современному стандарту являются чисто произвольными. Одним из них является ограничение, что определенная часть файла FRM не может быть больше, чем 64 КБ. Этот фрагмент кода, который влияет на максимальное количество столбцов, выглядит так:

/* Hack to avoid bugs with small static rows in MySQL */
reclength=max(file->min_record_length(table_options),reclength);
if (info_length+(ulong) create_fields.elements*FCOMP+288+
    n_length+int_length+com_length > 65535L || int_count > 255)
{
  my_message(ER_TOO_MANY_FIELDS, ER(ER_TOO_MANY_FIELDS), MYF(0));
  DBUG_RETURN(1);
}

Что, конечно, очевидно! Различные части этого ограничения:

  • info_length примерно 2 + strlen (field_name) для каждого поля. Если у вас нет много столбцов, а затем это что-то другое (так как в этом случае вы не получаете свой интерфейс терминала 80 × 24 в вашем файле FRM, вы вместо этого получаете несколько байтов на 19 столбцов).
  • create_fields.elements * FCOMP — это просто количество полей, умноженное на 17
  • 288 статичен и всегда нужен
  • int_length — длина интервала. Это не нормальное значение слова «интервал», мы можем только догадываться, что он назван так из-за чего-то специфического для UNIREG, или это были просто навыки английского языка Монти в 1980-х. Мы вернемся к этому.
  • com_length — длина всех комментариев для каждого поля (но не таблицы).

Интервал в UNIREG говорят — это набор строк, которые являются опциями для столбцов ENUM или SET. Хитрость заключается в том, что это уникальные интервалы, а не фактические интервалы, поэтому два столбца ENUM, имеющие параметры «Y» и «N», будут использовать меньше места в FRM, чем если бы у вас был один с «Y» и «N» и другой с ‘A’ и ‘B’.

Если вы заметили, что если у вас есть длинный комментарий к каждому полю, вы уменьшаете количество различных элементов ENUM, которые вы можете иметь, вы правы. Существует также ограничение в 255 уникальных интервалов, поэтому, хотя у вас может быть гораздо больше столбцов ENUM (‘Y’, ‘N’), у вас может быть только 255 столбцов ENUM с уникальными значениями.

Если вы искали очень простую формулу, которая в основном точна, я представляю это:

    foreach field:  17+2*(strlen(field_name)+2) (bytes)
    + length of all comments (in bytes)
    + length of all intervals (for ENUM, SET) in bytes.

Если вы используете это в качестве практического правила, когда оно не может превышать 64 КБ, вы примерно на правильном пути для определения максимального количества столбцов в таблице MySQL.

Итак, какое максимальное количество столбцов в таблице MySQL? Хорошо .. Я попробовал несколько вещей, прежде чем остановился на следующей (perl) программе (принимает параметр командной строки числа создаваемых столбцов), чтобы создать оператор CREATE TABLE sql:

sub cname ($) {
  my $c=shift;
  my $name="";
  while($c > 0)
  {
    my $n=$c%36;
    $name.=chr(ord('0')+$n) if $n < 10;     $name.=chr(ord('a')+($n-10)) if $n >= 10;
    $c= int $c/36;
  }
  return $name
}
my $sql= "CREATE TABLE t (";
 foreach(1..shift @ARGV) {
    my $n=cname($_);
    $sql.="`$n`";
    $sql.=" ENUM('Y','N','M','0','1','2')\n";
 }
 chop $sql;
 chop $sql;
 $sql.=");";
 print $sql;

Это дает вам инструкцию CREATE TABLE размером 46 КБ и FRM-файл размером 76 КБ для таблицы с 2829 столбцами. Я считаю, что это максимальное количество столбцов, которое вы можете создать.

Однако, если вы попытаетесь установить механизм хранения InnoDB, вы получите сообщение об ошибке! Точное сообщение об ошибке, которое вы получаете, не очень интересно и просто говорит: «Не могу создать таблицу« test.t »(errno: 139)». Это связано с тем, что InnoDB имеет жесткий предел в 1000 столбцов. Это код из ha_innodb.cc, который обеспечивает это ограничение:

if (form->s->fields > 1000) {
  /* The limit probably should be REC_MAX_N_FIELDS - 3 = 1020,
  but we play safe here */
 
  DBUG_RETURN(HA_ERR_TO_BIG_ROW);
}

Любопытно, что из MySQL 5.6 он заменен следующим набором определений.

#define	DATA_N_SYS_COLS 3 /* data0type.h */
/* from rem0types.h */
#define REC_MAX_N_FIELDS        (1024 - 1)
#define REC_MAX_N_USER_FIELDS	(REC_MAX_N_FIELDS - DATA_N_SYS_COLS * 2)

Что в сумме составляет (1024-1) -3 * 2 = 1017, что точно так же, как то, что я могу создать. Это верно, в MySQL 5.6 вы можете создать таблицу с несколькими столбцами в InnoDB!

Это привело меня к другой идее … какое минимальное максимальное количество столбцов вы можете создать? Вы можете подумать, что это 255 на основе ограничения количества интервалов, указанных выше, но можете ли вы получить еще меньше? Почему да, вы можете! С этим битом кода на Perl я смог получить ошибку слишком большого числа столбцов только с 192 столбцами (т.е. сработал 191):

 sub cname ($$) {
     my $c=shift;
     my $name="";
     while($c > 0)
     {
	 my $n=$c%36;
	 $name.=chr(ord('0')+$n) if $n < 10; 	 $name.=chr(ord('a')+($n-10)) if $n >= 10;
	 $c= int $c/36;
     }
     $name.='0' foreach(length $name .. shift);
     return $name
 }
 my $sql= "CREATE TABLE `".cname(16,63)."` (";
 foreach(1..shift @ARGV) {
     my $n=cname($_,63);
     $sql.="`$n`";
     $sql.=" ENUM('".cname(0,64)."') COMMENT '".cname($_,254)."',\n";
 }
 chop $sql;
 chop $sql;
 $sql.=");";
 print $sql;

Таким образом, максимальное количество столбцов для таблицы в MySQL находится где-то между 191 и 2829, в зависимости от ряда факторов. Мне было бы интересно услышать, если бы вы смогли побить мой минимум / максимум!

90
Share: