MySQL делает еще одну бомбу, не говори больше!

Привет всем, я Сяо Цай, Сяо Цай, который хочет быть Цай Буцаем в интернет-индустрии. Она может быть мягкой или жесткой, как она мягкая, а белая проституция жесткая!
Черт~ Не забудьте поставить мне тройку после прочтения!

Эта статья в основном знакомитЧто вы должны знать о разработке Mysql и собеседованиях
Эта статья длинная и разделена на две части (коллекционная, пыль не ешь)
При необходимости вы можете обратиться к
Если это поможет, не забудьтекак❥

1. Анализ перехвата запросов

1) Медленный журнал запросов

• Журнал медленных запросов MySQL — это запись журнала, предоставляемая MySQL.Он используется для записи операторов, время ответа которых превышает пороговое значение в MySQL.В частности, SQL, время выполнения которого превышает значение long_query_time, будет записано в журнале медленных запросов.
• В частности, SQL, время выполнения которого превышает значение long_query_time, будет записано в журнал медленных запросов. Значение по умолчанию для long_query_time равно 10, что означает выполнение операторов дольше 10 секунд.
• Мы можем проверить, какой SQL превышает наше максимальное допустимое значение времени.Например, если SQL выполняется более 5 секунд, даже если у нас медленный SQL, мы надеемся собрать SQL, который превышает 5 секунд, который можно объединить с предыдущим объяснить для всестороннего анализа.

начать использовать:
По умолчанию журнал медленных запросов не включен в базе данных MySQL, и нам нужно вручную установить этот параметр.
пройти черезshow variables like '%slow_query_log'Проверьте, включен ли журнал медленных запросов

# 以下方式只对当前数据库有效，MySQL重启后失效
set global slow_query_log = 1;
set global long_query_time = 1.0;
# 主要重新连接或者新开一个会话才能看到修改值
set session long_query_time = 1.0;

Изменения вступят в силу навсегдаmy.cnf

slow_query_log = 1

#指定生成位置，如果没有指定默认生成 host_name-slow.log
slow_query_log_file=/var/lib/mysql/cbuc_slow.log

После открытия, если long_query_time не указано, по умолчанию 10 секунд, тогда, если время выполнения точно равно long_query_tie, оно не будет записано, то есть суждение в исходном коде mysql大于long_query_time,而非大于等于
эксперимент:

# 手动制造一条慢SQL
select sleep(9)

Файл журнала трассировки: tail -50f cbuc_slow.log

show global status like '%Slow_queries%'

【Сводка конфигурации】
существуетmy.iniилиmy.cnfКонфигурация в файле конфигурации

show_query_log = 1;
show_query_log_file = /var/lib/mysql/cbuc_slow.log
long_query_time = 3;
log_output = FILE

日志分析工具mysqldumpslow
Посмотрите справку для mysqldumpslow:

1. s: указывает, каким образом сортировать;
2. в: количество посещений
3. л: время блокировки
4. р: вернуть запись
5. t: количество строк запроса
6. al: среднее время блокировки
7. ar: Среднее количество возвращенных записей
8. at: среднее время запроса
9. t: то есть, сколько частей данных нужно вернуть
10. g: за которым следует обычный шаблон соответствия, без учета регистра

【使用参考】
1,Получите 10 SQL-запросов, которые возвращают наибольшее количество наборов записей

mysqldumpslow -s -t 10 /var/lib/mysql/cbuc_slow.log

2,Получите 10 самых посещаемых SQL-запросов

mysqldumpslow -s -c -t 10 /var/lib/mysql/cbuc_slow.log

3.Получите 10 лучших операторов запроса с левыми соединениями, отсортированными по времени

mysqldumpslow -s -t -t 10 -g "left join"  /var/lib/mysql/cbuc_slow.log

4.Кроме того, рекомендуется использовать эти команды в сочетании с | и другими, иначе экран может лопнуть.

mysqldumpslow -s r -t 10 /var/lib/mysql/cbuc_slow.log | more

2) Показать профиль

• Именно mysql можно использовать для анализа потребления ресурсов при выполнении операторов в текущем сеансе и для измерения настройки SQL.
• По умолчанию параметр отключен и сохраняет последние 15 результатов запуска.

【Этап анализа】

• Проверьте, поддерживает ли он

# 默认是关闭，使用前需要开启
show variables like 'profiling';

• включи

set profiling = 1;

• контрольная работа

# 运行两个SQL查看
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id

Посмотреть Результаты :

