Анализ 100 классических вопросов интервью с базой данных MySQL (коллекционное издание)

предисловие

Анализ 100 классических вопросов интервью с базой данных MySQL загружен на github.

GitHub.com/Я бы хотел 123/Java…

Общественный номер: маленький мальчик собирает улиток

база данных

1. Каковы меры предосторожности при использовании индексов MySQL?

На этот вопрос можно ответить по трем параметрам: в каких случаях индекс даст сбой, в каких сценариях индекс не подходит и правила индекса.

При каких обстоятельствах индекс не сработает

Условие запроса содержит или, что может привести к сбою индекса.
Если тип поля является строкой, поле должно быть заключено в кавычки, иначе индекс будет недействительным.
например, подстановочные знаки могут привести к аннулированию индекса.
Для объединенного индекса условный столбец в запросе не является первым столбцом в объединенном индексе, и индекс недействителен.
Используйте встроенную функцию mysql для столбца индекса, индекс недействителен.
Операции с индексированными столбцами (например, +, -, *, /) делают индекс недействительным.
При использовании в индексных полях (!= или , а не в) индекс может стать недействительным.
Использование null и не null в поле индекса может привести к сбою индекса.
Формат кодирования полей, связанных с левым запросом на соединение или правым запросом на соединение, отличается, что может привести к сбою индекса.
MySQL оценивает, что использование полного сканирования таблицы быстрее, чем использование индекса, поэтому индекс не используется.

Бэкенд-программисты должны: десять самых разных симптомов сбоя индекса

Для каких сценариев не подходят индексы?

Не подходит для индексации с небольшим объемом данных
Частые обновления не подходят для индексации
Поля с низкой дискриминацией не подходят для индексации (например, пол)

Некоторые негласные правила индексации

индекс покрытия
форма возврата
Структура данных индекса (дерево B+)
крайний левый префикс
выталкивание индекса

2. Сталкивалась ли MySQL с проблемой взаимоблокировки и как вы ее решили?

Мои общие шаги по устранению взаимоблокировок окрашены в фиолетовый цвет:

Просмотрите журнал тупиковых ситуаций, показывающий статус движка innodb;
найти тупик Sql
Анализ ситуации блокировки sql
Моделирование тупиковой ситуации
Анализ журналов взаимоблокировок
Анализ результатов взаимоблокировки

Посмотрите две мои статьи:

Научить вас анализировать проблему взаимоблокировки Mysql
Как устранить взаимоблокировку Mysql: вставить в повторяющуюся взаимоблокировку процесс анализа устранения неполадок

3. Как вы оптимизируете SQL в своей повседневной работе?

На этот вопрос можно ответить, исходя из следующих измерений:

показатель
Избегайте возврата ненужных данных
соответствующие партии
Оптимизировать структуру sql
Подбиблиотека и подтаблица
разделение чтения-записи

Посмотрите мою статью:Необходимые вещи для back-end программиста: 30 советов по написанию высококачественного SQL

4. Расскажите о дизайне подбазы данных и подтаблицы.

Схема подбиблиотеки и подтаблицы, промежуточное ПО подбиблиотеки и подтаблицы, возможные проблемы, возникающие в подбиблиотеке и подтаблице

Схема подбиблиотеки и подтаблицы:

Горизонтальная подбиблиотека: в зависимости от поля, по определенной стратегии (хеш, диапазон и т. д.) данные в одной библиотеке разбиваются на несколько библиотек.
Горизонтальное разбиение таблицы: на основе полей, в соответствии с определенными стратегиями (хеш, диапазон и т. д.) данные в одной таблице разбиваются на несколько таблиц.
Вертикальная подбиблиотека: в зависимости от таблицы разные таблицы делятся на разные библиотеки в зависимости от принадлежности к бизнесу.
Вертикальное разделение таблицы: в зависимости от поля поля в таблице разбиваются на разные таблицы (основная таблица и таблица расширения) в соответствии с активностью поля.

Обычно используемое промежуточное ПО для подбаз данных и подтаблиц:

осколки-jdbc (dangdang)
Mycat
ТДДЛ (Таобао)
Oceanus (промежуточное ПО для баз данных 58 городов)
vitess (промежуточное программное обеспечение базы данных, разработанное Google)
Atlas(Qihoo 360)

Проблемы, с которыми можно столкнуться в подбиблиотеке и подтаблице

Проблема с транзакциями: необходимо использовать распределенные транзакции
Проблема межузлового соединения: чтобы решить эту проблему, ее можно реализовать двумя запросами.
Проблемы с межузловым подсчетом, упорядочением, группировкой и агрегированием: объединяйте результаты на стороне приложения после получения результатов на каждом узле.
Миграция данных, планирование емкости, расширение емкости и т. д.
Проблема с идентификатором: после разделения базы данных вы больше не можете полагаться на собственный механизм генерации первичного ключа базы данных.Самый простой способ рассмотреть UUID
Проблемы с сортировкой и разбиением на страницы по сегментам (увеличение размера страницы в фоновом режиме?)

Лично я считаю эти две статьи в интернете хорошими, можете глянуть на них:

Обычно используемое решение для подтаблиц подбаз данных в Интернете для базы данных MySQL
Проблемы и решения, которые необходимо рассмотреть для подбазы данных и подтаблицы

5. Разница между InnoDB и MyISAM

InnoDB поддерживает транзакции, MyISAM — нет.
InnoDB поддерживает внешние ключи, MyISAM не поддерживает внешние ключи.
InnoDB поддерживает MVCC (управление параллелизмом нескольких версий), MyISAM — нет.
При выборе count(*) из таблицы MyISAM работает быстрее, потому что в нем есть переменная, которая хранит общее количество строк во всей таблице и может быть прочитана напрямую InnoDB требует полного сканирования таблицы.
Innodb не поддерживает полнотекстовое индексирование, в то время как MyISAM поддерживает полнотекстовое индексирование (InnoDB после 5.7 также поддерживает полнотекстовое индексирование).
InnoDB поддерживает блокировки на уровне таблиц и строк, а MyISAM поддерживает блокировки на уровне таблиц.
Таблицы InnoDB должны иметь первичный ключ, в то время как MyISAM не может иметь первичного ключа.
Таблицы Innodb требуют больше памяти и места для хранения, а MyISAM можно сжать, заняв меньше места.
Innodb вставляется по размеру первичного ключа, записи MyISAM вставляются в порядке и сохраняются в порядке вставки записи.
Механизм хранения InnoDB обеспечивает безопасность транзакций с возможностями фиксации, отката и восстановления после сбоя, он менее эффективен, чем MyISAM, при записи, чем InnoDB, и будет занимать больше места на диске для хранения данных и индексов.

6. Принцип индексации базы данных, зачем использовать дерево B+, почему бы не использовать бинарное дерево?

