Метка: MySQL

Опубликовано

Вышла книга “MySQL. Практические рецепты”

MySQL. Практические рецепты

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

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

Эффективная работа с базами данных MySQL с помощью SQL-операций, извлечения данных и пользовательских запросов

Книга поможет вам с помощью практических советов изучить процесс создания SQL-запросов от самого начального уровня до экспертного. Вы освоите различные методы извлечения нужных сведений из баз данных MySQL.

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

В книге:

  • Подробно описаны принципы анализа данных с использованием наборов данных MySQL
  • Представлены методики выполнения сложных запросов к базам данных MySQL
  • Рассматриваются такие понятия SQL, как объединение таблиц, фильтрация данных, структурирование запросов и вывод таблиц
  • Показано использование простой математики и логики, множественных фильтров, описаны приемы агрегирования данных
  • Даны примеры грамотного использования табличных выражений, подзапросов и сложных вычислений, правильного применения расширенных методов для проведения анализа скользящих и временных рядов
  • Практические примеры проводят читателя через все этапы освоения SQL — от новичка до мастера

Для кого эта книга

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

Книгу “MySQL. Практические рецепты” можно купить со скидкой в интернет-магазине издательства “БХВ“.

Об авторе. 15

Об авторе рецензии. 16

Благодарности. 17

Введение. 18

Зачем нужно изучать SQL?. 18

Почему MySQL?. 19

Что представляет собой SQL?. 19

Для кого предназначена эта книга. 19

Что даст вам эта книга. 20

Как читать эту книгу. 20

Детализированный запрос. 21

Структура книги. 21

Цветные изображения. 23

Образец данных и образцы запросов. 23

Версии MySQL. 23

Теперь самое время начать работу с запросами к MySQL. 23

ГЛАВА 1. Составление основных запросов SQL.. 25

Предварительные условия. 25

  1. Реляционные базы данных. 26
  2. Запуск MySQL Workbench. 27
  3. Подключение к базе данных. 30
  4. Просмотр таблиц в базе данных. 31
  5. Обнаружение всех представлений в базе данных. 32
  6. Использование клиентской командной строки. 32
  7. Вывод данных в таблицу. 35
  8. Отображение данных из определенного поля. 38
  9. Поиск столбцов в таблице. 39
  10. Отображение данных из определенного набора полей. 40
  11. Использование командной строки для отображения структуры таблицы.. 41
  12. Изменение имени поля в выходных данных. 42
  13. Сортировка данных. 43
  14. Сортировка данных в порядке, обратном алфавитному. 46
  15. Применение нескольких сортировочных критериев. 47
  16. Ограничение количества отображаемых записей. 48

Резюме. 50

Основные знания, приобретенные в этой главе. 50

ГЛАВА 2. Использование нескольких таблиц при составлении
запросов данных. 53

Хранение данных в нескольких таблицах. 53

  1. Соединение таблиц. 54
  2. Соединение таблиц с разными именами полей и связями. 58
  3. Удаление дубликатов из результатов запроса. 59
  4. Соединение нескольких таблиц. 62
  5. Использование псевдонимов таблиц. 65
  6. Соединение нескольких таблиц. 68
  7. Визуализация баз данных. 70
  8. Использование представлений для запоминания сложных объединений таблиц 72

Резюме. 73

Основные знания, приобретенные в этой главе. 74

ГЛАВА 3. Расширенное соединение таблиц. 75

Многообразие типов соединений таблиц. 75

  1. LEFT JOIN и возврат всех данных из таблицы с левым соединением.. 76
  2. RIGHT JOIN и возврат всех данных из таблицы с правым соединением.. 79
  3. Соединения через промежуточные таблицы.. 81
  4. Использование в соединениях нескольких полей. 83
  5. Объединение таблицы с самой собой. 85
  6. Соединение таблиц по диапазонам значений. 88
  7. Перекрестные соединения. 91
  8. Объединение концепций. 92

Резюме. 93

Основные знания, приобретенные в этой главе. 93

ГЛАВА 4. Фильтрация данных. 95

Использование языка SQL для фильтрации данных. 95

  1. Фильтрация данных с помощью соединений. 96
  2. Фильтрация данных с использованием соединения нескольких таблиц. 96
  3. Фильтрация выводимых данных с помощью промежуточных таблиц. 99
  4. Фильтрация текста. 102
  5. Применение нескольких текстовых фильтров. 103
  6. Исключение элемента. 105
  7. Использование нескольких фильтров исключения. 106
  8. Фильтрация чисел, превышающих заданный порог. 108
  9. Фильтрация чисел ниже определенного порога. 109
  10. Фильтрация по значениям до определенного числа включительно. 110
  11. Фильтрация по диапазону значений. 111
  12. Использование булевых фильтров (True или False) 113

Резюме. 115

Основные знания, приобретенные в этой главе. 115

ГЛАВА 5. Применение сложных фильтров при составлении запросов. 117

Комплексные методы фильтрации. 117

  1. Фильтры “или… или…”. 118
  2. Одновременное использование нескольких отдельных критериев. 119
  3. Использование нескольких фильтров и исключение данных. 120
  4. Одновременная фильтрация текста и чисел. 122
  5. Одновременное применение сложных альтернативных фильтров. 123
  6. Поиск с учетом регистра. 126
  7. Отключение чувствительности к регистру в фильтрах. 127
  8. Поиск с использованием подстановочных знаков. 129
  9. Подстановочные знаки для исключения данных. 131
  10. Включение чувствительности к регистру в фильтрах подстановочных знаков 132
  11. Фильтрация данных по определенной части текста. 133
  12. Работа с NULL, или с незаполненными полями. 136
  13. Поиск с помощью регулярных выражений. 138

Резюме. 139

Основные знания, полученные в этой главе. 140

ГЛАВА 6. Выполнение простых расчетов. 141

