🌈Действительно ли MySQL CRUD? ✨Давайте посмотрим на разницу между 2k и 12k (ниже)

MySQL оптимизация производительности
🌈Действительно ли MySQL CRUD? ✨Давайте посмотрим на разницу между 2k и 12k (ниже)

Это 12-й день моего участия в августовском испытании обновлений. Узнайте подробности события:Испытание августовского обновления

🌈 Обзор прошлых выпусков

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

Четыре. Объясните анализ производительности.

4.1 Обзор

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

image-20210526152321356

4.2, подготовка данных

create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);

create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);


insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;

4.3 идентификатор

Столбец id в результирующем наборе, возвращаемом объяснением, представляет собой порядковый номер запроса на выборку, который представляет порядок, в котором предложение выбора или таблица операций выполняются в запросе.

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

# 查询课程编号为2或者教师编号为3的老师信息
EXPLAIN select t.* from teacher t 
left join teacherCard tc 
on tc.tcid = t.tcid
left join course c 
on c.tid = t.tid
where c.cid = 2 or t.tid = 3

image-20210526154029652

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

imgЧто делать, если значения id разные?

# 查询教授SQL课程的老师的描述（desc）
# 我们如果不使用子查询的话，会发现id值还是相同，所以下面将展示子查询的形式
EXPLAIN select tc.tcdesc from teacherCard tc where tc.tcid = 
(select t.tcid from teacher t where  t.tid =  
	(select c.tid from course c where c.cname = 'sql')
);

image-20210526160427308

Идентификатор отличается, если это подзапрос,Порядковый номер идентификатора будет увеличиваться, и чем больше значение идентификатора, тем выше приоритет., тем раньше он выполняется, при вложенности подзапросов сначала проверяется внутренний слой, а затем внешний слой. Итак, сначала запросите таблицу c, затем таблицу t и, наконец, таблицу tc.

imgЗатем углубитесь, что, если тот же 1 имеет другой идентификатор?

# 查询教授SQL课程的老师的描述（desc）
# 我们采用子查询1加多表的形式进行查询
explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid
and t.tid = (select c.tid from course c where cname = 'sql') ;

Чем больше значение id, тем выше приоритет.Если значение id одинаковое, выполнение будет выполняться сверху вниз.

image-20210526163447435

4.4, выберите_тип

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

Атрибуты имея в виду
SIMPLE Простой запрос на выборку, который не содержит подзапросов или запросов объединения UNION.
PRIMARY Если запрос содержит какие-либо сложные подразделы, самый внешний запрос помечается как основной.
DERIVED Используется временная таблица
SUBQUERY Содержит подзапросы в подзапросе SQL (не самые внешние)
DEPEDENT SUBQUERY Подзапрос включается в список SELECT или WHERE, и подзапрос основан на внешнем
UNCACHEABLE SUBQUERY Невозможно использовать кэшированные подзапросы
UNION Если есть table1 union table2, то получается table1, table2 - union
UNION RESULT Сообщите разработчикам, что между этими таблицами существует запрос на объединение.

4.4.1 ПРОСТО

Простой запрос выбора, запрос не содержит подзапроса или запроса соединения UNION.

select * from teacher

image-20210526170025751

4.4.2 ПЕРВИЧНЫЙ

Если запрос содержит какие-либо сложные подразделы, самый внешний запрос помечается как основной.

4.4.3 ПРОИЗВОДНЫЙ

Когда используется временная таблица, она будет помечена как DERIVED. У него два 1 дела:

  1. В подзапросе from есть только одна таблица.
explain select  cr.cname  from ( select * from course where tid in (1,2) ) cr ;
  1. В подзапросе from, если есть table1 union table2, то получается table1, table2 является union
explain select  cr.cname 	from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

4.4.4 СОЮЗ

Если второй SELECT появляется после UNION, он будет помечен как UNION, а первый SELECT будет помечен как DERIVED.

explain select  cr.cname 	from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

image-20210526165729964

4.4.5 ОБЪЕДИНЕНИЕ РЕЗУЛЬТАТА

Сообщите разработчику, что между этими таблицами есть запрос на объединение