• ALL: Показать всю служебную информацию
• BLOCK IO : Отображение служебной информации, связанной с вводом-выводом блока.
• ПЕРЕКЛЮЧАТЕЛИ КОНТЕКСТА: накладные расходы, связанные с переключением контекста
• ЦП: Отображает служебную информацию, связанную с ЦП.
• IPC: отображает служебную информацию об отправке и получении.
• ПАМЯТЬ: Отображает служебную информацию, связанную с памятью.
• ОШИБКИ СТРАНИЦЫ: Отображение служебной информации, связанной с ошибкой страницы.
• SOURCE : Отображение служебной информации, связанной с Source_function, Source_file, Source_line.
• SWAPS: отображает информацию о накладных расходах, связанных с количеством свопов.

3) Глобальный журнал запросов

• конфигурация включена
  в MySQLmy.cnfилиmy.iniустановить в

# 开启
general_log = 1

# 记录日志文件的路径
general_log_file = /path/logfile

# 输出格式
log_output = FILE

• кодирование включено
  Заказ:set global general_log = 1;
  Глобальные журналы могут храниться в файлах журналов или в системных таблицах MySQL. Производительность будет лучше храниться в журнале, хранящемся в таблице:
  set global log_output = 'TABLE'
  После этого написанный вами оператор sql будет записан в таблицу general_log в библиотеке mysql, которую можно просмотреть с помощью следующей команды
  select * from mysql.general_log

2. Механизм блокировки Mysql

1 Обзор

Блокировка — это механизм, с помощью которого компьютер координирует одновременный доступ к ресурсу несколькими процессами или потоками.
В базе данных, в дополнение к традиционным вычислительным ресурсам (таким как ЦП, ОЗУ, ввод-вывод и т. д.), данные также являются ресурсом, совместно используемым многими пользователями. Как обеспечить согласованность и достоверность одновременного доступа к данным — это проблема, которую должны решать все базы данных, и конфликт блокировок также является важным фактором, влияющим на производительность одновременного доступа к базе данных. С этой точки зрения блокировки особенно важны и более сложны для баз данных.

【案例理解】
В настоящее время на складе есть только один товар, но два человека, А и В, хотят разместить заказ одновременно, то удастся ли А разместить заказ или Б сделать заказ.
В настоящее время нам нужно использовать транзакции.Нам нужно сначала взять количество товаров из таблицы инвентаря, затем сгенерировать заказ, сгенерировать платежную информацию после успешной оплаты, а затем обновить количество товаров. В этом процессе нам необходимо использовать блокировки для защиты ограниченных ресурсов и решения проблем изоляции и параллелизма.
【锁的分类】

• Разделение по типу операции с данными (блокировка чтения/записи)

1. Блокировка чтения (общая блокировка):Для одних и тех же данных несколько операций чтения могут выполняться одновременно, не влияя друг на друга.
2. Блокировка записи (эксклюзивная блокировка):Он блокирует другие блокировки записи и чтения, пока текущая операция записи не будет завершена.

• Разделение от детализации операций с данными

1. блокировка стола
2. блокировка строки

2) Трехуровневый замок

【表锁】
Особенности: (частичное чтение)

Он ориентирован на механизм хранения MyISAM с низкими накладными расходами и быстрой блокировкой, отсутствием взаимоблокировок, большой степенью детализации блокировки, высокой вероятностью конфликта блокировок и минимальным параллелизмом.

• Ручной замок:

lock table <table_name1> <read/write>,<table_name2> <read/write>

• Просмотрите замки, добавленные в таблицу:

show open tables;

• снять блокировку стола

unlock tables;

读锁说明：
Создайте два новых сеанса,session1иsession2
В это время таблица mylock выполняется в session1readзаблокировано следующим образом:

1. сеанс1 можетЗапросИнформация таблицы, session2 также можетЗапросзаписи этой таблицы
2. не в сессии1ЗапросДля других таблиц, которые не заблокированы, session2 можетзапросить и обновитьДругие столы, которые не заблокированы
3. session1вставить или обновить锁定的表выдаст ошибку, session2вставить или обновить锁定的表будет ждать.
4. Когда сеанс1 освобождает блокировку, до сеанса2вставить или обновитьИсполнение завершено.

写锁说明：
Также создайте две новые сессии,session1иsession2
В это время таблица mylock выполняется в session1writeзаблокировано следующим образом:

1. session1 в таблице блокировкизапрос + обновление + вставкаВсе операции можно выполнять, session2 имеетЗапросЗаблокировано, ожидание снятия блокировки. Однако, если есть кеш данных до сеанса2, кешированные данные могут быть считаны.Как только данные изменятся, кеш станет недействительным, и операция будет заблокирована.

【小结】:
MyISAM автоматически добавит блокировки чтения ко всем задействованным таблицам перед выполнением оператора запроса и автоматически добавит блокировки записи к задействованным таблицам перед выполнением операций добавления, удаления и модификации.

