Оптимизация долгого запроса к большой таблице с фильтром по датам и времени

оптимизация sql запроса

Общеизвестно, что чем больше таблицы, тем дольше выполняются запросы к ним.

Под прицелом у нас очень большая таблица (десятки или сотни гигабайт) и запрос с фильтром по датам. Нужно понимать, что индексы на даты ставят очень редко. Так что подобные запросы могут выполняться десятки секунд или минут.

Обычно, запросы так же подключают другие таблицы к главной, чтобы вытянуть необходимые данные.

Исходные данные для нашего примера:

  1. huge_table — наша целевая таблица размером 100 гигабайт
  2. join_table1, join_table2 — второстепенные таблицы из которые получаем дополнительные данные. Небольшие (гигабайт или около того)

Запрос выглядит так

SELECT huge_table.*
FROM huge_table
LEFT JOIN join_table1 on huge_table.column1 = join_table1.id
LEFT JOIN join_table2 on huge_table.column2 = join_table2.id
WHERE huge_table.date BETWEEN "2023-01-01 00:00:00" AND "2024-01-01 00:00:00";

Т.е. забираем данные по таблице huge_table за 2023й год

так как таблицы join_table1, join_table2 по условия небольшие, для оптимизации, мы можем рассмотреть упрощенный запрос примерно с тем же временем выполнения:

SELECT *
FROM huge_table
WHERE date BETWEEN "2023-01-01 00:00:00" AND "2024-01-01 00:00:00";

Реальное время выполнения для таблиц в 100 гигов будет несколько минут.

Теперь, представим, что нам нужно найти id первой записи за 2023й год и первой записи за 2024й в той же таблице. Оптимальные запросы будут такие:

SELECT id FROM huge_table WHERE date >= "2023-01-01 00:00:00" ORDER BY id ASC LIMIT 1;

SELECT id FROM huge_table WHERE date <= "2024-01-01 00:00:00" ORDER BY id DESC LIMIT 1;

Оба запроса в сумме выполняются до 5-10 секунд

Теперь понятно что делать с нашим исходным запросом:

SELECT *
FROM huge_table
WHERE date BETWEEN 
     (SELECT id FROM huge_table WHERE date >= "2023-01-01 00:00:00" ORDER BY id ASC LIMIT 1) 
AND 
     (SELECT id FROM huge_table WHERE date <= "2024-01-01 00:00:00" ORDER BY id DESC LIMIT 1);

выполняется на 2 порядка быстрее чем исходный запрос, в итоге.

Обновление MySQL 5.7 до 8.0: Как использовать Update Checker для безопасного перехода

mysql update

Обновление MySQL с версии 5.7 до 8.0 — важный этап, который требует внимательного планирования и грамотного выполнения. В этой статье мы рассмотрим процесс обновления, используя встроенный инструмент MySQL Shell — util.checkForServerUpgrade(). Данный инструмент поможет вам диагностировать проблемы совместимости и обеспечит плавный переход на новую версию.

Для начала, давайте установим MySQL Shell и перейдем к обновлению.

Шаг 1: Установка MySQL Shell

На Linux

$ sudo apt-get update 
$ sudo apt-get install mysql-shell

На macOS

$ brew install mysql-shell

На Windows

  1. Скачайте установщик MySQL Shell с официального сайта MySQL.
  2. Запустите установщик и следуйте инструкциям.

Шаг 2: Запуск MySQL Shell

Откройте терминал или командную строку и введите:

$ mysqlsh

Шаг 3: Проверка совместимости обновления

Инициируете соединение с базой данных (будь то локальная или удаленная база):

JS> \connect --mysql {user_name}@{my_sql_server}:3306

где {user_name} — ваш пользователь с базе данных с максимальными правами, {my_sql_server} — сервер базы данных. Потребуется ввод пароля пользователя

Используйте команду util.checkForServerUpgrade() для анализа текущей конфигурации и схемы базы данных:

