Почему добавление индекса ничего не изменило в утверждении объяснения здесь?

Question

Вот скрипт для создания таблицы:

CREATE DATABASE IF NOT EXISTS sailors;
USE sailors;
DROP TABLE IF EXISTS reserves;
DROP TABLE IF EXISTS sailors;
DROP TABLE IF EXISTS boats;

CREATE TABLE sailors
(
sid    INTEGER NOT NULL ,
sname  VARCHAR(20) NOT NULL ,
rating INTEGER NULL ,
age    DECIMAL(5,2) NULL, 
CONSTRAINT sailors_pk PRIMARY KEY (sid)
);

CREATE TABLE boats
(
bid    INTEGER NOT NULL ,
bname  VARCHAR(20) NOT NULL ,
color  VARCHAR(10) NOT NULL ,
CONSTRAINT boats_pk PRIMARY KEY (bid)
);

CREATE TABLE reserves
(
sid    INTEGER NOT NULL ,
bid    INTEGER NOT NULL ,
day    DATE NOT NULL ,
CONSTRAINT reserves_pk PRIMARY KEY (sid,bid,day)
, foreign key (sid) references sailors (sid)
, foreign key (bid) references boats  (bid)
);

INSERT INTO sailors VALUES(22, 'Dustin', 7, 45.0);
INSERT INTO sailors VALUES(29, 'Brutus', 1, 33.0);
INSERT INTO sailors VALUES(31, 'Lubber', 8, 55.5);
INSERT INTO sailors VALUES(32, 'Audy',   8, 25.5);
INSERT INTO sailors VALUES(58, 'Rusty', 10, 35.5);
INSERT INTO sailors VALUES(64, 'Horatio',7, 35.0);
INSERT INTO sailors VALUES(71, 'Zorba', 10, 16.0);
INSERT INTO sailors VALUES(74, 'Horatio',9, 35.0);
INSERT INTO sailors VALUES(85, 'Art',    3, 25.5);
INSERT INTO sailors VALUES(95, 'Bob',    3, 63.5);

INSERT INTO boats VALUES(101, 'Interlake', 'blue');
INSERT INTO boats VALUES(102, 'Interlake', 'red');
INSERT INTO boats VALUES(103, 'Clipper',   'green');
INSERT INTO boats VALUES(104, 'Marine',    'red');
INSERT INTO boats VALUES(105, 'Clipper',   'white');

INSERT INTO reserves VALUES(22, 101, DATE '2012-10-10');
INSERT INTO reserves VALUES(22, 102, DATE '2012-10-10');
INSERT INTO reserves VALUES(22, 103, DATE '2012-08-10');
INSERT INTO reserves VALUES(22, 104, DATE '2012-07-10');
INSERT INTO reserves VALUES(31, 102, DATE '2012-10-11');
INSERT INTO reserves VALUES(31, 103, DATE '2012-06-11');
INSERT INTO reserves VALUES(31, 104, DATE '2012-12-11');
INSERT INTO reserves VALUES(64, 101, DATE '2012-05-09');
INSERT INTO reserves VALUES(64, 102, DATE '2012-08-09');
INSERT INTO reserves VALUES(74, 103, DATE '2012-08-09');

Вот сценарий с EXPLAIN заявление и результат этого.

EXPLAIN SELECT * FROM sailors ORDER BY rating;

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, sailors, , ALL, , , , , 10, 100.00, Using filesort

Теперь я добавляю индексы и ожидаю EXPLAIN результат выписки для последующего изменения:

CREATE INDEX rating ON sailors(rating);
CREATE INDEX age ON sailors(age);

Теперь я бегу EXPLAIN Скажите и посмотрите, что я ничего не изменил

EXPLAIN SELECT * FROM sailors ORDER BY rating;

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, sailors, , ALL, , , , , 10, 100.00, Using filesort

Почему ничего не изменилось в EXPLAIN заявление? Я ожидал улучшения производительности.

Answer 1

Использование вторичного индекса (например, INDEX(rating)) включает в себя отскок между индексом BTree и данными BTree. Было определено, что это медленнее, чем просто читать все данные, а затем сортировать их. Отсечка варьируется, но составляет где-то около 20% таблицы - то есть, если у вас был WHERE пункт, который отфильтровывается до менее чем 20% данных (и оба WHERE и ORDER BY можно обрабатывать вместе), тогда оптимизатор, вероятно, будет использовать индекс.

Термин "сортировка файлов" является неправильным. Это не обязательно включает "файл" - это часто делается в оперативной памяти.