explain select  cr.cname 	from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

image-20210526165837982

4.5, таблица

table указывает, на какой таблице основаны данные.

# 给一条复杂一点的SQL
explain select  cr.cname 	from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

image-20210526165108816

В таблице запроса с id = 1 есть значение , указывающее, что запрос использует производную таблицу, а источником производной таблицы является производная таблица с id 2.

4.6, тип

Тип представляет тип индекса, который является более важным показателем.Производительность от высокого к низкому:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL

Вообще говоря, необходимо добиться того, чтобы запрос достиг хотя бы уровня диапазона, желательно ref.Из них идеальными являются system и const, которых вообще трудно достичь.В общем случае можно достичь ref или range.

Если вы хотите оптимизировать тип, у вас должен быть индекс.

4.6.1, система

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

4.6.2, константа

Указывает, что его можно найти один раз по индексу, и можно запросить только SQL одной части данных, которая используется для первичного ключа или уникального,Действительно только для этих двух индексов. Это также трудно достичь.

# 创建表
create table user
(
	tid int(3),
	tname varchar(20)
);

insert into test01 values(1,'xiaolin') ;
commit;

# 添加索引
alter table test01 add constraint tid_pk primary key(tid) ;

# 测试
explain select * from (select * from test01 )t where tid =1 ;

image-20210526172245760

4.6.3, eq_ref

Уникальное сканирование индекса, для каждого ключа индекса есть только одна запись в таблице, которая ему соответствует (есть только одна, и она не может быть больше 0). Обычно встречается при сканировании первичного ключа или уникального индекса. Это непредсказуемо.

explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid

image-20210526182358229

4.6.4 ссылка

Сканирование неуникального индекса для каждого запроса ключа индекса возвращает все совпадающие строки.

# 先给teacher表的name字段加一个索引
alter table teacher add index index_name(tname);

# 在修改数据库表，两条语句用同一个name
explain SELECT * from teacher where tname = 'tw';

image-20210526193422920

4.6.5, диапазон

Извлекает только заданный диапазон строк, используя индекс для выбора строк. Ключевой столбец, показывающий, какой индекс используется, обычно присутствует в вашем операторе where. between,<,>,in (Иногда это произойдет с ошибкой, и он перейдет в состояние отсутствия индекса) и другие запросы.Это сканирование индекса по диапазону лучше, чем полное сканирование таблицы, потому что оно должно начинаться только в определенной точке индекса и заканчиваться в другой точка, без сканирования всего индекса.

# 给teacher的tid加一个普通索引
alter table teacher add index index_id(tid);

# 查询id小于3的老师
explain select * from teacher where tid < 3

image-20210526195452461

4.6.6, индекс

Запросите данные в столбце со всеми добавленными индексами.

# 我们刚刚给tid加了索引
explain select tid from teacher;

image-20210526200118276

4.6.7, все

Все данные в таблице запроса обычно не индексируются. Поле tname не индексируется.

image-20210526200213992

4.6.8 Резюме

  • system\const: Результатом является только один фрагмент данных.
  • eq_ref: Результатом является несколько частей данных, но каждая часть данных уникальна.
  • ссылка: Есть несколько результатов, но каждый фрагмент данных равен 0 или больше.

4.7, возможные_ключи

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

4.8, ключ

Фактический используемый индекс. Если NULL, индекс не используется.

4.9, key_len

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

image-20210526201202193

Метод расчета key_len:

  1. Сначала посмотрите на тип + длину поля в индексе, например int=4 , varchar(20) =20 , char(20) =20.
  2. Если это строковое поле, такое как varchar или char, его нужно умножить на разные значения в зависимости от набора символов, например, utf-8 умножить на 3, GBK умножить на 2.
  3. Динамическая строка, такая как archar, должна добавить 2 байта.
  4. Добавьте 1 байт для полей, которые могут быть пустыми.
  5. Если это составной индекс, длина key_len равна сумме текущего индекса и предыдущего индекса.

4.10 ссылка

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

4.11, ряды

Столбец rows показывает количество строк, которые, по мнению MySQL, он должен проверить при выполнении запроса. Чем меньше тем лучше!

