Практически ли хранить столбцы строк в индексах?

Предположим, что у нас есть эта структура/данные примера:

@Посмотрите на http://sqlfiddle.com/#!8/1f85e/1

-- SET GLOBAL innodb_file_per_table=1;
DROP TABLE IF EXISTS mysql_index_reading_myisam;
CREATE TABLE IF NOT EXISTS mysql_index_reading_myisam (
    id INT NOT NULL AUTO_INCREMENT
  , str VARCHAR(50) NOT NULL
  , enm ENUM('thatis', 'thequestion') NOT NULL
  , cnt TINYINT NOT NULL

  , PRIMARY KEY (id)
  , INDEX str_cnt (str, cnt)
  , INDEX enm_cnt (enm, cnt)

) ENGINE=MyISAM CHARSET=Latin1;
INSERT INTO mysql_index_reading_myisam (str, enm, cnt) VALUES
    ('Tobeornottobe', 'Thatis', 1)
  , ('toBeornottobe', 'thatIs', 2)
  , ('tobeOrnottobe', 'ThatIs', 3)
  , ('tobeorNottobe', 'thatis', 4)
  , ('tobeornotTobe', 'THATIS', 5)
;
DROP TABLE IF EXISTS mysql_index_reading_innodb;
CREATE TABLE mysql_index_reading_innodb LIKE mysql_index_reading_myisam;
ALTER TABLE mysql_index_reading_innodb ENGINE InnoDB;
INSERT INTO mysql_index_reading_innodb SELECT * FROM mysql_index_reading_myisam;

EXPLAIN SELECT cnt FROM mysql_index_reading_myisam WHERE str = 'tobeornottobe';
EXPLAIN SELECT cnt FROM mysql_index_reading_innodb WHERE str = 'tobeornottobe';
EXPLAIN SELECT cnt FROM mysql_index_reading_myisam WHERE enm = 'thatis';
EXPLAIN SELECT cnt FROM mysql_index_reading_innodb WHERE enm = 'thatis';

Проверьте, как он хранится внутри

# egrep --ignore-case --only-matching --text '(tobeornottobe|thatis)' *
mysql_index_reading_innodb.frm:thatis
mysql_index_reading_innodb.ibd:Tobeornottobe
mysql_index_reading_innodb.ibd:toBeornottobe
mysql_index_reading_innodb.ibd:tobeOrnottobe
mysql_index_reading_innodb.ibd:tobeorNottobe
mysql_index_reading_innodb.ibd:tobeornotTobe
mysql_index_reading_innodb.ibd:Tobeornottobe
mysql_index_reading_innodb.ibd:toBeornottobe
mysql_index_reading_innodb.ibd:tobeOrnottobe
mysql_index_reading_innodb.ibd:tobeorNottobe
mysql_index_reading_innodb.ibd:tobeornotTobe
mysql_index_reading_myisam.frm:thatis
mysql_index_reading_myisam.MYD:Tobeornottobe
mysql_index_reading_myisam.MYD:toBeornottobe
mysql_index_reading_myisam.MYD:tobeOrnottobe
mysql_index_reading_myisam.MYD:tobeorNottobe
mysql_index_reading_myisam.MYD:tobeornotTobe
mysql_index_reading_myisam.MYI:Tobeornottobe
mysql_index_reading_myisam.MYI:toBeornottobe

В обоих версиях перечисления ресурсов хранятся в *.frm, как и должно быть. Хорошо.
В обоих файлах данных, хранящихся в файлах данных и данных/индексах. Хорошо.
В индексе MyISAM есть две записи.
В индексе InnoDB есть все пять записей в правильном случае.

Что я уже нашел

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

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

SELECT key_part3 FROM tbl_name WHERE key_part1 = 1

http://www.mysqlperformanceblog.com/2009/09/12/3-ways-mysql-uses-indexes/