Этот вопрос можно рассматривать с нескольких точек зрения: достаточно ли быстр запрос, стабильна ли эффективность, сколько данных хранится и количество операций поиска на диске, почему не двоичное дерево, почему не сбалансированное двоичное дерево, почему дерево B, но дерево B+?

Почему не общее бинарное дерево?

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

Почему не сбалансированное бинарное дерево?

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

Тогда почему это не B-дерево, а B+-дерево?

1) На нелистовых узлах дерева B+ не хранятся данные, хранится только значение ключа, а узел дерева B хранит не только значение ключа, но и данные. Размер страницы в innodb по умолчанию составляет 16 КБ.Если данные не будут сохранены, будет сохранено больше значений ключей, порядок соответствующего дерева (дерево дочерних узлов узла) будет больше, а дерево будет короче и толще, таким образом, количество операций ввода-вывода для поиска данных на диске снова сократится, а эффективность запроса данных будет выше.

2) Все данные индекса дерева B+ хранятся в листовых узлах, и данные упорядочены по порядку, а связанный список связан. Затем дерево B+ делает поиск по диапазонам, поиск по сортировке, поиск по группам и поиск с дедупликацией чрезвычайно простыми.

Ознакомьтесь с этой статьей:Если кто-то спросит вас, почему MySQL использует дерево B+ в качестве индекса, отправьте ему эту статью.

7. Разница между кластеризованным индексом и некластеризованным индексом

Таблица может иметь только один кластеризованный индекс, а таблица может иметь несколько некластеризованных индексов.
Для кластеризованного индекса логический порядок значений ключей в индексе определяет физический порядок соответствующих строк в таблице, для некластеризованного индекса логический порядок индексов в индексе отличается от физического порядок хранения строк на диске.
Индекс описывается структурой данных двоичного дерева.Мы можем понять кластеризованный индекс следующим образом: листовые узлы индекса являются узлами данных. Листовой узел некластеризованного индекса по-прежнему является узлом индекса, но имеется указатель на соответствующий блок данных.
Кластеризованный индекс: физическое хранилище сортируется по индексу Некластеризованный индекс: физическое хранилище не сортируется по индексу;

Когда использовать кластеризованный или некластеризованный индекс?

8. Если ограничение в 1000000 загружается медленно, как вы решили эту проблему?

Вариант 1: Если id непрерывный, можно сделать так, вернуть максимальную запись (смещение) последнего запроса, а потом спуститься на лимит

select id，name from employee where id>1000000 limit 10.

Вариант 2. Ограничьте количество страниц, если бизнес позволяет:

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

Вариант 3: упорядочить по + индексу (id - это индекс)

select id，name from employee order by id  limit 1000000，10

Вариант 4. Используйте отложенную ассоциацию или подзапросы для оптимизации сценариев многостраничного пейджинга. (Сначала быстро найдите сегмент идентификатора, который необходимо получить, а затем свяжите его)

SELECT a.* FROM employee a, (select id from employee where 条件 LIMIT 1000000,10 ) b where a.id=b.id

9. Как выбрать подходящую схему распределенного первичного ключа?

Автоматически увеличивающаяся последовательность или поле базы данных.
UUID.
Redis генерирует идентификатор
Алгоритм снежинки в Твиттере
Используйте zookeeper для создания уникального идентификатора
Идентификатор объекта MongoDB

10. Каковы уровни изоляции транзакций? Каков уровень изоляции MySQL по умолчанию?

Читать незафиксированные
Чтение зафиксировано
Повторяемое чтение
Сериализуемый

Уровень изоляции транзакций по умолчанию в Mysql — Repeatable Read.

Посмотрите мою статью:В этой статье подробно рассматриваются четыре уровня изоляции транзакций MySQL.

11. Что такое фантомное чтение, грязное чтение и неповторяемое чтение?

Транзакции A и B выполняются попеременно, и транзакция A нарушается транзакцией B, потому что транзакция A считывает незафиксированные данные транзакции B, котораягрязное чтение
В рамках транзакции два идентичных запроса считывают одну и ту же запись, но возвращают разные данные.неповторяемое чтение.
Транзакция A запрашивает диапазон наборов результатов, другая параллельная транзакция B вставляет/удаляет данные в этот диапазон и автоматически фиксирует, а затем транзакция A снова запрашивает тот же диапазон, и наборы результатов, полученные двумя чтениями, различаются.галлюцинации.

Посмотрите мою статью:В этой статье подробно рассматриваются четыре уровня изоляции транзакций MySQL.

12. Как безопасно изменять одну и ту же строку данных в случае высокой степени параллелизма?

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

Используйте пессимистическую блокировку

Идея пессимистической блокировки заключается в том, что когда текущий поток хочет войти и изменить данные, другие потоки должны быть закрыты~ Например, вы можете использовать select...for update ~

select * from User where name=‘jay’ for update

Приведенный выше оператор sql заблокирует все записи в таблице User, соответствующие условиям извлечения (name='jay'). Никакой другой поток не может изменять эти записи, пока транзакция не будет зафиксирована.

Используйте оптимистическую блокировку

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

Вы можете взглянуть на мою статью, основная идея ha~Практика оптимистической блокировки CAS для решения проблем параллелизма

13. Оптимистическая блокировка и пессимистическая блокировка базы данных.

Пессимистический замок:

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

Оптимистичная блокировка:

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

Эта статья уже публиковалась ранее, я думаю, что автор написал ее очень подробно~

Получите полное представление о пессимистичных и оптимистичных замках с картинками и текстами.

14. Каковы общие шаги оптимизации SQL, как посмотреть план выполнения (объяснить) и как понять значение каждого поля.

показать команду состояния, чтобы понять частоту выполнения различных sql
Найдите эти SQL-запросы с низкой эффективностью выполнения в журнале медленных запросов.
Объяснение анализирует план выполнения неэффективного sql (это очень важно, его использование для анализа sql в ежедневной разработке значительно уменьшит онлайн-аварии, вызванные sql)

Прочитав эту статью, я думаю, что это очень хорошо:Общие шаги по оптимизации инструкции sql

15. Что означает выбор для обновления, будет ли он блокировать таблицу, строку или другое.

выбрать для обновления Значение

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

Если индексный/первичный ключ не используется, это блокировка таблицы, в противном случае — блокировка строки.

выбрать для проверки блокировки обновления

Структура таблицы:

//id 为主键，name为唯一索引
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8

id это первичный ключ.При выборе записи для обновления 1270070 открыть другую транзакцию для обновления записи.Обнаружено что обновление заблокировано.На самом деле оно заблокировано. Как показано ниже:

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

Давайте продолжим весь путь на север, изменим баланс общего поля и обнаружим, что он снова заблокирован. Следовательно, без индекса/первичного ключаВыбрать для обновления добавляет блокировку таблицы

16. Четыре характеристики транзакций MySQL и принципы их реализации