4.12. Дополнительно

Другая дополнительная важная информация.

4.12.1 Использование файловой сортировки

Появление этого говорит о том, что у вас сильно потребляет производительность SQL, и требуется дополнительная сортировка (запросы).Например, есть поля возраст и имя.Я сначала нахожу их по имени, а потом сортирую по возрасту.

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

Для составного индекса его нельзя использовать по столбцам. Чтобы удовлетворить наилучшему левому префиксу, где и order by используются в порядке составного индекса, а не по столбцам или не по порядку.

# 我们先删除掉course中cid的主键，再执行查询
EXPLAIN select * from course where tid=1 order by cid

image-20210527101047367

4.12.2 Использование временных

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

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

4.12.3 Использование индекса

Использование индекса означает, что в соответствующей операции выбора используется покрывающий индекс.Пока все используемые столбцы находятся в индексе, это индексное покрытие. Он** избегал доступа к строкам данных таблицы, и производительность была улучшена! **Причина в том, что этот SQL-запрос не читает исходный файл, а только получает данные из индексного файла и не делает запросов в исходной таблице (не возвращает табличный запрос).

  1. Если использование where также присутствует, это указывает, что индекс используется для выполнения поиска ключа индекса.

  2. Если использование where одновременно отсутствует, это указывает на то, что индекс используется только для чтения данных, а не для использования индекса для выполнения поиска.

  3. Если используется покрывающий индекс, это повлияет на возможные_ключи и ключи:

    • Если нет где, индекс появляется только в ключе.
    • Если есть где, индекс появится в возможных_ключах и ключе

4.12.4 Использование где

Указывает, где используется фильтрация (как из индекса, так и обратно в исходную таблицу).

# 索引列id可以从索引中查询，但是除了id之外的其他列需要去原表中查询	
explain SELECT * from course c where c.tid =1

image-20210527115242995

4.12.5 Использование буфера соединения

Указывает, что используется кэширование соединения.

explain SELECT * from course c,teacher t where t.tid = c.tid

image-20210527104617003

Пять, оптимизация SQL для одной таблицы

Оператор создания таблицы:

CREATE TABLE `dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
ceo INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`empno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

5.1. Полноценный индекс — мой любимый

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

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

показатель

create index index_age_depid_name on emp(age,deptid,name);

# 查看emp的索引，检测我们建立索引是否成功
show index from emp

Написание тестов SQL

EXPLAIN SELECT  * FROM emp WHERE emp.age=30;

image-20210529221955036

EXPLAIN SELECT  * FROM emp WHERE emp.age=30 and deptid=4;

image-20210529222442893

EXPLAIN SELECT  * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd';

image-20210529222500414

5.2 Правило наилучшего левого префикса

5.2.1 Сбой индекса

explain select * from emp where  deptid = 4 and name = "xiaolin"

image-20210530143035021

Мы можем обнаружить, что индекс недействителен в настоящее время.

5.2.2 Когда индекс действителен

explain select * from emp where  age = 18 and deptid = 4

image-20210530143311904

5.2.3. Резюме

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

Чтобы использовать индекс, условия фильтра должны быть выполнены в том порядке, в котором индекс был создан.Если поле пропущено, поля после индекса нельзя использовать.

5.3. Не выполняйте никаких вычислений в столбцах индекса

explain select * from emp where  age +1  = 18  and deptid = 4

image-20210530143910916

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

5.4, ​​попробуйте использовать индекс покрытия

Давайте сначала рассмотрим случай, когда покрывающие индексы неприменимы.

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'xiaolin';

image-20210530145053614

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

image-20210530145133951

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

5.5 Старайтесь не использовать нечеткое сопоставление префиксов

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

# 先给name字段加上一个索引
create index index_name on emp(name);

# 测试后缀模糊匹配
explain select * from emp where  name like  "a%";

image-20210530145922716

# 测试前缀模糊匹配
explain select * from emp where  name like  "%a";

image-20210530145954424

# 测试前后都模糊匹配
explain select * from emp where  name like  "%a";

image-20210530150158598