JS> util.checkForServerUpgrade()

Вывод результатов будет примерно таким:

mysql update checker

Шаг 4: Подготовка к обновлению

Прежде чем начать фактическое обновление, убедитесь, что вы выполнили необходимые шаги в соответствии с рекомендациями, полученными из util.checkForServerUpgrade().

Шаг 5: Фактическое обновление

Выполните фактическое обновление MySQL с использованием команды util.upgrade.applyUpgrade().

Следуйте указаниям, предоставляемым MySQL Shell, и дождитесь завершения обновления.

Шаг 6: Проверка стабильности

После обновления проведите тщательное тестирование, чтобы гарантировать стабильную и эффективную работу вашей базы данных.

Обновление MySQL c 5.7 до 8.0 — нюансы структур и данных

mysql data tables

Обновление MySQL с версии 5.7 до 8.0 может включать в себя изменения в структуре таблиц и формате данных. Вот несколько нюансов, которые следует учитывать при обновлении:

  • Совместимость с типами данных:

В MySQL 8.0 улучшена обработка данных JSON, и формат хранения JSON может отличаться от формата в MySQL 5.7. Например, в MySQL 8.0 допускается более широкий спектр значений JSON и более глубокие вложенности.

После обновления структуры таблицы убедитесь, что ваши запросы для работы с JSON-данными все еще корректны. Проанализируйте запросы чтения и записи, чтобы удостовериться, что они соответствуют новой версии MySQL.

  • Устаревшие и удаленные функции:

Вот несколько советов и примеров по обработке устаревших и удаленных функций:

Перед обновлением полезно получить список устаревших функций, которые используются в вашей текущей версии MySQL. Это можно сделать с помощью запроса к информационной схеме:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'your_database' AND ROUTINE_COMMENT LIKE '%deprecated%';

Где ‘your_database’ замените на имя вашей базы данных.

После того как вы определите устаревшие функции, ищите их замены в официальной документации MySQL. Обычно, в документации приводятся рекомендации по использованию новых функций или способов достижения того же результата.

Примеры изменений:

Группировка результатов GROUP_CONCAT:
У функции GROUP_CONCAT в MySQL 8.0 появились новые опции и ограничения. Например, вместо использования:

SELECT GROUP_CONCAT(column_name) FROM your_table;

Вы можете использовать:

SELECT GROUP_CONCAT(column_name ORDER BY some_column SEPARATOR ', ') FROM your_table;

Использование директивы ONLY_FULL_GROUP_BY:

В MySQL 8.0 строже соблюдается директива ONLY_FULL_GROUP_BY, что означает, что все столбцы в SELECT, кроме агрегированных или включенных в GROUP BY, должны быть включены в GROUP BY. Это может потребовать пересмотра запросов.

  • Новые возможности

MySQL 8.0 вводит новые возможности и улучшения. Рассмотрите возможность использования новых функций, таких как оконные функции, улучшенная поддержка аналитики и другие.

  • Обработка NULL-значений

Поведение в отношении NULL-значений может измениться. Убедитесь, что ваш код обрабатывает NULL-значения так, как это предполагается в новой версии.

  • Ограничения на длину индексов

В MySQL 8.0 могут быть изменены ограничения на длину индексов. Если у вас есть индексы, убедитесь, что они соответствуют новым правилам.

  • Системные переменные и конфигурационные параметры

Некоторые параметры конфигурации могут быть переименованы или изменены. Обновите свой файл конфигурации с учетом новых параметров.

  • Поведение с SQL-запросами

Некоторые запросы могут начать возвращать различные результаты или вызывать ошибки из-за изменений в синтаксисе SQL или оптимизаторе запросов. Пересмотрите свои запросы и приведите их в соответствие с новой версией.

  • Поддержка времени выполнения

MySQL 8.0 внес изменения в обработку времени выполнения. Убедитесь, что ваше приложение корректно обрабатывает новые требования.

  • Аутентификация и безопасность

Внесены изменения в системы аутентификации и безопасности. Проанализируйте изменения в этой области и примените соответствующие настройки.

  • Тестирование

Перед обновлением рекомендуется провести тестирование на тестовой среде, чтобы выявить и решить возможные проблемы.

Обновление mySQL с версии 5.7 до 8.0

update mysql
  1. Сперва стоит ознакомиться с нюансами, которые следует учитывать при обновлении с версии 5.7 до 8.0
  2. Так же, стоит воспользоваться инструментом для проверки готовности обновления базы данных — Update Checker

Сами процедуры могут немного различаться в зависимости от операционной системы, поэтому приведенные ниже шаги ориентированы на общую схему и могут потребовать некоторых адаптаций.

  • Создаем резервную копию данных

Создание резервной копии данных в MySQL можно выполнить с использованием инструмента mysqldump, который поставляется вместе с MySQL. Этот инструмент позволяет экспортировать базу данных или выбранные таблицы в файл SQL, который затем можно использовать для восстановления данных. Вот примеры использования mysqldump:

mysqldump -u username -p --all-databases > backup.sql

Где:

  • -u username: указывает имя пользователя базы данных.
  • -p: запрашивает пароль пользователя (вас попросят ввести пароль после выполнения команды).
  • --all-databases: указывает, что нужно создать резервную копию всех баз данных.

Создание резервной копии конкретной базы данных:

mysqldump -u username -p database_name > backup.sql

Где database_name — это имя вашей базы данных.

Создание резервной копии конкретных таблиц:

mysqldump -u username -p database_name table1 table2 > backup.sql

Где table1 и table2 — это имена таблиц.

Восстановление из резервной копии:

mysql -u username -p database_name < backup.sql

Где database_name — это имя базы данных, а backup.sql — ваш файл резервной копии.

Помните о безопасности: не храните пароли в открытом виде в командах, и убедитесь, что ваш файл резервной копии хранится в безопасном месте.

  • Убеждаемся в наличии резервной копии конфигурационных файлов

Cохраняем резервные копии конфигурационных файлов MySQL, таких как my.cnf или my.ini.

  • Устанавливаем новую версию MySQL

Установка MySQL 8.0 на Linux (Ubuntu):

Обновите информацию о доступных пакетах:

sudo apt-get update

Установите MySQL Server:

sudo apt-get install mysql-server

MySQL должен автоматически запуститься после установки. Если нет, вы можете запустить его вручную:

sudo systemctl start mysql

После установки MySQL, выполните команду для настройки безопасности:

sudo mysql_secure_installation

В процессе выполнения вы будете отвечать на вопросы и устанавливать пароль для пользователя root MySQL.

Установка MySQL 8.0 на Windows:
— Перейдите на официальный сайт MySQL и загрузите установочный файл для Windows: https://dev.mysql.com/downloads/installer/
— Запустите установщик и следуйте инструкциям. Выберите «Developer Default» для установки сервера MySQL, а также других инструментов, таких как MySQL Workbench.
— В процессе установки вам могут быть предложены параметры конфигурации сервера. Убедитесь, что вы запомнили пароль для пользователя root MySQL.
— Завершите установку, следуя указаниям установщика.
— MySQL Server должен автоматически запуститься после установки. Если нет, вы можете запустить его с помощью MySQL Notifier или службы Windows.

  • Используем MySQL Shell для обновления схемы

MySQL 8.0 включает в себя MySQL Shell, который предоставляет утилиту mysqlsh. Запустите его и введите следующие команды:

mysqlsh 
\sql
\source path/to/mysql-upgrade.sql

Вместо path/to/mysql-upgrade.sql укажите путь к скрипту обновления, который предоставляется в составе дистрибутива MySQL.

  • Обновление и анализ данных

После обновления схемы MySQL, переносим данные из старой версии в новую, используя утилиты, такие как mysqldump или mysql_upgrade.

  • Настраиваем конфигурацию