Выполнение вычислений на языке SQL. 141

  1. Выполнение простых математических действий. 141
  2. Анализ типов данных в представлениях MySQL. 144
  3. Изоляция сегментов формул с использованием математики. 146
  4. Расчет коэффициентов. 147
  5. Предотвращение ошибок деления на ноль. 149
  6. Увеличение значений на определенный процент. 150
  7. Сортировка вывода по результату расчетов. 151
  8. Обработка недостающих данных. 153
  9. Фильтрация вычислений. 155
  10. Сложные вычисляемые фильтры.. 157
  11. Быстрое написание точного SQL-запроса. 160

Резюме. 161

Основные знания, полученные в этой главе. 161

ГЛАВА 7. Объединение результатов. 163

Объединение данных в SQL-запросе. 163

  1. Подсчет итоговых показателей таблицы.. 164
  2. Использование рассчитанных сумм.. 165
  3. Группировка суммарных показателей. 166
  4. Использование нескольких уровней группирования. 167
  5. Расчет средних значений. 169
  6. Подсчет сгруппированных элементов. 170
  7. Подсчет уникальных элементов. 172
  8. Отображение верхнего и нижнего числовых порогов. 173
  9. Групповая фильтрация. 174
  10. Фильтрация суммарных результатов. 175
  11. Выбор данных на основе суммарных результатов и определенных критериев фильтрации 176
  12. Сортировка по суммарным результатам.. 178

Резюме. 179

Основные знания, полученные в этой главе. 180

ГЛАВА 8. Работа с датами в MySQL.. 181

Анализ данных за определенный период времени. 181

  1. Фильтрация записей по дате. 182
  2. Фильтрация данных по диапазону дат. 183
  3. Способ указания дат в запросах MySQL. 185
  4. Расчет количества дней между двумя датами. 187
  5. Объединение данных по диапазону дат. 189
  6. Удаление элемента времени из фильтруемой даты.. 191
  7. Фильтрация по годам.. 193
  8. Фильтрация записей за несколько лет. 194
  9. Фильтрация данных по году и месяцу. 196
  10. Поиск данных по определенному кварталу. 197
  11. Фильтрация данных по дням недели. 199
  12. Поиск записей для определенной недели года. 200
  13. Объединение данных по дням недели определенного года. 201
  14. Сортировка данных по полному названию дня недели. 202
  15. Суммарные итоговые и средние показатели по дням года. 204
  16. Совокупные итоговые и средние показатели по дням месяца. 205
  17. Отображение обобщенных значений за месяц. 207
  18. Отображение суммарных данных за 75 дней до определенной даты.. 208
  19. Отображение данных за предыдущие три месяца. 210
  20. Определение текущей системной даты.. 212

Резюме. 212

Основные знания, полученные в этой главе. 213

ГЛАВА 9. Форматирование текста в результатах запроса. 215

Оптимизация вывода результата SQL-запросов. 215

  1. Добавление текста в выходные данные. 215
  2. Добавление нескольких фрагментов текста к числам.. 217
  3. Объединение столбцов с использованием различных разделителей. 218
  4. Предотвращение появления значения NULL в объединенном выводе. 221
  5. Соединение и группировка. 222
  6. Объединение столбцов. 223
  7. Преобразование текста в верхний регистр. 225
  8. Преобразование текста в нижний регистр. 226
  9. Извлечение нескольких символов из поля. 227
  10. Отображение трех символов справа от текста. 228
  11. Отображение заданного количества символов из определенного места текста 230
  12. Фильтрация записей по части поля. 231
  13. Фильтрация данных по определенным символам в заданной позиции внутри поля 232
  14. Объединение с частью поля. 234

Резюме. 235

Основные знания, полученные в этой главе. 235

ГЛАВА 10. Форматирование чисел и дат. 237

Представление чисел и дат. 237

  1. Отбрасывание десятичных знаков из выходных данных. 238
  2. Округление поля до ближайшего целого числа. 239
  3. Округление значения до ближайшего целого числа. 240
  4. Округление значения в большую или меньшую сторону до ближайшей тысячи 241
  5. Отображение значения в определенном числовом формате. 242
  6. Отображение значения в определенной валюте. 244
  7. Указание формата локали. 246
  8. Вывод даты в определенном формате. 247
  9. Вывод даты в формате ISO Date. 250
  10. Представление времени в определенном формате. 252

Резюме. 254

Основные знания, полученные в этой главе. 254

ГЛАВА 11. Использование базовой логики для улучшения анализа. 257

Применение SQL-логики. 257

  1. Генерирование предупреждения при превышении значения. 257
  2. Сокращение текста и добавление многоточий для обозначения усечения. 259
  3. Разработка сложных расчетных оповещений. 260
  4. Создание ключевых показателей эффективности. 263
  5. Классификация ряда элементов при отсутствии необходимых категорий
    в данных. 265
  6. Создание специальных групп категорий. 268
  7. Использование нескольких специальных категорий. 270
  8. Распределение данных по категориям с использованием нескольких вложенных классификаций 271
  9. Объединение специальных категорий. 273
  10. Размещение значений NULL в начале или конце списка. 276
  11. Классификация данных по произвольным категориям.. 278

Резюме. 280

Основные знания, полученные в этой главе. 280

ГЛАВА 12. Вложенные запросы.. 283

Что такое вложенные запросы?. 283

  1. Добавление сводных полей к подробным наборам данных. 284
  2. Отображение значения в процентах общего числа. 286
  3. Использование вложенного запроса для фильтрации данных. 289
  4. Использование вложенного запроса как компонента вычисления
    для фильтрации данных. 290
  5. Фильтрация объединенного диапазона данных с помощью нескольких вложенных запросов 293
  6. Фильтрация результатов объединения с помощью второго объединения. 295
  7. Вложенные внутренние запросы.. 296
  8. Использование вложенных запросов для исключения данных. 299
  9. Несколько вложенных внутренних запросов. 301
  10. Фильтрация по основным и вложенным запросам.. 304
  11. Применение отдельных фильтров во вложенном и основном запросах. 306

Резюме. 308

Основные знания, полученные в этой главе. 308

ГЛАВА 13. Производные таблицы.. 309

