По поводу mysql сделал резюме!

MySQL опрос

Сокращение операций ввода-вывода с помощью покрывающих индексов

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

# name是索引字段
1. SELECT id,name from user WHERE name='假装懂编程'#不需要回表
2. SELECT id,name,age from user WHERE name='假装懂编程' #需要回表

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

не используйте выбор *

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

  • Определенно не используйте покрывающий индекс.
  • Дополнительные поля вызовут нагрузку на синтаксический анализ mysql.
  • Дополнительные поля увеличивают нагрузку на сеть.

сложный sql для объяснения

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

mysql> explain select id,name from user3 where name="假装懂编程";
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user3 | NULL       | ref  | name          | name | 403     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ref — относительно важный показатель (чем дальше по порядку, тем ниже эффективность):

system->const->eq_ref->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->all。

Особенно, когда мы объясняемref=allКогда вам нужно обратить внимание, в это время ваш sql должен выполнить полное сканирование таблицы.

Разница между varchar и char

char

Фиксированная длина (количество символов), если длина меньше определенной длины, она будет дополнена пробелами, которые будут занимать место впустую. Однако, поскольку длина фиксирована, скорость доступа к char выше, чем у varchar, и char может хранить до 255 символов независимо от кодировки.

varchar

Переменная длина (количество символов), если длина меньше определенной длины, она будет сохранена в соответствии с фактической длиной, что сэкономит место по сравнению с char, но поскольку это переменная длина, скорость доступа ниже, чем у char . С точки зрения хранения, если длина столбца меньше или равна 255 байтам, для записи длины требуется дополнительный 1 байт, а если длина столбца больше 255 байт, требуются дополнительные 2 байта. для записи длины.

Остерегайтесь ловушек ограничения при пейджинге

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

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

select * from message limit 0,100 # 第一页
select * from message limit 100,100 #第二页
...
select * from message limit 1000000,100 #第10000页

Когда мы находим 10 000-ю страницу, нам нужно отфильтровать 1 000 000 фрагментов данных, а затем получить фрагменты данных 100. Это требует огромных затрат времени. Суть этой проблемы еще в том, что индекс не используется, тогда мы можем использовать индекс для пейджинга, мы можем решить это так:
Поскольку идентификатор первичного ключа является самоувеличивающимся и непрерывным, мы каждый раз находим наш первый фрагмент данных по идентификатору, а затем извлекаем 100 фрагментов в обратном порядке. Этот идентификатор является самым большим идентификатором в данных о подкачке, которые вы получили в прошлый раз.

select * from message where id>=[id] limit 100

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

Лимит, который нужно вернуть в таблицу

Сейчас есть такая таблица, кроме индекса первичного ключа есть ещеuser_idобщий индекс

CREATE TABLE `message` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB;

Объем данных таблицы составляет почти 150 Вт:

mysql> select count(*) from message;
+----------+
| count(*) |
+----------+
|  1496067 |
+----------+

я буду считатьuser_id=99999пользовательские данные, а из всех данных берутся только последние 5 элементов, начиная с 70w, поэтому я выполнил это так:

mysql> select * from message where user_id=99999 limit 700000,5;
+---------+---------+---------+
| id      | user_id | name    |
+---------+---------+---------+
| 1282606 |   99999 | t154458 |
| 1282607 |   99999 | t154459 |
| 1282608 |   99999 | t154460 |
| 1282609 |   99999 | t154461 |
| 1282610 |   99999 | t154462 |
+---------+---------+---------+
5 rows in set (1.17 sec)

обнаружил, что это необходимо1.17sВ то время user_id не индексируется, и я получаю только 5 фрагментов данных, я также вижу, что он используется через объяснениеuser_idпоказатель.

mysql> explain select * from message where user_id=99999 limit 700000,5;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | message | NULL       | ref  | user_id       | user_id | 4       | const | 745650 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------+

После анализа я нашел некоторые подсказки, поэтому я выполнил это следующим образом:

mysql> select a.* from message a join (select id from message where user_id=99999 limit 700000,5) b on a.id=b.id;
+---------+---------+---------+
| id      | user_id | name    |
+---------+---------+---------+
| 1282606 |   99999 | t154458 |
| 1282607 |   99999 | t154459 |
| 1282608 |   99999 | t154460 |
| 1282609 |   99999 | t154461 |
| 1282610 |   99999 | t154462 |
+---------+---------+---------+
5 rows in set (0.14 sec)

