Блог

Главная - Вопросы по программированию - Выбор из таблицы с двумя отдельными индексами не использует ключ в зависимости от значения в предложении where

Выбор из таблицы с двумя отдельными индексами не использует ключ в зависимости от значения в предложении where

#mysql #explain

Вопрос:

Я настраиваю индексы нашей базы данных и обнаружил некоторое странное поведение в Mysql 5.7.32. Вот сценарий для репликации проблемы.

У меня есть таблица employees с тремя столбцами id , firstname и lastname . В таблице есть два индекса для каждого из столбцов varchar. Для одного из приведенных ниже операторов SELECT вывод неожиданно не использует ключ.

Почему один из этих запросов не использует индекс? Это потому Miller , что это первое значение в таблице? Или это неточность ОБЪЯСНЕНИЯ?

 DROP TABLE if EXISTS `employee`;
CREATE TABLE `employee` (
    `id` INT(11) NOT NULL auto_increment,
    `firstname` VARCHAR(50) NOT NULL,
    `lastname` VARCHAR(50) NOT NULL, 
    PRIMARY KEY (`id`), 
    INDEX `index_firstname` (`firstname`),
    INDEX `index_lastname` (`lastname`)
);

INSERT INTO `employee` (firstname,lastname) VALUES('alice','Miller');
INSERT INTO `employee` (firstname,lastname) VALUES('bob','Miller');
INSERT INTO `employee` (firstname,lastname) VALUES('charlie','Miller');
INSERT INTO `employee` (firstname,lastname) VALUES('doyle','Miller');
INSERT INTO `employee` (firstname,lastname) VALUES('evan','Smith');
INSERT INTO `employee` (firstname,lastname) VALUES('franz','Smith');
INSERT INTO `employee` (firstname,lastname) VALUES('gloria','Smith');
INSERT INTO `employee` (firstname,lastname) VALUES('helga','Unique');

EXPLAIN SELECT * FROM employee WHERE firstname='alice';   # uses the key 'index_firstname'
EXPLAIN SELECT * FROM employee WHERE lastname='Smith';    # uses the key 'index_lastname'
EXPLAIN SELECT * FROM employee WHERE lastname='Unique';   # uses the key 'index_lastname'

EXPLAIN SELECT * FROM employee WHERE lastname='Miller';   # does not use the key 'index_lastname'

Ответ №1:

Если выборка значений индекса имеет отношение ~25% (не точное, см. Ниже) к выборке данного значения, индекс не используется.

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

Комментарии:

1. Ага, это все объясняет. Я мог бы убедиться, что запрос начнет использовать индекс, добавив еще несколько строк с другим lastname . Также: при выборе только lastname столбца вместо * , он будет использовать индекс, даже если соотношение выше. Спасибо!

Метки: