Что такое индексы в MySQL, как и для чего их использовать и создавать?go-96

Индексы в MySQL — это специальные структуры данных, которые ускоряют поиск и сортировку записей в таблицах. Они работают аналогично оглавлению в книге, позволяя СУБД быстро находить данные без полного сканирования таблицы.

Основные типы индексов в MySQL

  1. Primary Index (Первичный ключ)

    • Уникальный идентификатор записи
    • Создается автоматически для PRIMARY KEY
    • Таблица может иметь только один первичный индекс

  2. Secondary Index (Вторичный индекс)

    • Создается для часто используемых в WHERE/JOIN/ORDER BY столбцов
    • Может быть неуникальным

  3. Unique Index (Уникальный индекс)

    • Гарантирует уникальность значений в столбце
    • Может быть NULL (кроме PRIMARY KEY)

  4. Composite Index (Составной индекс)

    • Индекс по нескольким столбцам
    • Порядок столбцов важен (правило левого префикса)

  5. Full-text Index (Полнотекстовый индекс)

    • Для полнотекстового поиска
    • Только для MyISAM и InnoDB (с MySQL 5.6+)

Как создавать индексы

Синтаксис создания индексов:

-- При создании таблицы
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    UNIQUE INDEX idx_email (email),
    INDEX idx_created_at (created_at)
);

-- Добавление индекса к существующей таблице
ALTER TABLE users ADD INDEX idx_username (username);
CREATE INDEX idx_username ON users(username);

-- Составной индекс
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);

Когда использовать индексы

  1. Для ускорения поиска:

    • Столбцы в условиях WHERE
    • Столбцы в JOIN условиях

  2. Для оптимизации сортировки:

    • Столбцы в ORDER BY
    • Столбцы в GROUP BY

  3. Для покрывающих индексов (covering index):

    • Когда индекс содержит все необходимые для запроса поля

Когда индексы не помогут

  1. При работе с небольшими таблицами (полное сканирование может быть быстрее)
  2. Для столбцов с низкой селективностью (например, пол "М/Ж")
  3. При частых операциях INSERT/UPDATE (каждый индекс замедляет запись)

Пример анализа использования индексов

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

Вывод EXPLAIN покажет:

  • Используется ли индекс (ключевое поле key)
  • Количество просмотренных строк (rows)
  • Тип доступа (type = ref, range, index, all)

Оптимальные практики

  1. Индексируйте осознанно - каждый индекс занимает место и замедляет запись
  2. Используйте составные индексы для часто используемых комбинаций столбцов
  3. Следите за селективностью - индексы эффективны для высокоселективных столбцов
  4. Используйте покрывающие индексы чтобы избежать чтения самой таблицы
  5. Периодически анализируйте и оптимизируйте индексы с помощью: 
    ANALYZE TABLE users;
OPTIMIZE TABLE users;

Пример на Go: работа с индексами

package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err := sql.Open("mysql", "user:password@/dbname")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Проверка использования индекса
    rows, err := db.Query("EXPLAIN SELECT * FROM users WHERE username = ?", "john_doe")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    var (
        id           int
        selectType   string
        table        string
        possibleKeys string
        key          string
        keyLen       int
        ref          string
        rowsExamined int
        extra        string
    )

    for rows.Next() {
        if err := rows.Scan(&id, &selectType, &table, &possibleKeys, &key, &keyLen, &ref, &rowsExamined, &extra); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("Используется индекс: %s, Просмотрено строк: %d\n", key, rowsExamined)
    }
}

Резюмируем

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

Вопросы

Собеседования