Анализируем и адаптируем конфигурационные файлы (например, my.cnf или my.ini) для новой версии MySQL.

  • Перезапустите MySQL после завершения обновления и настройки.
  • Проверяем работоспособность вашего приложения и базы данных, чтобы убедиться, что все работает корректно.

Необходимо обязательно ознакомьтесь с официальной документацией MySQL для вашей конкретной версии и операционной системы, чтобы уточнить детали и изменения, связанные с обновлением.

Django python — выгрузка результатов тяжелого запроса в CSV c разделителем

Исходные данные:

  • Джанго проект, база данных
  • Есть сложный запрос, с большим количеством данных. Часто запрос моно описать моделями, чтобы усложнить задачу, будем писать сырой запрос mysql
  • Необходимо надежно выгрузить в CSV. Может даже миллионы строк
  • первой строкой в выгрузке будет хедер с названием столбцов
  • кастомный разделитель полей, например точка с запятой

1) views.py

  • добавляем необходимые импорты в начале кода
from django.http import StreamingHttpResponse
import csv
  • файло-подобный объект, который понадобится для добавления строк запроса в выходной CSV
class Echo:
    def write(self, value):
        return value
  • сам метод выгрузки в csv
def download_csv(filename, rows):
    pseudo_buffer = Echo()
    # используем кастомный разделитель полей - точка с запятой, по умолчанию, разделитель - запятая
    writer = csv.writer(pseudo_buffer, delimiter=';')
    return StreamingHttpResponse(
        (
            writer.writerow(row) for row in rows
        ),
            content_type="text/csv",
            headers={'Content-Disposition': 'attachment; filename="' + filename + '.csv"'},
        )
  • и пример его использования:
def download_orders_for_user(request):
    # например, запрос всех заказов пользователей с айдишниками от 1 до 10000 и суммой больше 10
    query = """
        SELECT order.number, 
            order.date, 
            order.sum, 
            order.status
        FROM order
        LEFT JOIN user ON user.id = order.user_id
        WHERE user BETWEEN 1 and 10000
        AND order.sum > 10
    """

    with connections["default"].cursor() as cursor:
        cursor.execute(query)
        rows = cursor.fetchall()

    # формируем имя файла
    filename = 'orders_for_users'

    # первой строкой у нас будет хедер с названием столбцов
    header_row = (('Номер', 'Дата', 'Сумма', 'Статус'),)
    rows = header_row + rows
    return download_csv(filename, rows)

2) urls.py

Зададим 2 урла:

  • для страницы, со ссылкой на выгрузку
  • для запроса выгрузки (нажатие на ссылку выгрузки)
…
urlpatterns = [
…
path('download_page/', views.download_page, name='download_page'),
path('download_page/csv/', views.download_orders_for_user, name='download_orders_for_user'),
]

3) создадим темплейт с HTML страничкой

для размещения ссылки скачивания и проверки работы наших скриптов
download_page.html
со следующим содержимым:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Download SCV</title>
</head>
<body>
    <div>
        <h4>Скачать заказы пользователей: </h4>
        <a id="download" href="csv/">CSV</a>
    </div>
</body>
</html>

Это все!

django подключение второй базы данных без моделей

Может понадобится, например, когда нужно перенести какие то данные из одной базы данных, в базу данных под управлением django. В таком случае, по первой базе данных нет необходимости (или невозможно) формировать модели.

При необходимости, к первой базе тоже можно применить модели в django приложении.

Первым делом, добавляем второе подключение («sourcedb«), наряду с основным («default«) в файле конфигов settings.py. Для примера, у нас mysql:

...
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.mysql",
        "NAME": "DATABASE_NAME_default",
        "HOST": "DATABASE_HOST_default",
        "PORT": "3306",
        "USER": "DATABASE_USER_default",
        "PASSWORD": "DATABASE_PASSWORD_default",
        'OPTIONS': {
            'sql_mode': 'traditional',
        }
    },
    "sourcedb": {
        "ENGINE": "django.db.backends.mysql",
        "NAME": "DATABASE_NAME_sourcedb",
        "HOST": "DATABASE_HOST_sourcedb",
        "PORT": "3306",
        "USER": "DATABASE_USER_sourcedb",
        "PASSWORD": "DATABASE_PASSWORD_sourcedb",
        'OPTIONS': {
            'sql_mode': 'traditional',
        }
    }
}
...

Теперь можно использовать второе подключение для забора данных:

1) одну запись (fetchone):

from django.db import connections
…

def get_single_record_from_second_database(order_number):

    try:
        with connections['sourcedb'].cursor() as cursor:
            cursor.execute("""SELECT id, `sum`, user_id 
                            FROM orders 
                            WHERE order_number = '%s'
                            """ % order_number)
            order = cursor.fetchone()
            cursor.close()
    except Exception as error:
        print("Failed to read data from Orders table (for %s):" % order_number, error)
...

2) или обрабатываем много записей (fetchall)

from django.db import connections
…

def get_multiple_records_from_second_database(date):

    try:
        with connections['sourcedb'].cursor() as cursor:
            cursor.execute("""SELECT id, `sum`, user_id 
                            FROM orders 
                            WHERE date = '%s'
                            """ % date)
            orders = cursor.fetchall()
                for order in orders:
                    ...
            cursor.close()
    except Exception as error:
        print("Failed to read data from Orders table (for %s):" % date, error)
...

В цикле for можно обрабатывать заказы (order) и заполнять данными основную (default) базу данных

Django python — загрузка csv и сохранение в базу данных

Это руководство для новичков в Django, которые прочитали начальное руководство по Django на официальном сайте и столкнулись с задачей загрузки csv файла с таблицей в базу данных

В этом случае, у вас есть приложение polls, с урлами, моделями, вьюшками для классов вопросов (Question) и ответов (Choices). И база данных с сгенерированными таблицами (polls_question, polls_choice)

Для примера, будем загружать в базу данных csv файл с вопросами (Questions). 

Назовем файл questions_01a.csv и поместим туда такое содержимое:

Why?,2022-11-14 10:00
For what?,2022-11-14 10:01
When?,2022-11-14 10:02
Where?,2022-11-14 10:03

Перенос строк — как разделитель информации по вопросам, и запятая — как разделитель ячеек данных

Изменения в /polls/urls.py:

from django.urls import path

from . import views

app_name = 'polls'
urlpatterns = [
    …
    path('upload_csv/', views.upload_csv, name='upload_csv'),
]

Изменения в /polls/views.py:

from django.http import HttpResponseRedirect
from django.shortcuts import render, get_object_or_404
from django.urls import reverse
from django.views import generic
from django.utils import timezone
from django.contrib import messages

from .models import Question, Choice

…

def upload_csv(request):
    data = {}
    if "GET" == request.method:
        return render(request, "polls/upload.html", data)
    # if not GET, then proceed
    try:
        csv_file = request.FILES["csv_file"]
        if not csv_file.name.endswith('.csv'):
            messages.error(request, 'File is not a CSV')
            return HttpResponseRedirect(reverse("polls:upload_csv"))
        # if file is too large - error
        if csv_file.multiple_chunks():
            messages.error(request, "Uploaded file is too big (%.2f MB). " % (csv_file.size/(1000*1000),))
            return HttpResponseRedirect(reverse("polls:upload_csv"))

        file_data = csv_file.read().decode("utf-8")

        lines = file_data.split("\n")
        # loop over the lines and save them to db via model
        for line in lines:
            fields = line.split(",")

            try:
                question = Question(
                    question_text=fields[0],
                    pub_date=fields[1],
                )
                question.save()

            except Exception as e:
                messages.error(request, "Unable to upload file. "+repr(e))
                pass

    except Exception as e:
        messages.error(request, "Unable to upload file. "+repr(e))

    return HttpResponseRedirect(reverse("polls:upload_csv"))

