Есть чувства, есть галантерейные товары, поиск в WeChat【Третий принц Ао Бин] Обратите внимание на этого другого программиста.
эта статьяGitHub github.com/JavaFamilyВключено, и есть полные тестовые площадки, материалы и мой цикл статей для интервью с производителями первой линии.
предисловие
В тот день у меня был обеденный перерыв, меня разбудил администратор базы данных компании, сказав, что в какой-то библиотеке появилось большое количество медленного SQL. Вскоре, очень скоро, прежде чем я успел ответить, библиотека зависла. Я подумал, что Нынешние пользователи не стали бы об этом говорить.
Это очень распространенный сценарий, потому что многие бизнесы начинают с небольшого объема данных, поэтому при написании sql не обращают внимания на производительность, и если масштаб увеличивается, многие бизнесы нуждаются в настройке. также подытожили многое, и я поделюсь им с вами ниже.
В процессе разработки кода мы будем следовать некоторым спецификациям разработки SQL, чтобы писать высококачественный SQL для улучшения времени отклика (RT) интерфейса.Для некоторых основных интерфейсов RT требуется в пределах 100 мс или даже ниже.
Поскольку объем данных на ранней стадии бизнеса относительно невелик, он в основном может удовлетворить это требование.Однако по мере увеличения объема бизнеса объем данных также увеличивается, а затраты времени на SQL, соответствующие интерфейсу также увеличивается, что напрямую влияет на взаимодействие с пользователем SQL необходимо оптимизировать.
Точки оптимизации в основном включают нормативную проверку SQL, проверку индекса структуры таблицы и анализ случаев оптимизации SQL.Давайте поговорим о том, как оптимизировать SQL с учетом этих трех аспектов в сочетании с реальными случаями.
Проверка соответствия SQL
Каждая компания имеет свои собственные спецификации разработки MySQL, которые в основном одинаковы, вот некоторые из наиболее важных, с которыми я часто сталкиваюсь в своей работе.
выбрать проверить
определяемая пользователем функция UDF
Пользовательская функция UDF используется после выбора инструкции SQL.Количество строк, возвращаемых SQL, указывает, сколько раз будет вызываться функция UDF, что сильно влияет на производительность.
#getOrderNo是用户自定义一个函数用户来根据order_sn来获取订单编号
select id, payment_id, order_sn, getOrderNo(order_sn) from payment_transaction where status = 1 and create_time between '2020-10-01 10:00:00' and '2020-10-02 10:00:00';
проверка типа текста
Если в выборе появляется поле текстового типа, оно будет потреблять много трафика сети и ввода-вывода.Поскольку возвращаемый контент слишком велик и превышает настройку max_allowed_packet, программа сообщит об ошибке, и ее нужно использовать с осторожностью. .
#表request_log的中content是text类型。
select user_id, content, status, url, type from request_log where user_id = 32121;
group_concat следует использовать с осторожностью
gorup_concat — это функция агрегации строк, которая будет влиять на время отклика SQL.Если возвращаемое значение превышает настройку max_allowed_packet, программа сообщит об ошибке.
select batch_id, group_concat(name) from buffer_batch where status = 0 and create_time between '2020-10-01 10:00:00' and '2020-10-02 10:00:00';
Встроенные подзапросы
Когда после выбора есть подзапрос, он называется встроенным подзапросом.Количество строк, возвращаемых SQL, зависит от того, сколько раз нужно выполнить подзапрос, что серьезно влияет на производительность SQL.
select id,(select rule_name from member_rule limit 1) as rule_name, member_id, member_type, member_name, status from member_info m where status = 1 and create_time between '2020-09-02 10:00:00' and '2020-10-01 10:00:00';
из чека
Как связать таблицу
Не рекомендуется использовать левое соединение в MySQL.Даже если индекс столбца условия фильтра включен, в некоторых случаях индекс не будет использоваться, что приведет к сканированию большого количества строк данных и ухудшению производительности SQL. При этом необходимо понимать разницу между ON и Where.
SELECT a.member_id,a.create_time,b.active_time FROM operation_log a LEFT JOIN member_info b ON a.member_id = b.member_id where b.`status` = 1
and a.create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' limit 100, 0;
подзапрос
Поскольку оптимизатор MySQL, основанный на затратах, CBO, имеет слабые возможности обработки подзапросов, не рекомендуется использовать подзапросы, и его можно переписать как Inner Join.
select b.member_id,b.member_type, a.create_time,a.device_model from member_operation_log a inner join (select member_id,member_type from member_base_info where `status` = 1
and create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00') as b on a.member_id = b.member_id;
где проверить
индексированные столбцы вычисляются
Когда поле индексируется и одновременно появляется условие where, никакая операция не может быть выполнена, что приведет к сбою индекса.
#device_no列上有索引，由于使用了ltrim函数导致索引失效
select id, name , phone, address, device_no from users where ltrim(device_no) = 'Hfs1212121';
#balance列有索引,由于做了运算导致索引失效
select account_no, balance from accounts where balance + 100 = 10000 and status = 1;
преобразование типов
Для полей типа Int можно индексировать значение типа varchar, и MySQL автоматически выполняет неявное преобразование типов, наоборот, для полей типа varchar значение Int индексировать нельзя, и соответствующий тип поля должен передаваться соответственно всегда правильно.
#user_id是bigint类型，传入varchar值发生了隐式类型转换，可以走索引。
select id, name , phone, address, device_no from users where user_id = '23126';
#card_no是varchar(20)，传入int值是无法走索引
select id, name , phone, address, device_no from users where card_no = 2312612121;
набор символов столбца
Начиная с MySQL 5.6, рекомендуется, чтобы все наборы символов объекта использовали utf8mb4, включая набор символов экземпляра MySQL, набор символов базы данных, набор символов таблицы и набор символов столбца. Чтобы избежать сбоя индекса из-за несоответствия наборов символов поля при запросе Join, в настоящее время только utf8mb4 поддерживает хранение выражений emoji.
character_set_server = utf8mb4 #数据库实例字符集
character_set_connection = utf8mb4 #连接字符集
character_set_database = utf8mb4 #数据库字符集
character_set_results = utf8mb4 #结果集字符集
группировать по чеку
индекс префикса
Столбец после group by имеет индекс, и индекс может устранить нагрузку на ЦП, вызванную сортировкой.Если это префиксный индекс, сортировку нельзя исключить.
#device_no字段类型varchar(200)，创建了前缀索引。
mysql> alter table users add index idx_device_no(device_no(64));
mysql> select device_no, count(*) from users where create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' group by device_no;
работа функции
Предполагая, что вам нужно подсчитать количество новых пользователей в день в определенном месяце, обратитесь к следующему оператору SQL.Хотя можно использовать индекс create_time, сортировку нельзя исключить.Рассмотрите избыточное поле типа даты stats_date, чтобы решить эту проблему. .
select DATE_FORMAT(create_time, '%Y-%m-%d'), count(*) from users where create_time between '2020-09-01 00:00:00' and '2020-09-30 23:59:59' group by DATE_FORMAT(create_time, '%Y-%m-%d');
заказ по чеку
индекс префикса
Столбец после order by имеет индекс, который может устранить нагрузку на ЦП, вызванную сортировкой.Если это префиксный индекс, сортировку нельзя исключить.
Порядок полей
Порядок полей сортировки, повышение и понижение по возрастанию и убыванию должны соответствовать индексу и полностью использовать порядок индекса, чтобы исключить нагрузку на ЦП, вызванную сортировкой.
лимитная проверка
ограничьте m,n, чтобы быть осторожным
Для лимита m, n пейджинговых запросов, чем больше страница переворачивается, чем длиннее m, тем больше время SQL, для этого сначала нужно вынуть id первичного ключа, а затем запрос соединения с исходным таблица должна выполняться через идентификатор первичного ключа.
проверка структуры таблицы
Ключевые слова имени таблицы и столбца
На этапе проектирования и моделирования базы данных имя таблицы и имя поля должны быть установлены разумно, и нельзя использовать ключевые слова MySQL, такие как desc, order, status, group и т. д. Также рекомендуется установить lower_case_table_names = 1. Имена таблиц не чувствительны к регистру.
механизм хранения таблиц
Для бизнес-систем OLTP рекомендуется использовать механизм InnoDB для получения лучшей производительности, которой можно управлять с помощью параметра default_storage_engine.
Свойство AUTO_INCREMENT
При создании таблицы идентификатор первичного ключа имеет атрибут AUTO_INCREMENT и AUTO_INCREMENT=1, который подсчитывается системной глобальной переменной dict_sys.row_id внутри InnoDB, row_id представляет собой 8-байтовый bigint без знака, а InnoDB зарезервировала только row_id во время разработки. Длина 6 байт, поэтому диапазон значений row_id составляет от 0 до 2 ^ 48 - 1. Если значение id достигает максимального значения, следующее значение будет продолжать циклически увеличиваться с 0, а вставка указанного первичного ключа значение id запрещено в коде. .
#新插入的id值会从10001开始，这是不对的，应该从1开始。
create table booking( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',......) engine = InnoDB auto_increment = 10000;
#指定了id值插入，后续自增就会从该值开始+1，索引禁止指定id值插入。
insert into booking(id, book_sn) values(1234551121, 'N12121');
НЕ НУЛЕВОЕ свойство
В соответствии с бизнес смыслом, попробуйте добавить атрибут NOT NULL DEFAULT VALUE во все поля, если в значении столбца хранится большое количество NULL, это повлияет на стабильность индекса.
Свойство ПО УМОЛЧАНИЮ
При создании таблицы рекомендуется, чтобы каждое поле имело максимально возможное значение по умолчанию, DEFAULT NULL запрещен, а значение ответа по умолчанию заполняется для типа поля.
КОММЕНТАРИЙ свойство
Примечания поля должны быть в состоянии прояснить функцию поля, особенно некоторые поля, которые представляют состояние, и все возможные значения состояния поля и значение значения должны быть записаны явно.
Тип ТЕКСТА
Не рекомендуется использовать тип данных Text, так как, с одной стороны, из-за того, что передача большого количества пакетов данных может превысить настройку max_allowed_packet, программа может сообщить об ошибке, а с другой стороны, DML-операция на таблица станет очень медленной.Рекомендуется использовать es или объектное хранилище OSS для хранения и извлечения.
проверка индекса
индексированное свойство
Мощность индекса относится к количеству уникальных значений индексируемого столбца. Чем больше уникальных значений близко к количеству (*) таблицы, тем выше скорость выбора индекса, тем меньше строк сканируется по индексу и тем выше производительность. Например, частота выбора идентификатора первичного ключа составляет 100%. В MySQL все обновления обновляются с использованием идентификатора первичного ключа, поскольку идентификатор представляет собой кластеризованный индекс, в котором хранится вся строка данные, и не нужно возвращать в таблицу, а производительность самая высокая.
mysql> select count(*) from member_info;
+----------+
| count(*) |
+----------+
| 148416 |
+----------+
1 row in set (0.35 sec)
mysql> show index from member_base_info;
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| member_info | 0 | PRIMARY | 1 | id | A | 131088 | NULL | NULL | | BTREE | | |
| member_info | 0 | uk_member_id | 1 | member_id | A | 131824 | NULL | NULL | | BTREE | | |
| member_info | 1 | idx_create_time | 1 | create_time | A | 6770 | NULL | NULL | | BTREE | | |
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
#Table： 表名
#Non_unique ：是否为unique index，0-是，1-否。
#Key_name：索引名称
#Seq_in_index：索引中的顺序号，单列索引-都是1；复合索引-根据索引列的顺序从1开始递增。
#Column_name：索引的列名
#Collation：排序顺序，如果没有指定asc/desc，默认都是升序ASC。
#Cardinality：索引基数-索引列唯一值的个数。
#sub_part：前缀索引的长度；例如index (member_name(10)，长度就是10。
#Packed：索引的组织方式，默认是NULL。
#Null：YES:索引列包含Null值；'':索引不包含Null值。
#Index_type：默认是BTREE，其他的值FULLTEXT，HASH，RTREE。
#Comment：在索引列中没有被描述的信息，例如索引被禁用。
#Index_comment：创建索引时的备注。
индекс префикса
Для типа строки переменной длины varchar(m), чтобы уменьшить key_len, вы можете рассмотреть возможность создания префиксного индекса, но префиксный индекс не может устранить группировку по, а упорядочивание по приводит к накладным расходам на сортировку. Если фактическое максимальное значение поля намного меньше m, рекомендуется уменьшить длину поля.
alter table member_info add index idx_member_name_part(member_name(10));
Порядок составного индекса
Многим нравится думать, что ведущий столбец должен быть столбцом с множеством уникальных значений при создании составного индекса, Например, индекс индекса idx_create_time_status(create_time, status), этот индекс часто не может попасть, потому что количество Сканирования ввода-вывода слишком много, общая стоимость выше, чем полное сканирование таблицы.Окончательный выбор CBO — выполнить полное сканирование таблицы.
MySQL следует принципу сопоставления с самым левым индексом.Для составных индексов столбцы индекса сканируются слева направо, пока не будет получен первый запрос диапазона (>=, >,
select account_no, balance from accounts where status = 1 and create_time between '2020-09-01 00:00:00' and '2020-09-30 23:59:59';
индекс столбца времени
Для полей по умолчанию created_at(create_time) и updated_at(update_time) индексы должны создаваться по умолчанию, что обычно является правилом по умолчанию.
Пример оптимизации SQL
Благодаря мониторингу и оповещению о медленных запросах часто обнаруживается, что некоторые операторы SQL, в которых поля фильтра имеют индексы, но индексы недействительны из-за проблемы записи SQL.В следующих двух случаях показано, как выполнять запросы с помощью перезаписи SQL. Вы можете использовать следующий SQL для получения медленных запросов за последние 5 минут для оповещения.
select CONCAT( '# Time: ', DATE_FORMAT(start_time, '%y%m%d %H%i%s'), '\n', '# User@Host: ', user_host, '\n', '# Query_time: ', TIME_TO_SEC(query_time), ' Lock_time: ', TIME_TO_SEC(lock_time), ' Rows_sent: ', rows_sent, ' Rows_examined: ', rows_examined, '\n', sql_text, ';' ) FROM mysql.slow_log where start_time between current_timestamp and date_add(CURRENT_TIMESTAMP,INTERVAL -5 MINUTE);
медленный запрос SQL
| 2020-10-02 19:17:23 | w_mini_user[w_mini_user] @ [10.200.20.11] | 00:00:02 | 00:00:00 | 9 | 443117 | mini_user | 0 | 0 | 168387936 | select id,club_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and app_id is not null) or (invite_id=12395 or applicant_id=12395) order by created_time desc limit 0,10; | 1219921665 |
Из медленного запроса slow_log видно, что время выполнения 2с, сканируется 443117 строк, а возвращается только 9 строк, что неразумно.
SQL-анализ
#原始SQL，频繁访问的接口，目前执行时间2s。
select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and app_id is not null) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;
#执行计划
+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | t_user_msg | index | invite_id,app_id,team_id | created_time | 5 | NULL | 10 | Using where |
+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)
Как видно из плана выполнения, на таблице есть одноколоночные индексы инвайт_ид, апп_ид, команда_ид, created_time, используется индекс create_time, а индекс type=index полностью сканируется, т.к. create_time не появляется после условие where, но только после порядка by. , может быть только type=index, что также указывает на то, что чем больше количество табличных данных, тем медленнее SQL. Мы предполагаем использовать три одностолбцовых индекса: , а затем введите = index_merge.
Согласно общепринятому мышлению условие ИЛИ разбивается на две части и анализируется отдельно.
select id, ……. from t_user_msg where 1 and **(team_id in (3212) and app_id is not null)** order by created_time desc limit 0,10;
Из плана выполнения это индекс team_id, проблем нет.
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+----------------------+---------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | t_user_msg | ref | app_id,team_id | team_id | 8 | const | 30 | Using where; Using filesort |
Посмотрите на другой оператор sql:
select id, ……. from t_user_msg where 1 and **(invite_id=12395 or app_id=12395)** order by created_time desc limit 0,10;
В плане выполнения используются одностолбцовые индексы инвайт_ид и апп_ид соответственно, и операция index_merge выполняется одновременно, и проблем нет.
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------------------+
| 1 | SIMPLE | t_user_msg | index_merge | invite_id,app_id | invite_id,app_id | 9,9 | NULL | 2 | Using union(invite_id,app_id); Using where; Using filesort |
Благодаря вышеприведенному анализу не составляет труда использовать индекс team_id для плана выполнения первой части SQL, а второй части SQL — использовать соответственно index_id, app_id и index_merge. - индексы столбцов?
index_merge включен по умолчанию, когда включена опция оптимизатора, в основном для объединения результатов сканирования нескольких диапазонов в один, который можно просмотреть через переменные.
mysql >select @@optimizer_switch;
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,
Все остальные три поля передаются в определенных значениях, и все они прошли через соответствующий индекс. Я могу только подозревать, что условие app_id не равно нулю влияет на выбор CBO окончательного плана выполнения. Удалите это условие и посмотрите на план выполнения. Если у вас есть три одностолбцовых индекса и type=index_merge, просто сделайте следующееapp_id is not nullЭто условие в порядке.
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------------+---------------------------------+---------------------------------+---------+------+------+---------------------------------------------------------------------------+
| 1 | SIMPLE | t_user_msg | index_merge | invite_id,app_id,teadm_id | team_id,invite_id,app_id | 8,9,9 | NULL | 32 | Using union(team_id,invite_id,app_id); Using where; Using filesort |
SQL переписать
Благодаря приведенному выше анализу известно, что условие app_id не равно null влияет на выбор CBO, и преобразование осуществляется ниже.
Переписать и оптимизировать 1
В соответствии со спецификацией разработки SQL перепишите OR в режим Union All.Окончательный SQL выглядит следующим образом:
select id, ……. from (
select id, ……. from t_user_msg where **1 and (club_id in (5821) and applicant_id is not null)**
**union all** select id, ……. from t_user_msg where **1 and invitee_id='146737'**
**union all** select id, ……. from t_user_msg where **1 and app_id='146737'**
) as a order by created_time desc limit 0,10;
При нормальных обстоятельствах код Java и SQL разделены, а SQL настраивается в файле xml.Согласно бизнес-требованиям, за исключением team_id, который является обязательным, два других являются необязательными.Поэтому, хотя это переписывание может повысить эффективность SQL исполнение, но оно не подходит для данного бизнес-сценария.
Переписать и оптимизировать 2
app_id не является нулевым, переписать какIFNULL(app_id, 0) >0), окончательный SQL:
select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **IFNULL(app_id, 0) >0)**) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;
Переписать и оптимизировать 3
Измените поле app_id bigint(20) DEFAULT NULL на app_id bigint(20)NOT NULL DEFAULT 0, и в то же время обновить значение app_id равно null до 0, вы можете преобразовать условие app_id не равно null в app_id > 0, окончательный SQL:
select id,team_id,reason,status,type,created_at,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **app_id > 0)**) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;
Из плана выполнения два метода оптимизации перезаписи используют три одностолбцовых индекса, а время выполнения сокращается с 2 с до 10 мс.Оптимизация 1Таким образом, если вы сможете с самого начала следовать спецификациям разработки MySQL, проблемы удастся избежать.
Суммировать
Вышеприведенное описывает нормативную проверку SQL, проверку структуры таблицы, проверку индекса и оптимизацию запросов с помощью перезаписи SQL.В процессе написания кода, если вы можете выполнить эти нормативные проверки заранее, вы можете оценить план выполнения, который вы считаете идеальным, а затем разобрать его через объяснения Придумайте план выполнения MySQL CBO, сравните и проанализируйте разницу между ними и выясните разницу между вашим выбором и CBO, вы сможете не только писать качественный SQL, но и понимать принцип работы СВО.
Статья постоянно обновляется, вы можете искать в WeChat "Третий принц Ао Бин"Прочтите это в первый раз, ответьте [материал] Подготовленные мной материалы интервью и шаблоны резюме крупных заводов первой линии, эта статьяGitHub github.com/JavaFamilyОн был включен, и есть полные тестовые сайты для интервью с крупными заводами.Добро пожаловать в Star.