Что представляет собой производная таблица?. 309

  1. Использование производной таблицы для создания промежуточных вычислений 310
  2. Группировка и упорядочивание данных посредством пользовательской классификации 315
  3. Присоединение производных таблиц к другим таблицам.. 317
  4. Использование нескольких результатов из производной таблицы для фильтрации данных 321
  5. Сложные сводные производные таблицы.. 324
  6. Объединение нескольких производных таблиц. 326
  7. Использование нескольких производных таблиц для сложных объединений. 330
  8. Использование производных таблиц для присоединения несвязанных таблиц 333
  9. Сравнение данных за год с помощью производной таблицы.. 336
  10. Синхронизация фильтров между производной таблицей и основным запросом 338

Резюме. 340

Основные знания, полученные в этой главе. 340

ГЛАВА 14. Общие табличные выражения. 341

Оптимизация сложных запросов с помощью общих табличных выражений. 341

  1. Базовые общие табличные выражения. 342
  2. Вычисление с помощью CTE средних значений по нескольким
    величинам.. 345
  3. Повторное использование CTE в запросе. 347
  4. Использование CTE в производной таблице для обеспечения двух
    разных уровней объединения данных. 349
  5. Использование CTE для выделения данных из отдельного набора
    на другом уровне детализации. 352
  6. Использование нескольких общих табличных выражений. 354
  7. Вложенные общие табличные выражения. 357
  8. Использование нескольких общих табличных выражений для сравнения разрозненных наборов данных 359

Резюме. 363

Основные знания, полученные в этой главе. 363

ГЛАВА 15. Коррелированные вложенные запросы.. 365

Зачем использовать коррелированные вложенные запросы?. 365

  1. Простые коррелированные вложенные запросы.. 366
  2. Коррелированные вложенные запросы для отображения процентных
    долей определенного итогового значения. 368
  3. Сравнение наборов данных с помощью коррелированного вложенного запроса 369
  4. Дублирование вывода коррелированного вложенного запроса в результатах выполнения запроса 371
  5. Агрегированные коррелированные вложенные запросы.. 373
  6. Использование коррелированных вложенных запросов для фильтрации данных по совокупному значению.. 375
  7. Использование коррелированных вложенных запросов для обнаружения записей 377
  8. Использование коррелированного вложенного запроса для исключения данных 379
  9. Сложные объединения в коррелированных вложенных запросах. 380
  10. Использование коррелированного вложенного запроса для проверки значений в разных таблицах 383

Резюме. 384

Основные знания, полученные в этой главе. 384

ГЛАВА 16. Манипулирование набором данных. 387

Применение наборов данных для объединения и сопоставления данных. 387

  1. Чтение данных из нескольких одинаково структурированных таблиц
    с помощью оператора UNION.. 388
  2. Поиск и определение одинаковых данных в нескольких таблицах. 390
  3. Выделение общих элементов из нескольких подмножеств данных. 392
  4. Объединение в одном вложенном запросе нескольких одинаковых
    таблиц. 395
  5. Выделение из двух наборов данных неодинаковых записей. 396
  6. Отображение полных записей для несовпадающих данных. 398
  7. Отображение полных записей для одинаковых данных. 401

Резюме. 404

Основные знания, полученные в этой главе. 404

ГЛАВА 17. Использование SQL для более сложных вычислений. 407

Дополнительные методы расчета. 407

  1. Расчет процентного соотношения по каждой записи в наборе данных. 408
  2. Повторное использование нескольких вложенных запросов. 409
  3. Отбрасывание дробной части числа из результатов вычислений. 412
  4. Числовые типы данных. 413
  5. Преобразование типа отформатированных исходных данных в числовой тип, пригодный для использования. 415
  6. Проверка сбоев при удалении символов форматирования. 418
  7. Проверка наличия значений, не являющихся числовыми. 419
  8. Вычисление с помощью оператора MOD остатка от деления. 420
  9. Создание финансовых расчетов. 423
  10. Использование таблицы для получения последовательного списка
    чисел. 426
  11. Формирование случайной выборки из набора данных. 428

Резюме. 430

Основные знания, полученные в этой главе. 430

ГЛАВА 18. Сегментирование и классификация данных. 433

Ранжирование и сегментация данных. 433

  1. Упорядочивание данных по рангам.. 434
  2. Создание нескольких ранжированных групп. 436
  3. Создание нескольких рейтинговых групп и подгрупп. 438
  4. Фильтрация данных по рейтингу элементов. 440
  5. Классификация данных в строгом порядке ранжирования. 442
  6. Разделение данных на децили. 446
  7. Построение значений для перцентиля. 448
  8. Извлечение данных из определенного квинтиля. 450
  9. Возвращение верхнего N процента набора данных. 452
  10. Вычисление общих продаж.. 454
  11. Классификация данных с помощью функции PERCENT_RANK() 456

Резюме. 457

Основные знания, полученные в этой главе. 458

ГЛАВА 19. Скользящий анализ. 459

Промежуточные итоговые и средние значения, промежуточные подсчеты
и сравнительные значения. 459

  1. Добавление текущих итогов. 460
  2. Использование оконных функций в обобщенном запросе. 462
  3. Перезагрузка текущих итогов. 464
  4. Оконные функции во вложенном запросе. 466
  5. Добавление уникальных идентификаторов на лету с помощью
    функции ROW_NUMBER() 469
  6. Отображение записей с отсутствующими данными. 471
  7. Отображение полного диапазона дат и связанных с ними данных. 474
  8. Сравнение данных с данными из предыдущих записей. 477
  9. Сравнение данных по времени с помощью функций FIRST_VALUE()
    и LAST_VALUE() 481
  10. Отображение изменяющихся средних значений по заданному
    количеству записей. 483
  11. Отображение первой и последних четырех продаж по каждому
    клиенту. 486

Резюме. 488

Основные знания, полученные в этой главе. 488

ГЛАВА 20. Анализ данных, изменяющихся с течением времени. 491