Что делаем в views.py:

  • Импортируем стандартный фреймворк сообщений (messages), чтобы можно было на страничке выводить ошибки
  • Файл будем посылать методом POST, если же запрос GET — открываем страницу аплода
  • Делаем простые проверки получаемого файла (тип файла и максимальный размер)
  • Читаем построчно и сохраняем в базу данных, через модель

Создаем страничку для загрузки файла — /polls/templates/polls/upload.html со следующим кодом:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Upload</title>
    <link href="https://netdna.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <form action="{% url "polls:upload_csv" %}" method="POST" enctype="multipart/form-data" class="form-horizontal">
        {% csrf_token %}
        <div class="form-group">
            <label for="name" class="col-md-3 col-sm-3 col-xs-12 control-label">File: </label>
            <div class="col-md-8">
                <input type="file" name="csv_file" id="csv_file" required="True" class="form-control">
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-3 col-sm-3 col-xs-12 col-md-offset-3" style="margin-bottom:10px;">
                 <button class="btn btn-primary"> <span class="glyphicon glyphicon-upload" style="margin-right:5px;"></span>Upload </button>
            </div>
        </div>
        {% if messages %}
        <ul class="messages">
            {% for message in messages %}
            <li{% if message.tags %} class="{{ message.tags }}"{% endif %}>{{ message }}</li>
            {% endfor %}
        </ul>
        {% endif %}
    </form>
</body>
</html>

Что делаем в upload.html:

  • Минималистичная html страница
  • Подключаем стили бутстрап для более-менее симпатичного отображения элементов
  • Форма с кнопкой Загрузки
  • CSFR токен для защиты формы от размещения ее на внешних источниках, чтобы украсть ваши данные (стандартная защита)
  • Блок для отображения ошибок messages

Запускаем проект и пробуем открывать страничку 

http://127.0.0.1:8000/polls/upload_csv/

Загружаем наш заранее заготовленный файл questions_01a.csv и проверяем базу данных на наличие новых строк

Выглядит это так:

Оптимизация запросов MySQL — ускоряем запросы

  • Одна из распространённых причин медленных запросов — обработка лишних данных

Это может быть запрос лишних строк из большой таблицы, где не все они нужны. Можно использовать LIMIT или ограничивать выборку WHERE

SELECT * FROM huge_table LIMIT 10;
-- или
SELECT * FROM huge_table WHERE id BETWEEN 0 AND 1000;

Это может быть запрос всех столбцов в таблице с большим их количеством или в нескольких таблицах, соединенных JOIN. Лучше, запрашивать только необходимые столбцы

SELECT id, name, email FROM users LIMIT 1000;

Это может быть повторный запрос за теми же данными. В этом случае, если не получается избежать, стоит кешировать результаты и использовать данные из кеша

  • Другая причина медленных запрсов — анализ лишних данных

Необходимо минимизировать этот параметр. Например, запрос по индексируемому столбцу анализирует очень ограниченный набор данных, а по этому же столбцу без индекса — может анализировать все строки таблицы. Простейшее решение будет добавление или оптимизация индексов (ссылка на статью) 

Каким образом и сколько данных анализируется, можно посмотреть используя команду EXPLAIN и строка rows в результатах

  • Эффективным способом ускорения сложного запроса может быть разбиение его на более простые, которые выполняются быстрее первоначального
-- Было
SELECT * FROM tag 
JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag_name = ‘mysql’;

-- Стало
SELECT * FROM tag WHERE tag_name = ‘mysql’;
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE id IN (123, 456,789);
  • Или разбиение очень длительного запроса на более короткие порции
-- Было
DELETE FROM messages WHER created < DATE_SUB(NOW(), INTERVAL 3 MONTH);

