О чем эта статья
Данная статья не будет академическим изложением проблем индексирования в база данных, а будет затрагивать один маленький практический аспект использования индексов для выборки данных. Если отвечать на вопрос заголовка - о деталях и внимательном чтении документации, но я не такой педант (во всяком случае не всегда), мне значительно более интересно экспериментально посмотреть суть проблемы.
Окружение и приложение
Есть Django Web-приложение на python 2.7 (не спрашивайте почему не 3.х). Web-приложение состоит из нескольких типов Worker классов для взаимодействия как с внешними сервисами по HTTP, так и с локальным аналогом данных сервисов, использующих для хранения СУБД MySQL. Мы имеем следующие объекты в модели данных: языковая пара и общая таблица для отображения одного текста в другой:
class LocalTranslationMatrixLanguagesPair(models.Model):
@classmethod
def create(cls, language1, language2):
return cls(language1=language1, language2=language2)
language1 = models.CharField(u'language1', max_length=4, null=False)
language2 = models.CharField(u'language2', max_length=4, null=False)
class Meta:
unique_together = (u'language1', u'language2')
class LocalTranslationMatrix(models.Model):
@classmethod
def create(cls, dictionary, languages, text1, text2, is_main):
return cls(dictionary=dictionary, languages=languages, text1=text1, text2=text2, is_main=is_main)
dictionary = models.ForeignKey(Dictionary, related_name=u'local_matrix_dictionary', null=False)
languages = models.ForeignKey(LocalTranslationMatrixLanguagesPair, related_name=u'local_matrix_language_pair')
text1 = models.CharField(u'text1', max_length=512, null=False, db_index=True)
text2 = models.CharField(u'text2', max_length=512, null=False, db_index=True)
is_main = models.BooleanField(u'is_main')
Самое главное здесь наличие индексов по полям text1 и text2. В MySQL в таблице, на которую отображается LocalTranslationMatrix хранится около 2 млн. строк с перспективой расширения до 10 млн и более.
Использование индексов
Из вышеприведенного кода можно сказать, что у нас есть пары текстов для сопоставления для разных пар языков. Есть задача поиска текста как по столбцу text1, так и по text2 с учетом того, что повторы текста могут быть по этим столбцам, но для разных пар языков. Для решения этой задачи будет использоваться функция filter с использованием функции Q для составления запросов. Так вот вся суть этой статьи заключается в следующем предложении: экспериментально я определил, что наилучшим образом поиск строк происходит в том случае, когда составление запроса происходит по столбцам, с которыми связаны индексы, и чем короче запрос, тем быстрее происходит выборка данных, по сравнению с поиском по индексам и фильтрация по другим столбцам.
Например, такой запрос БЕЗ ИНДЕКСОВ:
translations = LocalTranslationMatrix.objects.filter(Q(text1=translation_request.text) &
Q(languages=lang_pair.id))
на 2 млн строк выполняется за время ~2-3 сек
Если его модифицировать следующим образом и использовать индексы:
translations = LocalTranslationMatrix.objects.filter(Q(text1=translation_request.text))
то он будет выполнен за ~1 мс и меньше
А фильтрация на объектах в памяти питоновского процесса будет выполнена намного быстрее.
Маленькая хитрость или проблемы с длинными индексами
В mysql при индексах на столбцах длиннее 767 байт (255 символов на utf-8 с 3 байтами на символ) возникают проблемы: не создается база данных или не применяется миграция, для этого я создаю отдельную миграцию, которая выполняет следующее:
# -*- coding: utf-8 -*-
from __future__ import unicode_literals
import os
from django.db import migrations, models
class Migration(migrations.Migration):
#initial = False
dependencies = [
('translator', '0001_initial')
]
operations = [
migrations.RunSQL("SET GLOBAL innodb_large_prefix = ON; SET GLOBAL innodb_file_format=Barracuda;"),
migrations.RunSQL("ALTER TABLE translator_translation ROW_FORMAT=DYNAMIC;"),
migrations.RunSQL("ALTER TABLE translator_localtranslationmatrix ROW_FORMAT=DYNAMIC;")
]
Иногда возникают проблемы с созданием индексов через модель, их также можно создать через RunSQL:
migrations.RunSQL("CREATE INDEX translator_translation_i_original_text ON translator_translation(original_text)"),
Маленькая хитрость или проблемы с длинными индексами
В mysql при индексах на столбцах длиннее 767 байт (255 символов на utf-8 с 3 байтами на символ) возникают проблемы: не создается база данных или не применяется миграция, для этого я создаю отдельную миграцию, которая выполняет следующее:
# -*- coding: utf-8 -*-
from __future__ import unicode_literals
import os
from django.db import migrations, models
class Migration(migrations.Migration):
#initial = False
dependencies = [
('translator', '0001_initial')
]
operations = [
migrations.RunSQL("SET GLOBAL innodb_large_prefix = ON; SET GLOBAL innodb_file_format=Barracuda;"),
migrations.RunSQL("ALTER TABLE translator_translation ROW_FORMAT=DYNAMIC;"),
migrations.RunSQL("ALTER TABLE translator_localtranslationmatrix ROW_FORMAT=DYNAMIC;")
]
Иногда возникают проблемы с созданием индексов через модель, их также можно создать через RunSQL:
migrations.RunSQL("CREATE INDEX translator_translation_i_original_text ON translator_translation(original_text)"),
Заключение
Практика показывает, что самый лучший вариант - поиск ИСКЛЮЧИТЕЛЬНО только по индексам, а фильтрацию лучше проводить на объектах в памяти.