Временной анализ. 491

  1. Обобщенные значения за год до настоящего времени. 492
  2. Объединение значений за месяц до настоящего времени. 494
  3. Возврат суммарных значений за квартал и до настоящего времени. 496
  4. Выделение данных за предыдущий месяц. 498
  5. Использование производной таблицы для сравнения данных
    со значениями предыдущего года. 500
  6. Нахождение общей суммы за каждый будний день в течение года. 502
  7. Подсчет количества выходных дней между двумя датами. 507
  8. Совокупные данные за последний день месяца. 509
  9. Обобщенные данные на последнюю пятницу месяца. 512
  10. Анализ временных отрезков в виде лет, месяцев и дней. 514
  11. Выделение временных периодов из элементов даты и времени. 517
  12. Данные списка по времени суток. 519
  13. Объединение данных по часовым диапазонам.. 521
  14. Обобщенные данные с разбивкой по часам по четвертям.. 523

Резюме. 524

Основные знания, полученные в этой главе. 525

ГЛАВА 21. Вывод комплексных данных. 527

Отображение комплексных данных с помощью SQL-запроса. 527

  1. Создание сводной таблицы.. 528
  2. Создание сводной таблицы, отображающей несколько сгруппированных строк 531
  3. Отмена группировки данных. 534
  4. Добавление итогов в сводные запросы.. 537
  5. Создание понятных таблиц, включающих итоговые данные
    и промежуточные итоги. 540
  6. Работа с данными иерархического типа. 542
  7. Создание иерархий с отступом.. 545
  8. Замена в итоговом результате аббревиатур полным текстом.. 546
  9. Замена определенного количества символов другим текстом.. 548
  10. Создание списка из нескольких записей с разделителями-запятыми. 549
  11. Экспорт списков, разделенных запятыми. 552
  12. Экспорт списков с заголовками. 555
  13. Экспорт списков фиксированной ширины.. 558
  14. Удаление лишних пробелов из выходных данных. 560

Резюме. 561

Основные знания, полученные в этой главе. 562

ПРИЛОЖЕНИЯ.. 563

Приложение 1. Установка MySQL.. 565

Установка MySQL. 565

  1. Установка MySQL в операционной системе Windows. 565
  2. Установка MySQL на Macintosh. 573
  3. Установка MySQL в операционной системе Linux. 574

Приложение 2. Установка MySQL Workbench. 576

Установка MySQL Workbench в операционной системе Windows. 576

  1. Установка MySQL под операционную систему Windows. 576
  2. Установка приложения MySQL Workbench на компьютере Macintosh. 581
  3. Запуск MySQL Workbench. 581
  4. Создание соединения с MySQL. 582

Приложение 3. Настройка базы данных с примерами. 584

  1. Загрузка примеров под операционную систему Windows. 584
  2. Загрузка выборочных данных в базу данных PrestigeCars. 584
  3. Открытие примеров запросов. 585

 

Предметный указатель. 587

Adam Aspin

Адам Аспин (Adam Aspin) — аналитик данных с более чем 25-летним стажем. Работал в таких областях, как финансы, фармацевтика, коммунальные услуги, банковское дело, телекоммуникации и розничная торговля. Автор нескольких книг по различным аспектам платформы данных Microsoft, а также многочисленных публикаций на порталах SQLServerCentral.com и Simple-Talk. Регулярно выступает на различных мероприятиях, посвященных SQL Server, таких как SQLBits, SQL Saturdays/User groups и других.

Опубликовано

Новая книга: “Изучаем MySQL. 2-е издание”

Изучаем MySQL. 2-е издание

Книга знакомит с MySQL — самой популярной системой управления базами данных с открытым исходным кодом. Изложены основы MySQL: установка, моделирование и конструирование баз данных, команды SQL и создание новой базы данных. Рассмотрены практически вопросы работы с MySQL: расширенные запросы, транзакции и замковый механизм, проверка эффективности запросов, управление пользователями и привилегиями, использование файлов опций, резервное копирование и восстановление, конфигурирование и настройка сервера. Отдельное внимание уделено мониторингу серверов MySQL, асинхронной и синхронной репликации, кластерным решениям, работе в облаке, балансировке нагрузки и другим продвинутым методам и инструментам.

Электронный архив на сайте издательства содержит цветные иллюстрации к книге.

Для программистов баз данных

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

Книга дает исчерпывающий обзор организации и конструирования эффективной базы данных с помощью MySQL. Рассматривается последняя версия MySQL, включая ее наиболее важные аспекты. Независимо от того, над чем вы работаете, будь то развертывание рабочей среды, устранение неполадок или аварийное восстановление, настоящее практическое руководство предоставляет полезные рекомендации и описание инструментов, необходимые для полного использования преимуществ этой мощной реляционной СУБД.

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

Вы узнаете, как:

  • Начинать работу с реляционной СУБД MySQL и управлять данными
  • Развертывать базы данных MySQL на “голом железе”, на виртуальных машинах и в облаке
  • Конструировать инфраструктуры базы данных
  • Кодировать высокоэффективные запросы
  • Обеспечивать мониторинг и устранять неполадки баз данных MySQL
  • Выполнять эффективные операции резервного копирования и восстановления
  • Оптимизировать издержки баз данных в облаке
  • Понимать концепции баз данных, в особенности те, которые относятся к MySQL

Книгу “Изучаем MySQL. 2-е издание” можно купить со скидкой в интернет-магазине издательства “БХВ“.

Отзывы………………………………………………………………………………………………….. 13

Об авторах……………………………………………………………………………………………… 14

Благодарности……………………………………………………………………………………….. 15

Предисловие…………………………………………………………………………………………… 17

Для кого эта книга предназначена…………………………………………………………………………………………… 17

Как эта книга организована……………………………………………………………………………………………………… 18

Начало работы с MySQL………………………………………………………………………………………………….. 18

Использование MySQL……………………………………………………………………………………………………… 18

MySQL в производстве……………………………………………………………………………………………………… 19

Прочие темы………………………………………………………………………………………………………………………. 20

Исходный код и иллюстрации………………………………………………………………………………………………….. 20

Условные обозначения в книге…………………………………………………………………………………………………. 20