Использование индекса для чтения данных Некоторые механизмы хранения (MyISAM и Innodb включено) также может использовать индекс для чтения данных, поэтому избегать чтения сами данные строки. Это не просто экономия от того, индекс вместо одного, но он может сэкономить IO порядков в в некоторых случаях - индексы сортируются (по крайней мере, на границе страницы), поэтому делая сканирование диапазона индексов, вы обычно получаете много записей индекса из одна и та же страница, но сами строки могут быть разбросаны по многим страницам требующих потенциально большого количества МО. Кроме того, если вам просто нужно доступ к паре индексов столбцов может быть намного меньше, чем данные, которые являются одной из причин, по которым индексы помогают ускорить запросов, даже если данные хранятся в памяти. Если MySQL только считывает индекс и не обращаясь к строкам, вы увидите "используя индекс" в выводе EXPLAIN.

Затем в источниках sql_select.cc: http://bazaar.launchpad.net/~mysql/mysql-server/5.1/view/head:/sql/sql_select.cc#L12834

/*
  We can remove binary fields and numerical fields except float,
  as float comparison isn't 100 % secure
  We have to keep normal strings to be able to check for end spaces
*/
if (field->binary() &&
    field->real_type() != MYSQL_TYPE_STRING &&
    field->real_type() != MYSQL_TYPE_VARCHAR &&
    (field->type() != MYSQL_TYPE_FLOAT || field->decimals() == 0))
{
  return !store_val_in_field(field, right_item, CHECK_FIELD_WARN);
}

Итак, мои вопросы

Можно ли хранить в столбцах строки индексов, которые нужны только как данные? Например, таблица с 20 столбцами, и нам часто нужен strcolumn, который выполняется в intcolumn. Хорошо ли создавать индекс, например (intcolumn, strcolumn), или мы действительно нуждаемся (intcolumn) здесь?
Использует ли mysql в движке innodb некоторые дополнительные действия для извлечение данных (когда мы видим "Использование где, используя индекс" )?
То же самое происходит и для ENUM. Это происходит, потому что Enum_field`s real_type возвращает MYSQL_TYPE_STRING. Одинаково ли это для перечислений?
Можно ли предположить, что перечисления - это супер зло, и мы всегда должны вместо этого используйте только простую ссылочную таблицу?
Для MyISAM он является обязательным, поскольку он хранит в индексе не все значения. Но тогда почему он хранит два значения - не один?
Если это все действительно происходит - это просто текущие ограничения ядро mysql, которое не зависит от реализации конкретного обработчика?

ps: Я вижу, что этот вопрос - нечто огромное. Если кто-то поможет переформулировать/сломать - это будет хорошо.

Update1: добавление другого SQL о "Использование индекса" vs "Использование индекса, использование которого"

@Посмотрим на http://sqlfiddle.com/#!8/3f287/2

DROP TABLE IF EXISTS tab;
CREATE TABLE IF NOT EXISTS tab (
    id INT NOT NULL AUTO_INCREMENT
  , num1 TINYINT NOT NULL
  , num2 TINYINT
  , str3 CHAR(1) NOT NULL

  , PRIMARY KEY (id)
  , INDEX num1_num2 (num1, num2)
  , INDEX num1_str3 (num1, str3)
  , INDEX num2_num1 (num2, num1)
  , INDEX str3_num1 (str3, num1)

) ENGINE=InnoDB;
INSERT INTO tab (num1, num2, str3) VALUES
    (1, 1, '1')
  , (2, 2, '2')
  , (3, 3, '3')
  , (4, 4, '4')
  , (5, 5, '5')
  , (6, 6, '6')
  , (7, 7, '7')
  , (8, 8, '8')
  , (9, 9, '9')
  , (0, 0, '0')
;
INSERT INTO tab (num1, num2, str3) SELECT num1, num2, str3 FROM tab;

-- Using index
EXPLAIN SELECT num2 FROM tab WHERE num1 =  5;
EXPLAIN SELECT str3 FROM tab WHERE num1 =  5;
-- Using where; Using index
EXPLAIN SELECT num1 FROM tab WHERE num2 =  5;
EXPLAIN SELECT num1 FROM tab WHERE str3 = '5';

Вопросы № 2

Почему в случае поиска не null int мы видим только "Использование индекса"?
Но в случае nullable int OR string - мы видим также "Использование где"?
Какие дополнительные действия выполняет mysql?

Ответ 1

Можно ли хранить в столбцах строки индексов, которые нужны только как данные? Например, таблица с 20 столбцами, и нам часто нужен strcolumn, который выполняется в intcolumn. Хорошо ли создавать индекс вроде (intcolumn, strcolumn) или мы действительно нуждаемся (intcolumn) здесь?

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

Как и во всем, его использование - это компромисс, который может быть уместным при некоторых обстоятельствах, но не в других.
Действительно ли mysql в движке innodb выполняет некоторые дополнительные действия для извлечения данных (когда мы видим "Using where; Using index" )?

В sqlfiddle, на который ссылаются ваши ссылки на вопрос, Using where; Using index для всех четырех запросов. Как описано в EXPLAIN Дополнительная информация:
В столбце Extra отчета EXPLAIN содержится дополнительная информация о том, как MySQL разрешает запрос. В следующем списке описаны значения, которые могут отображаться в этом столбце.
```
[ deletia ]
```
- Using index
  
  Информация о столбце извлекается из таблицы, используя только информацию в дереве индексов без необходимости выполнять дополнительную попытку прочитать фактическую строку. Эта стратегия может использоваться, когда запрос использует только столбцы, которые являются частью одного индекса.
  
  Если в столбце Extra также указано Using where, это означает, что индекс используется для выполнения поиска значений ключа. Без Using where оптимизатор может считывать индекс, чтобы избежать чтения строк данных, но не использовать его для поиска. Например, если индекс является индексом покрытия для запроса, оптимизатор может сканировать его, не используя его для поиска.
Поэтому все ваши запросы используют индексы покрытия для поиска и поиска данных, независимо от используемого механизма хранения.

Мне непонятно, что вы имеете в виду, когда говорите: "innodb engine действительно выполняет некоторые дополнительные действия для извлечения данных". Единственное отличие в выходе EXPLAIN, которое я вижу, это то, что запросы InnoDB показывают меньшее значение в столбце Rows; однако задокументировано:

Столбец Rows указывает количество строк, которые MySQL считает необходимым для выполнения запроса.

Для таблиц InnoDB это число является оценкой и может не всегда быть точным.
То же самое происходит и для ENUM. Это происходит потому, что real_type Enum_field возвращает MYSQL_TYPE_STRING. Он делает то же самое для перечислений?

Опять же, мне непонятно, что вы имеете в виду, когда говорите, что "такое же происходит". Однако, как обсуждалось выше, Using where; Using index просто указывает, что индекс покрытия использовался как для поиска, так и для извлечения данных.

Кроме того, поля ENUM имеют real_type of MYSQL_TYPE_ENUM, а не MYSQL_TYPE_STRING. См. sql/field.h:1873:
```
  enum_field_types real_type() const { return MYSQL_TYPE_ENUM; }