-- Стало
DELETE FROM messages WHER created < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 1000; 
-- несколько раз
  • Оптимизатор MySQL плохо оптимизирует корректированные подзапрос вида
SELECT * FROM films WHERE id IN (
    SELECT film_id FROM film_actors WHERE actor_id = 100
);

В этом случае, лучше разделить и связать их на бекенде

SELECT film_id FROM film_actors WHERE actor_id = 100;
-- И затем
SELECT * FROM films WHERE id IN (1,5,9,13,77);
  • Не стоит злоупотреблять сортировкой ORDER BY, так как, без индекса на сортируемом столбце, она может значительно увеличить время выполнения запроса. Можно использовать LIMIT для ускорения сортировки, если она необходима
  • Запросы с UNION часто идет с ограничением LIMIT, и для ускорения, следует добавить LIMIT в оба под-запроса
-- Было
(SELECT id, name FROM film_actors order by name)
UNION ALL
(SELECT id, name FROM theatre_actors order by name)
LIMIT 20;

-- Стало
(SELECT id, name FROM film_actors order by name LIMIT 20)
UNION ALL
(SELECT id, name FROM theate_actors order by name LIMIT 20)
LIMIT 20;
  • Запросы с LIMIT и OFFSET на больших таблицах плохо работают с большими OFFSET, а это часто встречается в пагинации

Например для:

SELECT * FROM users ORDER BY id LIMIT 100 OFFSET 40000;

Сервер сгенерирует  40100 строк и отбросит первые 40000

Стоит заменить на:

SELECT * FROM users WHERE id > 40000 ORDER BY id LIMIT 100;
  • Ну и на последок, на бекенде часто используется ORM для генерации запросов. И нужно понимать, что для сложных запросов ORM формирует неоптимальный медленный запрос. В таком случае, следует переписать ORM запрос на чистый SQL

Оптимизация запросов MySQL — архитектура таблиц

Когда мы говорим про оптимизацию запросов, в первую очередь, стоит оптимизировать сами типы столбцов таблицы. Лучше это делать при создании таблиц или при добавлении новых столбцов в таблицу.

  • Меньшие по размеру типы данных обычно быстрее, так как занимают меньше места на диске, в памяти и кэше процессора
  • Следует упрощать сложные типы данных, если такое возможно. 

Например, IP адреса лучше хранить в целочисленных данных, так как сравнение строковых данных работает намного медленнее. 

Даты и время рекомендуется хранить во встроенных типах данных MySQL. Из DATETIME и TIMESTAMP, последний предпочтительнее, так как требует вдвое меньше места для хранения

  • Целые числа: TINYINT (8 бит), SMALLINT (16 бит), MEDIUMINT (24 бит), INT (32 бит), BIGINT(64 бит) и диапазоны значений от -2^(N — 1) до 2^(N — 1). В зависимости от планируемого диапазона значений — выбираем более простой тип

Часто, отрицательные значений не требуются (например, авто-инкрементирующийся ID) и можно расширить диапазон в два раза благодаря атрибуту UNSIGNED.

  • Вещественные числа занимают значительно больше места.

Расчеты с данными с плавающей точкой высокозатратные и могут приводить к ошибкам на бекенде, когда «1» на самом деле берется как «0.9999987». В том числе поэтому, целесообразнее выбрать Целочисленный тип вместо Вещественного. Если нам известно, что у данных максимум 2 знака после запятой, «109.40» храним и обрабатываем как «10940»

  • Наиболее часто используемый строковый тип — VARCHAR. Используется при хранении данных переменной длины.
  • В отличии от VARCHAR, CHAR имеет фиксированную длину и лучше оптимизирован для сравнения строк с примерно одинаковой длинной (например для хранения MD5 сверток данных пользователя). 

CHAR так же эффективен для хранения часто изменяемых данных, так как не подвержен фрагментации при записи

  • Для хранения больших данных используются: TEXT — символьные данные, BLOB — двоичные данные
  • Если количество вариаций данных ограничено, то можно использовать ENUM