найти просто нужно0.14s, что экономит почти1sвремя.

В заключение:

Во-первых, потому что вы запрашиваете *, а это значит, что вам нужно получить все поля, то даже если вы используете индекс user_id, вам в конечном итоге придется вернуться к таблице для проверки. Таким образом, для части данных общее потребление равноВремя запроса индекса user_id + время запроса индекса идентификатора первичного ключа, а во-вторых, потому что вы использовалиlimit 70000,5, т.к. данные user_id=99999 очень большие, если лимит пройден, данные 70w надо фильтровать, так что(Время запроса индекса user_id + время запроса индекса идентификатора первичного ключа) умножается на70wВсе это потребление тратится впустую. Для второго sql сначала используется подзапрос для получения идентификатора первичного ключа:

select id from message where user_id=99999 limit 700000,5

Мы знаем, что общий индекс содержит id первичного ключа в дополнение к собственному ключу, поэтому нет необходимости возвращать таблицу для этого sql, и его общий расточительный расходВремя запроса для индекса user_idумножить на70w, 5 идентификаторов, полученных с помощью подзапроса, наконец, нужно использовать только5умножить наВремя запроса индекса идентификатора первичного ключадля получения необходимых данных. В целом, второй sql экономит больше, чем первый sql.Время запроса индекса идентификатора первичного ключаумножить на70wпотребление, поэтому первый намного медленнее.

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

Объединение нескольких полей для создания индекса называется объединенным индексом.Как правило, целью совместного индекса является определение фрагмента данных с помощью нескольких полей. Например, имя не может найти человека, потому что есть много людей с таким же именем, ноИмя+семейный адреснайти человека. Затем имя и домашний адрес можно объединить для создания уникального индекса. Обратите внимание, что уникальный ключ (имя, домашний адрес) и уникальный ключ (домашний адрес, имя) не совпадают. Общий вопрос заключается в том, может ли следующий запрос использовать индекс, предполагая, что существует объединенный индекс (a,b,c):

  • Вопрос 1:
select * from xx where a=1 and b=2 and c=3

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

  • вопрос 2:
select * from xx where and b=2 and c=3

Это не соответствует самому левому принципу, поэтому индекс не используется.

  • Вопрос 3:
select * from xx where and b=2 and a=1

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

  • Вопрос 4:
select * from xx where and a=1 and c=3

Это a может использовать индекс, c не использует индекс.

Проблема неявного преобразования чисел

Допустим сейчас есть такая таблица:

CREATE TABLE `user` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB;

вuser_idЭто поле является字符串тип, а также索引. В это время нам нужно проверить информацию о данных, чей user_id равен 100000. Итак, мы написали следующий sql:

select * from user where user_id=100000;

Если ничего другого, ваш sql будет медленным, когда ваша таблица уже очень большая.

explain select * from user where user_id=100000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | user3 | NULL       | index | user_id       | user_id | 1023    | NULL | 315384 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+

фокус:ОбнаружитьrowsОтображенные данные оказались полным сканированием таблицы. Очевидно, что user_id проиндексирован, почему все еще выполняется полное сканирование таблицы? Причина этой проблемы в том, что user_id является строкой, а значение, которое вы даете, является целым числом (user_id не добавляет одинарные кавычки).В mysql, если строка сравнивается с числом, строка преобразуется в число, а затем по сравнению, то есть наш sql эквивалентен:

select * from user where CAST(user_id AS signed int)=100000;

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

explain select * from user where user_id='100000';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user3 | NULL       | ref  | user_id       | user_id | 1023    | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+

Тогда вы найдетеrowsЗначение равно 1.

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

  • пройти черезtopКоманда подтверждает использование процессора.
  • пройти черезshow processlistдля просмотра текущего состояния потока mysql.
  • пройти черезshow OPEN TABLES where In_use > 0для просмотра состояния блокировки таблицы.
  • через mysqlerror logчтобы проверить на ошибки.
  • пройти черезshow engine innodb status\G; чтобы увидеть, есть ли взаимоблокировка.
  • пройти черезжурнал медленных запросовПроверьте медленные запросы.
  • пройти черезiostatдля просмотра ситуации с диском io.

