当前位置：首页 > 应用开发

工作中遇到的99%SQL优化，这里都能给你解决方案

前几篇文章介绍了mysql的工作给解底层数据结构和mysql优化的神器explain。后台有些朋友说小强只介绍概念，中遇平时使用还是到的都一脸懵，强烈要求小强来一篇实战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 age=17;

索引失效

不要在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描。

EXPLAIN select * from employees where name=LiLei;

EXPLAIN select * from employees where left(name, 3)=LiLei;

给hire_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;