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

4. Оптимизатор запросов преобразует дерево синтаксического анализа в план запроса. В общем, запрос может выполняться разными способами и в конечном итоге возвращать один и тот же результат. Оптимизатор основан на成本Найдите лучший план выполнения среди них

5. План выполнения вызывает механизм выполнения запросов, а механизм запросов запрашивает данные через серию интерфейсов API.

6. После получения данных он будет хранить данные в кеше запросов, возвращая их клиенту.

Как настроить MySQL Query Cache в продакшене

Сначала посмотрите на онлайн-параметры:

Мы обнаружили, что установка для параметра query_cache_type значения OFF фактически отключает эту функцию для онлайн-данных и облачных серверов, предоставляемых крупными поставщиками облачных услуг.他的弊端大于优点.

показатель

пример

Создайте таблицу с именем пользователя, которая включает идентификатор, имя, возраст, пол и другую информацию о полях. Кроме того, id — это кластеризованный индекс первичного ключа, а idx_name — некластеризованный индекс.

CREATE TABLE `user` (
  `id` varchar(10) NOT NULL DEFAULT '',
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Мы установили его на 10 единиц данных, чтобы облегчить понимание следующего индекса.

INSERT INTO `user` VALUES ('1', 'andy', '20', '女');
INSERT INTO `user` VALUES ('10', 'baby', '12', '女');
INSERT INTO `user` VALUES ('2', 'kat', '12', '女');
INSERT INTO `user` VALUES ('3', 'lili', '20', '男');
INSERT INTO `user` VALUES ('4', 'lucy', '22', '女');
INSERT INTO `user` VALUES ('5', 'bill', '20', '男');
INSERT INTO `user` VALUES ('6', 'zoe', '20', '男');
INSERT INTO `user` VALUES ('7', 'hay', '20', '女');
INSERT INTO `user` VALUES ('8', 'tony', '20', '男');
INSERT INTO `user` VALUES ('9', 'rose', '21', '男');

Кластерный индекс (индекс первичного ключа)

Сначала подойдите к изображению здания города, затем посмотрите на изображение и поговорите.

Он содержит две характеристики:

1. Используйте размер значения первичного ключа записи для сортировки записей и страниц.

Записи на странице упорядочены в виде списка из одного элемента в соответствии с размером первичного ключа.

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

2. Листовой узел хранит完整的用户记录.

注：聚簇索引不需要我们显示的创建，他是由InnoDB存储引擎自动为我们创建的。如果没有主键，其也会默认创建一个。

Некластеризованный индекс (вторичный индекс)

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

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

Он и кластерные индексы отличаются:

1. Конечные узлы сортируются по полю имени, а конечные узлы также сортируются по полю имени.

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

为什么不再是完整信息？

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

如果我搜索条件是基于name，需要查询所有字段的信息，那查询过程是啥？

1. По условиям запроса используется некластеризованный индекс name, причем некоторые строки некластеризованного индекса располагаются первыми.

2. Найдите соответствующий идентификатор в соответствии со строкой записи, а затем найдите соответствующую запись в кластеризованном индексе в соответствии с идентификатором. Этот процесс называется回表.

совместный индекс

Картинка не нарисуется.Короче говоря, если имя и возраст образуют совместный указатель, то сортировать сначала по имени, а если имя совпадает, сортировать по возрасту.

некоторые принципы

1. Принцип самого левого префикса. Совместный индекс (a,b,c), если есть условие запроса с a и b, то он пойдет на индекс, если есть условие запроса без a, то он не пойдет на индекс.

2. Используйте уникальный индекс. Столбцы с несколькими повторяющимися значениями имеют наихудшую индексацию. Например, столбец, содержащий имена, имеет разные значения, что упрощает различение каждой строки. Столбец, используемый для записи пола, содержит только «мужской» и «женский». Независимо от того, какое значение ищется, будет получено около половины строк. Такой индекс недостаточно высок для повышения производительности.

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

4. Столбец индекса не может участвовать в расчете, держите столбец "чистым", например, from_unixtime(create_time) = '2014-05-29', индекс использовать нельзя, причина очень проста, дерево b+ хранит все значения полей в таблице данных, но при извлечении нужно применить функцию ко всем элементам для сравнения, что явно слишком дорого. Таким образом, оператор должен быть записан как create_time = unix_timestamp('2014-05-29');

5. Обязательно установите первичный ключ. В предыдущем кластерном индексе говорилось, что если вы не укажете первичный ключ, InnoDB автоматически назначит ему первичный ключ, который мы не можем видеть. В любом случае необходимо сгенерировать первичный ключ, лучше его настроить, в дальнейшем кластерный индекс первичного ключа можно будет использовать в некоторых условиях поиска.

6. Рекомендуется использовать автоинкрементный идентификатор вместо uuid для первичного ключа. Приведенный выше кластерный индекс говорит о том, что данные на каждой странице отсортированы, и страницы тоже отсортированы.Если это uuid, то он должен быть случайным.Он может быть вставлен с середины, что приведет к разбиению страниц и созданию множества фрагментов таблицы . Если он самоувеличивающийся, то он самоувеличивается от меньшего к большему, и есть порядок, то он будет добавлен к последующей позиции текущего индекса при его вставке. Когда страница заполнена, автоматически открывается новая страница.

注：如果自增id用完了，那将字段类型改为bigint，就算每秒1万条数据，跑100年，也没达到bigint的最大值。

Ваннианские вопросы интервью (почему в индексе используется дерево B+)

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

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

Итак, вопрос в том, как MySQL выбирает метод выполнения с наименьшим временем и наименьшим использованием памяти?

Какова цена?

1. Стоимость ввода-вывода. Данные хранятся на жестком диске, и мы хотим выполнить какую-то операцию, чтобы загрузить их в память, время этого процесса называется стоимостью ввода-вывода. По умолчанию 1.

2. Стоимость процессора. Время сортировки результирующего набора в памяти называется затратами ЦП. По умолчанию 0,2.

Стоимость запроса одной таблицы

Во-первых, создайте пользовательскую таблицу dev_user, которая включает в себя идентификатор первичного ключа, имя пользователя, имя пользователя, пароль, пароль, внешний ключ user_info_id, состояние статуса, внешний ключ main_station_id, доступ к внешней сети, эти семь полей. Есть два индекса: один — кластерный индекс первичного ключа, а другой — явно добавленный уникальный индекс uname_unique с именем пользователя в качестве поля.

Если условием поиска является select * from dev_user, где username='XXX', как MySQL выбирает соответствующий индекс?

1. Используйте все возможные индексы

Мы можем видеть имя пользователя для поискового запроса, так что, возможно, пойдем по индексу uname_unique. Также можно использовать кластеризованные индексы, то есть полное сканирование таблиц.

2. Рассчитайте полную стоимость сканирования таблицы

мы проходимshow table status like ‘dev_user’команда знатьrowsиdata_lengthполе, как показано ниже.

rows: указывает количество записей в таблице, но эти данные неточны и являются оценочным значением.

data_length: указывает количество байтов дискового пространства, занимаемого таблицей.

data_length=聚簇索引的页面数量X每个页面的大小

Количество перевернутых страниц = 1589248÷16÷1024=97

Стоимость ввода/вывода: 97X1=97

Стоимость ЦП: 6141X0,2=1228

Общая стоимость: 97+1228=1325

3. Рассчитать стоимость выполнения запросов с использованием разных индексов

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

Стоимость ввода-вывода = 1+1X1=2 (количество интервалов диапазона + ожидаемое количество индексов вторичных записей)

Стоимость ЦП = 1X0,2+1X0,2=0,4 (стоимость чтения вторичного индекса + стоимость возврата табличного кластеризованного индекса)

Общая стоимость = стоимость ввода-вывода + стоимость ЦП = 2,4

4. Сравните стоимость различных вариантов внедрения и найдите вариант с наименьшей стоимостью

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

Стоимость многотабличных запросов

Для запроса соединения двух таблиц стоимость запроса состоит из следующих двух частей:

• Стоимость одного запроса для управления таблицей
• Стоимость запроса к управляемой таблице несколько раз (конкретный запрос несколько раз зависит от того, сколько записей есть в наборе результатов запроса к управляющей таблице)

index dive

Если предыдущее условие поиска не эквивалентно, а является интервалом, напримерselect * from dev_user where username>'admin' and username<'test'В настоящее время мы не можем видеть количество таблиц, которые необходимо вернуть.

Шаг 1: Сначала найдите первую запись в соответствии с условием имя пользователя> «admin», называемую区间最左记录.

Шаг 2: Найдите последнюю запись на основе условия username区间最右记录.

Шаг 3: Если разница между самой левой записью интервала и самой правой записью интервала не очень велика, вы можете точно подсчитать количество записей, которые необходимо вернуть в таблицу. Если разница очень велика, сначала посчитайте, сколько записей на 10 страницах, затем умножьте на количество страниц и, наконец, получите нечеткую статистику.

Explain

продукт убивает

Продукт: Почему эта страница выходит так медленно?

Разработка: Поскольку у вас много данных для проверки, он такой медленный

Продукт: Мне все равно, я хочу, чтобы эта страница поторопилась, как клиенты используют ее, как вы?

Разработка: . . . . . . . ты приходишь

Хахахаха, нет BB, если какие-то воры SQL медленные, нам нужно знать, перешел ли он к индексу и какой индекс он взял.На данный момент мне нужно использовать ключевое слово объяснения, чтобы понять, как MySQL внутренне выполняется.

id

Вообще говоря, выборка имеет уникальный идентификатор.Если это подзапрос, есть два выборки с разными идентификаторами, но есть исключения для всего.Для некоторых подзапросов их идентификаторы одинаковы.

Почему это?

Это связано с тем, что MySQL изменил подзапрос на запрос на соединение при оптимизации, а идентификатор запроса на соединение остался прежним.

select_type

• простой: запросы, которые не включают объединения и подзапросы, являются простыми типами.
• первичный: включая объединение, объединение всех, и самый левый запрос является первичным.
• объединение: включая объединение, объединение всех, кроме самого левого запроса, все остальные типы запросов являются объединением.

table

Покажите, о какой таблице идет речь.

тип: метод доступа

• ref: общий вторичный индекс и константа для сопоставления равных значений
• ref_or_null: общий вторичный индекс эквивалентно соответствует константе, индекс может быть нулевым
• const: уникальный первичный ключ или вторичный индекс соответствующий столбец с константами эквивалентности
• диапазон: запрос интервала диапазона
• все: полное сканирование таблицы

possible_keys

Индексы, которые можно использовать при выполнении однотабличного запроса к таблице

key

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

rows

• Если используется полное сканирование таблицы, то rows представляет количество строк, которые необходимо просмотреть.
• Если используется индекс, то rows представляет количество строк, которые, как ожидается, будут просканированы.

filtered

• Если полная таблица сканирует, затем отфильтруется от имени записей, которые соответствуют соотношению критериев поиска
• Если это индекс, то filtered представляет процент других условий поиска, кроме поиска, соответствующего индексу.

журнал повторов (физический журнал)

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

Тогда возникает проблема.Если я хочу перевести 100 юаней Чжан Саню, транзакция отправлена.В это время InnoDB загружает данные в память, и не успевает прошить их на жесткий диск, происходит внезапный сбой питания и сбой базы данных. После перезагрузки я обнаружил, что мои деньги не были успешно переведены, разве это не смущает?

Решение очевидное.После загрузки винчестера в память выполняем ряд операций.Одна операция лютая как тигр.Перед тем как перепрошиться на винчестер сначала записываем.Сумма в третьем запись увеличивается на 100, затем выполняются операции добавления, удаления, модификации и проверки, и, наконец, она сбрасывается на жесткий диск. Если она не прошита на жесткий диск, после перезагрузки сначала загрузите предыдущую запись, тогда данные вернутся.

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

буфер журнала

Чтобы решить проблему, связанную со слишком низкой скоростью диска, журнал повторов не может быть записан напрямую на диск, поэтому давайте выделим для него большой непрерывный объем памяти для размещения данных. Этот большой кусок памяти называется буфером журнала или буфером журнала. Когда придет время, снова прошейте жесткий диск. О том, когда это уместно, говорится в следующей главе.

мы можем пройтиshow VARIABLES like 'innodb_log_buffer_size'команда для просмотра текущего размера кэша журнала, на следующем рисунке размер показан в строке.

Время сброса журнала повторов

Поскольку журнал повторов постоянно растет, а объем памяти ограничен, данные не могут все время оставаться в кэше. Нам нужно прошить его на жесткий диск. 

Когда он будет сброшен на жесткий диск?

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

группа файлов журнала повторов

мы можем пройтиshow variables like 'datadir'Команда находит соответствующий каталог, в нем два файла, Это ib_logfile0 и ib_logfile1 соответственно, как показано на следующем рисунке.

Мы сбрасываем журналы повторов в буфер журнала буфера в эти два файла, как они написаны Он записывается циклически, сначала запишите ib_logfile0, затем запишите ib_logfile1, а затем запишите ib_logfile0, когда ib_logfile1 заполнится. Тогда будет проблема, если ib_logfile1 заполнен, то писать ib_logfile0, содержимое ib_logfile0 перед Не было ли оно просто перезаписано и потеряно? Это работа контрольно-пропускного пункта.

checkpoint

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

Конфликт-репетиторство

Поскольку система работает, страница постоянно модифицируется, и постоянно генерируются журналы повторов. журнал повторов постоянен Инкрементный, MySQL берет имя для него Номер последовательности журнала Номер последовательности журнала, называемый lsn. Его начальное значение — 8704, которое используется для записи того, сколько журналов повторного выполнения создается в настоящее время.

Журнал повторов — это файл журнала повторов, который сначала записывается в буфер журнала, а затем сбрасывается на диск. MySQL для Возьмите имя flush_to_disk_lsn. Используется для указания, сколько данных грязных страниц в кэше сбрасывается на диск. Его начальное значение такое же, как у lsn, и за ним будет пробел.

Выполнение контрольной точки разделено на два этапа.

• Вычислите максимальное значение lsn, соответствующее журналам повторов, которые могут быть перезаписаны текущей системой. журналы повторов могут быть перезаписаны, Это означает, что его соответствующая грязная страница сбрасывается на диск, пока мы вычисляем самую старую измененную самую старую_модификацию в текущей системе, Пока номер lsn в системе меньше, чем самое старое значение_модификации узла, журнал повторов на диске может быть перезаписан.
• Подсчитайте некоторые данные в процессе lsn.

журнал отмены (эта часть не очень понятна, поэтому я, вероятно, сказал это)

Базовые концепты

Журнал отмены служит двум целям: обеспечивает откат и управление версиями нескольких строк (MVCC).

В отличие от журнала повторов, в который записываются физические журналы, журнал отмен — это логический журнал. Можно считать, что при удалении записи в журнал отмен будет записана соответствующая запись вставки, и наоборот, при обновлении записи будет записана соответствующая противоположная запись обновления.

Например:

insert into a(id) values(1);(redo)
Эту запись нужно откатить.
Оператор отката — это удаление из объекта, где id = 1; (отмена)

Просто подумай об этом. Если нет, вставьте в (id) значения (1); (повторите)
Тогда предложение удалить из a, где id = 1;(отменить), бессмысленно.

Теперь смотрим на правильное рекавери:
Сначала вставьте в (id) значения (1); (повторить)
Затем удалите из, где id = 1; (отменить)
Система возвращается в исходное состояние, и такой записи нет.

способ хранения

дела

введение

В транзакции есть функция изоляции. Теоретически, когда транзакция обращается к определенным данным, другие транзакции должны быть упорядочены. После фиксации транзакции другие транзакции могут продолжать получать доступ к данным.

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

Проблема одновременного выполнения транзакций

• Грязное письмо (это слишком серьезно, никакой уровень изоляции не допускается)