1,Операция чтения (добавление блокировки чтения) в таблицу MyISAM не будет блокировать запрос на чтение других процессов в ту же таблицу, но заблокирует запрос на запись в ту же таблицу.Только когда блокировка чтения будет снята, операция записи других процессов.
2,Операция записи (добавление блокировки записи) в таблицу MyISAM заблокирует операции чтения и записи других потоков в ту же таблицу, и только когда блокировка записи будет снята, операции чтения и записи других процессов будут выполняться.
Резюме: Блокировка чтения блокирует запись, но не блокирует чтение. Блокировка записи блокирует как чтение, так и запись

【行锁】
Особенности: (частичное чтение)

Он ориентирован на механизм хранения InnoDB, который имеет высокие накладные расходы и медленную блокировку; будут возникать взаимоблокировки; степень детализации блокировки наименьшая, вероятность конфликтов блокировок самая низкая, а параллелизм самый высокий.
Между InnoDB и MyISAM есть два основных различия:

• Поддержка транзакций (ТРАНЗАКЦИЯ)
• блокировка на уровне строки

Бизнес-отчет:
Транзакция — это логическая единица обработки, состоящая из набора операторов SQL.Транзакция имеет следующие четыре атрибута, которые часто называют атрибутом ACID транзакции.

• 原子性（Atomicity）：Транзакция — это единица атомарных операций, изменения данных которой выполняются либо полностью, либо ни разу.
• 一致性（Consistent）：Данные должны оставаться в согласованном состоянии как в начале, так и при завершении транзакции. Это означает, что все соответствующие правила данных должны применяться к модификации транзакции для поддержания целостности данных; в конце транзакции все внутренние структуры данных (такие как индексы B-дерева или двусвязные списки) также должны быть правильными.
• 隔离性（Isolation）：Система базы данных предоставляет определенный механизм изоляции, чтобы гарантировать выполнение транзакций в «независимой» среде, на которую не влияют внешние параллельные операции. Это означает, что промежуточное состояние в процессе транзакции невидимо для внешнего мира, и наоборот.
• 持久性（Durable）：После того, как транзакция завершена, ее изменения в данных остаются постоянными, даже в случае сбоя системы.

Проблемы, вызванные параллельной обработкой транзакций:

• 更新丢失（Lost Update）
  Проблема потерянных обновлений возникает, когда две или более транзакций выбирают одну и ту же строку, а затем обновляют строку на основе первоначально выбранного значения, поскольку каждая транзакция не знает о существовании другой транзакции — последнее обновление перезаписывает обновления, сделанные другими. фирмы.
• 脏读（Dirty Reads）
  Транзакция A читает транзакцию BОтредактировано, но еще не зафиксированоданных, и операции выполняются на основе этих данных. В этот момент, если транзакция B откатывается, данные, считанные A, недействительны и не соответствуют требованиям согласованности.
• 不可重复读（Non-Repeatable Reads）
  Два идентичных запроса в области транзакции вернули разные данные.
• 幻读（Phantom Reads）
  Транзакция повторно считывает ранее извлеченные данные с помощью того же запроса только для того, чтобы обнаружить, что другие транзакции вставили новые данные, удовлетворяющие условиям ее запроса.Это явление называется «фантомным чтением». Другими словами, транзакция A считывает недавно добавленные данные, отправленные транзакцией B, которая не соответствует изоляции.

Уровень изоляции транзакции:

# 查看事务的隔离级别
show variable like 'tx_isolate'

Блокировка записи (эксклюзивная блокировка):
После добавления монопольной блокировки другие транзакции больше не могут добавлять какие-либо блокировки к A. Транзакция, получившая монопольную блокировку, может как читать, так и изменять данные.

# 通过这段加锁，mysql会对查询结果中的每行都加排他锁
select ... for update;

Блокировка пробела:
Когда мы извлекаем данные с условиями диапазона вместо условий равенства и запрашиваем общую или эксклюзивную блокировку, InnoDB блокирует записи индекса существующих записей данных, которые соответствуют условиям; для записей, значения ключей которых находятся в пределах диапазона условий, но не существует, называется "пробел"
InnoDB также заблокирует этот «пробел».Этот механизм блокировки называется GAP Lock.
危害：
Поскольку Query выполняется через поиск по диапазону, он заблокирует все значения ключа индекса во всем диапазоне, даже если это значение ключа не существует, блокировка пробела имеет фатальную слабость, то есть после блокировки значения ключа диапазона, даже если определенное значение ключа не существует. Некоторые несуществующие значения ключа также будут невинно заблокированы, так что любые данные в пределах заблокированного диапазона значений ключа не могут быть вставлены при блокировке. В некоторых сценариях это может сильно сказаться на производительности.
优化建议：

