Есть чувства, есть галантерейные товары, поиск в WeChat【Третий принц Ао Бин] Обратите внимание на этого другого программиста.
эта статьяGitHub github.com/JavaFamilyВключено, и есть полные тестовые площадки, материалы и мой цикл статей для интервью с производителями первой линии.
предисловие
Мы знаем, что дизайнерская идея кэширования широко распространена в базах данных РСУБД.Возьмите базу данных Oracle с так называемыми строками кода 2500w и кучей ошибок.План выполнения SQL может быть кэширован в библиотечном кеше, чтобы избежать жесткого разбора, когда тот же SQL выполняется снова (Синтаксический анализ -> Семантический анализ -> Создать план выполнения), результаты выполнения SQL кэшируются в компоненте памяти RESULT CACHE, который эффективно преобразует физический ввод-вывод в логический ввод-вывод и повышает эффективность выполнения SQL.
MySQL QueryCache похож на Oracle.Он кэширует текст операторов SQL и соответствующие наборы результатов.Это выглядит как отличная идея, так почему же он отключен по умолчанию в 5.6 после запуска MySQL 4.0, устарел в 5.7 и 8.0 Версия была удалена, и сегодня мы поговорим о прошлом и настоящем MySQL QueryCache.
Введение в QueryCache
Кэш запросов MySQL (QC: QueryCache) был введен в MySQL 4.0.1. Кэш запросов хранит текст оператора SELECT и набор результатов, отправленный клиенту. Если тот же SQL выполняется снова, сервер извлечет результаты из кеш запроса и вернуть его клиенту.Вместо повторного анализа и выполнения SQL на стороне клиента кеш запросов используется совместно между сеансами, поэтому кешированный набор результатов, сгенерированный одним клиентом, может отвечать другому клиенту, выполняющему тот же SQL.
Вернемся к вопросу в начале: как определить, является ли SQL общим?
Судя по тому, является ли текст SQL полностью согласованным, все символы, включая заглавные буквы, пробелы и другие символы, могут совместно использоваться точно так же.Преимущество совместного использования заключается в том, что он позволяет избежать жесткого синтаксического анализа и напрямую получать результат от контроля качества и возвращать его в клиент. Следующие два SQL-запроса не являются общими, потому что один из них, а другой — из.
--SQL 1
select id, balance from account where id = 121;
--SQL 2
select id, balance From account where id = 121;
Ниже приведен алгоритм базы данных Oracle для генерации sql_id через SQL_TEXT.Если sql_id отличается, это означает, что это не тот же самый SQL, и он не является общим, и будет происходить жесткий анализ.
#!/usr/bin/perl -w
use Digest::MD5 qw(md5 md5_hex md5_base64);
use Math::BigInt;
my $stmt = "select id, balance from account where id = 121\0";
my $hash = md5 $stmt;
my($a,$b,$msb,$lsb) = unpack("V*",$hash);
my $sqln = $msb*(2**32)+$lsb;
my $stop = log($sqln) / log(32) + 1;
my $sqlid = '';
my $charbase32 = '0123456789abcdfghjkmnpqrstuvwxyz';
my @chars = split '', $charbase32;
for($i=0; $i < $stop-1; $i++){
my $x = Math::BigInt->new($sqln);
my $seq = $x->bdiv(32**$i)->bmod(32);
$sqlid = $chars[$seq].$sqlid;
}
print "SQL is:\n $stmt \nSQL_ID is\n $sqlid\n";
Вы можете обнаружить, что значения sql_id, сгенерированные кодом SQL 1 и SQL 2, различаются, поэтому они не являются общими.
SQL is: select id, balance from account where id = 121
SQL_ID is dm5c6ck1g7bds
SQL is: select id, balance From account where id = 121
SQL_ID is 6xb8gvs5cmc9b
Если вы хотите сравнить различия между содержимым двух файлов кода Java, вам нужно только полностью понять этот код, а затем вы можете преобразовать и реализовать свою собственную бизнес-логику.
Конфигурация QueryCache
mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| Variable_name | Description |
|---|---|
| have_query_cache | Доступен ли кеш запросов, YES-доступен, NO-недоступен, при использовании стандартного бинарного MySQL значение всегда YES. |
| query_cache_limit | Управляет максимальным размером одного набора результатов запроса, значение по умолчанию — 1 МБ. |
| query_cache_min_res_unit | Размер блока данных сегмента кэша запросов по умолчанию составляет 4 КБ, что соответствует большинству бизнес-сценариев. |
| query_cache_size | Размер кеша запросов в байтах, установленный на 0, чтобы отключить QueryCache, примечание: не устанавливайте слишком большой размер кеша, вы можете столкнуться с конкуренцией за блокировку для очень больших кешей, потому что потоки необходимы для блокировки QueryCache во время процесса обновления используйте вопрос . |
| query_cache_type | Когда query_cache_size>0; эта переменная влияет на работу qc, есть три значения 0, 1, 2,0: отключить кэширование или извлечение кэшированных результатов.;1: включить кэширование, за исключением операторов SELECT SQL_NO_CACHE.;2: кэшировать только операторы, начинающиеся с SELECT SQL_CACHE. |
описание query_cache_min_res_unit
Размер по умолчанию — 4 КБ, если есть много результатов запроса, которые малы, то размер блока данных по умолчанию может вызвать фрагментацию памяти, что может заставить кеш запросов удалить запросы из кеша из-за нехватки памяти.
В этом случае значение query_cache_min_res_unit может быть уменьшено.Количество свободных блоков и запросов, удаленных из-за обрезки, определяется значениями переменных состояния Qcache_free_blocks и Qcache_lowmem_prunes.Это может быть увеличено, если большое количество запросов имеют большие наборы результатов, значения параметров для повышения производительности.
Обычно открывают метод QueryCache
# 修改MySQL配置文件/etc/my.cnf，添加如下配置，重启MySQL server即可。
[mysqld]
query_cache_size = 32M
query_cache_type = 1
Использование QueryCache
Сначала получите некоторые тестовые данные и протестируйте сценарии с отключенным и включенным QueryCache соответственно.
--创建一个用户表users，并且插入100w数据。
CREATE TABLE `users` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
`age` tinyint NOT NULL DEFAULT '0' COMMENT 'age',
`gender` char(1) NOT NULL DEFAULT 'M' COMMENT '性别',
`phone` varchar(16) NOT NULL DEFAULT '' COMMENT '手机号',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
select count(*) from users;
+----------+
| count(*) |
+----------+
| 1000000 |
Отключить сценарий queryCache
Когда QueryCache не используется, при каждом выполнении одного и того же оператора запроса выполняется жесткий синтаксический анализ, который потребляет много ресурсов.
#禁用QueryCache的配置
query_cache_size = 0
query_cache_type = 0
Повторите следующий запрос, чтобы наблюдать за временем выполнения.
--第一次执行查询语句
mysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.89 sec)
--第二次执行同样的查询语句
mysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.90 sec)
-- profile跟踪情况
mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| preparing | 0.000022 | 0.000017 | 0.000004 | 0 | 0 |
| Sorting result | 0.000014 | 0.000009 | 0.000005 | 0 | 0 |
| executing | 0.000011 | 0.000007 | 0.000004 | 0 | 0 |
| Sending data | 0.000021 | 0.000016 | 0.000004 | 0 | 0 |
| Creating sort index | 0.906290 | 0.826584 | 0.000000 | 0 | 0 |
Видно, что когда один и тот же оператор SQL-запроса выполняется несколько раз, время выполнения составляет около 0,89 с, и разницы практически нет, при этом время в основном расходуется на этапе создания индекса сортировки.
Открыть сцену queryCache
Когда кэш запроса включен, текст SQL и результаты запроса будут кэшироваться в QC при первом выполнении оператора запроса.При следующем выполнении того же SQL-запроса данные будут получены из QC и возвращены в клиент.
#禁用QueryCache的配置
query_cache_size = 32M
query_cache_type = 1
--第一次执行查询语句
mysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.89 sec)
--第二次执行查询语句
mysql> select * from users order by create_time desc limit 10;
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| id | name | age | gender | phone | create_time | update_time |
+---------+------------+-----+--------+-------------+---------------------+---------------------+
| 997855 | User997854 | 54 | M | 15240540354 | 2020-12-15 14:34:50 | 2020-12-15 14:34:50 |
.......
10 rows in set (0.00 sec)
-- profile跟踪数据
mysql> show profile cpu,block io for query 3;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| Waiting for query cache lock | 0.000016 | 0.000015 | 0.000001 | 0 | 0 |
| checking query cache for query | 0.000007 | 0.000007 | 0.000000 | 0 | 0 |
| checking privileges on cached | 0.000004 | 0.000003 | 0.000000 | 0 | 0 |
| checking permissions | 0.000034 | 0.000033 | 0.000001 | 0 | 0 |
| sending cached result to clien | 0.000018 | 0.000017 | 0.000001 | 0 | 0 |
Видно, что текст SQL и данные не кэшируются в QueryCache для первого выполнения, а время выполнения составляет 0,89 с.Поскольку QC включен, текст SQL и результаты выполнения кэшируются в QC.Второе выполнение выполняет тот же оператор SQL-запроса, напрямую попадает в QC и возвращает данные, жесткий анализ не требуется, поэтому время выполнения сокращается до 0 с. Из профиля отправка кэшированного результата клиенту напрямую отправляет данные в QC и возвращает их в клиент.
Частота попаданий в кеш запросов
Переменные состояния, связанные с кэшем запросов
mysql>SHOW GLOBAL STATUS LIKE 'QCache\_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 | --查询缓存中可用内存块的数目。
| Qcache_free_memory | 33268592 | --查询缓存的可用内存量。
| Qcache_hits | 121 | --从QC中获取结果集的次数。
| Qcache_inserts | 91 | --将查询结果集添加到QC的次数，意味着查询已经不在QC中。
| Qcache_lowmem_prunes | 0 | --由于内存不足而从查询缓存中删除的查询数。
| Qcache_not_cached | 0 | --未缓存的查询数目。
| Qcache_queries_in_cache | 106 | --在查询缓存中注册的查询数。
| Qcache_total_blocks | 256 | --查询缓存中的块总数。
Частота попаданий в кэш запросов и средний размер
Qcache_hits
Query cache hit rate = ------------------------------------------------ x 100%
Qcache_hits + Qcache_inserts + Qcache_not_cached
query_cache_size = Qcache_free_memory
Query Cache Avg Query Size = ---------------------------------------
Qcache_queries_in_cache
Операция обновления влияет на контроль качества
Например, в логике перевода платежной системы необходимо сначала заблокировать счет, а затем изменить баланс, основные шаги следующие:
| Query_ID | Query | Description |
|---|---|---|
| 1 | reset query cache | Очистите кеш запросов. |
| 2 | select balance from account where id = 121 | Первое исполнение, пропустить КК, добавить в КК. |
| 3 | select balance from account where id = 121 | Нажмите QC и верните результат напрямую. |
| 4 | update account set balance = balance - 1000 where id = 121 | Обновление, блокировка запроса кэша для обновления, аннулирование данных кэша. |
| 5 | select balance from account where id = 121 | Кэш инвалидирован, пропущен, добавлен в QC. |
| 6 | select balance from account where id = 121 | Нажмите QC и верните результат напрямую. |
В данном случае QC не подходит, т.к. первое выполнение запроса SQL пропускает, возвращает результат клиенту, после добавления текста SQL и результирующего набора в QC следующее выполнение того же SQL возвращает прямо из QC В качестве В результате операция жесткого синтаксического анализа не требуется, но каждое обновление заключается в том, чтобы сначала обновить данные, затем заблокировать контроль качества, а затем обновить кэшированные результаты, что приведет к тому, что предыдущие кэшированные результаты станут недействительными, и снова выполнить соответствующий запрос SQL. , такая частая блокировка QC->проверка QC->добавление QC->обновление QC потребляет ресурсы и снижает возможности одновременной обработки базы данных.
Зачем отказываться от QueryCache
Общие бизнес-сценарии
По типу работы бизнес-системы ее можно разделить на OLTP (онлайн-обработка транзакций) и OLAP (онлайн-обработка анализа), а для государственного и корпоративного бизнеса ее также можно разделить на BOSS (система поддержки бизнес-операций — бизнес-эксплуатация). система поддержки, называемая отраслевой отраслью) и BASS (система поддержки бизнес-анализа — система поддержки бизнес-анализа, называемая Jingfen), чтобы обобщить характеристики этих двух типов систем.
Сценарии, подходящие для QueryCache
Прежде всего, размер QC кеша запросов составляет всего несколько МБ, слишком большой размер кеша не подходит из-за необходимости потоков блокировать QueryCache во время процесса обновления, для очень больших кешей вы можете см. проблемы блокировки блокировки. Итак, в каких ситуациях может помочь кэширование запросов? Ниже приведены идеальные условия:
- Один и тот же запрос повторно выдается одними и теми же или несколькими клиентами.
- Базовые данные, к которым осуществляется доступ, являются статическими или полустатическими по своей природе.
- Запросы могут быть ресурсоемкими и/или создавать короткие, но сложные в вычислительном отношении наборы результатов с относительно небольшими наборами результатов.
- Параллелизм и количество запросов в секунду невелики.
Эти четыре ситуации являются только идеальными. Реальная бизнес-система управляется CRUD, данные часто обновляются, а количество запросов в секунду интерфейса запроса относительно велико. Поэтому существует очень мало бизнес-сценариев, которые могут соответствовать описанной выше идеальной ситуации. Таблица конфигурации и таблица словаря данных в основном являются статическими или полустатическими, а контроль качества можно использовать для повышения эффективности запросов.
Сценарии, не подходящие для QueryCache
Если данные таблицы меняются быстро, кеш запросов будет инвалидирован и нагрузка на сервер будет выше и обработка будет медленнее из-за постоянного удаления запросов из кеша, если данные обновляются каждые несколько секунд или чаще, то Кэширование запросов вряд ли подойдет.
В то же время кеш запросов использует один мьютекс для управления доступом к кешу, что фактически налагает однопоточный шлюз на механизм обработки SQL сервера.Когда QPS запроса относительно высок, это может стать узким местом производительности, что серьезно снизит производительность запросов, скорость обработки. Поэтому кэширование запросов по умолчанию отключено в MySQL 5.6.
удалить QueryCache
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. Deprecation includes query_cache_type, вы можете видеть, что из отключения MySQL 5.6 по умолчанию, отказа от 5.7 и полного удаления 8.0 Oracle также сделал такой выбор, учитывая все аспекты.
Мы говорили о подходящих и неподходящих бизнес-сценариях QueryCache выше и обнаружили, что эта функция слишком требовательна к бизнес-сценариям, и ее трудно сопоставить с реальным бизнесом, а после ее включения параллелизм и вычислительная мощность базы данных будут снижаться. значительно сократиться.Подытожим, почему MySQL отDisabled->Deprecated->RemovedОсновная причина QueryCache.
В то же время фрагментация кеша запросов также увеличит нагрузку на сервер и повлияет на стабильность базы данных.В официальном поиске Oracle по QueryCache можно найти много ошибок, что определяет, что MySQL 8.0 напрямую и решительно удаляет эту функцию.
Суммировать
Вышеприведенное представляет мысленный путь MySQL QueryCache от запуска -> отключение -> отказ -> удаление.В начале проектирования он был разработан, чтобы уменьшить накладные расходы на жесткий синтаксический анализ, вызванные повторяющимися запросами SQL, и в то же время преобразовать физические Ввод-вывод в логический ввод-вывод для улучшения SQL Тем не менее, MySQL претерпел несколько версий итераций.В то же время, с быстрым развитием аппаратных хранилищ, QC почти не имеет преимуществ, и это также уменьшит одновременную вычислительную мощность базы данных. конец, это было прямо Removd в версии 8.0.
На самом деле, идея дизайна кэш-памяти широко распространена в области аппаратного и программного обеспечения.С точки зрения оборудования: RAID-карты и процессоры имеют свои собственные кэши, но их слишком много с точки зрения программного обеспечения, такого как кэш ОС, буферный пул базы данных и Кэш программы Java. Как инженеры по исследованиям и разработкам, очень важно выбрать подходящее решение для кэширования в соответствии с бизнес-сценарием. Если оно не подходит, вам необходимо разработать индивидуальный кеш, чтобы он лучше соответствовал вашему бизнес-сценарию. Я буду сегодня так много говорить, и я надеюсь, что это будет полезно для вас. .
Я Ао Бин,Чем больше вы знаете, тем больше вы не знаете, спасибо за ваши таланты:подобно,собиратьа такжеКомментарий, увидимся в следующий раз!
Статья постоянно обновляется, вы можете искать в WeChat "Третий принц Ао Бин"Прочтите это в первый раз, ответьте [материал] Подготовленные мной материалы интервью и шаблоны резюме крупных заводов первой линии, эта статьяGitHub github.com/JavaFamilyОн был включен, и есть полные тестовые сайты для интервью с крупными заводами.Добро пожаловать в Star.