Мы можем обнаружить, что до тех пор, пока существует нечеткое соответствие префиксов, будет возникать проблема аннулирования индекса. Если необходимо использовать нечеткие запросы, начинающиеся с %, мы можем использовать покрытие индекса, чтобы в определенной степени повысить производительность.

5.6. Сокращение использования или

# 我们使用or的时候也很容易造成索引失效的问题。
explain select * from emp where  age = 18  or deptid = 4

image-20210530150413315

Если нам нужно использовать или в реальной разработке, мы можем вместо этого использовать union all или union.

# 使用union all替代
explain select * from emp where  age = 18  union all select * from emp where  deptid = 4;

image-20210530150635887

# 使用union替代
explain select * from emp where  age = 18  union select * from emp where  deptid = 4;

image-20210530150658153

5.8 Старайтесь не использовать явные и неявные преобразования типов

# 先试一下正常情况
explain select * from emp where name="123";

image-20210530161451102

# 再试试索引失效情况
explain select * from emp where name=123;

image-20210530161503769

Причина сбоя индекса в том, что нижний уровень mysql преобразует 123 типа int в 123 типа varchar, а индекс недействителен.

5.7. Резюме

Полное соответствует моему любимому, и крайняя левая приставка слушается. Ведущий старший брат не может умереть, а средний брат не может быть сломлен. Меньше вычислений в столбце индекса, все недопустимо после диапазона. LIKE проценты пишут крайний правый, закрывающий индекс не пишут *. Существует также операция ИЛИ для неравных нулевых значений, и следует учитывать влияние индексации. Кавычки VAR нельзя потерять, есть хитрость в оптимизации SQL.

Шесть, многотабличная оптимизация SQL

6.1, оператор создания таблицы

create table teacher2
(
	tid int(4) primary key,
	cid int(4) not null
);

insert into teacher2 values(1,2);
insert into teacher2 values(2,1);
insert into teacher2 values(3,3);

create table course2
(
	cid int(4) ,
	cname varchar(20)
);

insert into course2 values(1,'java');
insert into course2 values(2,'python');
insert into course2 values(3,'kotlin');
commit;

6.2, левое соединение

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

explain select *from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';

image-20210530153441436

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

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

По правилам добавляем индекс в таблицу учитель2.

alter table teacher2 add index index_teacher2_cid(cid);
# 再次执行
explain select *from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';

image-20210530153655103

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

# 给cname字段加上索引
alter table course2 add index index_course2_cname(cname);
# 再次执行
explain select *from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';

image-20210530154000937

Семь, другие методы оптимизации

7.1 существует

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

select tname from teacher where exists (select * from teacher);
# 等价于
select * from teacher;
  1. Если набор данных основного запроса большой, используйтеin.
  2. Если набор данных подзапроса большой, используйтеexist.

7.2, заказать по

7.2.1, Алгоритм сортировки MySQL

Обычно мы используем сортировку по файлам, когда используем порядок. Существует два алгоритма сортировки файлов:

  1. Двусторонняя сортировка: до MySQL 4.1 по умолчанию использовалась двусторонняя сортировка, так называемая двухсторонняя сортировка, заключавшаяся в двойном сканировании диска. При первом чтении поля сортировки с диска поле сортировки сортируется в буферном буфере. Сканируйте другие поля во второй раз. Этот двойной ввод-вывод очень требователен к производительности.
  2. Односторонняя сортировка: после MySQL 4.1, чтобы уменьшить количество обращений к IO, она была изменена на одностороннюю сортировку. Он только один раз читает все поля, а поля сортировки сортирует в буфере. но
# 单位是字节，如果max_length_for_sort_data值太低（需要排序的总大小超过了max_length_for_sort_data定义的字节数）,MySQL会自动从单路切换到双路。
set max_length_for_sort_data = 2048;

7.2.1.1, двусторонняя сортировка

До версии MySQL 4.1 использовалась двусторонняя сортировка, что буквально означает двукратное сканирование диска и, наконец, получение данных, сначала чтение указателя строки и столбца orderby, их сортировка, а затем повторное сканирование отсортированного списка в соответствии с список Значение in повторно считывает соответствующие данные из списка. Получить поле сортировки с диска, отсортировать в буфере, а затем получить другие поля с диска.

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