Разница между датой и временем и отметкой времени

  • пространство:
    • datetime занимает 8 байт.
    • временная метка занимает 4 байта.
  • Указывает диапазон:
    • дата и время:1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
    • отметка времени:1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999

Суммировать:

datetime занимает больше места, но может представлять более длительное время, отметка времени занимает меньше места и может поддерживать только до 2038 года.

Оптимизировать запросы с ограничением 1

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

select * from student where from="shanghai";

Этот sql извлечет все данные о студентах из Шанхая, и нам нужно только знать, есть ли какие-либо студенты из Шанхая, поэтому его можно оптимизировать с помощью ограничения 1:

select * from student where from="shanghai" limit 1;

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

Как обеспечить уникальность идентификатора после разделения таблицы?

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

  • Установите размер шага.Например, мы устанавливаем базовый размер шага от 1 до 10 для 10 таблиц, чтобы разные таблицы не имели одинаковый идентификатор первичного ключа.
SET auto_increment_offset=1; # 从1开始
SET auto_increment_increment=10; # 每次以10增长
1 11 21 ... # 第一张表
2 12 22 ... # 第二张表
...
10 20 30 ... # 第十张表

Этот способ подходит для архитектуры М-М.

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

Зачем использовать НЕ NULL

Для поля можно указать значение по умолчаниюNULL, вы также можете указать значение по умолчаниюNOT NULL, и я предлагаю установить лучшийNOT NULL, в основном по следующим причинам:

  • Значения NULL занимают место в mysql, а значения null не занимают места.

NULL columns require additional space in the row to record whether their values are NULL.

mysql> select length(NULL), length(''), length('1');
+--------------+------------+-------------+
| length(NULL) | length('') | length('1') |
+--------------+------------+-------------+
|         NULL |          0 |           1 |
+--------------+------------+-------------+
  • Обратите внимание на условия оценки, когда поле допускает null:
    1. Фильтр нулевого значения должен быть:
select * from xx where name is not null

   2. 空值的过滤：

select * from xx where name!=""
  • count не считает столбцы с нулевыми значениями:
mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  2 | NULL |
|  1 | tom  |
+----+------+
2 rows in set (0.00 sec)

mysql> select count(name) from user;
+-------------+
| count(name) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)
  • При сортировке (от меньшего к большему) нулевые значения будут стоять первыми:
mysql> select * from user order by sort asc;
+----+------+
| id | sort |
+----+------+
|  3 | NULL |
|  4 | NULL |
|  1 |    1 |
|  2 |    2 |
+----+------+

mysql считает null меньше, чем любое значение.

Может ли уникальное поле индекса быть NULL?

разрешено. Хотя уникальный индекс ограничивает уникальность каждого значения, он беспомощен против null.Null — это особое существование в mysql, и обычно рекомендуется использовать NOT NULL.

CREATE TABLE `user` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

insert into user (user_id) values (1),(null),(null);
mysql> select * from user order by user_id;
+----+---------+
| id | user_id |
+----+---------+
|  2 | NULL    |
|  3 | NULL    |
|  1 | 1       |
+----+---------+

Можно обнаружить, что два нулевых значения вставлены успешно.

Как работать с таблицей плюс полями онлайн 100 миллионов данных

  • Попробуйте выбрать малопиковую обработку
  • MySQL 5.7 поддерживает онлайн DDL, основные алгоритмы:
    • ALGORITHM=INPLACE: таблицу необходимо перестроить (Примечание: перестроение требуется для добавления столбца, переименования столбца, добавления индекса не требует перестроения), DML все еще может выполняться в течение этого периода, и сохраняется хорошая производительность параллелизма.
    • ALGORITHM=COPY: требуется перестроить таблицу, одновременные операции записи DML не разрешены, и она доступна для чтения.

rebuild: Включая реконструкцию таблицы, предпосылкой является обеспечение достаточного места на диске. При перестроении будут созданы новые файлы .frm и .ibd по исходному пути к таблице, что потребует больше операций ввода-вывода. И во время перестроения пространство журнала строк будет использоваться для записи операций DML во время выполнения DDL.Эта часть операций будет синхронизирована с новым табличным пространством после завершения DDL.

