Как работают индексы и их использование в Django

О чем эта статья

Данная статья не будет академическим изложением проблем индексирования в база данных, а будет затрагивать один маленький практический аспект использования индексов для выборки данных. Если отвечать на вопрос заголовка - о деталях и внимательном чтении документации, но я не такой педант (во всяком случае не всегда), мне значительно более интересно экспериментально посмотреть суть проблемы.

Окружение и приложение

Есть 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)"),

Заключение

Практика показывает, что самый лучший вариант - поиск ИСКЛЮЧИТЕЛЬНО только по индексам, а фильтрацию лучше проводить на объектах в памяти.