Описание
Книга посвящена практическим аспектам работы с MySQL. Рассмотрены приёмы сегментирования баз данных, репликации, шардирования. Уделено внимание упорядочиванию транзакций, резервному копированию и бесшовному взаимодействию между предприятием и облаком, что способствует сохранению данных и их эксплуатационной надёжности. Также исследованы разнообразные аналитические и мониторинговые инструменты и предложены проверенные методы, актуальные при развитии и долгосрочной поддержке MySQL и других РСУБД.
Для администраторов баз данных и специалистов по серверной разработке
Примеры из книги можно скачать по ссылке https://github.com/efficient-mysql-performance
Птица на обложке данной книги — хохлатый барбет (Trachyphonus vaillantii), получивший прозвище «фруктовый салат» за свое красочное оперение и фруктовую диету. В основном обитающие на юге Африки, они обычно встречаются в лесных массивах, пригородных садах и огородах, а также вдоль русел рек и не мигрируют.
Секреты и приёмы
СУБД MySQL в последние годы настолько усложнилась, что книги о ней попадаются то элементарные, то донельзя продвинутые. Но именно производительность MySQL – аспект наиболее важный на практике, часто обходят вниманием, поскольку очень сложно его раскрыть, не вдаваясь в детали внутреннего устройства MySQL. Перед вами книга, в которой эта проблема, наконец, решена. Анализируя скорость выполнения запросов, индексирование самых распространённых инструкций, блокировку рядов и паттерны доступа, эта книга служит путеводителем по важнейшим метрикам MySQL и позволяет выжать максимум из стационарных и облачных систем.
В этой книге:
- Начинать работу с реляционной СУБД MySQL и управлять данными
- Подробный разбор запросов MySQL и скорости их выполнения
- Агрегация и анализ метрик, формирование отчётов по ним
- Изучение транзакций и блокировок
- Масштабирование, шардирование, репликация MySQL
- Переход в облако
Дэниэл проделал отличную работу – эта книга поможет вам максимально быстро достичь цели, не петляя и не гадая. Особенно хорошо и доходчиво рассмотрена блокировка строк в InnoDB.
Вадим Ткаченко, совтор книги «MySQL по максимуму»
Предисловие…………………………………………………………………………………………… 11
Условные обозначения, используемые в этой книге………………………………………………………………. 12
Благодарности…………………………………………………………………………………………………………………………… 12
Глава 1. Время отклика на запрос………………………………………………………….. 13
Правдивая история ложной производительности…………………………………………………………………… 14
Краеугольный камень……………………………………………………………………………………………………………….. 14
Отчет о запросе………………………………………………………………………………………………………………………….. 15
Источники……………………………………………………………………………………………………………………….. 16
Агрегация………………………………………………………………………………………………………………………… 18
Отчетность………………………………………………………………………………………………………………………. 20
Профиль запроса……………………………………………………………………………………………………. 21
Отчет о запросе………………………………………………………………………………………………………. 23
Анализ запросов………………………………………………………………………………………………………………………… 23
Метрики запросов…………………………………………………………………………………………………………… 24
Время запроса………………………………………………………………………………………………………… 24
Время блокировки………………………………………………………………………………………………….. 26
Проверенные строки………………………………………………………………………………………………. 30
Отправленные строки…………………………………………………………………………………………….. 31
Затронутые строки…………………………………………………………………………………………………. 32
Полное сканирование таблицы…………………………………………………………………………….. 33
Полное сканирование присоединенной таблицы……………………………………………….. 33
Число временных таблиц на диске……………………………………………………………………….. 35
Количество запросов……………………………………………………………………………………………… 36
Метаданные и приложение……………………………………………………………………………………………. 36
Относительные значения……………………………………………………………………………………………….. 37
Среднее значение, процентиль и максимум…………………………………………………………………. 37
Улучшение времени отклика на запрос…………………………………………………………………………………… 40
Прямая оптимизация запросов………………………………………………………………………………………. 40
Косвенная оптимизация запросов…………………………………………………………………………………. 41
Когда оптимизировать запросы……………………………………………………………………………………………….. 42
Когда производительность влияет на клиентов…………………………………………………………… 42
До и после изменений кода……………………………………………………………………………………………. 42
Раз в месяц……………………………………………………………………………………………………………………….. 43
MySQL: быстрее………………………………………………………………………………………………………………………… 43
Итоги…………………………………………………………………………………………………………………………………………… 45
Практика: определение медленных запросов…………………………………………………………………………. 46
Глава 2. Индексы и индексация…………………………………………………………….. 49
Отвлекающие маневры в теме производительности………………………………………………………………. 51
Более быстрое и мощное оборудование!……………………………………………………………………… 51
Тонкая настройка (тюнинг) MySQL……………………………………………………………………………… 52
Индексы MySQL: наглядное введение……………………………………………………………………………………… 54
Таблицы InnoDB являются индексами…………………………………………………………………………. 55
Методы доступа к таблице……………………………………………………………………………………………. 59
Поиск по индексу……………………………………………………………………………………………………. 59
Сканирование индекса…………………………………………………………………………………………… 60
Сканирование таблицы…………………………………………………………………………………………. 61
Требование крайнего левого префикса………………………………………………………………………… 63
EXPLAIN: план выполнения запроса…………………………………………………………………………….. 65
WHERE…………………………………………………………………………………………………………………………….. 67
GROUP BY………………………………………………………………………………………………………………………… 73
ORDER BY………………………………………………………………………………………………………………………… 78
Покрывающие индексы………………………………………………………………………………………………….. 83
Соединение таблиц (join tables)…………………………………………………………………………………….. 84
Индексация: думать, как MySQL……………………………………………………………………………………………… 92
Узнайте запрос……………………………………………………………………………………………………………….. 92
Поймите с помощью EXPLAIN……………………………………………………………………………………….. 93
Оптимизируйте запрос…………………………………………………………………………………………………… 94
Развертывание и проверка…………………………………………………………………………………………….. 95
Это был хороший индекс, пока………………………………………………………………………………………………… 96
Изменение запросов……………………………………………………………………………………………………….. 96
Избыточные, повторяющиеся и неиспользуемые………………………………………………………… 97
Экстремальная селективность………………………………………………………………………………………. 98
Это ловушка! (Когда MySQL выбирает другой индекс)…………………………………………….. 99
Алгоритмы соединения таблиц………………………………………………………………………………………………. 100
Итоги…………………………………………………………………………………………………………………………………………. 101
Практика: поиск повторяющихся индексов…………………………………………………………………………… 102
Глава 3. Данные…………………………………………………………………………………… 105
Три секрета………………………………………………………………………………………………………………………………. 106
Индексы могут не помочь…………………………………………………………………………………………….. 106
Индексное сканирование…………………………………………………………………………………….. 107
Обнаружение строк……………………………………………………………………………………………… 107
Соединение таблиц………………………………………………………………………………………………. 108
Размер рабочего набора……………………………………………………………………………………… 109
Чем меньше данных, тем лучше………………………………………………………………………………….. 110
Чем меньше QPS, тем лучше………………………………………………………………………………………… 110
Принцип наименьшего количества данных…………………………………………………………………………… 111
Доступ к данным…………………………………………………………………………………………………………… 111
Возвращайте только необходимые столбцы…………………………………………………….. 112
Уменьшите сложность запросов…………………………………………………………………………. 112
Ограничьте доступ к строкам……………………………………………………………………………… 113
Ограничьте результирующий набор………………………………………………………………….. 116
Избегайте сортировки строк……………………………………………………………………………….. 118
Хранение данных…………………………………………………………………………………………………………. 118
Сохраняются только необходимые строки……………………………………………………….. 119
Используется каждый столбец……………………………………………………………………………. 119
Каждый столбец компактен и практичен…………………………………………………………… 119
Каждое значение компактно и практично…………………………………………………………. 121
Используется каждый вторичный индекс, а не дубликат…………………………………. 126
Хранятся только необходимые строки………………………………………………………………. 128
Удаление или архивирование данных…………………………………………………………………………………… 129
Инструменты…………………………………………………………………………………………………………………. 129
Размер пакета……………………………………………………………………………………………………………….. 129
Конфликт блокировки строк………………………………………………………………………………………… 132
Пространство и время………………………………………………………………………………………………….. 132
Парадокс бинарного лога……………………………………………………………………………………………. 133
Итоги…………………………………………………………………………………………………………………………………………. 134
Практика: аудит доступа к данным запросов………………………………………………………………………. 134
Глава 4. Паттерны доступа…………………………………………………………………… 137
MySQL ничего не делает…………………………………………………………………………………………………………. 138
Производительность дестабилизируется на пределе…………………………………………………………… 139
Toyota и Ferrari…………………………………………………………………………………………………………………………. 144
Паттерны доступа к данным…………………………………………………………………………………………………… 145
Чтение/запись……………………………………………………………………………………………………………….. 147
Пропускная способность……………………………………………………………………………………………… 147
Возраст данных……………………………………………………………………………………………………………. 148
Модель данных…………………………………………………………………………………………………………….. 150
Изоляция транзакций……………………………………………………………………………………………………. 150
Согласованность чтения……………………………………………………………………………………………… 151
Конкурентность……………………………………………………………………………………………………………. 152
Доступ к строкам………………………………………………………………………………………………………….. 153
Результирующий набор……………………………………………………………………………………………….. 153
Изменения в приложении………………………………………………………………………………………………………… 154
Аудит кода…………………………………………………………………………………………………………………….. 154
Разгрузка операций чтения………………………………………………………………………………………….. 156
Реплика MySQL……………………………………………………………………………………………………. 157
Кеш-сервер……………………………………………………………………………………………………………. 158
Очередь для операций записи……………………………………………………………………………………… 159
Секционирование данных……………………………………………………………………………………………. 161
Не используйте MySQL……………………………………………………………………………………………….. 162
Более быстрое и мощное оборудование?……………………………………………………………………………… 162
Итоги…………………………………………………………………………………………………………………………………………. 164
Практика: опишите паттерн доступа…………………………………………………………………………………….. 165
Глава 5. Шардирование……………………………………………………………………….. 167
Почему единая база данных не масштабируется…………………………………………………………………. 168
Рабочая нагрузка приложения……………………………………………………………………………………. 168
Тесты производительности синтетичны…………………………………………………………………….. 170
Операции записи…………………………………………………………………………………………………………… 171
Изменения схемы………………………………………………………………………………………………………….. 173
Операции……………………………………………………………………………………………………………………….. 173
Галька, а не валуны…………………………………………………………………………………………………………………. 174
Шардирование: краткое введение………………………………………………………………………………………….. 175
Ключ шардирования……………………………………………………………………………………………………. 176
Стратегии………………………………………………………………………………………………………………………. 177
Хеш………………………………………………………………………………………………………………………… 178
Диапазон……………………………………………………………………………………………………………….. 179
Поиск……………………………………………………………………………………………………………………… 181
Проблемы………………………………………………………………………………………………………………………. 182
Транзакции……………………………………………………………………………………………………………. 182
Объединения…………………………………………………………………………………………………………. 182
Кросс-шард запросы……………………………………………………………………………………………. 182
Решардирование…………………………………………………………………………………………………… 183
Ребалансировка……………………………………………………………………………………………………. 184
Онлайн-изменения схемы…………………………………………………………………………………….. 185
Альтернативы…………………………………………………………………………………………………………………………… 185
NewSQL………………………………………………………………………………………………………………………….. 185
Промежуточное программное обеспечение………………………………………………………………. 187
Микросервисы………………………………………………………………………………………………………………. 187
Не используйте MySQL……………………………………………………………………………………………….. 188
Итоги…………………………………………………………………………………………………………………………………………. 188
Практика: четырехлетняя пригодность…………………………………………………………………………………. 189
Глава 6. Метрики сервера…………………………………………………………………….. 191
Производительность запросов в сравнении с производительностью сервера…………………… 193
Нормальная и стабильная: лучшая база данных — это скучная база данных………………… 195
Ключевые показатели производительности…………………………………………………………………………. 196
Поле метрик……………………………………………………………………………………………………………………………… 197
Время отклика………………………………………………………………………………………………………………. 198
Скорость (англ. rate)……………………………………………………………………………………………………… 198
Объем использования (англ. utilization)………………………………………………………………………. 199
Ожидание………………………………………………………………………………………………………………………. 200
Ошибки………………………………………………………………………………………………………………………….. 201
Паттерны доступа………………………………………………………………………………………………………… 201
Внутренние метрики…………………………………………………………………………………………………….. 202
Спектры…………………………………………………………………………………………………………………………………….. 202
Время отклика на запрос……………………………………………………………………………………………… 204
Ошибки………………………………………………………………………………………………………………………….. 205
Запросы…………………………………………………………………………………………………………………………. 207
QPS…………………………………………………………………………………………………………………………. 207
TPS…………………………………………………………………………………………………………………………. 208
Чтение/запись……………………………………………………………………………………………………….. 210
Администрирование…………………………………………………………………………………………….. 210
Show………………………………………………………………………………………………………………………. 211
Потоки и соединения……………………………………………………………………………………………………. 211
Временные объекты……………………………………………………………………………………………………… 214
Подготовленные инструкции………………………………………………………………………………………. 215
Плохие SELECT…………………………………………………………………………………………………………….. 216
Пропускная способность сети…………………………………………………………………………………….. 217
Репликация……………………………………………………………………………………………………………………. 217
Объем данных……………………………………………………………………………………………………………….. 218
InnoDB…………………………………………………………………………………………………………………………… 220
Длина списка истории (метрика)………………………………………………………………………… 220
Взаимоблокировка……………………………………………………………………………………………….. 220
Блокировка строк…………………………………………………………………………………………………. 221
Пропускная способность данных……………………………………………………………………….. 222
IOPS……………………………………………………………………………………………………………………….. 223
Эффективность буферного пула…………………………………………………………………………. 225
Сброс страниц………………………………………………………………………………………………………. 227
Лог транзакций…………………………………………………………………………………………………….. 234
Мониторинг и оповещение……………………………………………………………………………………………………… 238
Разрешение……………………………………………………………………………………………………………………. 238
Охота на миражи (пороговые значения)…………………………………………………………………….. 240
Оповещения о пользовательском опыте и объективных ограничениях………………….. 241
Причина и следствие……………………………………………………………………………………………………. 243
Итоги…………………………………………………………………………………………………………………………………………. 245
Практика: анализ ключевых показателей производительности…………………………………………. 246
Практика: проверка оповещений и пороговых значений…………………………………………………….. 247
Глава 7. Запаздывание репликации……………………………………………………… 249
Основа………………………………………………………………………………………………………………………………………. 250
Источник — реплика……………………………………………………………………………………………………. 251
События бинарного лога……………………………………………………………………………………………… 253
Запаздывание репликации…………………………………………………………………………………………… 254
Причины……………………………………………………………………………………………………………………………………. 256
Пропускная способность транзакций…………………………………………………………………………. 256
Восстановление после сбоя…………………………………………………………………………………………. 257
Сетевые проблемы……………………………………………………………………………………………………….. 257
Риск: потеря данных………………………………………………………………………………………………………………… 258
Асинхронная репликация…………………………………………………………………………………………….. 258
Полусинхронная репликация………………………………………………………………………………………. 260
Уменьшение запаздывания: многопоточная репликация…………………………………………………….. 262
Мониторинг……………………………………………………………………………………………………………………………… 266
Время восстановления…………………………………………………………………………………………………………….. 268
Итоги…………………………………………………………………………………………………………………………………………. 270
Практика: мониторинг субсекундного запаздывания………………………………………………………….. 271
Глава 8. Транзакции…………………………………………………………………………….. 275
Блокировка строк…………………………………………………………………………………………………………………….. 276
Блокировки индексной записи и следующего ключа………………………………………………… 278
Блокировка интервала…………………………………………………………………………………………………. 282
Вторичные индексы……………………………………………………………………………………………………… 285
Блокировка намерения вставки…………………………………………………………………………………… 288
MVCC и логи отмены………………………………………………………………………………………………………………. 291
Длина списка истории…………………………………………………………………………………………………………….. 295
Распространенные проблемы…………………………………………………………………………………………………. 297
Большие транзакции (размер транзакции)…………………………………………………………………. 298
Длительные транзакции………………………………………………………………………………………………. 299
Зависшие транзакции…………………………………………………………………………………………………… 300
Заброшенные транзакции……………………………………………………………………………………………. 301
Отчетность……………………………………………………………………………………………………………………………….. 302
Активные транзакции: последние……………………………………………………………………………….. 302
Активные транзакции: итоги……………………………………………………………………………………….. 306
Активная транзакция: история……………………………………………………………………………………. 306
Зафиксированные транзакции: итоги…………………………………………………………………………. 307
Итоги…………………………………………………………………………………………………………………………………………. 309
Практика: оповещение о длине списка истории…………………………………………………………………… 310
Практика: изучение блокировки строк………………………………………………………………………………….. 311
Глава 9. Другие проблемы……………………………………………………………………. 313
Сплит-брейн — величайший риск………………………………………………………………………………………….. 313
Дрейф данных реален, но невидим………………………………………………………………………………………… 314
Не доверяйте ORM…………………………………………………………………………………………………………………… 315
Схемы постоянно меняются……………………………………………………………………………………………………. 316
MySQL расширяет стандартный SQL……………………………………………………………………………………. 316
«Шумные соседи»…………………………………………………………………………………………………………………….. 317
Приложения не завершаются корректно……………………………………………………………………………….. 317
Высокопроизводительная MySQL — это сложно………………………………………………………………… 318
Практика: определение барьеров для сплит-брейна……………………………………………………………. 319
Практика: проверка на дрейф данных…………………………………………………………………………………… 321
Практика: хаос…………………………………………………………………………………………………………………………. 321
Глава 10. MySQL в облаке……………………………………………………………………. 323
Совместимость…………………………………………………………………………………………………………………………. 324
Управление (администрирование базы данных)………………………………………………………………….. 325
Сеть и хранилище… и… задержка…………………………………………………………………………………………… 328
Производительность — это деньги………………………………………………………………………………………… 329
Итоги…………………………………………………………………………………………………………………………………………. 331
Практика: попробуйте MySQL в облаке……………………………………………………………………………….. 332
Предметный указатель…………………………………………………………………………. 333
Об авторе……………………………………………………………………………………………… 336
Об обложке…………………………………………………………………………………………… 336
Дэниэл Нихтер – архитекор баз данных, более 15 лет работает с MySQL. Увлёкся оптимизацией производительности MySQL ещё в 2004 году, работая в дата-центре. Из его заметок сложился блог HackMySQL.com, в котором он делился подробностями устройства MySQL и её инструментария. Затем 8 лет работал в компании Percona, где продолжал разрабатывать инструменты для оптимизации баз данных. Сегодня его инструменты де-факто служат эталоном для компаний во всём мире. Обладатель премии MySQL Community Award, выступает на конференциях, активно участвует в движении Open Source.
-
Настройка производительности MySQL
1188 ₽
891 ₽