7.2.1.2, односторонняя сортировка

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

7.2.1.3 Проблемы, существующие при односторонней сортировке

Односторонняя сортировка таит в себе определенные скрытые опасности: это может быть не один IO, а несколько IO. Потому что, если объем данных слишком велик, данные будут разделены, разделены на несколько раз, отсортированы в буфере, прочитаны в осколках и прочитаны несколько раз. Мы можем настроить размер буфера с помощью инструкции sql.

7.2.2 Стратегии улучшения порядка по запросу

7.2.2.1 Увеличьте значение параметра sort_butter_size

Независимо от того, какой алгоритм используется, улучшение этого параметра повысит эффективность.Конечно, его следует улучшать в соответствии с возможностями системы, потому что этот параметр регулируется в пределах 1-8 миллионов для каждого процесса.

7.2.2.2 Увеличьте значение параметра max_length_for_sort_data

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

Увеличение этого параметра повысит вероятность использования улучшенного алгоритма. Но если он установлен слишком большим, вероятность того, что общая емкость данных превысит sort_buffer_size, увеличивается, и очевидными симптомами являются высокая активность дискового ввода-вывода и низкая загрузка процессора. (Настройте между 1024-8192).

7.2.2.3 Сокращение полей запроса после выбора

Когда сумма размеров полей Query меньше max_length_for_sort_data и поле сортировки не типа TEXT|BLOB, будет использоваться улучшенный алгоритм - односторонняя сортировка, иначе будет использоваться старый алгоритм - многосторонняя сортировка.

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

Так что не используйтеselect * ...;.

7.2.2.4 Использование индекса покрытия

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

7.2.2.5 Обеспечьте единообразие сортировки

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

Восемь, медленный журнал запросов

8.1 Журнал медленных запросов

8.1.1 Что такое

Журнал медленных запросов MySQL — это запись журнала, предоставляемая MySQL.Он используется для записи операторов в MySQL, время ответа которых превышает пороговое значение.В частности, SQL, время выполнения которого превышает значение long_query_time, будет записано в журнал медленных запросов.

В частности, SQL, время выполнения которого превышает значение long_query_time, будет записано в журнал медленных запросов. Значение по умолчанию для long_query_time равно 10, что означает выполнение операторов дольше 10 секунд.

Он должен проверить, какой SQL превышает наше максимальное допустимое значение времени.Например, если SQL выполняется более 5 секунд, мы будем считать SQL медленным, и мы надеемся собрать SQL, который превышает 5 секунд и провести всесторонний анализ в сочетании с предыдущим объяснением.

8.1.2, как использовать

По умолчанию журнал медленных запросов не включен в базе данных MySQL, и нам нужно вручную установить этот параметр.

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

# 检查是否开启了慢查询日志,默认是off表示未开启。
show variables like '%slow_query_log';

image-20210620160710116

# 临时开启,在内存中开启,MySQL服务关闭时就关闭了
set global slow_query_log = 1;

image-20210620160829916

# 永久开启,需要在MySQL的配置文件中进行编辑
# 进入MySQL的配置文件
vim /etc/my.cnf

Добавьте две строки конфигурации в [mysqld].

image-20210620161452157

# 开启慢查询日志
slow_query_log=1
# 指定慢查询日志的存放路径
slow_query_log_file=/var/lib/mysql/localhost-slow.log

image-20210620161607808

# 查询慢查询的阈值
show variables like '%long_query_time%';

image-20210620161904875

# 设置慢查询阈值
# 临时设置,设置完毕后，需要重新登陆后才生效
set global long_query_time = 5;

image-20210620162128482

# 永久开启,需要在MySQL的配置文件中进行编辑,步骤和设置是否开启慢查询相同,只是写的参数不同。
# 进入MySQL的配置文件
vim /etc/my.cnf
# 在[mysqld]下面追加
long_query_time=3

# 查询超过慢查询阈值的sql数量
# 睡眠4s，模拟一条超过了4s的SQL
select sleep(4);
show global status like '%slow_queries%';

