Почему поиск без индекса быстрее поиска с индексом?

Очень странная ситуация.
Я решил "поиграть" с базой данных и создал БД с 20 000 000 записей и провёл пару тестов. По идеи, запись с индексом должна быть дольше, а поиск элементов быстрее. Тесты были такие:
Вопросы тестирования

заполнение БД;
добавление 100 элементов;
получение 100 000 элемента;
получение 20 000 000 элемента;
получение элементов в котором встречается число 2555;

Сейчас я приведу пример своего кода на nest js, но он лишь обёртка, так что под ним я ещё и запишу команды ( SQL-запросы). Так же я в обоих случаях заполнял БД одинаково без nestjs ( смотрите в SQL-запросах). Тесты в обоих случаях одинаковые, сравниваю по индексу key ( само поле chatKey ).

Вот код на nestjs:
spoiler

модель

@Table({tableName: 'chats', createdAt: false, updatedAt: false})
export class MessageTable extends Model {

@Column({type: DataType.INTEGER, primaryKey: true, unique: true, autoIncrement: true})
id: number;

@Column({type: DataType.JSON})
members: number[];

@Index('key')
@Column({type: DataType.TEXT})
chatKey: string;

@Column({type: DataType.INTEGER})
author: number;

@Column({type: DataType.STRING(32)})
date: string;

@Column({type: DataType.STRING(255)})
data: string;

@Column({type: DataType.STRING(511)})
files: string;

}

тесты

addMessage = (number: number) => {
return new Promise( (res) => {
this.ChatRepository.create({
id: 20000000 + number,
author: number,
date: 'asd',
chatKey: (20000000 + number).toString(),
data: 'das',
files: '[]',
members: [1]
}).then( () => {return res('das')});
})
}

getMessageByKey = (number) => {
return new Promise( (res) => {
this.ChatRepository.findAll({where: {chatKey: number.toString()} } )
.then( (response) => res(response))
})
}

getMessagesByKey = (number) => {
return new Promise( (res) => {
this.ChatRepository.findAll({where: {
chatKey: {
[Op.like]: `%${number.toString()}%`
}
} } )
.then( (response) => res(response))
})
}
for (let indexx = 0; indexx < 100; indexx++) {
this.messageService.addMessage(indexx + 3)
.then( () => {if (indexx === 99) {
console.log('Ready by ' + (Date.now() - this.DateNow) );
} })
}
this.messageService.getMessageByKey(100000).then( (res) => {
console.log(res);
console.log('Ready by ' + (Date.now() - this.DateNow));
})

this.messageService.getMessageByKey(20000000).then( (res) => {
console.log(res);
console.log('Ready by ' + (Date.now() - this.DateNow));
})

this.messageService.getMessagesByKey(2555).then( () => {
console.log('Ready by ' + (Date.now() - this.DateNow));
})



SQL-запросы


INSERT INTO chats(author, "chatKey", "data", date, files, id, members)
SELECT 1, s.id '"asd"', '"asdasd"', '"[asdfasdf]"', s.id, '[12, 13]'
FROM generate_series(1, 20000000) as s(ID)
ORDER BY random()

Executing (default): INSERT INTO "chats" ("id","members","chatKey","author","date","data","files") VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING "id","members","chatKey","author","date","data","files";

Executing (default): SELECT "id", "members", "chatKey", "author", "date", "data", "files" FROM "chats" AS "MessageTable" WHERE "MessageTable"."chatKey" = '100000';

Executing (default): SELECT "id", "members", "chatKey", "author", "date", "data", "files" FROM "chats" AS "MessageTable" WHERE "MessageTable"."chatKey" = '20000000';

Executing (default): SELECT "id", "members", "chatKey", "author", "date", "data", "files" FROM "chats" AS "MessageTable" WHERE "MessageTable"."chatKey" LIKE '%2555%';



А теперь самое интересное. Я тестил в два дня, так как создание с индексами заняло больше времени, чем я думал, поэтому я в первом случае проводил 5 тестов и усреднял значения, в в тесте с индексами писал разброс.


creating without index - 11 min 18 sec
adds 100 elements without index - 7803
get 100 000 element without index - 942
get 20 000 000 element without index - 5478
get all element where key === 2555 without index - 1195 - 6141

creating - 2 hr 16 min.
adds 100 elements - 5206 - 58907
get 100 000 element - 3813 - 56000
get 20 000 000 element - 1922 - 7316
get all element where key === 2555 - 2847 - 32458



Почему-то в среднем поиск без индекса быстрее, чем с ним. У меня два вопроса. Первый, что я сделал не так? Второй, стоит ли в этом случае использовать индексы?
  • Вопрос задан
  • 375 просмотров
Решения вопроса 2
Fragster
@Fragster
помогло? отметь решением!
Сделайте explain analyze и посмотрите. Опционально запихайте на
https://explain.tensor.ru/

Скорее всего дело в том, что "MessageTable"."chatKey" LIKE '%2555%' в принципе не может использовать индексы (по своей природе), а операций чтения нужно намного больше. Ну и записи при вставке.

Кстати, иногда эффективнее удалить индексы, вставить данные, добавить индексы, но это не всегда возможно. Да и не всегда эффективнее кроме, разве что, случая обновления бОльшей части таблицы, нужно проверять.
Ответ написан
@Eugene_Evgeni Автор вопроса
Я не знаю, в чём была проблема, но она решилась пересозданием БД без индекса, а потом индексированием её. Спасибо, Антон Антон .
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы