Призрака нет, давайте купим галантерейные товары! Оптимизация и диагностика SQL

MySQL

Оптимизация и диагностика SQL

Объяснить диагноз

Значение каждого параметра объяснения следующее:

имя столбца инструкция
id Номер выполнения идентифицирует строку, к которой принадлежит выбор. Если в операторе нет подзапросов или связанных запросов и есть только один выбор, в каждой строке будет отображаться 1. В противном случае внутренний оператор выбора обычно будет нумероваться последовательно, в соответствии с его позицией в исходном операторе.
select_type Показывает, является ли эта строка простым или сложным выбором, если запрос содержит какие-либо сложные подзапросы, самый внешний слой помечен как ОСНОВНОЙ (ПРОИЗВОДНЫЙ, ОБЪЕДИНЕНИЕ, ОБЪЕДИНЕНИЕ РЕЗУЛЬТАТ).
table доступ к таблице, на которую ссылаются (ссылаясь на такой запрос, как "derived3")
type Тип операции доступа/чтения данных (All, index, range, ref, eq_ref, const/system, NULL)
possible_key Узнайте, какие индексы могут быть полезны для эффективного поиска
key Показывает, какой индекс mysql фактически решает использовать для оптимизации запроса.
key_len Отображает количество байтов, используемых MySQL в индексе
ref Столбец или константа, используемая в значении поиска в индексе, записанном в ключевом столбце.
rows Количество строк для чтения, чтобы найти требуемую строку, оценка
Extra Дополнительная информация, такая как использование индекса, сортировка файлов и т. д.

select_type общие типы и их значения

  • SIMPLE: Запросы, не содержащие подзапросов или операций UNION.
  • PRIMARY: если запрос содержит какие-либо подзапросы, самый внешний запрос помечается как ОСНОВНОЙ.
  • SUBQUERY: первый SELECT в дочернем запросе
  • DEPENDENT SUBQUERY: первый SELECT в подзапросе, в зависимости от внешнего запроса
  • UNION: второй или последующий запрос операции UNION.
  • DEPENDENT UNION: второй или последующий запрос операции UNION, в зависимости от внешнего запроса.
  • UNION RESULT: результирующий набор, созданный UNION
  • DERIVED: подзапросы, появляющиеся в предложении FROM

Типы типов и их значения

  • system: это особый случай типа const, который возникает только тогда, когда запрашиваемая таблица имеет только одну строку данных.
  • consts: Часто возникает в сценарии, когда первичный ключ или уникальный индекс сравниваются с постоянным значением, а производительность запроса в это время оптимальна.
  • eq_ref: Используйте это, когда соединение использует полный индекс и является PRIMARY KEY или UNIQUE NOT NULL INDEX.
  • ref: используется, когда соединение использует префиксный индекс или условие соединения не является PRIMARY KEY или UNIQUE INDEX.
  • ref_or_null: Аналогичен запросу типа ref, но с добавлением запроса для столбцов со значением NULL.
  • index_merge: этот тип соединения указывает, что индекс используется для оптимизации слияния.
  • range: использовать индекс для сканирования диапазона, распространенного в условиях запроса, таких как между, > и
  • index: тип соединения индекса такой же, как и ALL, за исключением того, что сканируется дерево индекса, что обычно происходит, когда индекс является покрывающим индексом запроса.
  • ALL: Полное сканирование таблицы, наименее эффективный способ поиска

Али стандартные требования кодирования:至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

ключевой столбец

Действительно ли индексированное поле флага используется в запросе

Дополнительный столбец

Столбец «Дополнительно» в основном используется для отображения дополнительной информации.Общая информация и ее значение следующие:

  • Использование where : сервер MySQL будет фильтровать строки после того, как механизм хранения извлечет их.
  • Использование файловой сортировки: обычно происходит в операторе GROUP BY или ORDER BY, и сортировка или группировка не основаны на индексе.В этом случае необходимо использовать файл для сортировки в памяти, потому что производительность использования индексной сортировки лучше, чем использовать сортировку файлов, поэтому возникает такая ситуация. Вы можете рассмотреть возможность оптимизации, добавив индекс
  • Использование индекса: для запроса используется покрывающий индекс, в настоящее время нет необходимости обращаться к таблице, и все необходимые данные можно получить из индекса.
  • Использование условия индекса: поиск использует индекс, но данные необходимо вернуть в таблицу.
  • Использование временных: указывает, что для обработки запроса необходимо использовать временную таблицу, часто в операторах GROUP BY или ORDER BY.

