08 SQL优化

最新推荐文章于 2024-05-08 12:34:04 发布

bat在等我

最新推荐文章于 2024-05-08 12:34:04 发布

阅读量438

点赞数 1

分类专栏： Mysql 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/m0_46836425/article/details/128087237

版权

Mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

上一篇文章记录了索引的创建、使用、设计，除了索引方面还需要注意平日对于SQL的使用，对SQL进行优化；SQL的优化是建立在索引使用的基础上

这篇笔记将从以下7个方面对SQL进行优化。

1. 插入数据

使用批量插入,避免循环单条插入

注意批量插入不要超过1000条；若超出则可以将记录进行拆分后分批插入

手动进行事务的提交

开启和提交事务比较耗时，可以执行完多个命令后，手动提交事务

主键顺序插入

插入的时候按照主键从小到大的顺序进行插入，降低维护索引耗费的时长

大批量插入数据时使用load指令

如果有大批量数据需要导入，我们可以使用load指令将表的磁盘文件导入到数据库中

load指令

# 在客户端连接服务端的时候，加上参数--local-infile
mysql --local-file -u root -p;
# 查看参数local_infile，默认值为0
select @@local_infile;
# 设置全局允许加载MySQL数据文件
set global local_infile = 1;
# 加载数据， load data local infile为固定格式
load date local infile '/root/sql1.log' into table 'table_name' fields terminated by ',' lines terminated '\n';

2. 主键优化

在Innodb引擎中，表数据是根据索引组织存放的，这种存储方式称为索引组织表

主键优化方式：

降低主键长度

节省空间，使页中存放更多数据，减少树的层数

尽量顺序插入，降低页分裂

降低页分裂

不要使用uuid，最好要有顺序例如自增

降低页分裂

业务操作不要修改主键

降低页分裂和页合并

页分裂：

Innodb中数据存储的最小单元为页，在主键顺序插入时，会按顺序填充页，一个页满了之后继续填充下一个页形成双向链表

但是当主键乱序插入的时候，假设有A、B两个页，两个页都是满的，由于乱序插入需要在A、B两个页中间插入一条记录，这时需要A分裂出一半数据放入新页C中同时把该记录放入C中；由于页的分裂比较浪费性能，因此插入的时候最好按照主键从小到大的顺序插入

插入和更新操作都可以触发页分裂

参考：https://blog.csdn.net/weixin_44228698/article/details/119057511

页合并（拓展）：

innodb中删除一条记录时并不是立即删除，而是在页中标记为已删除，使得其占用空间可以被其他记录引用，在页合并的时候彻底删除掉

当一个页中被删除的数据达到MERGE_THRESHOLD（默认为页体积的50%）时，Innodb开始寻找该页前或后的页判断能否合并这两个页，当另一个页正好使用的空间不足50%，就可以合并这两个页为一个，空白的那个页可以继续记录数据

删除和更新可以触发页合并

页分裂图解

在这里插入图片描述

页合并图解

在这里插入图片描述

3. order by优化

使用explain查看order by语句执行计划，在extra中有两种：Using index和Using filesort两种

Using index：通过索引可以直接完成排序，效率很高
Using filesort：通过索引或者全表扫描找到数据后；需要将数据加载到sort buffer排序缓冲区中进行排序；所有不是通过索引直接返回数据的都是File sort排序；效率较低

因此我们如果优化order by语句也是通过索引来进行入手

order by也需要遵守最左前缀法则，如果order by字段没有最左侧的索引，那么索引将失效
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC、DESC）
如果不可避免要使用filesort，在大数据量的时候可以增加sort_buffer_size设置排序缓冲区的大小（默认256k）

4. group by优化

与order by优化类似，使用explain查看group by执行计划，发现extra中也有两种：Using temporal 和Using index两种

Using temporal：使用临时表，效率较低
Using index：直接通过索引返回记录

因此我们如果优化group by语句也是通过索引来进行入手

遵守最左前缀法则，避免索引失效

5. limit优化

一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

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

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

6. count优化

通常我们使用count的时候有四种方式：count(*)，count(主键)， count(1)， count(某一列)；对于Myisam引擎，磁盘直接将一个表的总行数记录在了引擎上，使用count(*)的时候直接返回了这个总行数，而Innodb引擎需要遍历每一行将数据从引擎中读出来再累计计数

四种方式的区别：

count(*)：遍历每一行，但是不取数据，服务层直接进行累加
count(1)：遍历每一行，但是不取数据，服务层在每一行中加入一个1，直接按行进行累加
count(主键)：遍历每一行，取出每一行中的主键，服务层拿到主键后按行进行累加
count(某一列)：遍历每一行，取出每一行中的该列，服务层判断该列是否为null，不为null的话进行累加

因此执行效率从快到慢为：count(*) ≈ count(1) > count(主键) > count(某一列)

7. update优化避免行锁升级为表锁

我们都知道Myisam引擎为表级锁，Innodb为行级锁，那么我们在update的时候需要避免行锁升级为表锁

在A事务中执行下面sql（name上没有索引），则会导致行锁升级为表级锁，使另一个事务阻塞

update student set sex = '男' where name = '赵四';

因为Innodb的行级锁针对索引加的锁而不是针对记录的，如果没有使用到索引或者索引失效，就会升级为成表级锁