```
Можно ли тогда предположить, что перечисления являются супер злыми, и мы должны всегда использовать только простую таблицу ссылок?

Есть множество причин, чтобы избежать ENUM, но я не думаю, что ваш вопрос затронул любой из них.
Для MyISAM он является обязательным, поскольку он хранит в индексе не все значения. Но тогда почему он хранит два значения - не один?

Результаты egrep приводят вас к ложным выводам. Просто потому, что поиск по шаблону "tobeornottobe", не учитывающий регистр, обнаруживает, что две строки соответствия в файле .myi не означают, что индекс MyISAM имеет две записи. Структура данных - это дерево:
```
              /\
             /  \
Tobeornottobe    toBeornottobe
                   /\
                  /  \
     tobeOrnottobe    tobeorNottobe
                       \
                        \
                         tobeornotTobe
```
Один получает это от просмотра всех строк .myi индексного файла:
```
$ strings mysql_index_reading_myisam.MYI
Tobeornottobe
toBeornottobe
beOrnottobe
orNottobe
notTobe
```
Поэтому, если бы вы выполнили (нечувствительный к регистру) поиск шаблона "nottobe", вы бы нашли пять совпадений, а не два.

Подробнее о формате хранения структур индекса MyISAM вы можете узнать в .myi Файл.
Если это все действительно происходит - это просто текущие ограничения ядра mysql, которые не зависят от реализации конкретного обработчика?

Я боюсь, что я понятия не имею, что здесь задают.