Как просмотреть SQL после оптимизации оптимизатором Mysql

# 仅在服务器环境下或通过Navicat进入命令列界面
explain extended  SELECT * FROM `student` where `name` = 1 and `age` = 1;

# 再执行
show warnings;

# 结果如下：
/* select#1 */ select `mytest`.`student`.`age` AS `age`,`mytest`.`student`.`name` AS `name`,`mytest`.`student`.`year` AS `year` from `mytest`.`student` where ((`mytest`.`student`.`age` = 1) and (`mytest`.`student`.`name` = 1))

Зачем это делать? Мы знаем, что в Mysql используется принцип сопоставления крайних слева, поэтому, если мой индекс построен с возрастом и именем, могу ли я использовать индекс для запросов в порядке имени и возраста? На самом деле это возможно, потому что оптимизатор запросов Mysql может помочь нам автоматически оптимизировать порядок выполнения SQL и т. д. и выбрать самый дешевый способ запроса (обратите внимание, что стоимость — это самая низкая, а не самое короткое время)

SQL-оптимизация

Решение для сценария большого пейджинга

Если данные в таблице необходимо глубоко разбить на страницы, как повысить эффективность? В спецификации программирования Java, подготовленной Али, написано:

利用延迟关联或者子查询优化超多分页场景

Примечание. MySQL не пропускает строку смещения, а берет строку смещения + N, затем возвращает предыдущую строку смещения и возвращает строку N, затем, когда смещение особенно велико, эффективность очень низкая, или контролирует общее количество страниц. return или SQL переписать для страниц, которые превышают определенный порог

# 反例（耗时129.570s）
select * from task_result LIMIT 20000000, 10;

# 正例（耗时5.114s）
SELECT a.* FROM task_result a, (select id from task_result LIMIT 20000000, 10) b where a.id = b.id;

# 说明
task_result表为生产环境的一个表，总数据量为3400万，id为主键，偏移量达到2000万

Ограничение 1 при получении части данных

Если ситуация с таблицей данных известна, бизнесу необходимо получить часть данных, которая соответствует определенному условию «Где», обратите внимание на использование Limit

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

# 反例（耗时2424.612s）
select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48';

# 正例（耗时1.036s）
select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48' LIMIT 1;

# 说明
task_result表为生产环境的一个表，总数据量为3400万，where条件非索引字段，数据所在行为第19486条记录

Объемная вставка

# 反例
INSERT into person(name,age) values('A',24)
INSERT into person(name,age) values('B',24)
INSERT into person(name,age) values('C',24)

# 正例
INSERT into person(name,age) values('A',24),('B',24),('C',24);

# 说明
比较常规，就不多做说明了

Оптимизация подобного утверждения

Общие бизнес-требования подобного заявления:'%关键字%'В этой форме все еще необходимо рассмотреть вопрос об использовании правильного нечеткого метода для замены требований к продукту Среди них в спецификации кодирования Али упоминаются:

页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

# 反例（耗时78.843s）
EXPLAIN select * from task_result where taskid LIKE '%tt600e6b601677b5cbfe516a013b8e46%' LIMIT 1;

# 正例（耗时0.986s）
select * from task_result where taskid LIKE 'tt600e6b601677b5cbfe516a013b8e46%' LIMIT 1

##########################################################################
# 对正例的Explain
1	SIMPLE	task_result		range	adapt_id	adapt_id	98		99	100.00	Using index condition

# 对反例的Explain
1	SIMPLE	task_result		ALL					                    33628554	11.11	Using where

# 说明
task_result表为生产环境的一个表，总数据量为3400万，taskid是一个普通索引列，可见%%这种匹配方式完全无法使用索引，从而进行全表扫描导致效率极低，而正例通过索引查找数据只需要扫描99条数据即可

