提示：没有学习上篇的同学请先学习【上篇】
Базовый индекс MySql и оптимизация данных [1-5] Перейдите к:nuggets.capable/post/705222…
6. Сбой индекса
Аннулирование индекса (следует избегать)
создать таблицу
- Создать таблицу SQL
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
- Тестовые данные в таблице персонала
mysql> select * from staffs;
+----+------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+---------+---------------------+
| 1 | z3 | 22 | manager | 2020-08-04 14:42:33 |
| 2 | July | 23 | dev | 2020-08-04 14:42:33 |
| 3 | 2000 | 23 | dev | 2020-08-04 14:42:33 |
+----+------+-----+---------+---------------------+
3 rows in set (0.00 sec)
- Составной указатель по таблице персонала: ФИО, возраст, должность
mysql> SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
- Составной указатель по таблице персонала: ФИО, возраст, должность
mysql> SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
6.2 Критерии аннулирования индекса
- полное соответствие стоимостимой любимый
- оптимальныйправило левого префикса: Если индексируется несколько случаев, должно соблюдаться правило крайнего левого префикса. Ссылается на запрос, начинающийся с самого левого переднего столбца индекса и не пропускающий столбцы в индексе.
- Ничего не делать с индексированными столбцами (Расчеты, функции, (автоматическое или ручное) преобразование типов), что приведет к сбою индекса и переходу к полному сканированию таблицы.
- механизм хранениянельзя использовать в индексестолбец справа от условия диапазона
- Попробуйте использовать покрывающий индекс (запрос, который обращается только к индексу (столбец индекса согласуется со столбцом запроса)) для уменьшения **выберите ***
- MySQL не может использовать индексы при использовании не равно (**!=** или ), что приведет к полному сканированию таблицы.
- нулевой, не нулевойИндекс также нельзя использовать (более ранняя версия не может использовать индекс, последующая версия должна быть оптимизирована, и индекс можно использовать)
- likeНачиная с подстановочного знака ('%abc...'), аннулирование индекса MySQL станет операцией полного сканирования таблицы.
- нитьбез одинарных кавычеканнулирование индекса
- бесполезныйor, при соединении с ним будетаннулирование индекса
6.2.1 Правило наилучшего левого сопоставления: старший брат не может умереть, а средний брат не может быть сломан
- Только когда берешь на себя имя старшего брата
- key = index_staffs_nameAgePos указывает, что индекс действует
- ref = const : эта константа является строковой константой «июль» во время запроса.
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
- Возьми на себя имя старшего брата, возьми возраст младшего брата.
- key = index_staffs_nameAgePos указывает, что индекс действует
- ref = const,const: две константы: «июль» и 23
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23;
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)
- Возьмите на себя инициативу, имя старшего брата приносит возраст младшего брата, возраст младшего брата приносит младшего брата
- key = index_staffs_nameAgePos указывает, что индекс действует
- ref = const,const,const : три константы: «июль», 23 и «dev».
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)
- Возьмите на себя инициативу, имя старшего брата зависает
- key = NULL указывает, что индекс недействителен
- ref = null означает, что ref также недействителен
mysql> EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
- Возьмите на себя инициативу, имя старшего брата не зависло, возраст младшего брата убежал
- key = index_staffs_nameAgePos указывает, что индекс не является недействительным
- ref = const указывает, что используется только одна константа, т. е. вторая константа (pos = 'dev') не действует.
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
6.2.2. Вычисление в столбце индекса приведет к сбою индекса, а затем переключится на полное сканирование таблицы
- Не выполнять никаких операций над именем ведущего старшего брата: ключ = index_staffs_nameAgePos указывает, что индекс действует
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
- Оперируйте именем ведущего старшего брата: используйте функцию ВЛЕВО, чтобы перехватить подстроку
- key = NULL указывает, что индекс действует
- type = ALL указывает на полное сканирование таблицы
mysql> EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) = 'July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
6.2.3, все недопустимо после области действия
- точное совпадение
- type = ref означает сканирование неуникального индекса, оператор SQL вернет все строки, соответствующие одному значению.
- key_len = 140 указывает количество байтов, используемых в индексе
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)
- Изменить возраст в соответствии с диапазоном
- type = range означает сканирование диапазона
- key = index_staffs_nameAgePos указывает, что индекс не является недействительным
- key_len = 78 и ref = NULL оба указывают, что поиск по диапазону делает все последующие индексы недействительными.
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
6.2.4 Максимально используйте покрывающие индексы (запросы, которые обращаются только к индексу (столбцы индекса согласованы со столбцами запроса)) для уменьшенияselect *
-
SELECT *написание
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
- Метод записи покрытия индекса: Extra = Использование где; Использование индекса Использование индекса означает использование столбца индекса для запроса, что значительно повысит эффективность запроса.
mysql> EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using where; Using index |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+
1 row in set (0.00 sec)
- Покрывающий индекс содержит условия диапазона: type = ref и Extra = Using where; Using index , хотя в условиях запроса используется поиск по диапазону, но поскольку нам нужно найти только столбец индекса, нет необходимости выполнять полное сканирование таблицы
mysql> EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using where; Using index |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
6.2.5 MySQL не может использовать индекс при использовании неравенства (!= или ), что приведет к полному сканированию таблицы
- Использование != вместо приведет к аннулированию индекса:
- key = null означает, что индекс недействителен
- rows = 3 означает полное сканирование таблицы
mysql> EXPLAIN SELECT * FROM staffs WHERE name != 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE name <> 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
6.2.6, имеет значение null, не является значением null и не может использовать индекс
- является нулевым, не является нулевым, приведет к сбою индекса: key = null означает, что индекс недействителен
mysql> EXPLAIN SELECT * FROM staffs WHERE name is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE name is not null;
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
6.2.7, типа % пишем самый правый
- Индекс отношения таблицы персонала
mysql> SHOW INDEX from staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
- вроде % написано слева
- type = All, rows = 3 означает полное сканирование таблицы
- key = null означает, что индекс недействителен
mysql> EXPLAIN SELECT * FROM staffs WHERE name like '%July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE name like '%July%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
- Вроде % написано справа: key=index_staffs_nameAgePos указывает, что индекс недействителен
mysql> EXPLAIN SELECT * FROM staffs WHERE name like 'July%';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
6.2.8. Решить проблему [например, '%str%'] инвалидации индекса: покрыть индекс
6.2.8.1, создать таблицу
- Создать таблицу SQL
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');
- Тестовые данные в таблице tbl_user
mysql> select * from tbl_user;
+----+------+------+-----------+
| id | name | age | email |
+----+------+------+-----------+
| 1 | 1aa1 | 21 | a@163.com |
| 2 | 2bb2 | 23 | b@163.com |
| 3 | 3cc3 | 24 | c@163.com |
| 4 | 4dd4 | 26 | d@163.com |
+----+------+------+-----------+
4 rows in set (0.00 sec)
6.2.8.2, создать индекс
- Команда SQL для создания индекса
CREATE INDEX idx_user_nameAge ON tbl_user(name, age);
- Создайте совместный индекс для полей имени и возраста таблицы tbl_user.
mysql> CREATE INDEX idx_user_nameAge ON tbl_user(name, age);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM tbl_user;
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_user | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| tbl_user | 1 | idx_user_nameAge | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
| tbl_user | 1 | idx_user_nameAge | 2 | age | A | 4 | NULL | NULL | YES | BTREE | | |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
6.2.9,Индекс тестового покрытия
- Следующие индексы SQL не будут ошибочны:
- Пока запрашиваемые поля могут быть связаны с покрывающим индексом и нет избыточных полей, покрывающий индекс не будет ошибочным.
- Но я не могу понять, при чем здесь id. . .
EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT name FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
- Следующие индексы SQL будут недействительными: если есть избыточные поля, покрывающий индекс будет недействительным.
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';
mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
6.2.10,Строковый индекс без одинарных кавычек не работает
- Нормальная работа, индексы не аннулируются
mysql> SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> explain select * from staffs where name='2000';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
- Если в строке забыть написать '' , то mysql выполнит за нас неявное преобразование типов, но если преобразование типов будет выполнено, то индекс будет невалидным
mysql> explain select * from staffs where name=2000;
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
6.2.11 Используйте или меньше, индекс будет недействителен при соединении с ним
- Использование соединения или приведет к сбою индекса.
mysql> SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> explain select * from staffs where name='z3' or name = 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
6.3 Вопросы интервью по оптимизации индекса
создать таблицу
- Создать таблицу SQL
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
- Тестовые данные в таблице test03
mysql> select * from test03;
+----+------+------+------+------+------+
| id | c1 | c2 | c3 | c4 | c5 |
+----+------+------+------+------+------+
| 1 | a1 | a2 | a3 | a4 | a5 |
| 2 | b1 | b2 | b3 | b4 | b5 |
| 3 | c1 | c2 | c3 | c4 | c5 |
| 4 | d1 | d2 | d3 | d4 | d5 |
| 5 | e1 | e2 | e3 | e4 | e5 |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)
- Индекс в таблице test03
mysql> SHOW INDEX FROM test03;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test03 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
Вопрос: Мы создали составной индекс idx_test03_c1234 и анализируем использование индекса в соответствии со следующим SQL?
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';- т. е. полное соответствие
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
1 row in set (0.00 sec)
EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';- Оптимизатор mysql оптимизирован, поэтому все наши индексы действуют.
mysql> EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
1 row in set (0.00 sec)
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';- Столбец c3 использует индекс для сортировки и не выполняет поиск, в результате чего столбец c4 не может использовать индекс для поиска.
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';- Оптимизатор mysql оптимизирован, поэтому все наши индексы действуют, а поиск по диапазону выполняется на c4
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 124 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;- Столбец c3 использует индекс для сортировки, а не поиска, а столбец c4 не использует индекс.
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3;- То же, что и выше, столбец c4 не использует индекс
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;- Блин, из-за того, что порядок создания индекса не соответствует порядку использования, mysql использует сортировку файлов
- Когда вы видите Использование файловой сортировки, вам нужно знать: этот оператор SQL должен быть оптимизирован.
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+
1 row in set (0.00 sec)
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3;- Используйте только c1 для индекса поля, но c2, c3 используются для сортировки, без файловой сортировки
- Это потому, что c2 находится сразу после c1 при сортировке, поэтому нет необходимости в файловой сортировке?
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2;- Есть файловая сортировка, мы построили индекс 1234, он не по порядку, 32 наоборот
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3;- Используйте индекс двух полей c1, c2, но c2, c3 используются для сортировки, без файловой сортировки
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3;- Это не имеет ничего общего с дерьмом c5.
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2;- Обратите внимание на условие запроса c2='a2' , я проверил c2 (c2 - константа), и я также отсортировал его, поэтому файловая сортировка не генерируется
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3;- Порядок 1 2 3 , порядок файлов не создается
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2;- На первый взгляд группировка по называется группировкой, и она должна быть отсортирована перед группировкой.Проблема группировки и упорядочения по в индексации в основном такая же.
- Используя временное использование Filesort Есть и то, и другое, я могу только сказать, что это мастер вымирания
`EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2;`
group by 表面上叫分组，分组之前必排序，group by 和 order by 在索引上的问题基本是一样的
Using temporary; Using filesort 两个都有，我只能说是灭绝师太
в заключении:
- Группировать по в основном нужно сортировать, но при неправильном использовании будет сгенерирована временная таблица
- Фиксированное значение является постоянным и недопустимым после диапазона и, наконец, зависит от порядка сортировки.
6.4. Краткая информация о сбое индекса
6.4.1 Общие рекомендации
- Для индексов с одним ключом попробуйте выбрать индекс с лучшей фильтруемостью для текущего запроса.
- При выборе составного индекса поле с наилучшей фильтруемостью в текущем запросе находится в порядке полей индекса, и чем левее, тем лучше.
- При выборе составного индекса попробуйте выбрать индекс, который может содержать больше полей в предложении where текущего запроса.
- Насколько это возможно, анализируя статистическую информацию и корректируя написание запроса для достижения цели выбора соответствующего индекса.
6.4.2 Резюме по оптимизации индекса
- Like начинается с константы, такой как 'kk%' и 'k%kk%' , которые можно понимать как константы.
6.4.3, как SQL измеряется
- = 'kk' : key_len = 93 , запомните значение этого параметра, оно пригодится позже
----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 93 | const,const,const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)
- как «кк%»:
- key_len = 93 , то же, что и выше, указывая, что все c1 c2 c3 используют индекс
- type = range указывает, что это поиск по диапазону
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like 'kk%' AND c3='a3';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
- как '%kk' и как '%kk%' : key_len = 31, указывая, что только c1 использует индекс
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like '%kk' AND c3='a3';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like '%kk%' AND c3='a3';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
- например 'k%kk%' : key_len = 93, что означает, что все c1 c2 c3 используют индекс
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like 'k%kk%' AND c3='a3';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
6.4.4,Резюме по оптимизации индекса
Полное значение соответствует моему фавориту, и нужно соблюдать крайний левый префикс;
Ведущий старший брат не может умереть, средний брат не может быть сломлен;
Меньше вычислений в столбце индекса, все недопустимо после диапазона;
LIKE проценты пишут крайний правый, индекс обложки не пишут*;
Существует также ИЛИ между неравными нулевыми значениями, и следует обратить внимание на влияние индекса;
VAR-кавычки не потеряются, есть хитрость в SQL-оптимизации
7. Анализ перехвата запросов
7.1 Оптимизация запросов
Принципы оптимизации MySQL
Схема настройки для mysql
- Включить и зафиксировать медленные запросы
- объяснить + медленный анализ SQL
- показать профильные запросы, детали выполнения и жизненный цикл SQL на сервере Mysql
- Настройка параметров сервера базы данных SQL
Всегда маленькая таблица управляет большой таблицей, аналогично вложенному циклу Вложенный цикл
-
EXISTSграмматика:
- SELECT ... FROM table WHERE EXISTS(subquery)
- Этот синтаксис можно понимать как: поместить запрошенные данные в подзапрос для условной проверки и определить, сохраняется ли результат данных основного запроса в соответствии с результатом проверки (ИСТИНА или ЛОЖЬ).
- EXISTS(подзапрос) возвращает толькоTRUEилиFALSE, поэтому в подзапросеВЫБЕРИТЕ *** также может бытьSELECT 1Или другое, официальное заявление состоит в том, что оно будет проигнорировано при фактическом выполненииSELECT ** список, так что никакой разницы
- EXIST S подзапросФактический процесс выполнения , может быть оптимизирован, а не поточечное сравнение на основе нашего понимания.Если вас беспокоят проблемы с эффективностью, вы можете провести реальные тесты, чтобы определить, есть ли проблемы с эффективностью.
- EXISTSПодзапросы часто могут также использовать условные выражения, другие подзапросы илиJOINДля замены, которая является лучшей, требуется конкретный анализ конкретных проблем.
в заключении:
- Всегда помните, что маленькие столы управляют большими столами.
- Когда набор данных в таблице B меньше, чем набор данных в таблице A, используйте в
- Когда набор данных таблицы А меньше, чем набор данных таблицы Б, используйте существующий
in 和 exists 的用法
- таблица tbl_emp и таблица tbl_dept
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.00 sec)
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.00 sec)
- правописание в
mysql> select * from tbl_emp e where e.deptId in (select id from tbl_dept);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
+----+------+--------+
7 rows in set (0.00 sec)
- Как написать существует
mysql> select * from tbl_emp e where exists (select 1 from tbl_dept d where e.deptId = d.id);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
+----+------+--------+
7 rows in set (0.00 sec)
7.2, ПОРЯДОК ПО оптимизации
Предложение ORDER BY, попробуйте использовать индекс для сортировки, избегайте использования FileSort для сортировки
7.2.1 Создайте таблицу
- Создать таблицу SQL
create table tblA(
#id int primary key not null auto_increment,
age int,
birth timestamp not null
);
insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());
create index idx_A_ageBirth on tblA(age, birth);
- Тестовые данные в таблице tblA
mysql> select * from tblA;
+------+---------------------+
| age | birth |
+------+---------------------+
| 22 | 2020-08-05 10:36:32 |
| 23 | 2020-08-05 10:36:32 |
| 24 | 2020-08-05 10:36:32 |
+------+---------------------+
3 rows in set (0.00 sec)
- индекс в таблице
mysql> SHOW INDEX FROM tblA;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tblA | 1 | idx_A_ageBirth | 1 | age | A | 3 | NULL | NULL | YES | BTREE | | |
| tblA | 1 | idx_A_ageBirth | 2 | birth | A | 3 | NULL | NULL | | BTREE | | |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
Пример 1: случай, когда индекс можно использовать для сортировки
- Только возраст старшего брата
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM tblA where birth>'2016-01-28 00:00:00' order by age;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
- Возглавьте возраст старшего брата + рождение младшего брата
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age,birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
- MySQL сортирует по возрастанию по умолчанию, либо все по возрастанию, либо все по убыванию, все может содержать
mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth ASC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM tblA ORDER BY age DESC, birth DESC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.01 sec)
Пример 2:Когда нельзя использовать индекс для сортировки
- Возьмите на себя инициативу, возраст старшего брата зависает
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.01 sec)
- По умолчанию MySQL сортируется в порядке возрастания.
mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
- MySQL поддерживает два метода сортировки: FileSort и Index.
- ORDER BY удовлетворяет двум условиям(принцип наилучшего левого префикса), будет использовать метод Index для сортировки
- Оператор ORDER BY использует крайний левый передний столбец индекса.
- Используйте предложение where и предложение OrderBy, чтобы объединить условные столбцы, чтобы удовлетворить самый левый передний столбец индекса.
- Завершите операцию сортировки в столбце индекса, насколько это возможно, в соответствии с построенным индексом.лучший левый префикс
Если сортировка не выполняется для индексированного столбца, mysql запускает два алгоритма сортировки файлов: двустороннюю сортировку и одностороннюю сортировку.
-
двусторонняя сортировка
- MySQL4.1Раньше использовалась двусторонняя сортировка, что буквально означает, что диск сканируется дважды, и данные в итоге получаются. Прочитайте указатель строки и столбец, подлежащий операции orderby, отсортируйте их, затем просмотрите отсортированный список и повторно прочитайте соответствующую передачу данных из списка в соответствии со значением в списке.
- Получить поле сортировки с диска, вbufferОтсортируйте, а затем извлеките другие поля с диска.
-
односторонняя сортировка
- Чтобы взять пакет данных, необходимо дважды просканировать диск, как мы все знаем., ввод-вывод занимает много времени, так что вmysql4.1После этого появился улучшенный алгоритм, то есть односторонняя сортировка.
- Прочитайте все столбцы, требуемые запросом, с диска, отсортируйте их в буфере сортировки в соответствии с столбцами, которые будут упорядочены, а затем просканируйте отсортированный список для вывода, что более эффективно и позволяет избежать повторного чтения данных, И включить случайный ввода-вывода в последовательный ввод-вывод, но он будет занимать больше места, поскольку каждая строка хранится в памяти.
-
Выводы и поднятые вопросы
- Поскольку односторонний — это улучшенный алгоритм, он, как правило, лучше, чем двусторонний.
- существуетsort_buffer, метод B занимает намного больше места, чем метод A, потому что метод B удаляет все поля, поэтому общий размер данных, которые могут быть удалены, превышаетsort_bufferемкость, чтобы каждый раз толькоsort_bufferОтсортируйте данные по размеру емкости (создайте файл tmp, объедините несколькими способами), а затем извлеките их после сортировки.sort_bufferРазмер емкости, перераспределение... Это приведет к множеству операций ввода/вывода.
- Вывод: изначально я хотел сохранить операцию ввода-вывода, но это привело к большому количеству операций /вывода, что перевесило выигрыш.
-
Стратегии более глубокой оптимизации
- увеличиватьsort_buffer_sizeУстановка параметров
- увеличиватьmax_length_for_sort_dataУстановка параметров
Следуйте приведенным ниже правилам, чтобы увеличить скорость Order By
-
Order byВремяselectЭто большое нет-нет.Очень важны только поля, требуемые Query. Эффекты здесь следующие:
- Когда сумма размеров полей запроса меньшеmax_length_for_sort_data, а когда поле сортировки не имеет типа TEXT|BLOB, будет использоваться улучшенный алгоритм односторонней сортировки, в противном случае будет использоваться старый алгоритм многосторонней сортировки.
- Данные для обоих алгоритмов, вероятно, превышаютsort_bufferемкость, после чего создастtmpФайлы сортируются слиянием, что приводит к множествуI/O, но риск использования алгоритма односторонней сортировки будет больше, поэтому необходимо увеличитьsort_buffer_size.
- попробуй улучшитьsort_buffer_sizeНезависимо от того, какой алгоритм используется, увеличение этого параметра повысит эффективность.Конечно, его следует улучшать в соответствии с возможностями системы, потому что этот параметр есть для каждого процесса.
- попробуй улучшитьmax_length_for_sort_dataУвеличение этого параметра увеличивает вероятность использования улучшенного алгоритма. Но если установить слишком большое значение, общая емкость данных превыситsort_buffer_sizeВероятность возрастает, явный симптом — высокий дискI/Oактивность и низкая загрузка процессора.
Сводка по оптимизации индекса сортировки
7.3, ГРУППА ПО оптимизации
группировка по оптимизации ключевых слов
- group byПо сутиСортировать перед группировкой, по индексулучший левый префикс
- когда недоступенстолбец индекса, увеличиватьmax_length_for_sort_dataустановка параметра + увеличениеsort_buffer_sizeУстановка параметров
- whereвыше чемhaving, можно записать вwhereНе ходите в ограниченных условияхhavingограниченное
- Остальные правила равныorder byпоследовательный
8. Медленный журнал запросов
8.1, введение в журнал медленных запросов
- MySQLЖурнал медленных запросовMySQLПредоставляет тип ведения журнала, который используется для записиMySQLЗаявление, в котором время отклика превышает пороговое значение, конкретно относится к времени выполнения, превышающему пороговое значение.long_query_timeSQL значения будет записан в журнал медленных запросов.
- long_query_timeЗначение по умолчанию — 10, что означает, что операторы SQL, которые выполняются более 10 секунд, будут регистрироваться.
- Что он видитSQLПревышено максимально допустимое значение времени. Например, если SQL выполняется более 5 секунд, даже если мы замедляем SQL, мы надеемся собрать более 5 секунд.5 секундизsql, перед объединениемexplainПроведите комплексный анализ.
8.2. Включить журнал запросов
Как играть?
инструкция:
- по умолчанию,MySQLбаза данныхЖурнал медленных запросов не включен, нам нужно вручную установить этот параметр.
- Конечно, если это не требуется для тюнинга, вообщене предлагаетсяВключите этот параметр, так как включение журнала медленных запросовболее менеепринести определенныевлияние на производительность. Журнал медленных запросов поддерживает запись записей журнала в файлы.
Проверьте, включен ли он и как его включить
- Проверьте, включен ли журнал медленных запросов:
- по умолчанию
slow_query_logЗначение OFF означает, что журнал медленных запросов отключен. - может быть установлен
slow_query_logзначение для включения - пройти через
SHOW VARIABLES LIKE '%slow_query_log%';Проверьте, включен ли журнал медленных запросов mysql.
- по умолчанию
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
- Как включить журнал медленных запросов:
-
set global slow_query_log = 1;Включить журнал медленных запросов -
использовать
set global slow_query_log=1Включение журнала медленных запросов влияет только на текущую базу данных и будет недействительным при перезапуске MySQL.
-
mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.07 sec)
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
- Если вы хотите сделать его постоянным, вы должны изменить файл конфигурации my.cnf (то же самое верно и для других системных переменных)
- Исправлятьmy.cnfдокумент,
[mysqld]Добавьте или измените параметры ниже:slow_query_logиslow_query_log_fileПосле этого перезапустите сервер MySQL. - Кроме того, настройте следующие две строки вmy.cnfдокумент
- Исправлятьmy.cnfдокумент,
[mysqld]
slow_query_log =1
slow_query_log_file=/var/lib/mysql/Heygo-slow.log
- Параметры для медленных запросов
slow_query_log_file, который указывает путь хранения файла журнала медленных запросов, система по умолчанию предоставит файл по умолчаниюhost_name-slow.log(если параметр не указанslow_query_log_fileесли)
8.3.войти в медленный запрос
Итак, после включения журнала медленных запросов, какое участие SQL записывается в медленном запросе?
- Это контролируется параметром long_query_time.По умолчанию значение long_query_time равно 10 секундам. Команда:
SHOW VARIABLES LIKE 'long_query_time%';Просмотр порога для медленного SQL
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
- Вы можете использовать команду для изменения, или вы можетеmy.cnfизменены параметры.
- Если время работы точно равноlong_query_timeситуация не будет записана. То есть вmysqlИсходный код является суждениембольше, чем long_query_time,ине больше или равно.
8.3.1 Пример журнала медленных запросов
- Просмотр порогового времени медленного SQL, пороговое время по умолчанию составляет 10 с.
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
- Установите пороговое время для медленного SQL, мы устанавливаем его на 3 с
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
Почему пороговое время не меняется после установки?
- Вам нужно переподключиться или открыть новый сеанс, чтобы увидеть измененное значение.
- просмотреть глобальный
long_query_timeценность:show global variables like 'long_query_time'; обнаружено, что он действует
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
- Записывать медленный SQL для последующего анализа
-
A select sleep(4); Если он превышает 3 секунды, он обязательно будет записан в журнал.
-
mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
| 0 |
+----------+
1 row in set (4.00 sec)
- Файл журнала медленных запросов находится в каталоге /var/lib/mysql/ с суффиксом -slow.log.
[root@Heygo mysql]# cd /var/lib/mysql/
[root@Heygo mysql]# ls -l
总用量 176156
-rw-rw----. 1 mysql mysql 56 8月 3 19:08 auto.cnf
drwx------. 2 mysql mysql 4096 8月 5 10:36 db01
-rw-rw----. 1 mysql mysql 7289 8月 3 22:38 Heygo.err
-rw-rw----. 1 mysql mysql 371 8月 5 12:58 Heygo-slow.log
-rw-rw----. 1 mysql mysql 79691776 8月 5 10:36 ibdata1
-rw-rw----. 1 mysql mysql 50331648 8月 5 10:36 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 8月 3 19:08 ib_logfile1
drwx------. 2 mysql mysql 4096 8月 3 19:08 mysql
srwxrwxrwx. 1 mysql mysql 0 8月 3 22:38 mysql.sock
drwx------. 2 mysql mysql 4096 8月 3 19:08 performance_schema
- Просмотр содержимого журнала медленных запросов
[root@Heygo mysql]# cat Heygo-slow.log
/usr/sbin/mysqld, Version: 5.6.49 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 200805 12:58:01
# User@Host: root[root] @ localhost [] Id: 11
# Query_time: 4.000424 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1596603481;
select sleep(4);
- Запросите, сколько медленных записей запросов в текущей системе:
show global status like '%Slow_queries%';
mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 1 |
+---------------+-------+
1 row in set (0.00 sec)
Версия конфигурации журнала медленных запросов
Настраивается в узле [mysqld] в файле /etc/my.cnf.
slow_query_log=1；
slow_query_log_file=/var/lib/mysql/Heygo-slow.log
long_query_time=3；
log_output=FILE
Команда анализа журнала mysqldumpslow
Что такое mysqldumpslow?
В производственной среде, если вы хотите вручную проанализировать журнал, найти и проанализировать SQL, это, очевидно, ручная работа.MySQL предоставляет инструмент анализа журнала mysqldumpslow.
Просмотр справочной информации для mysqldumpslow
[root@Heygo mysql]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
объяснение параметра mysqldumpshow
-
s: указывает, каким образом сортировать -
c: Количество посещений -
l: время блокировки -
r: возврат записи -
t: время запроса -
al: Среднее время блокировки -
ar: среднее количество возвращенных записей. -
at: Среднее время запроса -
t: то есть, сколько частей данных нужно вернуть -
g: за которым следует обычный шаблон соответствия, без учета регистра
Руководство по общим параметрам
- Получите 10 SQL-запросов, которые возвращают наибольшее количество наборов записей
mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log
- Получите 10 самых посещаемых SQL-запросов
mysqldumpslow -s c- t 10/var/lib/mysql/Heygo-slow.log
- Получите 10 лучших операторов запроса с левыми соединениями, отсортированными по времени
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/Heygo-slow.log
- Также рекомендуется комбинировать | и многое другое при использовании этих команд, иначе могут быть всплески экрана
另外建议在使用这些命令时结合 | 和more使用，否则有可能出现爆屏情况
8.4, скрипт пакетных данных
8.4.1, создать таблицу
- Создать таблицу SQL
CREATE TABLE dept
(
deptno int unsigned primary key auto_increment,
dname varchar(20) not null default "",
loc varchar(8) not null default ""
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE emp
(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,
ename varchar(20) not null default "",
job varchar(9) not null default "",
mgr mediumint unsigned not null default 0,
hiredate date not null,
sal decimal(7,2) not null,
comm decimal(7,2) not null,
deptno mediumint unsigned not null default 0
)ENGINE=INNODB DEFAULT CHARSET=utf8;
8.4.2 Настройка параметров
- Создайте функцию, если сообщается об ошибке: Эта функция не имеет ни одного из DETERMINISTIC......
- Так как мы включили журнал медленных запросов, потому что мы включили bin-log, нам пришлось указать параметр для нашей функции.
- log_bin_trust_function_creators = OFF , по умолчанию для функции должен быть передан параметр
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
- пройти через
set global log_bin_trust_function_creators=1;Мы можем обойтись без передачи параметров для функции
mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)
- После добавления параметров таким образом, если mysqld перезапустится, вышеуказанные параметры снова исчезнут, а постоянный метод будет изменен в конфигурационном файле.
- Под окнами: добавьте под узел my.ini --> [mysqld]log_bin_trust_function_creators=1
- Под linux: /etc/my.cnf --> [mysqld] добавить под узелlog_bin_trust_function_creators=1
8.4.3 Создайте функцию, чтобы убедиться, что каждый фрагмент данных отличается
- функция для генерации случайных строк
delimiter $$ # 两个 $$ 表示结束
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;
end $$
- Функция, которая случайным образом генерирует номера отделов
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*10);
return i;
end $$
8.4.4, создайте хранимую процедуру
- Создайте хранимую процедуру для вставки данных в таблицу emp.
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $$
- Создайте хранимую процедуру для вставки данных в таблицу отделов.
delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
until i=max_num
end repeat;
commit;
end $$
8.4.5, вызов хранимых процедур
- Вставьте 10 записей в таблицу отделов.
DELIMITER ;
CALL insert_dept(100, 10);
mysql> select * from dept;
+--------+---------+--------+
| deptno | dname | loc |
+--------+---------+--------+
| 101 | aowswej | syrlhb |
| 102 | uvneag | pup |
| 103 | lps | iudgy |
| 104 | jipvsk | ihytx |
| 105 | hrpzhiv | vjb |
| 106 | phngy | yf |
| 107 | uhgd | lgst |
| 108 | ynyl | iio |
| 109 | daqbgsh | mp |
| 110 | yfbrju | vuhsf |
+--------+---------+--------+
10 rows in set (0.00 sec)
- Вставьте записи 50w в таблицу emp
DELIMITER ;
CALL insert_emp(100001, 500000);
mysql> select * from emp limit 20;
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| 1 | 100002 | ipbmd | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 101 |
| 2 | 100003 | bfvt | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 107 |
| 3 | 100004 | | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 109 |
| 4 | 100005 | cptas | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 101 |
| 5 | 100006 | ftn | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 108 |
| 6 | 100007 | gzh | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 102 |
| 7 | 100008 | rji | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 100 |
| 8 | 100009 | | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 106 |
| 9 | 100010 | tms | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 100 |
| 10 | 100011 | utxe | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 101 |
| 11 | 100012 | vbis | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 104 |
| 12 | 100013 | qgfv | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 104 |
| 13 | 100014 | wrvb | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 105 |
| 14 | 100015 | dyks | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 109 |
| 15 | 100016 | hpcs | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 101 |
| 16 | 100017 | fxb | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 108 |
| 17 | 100018 | vqxq | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 102 |
| 18 | 100019 | rq | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 102 |
| 19 | 100020 | l | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 106 |
| 20 | 100021 | lk | salesman | 1 | 2020-08-05 | 2000.00 | 400.00 | 100 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
20 rows in set (0.00 sec)
9. Показать профиль
9.1.Что такое показать профиль?
- Именно mysql обеспечивает потребление ресурсов, которые можно использовать для анализа выполнения операторов в текущем сеансе. Настройка измерений, которые можно использовать для SQL
- Официальный сайт:Dev.MySQL.com/doc/Furious/…
- По умолчанию параметр выключен и сохраняются последние 15 прогонов.
9.2.Шаги анализа
1) Проверьте, поддерживает ли текущая версия SQL Show Profile.
-
show variables like ‘profiling%’;Проверьте, включен ли параметр «Показать профиль»
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.01 sec)
2),Включите функцию «Показать профиль», которая по умолчанию отключена и должна быть включена перед использованием.
-
set profiling=on;Открыть Показать профиль
mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.00 sec)
3),запустить SQL
- обычный SQL
select * from tbl_emp;
select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;
- медленный SQL
select * from emp group by id%10 limit 150000;
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;
4),Посмотреть Результаты
- Просмотрите результаты с помощью команды show profiles;
mysql> show profiles;
+----------+------------+----------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------------------+
| 1 | 0.00052700 | show variables like 'profiling%' |
| 2 | 0.00030300 | select * from tbl_emp |
| 3 | 0.00010650 | select * from tbl_emp e inner join tbl_dept d on e.'deptId' = d.'id' |
| 4 | 0.00031625 | select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id |
| 5 | 0.00042100 | select * from tbl_emp e left join tbl_dept d on e.deptId = d.id |
| 6 | 0.38621875 | select * from emp group by id%20 limit 150000 |
| 7 | 0.00014900 | select * from emp group by id%20 order by 150000 |
| 8 | 0.38649000 | select * from emp group by id%20 order by 5 |
| 9 | 0.06782700 | select COUNT(*) from emp |
| 10 | 0.35434400 | select * from emp group by id%10 limit 150000 |
+----------+------------+----------------------------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)
5),Диагностика SQL
-
show profile cpu, block io for query SQL编号;Просмотр конкретного потока выполнения оператора SQL и времени, затраченного на каждый шаг
mysql> show profile cpu, block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000055 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000046 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000089 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
Примечания к параметрам:
ALL: показать всю служебную информацию
BLOCKIO: блок отображения служебных данных, связанных с вводом-выводом
CONTEXT SWITCHES: накладные расходы, связанные с переключением контекста
CPU: Отображение служебной информации, связанной с процессором.
IPC: Отображает служебную информацию об отправке и получении.
MEMORY: Отображение служебной информации, связанной с памятью
PAGE FAULTS: Отображение служебной информации, связанной с ошибкой страницы.
SOURCE: Отображает служебную информацию, связанную с Source_function, Source_file, Source_line.
SWAPS: Отображает информацию о накладных расходах, связанных с количеством обменов.
Выводы для ежедневного развития
converting HEAP to MyISAM: Результат запроса слишком велик, а памяти недостаточно для переноса на диск.
Creating tmp table: создать временную таблицу, mysql сначала копирует данные во временную таблицу, а затем удаляет временную таблицу, когда она израсходована
Copying to tmp table on disk: Скопируйте временную таблицу в памяти на диск, опасно! ! !
locked: заблокировать таблицу
10. Глобальный журнал запросов
Никогда не включайте эту функцию в рабочей среде.
10.1, настроить и включить глобальный журнал запросов
- В my.cnf mysql настройки следующие:
# 开启
general_log=1
# 记录日志文件的路径
general_log_file=/path/logfile
# 输出格式
log_output=FILE
10.2.Кодирование включить глобальный журнал запросов
- Выполните следующую команду, чтобы включить глобальный журнал запросов
set global general_log=1;
set global log_output='TABLE';
- После этого выполняемый вами оператор sql будет записан в таблицу general_log в библиотеке mysql, которую можно просмотреть с помощью следующей команды
select * from mysql.general_log;
mysql> select * from mysql.general_log;
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
| 2020-08-05 14:41:07 | root[root] @ localhost [] | 14 | 0 | Query | select * from emp group by id%10 limit 150000 |
| 2020-08-05 14:41:12 | root[root] @ localhost [] | 14 | 0 | Query | select COUNT(*) from emp |
| 2020-08-05 14:41:30 | root[root] @ localhost [] | 14 | 0 | Query | select * from mysql.general_log |
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
3 rows in set (0.00 sec)