no-rebuild: Не включает реконструкцию таблицы.За исключением добавления индекса, который будет генерировать некоторые операции записи вторичного индекса, остальные операции только изменяют элементы метаданных, то есть создается только файл .frm под исходной таблицей. путь, и журнал строк не будет применяться. Будет потреблять слишком много операций ввода-вывода, обычно очень быстро.
задержка репликации: В архитектуре ведущий-подчиненный ведущий выполняет DDL во время выполнения DML.Если подчиненный является одним потоком sql для последовательного выполнения команд из журнала ретрансляции, DML в течение этого периода должен ждать выполнения DDL подчиненного устройства на подчиненный для синхронизации. Поэтому при сравнении давления ввода-вывода это может вызвать задержку репликации.

  • В производственной среде рекомендуется использовать сторонние инструменты, такие как pt-osc/gh-ost, для добавления столбцов в режиме онлайн.
  • MySQL 8.0 может быстро добавлять столбцы с помощью мгновенного метода, нужно только изменить информацию метаданных, стоимость невелика, и это может быть выполнено за считанные секунды. но будь доволенне сжатая таблица,не табличное пространство словаря данных,не полнотекстовый индекс,не временная таблица,Добавить столбец в последнюю позицию.

Разница между count(1), count(*) и count(column)

В бизнесе часто подсчитывается некоторая информация, которая неотделима от функции подсчета mysql.Икс в count(x) может быть разным.На самом деле в некоторых случаях они одинаковые,а в некоторых случаях разные.

  • Счетчик (*) движка myisam самый быстрый, потому что он не требует статистики в реальном времени, он сохраняется, но предпосылка заключается в том, что условие where нельзя добавить.
  • Нет разницы между count(1) и count(*), в count(*)*Это не все данные, как предполагалось, давайте посмотрим на пример:
CREATE TABLE `user_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB;

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

#count(*)
mysql> explain select count(*) from user_info;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
#count(1)
mysql> explain select count(1) from user_info1;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

Через объяснение выясняется, что оба используют индекс первичного ключа, нет никакой разницы. Итак, count(1) и count(*) учитываются индексом первичного ключа?

  • Покрытие оптимизации индекса для count(*) и count(1):
CREATE TABLE `user_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY (`user_id`)
) ENGINE=InnoDB;

Мы добавили индекс user_id:

#count(*)
explain select count(*) from user_info;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | index | NULL          | user_id | 4       | NULL |    1 |   100.00 | Using index |

#count(1)
explain select count(1) from user_info;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | index | NULL          | user_id | 4       | NULL |    1 |   100.00 | Using index |

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

  • count(column): Если значение столбца может быть NULL, то значение NULL не будет учитываться, как описано выше.
mysql> select * from user;
+----+------+---------------------+
| id | name | ctime               |
+----+------+---------------------+
|  1 | tom  | 2021-08-27 08:45:50 |
|  2 | NULL | 2021-08-27 08:45:50 |
|  3 | NULL | 2021-08-27 08:46:18 |
+----+------+---------------------+
3 rows in set (0.00 sec)

mysql> select count(name) from user;
+-------------+
| count(name) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)
  • count(1), count(2)... count(10086) не имеет значения.

Разница между левым соединением, правым соединением и внутренним соединением

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

Уведомление: Используйте маленький стол для управления большим столом.

Будьте осторожны или не используйте индексы