Глава 1. Установка MySQL……………………………………………………………………. 23

Развилки MySQL………………………………………………………………………………………………………………………… 24

Редакция MySQL для сообщества……………………………………………………………………………………. 24

Сервер Percona для MySQL………………………………………………………………………………………………. 24

Сервер MariaDB…………………………………………………………………………………………………………………. 24

Редакция MySQL для предприятий………………………………………………………………………………….. 25

Варианты инсталляции и платформы……………………………………………………………………………………… 25

  1. Скачать дистрибутив, который вы хотите инсталлировать……………………………………… 26
  2. Установить дистрибутив………………………………………………………………………………………………. 26
  3. Выполнить все необходимые постинсталляционные настройки……………………………… 27
  4. Выполнить контрольные нагрузочные тесты……………………………………………………………… 27

Установка MySQL в Linux………………………………………………………………………………………………………… 27

Инсталлирование MySQL в CentOS 7……………………………………………………………………………… 27

Инсталлирование MySQL 8.0………………………………………………………………………………….. 28

Инсталлирование MariaDB 10.5……………………………………………………………………………… 31

Инсталлирование сервера Percona Server 8.0…………………………………………………………. 32

Инсталлирование MySQL 5.7………………………………………………………………………………….. 33

Инсталлирование Percona Server 5.7……………………………………………………………………….. 34

Инсталлирование MySQL в CentOS 8……………………………………………………………………………… 35

Инсталлирование MySQL 8.0………………………………………………………………………………….. 35

Инсталлирование сервера Percona Server 8.0…………………………………………………………. 36

Инсталлирование MySQL 5.7………………………………………………………………………………….. 39

Инсталлирование MySQL в Ubuntu 20.04 LTS (Focal Fossa)…………………………………………. 41

Инсталлирование MySQL 8.0………………………………………………………………………………….. 41

Инсталлирование сервера Percona Server 8……………………………………………………………. 44

Инсталлирование MariaDB 10.5……………………………………………………………………………… 45

Инсталлирование MySQL 5.7………………………………………………………………………………….. 46

Установка MySQL в macOS Big Sur………………………………………………………………………………………….. 49

Инсталлирование MySQL 8……………………………………………………………………………………………… 49

Установка MySQL в Windows 10………………………………………………………………………………………………. 55

Содержимое каталога MySQL………………………………………………………………………………………………….. 62

Файлы MySQL 5.7, используемые по умолчанию………………………………………………………….. 63

Файлы MySQL 8.0, используемые по умолчанию………………………………………………………….. 66

Использование интерфейса командной строки………………………………………………………………………. 66

Использование Docker……………………………………………………………………………………………………………….. 67

Установка Docker………………………………………………………………………………………………………………. 67

Инсталлирование Docker в CentOS 7………………………………………………………………………. 68

Инсталлирование Docker в Ubuntu 20.04 (Focal Fossa)…………………………………………. 68

Развертывание контейнера MySQL…………………………………………………………………………. 69

Развертывание контейнеров MariaDB и Percona Server…………………………………………. 71

Использование симулированных производственных сред……………………………………………………. 72

Инсталлирование DBdeployer………………………………………………………………………………………….. 73

Использование DBdeployer……………………………………………………………………………………………….. 73

Модернизация сервера MySQL………………………………………………………………………………………………… 77

Глава 2. Моделирование и конструирование баз данных………………………. 81

Как не надо разрабатывать базу данных……………………………………………………………………………….. 81

Процесс конструирования базы данных…………………………………………………………………………………. 83

Модель взаимосвязей сущностей……………………………………………………………………………………………… 84

Представление сущностей………………………………………………………………………………………………… 84

Представление взаимосвязей……………………………………………………………………………………………. 87

Частичное и полное участие…………………………………………………………………………………………….. 89

Сущность или атрибут?……………………………………………………………………………………………………. 89

Сущность или взаимосвязь?……………………………………………………………………………………………… 91

Промежуточные сущности……………………………………………………………………………………………….. 91

Слабые и сильные сущности……………………………………………………………………………………………. 92

Нормализация базы данных…………………………………………………………………………………………………….. 94

Нормализация таблицы образцов……………………………………………………………………………………………. 96

Первая нормальная форма: никаких повторяющихся групп…………………………………………. 96

Вторая нормальная форма: устранить избыточные данные………………………………………… 97

Третья нормальная форма: исключить данные, не зависящие от ключа……………………… 97

Примеры моделирования взаимосвязей сущностей……………………………………………………………….. 98

Использование модели взаимосвязей сущностей…………………………………………………………………. 102

Соотнесение сущностей и взаимосвязей с таблицами базы данных………………………….. 103

Соотнести сущности с таблицами базы данных…………………………………………………. 104

Соотнесите взаимосвязи с таблицами базы данных……………………………………………. 104

Создание модели взаимосвязей сущностей банковской базы данных……………………….. 104

Взаимосвязь “многие ко многим” (N:M)…………………………………………………………………. 105

Взаимосвязь “один ко многим” (1:N)……………………………………………………………………… 105

Конвертирование EER в базу данных MySQL с помощью
инструмента Workbench…………………………………………………………………………………………………. 106

Глава 3. Базовый SQL………………………………………………………………………….. 111

Использование базы данных sakila……………………………………………………………………………………….. 112

Инструкция SELECT и базовые приемы выполнения запросов…………………………………………… 114

Однотабличные инструкции SELECT……………………………………………………………………………. 115

Выбор столбцов………………………………………………………………………………………………………………. 116

Отбор строк с помощью компонента WHERE……………………………………………………………….. 118

Основы WHERE……………………………………………………………………………………………………….. 118

Комбинирование условий с AND, OR, NOT и XOR……………………………………………….. 122

Компонент ORDER BY……………………………………………………………………………………………………… 127

Компонент LIMIT……………………………………………………………………………………………………………… 129

Соединение двух таблиц………………………………………………………………………………………… 131

Инструкция INSERT…………………………………………………………………………………………………………………. 133

Основы INSERT………………………………………………………………………………………………………………… 133

