<返回更多

SQL优化的七个方法,你会哪个?

2024-03-07  微信公众号  程序员恰恰
加入收藏

一、插入数据优化

 

普通插入:

在平时我们执行insert语句的时候,可能都是一条一条数据插入进去的,就像下面这样。

INSERT INTO `department` VALUES(1, '研发部(RD)', '2层'),
INSERT INTO `department` VALUES(2, '人事部(RD)', '1层'),
INSERT INTO `department` VALUES(3, '后勤部(RD)', '4层'),
INSERT INTO `department` VALUES(3, '财务部(RD)', '4层'),

 

现在我们考虑以下三个方面对insert操作进行优化。

1、采用批量插入(一次插入的数据不建议超过1000条),

执行批量插入,一次性插入的数据不建议超过1000条,如果要插入上万条数据的话,可以将其分割为多条insert语句进行插入。

INSERT INTO `department` (`id`, `deptName`, `address`)
VALUES
 (1, '研发部(RD)', '2层'),
 (2, '人事部(HR)', '3层'),
 (3, '市场部(MK)', '4层'),
 (4, '后勤部(MIS)', '5层'),
 (5, '财务部(FD)', '6层');

 

2、手动提交事务

因为一条一条insert插入的时候,如果是自动提交事务,我们的MySQL会频繁的开启、执行事务;

所以我们可以考虑在在大段insert单条插入语句执行的时候,用手动提交事务的方式来执行。

begin;
INSERT INTO `department` (`deptName`, `address`)VALUES('研发部(RD)', '2层'),('人事部(HR)', '3层'),('市场部(MK)', '4层'),('后勤部(MIS)', '5层');
INSERT INTO `department` (`deptName`, `address`)VALUES('研发部(RD)', '2层'),('人事部(HR)', '3层'),('市场部(MK)', '4层'),('后勤部(MIS)', '5层');
INSERT INTO `department` (`deptName`, `address`)VALUES('研发部(RD)', '2层'),('人事部(HR)', '3层'),('市场部(MK)', '4层'),('后勤部(MIS)', '5层');
commit;

图片图片

3、大批量插入

如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令插入。

-- 1、首先,检查一个全局系统变量 'local_infile' 的状态, 如果得到如下显示 Value=OFF,则说明这是不可用的
show global variables like 'local_infile';
-- 2、修改local_infile值为on,开启local_infile
set global local_infile=1;
-- 3、加载数据 
/*
    脚本文件介绍 :
    每一列数据用","分割",
    每一行数据用 n'回车分割   
*/
load data local infile 'D:\sql_data\sql1.log' into table tb_user fields terminated by ',' lines terminated by 'n';

经过测试,导入100万行数据,仅仅耗时16.84s

图片

注意事项:使用load的时候要按主键顺序插入,主键顺序插入的性能要高于乱序插入的性能。

二、主键优化

在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表。

我们的InnoDB存储引擎的聚集索引结果中,B+Tree的叶子结点下存储的是row,行数据,并且是根据主键顺序存放。所有的数据都会出现在叶子结点,而非叶子结点仅仅起到了索引的作用。

图片图片

主键设计原则:

1、满足业务需求的情况下,尽量降低主键的长度

2、插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键

3、尽量不要使用UUID做主键或者是其他自然主键,如身份证号

4、业务操作时,避免对主键的修改

三、order by优化

 

我们先了解两个概念,前面我们在Explatin详解文章中提到过:SQL性能分析工具ExplAIn详解

Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区 sort buffer 中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高

我们对order by的优化就是尽可能优化为Using index。

