Перейти к содержимому 
Я покажу вам, как Производительность базы данных в веб-хостинге: с целенаправленными запросами, целевыми индексами и чистой блокировкой. Это позволяет разгрузить MySQL под нагрузкой, избежать времени ожидания и добиться надежного времени отклика даже при большом количестве одновременных обращений.
Центральные пункты
- Запросы не унывать: Проекция, фильтры, EXPLAIN
- Индексы задавайте конкретно: ГДЕ, ПРИСОЕДИНИТЬСЯ, УПОРЯДОЧИТЬ ПО
- Блокировка минимизировать: блокировки строк, короткие транзакции
- Кэширование использовать: Redis/Memcached, Keyset-Pagination
- Мониторинг установить: Slow-Log, Performance Scheme
Схема и ресурсы в веб-хостинге: регулировочные винты
Хорошо продуманный Разработка схемы экономит время сервера, поскольку предотвращает ненужные соединения и дублирование данных без ущерба для читабельности запросов. Я нормализую таблицы до разумного уровня и денормализую специально, когда измеренные значения показывают, что соединения становятся слишком дорогими. На общих и управляемых хостах я обращаю внимание на профили процессора, оперативной памяти и ввода-вывода, поскольку узкие места часто кроются не в SQL, а в дефицитных ресурсах. Для InnoDB я устанавливаю значение innodb_buffer_pool_size обычно до 70-80% доступной оперативной памяти, чтобы держать в памяти как можно больше страниц. Кроме того, я проверяю, помещаются ли в память временные таблицы, чтобы запросы не блокировали медленные носители данных.
Модель данных и типы: Основа для быстрого доступа
Я выбираю Типы данных как можно меньше и уместнее: INT вместо BIGINT, DECIMAL для денежных значений, DATETIME вместо TEXT для временных характеристик. Для строк я постоянно использую utf8mb4 с подходящей коллизией (например, _ai_ci для сравнений без учета ударения и регистра). Там, где необходимо чувствительное к регистру или двоичное сравнение, я специально использую коллизию _bin на уровне столбцов. Эти решения влияют на размер индекса, поведение сортировки и, в конечном счете, на объем данных, помещающихся в буферный пул.
На сайте Первичный ключ Я держу ключ небольшим (обычно AUTO_INCREMENT INT/BIGINT). Поскольку вторичные индексы InnoDB содержат PK в качестве суффикса, компактный PK экономит память и ускоряет сканирование только по индексу. Монотонно растущие PK также уменьшают разделение страниц при вставке. Для таблиц с большой интенсивностью записи и анализом, основанным на времени, я использую вторичные индексы по created_at или status+created_at для обслуживания типичных запросов без затрат на сортировку.
Для JSON-полей, я создаю вычисляемые (GENERATED) столбцы, которые извлекают определенные части JSON. Я могу индексировать эти генерируемые столбцы как обычные столбцы, чтобы фильтры по путям JSON были основаны на индексе. Я также отображаю производные значения (например, LOWER(email)) как виртуальный столбец вместо использования функций в WHERE - таким образом, запросы остаются универсальными.
Эффективное проектирование запросов: EXPLAIN, фильтры, проекция
Я всегда начинаю оптимизацию с Запросне SELECT-*, а только необходимые столбцы, чтобы меньше нагружать сеть и процессор. Я использую EXPLAIN, чтобы проверить, эффективны ли индексы и использует ли оптимизатор сканирование индексов вместо полного сканирования таблицы. Я пишу фильтры sargable, то есть на стороне столбцов, без функций типа LOWER() в WHERE, чтобы индексы могли действовать. В случае заметных задержек я часто ссылаюсь на причины в дизайне запроса; хорошим введением является эта статья о Высокая задержка базы данных. Журнал медленных запросов дает мне информацию о самых больших потерях времени, которые я затем настраиваю с помощью EXPLAIN ANALYZE и реальных параметров.
Я установил Подготовленные заявления со связанными параметрами, чтобы сократить усилия по разбору и планированию и сохранить стабильность плана. Я часто заменяю условия OR для разных столбцов на UNION ALL из двух удобных для индекса частичных запросов. Там, где это возможно, я разрабатываю Охватывающие запросыПодходящий индекс, содержащий все выбранные столбцы, позволяет избежать дополнительных поисков в таблице и экономит ввод-вывод. Я планирую сортировку так, чтобы она согласовывалась с последовательностью индексов; это устраняет необходимость в файловом сортировании и временных таблицах.
В MySQL 8 я использую Функции окна когда они заменяют джойны или подзапросы и остаются удобными для индексов. При больших значениях LIMIT я ускоряю использование методов поиска (набор ключей) и стабильных курсоров (например, ORDER BY created_at, id) для обеспечения детерминированного и воспроизводимого просмотра страниц.
Соединения, пагинация и кэширование в повседневной жизни
Я предпочитаю INNER JOIN перед LEFT JOIN, если это технически допустимо, и проиндексируйте каждый соединяемый столбец обеих таблиц. Я часто заменяю подзапросы на соединения, потому что MySQL может лучше их планировать и работать с индексами. Я предпочитаю реализовывать пагинацию как пагинацию по набору ключей (WHERE id > ? ORDER BY id LIMIT N), потому что OFFSET становится дорогим при больших пропусках. Я кэширую результаты, которые редко меняются, через Redis или Memcached, что значительно снижает нагрузку на сервер. Я оставляю исторически существующий кэш запросов деактивированным для многих операций записи, поскольку его административные расходы в противном случае будут иметь тормозящий эффект.
Я предотвращаю N+1 запросы, путем пакетной загрузки необходимых записей данных (список IN с ограниченным размером) и предварительного разрешения отношений с помощью подходящих объединений. Для Кэширование Я определяю четкие правила аннулирования: запись через запись для изменений, короткие TTL для изменчивых областей, более длительные TTL для фидов и архивов. Я структурирую ключи кэша с учетом версий (например, версии схемы или фильтра), чтобы при развертывании не возникало устаревших структур.
Для пагинации кнопок в реальных приложениях я часто использую Составной курсор (например, created_at и id), чтобы сортировка оставалась стабильной и поддерживалась индексами. Для мягких критериев (например, релевантности) я слежу за тем, чтобы ведущий критерий сортировки был индексируемым, а релевантность служила только для переключения в кэше или в предварительном расчете.
Правильное планирование индексов: от одиночного к композитному
Точный Индекс преобразует линейный поиск в логарифмы: При 100 000 строк я обычно использую несколько сравнений вместо полного сканирования. Я устанавливаю индексы на столбцы, которые встречаются в WHERE, JOIN и ORDER BY, и проверяю с помощью EXPLAIN, используются ли они. Я планирую составные индексы в соответствии с левосторонним использованием: (A,B,C) охватывает поиск по A, A+B и A+B+C, но не B+C без A. Для длинных строк я использую префиксные индексы, например первые 10-20 байт, чтобы сэкономить память и увеличить количество попаданий в кэш. Как Показатели дозирования Практика показывает: слишком большое количество индексов обходится очень дорого при INSERT/UPDATE/DELETE.
| Тип индекса | Преимущества | Недостатки | Типичное использование |
|---|---|---|---|
| ОСНОВНОЙ | Уникальность, очень быстрый поиск | Дубликаты запрещены | Каждая таблица, ключ кластера для InnoDB |
| УНИКАЛЬНЫЙ | Предотвращает дублирование значений | Усилия по написанию увеличиваются | Электронная почта, имя пользователя, ссылка |
| ИНДЕКС | Гибкие фильтры и сортировка | Усилия по хранению и обслуживанию | Столбцы WHERE и JOIN |
| ПОЛНЫЙ ТЕКСТ | Поиск текста на основе релевантности | Продуманный дизайн, большие размеры | Поиск по заголовкам и содержанию |
Я обращаю внимание на Индексы покрытия, которые содержат все необходимые столбцы (фильтр, сортировка, проекция). Это позволяет достичь планов „Использование индекса“, которые читают только индекс. Для сортировки по убыванию я использую поддержку MySQL 8 для компонентов DESC в составных индексах, чтобы не было необходимости в инвертированном сканировании или дополнительной сортировке.
Для экспериментов я использую невидимые индексы на: Я делаю индекс невидимым, наблюдаю за планами и задержками, а затем решаю, удалить его или оставить - без риска для производственной нагрузки. Я регулярно выполняю ANALYZE TABLE, чтобы статистика была свежей, а оптимизатор правильно оценивал кардинальность.
WordPress MySQL: типичные "горячие точки" и способы их устранения
На сайте WordPress-При настройке я сначала проверяю wp_posts и wp_postmeta, потому что именно здесь заканчивается большинство запросов. Я индексирую wp_posts.post_date, если архивы или фиды поставляют отсортированные посты, а также wp_postmeta.meta_key для быстрого поиска метаданных. В WooCommerce я обращаю внимание на запросы к заказам и товарам, которые часто содержат JOINы на многих метаданных; здесь помогают целевые составные индексы. Я ускоряю дорогие списки администратора с помощью пагинации по набору ключей и сортировки на стороне сервера с использованием подходящих индексов. Я также использую кэш объектов и переходные периоды, чтобы повторяющиеся запросы постоянно не обращались к базе данных.
На сайте meta_query-фильтров, я слежу за правильностью набора: привожу числовые значения, чтобы сравнения оставались индексируемыми. Я избегаю широкого поиска по LIKE с ведущим подстановочным знаком; вместо этого я сохраняю ключи для поиска отдельно и индексирую их. По возможности я заранее загружаю WP_Query с необходимыми метаданными, чтобы предотвратить появление N+1 шаблонов в шаблоне. Я настраиваю задания cron и частоту сердцебиения, чтобы не было постоянной нагрузки на базу в админке.
Понимание блокировки: Блокировки строк, MVCC и изоляция
Я минимизирую Блокировка, полагаясь на InnoDB, записывая короткие транзакции и затрагивая только те строки, которые действительно нужны. Блокировки на уровне строк позволяют одновременный доступ, в то время как блокировки таблиц останавливают многие вещи; это оказывает огромное влияние на время ожидания. MVCC обеспечивает чтение без блокировки, пока я устанавливаю подходящие уровни изоляции, например READ COMMITTED. Я редко использую SELECT ... FOR UPDATE, поскольку он может блокировать сеансы записи и создавать длинные цепочки времени ожидания. Более подробные практические примеры по блокировкам и циклам см. в этом руководстве Тупики в хостинге.
Я обращаю внимание на Изоляция по умолчанию REPEATABLE READ из InnoDB и возникающие в результате разрывы блокировок при обновлении диапазона. Если возможно, я переключаюсь на READ COMMITTED и проверяю, допустимы ли фантомы с технической точки зрения - это уменьшает количество блокировок. Я строго инкапсулирую процессы записи, избегаю интерактивного ожидания внутри транзакций и изолирую "горячие точки" (например, счетчики) в отдельных таблицах или использую атомарные UPDATE с условиями.
Сохраняйте транзакции компактными и избегайте тупиковых ситуаций
Я держу Транзакции как можно короче и перемещаю вычислительно трудоемкие шаги, не требующие блокировок, до или после части записи. Я всегда выполняю обновления в одной и той же последовательности столбцов и таблиц, чтобы между сессиями не образовывалось циклов. Я разбиваю длинные партии на более мелкие куски, чтобы другие сессии могли продвигаться между ними. В случае возникновения конфликтов я полагаюсь на повторные попытки с отступлением, а не заставляю сеанс ждать несколько минут. Тайм-ауты для блокировок и утверждений не позволяют очередям накапливаться незаметно.
На сайте тупиковые ситуации Я анализирую SHOW ENGINE INNODB STATUS и информацию о тупиках, чтобы определить задействованные запросы и скорректировать последовательность доступа. Целенаправленный дополнительный индекс, уменьшающий количество сканирований диапазона, часто решает больше, чем любое увеличение таймаутов. Я регистрирую затронутые SQL-запросы, включая привязки, чтобы можно было воспроизвести и навсегда устранить патологии.
Масштабирование: репликация, разделение, шардинг
Если нагрузка возрастает, я отсоединяю Доступ к чтению через реплики чтения, чтобы нагрузка на основной сервер при записи не замедляла работу всего приложения. Перед репликами размещаются кэши, чтобы не каждый запрос обращался к базе данных. Я разделяю большие, исторически растущие таблицы с помощью разбиения по дате или хэшу, что делает обслуживание и сканирование более предсказуемыми. Если один узел достигает своих пределов, я рассматриваю возможность разделения на специализированные домены. По-прежнему важно, чтобы приложение и драйвер справлялись с задержкой репликации и использовали последовательные пути только для критически важных процессов.
Я принимаю во внимание Читать-писать-требования: критические потоки считываются непосредственно с основного сервера, менее чувствительные пути могут считываться с реплики с задержкой. Я постоянно проверяю метрики задержки и автоматически переключаюсь обратно на основной сервер, если лимиты превышены. Я планирую разделы так, чтобы обрезка вступила в силу (фильтр по ключу раздела), и избегаю глобального ORDER BY по многим разделам, если нет подходящего индекса.
Конфигурация сервера: нужные параметры
В дополнение к буферному пулу я настраиваю max_connections в соответствии с фактическим параллелизмом, чтобы сервер не управлял слишком большим количеством полуактивных потоков. Я использую thread_cache_size, чтобы избежать дорогостоящего создания новых потоков при частых подключениях. Я увеличиваю tmp_table_size и max_heap_table_size настолько, чтобы временные таблицы редко переключались на носители данных. В системах с большим объемом оперативной памяти я уделяю внимание чистой настройке NUMA и ввода-вывода, чтобы память и SSD обеспечивали запланированную производительность. Я ограничиваю ротацию журналов, чтобы диагностика оставалась без заполнения носителей.
В средах PHP и Node я полагаюсь на Повторное использование соединений и ограниченные пулы работников: Лучше несколько хорошо используемых соединений, чем сотни неиспользуемых. В PHP-FPM я устанавливаю pm.max_children и pm.max_requests, чтобы MySQL не утонул в потоках соединений. Я использую постоянные соединения только в том случае, если они соответствуют нагрузке и не происходит перекоммит - в противном случае короткие, повторно используемые соединения с чистым пулом являются более надежными.
Мониторинг и устранение неполадок: что я проверяю каждый день
Я измеряю непрерывныйЖурнал медленных запросов, схема производительности и переменные состояния показывают мне тенденции еще до того, как пользователи заметят время ожидания. Я использую EXPLAIN ANALYZE, чтобы проверить фактическое время выполнения отдельных операторов и сравнить его с ожидаемым. Такие инструменты, как pt-query-digest или mysqltuner.pl, предоставляют информацию об индексах, размерах буферов и неисправных шаблонах. Я проверяю фрагментацию на еженедельной основе и выполняю целевой OPTIMIZE TABLE там, где это дает ощутимую разницу. После внесения изменений я всегда тестирую дампы производственных данных, чтобы оптимизация работала и при реальной кардинальности.
К Основные показатели Для меня это: частота попадания в буферный пул, количество просмотренных строк по сравнению с количеством отправленных строк, handler_read_rnd_next (доля полных сканирований), временные таблицы на диске, threads_running, время блокировки строк InnoDB, table_open_cache и open_files_limit. В случае возникновения проблем я специально активирую потребителей схемы производительности и использую представления схемы sys, чтобы разделить "горячие точки" на уровни запросов и ожиданий.
Статистика оптимизатора и стабильность плана
Я держу Статистика Current: ANALYZE TABLE для соответствующих изменений данных, а в тех случаях, когда кардинальность трудно оценить, я использую гистограммы (MySQL 8), чтобы оптимизатор правильно оценивал селективные предикаты. В случае сильно колеблющихся планов я проверяю, нет ли в них связующего поля, и стабилизирую их с помощью скорректированных индексов или слегка переформулированных запросов. Я полностью избегаю жестких подсказок оптимизатора и использую их, если вообще использую, то только в очень ограниченном объеме после измерений.
Изменения в работе: онлайн DDL и схемы миграции
Я планирую изменения схемы с помощью ALGORITHM=INSTANT/INPLACE и LOCK=NONE, где это возможно. Это позволяет вводить новые столбцы или индексы во время работы без прерывания чтения/записи. Для дорогостоящих перестроек я работаю с теневыми таблицами и переключаемыми представлениями или флагами возможностей. Я предпочитаю создавать индексы вне основных окон загрузки и отслеживаю задержки ввода-вывода и репликации, чтобы не отставать от реплик при чтении.
Массовые операции и обслуживание данных
Для Массовые вставки Я использую многострочные INSERT в контролируемых партиях, пропускаю autocommit и делаю транзакции небольшими. Если разрешено, LOAD DATA INFILE значительно ускоряет работу; в противном случае я работаю с подготовленными операторами и разумными размерами пакетов. При выполнении больших обновлений я действую итеративно (циклы LIMIT с устойчивой сортировкой), чтобы сохранить короткие блокировки и избежать переполнения буферного пула. Я планирую задания по обслуживанию (архивирование, удаление старых данных) с тщательной логикой дросселирования, чтобы не замедлять продуктивную нагрузку.
Критические модели и быстрые контрмеры
Когда я Пиковая нагрузка Я ограничиваю дорогие страницы с помощью OFFSET и переключаюсь на пагинацию по клавишам, что приносит немедленное облегчение. Если на частых фильтрах нет индексов, даже хорошо настроенный составной индекс дает двузначный процентный прирост. В случае длинных блокировок я разрезаю самые крупные транзакции на более мелкие блоки, что быстро сокращает очереди. Я тестирую запросы перед обновлением плагинов в WordPress, поскольку новые функции часто вводят дополнительные метафильтры. Для измерения я устанавливаю Timing, Rows Examined и Rows Sent на уровне запросов, чтобы объективно подтвердить прогресс.
Краткое резюме
С четким Запросы, Я стабильно повышаю производительность базы данных с помощью правильных индексов и бережной блокировки. Я начинаю с проекции и фильтрации, измеряю с помощью EXPLAIN ANALYZE, а затем корректирую схему и индексы. Я рано запускаю кэши, включаю репликацию при увеличении числа обращений к чтению, а разбиение на разделы стабилизирует очень большие таблицы. Я устанавливаю такие параметры, как innodb_buffer_pool_size, tmp_table_size и max_connections, основываясь на данных, а не на интуиции. Если вы будете последовательно измерять, вносить целенаправленные изменения и снова измерять, вы добьетесь короткого времени отклика и стабильного пользовательского опыта в веб-хостинге.
