В предыдущих статьях была представлена базовая структура данных mysql и объяснены артефакты оптимизации mysql. Некоторые друзья в фоновом режиме сказали, что Сяоцян только представил концепции, и он все еще был сбит с толку при использовании.Он настоятельно просил Сяоцяна написать настоящую оптимизацию SQL.После двух дней сортировки и обобщения на выходных фактическая оптимизация SQL была недавно выпущена. 90% будет введена оптимизация sql, если длина будет слишком большой, она будет разделена на 3 статьи.
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(20) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';
insert into employees(name,age,position,hire_time) values('LiLei', 22, 'manager', NOW())
insert into employees(name,age,position,hire_time) values('HanMeimei', 23, 'dev', NOW())
insert into employees(name,age,position,hire_time) values('Lucy', 23, 'dev', NOW())
полное соответствие стоимости
Тип поля индекса varchar(n): 2 байта хранят длину строки, если это utf-8, то длина 3n+2
EXPLAIN select * from employees where name='LiLei';
EXPLAIN select * from employees where name='LiLei' AND age = 22;
EXPLAIN select * from employees where name='LiLei' AND age = 22 AND position = 'manager';
правило крайнего левого префикса
Если индекс состоит из нескольких столбцов, он подчиняется правилу крайнего левого префикса. Ссылается на запрос, начинающийся с самого левого переднего столбца индекса и не пропускающий столбцы в индексе. Следующие два SQL-запроса не будут проходить через индекс в соответствии с правилом крайнего левого префикса.
EXPLAIN select * from employees where age = 22 AND position='manager';
EXPLAIN select * from employees where position ='manager';
аннулирование индекса
Не выполняйте никаких операций (вычисления, функции, преобразования типов) над столбцом индекса, что приведет к сбою индекса и переходу к полному сканированию таблицы.
EXPLAIN select * from employees where name='LiLei';
EXPLAIN select * from employees where left(name, 3)='LiLei';
Добавьте нормальный индекс к найму_time:
alter table `employees` ADD INDEX `idx_hire_time`(`hire_time`) USING BTREE;
EXPLAIN select * from employees where date(hire_time) = '2019-08-25';
Восстановить исходное состояние индекса
ALTER TABLE `employees` DROP INDEX `idx_hire_time`;
Механизм хранения не может использовать столбец справа от условия диапазона в индексе.
-- EXPLAIN SELECT * FROM employees WHERE name ='LiLei' AND age=22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name ='LiLei' AND age>22 AND position ='manager';
индекс покрытия
Попробуйте использовать покрывающие индексы (запросы, которые обращаются только к индексу (столбец индекса содержит столбец запроса)) и уменьшите оператор select *.
EXPLAIN SELECT name,age,position FROM employees WHERE name ='LiLei' AND age=22 AND position ='manager';
Условное суждение
MySQL не может использовать индексы при использовании неравенства (! = или ), что приведет к полному сканированию таблицы.
EXPLAIN SELECT * FROM employees WHERE name !='LiLei' ;
Нулевое оценочное суждение
имеет значение null, не является значением null и не может использовать индекс
EXPLAIN SELECT * FROM employees WHERE name is null;
like
Подобно тому, как начинается с подстановочного знака ('$abc'), аннулирование индекса MySQL станет операцией полного сканирования таблицы.
EXPLAIN SELECT * FROM employees WHERE name LIKE '%Lei';
Строковый индекс без одинарных кавычек не работает
EXPLAIN SELECT * FROM employees WHERE name ='1000';
EXPLAIN SELECT * FROM employees WHERE name =1000;
или&используется меньше
Используйте меньше или или в. При запросе с ним MySQL не обязательно использует индекс.Внутренний оптимизатор MySQL оценит, следует ли использовать индекс в целом, основываясь на нескольких факторах, таких как соотношение индексов и размер таблицы.
EXPLAIN SELECT * FROM employees WHERE name ='LiLei' or name='HanMeimei';
Оптимизация запроса диапазона
Добавьте однозначный индекс к возрасту
ALTER TABLE `employees`ADD INDEX `idx_age`(`age`) USING BTREE;
EXPLAIN select * from employees where age > 1 and age <= 2000;
Причина отказа от использования индекса: внутренний оптимизатор MySQL будет оценивать, следует ли использовать индекс в целом, на основе множества факторов, таких как коэффициент извлечения и размер таблицы. В этом примере индекс может не использоваться, поскольку один запрос с большим объемом данных приводит к тому, что оптимизатор, в конце концов, отказывается от использования индекса. Метод оптимизации: вы можете разделить большой диапазон на несколько небольших диапазонов.
Еще не подписались на мой официальный аккаунт?
- Отсканируйте QR-код в конце статьи и перейдите в официальный аккаунт [Xiaoqiang's Advanced Road], чтобы получить следующее:
- Учебные материалы: видеоуроки 1T: обучающие видеоролики по интерфейсу и серверной части Javaweb, обучающие видеоролики по машинному обучению/искусственному интеллекту, обучающие видеоролики по системе Linux, видеоуроки по IELTS;
- Более 100 книг: классические обязательные к просмотру книги, включая C/C++, Java, три языка программирования Python и сборник решений задач LeetCode;
- Программные инструменты: почти все программное обеспечение, которое вы можете использовать в процессе программирования;
- Исходный код проекта: 20 исходный код проекта JavaWeb.