1. Насколько это возможно, пусть весь поиск данных выполняется через индекс и избегайте эскалации неиндексных блокировок строк до блокировок таблиц.
2. Извлекайте как можно меньше условий и избегайте блокировок пробелов.
3. Попробуйте контролировать размер транзакции, чтобы уменьшить количество заблокированных ресурсов и продолжительность времени.
4. После блокировки строки старайтесь не переносить другие строки или таблицы, быстро обработайте заблокированную строку и снимите блокировку.
5. Для транзакций, включающих одну и ту же таблицу, старайтесь поддерживать постоянный порядок вызова таблиц.
6. Настолько низкоуровневая изоляция транзакций, насколько позволяет бизнес-среда.
   【页锁】
   Накладные расходы и время блокировки находятся между блокировками таблицы и блокировками строк, и будут возникать взаимоблокировки; гранулярность блокировок находится между блокировками таблиц и блокировками строк, а степень параллелизма средняя.

3. Мастер-ведомая репликация

1) Основной принцип репликации

Ведомое будет читать бинарный журнал от ведущего для синхронизации данных

【Три шага】

• Мастер регистрирует изменения в двоичном журнале. Эти процессы регистрации называются событиями двоичного журнала, событиями двоичного журнала.
• Подчиненное устройство копирует события двоичного журнала ведущего в свой журнал реле (релейный журнал).
• Ведомый повторяет события в журнале ретрансляции, применяя изменения к своей собственной базе данных, а репликация mysql является асинхронной и сериализованной.
  

  

2) Основные принципы репликации

• На каждого раба приходится только один мастер
• Каждое подчиненное устройство может иметь только один уникальный идентификатор сервера.
• У каждого мастера может быть несколько ведомых

Самая большая проблема с репликацией:Задерживать

3) Общая конфигурация ведущий-ведомый

Версия mysql такая же, фон работает как служба Конфигурация master-slave находится в узле [mysqld], все в нижнем регистре
[Хост изменяет файл конфигурации my.ini]

• [должен]Уникальный идентификатор главного сервера

server-id = 1

• [должен]включить двоичные файлы

log-bin = 自己本地的路径/data/mysqlbin
log-bin = D:/devSoft/MySQLServer5.5/data/mysqlbin

• [необязательный]включить журнал ошибок

log-err = 自己本地的路径/data/mysqlerr
log-err = D:/devSoft/MySQLServer5.5/data/mysqlerr

• [необязательный]Корневая директория

basedir = "自己本地路径"
basedir = D:/devSoft/MySQLServer5.5/

• [необязательный]временный каталог

tmpdir = "自己本地路径"
tmpdir =  D:/devSoft/MySQLServer5.5/

• [необязательный]каталог данных

datadir = "自己本地路径"
datadir =  D:/devSoft/MySQLServer5.5/data/

• read-only = 0
  Хост может читать и писать
• [необязательный]Установите базу данных, чтобы она не реплицировалась

binlog-ignore-db = mysql

• [необязательный]Настройте базу данных для репликации

binlog-do-db = 需要复制的数据库的名字

[Измените файл конфигурации my.ini с компьютера]

• [должен]Уникальный идентификатор подчиненного сервера

• [необязательный]включить двоичные файлы

  【修改后，主从机都需要重启后台mysql服务】
【主从机都需要关闭防火墙】
【在windows主机上建立账户并授权slave】

• шаг 1:

GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'从机的数据库IP'INDETIFIED BY '123456'

• Шаг 2:

flush privileges;

• Посмотреть основной статус

show master status;
# 记录File和Position 的值

• Не выполняйте никаких дальнейших операций после выполнения вышеуказанных шагов, чтобы предотвратить изменение значения состояния главного сервера.

[Настройте хост, который необходимо реплицировать на ведомом устройстве Linux]

• шаг 1

change master to master_host = '主机IP'，
master_user='zhangsan',master_password = '123456',
master_log_file='file名字',
master_log_pos=position数字

• Шаг 2:
  Запустите функцию копирования с сервера

start slave;

• Шаг 3:

show slave status
#下面两个参数都是Yes，便说明主从配置成功
Slave_IO_Running：Yes
Slave_SQL_running:Yes

【主机新建库，新建表，insert记录，从机便会复制】
【停止从服务复制功能】

stop slave;

Эта статья длинная, и вы видите, что здесь все хорошо, и путь к росту бесконечен.
Если вы будете усердно работать сегодня, завтра вы сможете сказать на одну вещь меньше, чтобы попросить о помощи!
很久很久之前，有个传说，据说：
Не понравилось после прочтения, все хреново