SQL优化的7个方法

程序员恰恰 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 将结果集存储在临时文件中并对其进行排序。这并不一定意味着性能问题，但是可能会影响查询的执行时间，尤其是当处理大量数据时。

总结：

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）

四、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字段添加索引

记住一点，根据索引字段去更新数据即可！（因为索引字段相当于上的行锁，非索引字段上的表锁）。

文章转载自程序员恰恰，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。