SQL性能优化技巧，常见优化10经验

最新推荐文章于 2024-09-04 08:54:10 发布

十一技术斩

最新推荐文章于 2024-09-04 08:54:10 发布

阅读量1.1k

点赞数

文章标签： sql 性能优化 mysql java 数据库

本文链接：https://blog.csdn.net/uuqaz/article/details/124665634

版权

本文总结了SQL性能优化的十大经验，包括全值匹配、最左前缀原则、避免索引列操作等，以及order by、group by、分页查询、join查询、in和exists优化和count(*)查询的优化方法。通过这些技巧，可以显著提升SQL查询效率。

摘要由CSDN通过智能技术生成

里就给小伙伴们带来工作中常用的一些 SQL 性能优化技巧总结，包括常见优化十经验、order by 与 group by 优化、分页查询优化、join 关联查询优化、in 和 exsits 优化、count(*)查询优化。

一、常见优化十经验

其实这个十经验不一定准确，通过上一篇 MySQL再深入执行计划之trace工具我们已经知道 MySQL 执行查询语句时会进行成本分析，数据量和实际数据值会影响到 MySQL 的实际查询过程

-- 示例表
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('itwxe',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('weiwei', 23,'test',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('leilei',23,'dev',NOW());

-- 插入10w条测试数据
drop procedure if exists insert_employees; 
delimiter $$
create procedure insert_employees()        
begin
  declare i int;                    
  set i = 1;                          
  while(i <= 100000)do                 
    insert into employees(name, age, position) values(CONCAT('itwxe', i), rand() * 42 + 18, 'dev');  
    set i = i + 1;                       
  end while;
end$$
delimiter ;
call insert_employees();

1. 尽量全值匹配

explain select * from employees where name = 'itwxe';
explain select * from employees where name = 'itwxe' and age = 22;
explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';

记住这三个 key_len 的值，idx_name_age_position (name,age,position)由这三个字段组成，74代表使用了 name 列；78代表使用了 name,age 列；140代表使用了 name,age,position 列。

2. 最左前缀原则

在使用联合索引的时候要特别注意最左前缀原则，即查询从联合索引的最左前列开始并且不跳过索引中的列。

explain select * from employees where name = 'itwxe' and age = '18';
explain select * from employees where name = 'itwxe' and position = 'manager';
explain select * from employees where position = 'manager';

应该也是比较好理解的，不过需要注意的是和查询 SQL 书写的顺序无关，最左指的是联合索引创建时列的顺序。例如 where 中颠倒顺序还是会使用 idx_name_age_position (name,age,position) 中的三个列索引查询。

explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';
explain select * from employees where age = 22 and position = 'manager' and name = 'itwxe';
explain select * from employees where position = 'manager' and name = 'itwxe' and age = 22;

可以看到即使颠倒了顺序，三个的执行计划也是一毛一样的。

3. 不在索引列上做任何操作(计算、函数、(自动/手动)类型转换)，会导致索引失效而转向全表扫描

需要注意的是这里说的索引列任何操作(计算、函数、(自动/手动)类型转换)不做操作指的是 where 条件之后的，而不是查询结果字段里面的。

例如对 name 列进行 left 函数操作。

explain select * from employees where name = 'weiwei';
explain select * from employees where left(name,6) = 'weiwei';

4. 存储引擎不能使用索引中范围条件右边的列

explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';
explain select * from employees where name = 'itwxe' and age > 22 and position = 'manager';