Атомарность: транзакция выполняется как единое целое, и либо все операции над базой данных, содержащиеся в ней, либо не выполняются.
Согласованность: это означает, что данные не будут уничтожены до начала транзакции и после ее завершения.Если счет A переводит 10 юаней на счет B, общая сумма A и B остается неизменной независимо от того, успешна она или нет.
Изоляция: при одновременном доступе нескольких транзакций транзакции изолированы друг от друга, то есть одна транзакция не влияет на выполнение других транзакций. Короче говоря, это означает, что между делами нет речной воды.
Постоянство: указывает, что после завершения транзакции операционные изменения, внесенные транзакцией в базу данных, будут сохраняться в базе данных.

Идея реализации функции ACID транзакций

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

17. Если в таблице почти 10 миллионов данных, CRUD относительно медленный, как его оптимизировать.

Подбиблиотека и подтаблица

В таблице почти 10 миллионов данных, вы можете подумать об оптимизации структуры таблицы и подтаблиц (горизонтальная подтаблица, вертикальная подтаблица), конечно, если вы ответите таким образом, вы должны быть готовы к тому, что интервьюер спросит вопросы о подбазе данных и подтаблицах, такие как

Схема разделенной таблицы (горизонтальная разделенная таблица, вертикальная разделенная таблица, хэш правила разделения и т. д.)
Промежуточное ПО для подтаблиц подбазы данных (Mycat, sharding-jdbc и т. д.)
Некоторые проблемы с подбазой данных и подтаблицей (проблема с транзакцией? проблема с соединением между узлами)
Решения (распределенные транзакции и т. д.)

Оптимизация индекса

В дополнение к подбазе данных и подтаблице, оптимизации структуры таблицы, конечно, есть и другие решения, такие как оптимизация индекса~

Если вам интересно, вы можете прочитать мою статью~Необходимые вещи для back-end программиста: 30 советов по написанию высококачественного SQL

18. Как написать sql для эффективного использования составных индексов.

Составной индекс, также называемый составным индексом, позволяет пользователям создавать индекс для нескольких столбцов.Такой тип индекса называется составным индексом.

Когда мы создаем комбинированный индекс, такой как (k1, k2, k3), это эквивалентно созданию трех индексов (k1), (k1, k2) и (k1, k2, k3), что является самым левым принципом сопоставления.

select * from table where k1=A AND k2=B AND k3=D

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

19. Разница между in и существует в mysql.

Это, следуйте за демонстрацией, чтобы увидеть более захватывающие, ахаха

Предполагая, что таблица A представляет таблицу сотрудников предприятия, а таблица B представляет таблицу отделов, для запроса всех сотрудников всех отделов легко получить следующий SQL:

select * from A where deptId in (select deptId from B);

Написание этого эквивалентно:

Таблица отдела первого запроса B

select deptId from B

Затем используйте deptId отдела, чтобы запросить сотрудников A

select * from A where A.deptId = B.deptId

