MySQL по максимуму. 3-е издание

Предисловие.......................................................................................................................................................16

Введение............................................................................................................................................................17
Структура книги...........................................................................................................................................17
Версии программного обеспечения и их доступность....................................................................20
Условные обозначения..............................................................................................................................21
О примерах кода...........................................................................................................................................22
Благодарности к третьему изданию......................................................................................................23
Благодарности ко второму изданию.....................................................................................................23
Благодарности к первому изданию........................................................................................................26
Глава 1. История и архитектура MySQL ................................................................................................. 28
Логическая архитектура MySQL...........................................................................................................28
Управление соединениями и их безопасность............................................................................30
Оптимизация и выполнение.............................................................................................................30
Управление конкурентным доступом...................................................................................................31
Блокировки чтения/записи...............................................................................................................31
Детальность блокировок....................................................................................................................32
Транзакции.....................................................................................................................................................34
Уровни изолированности...................................................................................................................36
Взаимоблокировки...............................................................................................................................37
Ведение журнала транзакций...........................................................................................................38
Транзакции с MySQL..........................................................................................................................39
Управление конкурентным доступом с помощью многоверсионности...................................41
Подсистемы хранения в MySQL............................................................................................................42
Подсистема хранения InnoDB..........................................................................................................44
Подсистема хранения MyISAM.......................................................................................................47
Другие встроенные подсистемы хранения данных MySQL..................................................49
Подсистемы хранения сторонних разработчиков.....................................................................51
Выбор подходящей подсистемы хранения...................................................................................54
Преобразования таблиц......................................................................................................................58
Хронология MySQL....................................................................................................................................60
Модель развития MySQL.........................................................................................................................64
Итоги главы...................................................................................................................................................65
Глава 2. Эталонное тестирование MySQL ............................................................................................... 66
Зачем нужно эталонное тестирование..................................................................................................66
Стратегии эталонного тестирования.....................................................................................................68
Тактики эталонного тестирования........................................................................................................72
Проектирование и планирование эталонных тестов................................................................73
Должно ли быть длительным эталонное тестирование?.........................................................74
Фиксация производительности и состояния системы............................................................75
Получение точных результатов.......................................................................................................77
Прогон эталонного теста и анализ результатов..........................................................................79
Важность построения графика.........................................................................................................81
Инструменты эталонного тестирования..............................................................................................83
Полностековые инструменты...........................................................................................................83
Инструменты покомпонентного тестирования..........................................................................84
Примеры эталонного тестирования......................................................................................................86
http_load...................................................................................................................................................87
MySQL Benchmark Suite....................................................................................................................88
sysbench.....................................................................................................................................................89
Инструмент dbt2 TPC-C из комплекта Database Test Suite..................................................94
Инструмент Percona’s TPCC-MySQL...........................................................................................97
Итоги главы................................................................................................................................................100
Глава 3. Профилирование производительности сервера ................................................................ 101
Введение в оптимизацию производительности.............................................................................101
Оптимизация с помощью профилирования.............................................................................104
Интерпретация профиля.................................................................................................................106
Профилирование приложения.............................................................................................................108
Профилирование запросов MySQL...................................................................................................113
Профилирование рабочей нагрузки сервера............................................................................113
Профилирование отдельных запросов.......................................................................................118
Использование профиля для оптимизации..............................................................................125
Диагностика редко возникающих проблем.....................................................................................125
Проблемы одиночного запроса или всего сервера?...............................................................126
Фиксация данных диагностики....................................................................................................131
Кейс по диагностике..........................................................................................................................137
Другие инструменты профилирования.............................................................................................146
Таблицы USER_STATISTICS......................................................................................................146
Инструмент strace..............................................................................................................................147
Итоги главы................................................................................................................................................147
Глава 4. Оптимизация схемы и типов данных .................................................................................... 150
Выбор оптимальных типов данных....................................................................................................150
Целые числа.........................................................................................................................................152
Вещественные числа.........................................................................................................................153
Строковые типы.................................................................................................................................154
Типы Date и Time...............................................................................................................................161
Битовые типы данных......................................................................................................................163
Выбор идентификаторов.................................................................................................................165
Специальные типы данных.............................................................................................................168
Подводные камни проектирования схемы в MySQL...................................................................168
Нормализация и денормализация.......................................................................................................170
Достоинства и недостатки нормализованной схемы.............................................................171
Достоинства и недостатки денормализованной схемы........................................................171
Сочетание нормализации и денормализации..........................................................................172
Кэшированные и сводные таблицы....................................................................................................173
Материализованные представления...........................................................................................175
Таблицы счетчиков............................................................................................................................176
Ускорение работы команды ALTER TABLE..................................................................................178
Модификация одного лишь .frm-файла.....................................................................................179
Быстрое построение индексов MyISAM...................................................................................181
Итоги главы................................................................................................................................................182
Глава 5. Повышение производительности с помощью индексирования ................................... 183
Основы индексирования........................................................................................................................184
Типы индексов....................................................................................................................................184
Преимущества индексов..................................................................................................................195
Стратегии индексирования для достижения высокой производительности......................196
Изоляция столбца..............................................................................................................................196
Префиксные индексы и селективность индекса.....................................................................197
Многостолбцовые индексы............................................................................................................200
Выбор правильного порядка столбцов.......................................................................................202
Кластерные индексы.........................................................................................................................205
Покрывающие индексы...................................................................................................................216
Использование просмотра индекса для сортировки.............................................................221
Упакованные (сжатые по префиксу) индексы........................................................................223
Избыточные и дублирующиеся индексы...................................................................................224
Неиспользуемые индексы...............................................................................................................227
Индексы и блокировки....................................................................................................................227
Кейсы по индексированию....................................................................................................................229
Поддержка нескольких видов фильтрации..............................................................................230
Устранение дополнительных условий поиска по диапазону.............................................232
Оптимизация сортировки...............................................................................................................233
Обслуживание индексов и таблиц......................................................................................................234
Поиск и исправление повреждений таблицы..........................................................................235
Обновление статистики индекса..................................................................................................236
Уменьшение фрагментации индекса и данных.......................................................................238
Итоги главы................................................................................................................................................239
Глава 6. Оптимизация производительности запросов ..................................................................... 242
Почему запросы бывают медленными...............................................................................................242
Основная причина замедления: оптимизируйте доступ к данным.........................................243
Не запрашиваете ли вы лишние данные у базы?....................................................................244
Не слишком ли много данных анализирует MySQL?...........................................................245
Способы реструктуризации запросов................................................................................................249
Один сложный или несколько простых запросов?................................................................249
Разбиение запроса на части............................................................................................................250
Декомпозиция соединения.............................................................................................................251
Основные принципы выполнения запросов...................................................................................252
Клиент-серверный протокол MySQL.........................................................................................253
Кэш запросов.......................................................................................................................................256
Процесс оптимизации запроса......................................................................................................257
Подсистема выполнения запросов...............................................................................................271
Возврат результатов клиенту.........................................................................................................272
Ограничения оптимизатора MySQL..................................................................................................272
Коррелированные подзапросы......................................................................................................273
Ограничения UNION........................................................................................................................277
Оптимизация слияния индексов..................................................................................................278
Распространение равенства............................................................................................................278
Параллельное выполнение.............................................................................................................278
Хеш-соединения.................................................................................................................................278
Непоследовательный просмотр индекса...................................................................................279
Функции MIN() и MAX()..............................................................................................................281
SELECT и UPDATE для одной и той же таблицы................................................................282
Подсказки оптимизатору запросов.....................................................................................................282
Оптимизация запросов конкретных типов......................................................................................286
Оптимизация запросов с COUNT()............................................................................................286
Оптимизация запросов с JOIN......................................................................................................289
Оптимизация подзапросов.............................................................................................................289
Оптимизация GROUP BY и DISTINCT...................................................................................290
Оптимизация GROUP BY WITH ROLLUP............................................................................291
Оптимизация LIMIT и OFFSET..................................................................................................292
Оптимизация SQL_CALC_FOUND_ROWS..........................................................................293
Оптимизация UNION.......................................................................................................................294
Статический анализ запросов........................................................................................................295
Переменные, определяемые пользователем.............................................................................295
Кейс................................................................................................................................................................302
Построение таблицы очередей в MySQL..................................................................................302
Вычисление расстояния между двумя точками......................................................................305
Применение пользовательских функций..................................................................................309
Итоги главы................................................................................................................................................310
Глава 7. Дополнительные возможности MySQL ............................................................................... 311
Секционированные таблицы................................................................................................................311
Как работает секционирование.....................................................................................................312
Типы секционирования...................................................................................................................313
Как использовать секционирование............................................................................................315
Что может пойти не так....................................................................................................................316
Оптимизация запросов к секционированным таблицам.....................................................319
Объединенные таблицы...................................................................................................................320
Представления...........................................................................................................................................323
Обновляемые представления.........................................................................................................326
Представления и производительность.......................................................................................326
Ограничения представлений.........................................................................................................328
Ограничения внешнего ключа..............................................................................................................329
Хранение кода внутри MySQL.............................................................................................................330
Хранимые процедуры и функции................................................................................................333
Триггеры................................................................................................................................................334
События.................................................................................................................................................337
Сохранение комментариев в хранимом коде............................................................................338
Курсоры........................................................................................................................................................339
Подготовленные операторы..................................................................................................................340
Оптимизация подготовленных операторов..............................................................................341
SQL-интерфейс для подготовленных операторов..................................................................342
Ограничения подготовленных операторов...............................................................................344
Функции, определяемые пользователем..........................................................................................345
Плагины........................................................................................................................................................346
Кодировка и схемы упорядочения......................................................................................................347
Использование кодировок в MySQL..........................................................................................348
Выбор кодировки и схемы упорядочения.................................................................................351
Как кодировка и схема упорядочения влияют на запросы.................................................352
Полнотекстовый поиск...........................................................................................................................355
Полнотекстовые запросы на естественном языке..................................................................357
Булев полнотекстовый поиск........................................................................................................359
Изменения в полнотекстовом поиске в версии MySQL 5.1................................................360
Компромиссы полнотекстового поиска и обходные пути...................................................360
Настройка и оптимизация полнотекстового поиска.............................................................363
Распределенные транзакции.................................................................................................................364
Внутренние XA-транзакции...........................................................................................................365
Внешние XA-транзакции................................................................................................................366
Кэш запросов MySQL..............................................................................................................................367
Как MySQL проверяет попадание в кэш...................................................................................367
Как кэш использует память............................................................................................................369
Когда полезен кэш запросов...........................................................................................................372
Как настраивать и обслуживать кэш запросов........................................................................375
InnoDB и кэш запросов....................................................................................................................379
Общие оптимизации кэша запросов............................................................................................380
Альтернативы кэшу запросов........................................................................................................381
Итоги главы................................................................................................................................................381
Глава 8. Оптимизация параметров сервера .......................................................................................... 384
Основы конфигурации MySQL...........................................................................................................385
Синтаксис, область видимости и динамичность.....................................................................386
Побочные эффекты установки переменных.............................................................................388
Приступая к работе............................................................................................................................391
Итеративная оптимизация с помощью эталонного тестирования...................................392
Чего делать не следует.............................................................................................................................394
Создание конфигурационного файла MySQL................................................................................396
Настройка использования памяти......................................................................................................401
Сколько памяти может использовать MySQL........................................................................402
Сколько памяти нужно соединению...........................................................................................402
Резервирование памяти для операционной системы............................................................403
Выделение памяти для кэшей........................................................................................................403
Буферный пул InnoDB.....................................................................................................................404
Кэш ключей MyISAM.......................................................................................................................406
Кэш потоков.........................................................................................................................................409
Кэш таблиц...........................................................................................................................................409
Словарь данных InnoDB.................................................................................................................411
Настройка ввода/вывода в MySQL....................................................................................................412
InnoDB...................................................................................................................................................412
MyISAM................................................................................................................................................427
Настройка конкурентного доступа в MySQL.................................................................................429
InnoDB...................................................................................................................................................429
MyISAM................................................................................................................................................431
Настройка с учетом рабочей нагрузки..............................................................................................432
Оптимизация работы с полями типа BLOB и TEXT............................................................433
Оптимизация файловой сортировки..........................................................................................435
Завершение базовой конфигурации...................................................................................................436
Настройки безопасности и готовности к работе............................................................................438
Дополнительные настройки InnoDB.................................................................................................441
Итоги главы................................................................................................................................................444
Глава 9. Оптимизация операционной системы и оборудования .................................................. 445
Что ограничивает производительность MySQL............................................................................445
Как выбирать процессоры для MySQL.............................................................................................446
Что лучше: быстрые процессоры или много процессоров...................................................446
Архитектура ЦП.................................................................................................................................449
Масштабирование на несколько процессоров и ядер...........................................................449
Поиск баланса между памятью и дисками.......................................................................................452
Произвольный и последовательный ввод/вывод...................................................................453
Кэширование, чтение и запись......................................................................................................454
Что такое рабочее множество........................................................................................................455
Определение эффективного соотношения «память — диск».............................................456
Выбор жестких дисков.....................................................................................................................458
Твердотельные хранилища данных....................................................................................................460
Обзор флеш-памяти..........................................................................................................................461
Флеш-технологии..............................................................................................................................462
Эталонное тестирование флеш-памяти.....................................................................................464
Твердотельные диски ......................................................................................................................465
Устройства хранения PCIe.............................................................................................................467
Другие типы твердотельных хранилищ данных.....................................................................468
Когда стоит использовать флеш-устройства............................................................................468
Использование технологии Flashcache......................................................................................470
Оптимизация MySQL для твердотельных хранилищ данных...........................................472
Выбор оборудования для подчиненного сервера...........................................................................476
Оптимизация производительности с помощью RAID................................................................477
Отказ, восстановление и мониторинг RAID............................................................................480
Выбор между аппаратной и программной реализациями RAID.......................................481
Конфигурация RAID и кэширование.........................................................................................483
Сети хранения данных и сетевые системы хранения данных...................................................486
Эталонные тесты SAN-сетей..........................................................................................................487
Использование SAN-сетей через NFS или SMB.....................................................................488
Производительность MySQL в SAN...........................................................................................489
Надо ли использовать SAN.............................................................................................................489
Использование нескольких дисковых томов..................................................................................491
Конфигурация сети..................................................................................................................................493
Выбор операционной системы.............................................................................................................496
Выбор файловой системы......................................................................................................................497
Выбор планировщика дисковых очередей.......................................................................................500
Многопоточность......................................................................................................................................500
Подкачка......................................................................................................................................................501
Состояние операционной системы.....................................................................................................504
Как интерпретировать выдачу vmstat.........................................................................................504
Как интерпретировать выдачу iostat...........................................................................................505
Другие полезные инструменты.....................................................................................................507
Машина с нагруженным процессором........................................................................................508
Машина с нагруженной подсистемой ввода/вывода............................................................509
Машина с интенсивной подкачкой..............................................................................................510
Простаивающая машина..................................................................................................................510
Итоги главы................................................................................................................................................510
Глава 10. Репликация ................................................................................................................................... 513
Обзор репликации....................................................................................................................................513
Проблемы, решаемые репликацией.............................................................................................515
Как работает репликация................................................................................................................516
Настройка репликации...........................................................................................................................517
Создание аккаунтов репликации..................................................................................................518
Конфигурирование главного и подчиненного серверов......................................................519
Запуск подчиненного сервера........................................................................................................520
Инициализация подчиненного сервера на основе существующего.................................523
Рекомендуемая конфигурация репликации.............................................................................525
Взгляд на репликацию изнутри...........................................................................................................527
Покомандная репликация...............................................................................................................527
Построчная репликация..................................................................................................................528
Какая репликация лучше, покомандная или построчная....................................................529
Файлы репликации...........................................................................................................................531
Отправка событий репликации другим подчиненным серверам......................................533
Фильтры репликации.......................................................................................................................534
Топологии репликации...........................................................................................................................536
Один главный сервер с несколькими подчиненными...........................................................537
«Главный сервер — главный сервер» в режиме «активный — активный».....................538
«Главный сервер — главный сервер» в режиме «активный — пассивный»...................540
«Главный сервер — главный сервер с подчиненными»........................................................541
Кольцевая репликация.....................................................................................................................542
Главный сервер, главный сервер-распространитель и подчиненные..............................543
Дерево или пирамида........................................................................................................................545
Пользовательские схемы репликации........................................................................................546
Репликация и планирование производительности.......................................................................552
Почему репликация не помогает масштабированию записи..............................................553
Когда подчиненные серверы начнут отставать........................................................................554
Запланированная недогрузка........................................................................................................555
Администрирование и обслуживание репликации.......................................................................555
Мониторинг репликации.................................................................................................................556
Измерение отставания подчиненного сервера.........................................................................556
Как узнать, согласованы ли подчиненные серверы с главным..........................................558
Восстановление синхронизации подчиненного сервера с главным.................................559
Смена главного сервера...................................................................................................................560
Смена ролей в конфигурации «главный сервер — главный сервер»...............................565
Проблемы репликации и их решение................................................................................................566
Ошибки, вызванные повреждением или утратой данных...................................................566
Использование нетранзакционных таблиц...............................................................................570
Смешивание транзакционных и нетранзакционных таблиц..............................................570
Недетерминированные команды..................................................................................................571
Использование различных подсистем хранения на главном
и подчиненном серверах..................................................................................................................571
Изменение данных на подчиненном сервере...........................................................................572
Неуникальный идентификатор сервера.....................................................................................572
Неопределенный идентификатор сервера................................................................................573
Зависимости от нереплицируемых данных..............................................................................573
Отсутствующие временные таблицы..........................................................................................574
Репликация не всех обновлений...................................................................................................575
Конкуренция, вызванная блокировками при выполнении SELECT
в InnoDB................................................................................................................................................576
Запись на обоих главных серверах в конфигурации «главный — главный»................578
Слишком большое отставание репликации..............................................................................580
Чрезмерно большие пакеты от главного сервера....................................................................584
Ограниченная пропускная способность сети...........................................................................585
Отсутствие места на диске..............................................................................................................585
Ограничения репликации...............................................................................................................585
Насколько быстро работает репликация..........................................................................................586
Расширенные возможности репликации MySQL.........................................................................588
Прочие технологии репликации..........................................................................................................591
Итоги главы................................................................................................................................................593
Глава 11. Масштабирование MySQL ..................................................................................................... 595
Что такое масштабируемость................................................................................................................595
Масштабирование MySQL....................................................................................................................602
Планирование масштабируемости...............................................................................................602
Перед тем как приступать к масштабированию......................................................................603
Вертикальное масштабирование..................................................................................................604
Масштабирование по горизонтали..............................................................................................606
Масштабирование с помощью консолидации.........................................................................625
Масштабирование кластеризацией.............................................................................................626
Обратное масштабирование...........................................................................................................630
Балансирование нагрузки......................................................................................................................633
Прямое подключение........................................................................................................................635
Представляем посредника..............................................................................................................640
Балансирование нагрузки при наличии одного главного и нескольких
подчиненных серверов.....................................................................................................................643
Итоги главы................................................................................................................................................644
Глава 12. Высокая доступность ................................................................................................................ 646
Что такое высокая доступность...........................................................................................................646
Причины простоев....................................................................................................................................648
Достижение высокой доступности.....................................................................................................649
Сокращение среднего времени между отказами.....................................................................649
Сокращение среднего времени восстановления.....................................................................651
Устранение отдельных критических точек......................................................................................652
Разделяемое хранилище данных или реплицированный диск..........................................653
Синхронная репликация MySQL.................................................................................................657
Избыточность на основе репликации.........................................................................................661
Аварийное переключение и восстановление после сбоев...........................................................663
Повышение подчиненного сервера, или перемена ролей....................................................665
Виртуальные IP-адреса и передача IP-адреса..........................................................................665
Решения на основе посредника.....................................................................................................666
Обработка переключения на резерв при отказе на уровне приложения........................668
Итоги главы................................................................................................................................................668
Глава 13. MySQL в облаке ...........................................................................................................................671
Достоинства, недостатки и мифы облака.........................................................................................672
Экономика MySQL в облаке.................................................................................................................674
Масштабирование MySQL и HA в облаке.......................................................................................676
Четыре основных ресурса......................................................................................................................677
Производительность MySQL в облачном хостинге......................................................................678
База данных MySQL как услуга..........................................................................................................683
Amazon RDS.........................................................................................................................................684
Другие решения DBaaS....................................................................................................................685
Итоги главы................................................................................................................................................686
Глава 14. Оптимизация на уровне приложения ................................................................................. 688
Типичные проблемы................................................................................................................................688
Проблемы веб-сервера............................................................................................................................691
Кэширование..............................................................................................................................................695
Кэширование на уровне ниже приложения..............................................................................695
Кэширование на уровне приложения.........................................................................................696
Стратегии управления кэшем........................................................................................................698
Кэширование иерархий объектов.................................................................................................700
Предварительная генерация содержимого...............................................................................701
Кэш как инфраструктурный компонент....................................................................................702
Использование HandlerSocket и доступа к memcached........................................................703
Расширение MySQL................................................................................................................................703
Альтернативы MySQL............................................................................................................................704
Итоги главы................................................................................................................................................705
Глава 15. Резервное копирование и восстановление ........................................................................ 706
Зачем нужно резервное копирование................................................................................................707
Определение требований к восстановлению...................................................................................708
Проектирование резервного копирования в MySQL...................................................................710
Оперативное или автономное резервное копирование........................................................711
Логическое и физическое резервное копирование.................................................................713
Что копировать...................................................................................................................................716
Подсистемы хранения и согласованность.................................................................................718
Репликация..........................................................................................................................................721
Управление и резервное копирование двоичных журналов.....................................................722
Формат двоичного журнала...........................................................................................................722
Безопасное удаление старых двоичных журналов.................................................................723
Резервное копирование данных...........................................................................................................724
Снятие логической резервной копии..........................................................................................724
Снимки файловой системы............................................................................................................727
Восстановление из резервной копии..................................................................................................735
Восстановление из физических файлов.....................................................................................736
Восстановление из логической копии........................................................................................738
Восстановление на конкретный момент времени...................................................................741
Более сложные методы восстановления....................................................................................743
Восстановление InnoDB..................................................................................................................745
Инструменты резервного копирования и восстановления........................................................748
MySQL Enterprise Backup...............................................................................................................748
Percona XtraBackup...........................................................................................................................748
mylvmbackup........................................................................................................................................749
Zmanda Recovery Manager...............................................................................................................749
mydumper..............................................................................................................................................750
mysqldump.............................................................................................................................................750
Скрипты резервного копирования......................................................................................................751
Итоги главы................................................................................................................................................754
Глава 16. Инструменты для пользователей MySQL ......................................................................... 756
Средства организации интерфейса.....................................................................................................756
Утилиты командной строки..................................................................................................................757
Утилиты SQL.............................................................................................................................................758
Инструменты мониторинга...................................................................................................................758
Инструменты мониторинга с открытым исходным кодом..................................................759
Коммерческие системы мониторинга.........................................................................................762
Мониторинг из командной строки с использованием Innotop..........................................764
Итоги главы................................................................................................................................................768
Приложения
Приложение A. Ветки и версии MySQL.................................................................................................770
Percona Server.............................................................................................................................................770
Maria DB......................................................................................................................................................771
Drizzle............................................................................................................................................................772
Другие варианты MySQL.......................................................................................................................774
Итоги.............................................................................................................................................................774
Приложение Б. Состояние сервера MySQL..........................................................................................776
Системные переменные..........................................................................................................................776
Команда SHOW STATUS......................................................................................................................776
Команда SHOW INNODB STATUS..................................................................................................783
Команда SHOW PROCESSLIST........................................................................................................798
Команда SHOW ENGINE MUTEX STATUS.................................................................................799
Состояние репликации...........................................................................................................................800
База данных INFORMATION_SCHEMA.......................................................................................801
Performance Schema..................................................................................................................................803
Итоги.............................................................................................................................................................804
Приложение В. Передача больших файлов...........................................................................................805
Копирование файлов...............................................................................................................................805
Эталонные тесты копирования файлов............................................................................................808
Приложение Г. Команда EXPLAIN..........................................................................................................810
Вызов команды EXPLAIN.....................................................................................................................810
Столбцы результата команды EXPLAIN..........................................................................................813
Вывод плана выполнения в виде дерева...........................................................................................824
Улучшения в версии MySQL 5.6.........................................................................................................825
Приложение Д. Отладка блокировок......................................................................................................826
Ожидание блокировки на уровне сервера........................................................................................826
Ожидание блокировки в InnoDB........................................................................................................831
Приложение Е. Использование Sphinx совместно с MySQL..........................................................836
Типичный поиск с помощью Sphinx..................................................................................................836
Зачем использовать Sphinx....................................................................................................................840
Обзор архитектуры...................................................................................................................................848
Специальные возможности...................................................................................................................851
Примеры практической реализации..................................................................................................857
Итоги.............................................................................................................................................................864