image-20210620163625563

# 如果我们想知道具体是哪条SQL的话，我们需要去刚刚指定的慢查询日志文件中进行查询
cat /var/lib/mysql/localhost-slow.log

image-20210620164508302

8.2, инструмент анализа журнала mysqldumpslow

Мы можем обнаружить, что очень недружелюбно использовать собственный журнал медленных запросов Мы можем проанализировать медленный запрос с помощью инструмента анализа журнала mysqldumpslow, который поставляется с mysql.

# 在Linux中查看mysqldumpslow的帮助信息
mysqldumpslow --help
параметр описывать
-s это способ сортировки
c количество посещений
l время блокировки
r Вернуться к записи
t время запроса
al среднее время блокировки
ar Среднее количество возвращенных записей
at Среднее время запроса
-t Сколько частей данных вернуть
-g Далее следует обычный шаблон сопоставления, нечувствительный к регистру. 
# 得到返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
# 得到访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
# 得到按照时间排序的前 10 条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
# 另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

Девять, замок

9.1, что такое механизм замка

В MySQL есть много типов блокировок, например блокировки строк, с которыми мы наиболее знакомы.Вот вопрос, почему в MySQL существуют блокировки строк? На самом деле причина в следующем:MySQL должен обеспечить согласованность данных. Когда данные обновляются, текущее чтение (чтение последних данных) должно быть выполнено первым, чтобы получить данные, и необходимо гарантировать, что данные не будут изменены другими транзакциями в течение периода с момента, когда данные найдено до завершения изменения. Таким образом, ваш оператор обновления выполняется, и результат семантически «непротиворечив».

9.2 Классификация замков

  • По типу операции:
    1. Блокировка чтения (общая блокировка): для одних и тех же данных можно одновременно выполнять несколько операций чтения, не мешая друг другу.
    2. Блокировка записи (блокировка взаимного исключения): если текущая операция записи не завершена, другие чтения не могут быть выполнены (эквивалентно тому, как A приносит одежду, первоначально выставленную на стойке регистрации, в примерочную при покупке одежды, а B даже смотрит на нее. не может читать) операция записи.
  • Рабочий диапазон:
    1. Блокировка таблицы: блокирует всю таблицу одновременно и блокирует всю таблицу с грубой детализацией. Подсистема хранения MyISAM использует блокировки таблиц с низкими издержками, быстрыми блокировками и отсутствием взаимоблокировок.Однако диапазон блокировок велик, конфликты блокировок могут возникать, а параллелизм низок.
    2. Блокировка строки: блокировка фрагмента данных за раз, гранулярность хорошая, и это не легко вызвать конфликты Механизм хранения InnoDB использует блокировку строки, которая дорога, медленна для блокировки, склонна к взаимоблокировке, маленькому диапазону блокировки и высокий параллелизм Возникают проблемы с высоким параллелизмом, такие как грязное чтение, фантомное чтение и неповторяющееся чтение.
    3. блокировка страницы

9.3 Операция блокировки

/* MYSQL/SQLSERVER 支持自增，Oracle 需要借助于序列来实现自增 */
create table tablelock
(
id int primary key auto_increment,
name varchar(20)
) engine myisam;

insert into tablelock(name) values('a1');
insert into tablelock(name) values('a2');
insert into tablelock(name) values('a3');
insert into tablelock(name) values('a4');
insert into tablelock(name) values('a5');

9.3.1, блокировка стола

9.3.1.1 Добавить блокировку чтения/записи

# 给表加读锁或者写锁，可以给多张表一起加，语法格式为
lock table 表1 read/writelock table 表2 read/write;

9.3.1.2, блокировка просмотра

# 查看加锁的表，1代表加了锁
show open tables;

9.3.1.3 Снять блокировку

# 释放锁
unlock tables;

9.3.1.4, анализ серьезности блокировки таблицы

show status like 'table%';

Его результат имеет две строки данных:

  • Table_locks_immedicate: указывает количество блокировок, которые можно получить немедленно.

  • Tbale_locks_waited: указывает количество ожидаемых блокировок таблицы.Чем больше значение, тем интенсивнее конкуренция за блокировки.

