Почему использование INT для выбора индекса Varchar, содержащего числа, намного медленнее, чем использование строк?

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

Хотя в этом столбце есть индекс, использование числовых строк для поиска строки НАМНОГО быстрее (0,01 секунды), чем использование целых чисел (0,54 секунды). Что является причиной этого? Кажется, он не может использовать значение для индекса...

Я что-то упускаю из виду? Похоже, он не использует Int для индекса? Должен ли я давать подсказки по использованию индекса, или для этого есть переключатель базы данных? Или, если я неправильно понимаю вывод объяснения, почему тогда он намного медленнее?

Схема таблицы, чтобы показать пример:

CREATE TABLE `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stuff` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stuff` (`stuff`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Здесь он использует строку для индекса:

explain select * from example where stuff='200';
----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | example | ref  | idx_stuff     | idx_stuff | 137     | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+

Здесь похоже, что Int не преобразуется в строку для поиска индекса:

explain select * from example where stuff=200;
+----+-------------+---------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | example | index | idx_stuff     | idx_stuff | 137     | NULL |    2 | Using where; Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+--------------------------+

mysql indexing innodb database-performance
person nico gawenda    schedule 23.04.2012    source источник
comment
поскольку ваш столбец хранится как varchar, поиск по строке должен быть быстрее.   -  person hjpotter92    schedule 23.04.2012
comment
но он говорит, что использование индекса и 200 можно было легко преобразовать в строку раньше.   -  person nico gawenda    schedule 23.04.2012

Ответы (2)


arrow_upward
12
arrow_downward

Как указано в руководстве:

Для сравнения строкового столбца с числом MySQL не может использовать индекс столбца для быстрого поиска значения. Если str_col является индексированным строковым столбцом, индекс нельзя использовать при выполнении поиска в следующем операторе:

SELECT * FROM tbl_name WHERE str_col=1;

Причина этого в том, что существует множество различных строк, которые могут преобразовываться в значение 1, например '1', ' 1' или '1a'.

При необходимости вы всегда можете CAST преобразовать целое число в строку в чтобы воспользоваться индексом:

SELECT * FROM example WHERE stuff = CAST(200 AS CHAR);
person eggyal    schedule 08.05.2012
comment
Примечание: использование CAST(200 AS CHAR) может не сработать, чтобы заставить MySQL использовать индекс. - person Kevin Borders; 24.11.2015
comment
@KevinBorders: А почему бы и нет? - person eggyal; 24.11.2015
comment
Оказывается, это может произойти, если наборы символов не совпадают. CONVERT(200 USING latin1) исправил это для меня. - person Kevin Borders; 24.11.2015
comment
@KevinBorders: Это интересно. MySQL должен перекодировать в соответствующий набор символов. Какая версия? - person eggyal; 24.11.2015
comment
Это произошло с 5.6.21 и character_set_database latin1, а другие наборы символов для клиента, соединения, результатов и системы установлены на utf8. - person Kevin Borders; 24.11.2015
comment
привет, ягьял, есть поговорка, что индекс, добавленный к str_col, не приведен (str_col), поэтому casted (ctr_col) не имеет индекса, из-за которого выбор не использует индекс. Это высказывание кажется разумным, но почему int_col с выбором строкового значения использует индекс, разве он также не имеет неявного преобразования из int в строку? - person touchstone; 30.07.2019
comment
@touchstone: Да, поиск строки по целочисленному индексу влечет за собой неявное приведение параметра поиска к целому числу, но это приведение однозначно, тогда как приведение в другую сторону — нет. - person eggyal; 30.07.2019

arrow_upward
6
arrow_downward

Предупреждение: MySQL также может пропустить индекс, если его набор символов не совпадает, даже если оба значения равны CHAR. Если следующий запрос не работает:

SELECT * FROM example WHERE stuff = CAST(200 AS CHAR);

Затем получите набор символов базы данных, запустив show variables like 'character_set_database';, и используйте его в операторе CONVERT следующим образом (в этом примере предполагается, что набор символов вашей базы данных равен latin1 — замените его на ваше значение character_set_database):

SELECT * FROM example WHERE stuff = CONVERT(200 USING latin1);
person Kevin Borders    schedule 23.11.2015
comment
Преобразование сработало для меня там, где CAST не сработал. Благодарю вас! - person accord_guy; 29.10.2018