Избегайте преобразования функции или вычисления выражения для поля where в SQL

# 反例
select * from task_result where id + 1 = 15551;

# 正例
select * from task_result where id = 15550;

##########################################################################
# 对正例的Explain
1	SIMPLE	task_result		const	PRIMARY	PRIMARY	8	const	1	100.00	

# 对反例的Explain
1	SIMPLE	task_result		ALL					                33631512  100.00	Using where

# 说明
其实在知道了有SQL优化器之后，我个人感觉这种普通的表达式转换应该可以提前进行处理再进行查询，这样一来就可以用到索引了，但是问题又来了，如果mysql优化器可以提前计算出结果，那么写sql语句的人也一定可以提前计算出结果，所以矛盾点在这个地方，导致5.7版本以前的此种情况都无法使用索引吧，未来可能会对其进行优化

Используйте ISNULL(), чтобы определить, является ли это значением NULL.

Описание: Прямое сравнение NULL с любым значением равно NULL.

# 1） NULL<>NULL 的返回结果是 NULL，而不是 false。 
# 2） NULL=NULL 的返回结果是 NULL，而不是 true。 
# 3） NULL<>1 的返回结果是 NULL，而不是 true。

Многотабличный запрос

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

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

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

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

Однако дело не только в этом, так действительно должно быть:针对查询的数据行占总数据量过多时会转化成全表查询

Так сколько же стоит это преувеличение?

Результатом теста на 50%, но я лично думаю, что оптимизатор MySQL не будет полностью запутан в количестве строк, чтобы отличить, является ли он полным столом, но есть много других факторов, которые считают полную табличную сканирование, чтобы быть более эффективным и т. Д., поэтому я полностью признаю проблему только

количество (*) или количество (идентификатор)

Спецификация Ali по кодированию Java включает в себя следующее:

【强制】不要使用 count(列名) 或 count(常量) 来替代 count(*)

count(*) — это стандартный синтаксис для подсчета строк, определенный в SQL92, который не имеет ничего общего с базой данных и не имеет ничего общего с NULL и не-NULL.

Описание: count(*) будет подсчитывать строки со значениями NULL, а count(имя столбца) не будет подсчитывать строки со значениями NULL в этом столбце.

Различные типы полей приводят к сбою индекса

Спецификация Ali по кодированию Java включает в себя следующее:

【推荐】防止因字段类型不同造成的隐式转换，导致索引失效

На самом деле база данных будет выполнять неявное преобразование при запросе, например, поля типа varchar запрашиваются по числам

# 正例
EXPLAIN SELECT * FROM `user_coll` where pid = '1';
type：ref
ref：const	
rows:1	
Extra:Using index condition

# 反例
EXPLAIN SELECT * FROM `user_coll` where pid = 1;
type：index
ref：NULL	
rows:3(总记录数)
Extra:Using where; Using index

# 说明
pid字段有相应索引，且格式为varchar

о

Спасибо следующим сообщениям в блоге и их авторам:

Сухой товар! Оптимизация производительности SQL, написание качественных операторов SQL

Сухой товар! Оптимизация производительности SQL, написание высококачественных операторов SQL (2)

Официальная документация MySQL

Tips

Самостоятельно построенная таблица данных для тестирования

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) NOT NULL,
  `class` varchar(255) DEFAULT NULL,
  `page` bigint(20) DEFAULT NULL,
  `status` tinyint(3) unsigned NOT NULL COMMENT '状态：0 正常，1 冻结，2 删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4

вставить данные

DELIMITER ;;
    CREATE PROCEDURE insertData()
    BEGIN
        declare i int;
        set i = 1 ;
        WHILE (i < 1000000) DO
            INSERT INTO student(`name`,class,`page`,`status`)
                VALUES(CONCAT('class_', i),
                    CONCAT('class_', i),
                i, (SELECT FLOOR(RAND() * 2)));
            set i = i + 1;
        END WHILE;
        commit;
		END;;
CALL insertData();

Категории