Его можно абстрагировать в такой цикл:

   List<> resultSet ;
    for(int i=0;i<B.length;i++) {
          for(int j=0;j<A.length;j++) {
          if(A[i].id==B[j].id) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

Очевидно, что в дополнение к in мы также можем использовать exists для получения той же функции запроса, как показано ниже:

select * from A where exists (select 1 from B where A.deptId = B.deptId);

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

Тогда запись этого эквивалентна:

выберите * из A, первый цикл из таблицы A

выберите * из B, где A.deptId = B.deptId, а затем выполните цикл из таблицы B.

Точно так же его можно абстрагировать в такой цикл:

   List<> resultSet ;
    for(int i=0;i<A.length;i++) {
          for(int j=0;j<B.length;j++) {
          if(A[i].deptId==B[j].deptId) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

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

Поэтому мы хотим выбрать самый внешний цикл маленьким, то есть, еслиОбъем данных в B меньше, чем в A, поэтому его можно использовать в . Если объем данных в B больше, чем в A, целесообразно выбрать существует., что является разницей между in и exists.

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

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

21. Знакомы ли вы с MVCC и его основными принципами?

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

MVCC необходимо обратить внимание на эти знания:

Номер версии транзакции
скрытый столбец таблицы
undo log
read view

Посмотрите мою статью:В этой статье подробно рассматриваются четыре уровня изоляции транзакций MySQL.

22. Слышали ли вы о промежуточном программном обеспечении базы данных, сегментировании jdbc, mycat?

sharding-jdbc на данный момент основан на драйвере jdbc и не требует дополнительных прокси, поэтому на высокую доступность самого прокси обращать внимание не нужно.
Mycat основан на Proxy, который копирует протокол MySQL и маскирует Mycat Server под базу данных MySQL, а Sharding-JDBC — это расширение, основанное на интерфейсе JDBC и предоставляющее облегченные услуги в виде пакетов jar.

Некоторые пользователи сети рекомендовали эту статью:

Глубокое понимание Sharding-JDBC: быть самым легким средним уровнем базы данных

23. Как вы решаете задержку master-slave в MYSQL?

Хи-хи, давайте сначала рассмотрим принцип репликации master-slave, как показано на рисунке:

Репликация master-slave делится на пять шагов:

Шаг 1: События обновления (обновление, вставка, удаление) основной библиотеки записываются в binlog.
Шаг 2: Инициируйте подключение из библиотеки и подключитесь к основной библиотеке.
Шаг 3: В это время основная библиотека создает поток дампа binlog и отправляет содержимое binlog в подчиненную библиотеку.
Шаг 4: После запуска подчиненной библиотеки создайте поток ввода-вывода, прочитайте содержимое binlog из основной библиотеки и запишите его в журнал реле.
Шаг 5: Также будет создан поток SQL для чтения содержимого из журнала ретрансляции, выполнения события обновления чтения из позиции Exec_Master_Log_Pos и записи обновленного содержимого в базу данных подчиненного устройства.

Заинтересованные друзья также могут прочитать мою статью:Бэкенд-программисты должны: блок-схема/схема, связанная с базой данных mysql.

Причины задержки синхронизации master-slave

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

Решение задержки синхронизации ведущий-ведомый

Главный сервер отвечает за операцию обновления и имеет более высокие требования к безопасности, чем подчиненный сервер, поэтому некоторые параметры настройки могут быть изменены, например, sync_binlog=1, innodb_flush_log_at_trx_commit=1 и другие настройки.
Выберите лучшее аппаратное устройство в качестве ведомого.
Если подчиненный сервер используется в качестве резервного вместо предоставления запросов, его нагрузка будет снижена, а эффективность выполнения SQL в журнале ретрансляции, естественно, будет высокой.
Цель увеличения ведомого сервера состоит в том, чтобы распределить давление чтения, тем самым снизив нагрузку на сервер.

Вы можете прочитать эту статью~Причины и решения для задержки синхронизации MySQL master-slave

24. Расскажите о схеме оптимизации запроса к большой таблице.

Оптимизировать схему, оператор sql + индекс;
Рассмотрите возможность добавления кеша, memcached, Redis или локального кеша JVM;
Репликация master-slave, разделение чтения и записи;
подтаблица подбиблиотеки;

25. Что такое пул соединений с базой данных Зачем нужен пул соединений с базой данных?

Основной принцип пула соединений:Принцип пула соединений с базой данных: во внутреннем пуле объектов поддерживается определенное количество соединений с базой данных, а методы получения и возврата соединений с базой данных доступны для внешнего мира.

Процесс установления связи между приложением и базой данных:

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

Преимущества пула соединений с базой данных:

Повторное использование ресурсов (повторное использование соединения)
Более высокая скорость отклика системы
новые средства распределения ресурсов
Унифицированное управление соединениями для предотвращения утечек соединения с базой данных

Заинтересованные партнеры могут прочитать мою статью~Анализ и решение проблемы утечки памяти пула соединений с базой данных

26. Как выполняется оператор SQL в MySQL?

Давайте взглянем на диаграмму логической архитектуры Mysql~

проверить фразу:

Сначала проверьте, есть ли у оператора разрешение
Если у вас нет разрешения, верните сообщение об ошибке напрямую
Если у вас есть разрешение, до MySQL 8.0 кеш будет запрашиваться в первую очередь.
Если кэша нет, анализатор выполняет лексический анализ, извлечение ключевых элементов sql-оператора select и т. д. Затем оцените, есть ли в операторе sql синтаксические ошибки, например, правильные ли ключевые слова и т. д.
Оптимизатор определяет план выполнения
Выполнить проверку разрешения. Если разрешения нет, будет возвращено сообщение об ошибке. Если разрешение есть, будет вызван интерфейс ядра базы данных и возвращен результат выполнения.

Эта статья очень хороша, давайте проверим ее:Как оператор SQL выполняется в MySQL

27. Знаете ли вы стратегию индексирования в движке InnoDB?

индекс покрытия
крайний левый префикс
выталкивание индекса

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

Эта статья очень хороша, давайте проверим ее:Давайте поговорим об этих стратегиях индексирования в движке InnoDB.

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

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

Как думать о проблемах преобразования часовых поясов / взгляните на это:Как учитывать преобразование часового пояса, когда в базе данных хранятся форматы даты?

29. Как оптимизировать SQL, выполнение которого занимает слишком много времени, и с чего начать?

Проверьте, задействованы ли несколько таблиц и подзапросов, оптимизируйте структуру Sql, например, удалите лишние поля, можно ли разделить таблицы и т. д.
Оптимизируйте структуру индекса, чтобы увидеть, можно ли правильно добавлять индексы.
Для большого количества таблиц вы можете рассмотреть возможность разделения/подтаблиц (например, счетчики потока транзакций)
Разделение базы данных master-slave, разделение чтения-записи
объяснять анализировать операторы SQL, просматривать планы выполнения и оптимизировать SQL
Проверьте журнал выполнения mysql и проанализируйте, есть ли другие проблемы.

30. Какие есть команды метода для анализа производительности сервера базы данных MYSQL?

Показать статус, некоторые значения переменных, за которыми стоит следить:

Bytes_received и Bytes_sent трафика на сервер и с сервера.

Команда, которую выполняет сервер Com_*.

Created_* Временные таблицы и файлы, созданные во время выполнения запроса.

Handler_* операции механизма хранения.

Выберите_* различные типы планов выполнения соединений.

Sort_* Несколько видов информации.

Профили показа используются MySql для анализа потребления ресурсов при выполнении оператора SQL в текущем сеансе.

31. В чем разница между блобом и текстом?

Blob используется для хранения двоичных данных, а Text — для хранения больших строк.
Значения BLOB-объектов обрабатываются как двоичные строки (строки байтов), они не имеют набора символов и сортируются и сравниваются на основе числового значения байтов в значении столбца.
текстовые значения обрабатываются как недвоичные строки (символьные строки). Они имеют набор символов и сортируют и сравнивают значения в соответствии с сопоставлением набора символов.

32. Какой тип поля лучше использовать для записи валюты в mysql?

Валюта обычно представлена в базе данных типами Decimal и Numric, которые реализованы MySQL как один и тот же тип. Они используются для хранения данных, связанных с деньгами.
зарплата DECIMAL(9,2), 9(точность) представляет собой общее количество знаков после запятой, которое будет использоваться для хранения значения, а 2(масштаб) представляет количество знаков после запятой, которое будет использоваться для хранения значения. Диапазон значений, хранящихся в столбце зарплаты, составляет от -9999999,99 до 9999999,99.
Значения DECIMAL и NUMERIC хранятся в виде строк, а не в виде двоичных чисел с плавающей запятой, чтобы сохранить десятичную точность этих значений.

33. Какие виды блокировок есть в Mysql, перечислите их?

Если разделить по степени детализации блокировки, существуют следующие три типа:

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

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

Бэкенд-программисты должны: блок-схема/схема, связанная с базой данных mysql.

34. В чем разница между хэш-индексом и деревом B+? Что вы выбрали при разработке индекса?

Деревья B+ могут выполнять запросы диапазона, а хэш-индексы — нет.
Дерево B+ поддерживает самый левый принцип совместного индекса, хэш-индекс не поддерживает.
Дерево B+ поддерживает порядок путем сортировки, а хэш-индекс — нет.
Хэш-индексы более эффективны, чем деревья B+ для запросов на равенство.
Когда дерево B+ использует подобное для выполнения нечеткого запроса, слова, стоящие за подобным (например, начинающиеся с %), могут играть роль оптимизации, а хэш-индекс вообще не может выполнять нечеткий запрос.

35. В чем разница между внутренним соединением, левым соединением и правым соединением в mysql?

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

36. Расскажите о схеме инфраструктуры MySQL.

Диаграмма логической архитектуры Mysql в основном разделена на три уровня:

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

37. Что такое внутренние соединения, внешние соединения, перекрестные соединения и декартовы произведения?

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

38. Расскажите о трех парадигмах базы данных

Первая нормальная форма: каждый столбец (каждое поле) в таблице данных не может быть далее разделен.
Вторая нормальная форма: на основе первой нормальной формы столбец субпервичного ключа полностью зависит от первичного ключа и не может быть частью первичного ключа.
Третья нормальная форма: на основе выполнения второй нормальной формы непервичный ключ в таблице зависит только от первичного ключа и не зависит от других непервичных ключей.

39. Сколько таблиц о разрешениях есть в MySQL?

Сервер MySQL контролирует доступ пользователей к базе данных через таблицу разрешений, которая хранится в базе данных mysql и инициализируется сценарием mysql_install_db. Этими таблицами привилегий являются user, db, table_priv, columns_priv и host.

Таблица разрешений пользователей: записывает информацию об учетной записи пользователя, которой разрешено подключение к серверу, а разрешения в ней находятся на глобальном уровне.
таблица разрешений базы данных: запишите разрешения на операции каждой учетной записи в каждой базе данных.
Table_Priv Permissions Table: Запишите разрешение на операцию на уровне таблицы данных.
Таблица разрешений columns_priv: записывает разрешения операций на уровне столбцов данных.
Таблица разрешений хоста: взаимодействуйте с таблицей разрешений базы данных для более подробного управления разрешениями операций на уровне базы данных на данном хосте. На эту таблицу разрешений не влияют операторы GRANT и REVOKE.

40. Сколько форматов ввода есть в binlog Mysql? Какая разница?

Есть три формата: заявление, строка и смешанный.

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

row, не записывает связанную с контекстом информацию оператора SQL, а только сохраняет измененную запись. Единицей записи является изменение каждой строки, которое в принципе может быть записано, однако из-за множества операций будет изменено большое количество строк (типа alter table), поэтому файл в этом режиме сохраняет слишком много информации. и объем журнала слишком велик.

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

41. Вы поняли 4 основные особенности движка InnoDB?

вставить буфер
двойная запись
Адаптивный хэш-индекс (ahi)
читать вперед

42. Каковы преимущества и недостатки индексов?

преимущество:

Уникальный индекс может гарантировать уникальность каждой строки данных в таблице базы данных.
Индексы могут ускорить запросы данных и сократить время запроса

недостаток:

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

43. Какие типы индексов существуют?

Индекс первичного ключа: столбцы данных не могут быть дублированы, а NULL не разрешен. Таблица может иметь только один первичный ключ.
Уникальный индекс: столбцы данных не могут быть дублированы, разрешены значения NULL, а таблица позволяет нескольким столбцам создавать уникальные индексы.
Обычный индекс: базовый тип индекса без ограничения уникальности допускает значения NULL.
Полнотекстовое индексирование. В настоящее время это ключевая технология, используемая поисковыми системами для сегментации и поиска текстового контента.
Индекс покрытия: столбец запроса должен быть покрыт построенным индексом без чтения строки данных.
Комбинированный индекс: несколько значений столбца образуют индекс для комбинированного поиска, который более эффективен, чем слияние индексов.

44. Каковы принципы создания индексов?

Принцип сопоставления крайнего левого префикса
Индексы создаются только для полей, которые часто используются в качестве условий запроса.
Поля, которые часто обновляются, не подходят для индексации.
Столбцы индекса не могут участвовать в вычислениях и не могут выполнять функциональные операции.
Отдайте предпочтение расширению индексов вместо создания новых, чтобы избежать ненужных индексов.
В предложении order by или group by необходимо обратить внимание на порядок при создании индекса.
Столбцы данных с низким уровнем дискриминации не подходят для индексных столбцов (например, пол).
Столбцы данных с определенными внешними ключами должны быть проиндексированы.
Не создавайте индексы для столбцов, определенных как текстовые и графические типы данных.
Удалить индексы, которые больше не используются или используются редко

45. Три способа создания индекса

Создавать индексы при выполнении CREATE TABLE

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Добавьте индекс с помощью команды ALTER TABLE.

ALTER TABLE table_name ADD INDEX index_name (column);

Создается с помощью команды CREATE INDEX

CREATE INDEX index_name ON table_name (column);

46. Как вы удалили данные миллионов или более?

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

47. Что такое принцип самого левого префикса? Каков самый левый принцип соответствия?

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

Когда мы создаем комбинированный индекс, такой как (k1, k2, k3), это эквивалентно созданию трех индексов (k1), (k1, k2) и (k1, k2, k3), что является самым левым принципом сопоставления. .

48. В чем разница между деревом B и деревом B+?Почему база данных использует дерево B+ вместо дерева B?

В B-дереве ключи и значения хранятся как во внутренних узлах, так и в листовых узлах; в дереве B+ внутренние узлы хранят только ключи, а листовые узлы хранят и ключи, и значения.
Листовые узлы B+-дерева связаны цепочкой, а листовые узлы B-дерева независимы.

Все данные индекса дерева B+ хранятся в листовых узлах, и данные упорядочены по порядку, а связанный список связан. Затем дерево B+ делает поиск по диапазонам, поиск по сортировке, поиск по группам и поиск с дедупликацией чрезвычайно простыми. .

На нелистовых узлах дерева B+ не хранятся данные, хранится только значение ключа, а узел дерева B хранит не только значение ключа, но и данные. Размер страницы в innodb по умолчанию составляет 16 КБ.Если данные не будут сохранены, будет сохранено больше значений ключей, порядок соответствующего дерева (дерево дочерних узлов узла) будет больше, а дерево будет короче и толще Таким образом, количество операций ввода-вывода, необходимых для поиска данных для диска, снова сократится, а эффективность запроса данных будет выше.

49. Знаете ли вы о покрытии индексов, таблиц возврата и т. д.?

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

Эта статья в Интернете дает понять:mysql, покрывающий индекс и возвращаемую таблицу

50. Дереву B+ не нужно запрашивать данные обратно в таблицу, если оно удовлетворяет кластеризованному индексу и индексу покрытия?

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

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

51. Когда использовать кластеризованные и некластеризованные индексы

52. Будет ли некластеризованный индекс обязательно возвращать табличный запрос?

Не обязательно, если все поля оператора запроса попадут в индекс, тогда нет необходимости выполнять запрос обратно к таблице (ха-ха, вот что такое покрывающие индексы).

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

53. Что такое составной индекс? Почему нужно обращать внимание на порядок в составном индексе?

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

Из-за крайнего левого принципа индексной стратегии в движке InnoDB вам необходимо обратить внимание на порядок в комбинированном индексе.

54. Что такое транзакция базы данных?

Транзакция базы данных (называемая транзакцией) — это логическая единица в процессе выполнения системы управления базой данных. Она состоит из ограниченной последовательности операций с базой данных. Эти операции либо все выполняются, либо не выполняются, и являются неотделимой единицей Работа.

55. Связь между уровнем изоляции и блокировкой

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

Посмотрите мою статью:В этой статье подробно рассматриваются четыре уровня изоляции транзакций MySQL.

56. Что такое блокировки базы данных по степени детализации блокировок? Механизм блокировки и алгоритм блокировки InnoDB

В зависимости от степени детализации блокировки различают: блокировки таблицы, блокировки страниц, блокировки строк.
По механизму запирания различают: оптимистическое запирание, пессимистическое запирание

57. С точки зрения категорий блокировок, какие блокировки есть в MySQL?

Что касается категорий блокировки, существуют общие блокировки и эксклюзивные блокировки.

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

Совместимость блокировки следующая:

58. Как реализована блокировка строк механизма InnoDB в MySQL?

Блокировка строк выполняется на основе индекса.

select * from t where id = 666 for update;

for update может завершить блокировку строки на основе условий, а id — это столбец с ключом индекса.Если id не является ключом индекса, InnoDB реализует блокировку таблицы.

59. Что такое тупик? Как с этим бороться?

Тупик — это явление, при котором две или более транзакций занимают друг друга на одном и том же ресурсе и запрашивают блокировку ресурсов друг друга, что приводит к порочному кругу. Взгляните на изображение следующим образом:

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

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

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

В одной и той же транзакции попытайтесь заблокировать все необходимые ресурсы одновременно, чтобы уменьшить вероятность взаимоблокировки;

Для бизнес-частей, которые очень подвержены взаимоблокировкам, вы можете попытаться повысить степень детализации блокировки и уменьшить вероятность взаимоблокировок с помощью блокировки на уровне таблицы;

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

Взаимоблокировки неотделимы от индексов.Чтобы решить проблемы с индексами, вам необходимо разумно оптимизировать ваши индексы.

Заинтересованные друзья, вы можете прочитать мой анализ тупика:Научить вас анализировать проблему взаимоблокировки Mysql

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

Зачем использовать просмотры?

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

Что такое представление?

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

61. Каковы характеристики вида? Какие варианты использования?

Особенности просмотра:

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

Посмотреть использование:Упрощение SQL-запросов, повышение эффективности разработки и совместимость со старыми структурами таблиц.

Общие сценарии использования просмотров:

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

62. Достоинства и недостатки взглядов, расскажите об этом?

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

63. В чем разница между count(1), count(*) и count(имя столбца)?

count(*) включает все столбцы, что эквивалентно количеству строк.При подсчете результатов значение столбца NULL не будет игнорироваться.
count(1) включает игнорирование всех столбцов, используя 1 для представления строки кода, при подсчете результатов значение столбца NULL не будет игнорироваться
count (название столбца) включает только столбец имени столбца, при подсчете результатов игнорирует счетчик значения пустого столбца (пусто здесь не просто пустая строка или 0, а означает ноль), т. е. значение поля равно NULL, статистика не подсчитывается.

64. Что такое курсор?

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

65. Что такое хранимая процедура? Какие преимущества и недостатки?

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

преимущество:

Хранимая процедура представляет собой предварительно скомпилированный блок кода, выполнение которого более эффективно.
Хранимые процедуры выполняются на стороне сервера, что снижает нагрузку на клиента.
Позволяет модульное программирование.Вам нужно создать процесс только один раз, и вы можете вызывать процесс в программе любое количество раз позже, аналогично повторному использованию методов.
Хранимая процедура заменяет большое количество операторов T_SQL, что может уменьшить сетевой трафик и повысить скорость связи.
Безопасность данных может быть обеспечена в определенной степени

недостаток:

проблема с отладкой
Портативность не является гибкой
Перекомпилировать проблему

66. Что такое триггер? Каковы сценарии использования триггеров?

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

используемые сцены:

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

67. Какие триггеры есть в MySQL?

В базе данных MySQL существует шесть типов триггеров:

Before Insert
After Insert
Before Update
After Update
Before Delete
After Delete

68. Что такое суперключ, ключ-кандидат, первичный ключ и внешний ключ?

Суперключ: в реляционной схеме набор атрибутов, которые могут однозначно идентифицировать кортеж, называется суперключом.
Ключ-кандидат: это наименьший суперключ, то есть суперключ без лишних элементов.
Первичный ключ: комбинация столбцов данных или атрибутов в таблице базы данных, которая однозначно и полностью идентифицирует хранимый объект данных. Столбец данных может иметь только один первичный ключ, и значение первичного ключа не может отсутствовать, то есть оно не может быть нулевым.
外键：在一个表中存在的另一个表的主键称此表的外键。 .

69. Какие существуют ограничения SQL?

NOT NULL: содержимое поля ограничения не должно быть NULL.
UNIQUE: ограничивает уникальность поля, таблица допускает несколько уникальных ограничений.
ПЕРВИЧНЫЙ КЛЮЧ: Поле ограничения уникально и не может повторяться. Допускается существование только одной таблицы.
ВНЕШНИЙ КЛЮЧ: используется для предотвращения действий, разрушающих связь между таблицами, а также может предотвратить вставку недопустимых данных во внешние ключи.
ПРОВЕРКА: используется для контроля диапазона значений поля.

70. Расскажите о шести связанных запросах и сценариях использования.

перекрестное соединение
внутреннее соединение
внешнее соединение
совместный запрос
полностью подключен
перекрестное соединение

71. Значение 50 в varchar(50)

Поля могут содержать до 50 символов
Например, varchar(50) и varchar(200) хранят строку «jay» в одном и том же пространстве, последний будет потреблять больше памяти при сортировке.

72. Разница между int(20) и char(20) и varchar(20) в mysql

int(20) указывает, что поле имеет тип int и длина отображения равна 20.
char(20) указывает, что поле представляет собой строку фиксированной длины длиной 20
varchar(20) указывает, что поле представляет собой строку переменной длины длиной 20

73. Разница между удалением, удалением и усечением

	delete	truncate	drop
тип	DML	DDL	DDL
откат	откат	нельзя откатить	нельзя откатить
удалить контент	Структура таблицы все еще существует, удалите все или часть строк данных таблицы	Структура таблицы осталась, удалите все данные в таблице	Удалить таблицу из базы данных, все строки данных, индексы и разрешения также удаляются
удалить скорость	Медленное удаление, удаление построчно	удалить быстро	Самое быстрое удаление

74. В чем разница между UNION и UNION ALL?

Объединение: выполнение операции объединения двух результирующих наборов, исключая повторяющиеся строки, и одновременная сортировка по правилам по умолчанию;
Объединение всех: выполнение операции объединения двух наборов результатов, включая повторяющиеся строки, без сортировки;
UNION более эффективен, чем UNION ALL

75. Жизненный цикл SQL?

Сервер устанавливает соединение с базой данных

Процесс базы данных получает запрос sql

Разобрать и сгенерировать план выполнения, выполнить

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

Через подключение на шаге 1 отправляем результат клиенту

Закройте соединение и освободите ресурсы

76. Порядок выполнения Sql?

77. Если значение столбца равно NULL, будет ли запрос использовать индекс?

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

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

78. Вы когда-нибудь заботились о трудоемком sql в бизнес-системе? Статистика слишком медленная для запросов? Как оптимизировать медленный запрос?

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

Оптимизация медленных запросов:

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

Посмотрите мою статью:Необходимые вещи для back-end программиста: 30 советов по написанию высококачественного SQL

79. Почему следует использовать автоинкрементный идентификатор или UUID для первичного ключа?

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

Самоувеличивающийся идентификатор: небольшое пространство для хранения данных и высокая эффективность запросов. Однако, если объем данных слишком велик, он превысит диапазон самовозрастающих значений, и могут возникнуть проблемы с объединением нескольких баз данных.
uuid: Он подходит для операций вставки и обновления большого количества данных, но он неупорядочен, эффективность вставки данных низкая и занимает много места.

80. Что делать, если первичный ключ автоинкремента mysql исчерпан?

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

81. Почему поле должно быть определено как ненулевое?

Значения Null занимают больше байт, а у null много подводных камней.

82. Если нужно сохранить хэш пароля пользователя, какое поле следует использовать для его хранения?

Строки фиксированной длины, такие как хэши паролей, соли и идентификационные номера пользователей, должны храниться в формате char вместо varchar, что экономит место и повышает эффективность поиска.

83. Что такое драйвер Mysql?

Этот пакет jar: mysql-connector-java-5.1.18.jar

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

84. Как оптимизировать длинные и сложные запросы? Вы когда-нибудь дрались?

Разбить большой запрос на несколько меньших одинаковых запросов
Уменьшите количество запросов на избыточные записи.
Сложный запрос можно рассматривать как разделенный на несколько простых запросов.
Разложите ассоциативные запросы, чтобы сделать кэширование более эффективным.

85. Оптимизируйте определенные типы запросов

Накопить как обычно:

Например, используйте select определенных полей вместо select *
Используйте count(*) вместо count(column_name)
Используйте кеш, не влияя на бизнес
объяснить, чтобы проанализировать ваш SQL

Посмотрите мою статью:Необходимые вещи для back-end программиста: 30 советов по написанию высококачественного SQL

86. Что делать, если зашкаливает ЦП базы данных MySQL?

Процесс расследования:

Используйте команду top, чтобы определить, вызвано ли это mysqld или другими причинами.
Если это вызвано mysqld, покажите список процессов, проверьте состояние сеанса и определите, выполняется ли какой-либо SQL, потребляющий ресурсы.
Узнать sql с большим потреблением, посмотреть, верен ли план выполнения, не пропал ли индекс, не слишком ли большой объем данных.

иметь дело с:

убейте эти потоки (и посмотрите, падает ли загрузка процессора),
Внесите соответствующие корректировки (такие как добавление индексов, изменение sql и изменение параметров памяти)
Rerun SQL.

Другие случаи:

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

87. Общие решения для разделения чтения и записи?

Прикладная программа оценивает в соответствии с бизнес-логикой и отправляет команды операции записи, такие как добавление, удаление и изменение, в основную базу данных, а также отправляет команды запроса в резервную базу данных.
Используйте промежуточное ПО в качестве прокси, отвечающего за идентификацию запросов на чтение или запись в базу данных, и распределяйте их по разным базам данных. (например: амеба, mysql-прокси)

88. Принцип и процесс репликации MySQL

Короче говоря, принцип репликации master-slave представляет собой трехэтапный процесс, а именно:

Основная база данных имеет двоичный файл bin-log, в котором записываются все добавления, удаления и изменения операторов SQL. (тема бинлога)
Скопируйте оператор sql файла bin-log основной базы данных из базы данных. (io нить)
Выполните эти операторы sql еще раз из файла журнала повторов relay-log базы данных. (поток выполнения SQL)

Как показано ниже:

Репликация master-slave на приведенном выше рисунке разделена на пять этапов:

Шаг 1: События обновления (обновление, вставка, удаление) основной библиотеки записываются в binlog.

Шаг 2: Инициируйте подключение из библиотеки и подключитесь к основной библиотеке.

Шаг 3: В это время основная библиотека создает поток дампа binlog и отправляет содержимое binlog в подчиненную библиотеку.

Шаг 4: После запуска подчиненной библиотеки создайте поток ввода-вывода, прочитайте содержимое binlog из основной библиотеки и запишите его в журнал реле.

Шаг 5: Также будет создан поток SQL для чтения содержимого из журнала ретрансляции, выполнения события обновления чтения из позиции Exec_Master_Log_Pos и записи обновленного содержимого в базу данных подчиненного устройства.

89. Разница между DATETIME и TIMESTAMP в MySQL

Точность хранения в секундах

разница:

Диапазон дат DATETIME — 1001–9999, диапазон времени TIMESTAMP — 1970–2038.
DATETIME хранит время независимо от часового пояса; TIMESTAMP хранит время в зависимости от часового пояса, и отображаемое значение также зависит от часового пояса
DATETIME имеет 8 байтов памяти, TIMESTAMP имеет 4 байта памяти.
Значение по умолчанию DATETIME равно null; поле TIMESTAMP по умолчанию не равно null, а значением по умолчанию является текущее время (CURRENT_TIMESTAMP).

90. Каков принцип реализации транзакций Innodb?

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

91. Расскажите о MySQL Объясните

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

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

system  > const > eq_ref > ref  > ref_or_null >
index_merge > unique_subquery > index_subquery > 
range > index > ALL

92. Реализация транзакций и журналов Innodb

Сколько существует видов бревен

Innodb имеет два типа журналов: повтор и отмена.

формат хранения журнала

повтор: когда страница изменяется, она сначала записывается в буфер журнала повторов, затем записывается в кэш файловой системы журнала повторов (fwrite), а затем синхронизируется с файлом на диске (fsync).
Отмена: до версии MySQL 5.5 отмена может храниться только в файле ibdata, после версии 5.6 журнал отмен может храниться вне ibdata путем установки параметра innodb_undo_tablespaces.

Как транзакции реализуются через ведение журнала

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

93. Максимальная длина типа данных TEXT в MySQL

TINYTEXT: 256 байт
ТЕКСТ: 65 535 байт (64 КБ)
СРЕДНЕТЕКСТ: 16 777 215 байт (16 МБ)
ДЛИННЫЙ ТЕКСТ: 4 294 967 295 байт (4 ГБ)

94. 500 дБ, перезапуск в кратчайшие сроки.

Пакетный инструмент ssh pssh можно использовать для выполнения команд перезагрузки на машинах, которые необходимо перезагрузить.
Вы также можете использовать многопоточные инструменты, такие как соль (при условии, что клиент установил соль) или ansible (для этого требуется только бесплатный вход по ssh) и другие многопоточные инструменты для одновременной работы нескольких служб.

95. Как вы контролируете свою базу данных? Как вы запрашиваете свои медленные журналы?

Есть много инструментов мониторинга, таких как zabbix, lepus, здесь я использую lepus

96. Выполнили ли вы проверку согласованности ведущий-ведомый? Если да, то как вы это сделали? Если нет, что вы собираетесь делать?

Существуют различные инструменты для проверки согласованности master-slave, такие как контрольная сумма, mysqldiff, pt-table-checksum и т. д.

97. Поддерживает ли ваша база данных хранение выражений эмодзи, если нет, то как с ней работать?

Заменить набор символов utf8 -> utf8mb4

98. Как MySQL получает текущую дату?

SELECT CURRENT_DATE();

99. 600-миллионная таблица a, 300-миллионная таблица b, через внешнюю ассоциацию tid, как вы можете быстро запросить 200 записей данных с 50000-го по 50200-е, которые удовлетворяют условиям.

1. Если TID таблицы A является самовозрастающим и непрерывным, то ID таблицы B является индексом выберите * из a,b, где a.tid = b.id и a.tid>500000 limit 200;

2. Если TID таблицы A не является непрерывным, то необходимо использовать покрывающий индекс, TID является либо первичным ключом, либо вспомогательным индексом, а ID таблицы B также должен иметь индекс. выберите * из b , (выберите tid из предела 50000,200) a где b.id = a .tid;

100. Анализ блокировки MySQL SQL

Блокировку SQL можно разделить на 9 ситуаций:

Комбинация 1: столбец id является первичным ключом, уровень изоляции RC
Комбинация 2: столбец id является вторичным уникальным индексом, уровень изоляции RC
Комбинация 3: столбец id является вторичным неуникальным индексом, уровень изоляции RC
Комбинация 4: в столбце id нет индекса, уровень изоляции RC
Комбинация пятая: столбец id является первичным ключом, уровень изоляции RR
Комбинация шестая: столбец id — вторичный уникальный индекс, уровень изоляции RR
Комбинация 7: столбец id является вторичным неуникальным индексом, уровень изоляции RR
Комбинация 8: в столбце id нет индекса, уровень изоляции RR
Комбинация девять: Сериализуемый уровень изоляции

публика

Добро пожаловать, чтобы обратить внимание на мой личный публичный аккаунт, подружиться и учиться вместе ~
Если ответ неверный, укажите на это, большое спасибо~

предисловие

база данных

1. Каковы меры предосторожности при использовании индексов MySQL?

При каких обстоятельствах индекс не сработает

Для каких сценариев не подходят индексы?

Некоторые негласные правила индексации

2. Сталкивалась ли MySQL с проблемой взаимоблокировки и как вы ее решили?

3. Как вы оптимизируете SQL в своей повседневной работе?

4. Расскажите о дизайне подбазы данных и подтаблицы.

5. Разница между InnoDB и MyISAM

6. Принцип индексации базы данных, зачем использовать дерево B+, почему бы не использовать бинарное дерево?

7. Разница между кластеризованным индексом и некластеризованным индексом

8. Если ограничение в 1000000 загружается медленно, как вы решили эту проблему?

Вариант 1: Если id непрерывный, можно сделать так, вернуть максимальную запись (смещение) последнего запроса, а потом спуститься на лимит

Вариант 2. Ограничьте количество страниц, если бизнес позволяет:

Вариант 3: упорядочить по + индексу (id - это индекс)

9. Как выбрать подходящую схему распределенного первичного ключа?

10. Каковы уровни изоляции транзакций? Каков уровень изоляции MySQL по умолчанию?

11. Что такое фантомное чтение, грязное чтение и неповторяемое чтение?

12. Как безопасно изменять одну и ту же строку данных в случае высокой степени параллелизма?

Используйте пессимистическую блокировку

Используйте оптимистическую блокировку

13. Оптимистическая блокировка и пессимистическая блокировка базы данных.

Пессимистический замок:

Оптимистичная блокировка:

14. Каковы общие шаги оптимизации SQL, как посмотреть план выполнения (объяснить) и как понять значение каждого поля.

15. Что означает выбор для обновления, будет ли он блокировать таблицу, строку или другое.

выбрать для обновления Значение

выбрать для проверки блокировки обновления

16. Четыре характеристики транзакций MySQL и принципы их реализации

17. Если в таблице почти 10 миллионов данных, CRUD относительно медленный, как его оптимизировать.

Подбиблиотека и подтаблица

Оптимизация индекса

18. Как написать sql для эффективного использования составных индексов.

19. Разница между in и существует в mysql.

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

21. Знакомы ли вы с MVCC и его основными принципами?

22. Слышали ли вы о промежуточном программном обеспечении базы данных, сегментировании jdbc, mycat?

23. Как вы решаете задержку master-slave в MYSQL?

Причины задержки синхронизации master-slave

Решение задержки синхронизации ведущий-ведомый

24. Расскажите о схеме оптимизации запроса к большой таблице.

25. Что такое пул соединений с базой данных Зачем нужен пул соединений с базой данных?

26. Как выполняется оператор SQL в MySQL?

27. Знаете ли вы стратегию индексирования в движке InnoDB?

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

29. Как оптимизировать SQL, выполнение которого занимает слишком много времени, и с чего начать?

30. Какие есть команды метода для анализа производительности сервера базы данных MYSQL?

31. В чем разница между блобом и текстом?

32. Какой тип поля лучше использовать для записи валюты в mysql?

33. Какие виды блокировок есть в Mysql, перечислите их?

34. В чем разница между хэш-индексом и деревом B+? Что вы выбрали при разработке индекса?

35. В чем разница между внутренним соединением, левым соединением и правым соединением в mysql?

36. Расскажите о схеме инфраструктуры MySQL.

37. Что такое внутренние соединения, внешние соединения, перекрестные соединения и декартовы произведения?

38. Расскажите о трех парадигмах базы данных

39. Сколько таблиц о разрешениях есть в MySQL?

40. Сколько форматов ввода есть в binlog Mysql? Какая разница?

41. Вы поняли 4 основные особенности движка InnoDB?

42. Каковы преимущества и недостатки индексов?

43. Какие типы индексов существуют?

44. Каковы принципы создания индексов?

45. Три способа создания индекса

46. ​​Как вы удалили данные миллионов или более?

47. Что такое принцип самого левого префикса? Каков самый левый принцип соответствия?

48. В чем разница между деревом B и деревом B+?Почему база данных использует дерево B+ вместо дерева B?

49. Знаете ли вы о покрытии индексов, таблиц возврата и т. д.?

50. Дереву B+ не нужно запрашивать данные обратно в таблицу, если оно удовлетворяет кластеризованному индексу и индексу покрытия?

51. Когда использовать кластеризованные и некластеризованные индексы

52. Будет ли некластеризованный индекс обязательно возвращать табличный запрос?

53. Что такое составной индекс? Почему нужно обращать внимание на порядок в составном индексе?

54. Что такое транзакция базы данных?

55. Связь между уровнем изоляции и блокировкой

56. Что такое блокировки базы данных по степени детализации блокировок? Механизм блокировки и алгоритм блокировки InnoDB

57. С точки зрения категорий блокировок, какие блокировки есть в MySQL?

58. Как реализована блокировка строк механизма InnoDB в MySQL?

59. Что такое тупик? Как с этим бороться?

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

61. Каковы характеристики вида? Какие варианты использования?

62. Достоинства и недостатки взглядов, расскажите об этом?

46. Как вы удалили данные миллионов или более?