CREATE TABLE `user_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11),
  `name` varchar(10),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Предполагая, что таблица user_info имеет только один индекс первичного ключа, нам нужно проверитьid=1илиuser_id=2Данные:

mysql> explain select * from user_info where id=1 or user_id=2;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info5 | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

тип оказалсяall(полное сканирование таблицы), решение состоит в том, чтобы добавить индекс к user_id:

alter table user_info add index user_id(`user_id`)
mysql> explain select * from user_info where id=1 or user_id=2;
+----+-------------+------------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                     |
+----+-------------+------------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
|  1 | SIMPLE      | user_info6 | NULL       | index_merge | PRIMARY,user_id | PRIMARY,user_id | 4,5     | NULL |    2 |   100.00 | Using union(PRIMARY,user_id); Using where |
+----+-------------+------------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+

Почему такие поля, как пол, не подходят для индексации

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

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

Суммировать:

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

копировать эти вещи

Традиционная копия (файл+поз)

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

Репликация на основе GTID

Недостатки традиционной репликации: Режим репликации на основе файла (бинлог) + позиция (смещение копии) в основном существует, когда мастер отказывает, а слейв-мастер, другие слейвы не могут скопировать новый мастер через файл + позиция. Так появляется репликация паттерна GTID:

концепция:

GTID (Global Transaction ID) — это номер совершенной транзакции, поддерживаемый MySQL 5.6, это глобально уникальный номер. GTID на самом деле состоит из UUID+TID. Где UUID — уникальный идентификатор экземпляра MySQL. TID представляет количество совершенных транзакций в этом экземпляре и монотонно увеличивается по мере фиксации транзакций. Ниже приведена конкретная форма GTID:3E11FA47-71CA-11E1-9E33-C80AA9429562:23, двоеточие разделяется uuid до и TID после.

Принцип работы:
  1. Когда транзакция выполняется и фиксируется на мастере, генерируется GTID и записывается в бинарный журнал.
  2. После того, как ведомое устройство получает изменение, прочитав GTID, оно устанавливает его в переменную gtid_next, которая является следующим значением GTID, которое необходимо выполнить.
  3. Поток sql получает GTID из журнала реле, а затем сравнивает, есть ли GTID в бинлоге на ведомой стороне.Если есть запись, это означает, что транзакция GTID была выполнена, и ведомое устройство будет ее игнорировать. Если записи нет, ведомое устройство выполнит транзакцию GTID и запишет GTID в свой собственный binlog.

Метод копирования

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

mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
  • sync_binlog=0: указывает на то, что MySQL не контролирует обновление binlog, а сама файловая система сбрасывает его в зависимости от ситуации. На этот раз производительность лучшая, но и риск самый большой. Потому что в случае сбоя вся информация binlog в binlog_cache будет потеряна.
  • sync_binlog=1: Указывает, что MySQL будет сбрасывать бинарный журнал в файловую систему каждый раз, когда транзакция фиксируется, что является самым безопасным параметром с наибольшей потерей производительности, и еще одна транзакционная потеря данных.
  • sync_binlog=N: если N не равно 0 или 1, метод обновления аналогичен sync_binlog=1, за исключением того, что частота обновления будет увеличена до N раз позже.

Асинхронная репликация:

Режим репликации mysql по умолчанию — асинхронный, то есть после того, как мастер синхронизирует бинлог со слейвом, ему все равно, получит его слейв или нет.

Полная синхронная репликация:

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

Полусинхронная репликация:

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

Многопоточная репликация:

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

  • В версии MYSQL5.6 многопоточная репликация осуществляется на уровне базы данных.Транзакции в нескольких базах данных разбиваются на несколько потоков для выполнения в соответствии с базой данных, чтобы обеспечить согласованность транзакций на уровне базы данных. Однако практических приложений не так много, и большинство из них представляют собой сценарии с одной библиотекой и несколькими таблицами.
  • После версии MYSQL5.7 существует способ, основанный на логических часах, который может одновременно выполнять воспроизведение журнала ретрансляции в базе данных (например, обновление таблицы x и обновление таблицы y, они могут выполняться одновременно, если нет конфликта транзакций между их).

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

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

Зачем использовать дерево b+ вместо дерева b

  • Поскольку B-дерево будет сохранять данные независимо от того, является ли это листовым узлом или неконечным узлом, для нелистовых узлов объем, который можно сохранить, меньше, поэтому для того же объема данных высота B-дерево может быть выше, увеличивая размер диска.Количество операций ввода-вывода повлияет на эффективность запроса, но преимущество b-дерева в том, что запрос ключевых слов не нужно каждый раз углубляться в конечные узлы .
  • Поскольку все данные дерева B+ хранятся в листовых узлах, каждый запрос по ключевому слову должен углубляться в конечные узлы, эффективность запроса для всех данных одинакова, а запрос более стабилен. Листовой узел дерева B+ представляет собой двусвязный список, поэтому сортировка и поиск по диапазону лучше.
Категории