Альтернативные синтаксические конструкции…………………………………………………………….. 136

Инструкция DELETE………………………………………………………………………………………………………………… 139

Основы DELETE……………………………………………………………………………………………………………….. 139

Использование WHERE, ORDER BY и LIMIT………………………………………………………………….. 140

Удаление всех строк с помощью TRUNCATE……………………………………………………………….. 141

Инструкция UPDATE……………………………………………………………………………………………………………….. 142

Примеры…………………………………………………………………………………………………………………………… 142

Использование WHERE, ORDER BY и LIMIT………………………………………………………………….. 143

Разведывательный анализ баз данных и таблиц с помощью SHOW и mysqlshow……………… 144

Глава 4. Работа со структурами базы данных………………………………………. 149

Создание и использование баз данных…………………………………………………………………………………. 149

Создание таблиц……………………………………………………………………………………………………………………… 152

Основы……………………………………………………………………………………………………………………………… 152

Порядок расстановки и наборы символов…………………………………………………………………….. 155

Другие функциональные возможности………………………………………………………………………….. 158

Типы столбцов…………………………………………………………………………………………………………………. 160

Целочисленные типы……………………………………………………………………………………………… 161

Типы с фиксированной точкой………………………………………………………………………………. 163

Типы с плавающей точкой…………………………………………………………………………………….. 164

Строковые типы………………………………………………………………………………………………………. 167

Типы даты и времени……………………………………………………………………………………………… 177

Другие типы…………………………………………………………………………………………………………….. 183

Ключи и индексы…………………………………………………………………………………………………………….. 183

Функциональность AUTO_INCREMENT………………………………………………………………………… 190

Изменение структур…………………………………………………………………………………………………………………. 193

Добавление, удаление и изменение столбцов………………………………………………………………. 193

Добавление, удаление и изменение индексов……………………………………………………………….. 197

Переименование таблиц и изменение других структур……………………………………………….. 199

Удаление структур………………………………………………………………………………………………………………….. 200

Удаление баз данных……………………………………………………………………………………………………… 200

Удаление таблиц……………………………………………………………………………………………………………… 201

Глава 5. Расширенные запросы……………………………………………………………. 203

Псевдонимы……………………………………………………………………………………………………………………………… 203

Псевдонимы столбцов…………………………………………………………………………………………………….. 204

Псевдонимы таблиц………………………………………………………………………………………………………… 206

Агрегирование данных……………………………………………………………………………………………………………. 209

Компонент DISTINCT………………………………………………………………………………………………………. 209

Компонент GROUP BY…………………………………………………………………………………………………….. 211

Агрегатные функции……………………………………………………………………………………………….. 218

Компонент HAVING…………………………………………………………………………………………………………. 219

Расширенные соединения……………………………………………………………………………………………………….. 221

Внутреннее соединение………………………………………………………………………………………………….. 221

Объединение…………………………………………………………………………………………………………………….. 224

Левое и правое соединения…………………………………………………………………………………………….. 231

Естественное соединение……………………………………………………………………………………………….. 235

Постоянные выражения в соединениях…………………………………………………………………………. 237

Вложенные запросы………………………………………………………………………………………………………………… 239

Основы вложенных запросов…………………………………………………………………………………………. 240

Компоненты ANY, SOME, ALL, IN и NOT IN…………………………………………………………………… 243

Использование ANY и IN…………………………………………………………………………………………. 244

Использование ALL…………………………………………………………………………………………………. 247

Написание строковых подзапросов……………………………………………………………………… 249

Компоненты EXISTS и NOT EXISTS………………………………………………………………………………… 250

Основы EXISTS и NOT EXISTS………………………………………………………………………………… 251

Коррелированные подзапросы……………………………………………………………………………… 252

Вложенные запросы в компоненте FROM…………………………………………………………………….. 256

Вложенные запросы в соединениях………………………………………………………………………………. 258

Пользовательские переменные……………………………………………………………………………………………….. 260

Глава 6. Транзакции и замковый механизм…………………………………………. 265

Уровни изоляции……………………………………………………………………………………………………………………… 266

REPEATABLE READ…………………………………………………………………………………………………………. 268

READ COMMITTED………………………………………………………………………………………………………….. 269

READ UNCOMMITTED…………………………………………………………………………………………………….. 270

SERIALIZABLE………………………………………………………………………………………………………………….. 271

Применение замков………………………………………………………………………………………………………………….. 274

Замки на основе метаданных…………………………………………………………………………………………. 275

Строковые замки……………………………………………………………………………………………………………… 280

Тупики………………………………………………………………………………………………………………………………. 283

Параметры MySQL, связанные с изоляцией и замками……………………………………………………….. 285

Глава 7. Достижение большего с MySQL……………………………………………… 287

Вставка данных с помощью запросов…………………………………………………………………………………… 287

Загрузка данных из файлов c разделителями-запятыми……………………………………………………… 293

Выгрузка данных в файлы с разделителями-запятыми……………………………………………………….. 301

Создание таблиц с помощью запросов………………………………………………………………………………….. 303

Выполнение многотабличных обновлений и удалений………………………………………………………. 308

Удаление………………………………………………………………………………………………………………………….. 308

Обновления………………………………………………………………………………………………………………………. 313

Замена данных…………………………………………………………………………………………………………………………. 314

Инструкция EXPLAIN………………………………………………………………………………………………………………. 318

Альтернативные механизмы хранения данных……………………………………………………………………. 324

InnoDB……………………………………………………………………………………………………………………………… 326

MyISAM и Aria…………………………………………………………………………………………………………………. 328

MyRocks и TokuDB…………………………………………………………………………………………………………. 329

Другие табличные типы………………………………………………………………………………………………….. 331

Глава 8. Управление пользователями и привилегиями………………………… 333

Пользователи и привилегии……………………………………………………………………………………………………. 333

Корневой пользователь…………………………………………………………………………………………………………… 335

Создание и использование новых пользователей………………………………………………………………… 336

Таблицы привилегий……………………………………………………………………………………………………………….. 343