Обычно для измерения рекомендуется использовать значение Table_locks_immedicate/Tbale_locks_waited.Если оно больше 5000, используйте механизм InnoDB, в противном случае используйте механизм MyISAM.

9.3.1.5 Резюме

  • Если сеанс имеет блокировку чтения для таблицы A, сеанс может читать таблицу A, но не может выполнять операцию записи, и сеанс не может выполнять какие-либо операции чтения и записи для других таблиц, кроме таблицы A.

  • Проще говоря, если к таблице A добавлена ​​​​блокировка чтения, текущий сеанс может только читать таблицу A.

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

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

9.3.2, блокировка строки

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

create table linelock
(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb;

insert into linelock(name) values('1');
insert into linelock(name) values('2');
insert into linelock(name) values('3');
insert into linelock(name) values('4');
insert into linelock(name) values('5');

Чтобы изучить блокировки строк, нам нужно временно отключить автоматическую отправку, чтобы мы могли отправлять вручную.

set autocommit = 0;

9.3.2.1, сводка блокировки строк

  1. Блокировки таблиц разблокируются путем разблокировки таблиц, а также могут быть разблокированы транзакциями. И блокировки строк разблокируются транзакциями (фиксация/откат).
  2. Если сеанс x выполняет операцию DML над фрагментом данных a (исследование: когда автоматическая фиксация отключена), другие сеансы должны ждать, пока сеанс x завершит транзакцию (фиксация/откат), прежде чем работать с данными a.

9.3.2.2 Меры предосторожности при блокировке строк

Если индексов нет, блокировки строк преобразуются в блокировки таблиц.

show index from linelock ;
alter table linelock add index idx_linelock_name(name);
# 索引未失效
# 会话0进行写操作
update linelock set name = 'ai' where name = '3' ;
# 会话1进行写操作，不同的数据
update linelock set name = 'aiX' where name = '4' ;

# 索引失效（发生了索引类型转换）
# 会话0进行写操作
update linelock set name = 'ai' where name = 3 ;
# 会话1对不同的数据进行写操作
update linelock set name = 'aiX' where name = 4 ;

Можно обнаружить, что данные заблокированы (заблокированы), потому что преобразование типа типа индекса привело к сбою индекса, поэтому эта операция будет изменена с блокировки строки на блокировку таблицы.

Существует особый случай блокировки строк.

Значение находится в диапазоне, но не существует, это называется блокировкой пробела. Например, у нас нет данных с id=7 в таблице блокировки линии, когда мы пишем SQL:update linelock set name ='x' where id >1 and id<9 ;, если в Китае в этом диапазоне нет id=7, но нет данных с id=7, то данные с id=7 становятся пробелом. MySQL автоматически заблокирует пробел, называемый блокировкой пробела, и это также блокировка строки. То есть MySQL автоматически добавит гэп-блокировку (блокировку строки) к данным с id=7.

9.3.2.3. Четыре способа отключить автоматическую отправку

  1. set autocommit =0 ;
  2. start transaction ;
  3. begin ;
  4. Добавить для обновления после sql.

Мы также можем добавить блокировки строк при запросе, просто используйте четвертый метод.

# 通过for update对query语句进行加锁。
select * from linelock where id =2 for update ;

9.3.2.4, анализ блокировки строк

Мы можем использовать операторы SQL для анализа блокировок строк.

show status like '%innodb_row_lock%' ;

У него четыре параметра:

  1. Innodb_row_lock_current_waits: количество ожидающих блокировок.
  2. Innodb_row_lock_time: общее время ожидания. Общее время ожидания от запуска системы до настоящего момента.
  3. Innodb_row_lock_time_avg : Среднее время ожидания. Среднее время ожидания от запуска системы до настоящего момента.
  4. Innodb_row_lock_time_max : максимальное время ожидания. Максимальное время ожидания от запуска системы до настоящего момента.
  5. Innodb_row_lock_waits: количество ожиданий. Общее количество времени ожидания от запуска системы до настоящего момента
Категории