ENUM работает быстро, так как, по сути, хранит целое число. Недостатком ENUM будет то, что при добавление нового значения в набор, нужно будет менять схему таблицы (ALTER TABLE) или писать миграцию на бекенде

  • Лучше избегать значений NULL, если они не нужны. 

Стоит объявлять столбец как NOT NULL. MySQL тяжелее оптимизировать запросы к столбцам, допускающим NULL

Оптимизация запросов MySQL — производительные индексы

Индексы (или ключи) — это специальные структуры данных, которые использует подсистемы хранения для ускорения нахождения строк.

  • Индексы бесполезны, и, даже, вредны, если данных в таблицы мало (тысячи или меньше строк)
  • Если данных много (десятки тысяч строк и больше) и есть запрос WHERE по одному столбцу (или же сортировка ORDER BY по этому столбцу), который выполняется часто, лучше «повесить» на него простой индекс
  • Индекс хорошо работают по полному значению, диапазону значений
  • Составной индекс включает набор индексов (например, по трем столбцам A + B + C)
  • В это случае, индекс хорошо работает по полному набору проиндексированных столбцов («A + B + C»), по префиксам (левой части индекса: «A» или «A + B») и НЕ работает по пост-фиксам (правой части индекса: «C» или «B + C»). В случае необходимости поиска по «B» или «B + C», можно создать отдельный индекс — «B + C». Опять таки, индекс «B + C» будет неэффективен при поиске по C.
  • Альтернативой при существующем индексе «A + B + C» и необходимости поиска по пост-фиксу индекса, может быть включение в поиск столбца A искусственным образом:
SELECT * FROM TABLE WHERE B=2 AND C=3;
-- преобразуем в:
SELECT * FROM TABLE WHERE A="2022-10-14" AND B=2 AND C=3;
  • Так же, необходимо рассмотреть возможность смены порядка следования столбцов в запросе WHERE. Если, имеем индекс «A + B + C», при этом наш запрос выглядит так:
-- было:
SELECT * FROM TABLE WHERE A = 1 AND C = 2 AND B = 3;
-- стало:
SELECT * FROM TABLE WHERE A = 1 AND B = 3 AND C = 2;
  • Нужно понимать, что, если в запросе есть поиск по одному столбцу и сортировка по другому, хорошо подойдет составно индекс по этим столбцам и плохо будет работать отдельный индекс по одному из столбцов
  • В случае индексов на столбец строкового типа, индекс хорошо работает по полному совпадению и при поиске по префиксу (WHERE column LIKE «abc%»). При это у индекса есть ограничения, и по длинной строке поиск будет работать плохо
  • Чем меньше памяти потребляет тип столбца, тем быстрее работает и индекс. Поэтому поиск по индексированному целочисленному столбцу гораздо быстрее, чем по строковому
  • В случае длинных строк, и необходимости поиска по ним, можно ввести индексируемый столбец с хешем по длинной строке.

Хороший пример данных, это столбец с адресом веб-страниц url и поддержка столбца  c хешем этой строки — url_hash. При сохранение данных, записываем в столбец url оригинальный адрес веб-страницы, а в url_hash — хеш строки этого адреса (например кодировкой CRC32 или другой кодировкой в цифровое значение). А при поиске ведем поиск только по индексированному url_hash.

-- долгий запрос:
SELECT * FROM TABLE WHERE url = "https://long-adress-url.com";
-- заменяем на:
SELECT * FROM TABLE WHERE url_hash = CRC32("https://long-adress-url.com");
  • Другой частый случай плохо-индексируемых данных — это IP-адреса. Для них, так же есть специальный функции на бекенде, которые превращают строку IP-адреса в числовой тип для отдельного индексируемого столбца.

Конкретные функции зависят от языка программирования. Например, для PHP подойдет функция ip2long для шифровки и long2ip для расшифровки