Мой официальный аккаунт:MarkerHub,Веб-сайт:markerhub.com
Чтобы увидеть больше избранных статей, нажмите:Java Notes Daquan.md
Малый концентратор ведет к чтению:
Для mysql я сказал много точек оптимизации, просто собери его, хахахаха~
- wolearn
- juejin.im/post/59b11ba151882538cb1ecbd0
предисловие
Эта статья в основном нацелена на реляционную базу данных MySql. База данных "ключ-значение" может относиться к:
Сначала кратко разберем основные понятия Mysql, а затем разделим оптимизацию на два этапа: создание и запрос.
1 Краткое введение в основные понятия
1.1 Логическая архитектура
-
Первый уровень: клиент передает команду sql для выполнения, подключившись к сервису
-
Второй уровень: сервер разбирает и оптимизирует sql, генерирует окончательный план выполнения и выполняет его
-
Третий уровень: механизм хранения, отвечающий за хранение и извлечение данных.
1.2 Блокировка
Базы данных решают сценарии параллелизма с помощью механизмов блокировки — общих блокировок (блокировок чтения) и эксклюзивных блокировок (блокировок записи). Блокировки чтения не блокируются, и несколько клиентов могут читать один и тот же ресурс одновременно. Блокировки записи являются исключительными и блокируют другие блокировки чтения и записи. Кратко упомянем оптимистическую блокировку и пессимистическую блокировку.
-
оптимистическая блокировка, обычно используемый в сценариях с меньшей конкуренцией данных, большим количеством чтения и меньшим количеством записи, реализованный с помощью номеров версий и временных меток.
-
пессимистический замок, обычно используемый в сценариях с интенсивной конкуренцией данных, когда каждая операция блокирует данные.
Для блокировки данных требуется определенная стратегия блокировки.
-
блокировка стола, заблокируйте всю таблицу, накладные расходы минимальны, но это повысит конкуренцию за блокировку.
-
блокировка строки, заблокируйте уровень строки, накладные расходы будут самыми большими, но он может поддерживать параллелизм в наибольшей степени.
Однако реальная реализация механизма хранения MySql не является простой блокировкой на уровне строк, а обычно реализует управление параллелизмом с несколькими версиями (MVCC). MVCC — это вариант блокировки на уровне строк, который в большинстве случаев позволяет избежать операций блокировки и имеет меньшие накладные расходы. MVCC достигается за счет сохранения моментального снимка данных на определенный момент времени.
1.3 Транзакции
Транзакция гарантирует атомарный набор операций, которые либо завершатся успешно, либо завершатся неудачно. В случае неудачи все предыдущие операции откатываются. MySql использует автоматическую фиксацию.Если транзакция явно не открыта, каждый запрос рассматривается как транзакция.
Уровень изоляции определяет, какие изменения внутри транзакции видны внутри и между транзакциями. Четыре общих уровня изоляции:
-
незафиксированное чтение(Read UnCommitted), изменения в транзакции видны другим транзакциям, даже если они не зафиксированы. Транзакции могут считывать незафиксированные данные, что приводит к грязным чтениям.
-
Отправить для чтения(Read Committed), когда транзакция начинается, видны только изменения, сделанные зафиксированной транзакцией. Изменения, сделанные транзакцией, не видны другим транзакциям, пока транзакция не будет зафиксирована. Также называемое неповторяемым чтением, одна и та же транзакция может читать одну и ту же запись несколько раз, что может отличаться.
-
повторяемое чтение(RepeatTable Read), результат тот же, когда одна и та же запись читается несколько раз в одной и той же транзакции.
-
Сериализуемый(Serializable), самый высокий уровень изоляции, обеспечивает последовательное выполнение транзакций.
1.4 Механизм хранения
Механизм InnoDB, наиболее важный и наиболее широко используемый механизм хранения. Он предназначен для обработки большого количества краткосрочных транзакций с высокой производительностью и автоматическим восстановлением после сбоя.
Механизм MyISAM, который не поддерживает транзакции и блокировки на уровне строк, не может быть безопасно восстановлен после сбоя.
2 Оптимизация во время создания
2.1 Оптимизация схемы и типов данных
целое число
Память, используемая TinyInt, SmallInt, MediumInt, Int, BigInt 8, 16, 24, 32, 64 бита памяти. Использование Unsigned для указания того, что отрицательные числа не разрешены, удваивает верхний предел положительных чисел.
вещественные числа
-
Float, Double поддерживает приблизительные операции с плавающей запятой.
-
Decimal, для хранения точных десятичных дробей.
нить
-
VarChar, в котором хранятся строки переменной длины. Для записи длины строки требуется 1 или 2 дополнительных байта.
-
Char, фиксированной длины, подходит для хранения строк фиксированной длины, таких как значения MD5.
-
Blob, Text предназначен для хранения очень больших данных. В двоичном и символьном виде соответственно.
тип времени
-
DateTime, содержит большой диапазон значений, занимая 8 байт.
-
Отметка времени, рекомендуется, такая же, как отметка времени UNIX, 4 байта.
Точка предложения по оптимизации
-
Попробуйте использовать соответствующий тип данных. Например, не используйте строковый тип для хранения времени, используйте целочисленный тип для хранения IP.
-
Выберите меньший тип данных. Может использовать TinyInt, но не Int.
-
Столбец идентификатора, рекомендуется использовать целочисленный тип, строковый тип не рекомендуется, он занимает больше места, а скорость вычислений ниже, чем у целочисленного типа.
-
Схема, автоматически сгенерированная системой ORM, не рекомендуется, так как обычно имеет такие проблемы, как невнимание к типу данных, использование большого типа VarChar и неразумное использование индекса.
-
Сценарии реального мира смешивают парадигмы и антипарадигмы. Высокая избыточность имеет высокую эффективность запросов и низкую эффективность обновления вставки; низкая избыточность имеет высокую эффективность обновления вставки и низкую эффективность запроса.
-
Создайте полностью независимую сводную таблицу\кэш-таблицу, регулярно генерируйте данные и используйте их для трудоемких операций пользователей. Для сводных операций, требующих высокой точности, можно использовать исторические результаты + последние записанные результаты для достижения цели быстрого запроса.
-
Во время переноса данных и обновления таблицы можно использовать теневые таблицы.Изменив имя исходной таблицы, можно сохранить исторические данные, не влияя на использование новой таблицы.
2.2 Указатель
Индекс содержит значения одного или нескольких столбцов. MySql может эффективно использовать только крайний левый столбец префикса индекса. Преимущества индексации:
-
Уменьшить объем данных, сканируемых запросом
-
Избегайте сортировки и таблиц нулевого часа
-
Измените случайный ввод-вывод на последовательный ввод-вывод (последовательный ввод-вывод более эффективен, чем случайный ввод-вывод)
B-Tree
Самый используемый тип индекса. Для хранения данных используется структура данных B-Tree (каждый конечный узел содержит указатель на следующий конечный узел, что облегчает обход конечных узлов). Индекс B-Tree подходит для полного значения ключа, диапазона значений ключа, поиска префикса ключа и поддерживает сортировку.
Ограничения индекса B-дерева:
-
Индекс нельзя использовать, не начав запрос с крайнего левого столбца индекса.
-
Столбцы в индексе нельзя пропускать. Если вы используете индексы первого и третьего столбцов, вы можете использовать только индекс первого столбца.
-
Если в запросе есть запрос диапазона, ни один из столбцов справа от него не может использовать индекс для оптимизации запроса.
хэш-индекс
Запрос действителен только в том случае, если все столбцы индекса точно совпадают. Механизм хранения вычисляет хэш-код для всех индексированных столбцов, а хэш-индекс сохраняет все хэш-коды в индексе и хранит указатели на каждую строку данных.
Пределы хэш-индекса:
-
нельзя использовать для сортировки
-
Частичное совпадение не поддерживается
-
Поддерживает только запросы на равенство, такие как =, IN(), не поддерживает
Точка предложения по оптимизации
-
Обратите внимание на доступность и применимые ограничения каждого индекса.
-
Индексированные столбцы недействительны, если они являются частью выражения или аргумента функции.
-
Для особенно длинных строк можно использовать индексацию префикса для выбора подходящей длины префикса в соответствии с селективностью индекса.
-
При использовании индекса с несколькими столбцами вы можете объединяться с помощью синтаксиса AND и OR.
-
Повторяющиеся индексы не нужны, так как (A, B) и (A) повторяются.
-
Индексы особенно полезны для условных запросов и группировки запросов по синтаксису.
-
Поместите запрос диапазона в конец условного запроса, чтобы предотвратить аннулирование правого индекса запросом диапазона.
-
Лучше не выбирать слишком длинный индекс, а столбец индекса не должен быть нулевым.
3 Оптимизация времени запроса
3.1 Три важных показателя качества запросов
-
Время отклика (время обслуживания, время ожидания)
-
сканированная линия
-
строка возвращена
3.2 Точки оптимизации запросов
-
Избегайте запросов к лишним столбцам, например, используйте Select * для возврата всех столбцов.
-
Не запрашивайте лишние строки
-
Разделить запрос. Разделите задачу, которая сильно нагружает сервер, на более длительный период времени и выполняйте ее несколько раз. Если вы хотите удалить 10 000 фрагментов данных, вы можете выполнить его 10 раз, сделать паузу на время после каждого выполнения, а затем продолжить выполнение. В ходе этого процесса ресурсы сервера могут быть высвобождены для других задач.
-
Разложите связанные запросы. Разложите запрос многотабличной ассоциации на несколько запросов к одной таблице. Конкуренция за блокировку может быть снижена, а эффективность самого запроса относительно высока. Поскольку подключение и отключение MySql являются упрощенными операциями, это не вызовет проблем с эффективностью, поскольку запрос разбивается на несколько раз.
-
Обратите внимание, что операция count может подсчитывать только ненулевые столбцы, поэтому count(*) используется для подсчета общего количества строк.
-
group by эффективна для группировки по столбцу идентификаторов, и результат группировки не должен находиться в столбце, отличном от столбца группировки.
-
Ассоциация задержки запроса ассоциации, вы можете сначала сузить область каждого запроса в соответствии с условиями запроса, а затем связать.
-
Ограничьте оптимизацию страниц. Вы можете выполнить сканирование на основе индекса, а затем запросить другие столбцы на основе ассоциации столбца индекса. как
SELECT
id,
NAME,
age
WHERE
student s1
INNER JOIN (
SELECT
id
FROM
student
ORDER BY
age
LIMIT 50,5
) AS s2 ON s1.id = s2.id
- Запрос Union по умолчанию дедуплицирован, если это не нужно для бизнеса, рекомендуется использовать более эффективный Union All
добавить
От Великого Бога - Маленькое Сокровище
- Если тип поля в условии несовместим с типом структуры таблицы, mysql автоматически добавит функцию преобразования, в результате чего индекс станет недействительным в качестве параметра функции.
2. Предыдущая часть аналогичного запроса не введена, и индекс не может попасть, если он начинается с %.
- В версии 5.7 добавлено 2 новые функции:
сгенерированный столбец, то есть этот столбец в базе вычисляется из других столбцов
CREATE TABLE triangle (sidea DOUBLE, sideb DOUBLE, area DOUBLE AS (sidea * sideb / 2));
insert into triangle(sidea, sideb) values(3, 4);
select * from triangle;
+-------+-------+------+
| sidea | sideb | area |
+-------+-------+------+
| 3 | 4 | 6 |
+-------+-------+------+
Поддержка данных формата JSON и предоставление соответствующих встроенных функций.
CREATE TABLE json_test (name JSON);
INSERT INTO json_test VALUES('{"name1": "value1", "name2": "value2"}');
SELECT * FROM json_test WHERE JSON_CONTAINS(name, '$.name1');
От экспертов JVM — Da
Обратите внимание на использование объяснения в анализе производительности.
EXPLAIN SELECT settleId FROM Settle WHERE settleId = "3679"
-
select_type, существует несколько значений: simple (представляет собой простой выбор, без объединения и подзапроса), primary (с подзапросом самый внешний запрос на выборку является первичным), union (второй или последующий запрос на выборку в объединении, не зависит от результата внешнего запроса), зависимое объединение (второй или последующий запрос выбора в объединении, в зависимости от результата внешнего запроса)
-
type, есть несколько значений: system (таблица имеет только одну строку (= системная таблица), что является частным случаем типа соединения const), const (постоянный запрос), ref (доступ к неуникальному индексу, только обычный индекс), eq_ref (использовать уникальный запрос индекса или компонента), все (запрос полной таблицы), индекс (запрос полной таблицы на основе индекса), диапазон (запрос диапазона)
-
possible_keys: Индексы в таблице, которые могут помочь в запросах
-
key, выберите индекс для использования
-
key_len, используемая длина индекса
-
rows, количество строк для сканирования, чем больше, тем лучше
-
extra, есть несколько значений: Только индекс (информация извлекается из индекса, быстрее, чем сканирование таблицы), Где используется (использование ограничений where), Использование файловой сортировки (может быть в памяти или сортировка на диске), Использование временной (используется при сортировке запроса результаты) Временные таблицы)
(Заканчивать)
Рекомендуемое чтение
Удивительно, на этом веб-сайте Java есть все виды проектов! https://markerhub.com
Мастер UP этой станции B, java действительно хорош!
классно! Последнюю версию идей программирования на Java можно прочитать онлайн!