Команды управления пользователями и журналирование…………………………………………………. 345

Модифицирование и удаление пользователей……………………………………………………………………… 347

Модифицирование пользователя…………………………………………………………………………………… 347

Удаление пользователя…………………………………………………………………………………………………… 351

Привилегии……………………………………………………………………………………………………………………………….. 355

Статические и динамические привилегии…………………………………………………………………….. 357

SUPER-привилегия…………………………………………………………………………………………………………… 358

Команды управления привилегиями……………………………………………………………………………… 359

GRANT………………………………………………………………………………………………………………………. 359

REVOKE……………………………………………………………………………………………………………………. 361

Проверка привилегий………………………………………………………………………………………………………. 362

Привилегия GRANT OPTION……………………………………………………………………………………………. 365

Роли…………………………………………………………………………………………………………………………………………… 368

Изменение пароля пользователя root и небезопасный запуск…………………………………………….. 375

Несколько идей в отношении безопасной настройки…………………………………………………………… 376

Глава 9. Использование файлов опций………………………………………………… 379

Структура файла опций………………………………………………………………………………………………………….. 379

Диапазон опций……………………………………………………………………………………………………………………….. 384

Порядок поиска файлов опций……………………………………………………………………………………………….. 387

Специальные файлы опций…………………………………………………………………………………………………….. 388

Конфигурационный файл путей для входа…………………………………………………………………… 388

Конфигурационный файл хранимых на диске системных переменных…………………….. 391

Определение действующих опций…………………………………………………………………………………………. 393

Глава 10. Резервное копирование и восстановление…………………………….. 399

Физические и логические резервные копии…………………………………………………………………………… 399

Логические резервные копии………………………………………………………………………………………….. 400

Физические резервные копии………………………………………………………………………………………….. 402

Обзор логических и физических резервных копий……………………………………………………….. 404

Репликация как инструмент резервного копирования………………………………………………………….. 405

Отказ инфраструктуры……………………………………………………………………………………………………. 405

Дефект развертывания…………………………………………………………………………………………………….. 406

Программа mysqldump…………………………………………………………………………………………………………….. 406

Самогенерация репликации с помощью mysqldump…………………………………………………….. 412

Загрузка данных из файла дампа SQL…………………………………………………………………………………… 413

mysqlpump…………………………………………………………………………………………………………………………………. 414

mydumper и myloader……………………………………………………………………………………………………………….. 416

Холодное резервное копирование и моментальные снимки файловой системы……………….. 418

Percona XtraBackup…………………………………………………………………………………………………………………. 419

Резервное копирование и восстановление…………………………………………………………………….. 421

Продвинутые функциональные возможности………………………………………………………………. 424

Инкрементное резервное копирование с помощью XtraBackup…………………………………. 425

Другие средства физического резервного копирования………………………………………………………. 427

MySQL Enterprise Backup……………………………………………………………………………………………….. 428

mariabackup……………………………………………………………………………………………………………………… 428

Восстановление на определенный момент времени…………………………………………………………….. 429

Техническая информация о двоичных журналах………………………………………………………… 430

Поддержка двоичных журналов……………………………………………………………………………………. 431

Идентификация цели восстановления на определенный момент времени…………………. 432

Пример восстановления на определенный момент времени: XtraBackup…………………. 433

Пример восстановления на определенный момент времени: mysqldump…………………… 434

Экспортирование и импортирование табличных пространств InnoDB…………………………….. 435

Технические предпосылки……………………………………………………………………………………………… 436

Экспортирование табличного пространства……………………………………………………………….. 436

Импортирование табличного пространства…………………………………………………………………. 437

Однотабличная реконструкция с помощью XtraBackup……………………………………………… 439

Тестирование и верифицирование резервных копий……………………………………………………………. 440

Вводное пособие по стратегии резервного копирования баз данных………………………………… 442

Глава 11. Конфигурирование и настройка сервера………………………………. 445

Демон сервера MySQL…………………………………………………………………………………………………………….. 445

Переменные сервера MySQL………………………………………………………………………………………………….. 446

Проверка настроек сервера……………………………………………………………………………………………. 446

Лучшие образцы практики……………………………………………………………………………………………… 447

Лучшие образцы практики в работе с операционной системой………………………… 447

Лучшие образцы практики в работе с MySQL…………………………………………………….. 455

Глава 12. Мониторинг серверов MySQL……………………………………………….. 463

Метрики операционной системы……………………………………………………………………………………………. 464

Центральный процессор…………………………………………………………………………………………………. 464

Диск…………………………………………………………………………………………………………………………………… 473

Память………………………………………………………………………………………………………………………………. 478

Сеть…………………………………………………………………………………………………………………………………… 483

Наблюдаемость сервера MySQL……………………………………………………………………………………………. 488

Переменные состояния……………………………………………………………………………………………………. 488

Базовые рецепты мониторинга………………………………………………………………………………………. 491

Доступность сервера MySQL………………………………………………………………………………… 491

Клиентские подключения………………………………………………………………………………………. 492

Счетчики запросов………………………………………………………………………………………………….. 495

Типы и качество запросов……………………………………………………………………………………… 496

Метрики операций ввода-вывода и транзакций InnoDB…………………………………….. 501

Журнал медленных запросов…………………………………………………………………………………………. 506

Отчет о состоянии механизма InnoDB………………………………………………………………………….. 510

Методы исследования…………………………………………………………………………………………………………….. 513

Метод USE……………………………………………………………………………………………………………………….. 513

Метод RED……………………………………………………………………………………………………………………….. 515

Мониторинговые инструменты MySQL………………………………………………………………………………… 516

Инцидентно-диагностический и ручной сбор данных…………………………………………………………. 523

Периодический сбор значений переменных состояния системы………………………………… 523

Использование pt-stalk для сбора метрик MySQL и операционной системы…………….. 525

Расширенный ручной сбор данных……………………………………………………………………………….. 526

Глава 13. Высокая доступность……………………………………………………………. 529

Асинхронная репликация……………………………………………………………………………………………………….. 529