新建表:employee

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `cus_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_dep_id_age` (`name`,`dep_id`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;

不使用索引情况:

图片图片

新建联合索引:name,dep_id,age

#创建联合索引
CREATE INDEX idx_name_dep_id_age ON employee (name, dep_id, age);
#查询当前索引
show INDEX from employee

#删除索引
DROP INDEX idx_name_dep_id_age ON employee;

 

如果order by字段全部使用升序排序或者降序排序,则都会走索引.

图片图片

#有索引情况顺序
Explain select name,dep_id,age from employee order  by name,dep_id,age ;
#有索引情况倒序
Explain select name,dep_id,age from employee order  by name desc,dep_id desc,age desc;

 

但是如果一个字段升序排序,另一个字段降序排序,则不会走索引,explain的extra信息显示的是Using index, Using filesort.

图片图片

#有索引情况有的顺序,有的倒序
Explain select name,dep_id,age from employee order  by name desc,dep_id asc,age desc;

 

如果要优化掉Using filesort,此时我们可以再创建一个联合索引,即name按倒序,dep_id按升序创建索引,就可以解决。

图片图片

注意的是虽然我已经创建了覆盖这些列的联合索引 idx_name_dep_id_age2,但 MySQL 优化器仍然可能会决定使用文件排序(filesort)来执行这个顺序的排序操作。

内存中无法容纳整个结果集时,MySQL 将结果集存储在临时文件中并对其进行排序。这并不一定意味着性能问题,但是可能会影响查询的执行时间,尤其是当处理大量数据时。

总结:

四、group by优化

 

先删除全部的索引(保留主键id)

#删除索引
DROP INDEX idx_name_dep_id_age2 ON employee;

#无索引情况
Explain select name,dep_id,age ,count(*)from employee group  by name,dep_id,age ;

无索引情况下,分组,出现filesort,type为All出现了全表扫描。

图片图片

新建联合索引,name,dep_id,age再观察。

#创建联合索引
CREATE INDEX idx_name_dep_id_age ON employee (name, dep_id, age);

#有索引情况
Explain select name,dep_id,age,count(*)from employee group  by name,dep_id,age ;

可见用到了索引

图片图片

总结:

如索引为idx_user_pro_age_stat,则句式可以是select ... where profession order by age,这样也符合最左前缀法则

五、limit优化

 

语法复习:

#0表示起始位置,10表示每一页展示的数据。
select * from student_info limit 0,10;

 

这条查询执行的速度非常快,但是如果我们将起始位置设置为100000呢?

limit分页查询在大数据量的时候,查询效率同样会非常的慢,例如一个常见又非常头疼的问题就是limit 2000000,10 此时需要MySQL排序前200010条记录,仅仅返回200000-2000010的记录,其他记录丢弃,查询排序的代价非常大。

优化方案:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化

例如:利用主键id,先获取要查询的10是个id.

explain select *
from student_info t,(select id from student_info order by id limit 2000000,10) a
where t.id = a.id;

 

图片图片

 

或者使用范围查询方式优化:

图片图片

 

explain select *
from student_info where id > 2000000 limit 10

 

六、COUNT优化

count是一个聚合函数,用于求取符合条件的总数据量。

MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高。

InnoDB引擎就比较麻烦,它执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累计计数。

count的几种用法:

count()是一个聚合函数,对于返回的结果集,一行行地判断,如果count函数的参数不是NULL,累计值就加1,否则不加,最后返回累计值。

用法:count(*)、count(主键)、count(字段)、count(1)、count(0).

count(主键):InnoDB会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null)。

count(字段):没有not null约束的话,InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加;有not null约束的话,InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加。

count(1):InnoDB引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1”进去,直接按行进行累加。

count(*):InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。

按照效率排序:count(字段)<count(主键)<count(1)<count(*)

count(*):

图片图片

count(name):

图片图片

count(1):

图片图片

七、UPDATE优化

InnoDB 的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。

测试1:

开启两个会话:更新student表的数据,会话1更新id为2的数据,会话2更新id为2的数据

图片图片

会话1:

update student set name = '123' where id = 1;

由于id有主键索引,所以只会锁id = 1这一行;

会话2: id=2,当然会立马执行结束,不用等待会话1提交事务

update student set name = '123' where id = 2;

测试2:

开启两个会话:更新student表的数据,会话1更新name(name字段无索引)为2的数据,会话2更新id为2的数据

update student set name = '123' where name = 'test';

图片图片

由于name没有索引,所以会把整张表都锁住,导致会话2等待会话1提交事务。

解决方法:给name字段添加索引

记住一点,根据索引字段去更新数据即可!(因为索引字段相当于上的行锁,非索引字段上的表锁)。
关键词:SQL优化      点击(8)
声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多SQL优化相关>>>