MySQL入门系列12-SQL优化

原创

wx59bcc77095d22 2023-09-15 08:45:28 博主文章分类：MySQL ©著作权

文章标签 MySQL 优化 文章分类 MySQL 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者wx59bcc77095d22的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、插入数据

我们一次性往数据库中的表插入多条记录，可以从三个方面进行优化。

1.批量插入数据

由于每次insert都需要与数据库建立连接，进行网络传输导致一定的性能损失，我们可以选择一次性插入多条记录，代替一条一条插入数据。

insert into t_user(name,age,status) VALUES('aa',20,'1'),('bb',21,'0'),('dd',22,'0');

2.手动控制事务

由于MySQL中的事务提交方式默认是自动提交的，也就是当我们执行完一条插入语句后，它就会自动提交事务，这种情况会涉及频繁的事务开启和提交，因此我们还可以手动控制事务，减少事务开关锁花费的时间。

#开启事务
START TRANSACTION;
insert into t_user(name,age,status) values('zs',20,'1'),('lisi',21,'1');
insert into t_user(name,age,status) values('ww',22,'1'),('zl',23,'1');
#提交
COMMIT;

3.主键顺序插入

由于主键索引的存在，每次插入数据都可能会重新组织索引结构，因此主键顺序插入的性能要高于乱序插入。

#顺序插入
insert into t_user values(4,'kk',28,'1'),(5,'nn',29,'1');
#乱序插入
insert into t_user values(8,'jl',28,'1'),(7,'sd',29,'1');

4.load指令

如果一次性需要插入大批量数据(比如几百万条的记录),使用insert语句插入性能很低，此时我们可以使用MySQL数据库提供的load指令进行插入。通过load指令我们可以一次性将本地文件中的数据全部加载进数据库表结构中。

a.客户端连接服务端时加上参数--local-infile

mysql --local-infile -uroot -p123456

b.设置全局参数local_infile的值为1，开启从本地加载文件导入数据的开关

set global local_infile=1;

c.执行load指令将准备好的数据，加载到表结构中

load  data  local  infile  '/root/sql1.log'  into  table  tb_user  fields
terminated  by  ','  lines  terminated  by  '\n' ;

--  local  infile  '需要加载的文件路径'
--  into  table  需要插入到哪张表
--  fields terminated  by  '字段分隔符'
--  lines  terminated  by  '行分隔符'

二、主键优化

1.降低主键的长度

对于一张表来说主键索引只有一个，但是二级索引可能有多个，在二级索引的叶子节点当中挂的就是数据的主键，因此如果主键长度比较长且二级索引比较多，将会占用大量的磁盘空间。

2.顺序插入

在条件允许的情况下，使用AUTO_INCREMENT自增主键，顺序插入数据。在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，行数据都是存储在聚集索引的叶子节点上的。

3.避免对主键的修改

尽量不要使用有意义的值作为主键，如身份证号码，避免在进行业务操作对主键产生修改操作。这是因为插入修改操作都会导致数据库重新组织索引结构。

三、ordre by优化

1.排序说明

MySQL的排序，有两种方式：

Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

我们当前的用户表中存在一个唯一索引。

MySQL入门系列12-SQL优化_优化

如果我们根据name进行排序将会使用到索引。

MySQL入门系列12-SQL优化_优化_02

如果我们把name的索引删除掉，我们再次用同样的方法进行测试，发现变成了Using filesort.

MySQL入门系列12-SQL优化_MySQL_03

MySQL入门系列12-SQL优化_MySQL_04

2.其他说明

由于我们在MySQL创建索引默认的叶子节点是从小到大排序的，如果我们在查询的时候，order by 使用desc降序的话除了会出现Using index还会出现Backward index scan,这个代表反向扫描索引。在 MySQL8版本中，支持降序索引，我们也可以创建降序索引。

MySQL入门系列12-SQL优化_MySQL_05

排序时,也需要满足最左前缀法则（与where条件不同的是，此时必须按照创建索引时的顺序进行排序）,否则也会出现Using filesort。

我们当前存在一个name和age的联合索引。

MySQL入门系列12-SQL优化_MySQL_06

排序的时候也要根据这个顺序来排序，不然也不会走索引。

MySQL入门系列12-SQL优化_MySQL_07

如果调换name和age的顺序就会出现Using filesort

MySQL入门系列12-SQL优化_MySQL_08

四、group by优化

与order by类似，分组就相当于大范围的排序。我们同样可以通过使用索引字段进行分组来提高效率。

我们对name进行索引的情况下:

MySQL入门系列12-SQL优化_优化_09

MySQL入门系列12-SQL优化_优化_10

如果我们删除索引再次查询，返回Using temporary，这个性能要低于Using index。

MySQL入门系列12-SQL优化_MySQL_11

五、limit优化

在数据量比较大的时候，如果进行limit分页查询，在查询时越往后，分页查询效率越低。这是因为当在进行分页查询时，例如执行limit 10000,10,此时需要MySQL排序前10000记录，仅仅返回10000-10010的记录，其他记录丢弃，查询排序的代价非常大。

MySQL入门系列12-SQL优化_优化_12

通过测试我们会看到，分页越往后，查询效率越低。

一般在进行分页查询的时候，我们可以通过覆盖索引+子查询的形式进行优化提高性能。

SQL语句参考:

select * from t_protocol_log limit 0,10;
select * from t_protocol_log p,(select id from t_protocol_log order by id limit 1000000,10)a WHERE p.id=a.id;
select * from t_protocol_log p,(select id from t_protocol_log order by id limit 2000000,10)a WHERE p.id=a.id;

执行SQL语句，发现我们的SQL还是提高了查询的效率。

MySQL入门系列12-SQL优化_MySQL_13

六、count优化

在数据量很大的情况下执行count操作是非常耗时的。

MyISAM引擎把一个表的总行数存在磁盘上，因此执行count(*)的时候直接返回这个数，效率很高，如果是带条件的count,MyISAM也很慢。

InnoDB引擎在执行count(*)的时候，需要把数据一行行的从引擎读出来，然后累计，比较耗时。

对count的优化主要有以下几种策略:

count用法	含义
count(主键)	InnoDB引擎会遍历整张表，把每一行的主键ID都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加(主键不可能为null)
count(字段)	没有not null约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null,不为null,计数累加。有not null约束:InnoDB引擎会遍历整张表把每一行的字段都取出来，返回给服务层，直接按行进行累加。
count(数字)	InnoDB引擎遍历整张表，但不取值，服务层对应返回的每一行，放一个数字1进去，直接按行进行累加。
count(*)	InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。