08 SQL优化

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

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

1. 插入数据

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

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

2. 手动进行事务的提交

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

3. 主键顺序插入

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

4. 大批量插入数据时使用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';
1
2
3
4
5
6
7
8

2. 主键优化

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

主键优化方式：

1. 降低主键长度

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

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

   降低页分裂

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

   降低页分裂

4. 业务操作不要修改主键

   降低页分裂和页合并

页分裂：

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两种

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

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

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

4. group by优化

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

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

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

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

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;
1

6. count优化

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

四种方式的区别：

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

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

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

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

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

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

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