Базовые параметры, устанавливаемые на источнике и реплике………………………………… 532

Создание реплики с помощью Percona XtraBackup……………………………………………………… 533

Создание реплики с помощью плагина клонирования………………………………………………… 535

Создание реплики с помощью mysqldump…………………………………………………………………….. 537

Создание реплики с помощью mydumper и myloader……………………………………………………. 539

Настройка утилит mydumper и myloader………………………………………………………………. 539

Извлечение данных из источника………………………………………………………………………….. 539

Реконструирование данных на сервере-реплике…………………………………………………. 540

Установление репликации……………………………………………………………………………………… 540

Групповая репликация……………………………………………………………………………………………………. 541

Инсталлирование групповой репликации……………………………………………………………. 541

Настройка групповой репликации в MySQL……………………………………………………….. 544

Синхронная репликация…………………………………………………………………………………………………………. 547

Кластер Galera/PXC………………………………………………………………………………………………………… 548

Глава 14. MySQL в облаке……………………………………………………………………. 553

База данных как служба (DBaaS)………………………………………………………………………………………….. 553

Amazon RDS для MySQL/MariaDB………………………………………………………………………………… 554

Google Cloud SQL для MySQL………………………………………………………………………………………… 558

Azure SQL…………………………………………………………………………………………………………………………. 561

Amazon Aurora…………………………………………………………………………………………………………………………. 563

Облачные экземпляры MySQL……………………………………………………………………………………………….. 564

MySQL в Kubernetes…………………………………………………………………………………………………………………. 564

Развертывание кластера Percona XtraDB в Kubernetes………………………………………………… 565

Глава 15. Балансировка нагрузки в MySQL…………………………………………. 571

Балансировка нагрузки с помощью драйверов приложений………………………………………………. 571

Балансировщик нагрузки ProxySQL………………………………………………………………………………………. 572

Инсталлирование и конфигурирование ProxySQL……………………………………………………….. 574

Балансировщик нагрузки HAProxy………………………………………………………………………………………… 578

Инсталлирование и конфигурирование HAProxy………………………………………………………… 579

Маршрутизатор MySQL…………………………………………………………………………………………………………. 584

Глава 16. Прочие темы…………………………………………………………………………. 591

Оболочка MySQL…………………………………………………………………………………………………………………….. 591

Установка оболочки MySQL………………………………………………………………………………………….. 591

Установка оболочки MySQL в Ubuntu 20.04 Focal Fossa…………………………………………….. 591

Установка оболочки MySQL на CentOS 8…………………………………………………………………….. 592

Развертывание симулированного кластера InnoDB с помощью оболочки MySQL…. 593

Утилиты оболочки MySQL…………………………………………………………………………………………….. 597

util.dumpInstance()…………………………………………………………………………………………………… 597

util.dumpSchemas()…………………………………………………………………………………………………… 600

util.dumpTables()……………………………………………………………………………………………………… 600

util.loadDump(url[, опции])…………………………………………………………………………………….. 600

Графики пламени…………………………………………………………………………………………………………………….. 601

Сборка MySQL из исходного кода…………………………………………………………………………………………. 604

Сборка MySQL для Ubuntu Focus Fossa и процессоров ARM…………………………………….. 604

Анализ аварий MySQL……………………………………………………………………………………………………………. 608

Предметный указатель…………………………………………………………………………. 615

Гриппа-Винисиус

Винисиус Гриппа — старший инженер службы поддержки компаний Percona и ACE Oracle. Помог клиентам Percona разобраться в сотнях раз-личных случаев использо-вания MySQL.

 

 

Кузьмичев-Сергей

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

Опубликовано

Новинка: “Настройка производительности MySQL”

Настройка производительности MySQL

Описание

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

Для администраторов баз данных и специалистов по серверной разработке

Примеры из книги можно скачать по ссылке https://github.com/efficient-mysql-performance

Птица на обложке данной книги — хохлатый барбет (Trachyphonus vaillantii), получивший прозвище «фруктовый салат» за свое красочное оперение и фруктовую диету. В основном обитающие на юге Африки, они обычно встречаются в лесных массивах, пригородных садах и огородах, а также вдоль русел рек и не мигрируют.

Секреты и приёмы

СУБД MySQL в последние годы настолько усложнилась, что книги о ней попадаются то элементарные, то донельзя продвинутые. Но именно производительность MySQL – аспект наиболее важный на практике, часто обходят вниманием, поскольку очень сложно его раскрыть, не вдаваясь в детали внутреннего устройства MySQL. Перед вами книга, в которой эта проблема, наконец, решена. Анализируя скорость выполнения запросов, индексирование самых распространённых инструкций, блокировку рядов и паттерны доступа, эта книга служит путеводителем по важнейшим метрикам MySQL и позволяет выжать максимум из стационарных и облачных систем.

В этой книге:

  • Начинать работу с реляционной СУБД MySQL и управлять данными
  • Подробный разбор запросов MySQL и скорости их выполнения
  • Агрегация и анализ метрик, формирование отчётов по ним
  • Изучение транзакций и блокировок
  • Масштабирование, шардирование, репликация MySQL
  • Переход в облако

Дэниэл проделал отличную работу – эта книга поможет вам максимально быстро достичь цели, не петляя и не гадая. Особенно хорошо и доходчиво рассмотрена блокировка строк в InnoDB.
Вадим Ткаченко, совтор книги «MySQL по максимуму»

Книгу “Настройка производительности 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

Daniel Nichter

Дэниэл Нихтер – архитекор баз данных, более 15 лет работает с MySQL. Увлёкся оптимизацией производительности MySQL ещё в 2004 году, работая в дата-центре. Из его заметок сложился блог HackMySQL.com, в котором он делился подробностями устройства MySQL и её инструментария. Затем 8 лет работал в компании Percona, где продолжал разрабатывать инструменты для оптимизации баз данных. Сегодня его инструменты де-факто служат эталоном для компаний во всём мире. Обладатель премии MySQL Community Award, выступает на конференциях, активно участвует в движении Open Source.